Question 2: SQL SELECT queries

This question is based on the View Ridge Gallery database you have been using in the labs. See the textbook for background to the case and the table structures.

The tables are:

ARTIST

CUSTOMER

WORK

TRANS

CUSTOMER_ARTIST_INT

You can use the dtoohey tables that we have been using. If you prefer, you can create your own copies of these tables under your own account to work with. If you do so, you should ensure you copy the same sample data and constraints as in the dtoohey tables.

Provide SQL AND result tables for the following queries. Paste the queries and the result tables from either your SSH client or SQL Developer into your assignment document. You can use a screen capture for the result tables, but NOT for the SQL.

Each question is worth 2 marks.

1. List the full details of any work of art, including the name of the artist who created the work, that have been described as Surrealist.
2. List the details of any work of art (including the name of the artist who created the work and the acquisition and asking price details) currently held in the gallery (i.e. works of art that have not been sold) with an asking price of > $400.
3. List the title of any work of art that has two (and only two) copies recorded in the database.
4. List the names of all deceased artists and the age they were when they die
5. List the name of each artist represented in the database and the number of works of art by that artist, ordered from lowest to highest number.
6. List the work ID, title and artist name of all the works of art that sold for more than the average price of all sales, and the price they sold for.
7. List the total amount of sales of works of art by the gallery for each year, and the number of works sold that year.
8. Which artist has had the most works of art sold, and how many of the artists works have been sold?
1. Calculate the total profit made on all the works of art that have been sold (the profit/loss on a work of art is the difference between the acquisition price and the sales price)
10. List the name of any customers who have an interest in all artists.