Provide SQL queries and the result tables for the following (20 marks): Please ensure that you include the result table as well as your SQL; you can copy and paste this from either your ssh client or SQL Developer. Each query is worth 2 marks. These tables exist in arion and are owned by the user dtoohey. You may, if you wish, create your own copies of the tables under your own account. If you do so, you should ensure that you copy the sample data in dtoohey’s tables. These queries are based on the View Ridge Gallery database you have been using in the Lab sessions. Please see Chapters 6 and 7 of Kroenke for background to the case and table structures. Marks are allocated not only for correct answers, but also for best practice in the creation of the queries. List the details of any works of art (including the Artist who created the work) that have more than three copies recorded in the database (i.e., a work that is listed in the database three times). SELECT w.WorkID,w.Title,w.Copy,w.Medium,w.Description,w.ArtistID,a.LastName,a.Nationality,a.DateOfBirth, a.DateDeceased FROM dtoohey.work w JOIN dtoohey.ARTIST a ON w.ArtistID=a.ArtistID WHERE w.Copy > 3; List the details of any work of art (including the Artist who created the work) that has a Surrealist style. List the details of the works of art (including the Artist who created the work, and the acquisition and asking price details) currently held in the gallery (e., works of art that have not been sold). List the sales for each customer (e., when a customer purchases a work of art from the Gallery, a transaction line is created. For a purchase, there will be values in the DateSold and SalesPrice columns). The query should include the details of the customer, the transaction and the work of art purchased. List the names of the deceased artists and the number of years of age they were when they died (for example, an artist born in 1950 and deceased in 2001 has an age of 51). The sum of the acquisition price of works of art for each year (for example, if there were two works of art purchased for $1500 and $1000 in 2019, and one work of art purchased for $500 in 2020, then the sums would be $2500 and $500, for 2019 and 2020 respectively). Calculate the profit made on works of art that have been sold (e., the profit/loss on an individual work of art is the difference between the acquisition price and the sales price). Which artist has had the most works of art sold, and how many of the artist’s works have been sold? Sales of which artist’s works have resulted in the highest average profit (e., the average of the profits made on each sale of works by an artist), and what is that amount? Customer name of any customers who have an interest in ALL artists.

Provide SQL queries and the result tables for the following (20 marks): Please ensure that you include the result table as well as your SQL; you can copy and paste this from either your ssh client or SQL Developer. Each query is worth 2 marks. These tables exist in arion and are owned by the user dtoohey. You may, if you wish, create your own copies of the tables under your own account. If you do so, you should ensure that you copy the sample data in dtoohey’s tables. These queries are based on the View Ridge Gallery database you have been using in the Lab sessions. Please see Chapters 6 and 7 of Kroenke for background to the case and table structures. Marks are allocated not only for correct answers, but also for best practice in the creation of the queries. List the details of any works of art (including the Artist who created the work) that have more than three copies recorded in the database (i.e., a work that is listed in the database three times). SELECT w.WorkID,w.Title,w.Copy,w.Medium,w.Description,w.ArtistID,a.LastName,a.Nationality,a.DateOfBirth, a.DateDeceased FROM dtoohey.work w JOIN dtoohey.ARTIST a ON w.ArtistID=a.ArtistID WHERE w.Copy > 3; List the details of any work of art (including the Artist who created the work) that has a Surrealist style. List the details of the works of art (including the Artist who created the work, and the acquisition and asking price details) currently held in the gallery (e., works of art that have not been sold). List the sales for each customer (e., when a customer purchases a work of art from the Gallery, a transaction line is created. For a purchase, there will be values in the DateSold and SalesPrice columns). The query should include the details of the customer, the transaction and the work of art purchased. List the names of the deceased artists and the number of years of age they were when they died (for example, an artist born in 1950 and deceased in 2001 has an age of 51). The sum of the acquisition price of works of art for each year (for example, if there were two works of art purchased for $1500 and $1000 in 2019, and one work of art purchased for $500 in 2020, then the sums would be $2500 and $500, for 2019 and 2020 respectively). Calculate the profit made on works of art that have been sold (e., the profit/loss on an individual work of art is the difference between the acquisition price and the sales price). Which artist has had the most works of art sold, and how many of the artist’s works have been sold? Sales of which artist’s works have resulted in the highest average profit (e., the average of the profits made on each sale of works by an artist), and what is that amount? Customer name of any customers who have an interest in ALL artists.

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

Provide SQL queries and the result tables for the following (20 marks):

Please ensure that you include the result table as well as your SQL; you can copy and paste this from either your ssh client or SQL Developer. Each query is worth 2 marks. These tables exist in arion and are owned by the user dtoohey. You may, if you wish, create your own copies of the tables under your own account. If you do so, you should ensure that you copy the sample data in dtoohey’s tables.

These queries are based on the View Ridge Gallery database you have been using in the Lab sessions. Please see Chapters 6 and 7 of Kroenke for background to the case and table structures.

Marks are allocated not only for correct answers, but also for best practice in the creation of the queries.

List the details of any works of art (including the Artist who created the work) that have more than three copies recorded in the database (i.e., a work that is listed in the database three times).

SELECT w.WorkID,w.Title,w.Copy,w.Medium,w.Description,w.ArtistID,a.LastName,a.Nationality,a.DateOfBirth, a.DateDeceased

FROM dtoohey.work w JOIN dtoohey.ARTIST a ON w.ArtistID=a.ArtistID

WHERE w.Copy > 3;

List the details of any work of art (including the Artist who created the work) that has a Surrealist style.

List the details of the works of art (including the Artist who created the work, and the acquisition and asking price details) currently held in the gallery (e., works of art that have not been sold).
List the sales for each customer (e., when a customer purchases a work of art from the Gallery, a transaction line is created. For a purchase, there will be values in the DateSold and SalesPrice columns). The query should include the details of the customer, the transaction and the work of art purchased.
List the names of the deceased artists and the number of years of age they were when they died (for example, an artist born in 1950 and deceased in 2001 has an age of 51).
The sum of the acquisition price of works of art for each year (for example, if there were two works of art purchased for $1500 and $1000 in 2019, and one work of art purchased for $500 in 2020, then the sums would be $2500 and $500, for 2019 and 2020 respectively).
Calculate the profit made on works of art that have been sold (e., the profit/loss on an individual work of art is the difference between the acquisition price and the sales price).
Which artist has had the most works of art sold, and how many of the artist’s works have been sold?
Sales of which artist’s works have resulted in the highest average profit (e., the average of the profits made on each sale of works by an artist), and what is that amount?
Customer name of any customers who have an interest in ALL artists.