SQL Statements Final

1. Find the CustomerID with the Phone of 555-236-1095.

SELECT CustomerID FROM Customer WHERE Phone = '555-236-1095'

4. Display the average RetailPrice of all items that have been purchased.

SELECT AVG (RetailPrice) FROM purchase_item

9. Write an SQL outer join statement to display the ArtistLastName, ArtistFirstName, ItemNumber, and ItemDescription and RetailPrice of items that have been purchased and those that have not been sold. Sort by RetailPrice. How many items have not been sold?

SELECT ArtistLastName, ArtistFirstName, item.ItemNumber, ItemDescription, RetailPrice FROM item LEFT JOIN purchase_item ON item.ItemNumber = purchase_item.ItemNumber ORDER BY RetailPrice

10. Determine if there are any customers that have no invoices.

SELECT CUSTOMER.CustomerID, LastName, FirstName FROM CUSTOMER WHERE customer.CustomerID NOT IN (SELECT CustomerID FROM purchase)

3B. Change the statement to find the item that has the highest cost. Sort on ItemNumber descending.

SELECT Cost, ItemNumber, ItemDescription FROM item WHERE Cost IN (Select MAX(Cost) FROM item)

3A. Change the statement to count the number of items that cost $100 or more but less than $200.

SELECT Count(*) FROM item WHERE cost >= 100 AND cost < 200

2. Find the CustomerID with the Phone ending in 0392.

SELECT CustomerID FROM Customer WHERE Phone Like '%0392'

5. Display the item descriptions of the items purchased in InvoiceNumber 1005.

SELECT ItemDescription FROM item JOIN purchase_item ON item.ItemNumber = purchase_item.ItemNumber WHERE InvoiceNumber = 1005

3. List ItemNumber and ItemDescription for all items that cost $100 or more but less than $200.

SELECT ItemNumber, ItemDescription, Cost FROM item WHERE Cost >= 100 AND Cost < 200

6. List LastName and FirstName of customers who have made at least one purchase with PreTaxAmount greater than $200. Which of the following are not in the list?

SELECT LastName, FirstName FROM CUSTOMER WHERE CustomerID IN (SELECT CustomerID FROM PURCHASE WHERE PreTaxAmount > 200)

7. Display the customer Id and sum of the PreTaxAmount for each customer (some customers have more than one purchase and some may not). Order by the Sum from highest to lowest. Which customer has the highest sum of pretax amount purchases?

SELECT LastName, FirstName, customer.CustomerID, Sum(PreTaxAmount) FROM customer JOIN purchase ON customer.CustomerID = purchase.CustomerID GROUP BY customer.CustomerID ORDER BY Sum(PreTaxAmount) DESC

8. List ItemNumber, Cost, and RetailPrice for purchased items for the artist with the last name of Baker. How many items by Baker have been sold?

SELECT item.ItemNumber, Cost, RetailPrice FROM item JOIN purchase_item ON item.ItemNumber = purchase_item.ItemNumber WHERE ArtistLastName = 'Baker' ORDER BY item.ItemNumber