MySQL_DQL_Task.md

Northwind Database Queries

Description

This project is a collection of SQL queries that answer questions about the Northwind database using MySQL8 DQL commands.

Connect to the Database

Parameter	Value
Host	localhost
Port	3306
Database	northwind
User	root
Password	password

Instructions

• Read the task description and write the SQL query that answers the question.
• Put your query in sql file e.g. mahmoud-kassem.sql each task should have a comment with the task number.

-- Task 1.0
SELECT * FROM table_name;

-- Task 1.1

• Run the query in the MySQL command-line client or MySQL Workbench.
• Compare the output of the query with the expected output.
• If the output matches the expected output, the query is correct.
• If the output does not match the expected output, revise the query and try again.
• Repeat the process until the output matches the expected output.
• Some tasks are pre-solved to help you understand the problem and the expected output.

Single Table Queries

Task 1.0

Query: Get all categories ordered by CategoryName.

SELECT category_name
FROM categories
ORDER BY category_name;

Expected output:

category_name
Beverages
Condiments
Confections
Dairy Products
Grains/Cereals
Meat/Poultry
Produce
Seafood

Task 1.1

Query: Get top 10 products by units in stock.

• Select the product_name and units_in_stock.

-- write a query to generate the expected output

Expected output:

product_name	units_in_stock
Rhonbrau Klosterbier	125
Boston Crab Meat	123
Grandma's Boysenberry Spread	120
Pate chinois	115
Sirop d'erable	113
Geitost	112
Inlagd Sill	112
Sasquatch Ale	111
Gustaf's Knackebrod	104
Rod Kaviar	101

Task 1.2

Query: Get all products that have a unit price between 10 and 20.

• Select the product_name and unit_price.

-- write a query to generate the expected output

Expected output:

product_name	unit_price
Pavlova	17
Steeleye Stout	18
Inlagd Sill	19
Chartreuse verte	18
Boston Crab Meat	18
Gula Malacca	19
Maxilaku	20
Valkoinen suklaa	16
Ravioli Angelo	20
Louisiana Hot Spiced Okra	17
Outback Lager	15
Rod Kaviar	15
Lakkalikoori	18
Chai	18
Chang	19

Task 1.3

Query: Get top 5 products that have the highest unit price sorted by unit price in descending order.

• Select the product_name and unit_price.

-- write a query to generate the expected output

Expected output:

product_name	unit_price
Cote de Blaye	264
Thuringer Rostbratwurst	124
Mishi Kobe Niku	97
Sir Rodney's Marmalade	81
Carnarvon Tigers	63

Task 1.4

Query: Get all employees that are not managers.

• Select the first_name, last_name, and title.

-- write a query to generate the expected output

Expected output:

first_name	last_name
Andrew	Fuller

Task 1.5

Query: Get all products that need to be reordered and are not discontinued then sort by product_id.

• Select the product_name.

-- write a query to generate the expected output

Expected output:

product_name
Aniseed Syrup
Queso Cabrales
Sir Rodney's Scones
Gorgonzola Telino
Mascarpone Fabioli
Gravad lax
Ipoh Coffee
Rogede sild
Chocolade
Maxilaku
Gnocchi di nonna Alice
Wimmers gute Semmelknodel
Louisiana Hot Spiced Okra
Scottish Longbreads
Outback Lager
Longlife Tofu

Task 1.6

Query: Get the total number of orders.

• Select the count of orders.

SELECT COUNT(order_id) AS total_orders
FROM orders;

Expected output:

total_orders
830

Task 1.7

Query: Get top 5 customers with highest orders then sort by order count in descending order and customer_id in ascending order.

• Select the customer_id and the count of orders (order_count) for each customer.

-- write a query to generate the expected output

Expected output:

customer_id	order_count
SAVEA	31
ERNSH	30
QUICK	28
FOLKO	19
HUNGO	19

Task 1.8

Query: Get all customers that have the same postal code then sort by postal code and customer_id.

• Select the customer_id, company_name, and postal_code.
• Order by postal_code and customer_id.

-- write a query to generate the expected output

Expected output:

customer_id	company_name	postal_code
PERIC	Pericles Comidas clasicas	05033
TORTU	Tortuga Restaurante	05033
CACTU	Cactus Comidas para llevar	1010
OCEAN	Oceano Atlantico Ltda.	1010
RANCH	Rancho grande	1010
DUMON	Du monde entier	44000
FRANR	France restauration	44000

Task 1.9

Query: Get the shipping address that has the most orders.

• Select the ship_address and the count of orders (order_count) of the shipping address.

-- write a query to generate the expected output

Expected output:

ship_address	order_count
187 Suffolk Ln.	31

Multi-table Queries

Task 2.0

Query: Get all products that have a unit price greater than 100 and are in the category of Beverages.

• Select the product_name, unit_price, and category_name.
• Tables: products, categories.

SELECT p.product_name, p.unit_price, c.category_name
FROM products p
JOIN categories c
ON p.category_id = c.category_id
WHERE unit_price >= 100;

Expected output:

product_name	unit_price	category_name
Cote de Blaye	264	Beverages
Thuringer Rostbratwurst	124	Meat/Poultry

Task 2.1

Query: How many products are in each category?

• Select the category_name and the count of products in each category (product_count) then group by category_name.

-- write a query to generate the expected output
-- Hint: Use the COUNT() function to count the number of products in each category

Expected output:

category_name	product_count
Beverages	12
Condiments	12
Confections	13
Dairy Products	10
Grains/Cereals	7
Meat/Poultry	6
Produce	5
Seafood	12

Task 2.2

Query: Get all products and their categories then sort by product_id.

• Select the product_name and category_name.
• Tables: products, categories.
• Order by product_id.

-- write a query to generate the expected output
-- Hint: Use the JOIN clause to combine the products and categories tables

Expected output:

product_name	category_name
Aniseed Syrup	Condiments
Chef Anton's Cajun Seasoning	Condiments
Grandma's Boysenberry Spread	Condiments
Uncle Bob's Organic Dried Pears	Produce
Northwoods Cranberry Sauce	Condiments
Ikura	Seafood
Queso Cabrales	Dairy Products
Queso Manchego La Pastora	Dairy Products
Konbu	Seafood
Tofu	Produce
Genen Shouyu	Condiments
Pavlova	Confections
Carnarvon Tigers	Seafood
Teatime Chocolate Biscuits	Confections
Sir Rodney's Marmalade	Confections
Sir Rodney's Scones	Confections
Gustaf's Knackebrod	Grains/Cereals
Tunnbrod	Grains/Cereals
NuNuCa NuB-Nougat-Creme	Confections
Gumbar Gummibarchen	Confections
Schoggi Schokolade	Confections
Nord-Ost Matjeshering	Seafood
Gorgonzola Telino	Dairy Products
Mascarpone Fabioli	Dairy Products
Geitost	Dairy Products
Sasquatch Ale	Beverages
Steeleye Stout	Beverages
Inlagd Sill	Seafood
Gravad lax	Seafood
Cote de Blaye	Beverages
Chartreuse verte	Beverages
Boston Crab Meat	Seafood
Jack's New England Clam Chowder	Seafood
Ipoh Coffee	Beverages
Gula Malacca	Condiments
Rogede sild	Seafood
Spegesild	Seafood
Zaanse koeken	Confections
Chocolade	Confections
Maxilaku	Confections
Valkoinen suklaa	Confections
Manjimup Dried Apples	Produce
Filo Mix	Grains/Cereals
Tourtiere	Meat/Poultry
Pate chinois	Meat/Poultry
Gnocchi di nonna Alice	Grains/Cereals
Ravioli Angelo	Grains/Cereals
Escargots de Bourgogne	Seafood
Raclette Courdavault	Dairy Products
Camembert Pierrot	Dairy Products
Sirop d'erable	Condiments
Tarte au sucre	Confections
Vegie-spread	Condiments
Wimmers gute Semmelknodel	Grains/Cereals
Louisiana Fiery Hot Pepper Sauce	Condiments
Louisiana Hot Spiced Okra	Condiments
Laughing Lumberjack Lager	Beverages
Scottish Longbreads	Confections
Gudbrandsdalsost	Dairy Products
Outback Lager	Beverages
Flotemysost	Dairy Products
Mozzarella di Giovanni	Dairy Products
Rod Kaviar	Seafood
Longlife Tofu	Produce
Rhonbrau Klosterbier	Beverages
Lakkalikoori	Beverages
Original Frankfurter grune SoBe	Condiments
Chai	Beverages
Chang	Beverages
Chef Anton's Gumbo Mix	Condiments
Mishi Kobe Niku	Meat/Poultry
Alice Mutton	Meat/Poultry
Guarana Fantastica	Beverages
Rossle Sauerkraut	Produce
Thuringer Rostbratwurst	Meat/Poultry
Singaporean Hokkien Fried Mee	Grains/Cereals
Perth Pasties	Meat/Poultry

Task 2.3

Query: Get all employees and their territories then sort by region, territory, last name, and first name of the employee.

• Select the region_description, territory_description, last_name, and first_name.
• Tables: employees, employees_territories, territories, regions.
• Order by region_description, territory_description, last_name, and first_name.

SELECT DISTINCT r.region_description, t.territory_description, e.last_name, e.first_name
FROM employees e
-- replace with employees_territories join
-- replace with territories join
-- replace with regions join
ORDER BY r.region_description, t.territory_description, e.last_name, e.first_name;

Expected output:

region_description	territory_description	last_name	first_name
Eastern	Bedford	Fuller	Andrew
Eastern	Boston	Fuller	Andrew
Eastern	Braintree	Fuller	Andrew
Eastern	Cambridge	Fuller	Andrew
Eastern	Cary	Peacock	Margaret
Eastern	Edison	Buchanan	Steven
Eastern	Fairport	Buchanan	Steven
Eastern	Georgetow	Fuller	Andrew
Eastern	Greensboro	Peacock	Margaret
Eastern	Louisville	Fuller	Andrew
Eastern	Mellvile	Buchanan	Steven
Eastern	Morristown	Buchanan	Steven
Eastern	New York	Buchanan	Steven
Eastern	Neward	Davolio	Nancy
Eastern	Providence	Buchanan	Steven
Eastern	Rockville	Peacock	Margaret
Eastern	Westboro	Fuller	Andrew
Eastern	Wilton	Davolio	Nancy
Northern	Beachwood	Callahan	Laura
Northern	Bloomfield Hills	Dodsworth	Anne
Northern	Findlay	Callahan	Laura
Northern	Hollis	Dodsworth	Anne
Northern	Minneapolis	Dodsworth	Anne
Northern	Philadelphia	Callahan	Laura
Northern	Portsmouth	Dodsworth	Anne
Northern	Racine	Callahan	Laura
Northern	Roseville	Dodsworth	Anne
Northern	Southfield	Dodsworth	Anne
Northern	Troy	Dodsworth	Anne
Southern	Atlanta	Leverling	Janet
Southern	Orlando	Leverling	Janet
Southern	Savannah	Leverling	Janet
Southern	Tampa	Leverling	Janet
Western	Bellevue	Suyama	Michael
Western	Campbell	King	Robert
Western	Chicago	King	Robert
Western	Colorado Springs	King	Robert
Western	Denver	King	Robert
Western	Hoffman Estates	King	Robert
Western	Menlo Park	King	Robert
Western	Phoenix	Suyama	Michael
Western	Redmond	Suyama	Michael
Western	San Francisco	King	Robert
Western	Santa Clara	King	Robert
Western	Santa Cruz	King	Robert
Western	Santa Monica	King	Robert
Western	Scottsdale	Suyama	Michael
Western	Seattle	Suyama	Michael

Task 2.4

Query: Get all states and their customers if they have company name in their data then sort by state name.

• Select the state_name, state_abbr, and company_name.
• Tables: us_states, customers.
• Order by state_name.

-- write a query to generate the expected output
-- Hint: Use the IS NOT NULL operator to filter out customers with company name

Expected output:

state_name	state_abbr	company_name
Alaska	AK	Old World Delicatessen
California	CA	Let's Stop N Shop
Idaho	ID	Save-a-lot Markets
Montana	MT	The Cracker Box
New Mexico	NM	Rattlesnake Canyon Grocery
Oregon	OR	Great Lakes Food Market
Oregon	OR	Hungry Coyote Import Store
Oregon	OR	Lonesome Pine Restaurant
Oregon	OR	The Big Cheese
Washington	WA	Lazy K Kountry Store
Washington	WA	Trail's Head Gourmet Provisioners
Washington	WA	White Clover Markets
Wyoming	WY	Split Rail Beer & Ale

Task 2.5

Query: Get all suppliers that are not customers then sort by country and city.

• Select the contact_name, country, city, and postal_code.
• Tables: suppliers, customers.
• Order by country and city.

-- write a query to generate the expected output
-- Hint: Use the EXCEPT operator to exclude suppliers that are customers

Expected output:

contact_name	country	city	postal_code
Ian Devling	Australia	Melbourne	3058
Wendy Mackenzie	Australia	Sydney	2042
Carlos Diaz	Brazil	Sao Paulo	5442
Jean-Guy Lauzon	Canada	Montreal	H1J 1C3
Chantal Goulet	Canada	Ste-Hyacinthe	J2S 7S8
Niels Petersen	Denmark	Lyngby	2800
Anne Heikkonen	Finland	Lappeenranta	53120
Eliane Noz	France	Annecy	74000
Marie Delamare	France	Montceau	71300
Guylene Nodier	France	Paris	75004
Petra Winkler	Germany	Berlin	10785
Sven Petersen	Germany	Cuxhaven	27478
Martin Bein	Germany	Frankfurt	60439
Elio Rossi	Italy	Ravenna	48100
Giovanni Giudici	Italy	Salerno	84100
Mayumi Ohno	Japan	Osaka	545
Yoshi Nagase	Japan	Tokyo	100
Dirk Luchte	Netherlands	Zaandam	9999 ZZ
Beate Vileid	Norway	Sandvika	1320
Chandra Leka	Singapore	Singapore	0512
Antonio del Valle Saavedra	Spain	Oviedo	33007
Lars Peterson	Sweden	Goteborg	S-345 67
Michael Bjorn	Sweden	Stockholm	S-123 45
Charlotte Cooper	UK	London	EC1 4SD
Peter Wilson	UK	Manchester	M14 GSD
Regina Murphy	USA	Ann Arbor	48104
Cheryl Saylor	USA	Bend	97101
Robb Merchant	USA	Boston	02134
Shelley Burke	USA	New Orleans	70117

Task 2.6

Query: Get all products that have been ordered by customers more than 40 times then sort by order count in descending order and product name.

• Select the product_name and the count of orders (order_count) for each product.
• Tables: products, order_details.
• Order by order_count in descending order and product_name.

-- write a query to generate the expected output
-- Hint: Use the COUNT() function to count the number of orders for each product
-- Hint: Use the GROUP BY clause to group the results by product_name
-- Hint: Use the HAVING clause to filter out products with less than 40 orders

Expected output:

product_name	order_count
Chartreuse verte	50
Gudbrandsdalsost	50
Louisiana Hot Spiced Okra	49
Mascarpone Fabioli	46
Mozzarella di Giovanni	46
Outback Lager	45
Thuringer Rostbratwurst	45
Valkoinen suklaa	42

Task 2.7

Query: Get top 5 employees with most orders in year 1998 sort by employee_id and order_id.

• Select the employee_id, last_name, first_name, and the count of orders (order_count) for each employee.
• Tables: employees, orders.
• Order by employee_id and order_id.

-- write a query to generate the expected output
-- Hint: Use the COUNT() function to count the number of orders for each employee

Expected output:

employee_id	last_name	first_name	order_count
4	Peacock	Margaret	44
1	Davolio	Nancy	42
2	Fuller	Andrew	39
3	Leverling	Janet	38
8	Callahan	Laura	31

Task 2.8 (Bonus)

Query: Get top region with most sales in year 1998 sort by region_id and order_id.

• Select the region_id, region_description, and the sum of order totals (total_sales) for each region.
• Tables: regions, territories, employees, orders, order_details.
• Order by total_sales in descending order, region_id, and order_id.

WITH sales_data AS (
    -- use a common table expression to calculate the total sales for each region
)
SELECT region_id, region_description, total_sales
FROM sales_data
ORDER BY total_sales DESC, region_id
LIMIT 1;

Expected output:

region_id	region_description	total_sales
1	Eastern	799299

End of tasks. Best of luck!