Assignment 05
1. List the customers who live on Main Street (in any city).
Results:
SQL SELECT [Link], [Link] FROM Customers WHERE ((([Link]) Like "*Main *"));
2. List the customers who have an account balance between $300 and $500, sorted by account balance in descending order.
Results:
SQL SELECT [Link], [Link] FROM Customers WHERE ((([Link])>=300 And ([Link])<=500));
3. What is the total amount of money owed us by customers who live in Miami?
Results:
SQL SELECT [Link], [Link], [Link] FROM Customers WHERE ((([Link])="Miami"));
4. How many orders have been placed by customers from Seattle after April?
Results:
SQL SELECT Count([Link]) AS CountOfODate, [Link] FROM Customers INNER JOIN Orders ON [Link] = [Link] GROUP BY [Link] HAVING (((Count([Link]))>4/1/2004) AND (([Link])="Seattle"));
5. What is the largest order ever placed (in terms of amount)? New query in design view menu
Results:
SQL view: SELECT Max([Link]) AS MaxOfAmount FROM Orders;
6. Which item (Item ID and Description) had the most sales in terms of quantity?
Results:
SQL: SELECT TOP 1 [Link] AS Items_ItemID, [Link], Sum([Link]) AS Quantitytotal FROM Items INNER JOIN ItemsSold ON Items.[ItemID] = ItemsSold.[ItemID] GROUP BY [Link], [Link] ORDER BY Sum([Link]) DESC;
7. What was the best day in terms of highest total value of orders?
Results:
SQL: SELECT TOP 1 [Link], Sum([Link]) AS Totalvalue FROM Orders GROUP BY [Link] ORDER BY Sum([Link]) DESC;
8. Calculate the total commissions owed to salesperson Johnson. Hint: You need to compute the total of commission multiplied by order amount.
Results:
SQL: SELECT [Link], Sum([Link]*[Link]) AS TotalCommission FROM Salespeople INNER JOIN Orders ON Salespeople.[SID] = Orders.[SID] WHERE ((([Link])='Johnson')) GROUP BY [Link];
9. Get the name and phone number of customers who bought blue jeans in June 2004.
Results:
SQL: SELECT [Link], [Link], [Link], [Link] FROM Items INNER JOIN (Customers INNER JOIN (Orders INNER JOIN ItemsSold ON [Link] = [Link]) ON [Link] = [Link]) ON [Link] = [Link] WHERE ((([Link]) Like 'Blue jeans') AND (([Link])<=#6/30/2004# And ([Link])>=#6/1/2004#));
10. List all of the items sold in May
Results:
SQL: SELECT [Link], [Link], [Link], [Link] FROM Items INNER JOIN (Orders INNER JOIN ItemsSold ON [Link] = [Link]) ON [Link] = [Link] WHERE ((([Link])<=#5/30/2004# And ([Link])>=#5/1/2004#));
11. Which salesperson sold jeans to a customer in Phoenix?
Results:
SQL: SELECT DISTINCT [Link], [Link], [Link], [Link] FROM Salespeople, Customers, Items, ItemsSold, Orders WHERE ((([Link])=[Orders].[SID]) AND (([Link])='Phoenix') AND (([Link]) Like '*jeans*') AND (([Link])=[Customers].[CID]) AND (([Link])=[ItemsSold].[OrderID]) AND (([Link])=[Items].[ItemID]));
12. Create an input screen that enables a clerk to update information and add new customers to the database.