subquery
Subquery
• A subquery (also called an inner query or nested query) is a query
embedded inside another [Link] is used to return data that will be
used in the main query (also called the outer query).
• Syntax Example
SELECT name, salaryFROM employeesWHERE salary > (SELECT
AVG(salary) FROM employees);
Types of Subqueries1
• Single Row Subquery
• Returns only one row.
SELECT name FROM employeesWHERE salary = (SELECT MAX(salary)
FROM employees);
2. Multiple Row SubqueryReturns multiple rows.
SELECT name FROM employeesWHERE department_id IN (SELECT
department_id FROM departments WHERE location = 'Delhi');
• Multiple Column
• SubqueryReturns multiple columns.
SELECT name FROM employeesWHERE (department_id, job_id)
IN(SELECT department_id, job_id FROM job_openings);
4. Correlated Subquery
Depends on the outer query row-by-row.
SELECT name FROM employees e1WHERE salary > ( SELECT
AVG(salary) FROM employees e2 WHERE e1.department_id =
e2.department_id);
subquery vs Join
Feature Subquery JOIN
Readability Easier for nested logic Better for complex relationships
May be slower (especially
Performance Often more optimized
correlated)
Use case Filtering, aggregation Combining tables
Important Points
• must be enclosed in parentheses ().
• Correlated subqueries execute once per row of the outer query
• IN, EXISTS, ANY, ALL, =, > are commonly used with subqueries.
• A void subqueries in the SELECT clause for performance-sensitive
queries.
Logistics CompanyProblem:
• Find delivery agents who completed more deliveries than the most
active agent in another city.
SELECT agent_id, nameFROM agentsWHERE city = 'Mumbai'AND
( SELECT COUNT(*) FROM deliveries d1 WHERE d1.agent_id =
agents.agent_id) > ( SELECT MAX(del_count) FROM ( SELECT
agent_id, COUNT(*) AS del_count FROM deliveries d2 WHERE
city = 'Delhi' GROUP BY agent_id ) AS delhi_stats);
Sales Management
System
--- Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
city VARCHAR(50)
);
-- Products Table
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10, 2)
);
-- Sales Table
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
quantity INT,
sale_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
• -- Insert into Customers
• INSERT INTO customers VALUES
• (1, 'Alice', 'Delhi'),
• (2, 'Bob', 'Mumbai'),
• (3, 'Charlie', 'Delhi'),
• (4, 'David', 'Chennai');
• -- Insert into Products
• INSERT INTO products VALUES
• (101, 'Laptop', 60000.00),
• (102, 'Phone', 30000.00),
• (103, 'Tablet', 20000.00),
• (104, 'Monitor', 15000.00);
• -- Insert into Sales
• INSERT INTO sales VALUES
• (1, 1, 101, 1, '2024-01-05'),
• (2, 2, 102, 2, '2024-01-10'),
• (3, 1, 104, 1, '2024-02-15'),
• (4, 3, 103, 3, '2024-03-20'),
• Show all sales with customer and product details
SELECT s.sale_id, [Link] AS customer_name, p.product_name, [Link],
s.sale_dateFROM sales sJOIN customers c ON s.customer_id =
c.customer_idJOIN products p ON s.product_id = p.product_id;
B. List customers who bought products worth more than 50,000
SELECT DISTINCT [Link] customers cJOIN sales s ON c.customer_id =
s.customer_idJOIN products p ON s.product_id = p.product_idWHERE ([Link] *
[Link]) > 50000;
• Customers who made any purchase
SELECT nameFROM customersWHERE customer_id IN (SELECT
DISTINCT customer_id FROM sales);
D. Subquery: Products never sold
SELECT product_nameFROM productsWHERE product_id NOT IN
(SELECT product_id FROM sales);
• Sales with above-average quantity
SELECT sale_id, customer_id, quantityFROM sales s1WHERE quantity >
( SELECT AVG(quantity) FROM sales s2 WHERE s1.product_id = s2.product_id);
Total revenue per customer
SELECT [Link], SUM([Link] * [Link]) AS total_spentFROM customers
cJOIN sales s ON c.customer_id = s.customer_idJOIN products p ON
s.product_id = p.product_idGROUP BY [Link];
Website Management Database
Structure
user_id name email country join_date
1 Alice alice@[Link] USA 2023-01-15
2 Bob bob@[Link] India 2023-03-10
3 Charlie charlie@[Link] UK 2023-02-05
4 David david@[Link] India 2023-04-20
5 Eva eva@[Link] Canada 2023-06-18
page_views table
view_id user_id page_name view_date
1 1 Home 2023-07-01
2 2 About 2023-07-01
3 1 Contact 2023-07-01
4 3 Home 2023-07-02
5 2 Services 2023-07-02
6 4 Home 2023-07-02
7 5 About 2023-07-03
8 1 Home 2023-07-03
9 3 Contact 2023-07-04
10 2 Home 2023-07-04
Total number of views by each user
SELECT [Link], COUNT(p.view_id) AS total_views
FROM users u
JOIN page_views p ON u.user_id = p.user_id
GROUP BY [Link];r of views by each user
Most visited page
SELECT page_name, COUNT(*) AS view_countFROM page_viewsGROUP BY
page_nameORDER BY view_count DESC;
• Page views per country
SELECT [Link], COUNT(p.view_id) AS total_viewsFROM users uJOIN
page_views p ON u.user_id = p.user_idGROUP BY [Link];
Daily page view counts
SELECT view_date, COUNT(*) AS viewsFROM page_viewsGROUP BY
view_dateORDER BY view_date;
Top 3 users by total page views
SELECT [Link], COUNT(p.view_id) AS total_viewsFROM users uJOIN page_views
p ON u.user_id = p.user_idGROUP BY [Link] BY total_views DESC;
• Average page views per user
SELECT AVG(view_count) AS avg_views_per_userFROM ( SELECT
user_id, COUNT(*) AS view_count FROM page_views GROUP BY
user_id) AS user_views;
Pages visited on more than 2 different days
SELECT page_name, COUNT(DISTINCT view_date) AS day_countFROM
page_viewsGROUP BY page_nameHAVING COUNT(DISTINCT
view_date) > 2;