Guest User

Untitled

a guest
Feb 27th, 2020
239
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- SELECT (ONE TABLE only)
  2. -- 4 (G) - Find all the French customers
  3. SELECT * FROM customers WHERE country = 'France';
  4.  
  5. -- 5 (G) - Find first name and last name on employees with the title ”sales rep”
  6. SELECT firstName, lastName FROM employees WHERE jobTitle = 'sales rep';
  7.  
  8. -- 6 (G) - Find name and description of the motorcycle models that cost between 50$ and 100$
  9. SELECT productName, productDescription FROM products WHERE productLine = 'motorcycles' AND buyPrice BETWEEN 50 AND 100;
  10.  
  11. -- 7 (G) - Find the customer numbers for the orders that have been cancelled
  12. SELECT customerNumber FROM orders WHERE status = 'cancelled';
  13.  
  14. -- 8 (G) - Find product lines and vendor for products with size 1:18
  15. SELECT productLine, productVendor FROM products WHERE productScale = '1:18';
  16.  
  17.  
  18.  
  19.  
  20. -- ORDER BY
  21. -- 9 (G) - List all the motorcycles sorted by product name
  22. SELECT * FROM products WHERE productLine = 'motorcycles' ORDER BY productName ASC;
  23.  
  24. -- 10 (G) - List models with less than 1000 in stock sorted by quantity in stock with highest quantity at the top of the list
  25. SELECT productName, quantityInStock FROM products WHERE quantityInStock <= 1000 ORDER BY quantityInStock DESC;
  26.  
  27. -- 11 (G) - List the Norwegian customers’ customer name and contact person sorted by the contact person’s first name
  28. SELECT customerName, contactFirstName, contactLastName FROM customers WHERE country = 'Norway' ORDER BY contactFirstName ASC;
  29.  
  30.  
  31.  
  32.  
  33. -- FUNCTIONS
  34. -- 12 (G) - How many German customers are there?
  35. SELECT COUNT(country = 'germany') AS 'German customers' FROM customers;
  36.  
  37. -- 13 (G) - What is the average price for classic car models?
  38. SELECT AVG(buyPrice) AS 'Average price for classic car models' FROM products WHERE productLine = 'classic cars';
  39.  
  40. -- 14 (Y) - What is the price of the most expensive model from ‘Autoart Studio Design’?
  41. SELECT MAX(buyPrice) AS 'Most expensive model from Autoart Studio Design' FROM products WHERE productVendor = 'Autoart Studio Design';
  42.  
  43. -- 15 (R) - How many different countries do the customers come from?
  44. SELECT COUNT(distinct country) AS 'Different countries' FROM customers;
  45.  
  46. -- 16 (R) - What is the quantity in stock for 1:12 models?
  47. SELECT COUNT(quantityInStock) AS 'Quantity in stock for 1:12 models' FROM products WHERE productScale = '1:12';
  48.  
  49. -- 17 (R) - What is the highest profit amongst the products?
  50. SELECT MAX(MSRP - buyPrice) AS 'Highest profit amongst the products' FROM products;
  51.  
  52.  
  53.  
  54. -- GROUP BY
  55. -- 18 (G) - What is the average price for each product line?
  56. SELECT AVG(buyPrice) AS 'Average price for each product line', productLine FROM products GROUP BY productLine;
  57.  
  58. -- 19 (Y) - How many different products does each vendor have?
  59. SELECT COUNT(distinct productName), productVendor AS 'Products from each vendor' FROM products GROUP BY productVendor;
  60.  
  61. -- 20 (R) - What is the profit percentage wise based on product scale?
  62. SELECT CONCAT(buyPrice/MSRP * 100) AS 'Profit percentage', productScale FROM products GROUP BY productScale;
  63.  
  64. -- 21 (R) - How many orders exist for each order status type?
  65. SELECT COUNT(orderNumber) AS 'Orders for each status', `status` FROM orders GROUP BY `status`;
  66.  
  67. -- 22 (R) - How many orders do each customer have?
  68. SELECT COUNT(orderNumber), customerNumber AS 'Amount of orders' FROM orders GROUP BY customerNumber;
  69.  
  70.  
  71.  
  72. -- SELECT (MANY TABLES)
  73. -- 23 (G) - Find all customer names and the name of the employee responsible for the customer
  74.  
  75.  
  76. -- 24 (Y) - Find all customer names and the name of the employee responsible for the customer for italian customers
  77.  
  78.  
  79. -- 25 (R) - Find all customer countries and the names of the employees responsible for customers in the countries
RAW Paste Data