2.2 SQL WHERE Clause

SQL WHERE Clause

• The WHERE clause filters records in a table
• It uses comparison operators like =, >, <, >=, <=, <> (not equal to)
• Example: SELECT * FROM EMPLOYEES WHERE SALARY > 600

SQL WHERE BETWEEN ... AND

• The BETWEEN operator selects values within a range
• Includes the start and end values
• Example: SELECT * FROM PRODUCTS WHERE PRICE BETWEEN 10 AND 20

SQL WHERE IN

• The IN operator selects rows where a column's value matches one of a list of values.
• Example: SELECT * FROM CUSTOMERS WHERE CITY IN ('London', 'Paris', 'New York')

SQL WHERE LIKE

• The LIKE operator filters for values matching a pattern
• Uses wildcards: % (matches any sequence of characters), _ (matches any single character)
• Example: SELECT * FROM USERS WHERE NAME LIKE 'J%' (finds names starting with 'J')

SQL WHERE NULL

• NULL represents missing or unknown values
• The IS NULL operator tests for NULL values
• Example: SELECT * FROM ORDERS WHERE DELIVERY_DATE IS NULL

SQL WHERE (NOT, AND, OR)

• Combining multiple conditions with AND, OR, NOT
• AND requires all conditions to be true
• OR requires at least one condition to be true
• NOT reverses a condition
• Example: SELECT * FROM ORDERS WHERE STATUS = 'Shipped' AND CUSTOMER_ID = 101

SQL WHERE (ORDER BY)

• Sorts query results
• Ascending order (by default):
  • Example: SELECT * FROM PRODUCTS ORDER BY PRICE
• Descending order use DESC
  • Example: SELECT * FROM PRODUCTS ORDER BY PRICE DESC

SQL (DISTINCT)

• Selects only unique values for a column.

• Example: SELECT DISTINCT CITY FROM CUSTOMERS

SQL (LOWER/UPPER)

• LOWER transforms text to lowercase.
• UPPER transforms text to uppercase.

SQL (CONCAT)

• Joins strings together into a new string
• Example: SELECT CONCAT(FirstName,' ',LastName) AS FullName

SQL (Aggregate Functions)

• Used with GROUP BY clause to perform calculations on values within that group
• Examples:
  • AVG(): Calculates the average
  • COUNT(): Counts rows
  • MAX(): Finds the maximum value
  • MIN(): Finds the minimum value
  • SUM(): Calculates the sum

SQL (HAVING)

• Filters groups after a GROUP BY operation.
• Example: selects customers who have placed more than two orders
• SELECT COUNT(*), CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(*) > 2

General SQL Syntax and Structure

• SELECT clause specifies which columns to retrieve
• FROM clause specifies which table to retrieve data from
• WHERE clause filters the data based on conditions
• GROUP BY clause groups rows with the same values in specified columns
• HAVING clause filters groups based on conditions
• ORDER BY clause sorts results