DAY-2

Day 2: SQL WHERE, ORDER BY, and DISTINCT

1. WHERE Clause

The WHERE clause is used to filter records based on a specific condition. It is commonly used in SELECT, UPDATE, and DELETE statements.

Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Example:

Retrieve all employees from the Employees table where the department is 'IT'.

SELECT * FROM Employees

WHERE Department = 'IT';

Examples:

1. Retrieve employees with a salary greater than 50000:

SELECT * FROM Employees WHERE Salary > 50000;

2. Find customers from 'New York':

SELECT * FROM Customers WHERE City = 'New York';

3. Get orders placed after January 1, 2024:

SELECT * FROM Orders WHERE OrderDate > '2024-01-01';

4. Using Multiple Conditions (AND & OR):

SELECT * FROM Products WHERE Price > 50 AND Category = 'Electronics';

SELECT * FROM Customers WHERE City = 'Los Angeles' OR City = 'Chicago';

5. Using BETWEEN, IN, LIKE:

SELECT * FROM Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';

SELECT * FROM Customers WHERE City IN ('New York', 'Los Angeles', 'Chicago');

SELECT * FROM Products WHERE ProductName LIKE 'Laptop%';

Operators Used in WHERE Clause:

·         = : Equals

·         != or <> : Not equal

·         > : Greater than

·         < : Less than

·         >= : Greater than or equal to

·         <= : Less than or equal to

·         BETWEEN : Between a range

·         IN : Matches any value in a list

·         LIKE : Pattern matching

·         IS NULL : Checks for NULL values

Example Using LIKE and BETWEEN:

SELECT * FROM Employees

WHERE Name LIKE 'A%'; -- Finds names starting with 'A'

SELECT * FROM Orders

WHERE OrderDate BETWEEN '2024-01-01' AND '2024-03-31';

 

2. ORDER BY Clause

The ORDER BY clause is used to sort the result set in either ascending (ASC) or descending (DESC) order.

Syntax:

SELECT column1, column2, ...

FROM table_name

ORDER BY column1 [ASC|DESC];

1. Retrieve all employees sorted by salary (ascending order):

SELECT * FROM Employees ORDER BY Salary ASC;

Sorting by Multiple Columns:

SELECT * FROM Employees

ORDER BY Department ASC, Salary DESC;

2. Retrieve all products sorted by price (descending order):

SELECT * FROM Products ORDER BY Price DESC;

3. Sort customers by city (ascending) and name (descending):

SELECT * FROM Customers ORDER BY City ASC, CustomerName DESC;

4. Sorting with WHERE Clause:

SELECT * FROM Orders WHERE OrderDate > '2024-01-01' ORDER BY OrderDate DESC;

3. DISTINCT Clause

The DISTINCT keyword is used to return unique values in a column, removing duplicates from the result set.

Syntax:

SELECT DISTINCT column1, column2, ...

FROM table_name;

Examples:

1. Retrieve unique cities from the Customers table:

SELECT DISTINCT City FROM Customers;

Using DISTINCT on Multiple Columns:

SELECT DISTINCT Department, JobTitle FROM Employees;

2. Find unique job titles from the Employees table:

SELECT DISTINCT JobTitle FROM Employees;

3. Get a list of unique product categories:

SELECT DISTINCT Category FROM Products;

4. Combining DISTINCT with ORDER BY:

SELECT DISTINCT City FROM Customers ORDER BY City ASC;

Advanced Queries Combining WHERE, ORDER BY, DISTINCT

1. Retrieve unique product categories where price is above 100, sorted alphabetically:

SELECT DISTINCT Category FROM Products WHERE Price > 100 ORDER BY Category ASC;

2. Get a list of customers who placed an order after 2023-01-01, sorted by order date (newest first):

SELECT DISTINCT CustomerID, CustomerName FROM Orders WHERE OrderDate > '2023-01-01' ORDER BY OrderDate DESC;

3. Find unique departments where employees have a salary above 70000, sorted in descending order:

SELECT DISTINCT Department FROM Employees WHERE Salary > 70000 ORDER BY Department DESC;

Summary

·         WHERE filters data based on a condition.

·         ORDER BY sorts the results.

·         DISTINCT removes duplicates from results. Returns unique values in a column.

Combine WHERE, ORDER BY, and DISTINCT for powerful queries.

Previous                                                                                                                                  Next