MySQL Queries

"SQL aggregates and subqueries are powerful tools for data manipulation and analysis. Aggregates, such as SUM, COUNT, AVG, MIN, and MAX, provide summarized information from a dataset. Subqueries, essentially queries within queries, allow for complex data filtering and retrieval. By combining these elements, users can extract meaningful insights from their data, such as calculating total sales by product category, finding customers with above-average purchase amounts, or determining the top-selling items within a specific time frame."- Gemini 2024

Example scripts created by Gemini. ERD reverse engineered from create script in MySQL Workbench.

CREATE DATABASE toy_store;
USE toy_store;

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    city VARCHAR(50),
    country VARCHAR(50)
);

CREATE TABLE toys (
    toy_id INT AUTO_INCREMENT PRIMARY KEY,
    toy_name VARCHAR(50),
    category VARCHAR(50),
    price DECIMAL(10,2)
);

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

CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    toy_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (toy_id) REFERENCES toys(toy_id)
);

INSERT INTO customers (first_name, last_name, city, country)
VALUES
    ('Michael', 'Jordan', 'Chicago', 'USA'),
    ('Jennifer', 'Lopez', 'Los Angeles', 'USA'),
    ('David', 'Beckham', 'London', 'UK'),
    ('Sophia', 'Loren', 'Rome', 'Italy'),
    ('Jackie', 'Chan', 'Hong Kong', 'China'),
    ('Penelope', 'Cruz', 'Madrid', 'Spain'),
    ('Brad', 'Pitt', 'New Orleans', 'USA'),
    ('Angelina', 'Jolie', 'Los Angeles', 'USA'),
    ('Tom', 'Cruise', 'New York', 'USA'),
    ('Nicole', 'Kidman', 'Sydney', 'Australia');

INSERT INTO toys (toy_name, category, price)
VALUES
    ('Play-Doh Colors', 'Arts & Crafts', 14.99),
    ('Lego City', 'Building Blocks', 79.99),
    ('Barbie Dreamhouse', 'Doll', 129.99),
    ('Hot Wheels Track Set', 'Vehicles', 34.99),
    ('Teddy Bear Plush', 'Plush', 24.99),
    ('Puzzle 1000 Pieces', 'Games', 19.99),
    ('Board Game Monopoly', 'Games', 39.99),
    ('Remote Control Helicopter', 'Vehicles', 59.99),
    ('Baby Doll', 'Doll', 19.99),
    ('Action Figure Set', 'Action Figures', 29.99);

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
    (2, '2023-11-22', 180.00),
    (3, '2023-12-03', 250.00),
    (4, '2023-11-18', 120.00),
    (5, '2023-12-12', 300.00),
    (6, '2023-11-25', 150.00),
    (7, '2023-12-01', 220.00),
    (8, '2023-11-19', 180.00),
    (9, '2023-12-04', 250.00),
    (10, '2023-11-23', 120.00);

INSERT INTO order_items (order_id, toy_id, quantity)
VALUES
    (2, 3, 1),
    (2, 4, 2),
    (3, 5, 3),
    (3, 6, 1),
    (4, 7, 2),
    (4, 8, 1),
    (5, 9, 4),
    (5, 10, 2),
    (6, 1, 3),
    (6, 2, 1);

-- Aggregate functions
SELECT COUNT(*) FROM customers;
SELECT AVG(price) FROM toys;
SELECT MAX(total_amount) FROM orders;

-- Group by and having
SELECT category, AVG(price) AS avg_price
FROM toys
GROUP BY category
HAVING AVG(price) > 30;

-- Joins
SELECT c.first_name, c.last_name, SUM(oi.quantity) AS total_purchased
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.first_name, c.last_name;

-- Subqueries
SELECT * FROM toys
WHERE price > (SELECT AVG(price) FROM toys);

SELECT c.first_name, c.last_name
FROM customers c
WHERE c.customer_id IN (
    SELECT customer_id FROM orders
    WHERE total_amount > 200
);

-- Any and all
SELECT toy_name
FROM toys
WHERE price > ANY (SELECT price FROM toys WHERE category = 'Doll');

SELECT toy_name
FROM toys
WHERE price > ALL (SELECT price FROM toys WHERE category = 'Plush');

Run online at onecompiler