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 as in dtoohey's 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.
- List the details of all works of art (including the name of the artist who created the work) that are signed.
- List all the nationalities with more than one artist represented in the database, and the number of artists of that nationality.
- List the number of works in each medium, ordered from highest to lowest number.
- List the names of all the customers and the names of the artists each customer has an interest in, in alphabetical order of artist last name within customer last name.
- List the full name and email of any customers who do not have a complete address recorded.
- List the work ID, title and artist name of all the works of art that sold for more than the average sales price, and the price they sold for.
- List the full name of any customers who haven't bought any works of art.
- Which artist (give his/her full name) has the most customers interested in him or her, and how many customers are interested in them?
- List the total dollar amount of sales each artist (give his/her full name) has made on their works, in descending order of total.
- List the name of any customers who have an interest in all the artists from the United States. Your query should not need to include names of individual artists.