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