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 tasks you've provided. I've also made sure to follow best practices in SQL query writing.
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.
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 aggregate the data and HAVING to apply the filter.
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 in descending order based on the count. It uses GROUP BY to group the results by medium.
SELECT C.FirstName || ' ' || C.LastName AS CustomerName,
A.FirstName || ' ' || A.LastName AS ArtistName
FROM tutorials.CUSTOMER C
JOIN tutorials.TRANS T ON C.CustomerID = T.CustomerID
JOIN tutorials.WORK W ON T.WorkID = W.WorkID
JOIN tutorials.ARTIST A ON W.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 multiple JOINs to connect the relevant tables.
SELECT C.FirstName || ' ' || C.LastName AS FullName, C.Email
FROM tutorials.CUSTOMER C
WHERE C.Street IS NULL;
Description: This query selects the full name and email of customers who do not have an address recorded (i.e., where the Street field is NULL).
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.
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 lists 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 entries in the TRANS table.
SELECT A.FirstName || ' ' || A.LastName AS ArtistName,
COUNT(DISTINCT T.CustomerID) AS NumberOfCustomers
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.ArtistID, A.FirstName, A.LastName
ORDER BY NumberOfCustomers DESC
FETCH FIRST 1 ROW ONLY;
Description: This query identifies the artist with the most customers interested in their works. It counts distinct customers for each artist and orders the results to find the top artist.
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 and orders the results in descending order. It uses SUM to aggregate sales prices and GROUP BY to group the results by artist.
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 T.CustomerID
FROM tutorials.TRANS T
JOIN tutorials.WORK W ON T.WorkID = W.WorkID
WHERE W.ArtistID = A.ArtistID AND T.CustomerID = C.CustomerID
)
);
Description: This query finds customers who have an interest in all artists from the United States. It uses a nested NOT EXISTS clause to ensure that for every artist from the U.S., there is a corresponding purchase by the customer.
For each query, you would need to run them in your SQL environment to see the actual result tables, as I cannot execute SQL queries or access databases directly. Make sure to adjust the queries if your actual table structures or column names differ from those assumed here.