here is the basic table
1:59 Lab DB schema.txt Artist ARTISTID NOT NULL NUMBER (38) LASTNAME NOT NULL CHAR(25) FIRSTNAME NOT NULL CHAR(25) NATIONALITY CHAR(30) DATEOFBIRTH NUMBER (4) DATEDECEASED NUMBER (4) Work WORKID TITLE COPY MEDIUM DESCRIPTION ARTISTID NOT NULL NUMBER(38) NOT NULL CHAR(35) NOT NULL CHAR(12) CHAR(35) VARCHAR2(1000) NOT NULL NUMBER ( 38 ) Customer CUSTOMERID LASTNAME FIRSTNAME STREET CITY STATE ZIPPOSTALCODE COUNTRY AREACODE PHONE NUMBER EMAIL NOT NULL NUMBER (38) NOT NULL CHAR(25) NOT NULL CHAR(25) CHAR(30) CHAR(35) CHAR(2) CHAR(9) CHAR(50) CHAR(3) CHAR(8) VARCHAR2(100) Trans TRANSACTIONID NOT NULL NUMBER ( 38 ) DATEACQUIRED ACQUISITIONPRICE NOT NULL NUMBER(8,2) DATESOLD DATE ASKINGPRICE NUMBER(8,2) SALESPRICE NUMBER(8.2) CUSTOMERID NUMBER(38) WORKID NOT NULL NUMBER(38) Customer Artist Int ARTISTID NOT NULL NUMBER(38) CUSTOMERID NOT NULL NUMBER(38) Question 2: SQL-SELECT queries (20 marks) 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. a. 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. b. 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 (ie. works of art that have not been sold) with an asking price of $400. c. List the title of any work of art that has two (and only two) copies recorded in the database. d. List the names of all deceased artists and the age they were when they died. e. 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. f. 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. g. List the total amount of sales of works of art by the gallery for each year, and the number of works sold that year. h. Which artist has had the most works of art sold, and how many of the artist's works have been sold? i. Calculate the total profit made on all the works of art that have been sold (the profil/loss on a work of art is the difference between the acquisition price and the sales price) j. List the name of any customers who have an interest in all artists
