CBAD 393 Practice 1

Tip

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

Database Connection

Just as in the instructions document, we load some required packages and establish a connection to the Northwind SQLite database. Once this chunk is run, RStudio’s “Connections” pane will index the tables, making the database much easier to work with.

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

These queries are designed to help you practice some basic SELECT statements and begin to find your way around the Northwind database.

-- Fill in the "..." to SELECT the ProductName and UnitPrice from the products table. 
-- You can remove the LIMIT 5 to see the full output.
SELECT ProductName, UnitPrice
FROM Products
LIMIT 5

5 records

ProductName	UnitPrice
Chai	18.00
Chang	19.00
Aniseed Syrup	10.00
Chef Anton’s Cajun Seasoning	22.00
Chef Anton’s Gumbo Mix	21.35

-- Write a query that shows the LastName and Birthday from the table that contains this data.
SELECT LastName, Birthdate
FROM Employees

9 records

LastName	BirthDate
Davolio	1968-12-08
Fuller	1972-02-19
Leverling	1983-08-30
Peacock	1957-09-19
Buchanan	1975-03-04
Suyama	1983-07-02
King	1980-05-29
Callahan	1978-01-09
Dodsworth	1986-01-27

-- Use WHERE to edit your query above so that it only shows the person with the lastname Peacock.
-- Remember the instructions document contains lots of WHERE examples.
SELECT Lastname
FROM Employees
WHERE Lastname = "Peacock"

1 records

LastName
Peacock

-- Using the example of WHERE with LIKE from the instructions, select the names and prices of any products that start with "L".
SELECT ProductName, UnitPrice
FROM Products
WHERE ProductName LIKE 'L%';

5 records

ProductName	UnitPrice
Louisiana Fiery Hot Pepper Sauce	21.05
Louisiana Hot Spiced Okra	17.00
Laughing Lumberjack Lager	14.00
Longlife Tofu	10.00
Lakkalikööri	18.00

-- Modify your query above so that you only select products that start with "L" and have unitsinstock less than 10.
SELECT ProductName, UnitPrice, UnitsInStock
FROM Products
WHERE ProductName LIKE "L%"
AND UnitsInStock < 10

2 records

ProductName	UnitPrice	UnitsInStock
Louisiana Hot Spiced Okra	17	4
Longlife Tofu	10	4

-- SELECT the CustomerId, ContactName and Country FROM the customers table. 
-- Once you're happy with your query, remove the LIMIT 5 so you can see the whole output, then remove the "--" from the  output.var line below to "uncomment" the line and save the data as "myquery". 
SELECT CustomerId, ContactName, Country
FROM Customers

# Using the skeleton ggplot code below, see if you can graph your country data to see how many customers are in each country. 
# Remember you should have saved this data above as myquery. 
ggplot(data = myquery,
       aes(x = Country)) +
  geom_bar()

Note

Try making some of your own queries to explore the database further. Making your own “playful” custom queries is the best way to learn this content. The data is very interesting if you are curious.