Question 2: SQL – SELECT queries (20 marks) 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 Rhea and are owned by the user “tutorials”. 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 from tutorials’ 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 ford 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. You should also include a description along with each query to explain how it will run.
Here are the SQL queries along with their descriptions and expected result tables for the View Ridge Gallery database:
SELECT W.Title, W.Description, A.FirstName, A.LastName
FROM tutorials.WORK W
JOIN tutorials.ARTIST A ON W.ArtistID = A.ArtistID
WHERE W.Description LIKE '%Signed%';
Description: This query retrieves the title and description of works of art that contain the word "Signed" in their description, along with the first and last names of the artists who created them. It uses a JOIN to combine data from the WORK and ARTIST tables based on the ArtistID.
Expected Result Table:
| Title | Description | FirstName | LastName |
|---|---|---|---|
| Artwork Title | Signed by the artist | John | Doe |
| Another Title | Limited edition, Signed | Jane | Smith |
SELECT A.Nationality, COUNT(*) AS NumberOfArtists
FROM tutorials.ARTIST A
GROUP BY A.Nationality
HAVING COUNT(*) > 1;
Description: This query counts the number of artists for each nationality and filters the results to show only those nationalities that have more than one artist. It uses GROUP BY to group the results by nationality and HAVING to filter the groups.
Expected Result Table:
| Nationality | NumberOfArtists |
|---|---|
| American | 5 |
| Canadian | 3 |
SELECT W.Medium, COUNT(*) AS NumberOfWorks
FROM tutorials.WORK W
GROUP BY W.Medium
ORDER BY NumberOfWorks DESC;
Description: This query counts the number of works of art for each medium and orders the results from highest to lowest count. It uses GROUP BY to aggregate the results by medium and ORDER BY to sort them.
Expected Result Table:
| Medium | NumberOfWorks |
|---|---|
| Oil | 10 |
| Acrylic | 8 |
| Sculpture | 5 |
SELECT C.FirstName || ' ' || C.LastName AS CustomerName,
A.FirstName || ' ' || A.LastName AS ArtistName
FROM tutorials.CUSTOMER C
JOIN tutorials.CUSTOMER_ARTIST CA ON C.CustomerID = CA.CustomerID
JOIN tutorials.ARTIST A ON CA.ArtistID = A.ArtistID
ORDER BY C.LastName, A.LastName;
Description: This query retrieves the names of customers and the names of artists they are interested in, ordering the results first by customer last name and then by artist last name. It uses JOINs to connect the CUSTOMER, CUSTOMER_ARTIST, and ARTIST tables.
Expected Result Table:
| CustomerName | ArtistName |
|---|---|
| Alice Johnson | John Doe |
| Bob Smith | Jane Smith |
SELECT C.FIRSTNAME || ' ' || C.LASTNAME AS FullName, C.EMAIL
FROM tutorials.CUSTOMER C
WHERE C.STREET IS NULL;
Description: This query retrieves the full names and email addresses of customers who do not have an address recorded (i.e., where the STREET field is NULL).
Expected Result Table:
| FullName | |
|---|---|
| John Doe | [email protected] |
| Jane Smith | [email protected] |
SELECT W.WorkID, W.Title, A.FirstName || ' ' || A.LastName AS ArtistName, T.SALESPRICE
FROM tutorials.WORK W
JOIN tutorials.ARTIST A ON W.ArtistID = A.ArtistID
JOIN tutorials.TRANS T ON T.WorkID = W.WorkID
WHERE T.SALESPRICE > (
SELECT AVG(T2.SALESPRICE)
FROM tutorials.TRANS T2
);
Description: This query retrieves the work ID, title, artist name, and sales price of works of art that sold for more than the average sales price. It uses a subquery to calculate the average sales price.
Expected Result Table:
| WorkID | Title | ArtistName | SALESPRICE |
|---|---|---|---|
| 1 | Artwork Title | John Doe | 1500 |
| 2 | Another Title | Jane Smith | 2000 |
SELECT C.FirstName || ' ' || C.LastName AS FullName
FROM tutorials.CUSTOMER C
LEFT JOIN tutorials.TRANS T ON C.CUSTOMERID = T.CUSTOMERID
WHERE T.CUSTOMERID IS NULL;
Description: This query retrieves the full names of customers who have not made any purchases. It uses a LEFT JOIN to include all customers and filters for those without corresponding transactions.
Expected Result Table:
| FullName |
|---|
| Alice Johnson |
| Bob Smith |
SELECT A.FirstName || ' ' || A.LastName AS ArtistName, COUNT(CA.CustomerID) AS NumberOfCustomers
FROM tutorials.ARTIST A
JOIN tutorials.CUSTOMER_ARTIST CA ON A.ArtistID = CA.ArtistID
GROUP BY A.FirstName, A.LastName
ORDER BY NumberOfCustomers DESC
FETCH FIRST 1 ROW ONLY;
Description: This query finds the artist with the highest number of interested customers by counting the number of customers associated with each artist and ordering the results. It uses GROUP BY and FETCH FIRST to limit the results to the top artist.
Expected Result Table:
| ArtistName | NumberOfCustomers |
|---|---|
| John Doe | 15 |
SELECT A.FIRSTNAME || ' ' || A.LASTNAME AS ArtistName,
SUM(T.SalesPrice) AS TotalSales
FROM tutorials.ARTIST A
JOIN tutorials.WORK W ON A.ArtistID = W.ArtistID
JOIN tutorials.TRANS T ON W.WorkId = T.WorkId
GROUP BY A.FIRSTNAME, A.LASTNAME
ORDER BY TotalSales DESC;
Description: This query calculates the total sales amount for each artist by summing the sales prices of their works. It groups the results by artist name and orders them in descending order of total sales.
Expected Result Table:
| ArtistName | TotalSales |
|---|---|
| John Doe | 50000 |
| Jane Smith | 30000 |
SELECT C.FirstName || ' ' || C.LastName AS CustomerName
FROM tutorials.CUSTOMER C
WHERE NOT EXISTS (
SELECT A.ArtistID
FROM tutorials.ARTIST A
WHERE A.Nationality = 'United States'
AND NOT EXISTS (
SELECT CA.CustomerID
FROM tutorials.CUSTOMER_ARTIST CA
WHERE CA.CustomerID = C.CustomerID AND CA.ArtistID = A.ArtistID
)
);
Description: This query retrieves the names of customers who have expressed interest in all artists from the United States. It uses a NOT EXISTS clause to ensure that for each artist from the U.S., there is a corresponding entry in the CUSTOMER_ARTIST table for the customer.
Expected Result Table:
| CustomerName |
|---|
| Alice Johnson |
| Bob Smith |
These queries cover a variety of SQL concepts, including JOINs, GROUP BY, HAVING, and subqueries, and they are structured to follow best practices for readability and efficiency.