Skip to content

Instantly share code, notes, and snippets.

@davinaleong

davinaleong/BCG SQL - 3.2.3 Slide 13.sql

Last active March 31, 2025 06:52
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save davinaleong/ea17c0a67a670a8325cd5b473b8c0f72 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save davinaleong/ea17c0a67a670a8325cd5b473b8c0f72 to your computer and use it in GitHub Desktop.
Download ZIP
BCG SQL Practice Questions
Raw
BCG SQL - 3.2.3 Slide 13.sql
# UI3.2.3BasicSQL - Hands-on Practice 1 (Slide 13)
# Code from Davina Leong
USE sql_101;
/*
1. View all rows for each of the sales and managers tables.
*/
SELECT * FROM sales;
SELECT * FROM managers;
/*
2. View the product_id and product_name columns of the
products table.
*/
SELECT product_id, product_name FROM products;
/*
3. Find the number of unique product names from the
products table.
*/
SELECT DISTINCT product_name FROM products;
/*
4. Find the number of unique first names from the
managers table.
*/
SELECT COUNT(DISTINCT first_name) AS unique_first_name FROM managers;
/*
5. View the product_name, unit_list_price and
unit_production_cost columns of the products table,
while giving them aliases of pname, list_p, and prod_c
respectively.
*/
SELECT product_name AS pname, unit_list_price AS list_p, unit_production_cost AS prod_c
FROM products;
Raw
BCG SQL - 3.2.3 Slide 46.sql
# IU3.2.3BasicSQL - Hands-on Practice 2 (Slide 46)
# Davina Leong
USE sql_101;
/*
1. Find the product id and unit list price of products
belonging to the 'Yoghurt' category.
*/
SELECT product_id, unit_list_price
FROM products
WHERE product_category = "Yoghurt";
/*
2. Find all the information (i.e., all columns) of the
managers with null values in their first NAME.
*/
UPDATE managers SET first_name = ""
WHERE sales_manager_id IS NOT NULL;
/*
3. Find the sales ID and sales date where quantity sold on
that date is greater than or equal to 2100.
*/
SELECT sales_manager_id, sales_date
FROM sales
WHERE quantity_sold >= 2100;
/*
4. Find the manager ID and the sum of quantity sold for all
products (except those with ID 7001001) for each of the
managers in the sales table and sort the output by
manager ID in descending ORDER.
*/
SELECT sales_manager_id, SUM(quantity_sold) AS total_quantity_sold
FROM sales
WHERE sales_manager_id NOT IN(7001001)
GROUP BY sales_manager_id
ORDER BY sales_manager_id DESC;
/*
5. Find the product ID, product name, and unit production
cost of the products with unit production cost below
$1.10 and sort the output by production cost in
ascending ORDER.
Hint 1: Omit the dollar sign when comparing numeric values
*/
SELECT product_id, product_name, unit_production_cost
FROM products
WHERE unit_production_cost < 1.10;
/*
6. Find the product ID and sales date with the highest
quantity sold from sales transacted after 30 Oct 2021
(exclusive) except for products with IDs 7001001 and
7001002.
Hint 1: Dates in SQL (and MySQL) are in reversed format: YYYY-MM-DD.
E.g. If the date is the 22nd of Mar 2025, it is written like this: 2025-03-22
*/
SELECT product_id, sales_date
FROM sales
WHERE sales_date >= "2021-10-30" AND product_id NOT IN(7001001, 7001002);
Raw
BCG SQL - 3.2.3 Slide 47.sql
# UI3.2.3BasicSQL - Recap Practice for Basic SQL (Slide 47)
# Code from Davina Leong
USE sql_101;
/*
1. Find the sales dates where the quantity sold for
product ID 7001001 is fewer than 140.
*/
SELECT sales_date, quantity_sold
FROM sales
WHERE product_id = 7001001;
/*
2. Find the corresponding profit margin for each product
name (i.e., list price subtract production cost). Give an
appropriate alias for the profit margin column.
*/
SELECT (unit_list_price - unit_production_cost) AS profit_margin
FROM products;
/*
3. Find the name and category of products with unit
production cost lower than the average.
*/
SELECT product_name, unit_production_cost
FROM products
WHERE unit_production_cost < (SELECT AVG(unit_production_cost) FROM products);
/*
4. Find the top 3 unique sales dates where the sum of
quantity sold on that date is the highest across the
2021 sales data.
*/
SELECT DISTINCT sales_date, SUM(quantity_sold) AS total_quantity_sold
FROM sales
WHERE YEAR(sales_date) = 2021
GROUP BY sales_id
ORDER BY sales_date DESC
LIMIT 3;
Raw
BCG SQL - 3.2.4 Slide 19.sql
# UI3.2.4IntermediateSQL - Hands-on Practice 1 (Slide 19)
# Code from Davina Leong
USE sql_101;
/*
1. Find the corresponding manager last name and date of
birth for each sales ID (using a left join).
*/
SELECT s.sales_id, m.last_name, m.date_of_birth
FROM managers AS m
LEFT JOIN sales AS s ON s.sales_manager_id = m.sales_manager_id;
/*
2. Find the product name and product category
corresponding to each sales ID (using an inner join).
*/
SELECT s.sales_id, p.product_name, p.product_category
FROM sales AS s
INNER JOIN products AS p ON s.product_id = p.product_id;
/*
3. Find the sales ID, quantity sold, and product name for
Milk category products that were sold between 11 Jan
2021 and 17 Jan 2021 and with quantity sold greater
than 1000.
*/
SELECT s.sales_id, s.quantity_sold, p.product_name, p.product_category
FROM sales AS s
LEFT JOIN products AS p ON s.product_id = p.product_id
WHERE p.product_category = "Milk" AND s.sales_date BETWEEN "2021-01-11" AND "2021-01-17" AND s.quantity_sold > 1000;
/*
4. Find the sales ID, sales date, product name, and
manager last name for sales that had product quantity
sold of less than 30.
*/
SELECT s.sales_id, s.sales_date, p.product_name, m.last_name
FROM sales AS s
LEFT JOIN products AS p ON s.product_id = p.product_id
LEFT JOIN managers AS m ON s.sales_manager_id = m.sales_manager_id
WHERE s.quantity_sold < 30;
/*
5. Find the total quantity sold (of all products) for each
sales manager (get the first name and last name).
*/
SELECT m.first_name, m.last_name, SUM(s.quantity_sold) AS total_quantity_sold
FROM managers AS m
LEFT JOIN sales AS s ON m.sales_manager_id = s.sales_manager_id
GROUP BY m.first_name, m.last_name;
Raw
BCG SQL - 3.2.4 Slide 46.sql
# IU3.2.4IntermediateSQL - Hands-on Practice 2 (Slide 46)
# Code from Davina Leong
USE sql_101;
/*
1. Create a CTE of sales table with dates after 24 Sep
2021 and a CTE of products table with products in
Yoghurt category, and then do an inner join of both.
*/
WITH
sales_cte AS (SELECT s.product_id FROM sales AS s WHERE s.sales_date < "2021-09-24"),
products_cte AS (SELECT p.product_id, product_category FROM products AS p WHERE p.product_category = "Milk")
SELECT * FROM sales_cte
INNER JOIN products_cte ON sales_cte.product_id = products_cte.product_id;
/*
2. Find the product ID, name, and category of products
which matches any record in the sales table for which
the sales date is after 24 Sep 2021 and the quantity
sold is greater than 1100.
*/
SELECT p.product_id, p.product_name, p.product_category
FROM products p
WHERE p.product_id = ANY(SELECT s.product_id FROM sales s WHERE s.sales_date > "2021-09-24" AND s.quantity_sold > 1100);
/*
3. Find the manager full names (i.e., concatenated first
and last name), date joined, and a new column called
`experience_level` based on the following
classification: If joined on 01 Jun 2019 or earlier, the
status is 'High', else if joined on 31 Dec 2019 or earlier,
status is 'Medium', and everything else is set as 'Low'.
*/
SELECT CONCAT(m.first_name, " ", m.last_name) AS full_names,
CASE
WHEN date_joined >= "2019-06-01" THEN "High"
WHEN date_joined >= "2019-12-31" THEN "Medium"
ELSE "Low"
END AS experience_level
FROM managers m;
/*
4. Find the total profits (rounded 2 decimal places) for
each sales month. Note that total profit is equal to
quantity sold multiplied by the profit margin.
*/
WITH profit_per_sale AS (
SELECT
s.sales_id,
s.sales_date,
s.quantity_sold,
(p.unit_list_price - p.unit_production_cost) AS profit_per_unit
FROM sales s
JOIN products p ON s.product_id = p.product_id
)
SELECT
MONTH(sales_date) AS sales_month,
ROUND(SUM(quantity_sold * profit_per_unit), 2) AS total_profit
FROM profit_per_sale
GROUP BY MONTH(sales_date);
Raw
BCG SQL - 3.2.5 Slide 22.sql
# UI3.2.5AdvancedSQL - Hands-on Practice 1 (Slide 22)
# Code from Davina Leong
USE sql_101;
/*
View syntax
CREATE VIEW bio_milk AS
SELECT *
FROM products
WHERE product_category = 'Milk'
AND product_name LIKE '%bio%';
*/
/*
1. Create a view called `high_sales_days` from the sales
table where quantity sold is greater than 2000.
*/
CREATE VIEW high_sales_days AS
SELECT * FROM sales
WHERE quantity_sold > 2000;
SELECT * FROM sql_101.high_sales_days; -- Check if view data is accurate
/*
2. Create a view called `bio_managers` by finding the
manager ID, first name and last name of managers who
have sold products with `bio` component.
(HINT: Need to join all 3 tables).
*/
CREATE VIEW bio_managers AS
SELECT m.sales_manager_id, m.first_name, m.last_name
FROM managers m
LEFT JOIN sales s ON m.sales_manager_id = s.sales_manager_id
LEFT JOIN products p ON s.product_id = p.product_id
WHERE p.product_name LIKE "%bio";
SELECT * FROM sql_101.bio_managers; -- Check if the view was created correctly
/*
3. Create a table called `customers` with the following
constraints: first_name VARCHAR(64)
last_name VARCHAR(64)
date_of_birth DATE
location VARCHAR(20), default = 'SG'
customer_id INT
All fields are not null, and primary key is customer_id
*/
CREATE TABLE customers (
customer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(64) NOT NULL,
last_name VARCHAR(64) NOT NULL,
date_of_birth DATE NOT NULL,
location VARCHAR(20) NOT NULL DEFAULT "SG",
PRIMARY KEY (customer_id)
);
DESC customers; -- Check if the table was created correctly
/*
4. Insert one dummy record into the customers table (feel
free to use mock data that matches the constraints)
and do a SELECT * to check whether it worked.
*/
INSERT INTO customers (first_name, last_name, date_of_birth)
VALUES ("John", "Doe", "1990-01-01");
SELECT * FROM customers; -- Check if the customer was inserted
/*
5. Update the managers table by setting the first name
from NULL to 'Peter' for the manager with last name
'Nillkin'. (HINT: To override safe mode, run the line
SET SQL_SAFE_UPDATES = 0; above your query).
*/
# Add primary keys to Managers, Products, and Sales table
-- Just need to execute these commands once
ALTER TABLE managers
ADD PRIMARY KEY (sales_manager_id);
ALTER TABLE products
ADD PRIMARY KEY (product_id);
ALTER TABLE sales
ADD PRIMARY KEY (sale_id);
-- End of once-only execution
UPDATE managers SET first_name = "Peter"
WHERE first_name IS NULL
AND last_name
LIKE "Nillkin"
AND sales_manager_id > 0;
SELECT * FROM managers
WHERE first_name LIKE "Peter"; -- Check if the data was updated
Raw
BCG SQL - 3.2.5 Slide 4.sql
# UI3.2.5AdvancedSQL - Recap Practice for Intermediate SQL (Slide 4)
# Code from Davina Leong
USE sql_101;
/*
1. Find the minimum, maximum, and average values (all
rounded to 2 decimal places) of the unit list price in
the products table.
*/
SELECT ROUND(MIN(unit_list_price), 2) AS min_unit_list_price,
ROUND(MAX(unit_list_price), 2) AS max_unit_list_price,
ROUND(AVG(unit_list_price), 2) AS avg_unit_list_price
FROM sql_101.products;
/*
2. Find the manager ID along with a new column that
shows the manager's age (in years, rounded to nearest
integer) as of today (from the managers table).
(HINT: Use DATEDIFF and CURDATE).
*/
SELECT sales_manager_id, ROUND(DATEDIFF(CURDATE(), date_of_birth) / 365.25, 2) AS age
FROM sql_101.managers;
/*
3. Find the sales ID, sales date, product name, and
manager last name for sales with quantity sold between
1800 and 2100.
*/
SELECT s.sales_id, s.sales_date, p.product_name, m.last_name
FROM sql_101.sales AS s
LEFT JOIN sql_101.managers m ON s.sales_manager_id = m.sales_manager_id
LEFT JOIN sql_101.products p ON s.product_id = p.product_id
WHERE s.quantity_sold BETWEEN 1800 AND 2100;
Raw
BCG SQL - 3.2.5 Slide 43.sql
# UI3.2.5AdvancedSQL - Hands-on Practice 2 (Slide 43)
# Code from Davina Leong
USE sql_101;
/* Notes:
1) Stored Procedures Syntax
DELIMITER $$
CREATE PROCEDURE get_products(IN category VARCHAR(30), IN price_above DOUBLE)
BEGIN
SELECT * FROM products
WHERE product_category = category
AND unit_list_price > price_above;
END $$
DELIMITER ;
2) Start with the inner SELECT query first, then think about making it into a function.
*/
/*
1. Write a basic stored procedure that finds the ID and name of
products which are not in the 'Yoghurt' category.
*/
# DESC products; -- check the description of the products table
DROP PROCEDURE IF EXISTS get_non_yoghurt_product; -- Remove the procedure so that I don't have to keep removing it manually while testing
DELIMITER $$
CREATE PROCEDURE get_non_yoghurt_product(IN p_category VARCHAR(20))
BEGIN
SELECT product_id, product_name FROM products
WHERE product_category NOT LIKE p_category;
END $$
DELIMITER ;
/*
2. Execute the stored procedure you created above.
*/
CALL get_non_yoghurt_product("%Yoghurt%");
/*
3. Write a stored procedure (with 1 parameter) that finds the total
quantity sold for all products based on the parameter: month of the
year (as numeric).
*/
SELECT * FROM sales; -- To check what data are in the products table
DROP PROCEDURE IF EXISTS get_total_quantity_sold;
DELIMITER $$
CREATE PROCEDURE get_total_quantity_sold(IN month_of_the_year INT)
BEGIN
SELECT SUM(quantity_sold) AS total_quantity_sold
FROM sales s
WHERE MONTH(sales_date) = month_of_the_year;
END $$
DELIMITER ;
CALL get_total_quantity_sold(1); -- Note to self: it's month, not year
/*
4. Find the category and revenue of the product with the highest total
revenue (i.e., sum of quantity sold * list price).
Total Revenue for each product = quantity_sold × list_price
*/
# SELECT *, (unit_list_price * quantity_sold) AS revenue FROM products, sales; -- To see the data in the products and sales tables
DROP PROCEDURE IF EXISTS get_highest_total_revenue;
DELIMITER $$
CREATE PROCEDURE get_highest_total_revenue()
BEGIN
SELECT p.product_category, (unit_list_price * quantity_sold) AS total_revenue
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
ORDER BY total_revenue DESC
LIMIT 1;
END $$
DELIMITER ;
CALL get_highest_total_revenue();
/*
5. Find the product name, sales month, and sum of quantity sold
where the average monthly total quantity sold is greater than
20,000.
*/
# SELECT * FROM products, sales;
DROP PROCEDURE IF EXISTS get_total_quantity_sold;
DELIMITER $$
CREATE PROCEDURE get_total_quantity_sold(IN monthly_quantity_sold INT)
BEGIN
SELECT p.product_name, MONTH(s.sales_date) AS sales_month, SUM(s.quantity_sold) AS total_quantity_sold
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_id, sales_month
HAVING total_quantity_sold > monthly_quantity_sold;
END $$
DELIMITER ;
CALL get_total_quantity_sold(20000);
/*
6. Find the full name (as a single column) of the manager who sold the
highest quantity of products in March 2021.
*/
DROP PROCEDURE IF EXISTS get_top_manager_march_2021;
DELIMITER $$
CREATE PROCEDURE get_top_manager_march_2021(IN sales_date DATE)
BEGIN
SELECT
CONCAT(m.first_name, " ", m.last_name) AS top_manager,
s.quantity_sold,
s.sales_date
FROM managers m
LEFT JOIN sales s ON m.sales_manager_id = s.sales_manager_id
WHERE s.sales_date > sales_date
ORDER BY s.quantity_sold DESC
LIMIT 1;
END $$
DELIMITER ;
CALL get_top_manager_march_2021("2021-03-01");
/*
7. Find the top 2 products with the highest profit sum in the first half
of the year (i.e., before 1 July 2021).
Profit = (Selling Price − Cost Price) × Quantity Sold
*/
DROP PROCEDURE IF EXISTS get_top_two_products;
DELIMITER $$
CREATE PROCEDURE get_top_two_products(IN sales_date DATE)
BEGIN
SELECT p.product_name,
ROUND(((p.unit_list_price - p.unit_production_cost) * s.quantity_sold), 2) AS profit
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
WHERE s.sales_date < sales_date
ORDER BY profit DESC
LIMIT 2;
END $$
DELIMITER ;
CALL get_top_two_products("2021-06-01");
/*
8. Create a view that displays the total revenue earned
(i.e., list price * quantity) in the month of March 2021.
View Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
*/
DROP PROCEDURE IF EXISTS create_march_revenue_view;
DELIMITER $$
CREATE PROCEDURE create_march_revenue_view(IN sales_month INT)
BEGIN
DROP VIEW IF EXISTS total_march_revenue_view;
CREATE VIEW total_march_revenue_view AS
SELECT ROUND(SUM(p.unit_list_price * s.quantity_sold), 2) AS total_revenue
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
WHERE MONTH(s.sales_date) >= sales_month;
SELECT * FROM sql_101.total_march_revenue_view;
END $$
DELIMITER ;
CALL create_march_revenue_view(3);

Comments are disabled for this gist.