Scribd
Upload
15 views6 pages

Sub Query

The document demonstrates the creation and management of an online store database using MySQL, including the creation of tables for customers, products, orders, and order items. It provides examples of inserting sample data and executing various SQL queries, including subqueries, joins, and correlated subqueries to analyze customer orders and product categories. The document also highlights the use of aggregate functions and conditions to derive insights from the data.

Uploaded by

akshat8958
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
15 views6 pages

Sub Query

The document demonstrates the creation and management of an online store database using MySQL, including the creation of tables for customers, products, orders, and order items. It provides examples of inserting sample data and executing various SQL queries, including subqueries, joins, and correlated subqueries to analyze customer orders and product categories. The document also highlights the use of aggregate functions and conditions to derive insights from the data.

Uploaded by

akshat8958
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 6

-- =============================================

-- MySQL Subqueries Demonstration


-- Online Store Database Example
-- =============================================

-- Create database and set it as the active database


CREATE DATABASE online_store;
USE online_store;

-- =============================================
-- TABLE CREATION
-- =============================================

-- Create customers table


CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
city VARCHAR(50),
state VARCHAR(2),
signup_date DATE
);

-- Create products table


CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock_quantity INT NOT NULL
);

-- Create orders table


CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATETIME NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Create order_items table


CREATE TABLE order_items (
item_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
item_price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- =============================================
-- SAMPLE DATA INSERTION
-- =============================================

-- Insert data into customers


INSERT INTO customers (first_name, last_name, email, city, state, signup_date)
VALUES
('John', 'Smith', '[email protected]', 'New York', 'NY', '2023-01-15'),
('Sarah', 'Johnson', '[email protected]', 'Los Angeles', 'CA', '2023-02-20'),
('Michael', 'Brown', '[email protected]', 'Chicago', 'IL', '2023-03-05'),
('Emily', 'Davis', '[email protected]', 'Houston', 'TX', '2023-01-30'),
('Robert', 'Wilson', '[email protected]', 'Phoenix', 'AZ', '2023-02-10'),
('Jennifer', 'Martinez', '[email protected]', 'Philadelphia', 'PA', '2023-03-
15'),
('David', 'Anderson', '[email protected]', 'San Antonio', 'TX', '2023-01-25'),
('Lisa', 'Thomas', '[email protected]', 'San Diego', 'CA', '2023-02-28'),
('James', 'Jackson', '[email protected]', 'Dallas', 'TX', '2023-03-12'),
('Mary', 'White', '[email protected]', 'San Jose', 'CA', '2023-01-18');

-- Insert data into products


INSERT INTO products (product_name, category, price, stock_quantity) VALUES
('Laptop Pro', 'Electronics', 1299.99, 25),
('Smartphone X', 'Electronics', 899.99, 50),
('Wireless Headphones', 'Electronics', 199.99, 100),
('Coffee Maker', 'Home Appliances', 79.99, 30),
('Blender', 'Home Appliances', 49.99, 40),
('Running Shoes', 'Sports', 129.99, 75),
('Yoga Mat', 'Sports', 29.99, 120),
('Mystery Novel', 'Books', 14.99, 200),
('Cookbook', 'Books', 24.99, 150),
('Desk Chair', 'Furniture', 149.99, 15);

-- Insert data into orders


INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-04-10 14:30:00', 1499.98),
(2, '2023-04-11 10:15:00', 249.98),
(3, '2023-04-12 16:45:00', 899.99),
(4, '2023-04-13 13:20:00', 1329.98),
(2, '2023-04-14 09:30:00', 49.99),
(5, '2023-04-15 15:10:00', 179.98),
(6, '2023-04-16 11:05:00', 159.98),
(7, '2023-04-17 14:55:00', 39.98),
(8, '2023-04-18 12:40:00', 899.99),
(9, '2023-04-19 16:25:00', 229.98),
(10, '2023-04-20 10:50:00', 279.97),
(1, '2023-04-21 13:35:00', 24.99),
(3, '2023-04-22 15:15:00', 129.99);

-- Insert data into order_items


INSERT INTO order_items (order_id, product_id, quantity, item_price) VALUES
(1, 1, 1, 1299.99),
(1, 3, 1, 199.99),
(2, 5, 1, 49.99),
(2, 7, 1, 29.99),
(2, 9, 1, 24.99),
(3, 2, 1, 899.99),
(4, 1, 1, 1299.99),
(4, 6, 1, 129.99),
(5, 5, 1, 49.99),
(6, 4, 1, 79.99),
(6, 8, 1, 14.99),
(6, 9, 1, 24.99),
(7, 6, 1, 129.99),
(7, 8, 2, 14.99),
(8, 8, 1, 14.99),
(8, 9, 1, 24.99),
(9, 2, 1, 899.99),
(10, 3, 1, 199.99),
(10, 6, 1, 129.99),
(11, 5, 1, 49.99),
(11, 7, 1, 29.99),
(11, 8, 1, 14.99),
(12, 9, 1, 24.99),
(13, 6, 1, 129.99);

-- =============================================
-- BASIC SUBQUERIES
-- =============================================

-- Find all customers who have placed at least one order


SELECT * FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id FROM orders
);

-- Finding customers who haven't placed orders


SELECT * FROM customers
WHERE customer_id NOT IN (
SELECT DISTINCT customer_id FROM orders
);

-- Find products with a price higher than average


SELECT * FROM products
WHERE price > (
SELECT AVG(price) FROM products
);

-- =============================================
-- GROUP BY WITH HAVING
-- =============================================

-- Find categories that have more than 2 products


SELECT category, COUNT(*)
FROM products
GROUP BY category
HAVING COUNT(*) > 2;

-- =============================================
-- SUBQUERIES IN THE WHERE CLAUSE
-- =============================================

-- Find all orders made by customers from Texas


SELECT * FROM orders
WHERE customer_id IN (
SELECT customer_id FROM customers WHERE state = 'TX'
);

-- Alternative using JOIN


SELECT * FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.state = 'TX';

-- =============================================
-- JOIN QUERIES VS SUBQUERIES
-- =============================================

-- Find all customers who ordered electronics products


-- Using JOINs
SELECT * FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE p.category = "Electronics";

-- Using subquery
SELECT * FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE oi.product_id IN (
SELECT product_id FROM products WHERE category = "Electronics"
);

-- =============================================
-- SUBQUERIES WITH AVERAGE CALCULATION
-- =============================================

-- Customers who spent more than average


-- 1. Calculate the total amount spent by each customer
-- 2. Calculate the average total spending across all customers
-- 3. Identify customers whose total spending exceeds this average

-- First attempt (fails - need alias)


SELECT AVG(total_spent) AS average_customer_spending FROM
(SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY
customer_id);

-- Error Code: 1248. Every derived table must have its own alias

-- Correct version with alias


SELECT AVG(total_spent) AS average_customer_spending FROM
(SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY
customer_id) AS customer_total;

-- Final query - Find customers who spent more than average


SELECT *,
(SELECT SUM(total_amount) FROM orders WHERE customer_id =
customers.customer_id) AS total_spent
FROM customers
WHERE
(SELECT SUM(total_amount) FROM orders WHERE customer_id =
customers.customer_id) >
(SELECT AVG(total_spent) AS average_customer_spending FROM
(SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY
customer_id) AS customer_total);

-- =============================================
-- COMPLEX SUBQUERIES
-- =============================================

-- Find customers who have ordered all products in the 'Electronics' category
SELECT c.email
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.category = "Electronics"
GROUP BY c.customer_id
HAVING COUNT(DISTINCT p.product_id) = (SELECT COUNT(*) FROM products WHERE
category="Electronics");

-- Find all customers who are not from California but have purchased the same
product-quantity combinations as California customers
SELECT c.email, c.state, p.product_name, oi.quantity FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE c.state != 'CA'
AND (oi.product_id, oi.quantity) IN
(SELECT oi.product_id, oi.quantity FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE c.state = 'CA');

-- =============================================
-- CORRELATED SUBQUERIES AND EXISTS
-- =============================================

-- Correlated Subqueries
-- A correlated subquery is a subquery that uses values from the outer query.
-- Unlike regular subqueries which can be executed independently, correlated
subqueries are dependent on the outer query and
-- must be re-evaluated for each row processed by the outer query.
-- Can Appear in Various SQL Clauses (SELECT / WHERE / HAVING)

-- Scalar subquery
-- Always Returns Exactly One Value
-- Can Be Independent or Correlated
-- Can Appear in Various SQL Clauses (SELECT / WHERE / HAVING)

-- Find customers who have placed at least one order


-- Using JOIN
SELECT DISTINCT c.customer_id, c.email
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

-- Using EXISTS
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE c.customer_id = o.customer_id
);

-- Find customers who haven't placed any orders


SELECT * FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE c.customer_id = o.customer_id
);

-- Products that have never been ordered


SELECT * FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id
);

-- Find customers who have ordered electronics products


-- Using JOINs
SELECT DISTINCT c.customer_id, c.first_name, c.last_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.category = 'Electronics';

-- Using EXISTS
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE c.customer_id = o.customer_id
AND p.category = 'Electronics'
);

You might also like