1) Print the states that have a customer, but no vendor.
2) Print a listing of all the states in the US along with a list of the customer names in each state.
HINT1: GROUP_CONCAT() is an aggregator in SQLite that makes a list of strings (text). https://sqlite.org/lang_aggfunc.html#groupconcat
HINT2: You may copy-paste the following subquery into your query to generate a tables of states:
VALUES ('AL'),('AK'),('AZ'),('AR'),('CA'),('CO'),('CT'),('DE'),('DC'),('FL'),('GA'),('HI'),('ID'),('IL'),('IN'),('IA'),('KS'),('KY'),('LA'),('ME'),('MD'),('MA'),('MI'),('MN'),('MS'),('MO'),('MT'),('NE'),('NV'),('NH'),('NJ'),('NM'),('NY'),('NC'),('ND'),('OH'),('OK'),('OR'),('PA'),('RI'),('SC'),('SD'),('TN'),('TX'),('UT'),('VT'),('VA'),('WA'),('WV'),('WI'),('WY')
3) Print all the pair of products whose names have the same number of characters. List each pair only once (ie., don't list both A,B and B,A).
HINT: SQLite has a LENGTH() function that can be applied to text.
https://sqlite.org/lang_corefunc.html#length
4) Print the orders that could have been fulfilled by just one vendor. I say "could have" because some products are available from multiple vendors. Just print the order numbers.
HINT: the correct solution has 397 rows.
5) Print the orders that must be fulfilled by exactly two vendors. In other words, it is not possible to fulfill the order using one vendor and it's not possible to fulfill the order using three or more different vendors. Please ignore the quantities in the order and just assume that all of a given product in an order is supplied by one vendor (for example, if someone orders two Trek 9000 Bikes, they must both come from the same vendor).
HINT: the correct solution has 36 rows.
6) Print a table of products and one wholesale price for each. If there are multiple vendors for a product, then there will be multiple wholesale prices available. Choose the highest wholesale price for Bikes, but choose the lowest wholesale price for all other categories of products. Please solve this using the CASE keyword.
7) Solve the problem above using the UNION keyword instead of CASE.
8) Let's say that we need to find two staff members to serve on a committee. However, there are two rules to obey. First, the employees working together cannot have the same first name. Second, their total salary must be less than $100k. Count the total number of possibilities. Count each pairing only once (ie., don't count both A,B and B,A).
9) Let's say you could travel back in time to any past semester and register for three courses. However, you want to choose courses where the average grade awarded turned out to be above 85. Is it possible? In other words, write a query to list the number of "easy" courses in each semester.
10) List the of students (first name and last name) who have the same major as another student from their home state.
The diagram below is missing some details, but it is meant to describe an art museum's collection of artwork and the times when that artwork has been displayed in the museum.
11) If we cannot add any more columns, which of the above columns should be primary keys in the Artist, Artwork, and Gallery tables?
12) If we do choose a primary key for each table using the columns above, what problems might we encounter with the Artist and Artwork tables?
13) What column(s) and foreign key(s) should be added to associate an Artist to each Artwork?
14) How should we complete the ArtworkDisplay table? What column(s), primary key, and foreign key(s) should be added? Museums actually own many more artworks than they can show at once, and they keep most of their collection in storage. A museum cycles its collection through its galleries over time. The ArtworkDisplay table is meant to store information about when each artwork was displayed (and where it was displayed).
15) How can you store information about what materials were used in the artwork? For example, the types of stone, paint, fabrics, paper, ink, pencil, etc. Notice that an artwork can have any number of materials.
16) How would you change your answer to question 13 to support collaborative artwork (where there can be many artists associated with a work)?
17) The notes column in the Artwork table should probably be optional. Let's say that these notes are written by different museum employees and we want to store the name of the note author as well as the note. What is the best way to store this? In other words we want the option to associate a <note, author> pair with each artwork, but it's optional. If there is a note listed, then we want to be sure there is also an author.
18) Can we think of any of the above tables as events or relationships?
19) Let's say we wanted to allow each museum visitor to vote for their favorite artwork. How would you store these votes in a way that allowed only one vote per visitor, and what query would you write to find the most popular artwork?
20) Let's say that we wanted to record a history of all the times when each artwork was restored/cleaned. What would you add the the schema to represent these events?