Day 2: Advanced SQL Queries – Filtering, Logical Operators, and Aggregates
"Welcome to Day 2 of our 15 Days of SQL Masterclass! 🚀 Today, we dive into advanced SQL queries, focusing on filtering data using the WHERE clause, combining conditions with AND and OR, and pattern matching with LIKE. Learn how to efficiently filter and aggregate data to get meaningful insights.
📚 What You'll Learn:
Filtering results with the WHERE clause.
Combining conditions using AND and OR.
Filtering ranges with BETWEEN.
Pattern matching with LIKE and wildcards.
Using comments and aliases to make your queries clearer.
Counting rows with specific conditions and unique values.
Stay tuned for Day 3, where we’ll explore more advanced SQL topics! Don’t forget to like, subscribe, and click the notification bell to stay updated! 💻🔍"
This plan should provide a comprehensive and engaging introduction to advanced SQL querying techniques for your audience.
-----Day2 where AND OR BETWEEN LIKE
-- Drop the existing table if it exists
DROP TABLE IF EXISTS payment;
-- Create the table with DATETIME instead of TIMESTAMP
-- Drop the existing table if it exists
DROP TABLE IF EXISTS payment;
-- Create the table with DATETIME instead of TIMESTAMP
DROP TABLE IF EXISTS payment;
CREATE TABLE payment (
payment_id INT PRIMARY KEY,
customer_id INT,
staff_id INT,
rental_id INT,
amount DECIMAL(10, 2),
payment_date DATETIME -- Change to DATETIME
);
INSERT INTO payment (payment_id, customer_id, staff_id, rental_id, amount, payment_date) VALUES
(1, 101, 1, 1, 9.99, '2020-01-10 15:23:45'),
(2, 102, 2, 2, 15.99, '2020-01-12 16:45:30'),
CREATE TABLE customer (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
INSERT INTO customer (customer_id, first_name, last_name) VALUES
(21,null,'kumar')
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
CREATE TABLE film (
film_id INT PRIMARY KEY,
title VARCHAR(100),
description TEXT
);
INSERT INTO film (film_id, title, description) VALUES
(1, 'The Epic Saga', 'An epic saga of adventure and mystery.'),
(2, 'Another Day', 'A story of resilience and perseverance.'),
--WHERE Clause:
--✓ Used to FILTER the data in the output
--✓ Always after FROM
--amount=6.99
select * from payment where amount=6.99
select * from payment where amount (greaterthan) 10.99 order by amount desc
select * from payment where amount !=6.99
--handling null values
select * from customer where first_name is null
select * from customer where first_name is not null
--Logical Operators (AND, OR):
--Using AND to combine conditions.
--Using OR to include multiple conditions.
amount=6.99 and customer_id=106
select * from payment where amount=6.99 and customer_id=106
select * from payment where amount=10.99 OR customer_id=106
select * from payment where (amount=10.99 or amount=6.99) and customer_id =106
--BETWEEN … AND …:
--Filtering a range of values.
select * from payment
select payment_id, amount from payment where amount between 3.99 and 7.99 order by amount asc
select payment_id, amount from payment where payment_date between '2020-01-24' and '2020-02-26'
--LIKE Clause:
--Using LIKE for pattern matching.
--Wildcards: _ for single characters and % for sequences of characters.
select * from actor where first_name like 'A%' --vijay
select * from actor where first_name like '%A%'
select * from actor where first_name like '_A%'
--Commenting & Aliases:
--Using comments to make code more readable.
--Creating aliases for columns.
-- Single line comment
SELECT * FROM payment;
/* Multiple lines comment
SELECT * FROM payment;
*/
SELECT payment_id AS invoice_no FROM payment;
--Count of Movies with 'story' in Description and Titles Starting with 'A' or Ending with 'R':
select * from film
select count(*) as number_of_movies from film
where description like '%story%'
and (title like 'A%' or title like '%R')
--List of Customers with First Name Containing 'av' and 'A' as the Second Letter, Ordered by Last Name Descending:
select * from customer
select * from customer where first_name like '%av%' and first_name like '_A%' order by last_name desc
--Count of Payments with Amount Either 0 or Between 3.99 and 7.99 on 2020-01-01:
select * from payment
select count(*) from payment where (amount=0 or amount between 3.99 and 7.99)
and payment_date between '2020-01-01' and '2020-01-30'
--Count of Distinct Last Names of Customers:
select count(distinct last_name) from customer
select distinct last_name from customer
Day 1: SQL Basics - Master SELECT, ORDER BY, DISTINCT, LIMIT, and COUNT
• Day 1: SQL Basics - Master SELECT, ORDER B...
#SQLFiltering
#SQLWHERE Clause
#SQLAND OR
#SQLBETWEEN
#SQLLIKE
#SQLAliases
#SQLAggregates
#SQLTutorial
#SQLMasterclass
#DataFiltering SQL