Filtering and sorting are fundamental operations in SQL used to retrieve specific data from tables and arrange it in a desired order. Like selecting rows from a table that meet specific criteria specified in the WHERE clause of a SQL query.
Sorting Data:
Sorting is the process of arranging rows in a specific order based on the values in one or more columns, typically specified in the ORDER BY clause.
Operators and techniques to specify conditions in the WHERE clause for more precise data retrieval.
Comparison Operators:
SQL supports various comparison operators to filter data based on conditions. Here are some commonly used ones:
1. Equality (=): Selects rows where a column’s value equals a specified value.
2. Inequality (!= or <>): Selects rows where a column’s value is not equal to a specified value.
3. Greater Than (>): Selects rows where a column’s value is greater than a specified value.
4. Less Than (<): Selects rows where a column’s value is less than a specified value.
5. Greater Than or Equal To (>=): Selects rows where a column’s value is greater than or equal to a specified value.
6. Less Than or Equal To (<=): Selects rows where a column’s value is less than or equal to a specified value.
7. BETWEEN: Selects rows where a column’s value is within a specified range.
8. LIKE: Selects rows where a column’s value matches a specified pattern using wildcard characters (% and _).
9. IS NULL: Selects rows where a column’s value is NULL.
Logical Operators:
Logical operators allow you to combine multiple conditions in the WHERE clause using logical conjunctions (AND, OR) and negation (NOT).
Use Pubs database
Example1: Retrieve books published after 2020 in the “titles” table.
SELECT title, PublicationYear
FROM titles
WHERE PublicationYear > ‘2020’;
Result:
Example 2: Retrieve books with a price greater than Rs-900 in the “sale” table.
SELECT Amount
FROM sale
WHERE amount > 900.00;
Result:
Example 3: Retrieve authors with last names starting with ‘S’ in the “authors” table.
SELECT AuthorID, AuthorFirstName, AuthorLastName
FROM authors
WHERE AuthorLastName LIKE ‘S%‘;
Result:
Example 4: Retrieve sale transactions with amount between Rs-500 and Rs-1000 in the “sale” table.
SELECT sale_id, amount
FROM sale
WHERE amount BETWEEN 500.00 AND 1000.00;
Result:
Example 5: Retrieve authors with no assigned phone numbers in the “authors” table.
SELECT AuthorID, AuthorFirstName, AuthorLastName
FROM authors
WHERE AuthorPhone IS NULL;
Result:
