- Question 1 (might be worth 5 points): How many employees have the word "son" in their name?
- Answer:
SELECT [employee id]
FROM employees
WHERE [last name] like '*son*';
- Question 2 (might be worth 10 points): How many customers do we have in London, England?
- Answer:
SELECT count(*)
FROM customers
WHERE city = 'london';
- Question 3 (might be worth 10 points): Which country did we ship the most ($$$) to?
- Answer:
SELECT orders.[ship country],
sum([order details].[unit price]*[order details].quantity)
FROM orders, [order details]
WHERE orders.[order id]=[order details].[order id]
GROUP BY orders.[ship country]
ORDER BY sum([order details].[unit price]*[order details].quantity) DESC;
- Question 4 (might be worth 10 points): On average (in $$$ or %), how much did we discount
our products?
- Answer:
SELECT avg(discount)
FROM [order details]; (%)
- Question 5 (might be worth 15 points): Who were our highest performing employees (sales $$$)?
- Answer:
SELECT top 3 employees.[first name], employees.[last name],
sum([unit price] * quantity)
FROM employees, [order details], orders
WHERE employees.[employee id]=orders.[employee id]
and orders.[order id]=[order details].[order id]
GROUP BY employees.[first name], employees.[last name]
ORDER BY sum([unit price] * quantity) DESC;
- Question 6 (might be worth 15 points): Which product do we earn the most profit on?
- Answer:
SELECT TOP 3 products.[product name],
[order details].[unit price], products.[MSRP]
FROM [order details], products
WHERE products.[product id]=[order details].[product id]
ORDER BY [order details].[unit price]-products.[MSRP] desc;
Now, do you notice anything WEIRD about this database???