CIS 502 PRACTICE SQL QUESTIONS!

• 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???