Table of Contents

• 1  References
• 2  Imports
• 3  Load the database
• 4  Basic SQL Questions
  • 4.1  Qn1 Which shippers do we have?
  • 4.2  Qn2 Certain fields from Categories
  • 4.3  3. Sales Representatives
  • 4.4  4. Sales Representatives in the United mStates
  • 4.5  5. Orders placed by specific EmployeeID
  • 4.6  6. Suppliers and ContactTitles
  • 4.7  7. Products with “queso” in ProductName
  • 4.8  8. Orders shipping to France or Belgium
  • 4.9  9. Orders shipping to any country in Latin
  • 4.10  10. Employees, in order of age
  • 4.11  11. Showing only the Date with a
  • 4.12  12. Employees full name
  • 4.13  13. OrderDetails amount per line item
  • 4.14  14. How many customers?
  • 4.15  15. When was the first order?
  • 4.16  16. Countries where there are customers
  • 4.17  17. Contact titles for customers
  • 4.18  18. Products with associated supplier names
  • 4.19  19. Orders and the Shipper that was used
• 5  Time taken

References

This tutorial is based on the book "SQL Practice Problems"

• https://www.amazon.com/SQL-Practice-Problems-learn-doing-ebook/dp/B01N41VQFO

Imports

import time
time_start_notebook = time.time()

import numpy as np
import pandas as pd
import os

import sqlite3

%load_ext sql
%config SqlMagic.displaycon=False
%config SqlMagic.feedback=False  # no Done, rows affected
%config SqlMagic.autopandas=True

%load_ext watermark
%watermark -a "Bhishan Poudel" -d -v -m
print()
%watermark -iv

Author: Bhishan Poudel

Python implementation: CPython
Python version       : 3.7.7
IPython version      : 7.16.1

Compiler    : Clang 4.0.1 (tags/RELEASE_401/final)
OS          : Darwin
Release     : 19.6.0
Machine     : x86_64
Processor   : i386
CPU cores   : 4
Architecture: 64bit


autopep8: 1.5.4
numpy   : 1.17.5
pandas  : 1.0.5
json    : 2.0.9
sqlite3 : 2.6.0

# python module version
sqlite3.version

'2.6.0'

# sqlite database version 
sqlite3.sqlite_version

'3.32.3'

Load the database

!pwd

/Volumes/Media/github/Tutorials_and_Lessons/SQL/c02_Book_sql_practice_problems_57

!ls data

Northwind.png          Northwind_small.sqlite orders_data.sql
Northwind.sqlite       northwind.sql          suppliers_products.png

%sql sqlite:////Volumes/Media/github/Tutorials_and_Lessons/SQL/c02_Book_sql_practice_problems_57/data/Northwind.sqlite

%%sql

SELECT *
FROM sqlite_master
WHERE TYPE='table';

	type	name	tbl_name	rootpage	sql
0	table	Employee	Employee	2	CREATE TABLE "Employee" \n(\n "Id" INTEGER PR...
1	table	Category	Category	3	CREATE TABLE "Category" \n(\n "Id" INTEGER PR...
2	table	Customer	Customer	4	CREATE TABLE "Customer" \n(\n "Id" VARCHAR(80...
3	table	Shipper	Shipper	8	CREATE TABLE "Shipper" \n(\n "Id" INTEGER PRI...
4	table	Supplier	Supplier	9	CREATE TABLE "Supplier" \n(\n "Id" INTEGER PR...
5	table	Product	Product	12	CREATE TABLE "Product" \n(\n "Id" INTEGER PRI...
6	table	OrderDetail	OrderDetail	14	CREATE TABLE "OrderDetail" \n(\n "Id" VARCHAR...
7	table	CustomerCustomerDemo	CustomerCustomerDemo	16	CREATE TABLE "CustomerCustomerDemo" \n(\n "Id...
8	table	CustomerDemographic	CustomerDemographic	18	CREATE TABLE "CustomerDemographic" \n(\n "Id"...
9	table	Region	Region	21	CREATE TABLE "Region" \n(\n "Id" INTEGER PRIM...
10	table	Territory	Territory	22	CREATE TABLE "Territory" \n(\n "Id" VARCHAR(8...
11	table	EmployeeTerritory	EmployeeTerritory	24	CREATE TABLE "EmployeeTerritory" \n(\n "Id" V...
12	table	Order	Order	11	CREATE TABLE "Order" (\n"index" INTEGER,\n "O...
13	table	orders	orders	155	CREATE TABLE orders (\n orderid smallint NO...

%%sql

SELECT *
FROM Orders
LIMIT 3;

	orderid	customerid	employeeid	orderdate	requireddate	shippeddate	shipvia	freight	shipname	shipaddress	shipcity	shipregion	shippostalcode	shipcountry
0	10248	VINET	5	1996-07-04	1996-08-01	1996-07-16	3	32.380001	Vins et alcools Chevalier	59 rue de l'Abbaye	Reims	None	51100	France
1	10249	TOMSP	6	1996-07-05	1996-08-16	1996-07-10	1	11.610000	Toms Spezialitäten	Luisenstr. 48	Münster	None	44087	Germany
2	10250	HANAR	4	1996-07-08	1996-08-05	1996-07-12	2	65.830002	Hanari Carnes	Rua do Paço, 67	Rio de Janeiro	RJ	05454-876	Brazil

Basic SQL Questions

Qn1 Which shippers do we have?

We have a table called Shippers. Return all the fields from all the shippers

%%sql

SELECT *
FROM Shipper;

	Id	CompanyName	Phone
0	1	Speedy Express	(503) 555-9831
1	2	United Package	(503) 555-3199
2	3	Federal Shipping	(503) 555-9931

Qn2 Certain fields from Categories

We only want to see two columns, CategoryName and Description.

%%sql

SELECT CategoryName,
       Description
FROM Category;

	CategoryName	Description
0	Beverages	Soft drinks, coffees, teas, beers, and ales
1	Condiments	Sweet and savory sauces, relishes, spreads, an...
2	Confections	Desserts, candies, and sweet breads
3	Dairy Products	Cheeses
4	Grains/Cereals	Breads, crackers, pasta, and cereal
5	Meat/Poultry	Prepared meats
6	Produce	Dried fruit and bean curd
7	Seafood	Seaweed and fish

3. Sales Representatives

We’d like to see just the FirstName, LastName, and HireDate of all the employees with the Title of Sales Representative. Write a SQL statement that returns only those employees.

%%sql

SELECT FirstName,
       LastName,
       HireDate
FROM Employee
WHERE title ='Sales Representative;

(sqlite3.OperationalError) unrecognized token: "'Sales Representative;"
[SQL: SELECT FirstName, LastName, HireDate
FROM Employee
WHERE title ='Sales Representative;]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

4. Sales Representatives in the United mStates

Now we’d like to see the same columns as above, but only for those employees that both have the title of Sales Representative, and also are in the United States.

%%sql

SELECT *
FROM Employee
LIMIT 1;

	Id	LastName	FirstName	Title	TitleOfCourtesy	BirthDate	HireDate	Address	City	Region	PostalCode	Country	HomePhone	Extension	Photo	Notes	ReportsTo	PhotoPath
0	1	Davolio	Nancy	Sales Representative	Ms.	1948-12-08	1992-05-01	507 - 20th Ave. E. Apt. 2A	Seattle	WA	98122	USA	(206) 555-9857	5467	None	Education includes a BA in psychology from Col...	2	http://accweb/emmployees/davolio.bmp

%%sql

SELECT FirstName,
       LastName,
       HireDate
FROM Employee
WHERE title ='Sales Representative'
  AND Country='USA';

	FirstName	LastName	HireDate
0	Nancy	Davolio	1992-05-01
1	Janet	Leverling	1992-04-01
2	Margaret	Peacock	1993-05-03

5. Orders placed by specific EmployeeID

Show all the orders placed by a specific employee. The EmployeeID for this Employee (Steven Buchanan) is 5.

%%sql

SELECT orderid,
       orderdate
FROM Orders
WHERE employeeid = 5
LIMIT 2;

	orderid	orderdate
0	10248	1996-07-04
1	10254	1996-07-11

6. Suppliers and ContactTitles

In the Suppliers table, show the SupplierID, ContactName, and ContactTitle for those Suppliers whose ContactTitle is not Marketing Manager

%%sql

SELECT *
FROM Supplier
LIMIT 2;

	Id	CompanyName	ContactName	ContactTitle	Address	City	Region	PostalCode	Country	Phone	Fax	HomePage
0	1	Exotic Liquids	Charlotte Cooper	Purchasing Manager	49 Gilbert St.	London	None	EC1 4SD	UK	(171) 555-2222	None	None
1	2	New Orleans Cajun Delights	Shelley Burke	Order Administrator	P.O. Box 78934	New Orleans	LA	70117	USA	(100) 555-4822	None	#CAJUN.HTM#

%%sql

SELECT id,
       ContactName,
       ContactTitle
FROM Supplier
WHERE ContactTitle != 'Marketing Manager'
LIMIT 5;

	Id	ContactName	ContactTitle
0	1	Charlotte Cooper	Purchasing Manager
1	2	Shelley Burke	Order Administrator
2	3	Regina Murphy	Sales Representative
3	5	Antonio del Valle Saavedra	Export Administrator
4	6	Mayumi Ohno	Marketing Representative

7. Products with “queso” in ProductName

In the products table, we’d like to see the ProductID and ProductName for those products where the ProductName includes the string “queso”.

%%sql

SELECT *
FROM Product
LIMIT 2;

	Id	ProductName	SupplierId	CategoryId	QuantityPerUnit	UnitPrice	UnitsInStock	UnitsOnOrder	ReorderLevel	Discontinued
0	1	Chai	1	1	10 boxes x 20 bags	18	39	0	10	0
1	2	Chang	1	1	24 - 12 oz bottles	19	17	40	25	0

%%sql

SELECT id,
       ProductName
FROM Product
WHERE ProductName like "%queso%";

	Id	ProductName
0	11	Queso Cabrales
1	12	Queso Manchego La Pastora

8. Orders shipping to France or Belgium

Looking at the Orders table, there’s a field called ShipCountry. Write a query that shows the OrderID, CustomerID, and ShipCountry for the orders where the ShipCountry is either France or Belgium.

%%sql

SELECT *
FROM Orders
LIMIT 2;

	orderid	customerid	employeeid	orderdate	requireddate	shippeddate	shipvia	freight	shipname	shipaddress	shipcity	shipregion	shippostalcode	shipcountry
0	10248	VINET	5	1996-07-04	1996-08-01	1996-07-16	3	32.380001	Vins et alcools Chevalier	59 rue de l'Abbaye	Reims	None	51100	France
1	10249	TOMSP	6	1996-07-05	1996-08-16	1996-07-10	1	11.610000	Toms Spezialitäten	Luisenstr. 48	Münster	None	44087	Germany

%%sql

SELECT orderid,
       customerid,
       shipcountry
FROM Orders
WHERE shipcountry = 'France'
  OR shipcountry = 'Belgium'
LIMIT 5;

	orderid	customerid	shipcountry
0	10248	VINET	France
1	10251	VICTE	France
2	10252	SUPRD	Belgium
3	10265	BLONP	France
4	10274	VINET	France

9. Orders shipping to any country in Latin

America Now, instead of just wanting to return all the orders from France of Belgium, we want to show all the orders from any Latin American country. But we don’t have a list of Latin American countries in a table in the Northwind database. So, we’re going to just use this list of Latin American countries that happen to be in the Orders table: Brazil Mexico Argentina Venezuela It doesn’t make sense to use multiple Or statements anymore, it would get too convoluted. Use the In statement.

%%sql

SELECT orderid,
       customerid,
       shipcountry
FROM Orders
WHERE shipcountry IN ('Brazil',
                      'Mexico',
                      'Argentina',
                      'Venezuela')
LIMIT 5;

	orderid	customerid	shipcountry
0	10250	HANAR	Brazil
1	10253	HANAR	Brazil
2	10256	WELLI	Brazil
3	10257	HILAA	Venezuela
4	10259	CENTC	Mexico

10. Employees, in order of age

For all the employees in the Employees table, show the FirstName, LastName, Title, and BirthDate. Order the results by BirthDate, so we have the oldest employees first.

%%sql

SELECT FirstName,
       LastName,
       Title,
       BirthDate
FROM Employee
ORDER BY BirthDate ASC;

	FirstName	LastName	Title	BirthDate
0	Margaret	Peacock	Sales Representative	1937-09-19
1	Nancy	Davolio	Sales Representative	1948-12-08
2	Andrew	Fuller	Vice President, Sales	1952-02-19
3	Steven	Buchanan	Sales Manager	1955-03-04
4	Laura	Callahan	Inside Sales Coordinator	1958-01-09
5	Robert	King	Sales Representative	1960-05-29
6	Michael	Suyama	Sales Representative	1963-07-02
7	Janet	Leverling	Sales Representative	1963-08-30
8	Anne	Dodsworth	Sales Representative	1966-01-27

11. Showing only the Date with a

DateTime field In the output of the query above, showing the Employees in order of BirthDate, we see the time of the BirthDate field, which we don’t want. Show only the date portion of the BirthDate field.

%%sql
PRAGMA table_info(Employee);

	cid	name	type	notnull	dflt_value	pk
0	0	Id	INTEGER	0	None	1
1	1	LastName	VARCHAR(8000)	0	None	0
2	2	FirstName	VARCHAR(8000)	0	None	0
3	3	Title	VARCHAR(8000)	0	None	0
4	4	TitleOfCourtesy	VARCHAR(8000)	0	None	0
5	5	BirthDate	VARCHAR(8000)	0	None	0
6	6	HireDate	VARCHAR(8000)	0	None	0
7	7	Address	VARCHAR(8000)	0	None	0
8	8	City	VARCHAR(8000)	0	None	0
9	9	Region	VARCHAR(8000)	0	None	0
10	10	PostalCode	VARCHAR(8000)	0	None	0
11	11	Country	VARCHAR(8000)	0	None	0
12	12	HomePhone	VARCHAR(8000)	0	None	0
13	13	Extension	VARCHAR(8000)	0	None	0
14	14	Photo	BLOB	0	None	0
15	15	Notes	VARCHAR(8000)	0	None	0
16	16	ReportsTo	INTEGER	0	None	0
17	17	PhotoPath	VARCHAR(8000)	0	None	0

# here, employee birth date is varying character.
# i will make it date type.

%%sql

SELECT FirstName,
       LastName,
       Title,
       CAST (BirthDate AS date)
FROM Employee
ORDER BY BirthDate ASC;

	FirstName	LastName	Title	CAST (BirthDate AS date)
0	Margaret	Peacock	Sales Representative	1937
1	Nancy	Davolio	Sales Representative	1948
2	Andrew	Fuller	Vice President, Sales	1952
3	Steven	Buchanan	Sales Manager	1955
4	Laura	Callahan	Inside Sales Coordinator	1958
5	Robert	King	Sales Representative	1960
6	Michael	Suyama	Sales Representative	1963
7	Janet	Leverling	Sales Representative	1963
8	Anne	Dodsworth	Sales Representative	1966

12. Employees full name

Show the FirstName and LastName columns from the Employees table, and then create a new column called FullName, showing FirstName and LastName joined together in one column, with a space in between.

%%sql

SELECT FirstName,
       LastName,
       FirstName || ' ' || LastName AS FullName
FROM Employee;

	FirstName	LastName	FullName
0	Nancy	Davolio	Nancy Davolio
1	Andrew	Fuller	Andrew Fuller
2	Janet	Leverling	Janet Leverling
3	Margaret	Peacock	Margaret Peacock
4	Steven	Buchanan	Steven Buchanan
5	Michael	Suyama	Michael Suyama
6	Robert	King	Robert King
7	Laura	Callahan	Laura Callahan
8	Anne	Dodsworth	Anne Dodsworth

13. OrderDetails amount per line item

In the OrderDetails table, we have the fields UnitPrice and Quantity. Create a new field, TotalPrice, that multiplies these two together. We’ll ignore the Discount field for now. In addition, show the OrderID, ProductID, UnitPrice, and Quantity. Order by OrderID and ProductID.

%%sql

SELECT *
FROM OrderDetail
LIMIT 2;

	Id	OrderId	ProductId	UnitPrice	Quantity	Discount
0	10248/11	10248	11	14.0	12	0.0
1	10248/42	10248	42	9.8	10	0.0

%%sql

SELECT OrderID,
       ProductID,
       UnitPrice,
       Quantity,
       (UnitPrice * Quantity) AS TotalPrice
FROM OrderDetail
ORDER BY OrderID,
         ProductID
LIMIT 5;

	OrderId	ProductId	UnitPrice	Quantity	TotalPrice
0	10248	11	14.0	12	168.0
1	10248	42	9.8	10	98.0
2	10248	72	34.8	5	174.0
3	10249	14	18.6	9	167.4
4	10249	51	42.4	40	1696.0

14. How many customers?

How many customers do we have in the Customers table? Show one value only, and don’t rely on getting the recordcount at the end of a resultset.

%%sql

SELECT *
FROM Customer
LIMIT 2;

	Id	CompanyName	ContactName	ContactTitle	Address	City	Region	PostalCode	Country	Phone	Fax
0	ALFKI	Alfreds Futterkiste	Maria Anders	Sales Representative	Obere Str. 57	Berlin	None	12209	Germany	030-0074321	030-0076545
1	ANATR	Ana Trujillo Emparedados y helados	Ana Trujillo	Owner	Avda. de la Constitución 2222	México D.F.	None	05021	Mexico	(5) 555-4729	(5) 555-3745

%%sql

SELECT count(*)
FROM Customer;

	count(*)
0	91

15. When was the first order?

Show the date of the first order ever made in the Orders table.

%%sql

SELECT *
FROM Orders
LIMIT 2;

	orderid	customerid	employeeid	orderdate	requireddate	shippeddate	shipvia	freight	shipname	shipaddress	shipcity	shipregion	shippostalcode	shipcountry
0	10248	VINET	5	1996-07-04	1996-08-01	1996-07-16	3	32.380001	Vins et alcools Chevalier	59 rue de l'Abbaye	Reims	None	51100	France
1	10249	TOMSP	6	1996-07-05	1996-08-16	1996-07-10	1	11.610000	Toms Spezialitäten	Luisenstr. 48	Münster	None	44087	Germany

16. Countries where there are customers

Show a list of countries where the Northwind company has customers.

%%sql

SELECT DISTINCT country
FROM Customer;

	Country
0	Germany
1	Mexico
2	UK
3	Sweden
4	France
5	Spain
6	Canada
7	Argentina
8	Switzerland
9	Brazil
10	Austria
11	Italy
12	Portugal
13	USA
14	Venezuela
15	Ireland
16	Belgium
17	Norway
18	Denmark
19	Finland
20	Poland

17. Contact titles for customers

Show a list of all the different values in the Customers table for ContactTitles. Also include a count for each ContactTitle. This is similar in concept to the previous question “Countries where there are customers” , except we now want a count for each ContactTitle.

%%sql

SELECT ContactTitle,
       count(ContactTitle) AS TotalContactTitle
FROM Customer
GROUP BY ContactTitle
ORDER BY TotalContactTitle DESC;

	ContactTitle	TotalContactTitle
0	Sales Representative	17
1	Owner	17
2	Marketing Manager	12
3	Sales Manager	11
4	Accounting Manager	10
5	Sales Associate	7
6	Marketing Assistant	6
7	Sales Agent	5
8	Order Administrator	2
9	Assistant Sales Agent	2
10	Owner/Marketing Assistant	1
11	Assistant Sales Representative	1

18. Products with associated supplier names

We’d like to show, for each product, the associated Supplier. Show the ProductID, ProductName, and the CompanyName of the Supplier. Sort by ProductID. This question will introduce what may be a new concept, the Join clause in SQL. The Join clause is used to join two or more relational database tables together in a logical way. Here’s a data model of the relationship between Products and Suppliers.

%%sql

SELECT p.id AS ProductId,
       p.ProductName,
       s.CompanyName
FROM Product p
INNER JOIN Supplier s ON p.SupplierID = s.id
ORDER BY ProductID
LIMIT 2;

	ProductId	ProductName	CompanyName
0	1	Chai	Exotic Liquids
1	2	Chang	Exotic Liquids

19. Orders and the Shipper that was used

We’d like to show a list of the Orders that were made, including the Shipper that was used. Show the OrderID, OrderDate (date only), and CompanyName of the Shipper, and sort by OrderID.

In order to not show all the orders (there’s more than 800), show only those rows with an OrderID of less than 10300.

%%sql

SELECT *
FROM Shipper
LIMIT 2;

	Id	CompanyName	Phone
0	1	Speedy Express	(503) 555-9831
1	2	United Package	(503) 555-3199

%%sql

SELECT o.orderid,
       CAST (o.orderdate AS date) AS orderdate,
            s.companyname
FROM Orders o
INNER JOIN Shipper s ON o.shipvia = s.id
WHERE orderid < 10300
ORDER BY orderid
LIMIT 3;

	orderid	orderdate	CompanyName
0	10248	1996	Federal Shipping
1	10249	1996	Speedy Express
2	10250	1996	United Package

Time taken

time_taken = time.time() - time_start_notebook
h,m = divmod(time_taken,60*60)
print('Time taken: {:.0f} hr {:.0f} min {:.0f} secs'.format(h, *divmod(m,60)))

Time taken: 0 hr 0 min 3 secs