1. List the female cats that are registered.
SELECT Animal.AnimalID
FROM Animal
WHERE (((Animal.Registered) Is Not Null) AND ((Animal.Gender)="female") AND ((Animal.Breed)="cat"));
2. List the customers who live in Vermont (VT).
Select Customer.CustomerID, Customer.Phone, Customer.FirstName, Customer.LastName, City.State
From City INNER JOIN Customer ON = City.CityID Customer.CityID
Where (City.State = ‘VT’)
3. Which dogs have a list price greater than $270?
SELECT Animal.AnimalID, Animal.ListPrice
FROM Animal
WHERE (((Animal.Breed)="dog") AND ((Animal.ListPrice)>270));
4. Which dogs cost less than $100 or were registered and cost more than $250?
Select Animal.AnimalID, Animal.Name, Animal.Category, Animal.Registered, Animal.ListPrice
From Animal
Where (((Category = 'dog') AND (ListPrice < 100)) Or ((Category = 'dog') AND (Registered is not Null) AND (ListPrice > 250)));
5. List the customers who purchased something in January.
SELECT Customer.FirstName, Customer.LastName, Sale.SaleDate
FROM Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID
WHERE (((Sale.SaleDate) Between #1/1/1998# And #1/31/1998#));
6. List the suppliers who delivered cats in March.
SELECT Supplier.SupplierID, Supplier.Name, AnimalOrder.ReceiveDate, Animal.AnimalID, Animal.Name, Animal.Category
FROM Supplier INNER JOIN (AnimalOrder INNER JOIN (Animal INNER JOIN AnimalOrderItem ON Animal.AnimalID = AnimalOrderItem.AnimalID) ON AnimalOrder.OrderID = AnimalOrderItem.OrderID) ON Supplier.SupplierID = AnimalOrder.SupplierID
WHERE (((AnimalOrder.ReceiveDate) Between #3/1/2007# And #3/31/2007#) AND ((Animal.Category)="Cat"));
7. List the birds with gold in their color.
Select AnimalID , Category , Color
From Animal
Where ((Category = 'bird')
and (Color LIKE '%Gold%'));
8. Which employees sold items or animals between June 15 and June 30? (List each employee only once.)
SELECT: DISTINCT Employee.EmployeeID, Employee.LastName, Employee.FirstName
FROM: Employee INNER JOIN Sale ON Employee.EmployeeID= Sale.EmployeeID
WHERE: (Sale.SaleDate Between ’15-June-2006’ AND ’30-June-2006’) ORDER BY Employee.LastName ;
9. Which animals were born no more than 30 days after they were ordered?
SELECT Animal.AnimalID, Animal.Name, Animal.Category, AnimalOrder.OrderDate
FROM AnimalOrder INNER JOIN (Animal INNER JOIN AnimalOrderItem ON Animal.AnimalID = AnimalOrderItem.AnimalID) ON AnimalOrder.OrderID = AnimalOrderItem.OrderID
WHERE ((([DateBorn]-[OrderDate]) <30));
10. What is the most expensive (SalePrice) cat ever sold?
11. Which dog was sold at the greatest discount (ListPrice-SalePrice)?
SELECT Max([ListPrice]-[SalePrice]) AS Maxdisc, Animal.Category
FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID
GROUP BY Animal.Category
HAVING (((Animal.Category)="dog"));
12. What is the highest-value merchandise order (total of quantity*cost) ever placed?
SELECT Max([Quantity]*[Cost]) AS [Value]
FROM OrderItem;
13. What is the most number of animals ever sold at one time (on the same sale)?
SELECT Count(SaleAnimal.AnimalID) AS CountOfAnimalID, SaleAnimal.SaleID
FROM Sale INNER JOIN (Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID
GROUP BY SaleAnimal.SaleID;
14. List the number of sales that took place each month. Hint: You need to find the function that returns the month number of the SaleDate.
SELECT Month([SaleDate]) AS [Month], Count(Sale.SaleID) AS CountOfSaleID
FROM Sale
GROUP BY Month([SaleDate]);
15. What are the total values of sales of merchandise items by category?
16. Which employee sold the most merchandise by value in June?
17. Which supplier has received the most money (Cost) for cats?
SELECT Supplier.SupplierID, Supplier.Name, Sum(AnimalOrderItem.Cost) AS SumOfCost
FROM Supplier INNER JOIN (AnimalOrder INNER JOIN (Animal INNER JOIN AnimalOrderItem ON Animal.AnimalID = AnimalOrderItem.AnimalID) ON AnimalOrder.OrderID = AnimalOrderItem.OrderID) ON Supplier.SupplierID = AnimalOrder.SupplierID
GROUP BY Supplier.SupplierID, Supplier.Name, Animal.Category
HAVING (((Animal.Category)="Cat"))
ORDER BY Sum(AnimalOrderItem.Cost) DESC;
18. Which category of animal provides the highest average profit percentage (using SalePrice and Cost)?
SELECT Animal.Category, Avg(([SalePrice]-[Cost])/[Cost]) AS Pct
FROM (Animal INNER JOIN AnimalOrderItem ON Animal.AnimalID = AnimalOrderItem.AnimalID) INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID
GROUP BY Animal.Category
ORDER BY Avg(([SalePrice]-[Cost])/[Cost]) DESC;
19. Who is the best customer measured by total value of merchandise purchases for the first half of the year?
SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName, Sum([ListPrice]*[quantity]) AS TV
FROM (Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID) INNER JOIN (Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID) ON Sale.SaleID = SaleItem.SaleID
WHERE (((Sale.SaleDate) Between #1/1/2004# And #6/30/2004#))
GROUP BY Customer.CustomerID, Customer.FirstName, Customer.LastName
ORDER BY Sum([ListPrice]*[quantity]) DESC;
20. On average, which supplier is slowest to ship merchandise?
SELECT Supplier.SupplierID, Supplier.Name, Avg([ReceiveDate]-[OrderDate]) AS Timing
FROM Supplier INNER JOIN MerchandiseOrder ON Supplier.SupplierID = MerchandiseOrder.SupplierID
GROUP BY Supplier.SupplierID, Supplier.Name
ORDER BY Avg([ReceiveDate]-[OrderDate]) DESC;
21. What is the oldest cat on the day it was sold?
SELECT Animal.AnimalID, Animal.Name, Animal.Category, [DateBorn]-[SaleDate] AS Age
FROM Sale INNER JOIN (Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID
WHERE (((Animal.Category)="Cat"))
ORDER BY [DateBorn]-[SaleDate] DESC;
22. Compare the average sale price of registered dogs to unregistered dogs?.
SELECT Animal.Registered, Avg(SaleAnimal.SalePrice) AS AvgOfSalePrice
FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID
WHERE (((Animal.Category)="Dog"))
GROUP BY Animal.Registered;
23. Has the company sold more female or male black cats?
SELECT Animal.Gender, Count(SaleAnimal.SaleID) AS CountOfSaleID
FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID
WHERE (((Animal.Category)="Cat") AND ((Animal.Color) Like "*Black*"))
GROUP BY Animal.Gender
ORDER BY Count(SaleAnimal.SaleID) DESC;
24. What is the most expensive merchandise item sold (SalePrice)?.
SELECT Merchandise.ItemID, Merchandise.Description, SaleItem.SalePrice
FROM Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID
ORDER BY SaleItem.SalePrice DESC;
25. Which merchandise item was sold at the greatest percentage discount from list price?
SELECT Merchandise.ItemID, Merchandise.Description, Max((([ListPrice]-[SalePrice])/[ListPrice])) AS MD
FROM Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID
GROUP BY Merchandise.ItemID, Merchandise.Description
ORDER BY Max((([ListPrice]-[SalePrice])/[ListPrice])) DESC;
