Lesson: Advanced Queries and Subqueries
Review of Basic SQL Queries
Recall that SQL stands for Structured Query Language, and is used to manage and manipulate data stored in relational databases. Here are some of the basic SQL commands we’ve covered so far:
- SELECT: used to retrieve data from one or more tables
- FROM: used to specify the table or tables to select data from
- WHERE: used to filter the results of a query based on some condition
- ORDER BY: used to sort the results of a query in ascending or descending order
- GROUP BY: used to group the results of a query by one or more columns
- JOIN: used to combine data from two or more tables based on a related column
Using Subqueries in SQL
A subquery is a query that is nested within another query. It is used to retrieve data that will be used by the main query as a filter, a calculation, or a comparison. Subqueries are enclosed in parentheses, and can be used in the SELECT, FROM, WHERE, and HAVING clauses of a query.
There are two types of subqueries: correlated and non-correlated. Correlated subqueries are executed for each row returned by the outer query, while non-correlated subqueries are executed only once for the whole query. Correlated subqueries can be slower than non-correlated subqueries, but can be more powerful and flexible.
Here are some examples of using subqueries in SQL:
- Using a subquery to filter results based on a condition from another table:
sqlCopy codeSELECT *
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
- Using a subquery to calculate a statistic on a subset of data:
sqlCopy codeSELECT AVG(salary)
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');
- Using a subquery to compare data between two tables:
sqlCopy codeSELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Writing Complex Queries with Multiple Subqueries
Complex queries often involve multiple subqueries that are combined to produce the desired result. To write a complex query, it’s important to break down the problem into smaller, more manageable parts, and then use subqueries to tackle each part individually.
Here’s an example of a complex query that involves multiple subqueries:
sqlCopy codeSELECT first_name, last_name, salary, commission_pct, job_id, department_id
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
AND commission_pct IS NULL
AND department_id IN (SELECT department_id FROM departments WHERE location_id = (SELECT location_id FROM locations WHERE country_id = 'US'))
AND job_id IN (SELECT job_id FROM jobs WHERE max_salary > (SELECT AVG(salary) FROM employees WHERE job_id LIKE '%REP%'));
This query selects the first name, last name, salary, commission percentage, job ID, and department ID of all employees who meet the following criteria:
- Their salary is greater than the average salary of all employees.
- They don’t receive any commission.
- They belong to a department that is located in the United States.
- Their job title contains the word ‘REP’ and their maximum salary is higher than the average salary of all employees with the word ‘REP’ in their job title.
This is just one example of a complex query that uses multiple subqueries. With practice and experimentation, you can learn to write your own complex queries that meet your specific needs.
Exercise:
Write a query to retrieve the names of customers who have placed orders for products that are not in stock.
dowhatkennydoes.com 