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:
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"
Your answers should require just one query.
1) List the recipe name for every recipe that includes an ingredient measured by a “clove”.
2) List all recipes that can be used as main courses and have at least 7 steps to create using the ingredients.
3) For the top five frequent purchasers (customers who have completed the most orders), print each customer’s ID, first name, and their number of orders.
4) How many orders has the customer named Suzanne Viescas placed so far?
5) What is the total revenue that the customer named Suzanne Viescas has brought through product sales?
6) List the entertainer’s ID and stage name for all entertainers who are from Seattle and performed at engagements that began after 7:00 pm (19:00).
7) List the entertainer’s ID and stage name for all entertainers who are deemed ultra successful. All ultra successful entertainers have a strong style (style strength greater than 2) and have websites.
Question 8 has been removed because it was not clear.
9) Which three entertainers have most female members? Print the EntertainerID and the number of female members.
10) What is the percentage of students with majors in English or Mathematics?
11) Which student enrolled in classes taught by faculty members who is from the same state as the student? Print the StudentID.
12) Print out the full name (both first and last name) of the bowler who has the highest total score, and his/her total score.
13) How many teams have different players with the same last name?
14) Which bowler has the highest game win rate? Print the name of bowler and his/her win rate.
15) Which two captains performed best (have highest total raw score) in this season? Print names and the total raw scores.
16) BowlingLeague: How many matches in total took place in Bolero Lanes?
17) BowlingLeague: List the matchID for the matches where the Sharks played on the Odd Lane.
18) SchoolScheduling: List the department name, first name, last name, and title of all department chairs.
19) SchoolScheduling: List the CategoryID, Category Description and Department Name of the categories associated with either Humanities or Social Sciences.
20) SchoolScheduling: List all the students names and their grade point averages (on a 100 point scale) from the classes they completed (did not withdraw or enroll without completing). HINT: keep in mind that each course has a different number of credits, so you should use a weighted average.