a guest Feb 27th, 2020 144 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
- -- SELECT (ONE TABLE only)
- -- 4 (G) - Find all the French customers
- SELECT * FROM customers WHERE country = 'France';
- -- 5 (G) - Find first name and last name on employees with the title ”sales rep”
- SELECT firstName, lastName FROM employees WHERE jobTitle = 'sales rep';
- -- 6 (G) - Find name and description of the motorcycle models that cost between 50$ and 100$
- SELECT productName, productDescription FROM products WHERE productLine = 'motorcycles' AND buyPrice BETWEEN 50 AND 100;
- -- 7 (G) - Find the customer numbers for the orders that have been cancelled
- SELECT customerNumber FROM orders WHERE status = 'cancelled';
- -- 8 (G) - Find product lines and vendor for products with size 1:18
- SELECT productLine, productVendor FROM products WHERE productScale = '1:18';
- -- ORDER BY
- -- 9 (G) - List all the motorcycles sorted by product name
- SELECT * FROM products WHERE productLine = 'motorcycles' ORDER BY productName ASC;
- -- 10 (G) - List models with less than 1000 in stock sorted by quantity in stock with highest quantity at the top of the list
- SELECT productName, quantityInStock FROM products WHERE quantityInStock <= 1000 ORDER BY quantityInStock DESC;
- -- 11 (G) - List the Norwegian customers’ customer name and contact person sorted by the contact person’s first name
- SELECT customerName, contactFirstName, contactLastName FROM customers WHERE country = 'Norway' ORDER BY contactFirstName ASC;
- -- FUNCTIONS
- -- 12 (G) - How many German customers are there?
- SELECT COUNT(country = 'germany') AS 'German customers' FROM customers;
- -- 13 (G) - What is the average price for classic car models?
- SELECT AVG(buyPrice) AS 'Average price for classic car models' FROM products WHERE productLine = 'classic cars';
- -- 14 (Y) - What is the price of the most expensive model from ‘Autoart Studio Design’?
- SELECT MAX(buyPrice) AS 'Most expensive model from Autoart Studio Design' FROM products WHERE productVendor = 'Autoart Studio Design';
- -- 15 (R) - How many different countries do the customers come from?
- SELECT COUNT(distinct country) AS 'Different countries' FROM customers;
- -- 16 (R) - What is the quantity in stock for 1:12 models?
- SELECT COUNT(quantityInStock) AS 'Quantity in stock for 1:12 models' FROM products WHERE productScale = '1:12';
- -- 17 (R) - What is the highest profit amongst the products?
- SELECT MAX(MSRP - buyPrice) AS 'Highest profit amongst the products' FROM products;
- -- GROUP BY
- -- 18 (G) - What is the average price for each product line?
- SELECT AVG(buyPrice) AS 'Average price for each product line', productLine FROM products GROUP BY productLine;
- -- 19 (Y) - How many different products does each vendor have?
- SELECT COUNT(distinct productName), productVendor AS 'Products from each vendor' FROM products GROUP BY productVendor;
- -- 20 (R) - What is the profit percentage wise based on product scale?
- SELECT CONCAT(buyPrice/MSRP * 100) AS 'Profit percentage', productScale FROM products GROUP BY productScale;
- -- 21 (R) - How many orders exist for each order status type?
- SELECT COUNT(orderNumber) AS 'Orders for each status', `status` FROM orders GROUP BY `status`;
- -- 22 (R) - How many orders do each customer have?
- SELECT COUNT(orderNumber), customerNumber AS 'Amount of orders' FROM orders GROUP BY customerNumber;
- -- SELECT (MANY TABLES)
- -- 23 (G) - Find all customer names and the name of the employee responsible for the customer
- -- 24 (Y) - Find all customer names and the name of the employee responsible for the customer for italian customers
- -- 25 (R) - Find all customer countries and the names of the employees responsible for customers in the countries
RAW Paste Data