Here are the SQL queries along with their descriptions and expected result tables for the View Ridge Gallery database:
1. List the details of any work of art (including the name of the artist who created the work) that has ‘Signed’ in their description.
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 |
2. List all the nationalities with more than one artist represented in the database, and the number of artists of that nationality.
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 |
3. List the number of works in each medium, ordered from highest to lowest number.
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 |
4. 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.
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 |
5. List the full name and email of any customers who have no address recorded.
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 | Email |
|------------------|---------------------|
| John Doe | [email protected] |
| Jane Smith | [email protected] |
6. 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.
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 |
7. List the full name of any customers who haven’t bought any works of art.
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 |
8. Which artist (give his/her full name) has the most customers interested in him or her, and how many customers are interested in them?
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 |
9. List the total dollar amount of sales each artist (give his/her full name) has made on their works, in descending order of total.
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 |
10. List the name of any customers who have an interest in all the artists from the United States.
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.