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.
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.
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 aggregate the data and HAVING to apply the filter.
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 in descending order based on the count. It uses GROUP BY to group the results by medium.
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.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.
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 selects the full name and email of customers who do not have an address recorded (i.e., where the Street field is NULL).
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.
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 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.
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(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.
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 and orders the results in descending order. It uses SUM to aggregate sales prices and GROUP BY to group the results by artist.
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 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.
Note:
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.