2.advanced_queries.md

Advanced SQL Query Clauses

Building on our knowledge of basic CRUD operations, let's explore the powerful clauses that help us filter, sort, and analyze data more effectively in SQL.

1. WHERE Clause (Filtering Data)

• Purpose: To filter rows based on specified conditions.
• Syntax:

  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;

• Common Operators:
  • Comparison: =, <>, <, >, <=, >=
  • Logical: AND, OR, NOT
  • Pattern Matching: LIKE, IN, BETWEEN
• Example: Using our Employees table:

  -- Find employees with salary greater than 65000
  SELECT FirstName, LastName, Salary
  FROM Employees
  WHERE Salary > 65000;
  
  -- Find employees in HR or IT departments
  SELECT *
  FROM Employees
  WHERE Department IN ('HR', 'IT');
  
  -- Find employees with names starting with 'A'
  SELECT *
  FROM Employees
  WHERE FirstName LIKE 'A%';

2. ORDER BY Clause (Sorting Data)

• Purpose: To sort the result set based on one or more columns.
• Syntax:

  SELECT column1, column2, ...
  FROM table_name
  [WHERE condition]
  ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

• Example:

  -- Sort employees by salary in descending order
  SELECT FirstName, LastName, Salary
  FROM Employees
  ORDER BY Salary DESC;
  
  -- Sort by department first, then by salary
  SELECT *
  FROM Employees
  ORDER BY Department ASC, Salary DESC;

3. LIMIT Clause (Restricting Results)

• Purpose: To limit the number of rows returned in the result set.
• Syntax:

  SELECT column1, column2, ...
  FROM table_name
  [WHERE condition]
  [ORDER BY column1, column2, ...]
  LIMIT number;

• Example:

  -- Get the top 3 highest-paid employees
  SELECT FirstName, LastName, Salary
  FROM Employees
  ORDER BY Salary DESC
  LIMIT 3;

4. GROUP BY Clause (Grouping Data)

• Purpose: To group rows that have the same values in specified columns.
• Common Aggregate Functions:
  • COUNT(): Count number of rows
  • SUM(): Calculate sum
  • AVG(): Calculate average
  • MAX(): Find maximum value
  • MIN(): Find minimum value
• Syntax:

  SELECT column1, aggregate_function(column2)
  FROM table_name
  [WHERE condition]
  GROUP BY column1;

• Example:

  -- Calculate average salary by department
  SELECT Department, 
         COUNT(*) as EmployeeCount,
         AVG(Salary) as AvgSalary
  FROM Employees
  GROUP BY Department;

5. HAVING Clause (Filtering Groups)

• Purpose: To filter groups based on aggregate conditions (similar to WHERE but for grouped data).
• Syntax:

  SELECT column1, aggregate_function(column2)
  FROM table_name
  [WHERE condition]
  GROUP BY column1
  HAVING aggregate_condition;

• Example:

  -- Find departments with average salary > 70000
  SELECT Department, 
         AVG(Salary) as AvgSalary
  FROM Employees
  GROUP BY Department
  HAVING AVG(Salary) > 70000;

Practical Session 🔴

-- Let's add more sample data first
INSERT INTO Employees VALUES
(104, 'David', 'Brown', 'IT', 72000.00),
(105, 'Emma', 'Davis', 'HR', 58000.00),
(106, 'Frank', 'Miller', 'Development', 80000.00),
(107, 'Grace', 'Wilson', 'IT', 68000.00);

-- Find all IT employees earning more than 70000
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Department = 'IT' AND Salary > 70000;

-- Sort employees by department and salary
SELECT *
FROM Employees
ORDER BY Department ASC, Salary DESC;

-- Get department statistics
SELECT 
    Department,
    COUNT(*) as EmployeeCount,
    ROUND(AVG(Salary), 2) as AvgSalary,
    MAX(Salary) as MaxSalary,
    MIN(Salary) as MinSalary
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 1;

-- Find top 3 earners
SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 3;

These advanced query clauses are essential for data analysis and reporting in SQL. They allow you to:

• Filter data precisely using WHERE
• Sort results meaningfully with ORDER BY
• Limit result sets using LIMIT
• Perform aggregate analysis with GROUP BY
• Filter grouped results using HAVING

Combining these clauses gives you powerful tools for data manipulation and analysis in SQL.