CBAD 393 Practice 2

Author

Olivia Mitchell

Tip

If you have not carefully reviewed the 393_practice2_instructions.qmd document, do that before beginning this practice

#Same setup packages as last week.
library(tidyverse)
library(DBI)
library(RSQLite)

# Connect to the database
mydb <- dbConnect(RSQLite::SQLite(), "northwind.db")

# Remember that you can view the database schema by clicking on the northwind_schema.png file in your files list on the lower-right side of the screen.

#Use this command to view tables in a database
dbListTables(mydb)

 [1] "Categories"          "Customers"           "EmployeeTerritories"
 [4] "Employees"           "Order Details"       "Orders"             
 [7] "Products"            "Regions"             "Shippers"           
[10] "Suppliers"           "Territories"         "sqlite_sequence"

#Use this command to view fields in a table, for example products.
dbListFields(mydb, "products")

 [1] "ProductID"       "ProductName"     "SupplierID"      "CategoryID"     
 [5] "QuantityPerUnit" "UnitPrice"       "UnitsInStock"    "UnitsOnOrder"   
 [9] "ReorderLevel"    "Discontinued"

Practice 1 Begins Here

-- Write a query that joins the 'products' and 'categories' tables. 
-- Return the name of each category and the names of products
-- belonging to that category.
-- remove the "--" to uncomment line 35 and save your data as myquery when done.

SELECT Categories.CategoryName, Products.ProductName
FROM Products
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID
LIMIT 10

myquery1

   CategoryName                     ProductName
1     Beverages                            Chai
2     Beverages                           Chang
3    Condiments                   Aniseed Syrup
4    Condiments    Chef Anton's Cajun Seasoning
5    Condiments          Chef Anton's Gumbo Mix
6    Condiments    Grandma's Boysenberry Spread
7       Produce Uncle Bob's Organic Dried Pears
8    Condiments      Northwoods Cranberry Sauce
9  Meat/Poultry                 Mishi Kobe Niku
10      Seafood                           Ikura

ggplot(data = myquery1,
       mapping = aes(y = CategoryName)) +
  geom_bar()

-- Join the 'customers' and 'orders' tables to pull customer LastNames and OrderIDs. 
SELECT customers.CompanyName, orders.Freight
FROM customers
INNER JOIN orders
ON customers.CustomerID = orders.CustomerID
WHERE customers.CompanyName LIKE "%Bottom-Dollar Markets%"
OR customers.CompanyName LIKE "%Around the Horn%"
LIMIT 10

myquery2

             CompanyName Freight
1        Around the Horn   22.50
2        Around the Horn   23.75
3  Bottom-Dollar Markets   30.25
4  Bottom-Dollar Markets   26.25
5  Bottom-Dollar Markets   28.50
6  Bottom-Dollar Markets   42.50
7        Around the Horn   20.00
8  Bottom-Dollar Markets   30.00
9        Around the Horn   34.00
10       Around the Horn   32.25

ggplot(data = myquery2,
       aes(x = Freight, y = CompanyName)) +
  geom_boxplot()

--Join the Suppliers and Products tables so that you can select 
--both the Country for each Supplier and the names of the Products they supply.
SELECT Suppliers.Country, Products.ProductName, Products.Discontinued
FROM Suppliers
INNER JOIN Products
ON Suppliers.SupplierID = Products.SupplierID
LIMIT 10

myquery3

   Country                     ProductName Discontinued
1       UK                            Chai            0
2       UK                           Chang            0
3       UK                   Aniseed Syrup            0
4      USA    Chef Anton's Cajun Seasoning            0
5      USA          Chef Anton's Gumbo Mix            1
6      USA    Grandma's Boysenberry Spread            0
7      USA Uncle Bob's Organic Dried Pears            0
8      USA      Northwoods Cranberry Sauce            0
9    Japan                 Mishi Kobe Niku            1
10   Japan                           Ikura            0

ggplot(data = myquery3,
       aes(y = Country, fill = Discontinued)) +
  geom_bar()

-- Use this block to pull the data you need to create boxplots for each product category that show the "revenue" attributable to that product category, but only for orders with revenue less than $1000. You can create revenue using an alias, for example "SELECT (UnitPrice * Quantity) AS revenue". 
SELECT Categories.CategoryName,
       ("Order Details".UnitPrice * "Order Details".Quantity) AS revenue
FROM "Order Details"
INNER JOIN Products
ON "Order Details".ProductID = Products.ProductID
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID
WHERE revenue < 1000
LIMIT 10

myquery4

     CategoryName revenue
1  Dairy Products   168.0
2  Grains/Cereals    98.0
3  Dairy Products   174.0
4         Produce   167.4
5         Seafood    77.0
6      Condiments   252.0
7  Grains/Cereals   100.8
8  Grains/Cereals   234.0
9      Condiments   336.0
10 Dairy Products    50.0

ggplot(data = myquery4,
       aes(x = revenue, y = CategoryName)) +
  geom_boxplot()

-- We have had a product recall! 
-- Create a query that shows the contact name and phone number for customers whose orders shipped in December 2024.. 
SELECT customers.ContactName, customers.Phone
FROM customers
INNER JOIN orders
ON customers.CustomerID = orders.CustomerID
WHERE orders.ShippedDate LIKE "2024-12%"
LIMIT 10

Displaying records 1 - 10
ContactName	Phone
Rene Phillips	(907) 555-7584
Eduardo Saavedra	(93) 203 4560
Marie Bertrand	(1) 42.34.22.66
Mario Pontes	(21) 555-0091
Elizabeth Lincoln	(604) 555-4729
Lúcia Carvalho	(11) 555-1189
Guillermo Fernández	(5) 552-3745
Miguel Angel Paolino	(5) 555-2933
Sven Ottlieb	0241-039123
Thomas Hardy	(171) 555-7788

-- More information has been provided about the recall. 
-- Edit your call list to only include customers who purchased "Boston Crab Meat" in that same time frame. 
SELECT DISTINCT customers.ContactName, customers.Phone
FROM customers
INNER JOIN orders
ON customers.CustomerID = orders.CustomerID
INNER JOIN "Order Details"
ON orders.OrderID = "Order Details".OrderID
INNER JOIN products
ON "Order Details".ProductID = products.ProductID
WHERE orders.ShippedDate LIKE "2024-12%"
AND products.ProductName = "Boston Crab Meat"
LIMIT 10

Displaying records 1 - 10
ContactName	Phone
Rene Phillips	(907) 555-7584
Eduardo Saavedra	(93) 203 4560
Guillermo Fernández	(5) 552-3745
Sven Ottlieb	0241-039123
Sergio Gutiérrez	(1) 123-5555
Palle Ibsen	86 21 32 43
Renate Messner	069-0245984
Miguel Angel Paolino	(5) 555-2933
Yvonne Moncada	(1) 135-5333
Laurence Lebihan	91.24.45.40

More Practice

Now write some custom queries of your own using an INNER jOIN statement and graphing some output in some manner with ggplot.

SELECT Products.ProductName, "Order Details".Quantity
FROM Products
INNER JOIN "Order Details"
ON Products.ProductID = "Order Details".ProductID
LIMIT 10

myquery5

                        ProductName Quantity
1                    Queso Cabrales       12
2     Singaporean Hokkien Fried Mee       10
3            Mozzarella di Giovanni        5
4                              Tofu        9
5             Manjimup Dried Apples       40
6   Jack's New England Clam Chowder       10
7             Manjimup Dried Apples       35
8  Louisiana Fiery Hot Pepper Sauce       15
9               Gustaf's Knäckebröd        6
10                   Ravioli Angelo       15

ggplot(data = myquery5,
       aes(x = Quantity)) +
  geom_boxplot() +
  labs(title = "Boxplot Showing Quantities for Chocolate Products")