SQL Practice Queries

6. For each product on order 10380, what is the discount per unit in dollars and discount per unit in percentage that Hungry Owl All-Night Grocers (the customer on that order) received from the retail list price? Include the order identifier, product identifier, and product name in the output. HINT: You should NOT include the customers table or orders table in your query.

SELECT (products.unitprice - orderdetails.unitprice),(products.unitprice - orderdetails.unitprice)/products.unitprice, orderdetails.orderid, products.productid, productname FROM northwinds.products JOIN northwinds.orderdetails ON products.productid = orderdetails.productid WHERE orderid = '10380'

2. For each season, show the average number of tackles that FC Cincinnati's players made and the average age of the players. Include the number of players that were included in each of the averages on each row of output. Sort the output by season from most recent to least recent.

SELECT AVG(Tkl), season, AVG(age), COUNT(age), COUNT(Tkl) FROM fcc_cc.defensive_actions WHERE squad = 'FC Cincinnati' GROUP BY season ORDER BY season DESC

5. What is the average retail list price per unit for Northwind's discontinued items that still have more than 20 units remaining in stock? Also include the number of products that meet these conditions in the output.

SELECT AVG(products.unitprice), COUNT(unitsinstock) FROM northwinds.products WHERE discontinued = '1' AND unitsinstock > '20'

2. What is the average number of units in stock for all of Northwind's products from the supplier named Leka Trading?

SELECT AVG(unitsinstock) FROM northwinds.products JOIN northwinds.suppliers ON products.supplierid = suppliers.supplierid WHERE companyname = 'Leka Trading'

Write a query to show the number of MLS players with a nationality of USA, CAN, or MEX who played for FC Dallas in 2019.

SELECT COUNT(*)FROM fcc_cc.playersJOIN fcc_cc.shooting ON players.player_id = shooting.player_idWHERE nation IN ('USA','CAN','MEX') AND squad = 'FC Dallas' AND season = 2019

1. How many Northwind Traders' employees report directly to employee 2, Dr. Andrew Fuller?

SELECT COUNT(employeeid) FROM northwinds.employees WHERE reportsto = '2'

7. Display the number of orders and total freight costs for Northwind's orders placed after '27-Dec-2014' and shipped by either Federal Shipping or Speedy Express. HINT: Dates are expressed in the Northwinds database as shown in the question. The ' character in Word cannot be directly copied into FSB Oracle, you'll need to re-type it.

SELECT COUNT(orderid), SUM(freight) FROM northwinds.orders JOIN northwinds.shippers ON orders.shipvia = shippers.shipperid WHERE shippers.companyname = 'Federal Shipping' OR shippers.companyname = 'Speedy Express' AND orderdate > 27-Dec-2014'

6. Which customers located in France have worked with the Northwinds employee Laura Callahan to place orders?

SELECT DISTINCT companyname FROM northwinds.customers JOIN northwinds.orders ON customers.customerid = orders.customerid JOIN northwinds.employees ON orders.employeeid = employees.employeeid WHERE customers.country = 'France' AND firstname = 'Laura' AND lastname = 'Callahan'

4. List the supplier company name, the name of Northwind's main contact at the company, and the phone number for all suppliers who provide Northwinds with a product in the Meat/Poultry category. Please list each company only once, even if they provide more than one product.

SELECT DISTINCT companyname, contactname, phone FROM northwinds.suppliers JOIN northwinds.products ON suppliers.supplierid = products.supplierid JOIN northwinds.categories ON products.categoryid = categories.categoryid WHERE categoryname = 'Meat/Poultry'

1. Write a query to show the total number of shots on target for each player on the FC Cincinnati squad. Also group the records by their position. Only include groupings in which the player has been with FC Cincinnati in that position for more than two seasons. Sort the output by the number of shots on target from high to low.

SELECT SUM(SoT), position, players.player, squad FROM fcc_cc.shooting JOIN fcc_cc.players ON players.player_id = shooting.player_id WHERE squad = 'FC Cincinnati' GROUP BY player, position, squad HAVING COUNT(*) > 2 ORDER BY SUM(SoT) DESC

Write a query in the space below that shows the total freight charge for Northwinds orders shipped to the USA or ordered by a customer based in the USA. Include only orders taken by employee number 2.

SELECT SUM(freight) FROM northwinds.orders JOIN northwinds.customers ON customers.customerid = orders.customerid WHERE (shipcountry = 'USA' OR country='USA') AND employeeid = 2

3. What is the total gross profit that Northwind's realized for all product orders in the Beverage category? HINT: Total gross profit is the quantity sold multiplied by (sales price - unit cost).

SELECT SUM(orderdetails.quantity * (orderdetails.unitprice - unitcost) FROM northwinds.orderdetails JOIN northwinds.products ON orderdetails.productid = products.productid JOIN northwinds.categories ON products.categoryid = categories.categoryid WHERE categoryname = 'Beverages'

Northwinds managers would like to identify the suppliers whose products generate the most sales (unit price paid multiplied by the quantity) for their sales representatives. Write a query in the space below that shows the supplier company names whose products resulted in a total of more than $10,000 in sales, taken by Northwind's employees with the title "Sales Representative". List the supplier whose products generated the highest total sales first.

SELECT companyname, SUM(orderdetails.unitprice * quantity) FROM northwinds.suppliers JOIN northwinds.products ON suppliers.supplierid = products.supplierid JOIN northwinds.orderdetails ON products.productid = orderdetails.productid JOIN northwinds.orders ON orderdetails.orderid = orders.orderid JOIN northwinds.employees ON orders.employeeid = employees.employeeid WHERE title = 'Sales Representative' GROUP BY companyname HAVING SUM(orderdetails.unitprice * quantity) > 10000 ORDER BY SUM(orderdetails.unitprice * quantity) DESC

1. List the first name, last name, extension, and manager's employee number for each Northwinds employee that works in the city of Seattle, Kirkland, or Redmond.

SELECT firstname, lastname, extension, reportsto FROM northwinds.employees WHERE city = 'Seattle' OR city = 'Kirkland' OR city = 'Redmond'

Write a query to list the first name, last name, and job title of each employee that reports to employee 5, Steven Buchanan.

SELECT firstname, lastname, title FROM northwinds.employees WHERE reportsto = 5

5. Display the order identifier, product name, quantity sold, and item price paid by the customer for each order shipped to Norway using either Federal Shipping or United Package as the shipping company.

SELECT orderdetails.orderid, productname, products.unitprice, quantity FROM northwinds.products JOIN northwinds.orderdetails ON products.productid = orderdetails.productid JOIN northwinds.orders ON orderdetails.orderid = orders.orderid JOIN northwinds.shippers ON orders.shipvia = shippers.shipperid WHERE shipcountry = 'Norway' AND (shippers.companyname = 'Federal Shipping' OR shippers.companyname = 'United Package')

Create a player metric as a weighted average of goals per shot on target and passing completion percentage. Apply a weight of 3 to the passing completion percentage and a scaling factor of 284 to the goals per shot on target. Label the metric column "Metric". List the average of this metric by squad (including all years) in descending order order of the metric.

SELECT passing.squad, AVG((284 * g_sot + 3 * cmp_perc)/4) as MetricFROM fcc_cc.passingJOIN fcc_cc.shooting ON passing.id = shooting.idGROUP BY passing.squadORDER BY Metric DESC

2. List the product ID, product name, number of units currently in stock, and the quantity per unit for each product where there are more units currently in stock than the number of units on order.

SELECT productid, productname, unitsinstock, quantityperunit FROM northwinds.products WHERE unitsinstock > unitsonorder

3. List the product name and category name (not number) for all products from supplier ID 17. Note: You do not need to join the suppliers table.

SELECT productname, categoryname FROM northwinds.products JOIN northwinds.categories ON products.categoryid = categories.categoryid WHERE supplierid = 17

Create a table that shows the average goals and assists per 90 minutes by team for each season. Only include players who had at least one free kick or penalty kick, and seasons in which the team scored more than 30 goals and had 5 or more records counted in the average. Order the list by team first, and then in order of average goals and assists per 90 from highest to lowest. Include the number of records counted in the average for each group in the output.

SELECT shooting.squad, shooting.season, AVG(ga_90), COUNT(ga_90)FROM fcc_cc.standard_statsJOIN fcc_cc.shooting ON standard_stats.id = shooting.idWHERE shooting.pk >= 1 OR fk >= 1GROUP BY shooting.squad, shooting.seasonHAVING SUM(shooting.g) > 30 AND COUNT(ga_90) >= 5ORDER BY shooting.squad, AVG(ga_90) DESC

4. List the number of units in stock, product name, unit cost, and extended cost for each Northwind's product purchased from a supplier not in Spain that has a unit cost of more than $12. HINT: Extended cost is similar to the extended price described in this week's reading, but you calculate it using unit cost and the number of units in stock.'

SELECT unitsinstock, productname, unitcost, unitcost * unitsinstock FROM northwinds.products JOIN northwinds.suppliers ON products.supplierid = suppliers.supplierid WHERE country != 'Spain' AND unitcocst > '12'