SQL Queries Nâng cao

JOINs

INNER JOIN

SELECT users.name, orders.order_date, orders.total_amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN

SELECT users.name, orders.order_date
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

RIGHT JOIN

SELECT users.name, orders.order_date
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

FULL OUTER JOIN

SELECT users.name, orders.order_date
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

Subqueries

Subquery trong WHERE

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Subquery trong FROM

SELECT department, AVG(avg_salary) as dept_avg_salary
FROM (SELECT department_id as department, AVG(salary) as avg_salary
      FROM employees
      GROUP BY department_id) as dept_salaries
GROUP BY department;

Window Functions

ROW_NUMBER()

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

RANK() và DENSE_RANK()

SELECT name, salary, department,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_dense_rank
FROM employees;

SUM() với OVER()

SELECT name, salary, department,
       SUM(salary) OVER (PARTITION BY department) as dept_total_salary,
       salary * 100.0 / SUM(salary) OVER (PARTITION BY department) as salary_percentage
FROM employees;

Common Table Expressions (CTEs)

WITH department_stats AS (
  SELECT department_id, 
         AVG(salary) as avg_salary,
         COUNT(*) as employee_count
  FROM employees
  GROUP BY department_id
)
SELECT d.name as department_name,
       ds.avg_salary,
       ds.employee_count
FROM departments d
JOIN department_stats ds ON d.id = ds.department_id
WHERE ds.avg_salary > 50000;

Bài tập thực hành

Hãy thực hành với các câu truy vấn phức tạp!