SHARE
TWEET

Untitled

a guest Feb 27th, 2020 144 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top