For this assignment, you will use the program "DB Browser for SQLite" (available at http://sqlitebrowser.org/).
I have posted five sample database files to the course website. These database files can be opened with that program:
I have also posted (and handed out) a "SQL cheat sheet" and a "Sqlite database diagrams" PDF.
For every question, we expect to see both your SQL code and the resulting data. Copy and paste both the SQL code and the results into a plain-text document, save it for your records, and copy and paste your answers into Canvas for electronic submission. You may not submit a word document or screenshots, just plain text.
Here is an example question that applies to the SalesOrders.sqlite database:
Question: What bikes cost more than $1000?
Answer:
SELECT ProductName, RetailPrice FROM Products
WHERE CategoryID=2 AND RetailPrice > 1000;
"Trek 9000 Mountain Bike" "1200"
"Eagle FS-3 Mountain Bike" "1800"
"GT RTS-2 Mountain Bike" "1650"
You may need more than one query to answer some questions, but please try to answer each question with one query (perhaps having subqueries). In the example above you could replace CategoryID=2 with a subquery like CategoryID=(SELECT CategoryID FROM Categories WHERE CategoryDescription="Bikes")
You may find it helpful to use the “Basic SQL Cheat Sheet” posted in the "Files" section of Canvas, and also the 7-step method described in Lecture 2.
1) Which Agent earns the highest salary?
2) How many Entertainers are in the database?
3) How many customers have a "musical perefence" for the Jazz style of music?
4) How many students live in the state of Oregon (OR)?
5) Which students are majoring in Accounting or Information Sciences? (Print the names only.)
6) How many people teach (or have taught) classes? (HINT: try "SELECT DISTINCT...")
7) How many different recipes are there?
8) Which ingredients have names that start with the letter "V"?
9) Which recipes have (non-empty) Notes? (Print the title only.)
10) What is the average value of all the orders sold?
HINT: There are two ways to do this:
Strategy1: First generate a table listing the total value of each order. Next, use that as a subquery in an outer query that computes the average as follows:
SELECT AVG(...) FROM (subquery) ...
Strategy2: Compute the total revenue and divide by the number of orders.
11) What was the total revenue for the store? I mean "gross revenue" which ignores the wholesale cost of the items.
12) How much revenue was generated by sales of bikes?