Gimme5 - SQL - Answers

Gimme5 - SQL

Using the database on W3 Schools

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

Q1: Click on the Products table

a. How many records are there?  77

b. Create some SQL that selects just the records for Supplierid = 7?
SELECT * FROM [Products]
WHERE supplierId = 7

(5 records)

Q2: Open the suppliers table

a. What is the name of the supplier with suplierid = 7?
SELECT SupplierName FROM [Suppliers]
where supplierid = 7
(Pavlova, Ltd.)

b. How many records have the word "son" in the contactname field?
SELECT * FROM [Suppliers]
where contactname like "%son%"
(2 records)

Q3: Open the orders table

a. How may records are in the orders table? (77)

b. What is the ID of the customer with the most orders? (HARD)
SELECT count(*), customerid FROM [Orders]
group by customerid
order by count(*)

(20)

Q4. Open the customers table

a. How many customers are in Mexico or Spain
SELECT * FROM [Customers]
WHERE Country in("Mexico", "SPAIN")
(5)

b. What is the name of the customer from Q3 b with the most orders
SELECT * FROM [Customers]
WHERE customerid = 20
(Ernst Handel)

Q5. Open the products table and find this product - Gustaf's Knäckebröd
SELECT * FROM [Products]
where productname = "Gustaf's Knäckebröd"

a. how much is it? (21)

b. what is its category code? ID = 5

c. look in the category table and find that code? (ok)

d. what type of product
(Grains/Cereals)