Day 7

What are the categories of products made by manufacturers located in New Jersey? Use a subquery.

select distinct category from product where manufacturerid IN (select manufacturerid from manufacturer where state='NJ')

A subquery is a query that is written inside another query Also called a nested query In addition, a subquery can have a subquery A subquery contains the following clauses SELECT, FROM (optional WHERE, GROUP BY, HAVING) Question: A subquery can reside in which of the following clauses of the outside (outer) query?

from ,where, having (must be an aggregate function), select

You determined that you will need a RIGH JOIN to correctly run a query. Will it make a difference if you write RIGH OUTER JOIN instead of RIGH JOIN?

No, we can leave out OUTER and and INNER from a JOIN statement

We will start with subqueries in the WHERE clause. What is the purpose of the WHERE clause?

To evaluate a logical test

Now we are ready to write subqueries in the FROM clause. What is the purpose of the FROM clause?

To list all the tables where the data will be coming from

Now it's time to do a subquery in the HAVING clause. What is the purpose of the HAVING clause?

To test a logical test on an aggregate function

A correlated subquery, just like an un-correlated subquery that we've been practicing so far, contains a query within a query. Why do we call them correlated? Because the two queries are related. The inner query uses information obtained from the outer query (e.g., from a table referenced in the outer query). For the programmers among you, this is similar to the nested loop structure. With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to be used by the main query. A correlated subquery, however, executes once for each candidate row considered by the outer query. In other words, the inner query is driven by the outer query. Correlated subqueries are a difficult concept to grasp. While we will be practicing them, there will be no correlated subquery on our first exam. Is this okay with you?

Yes, I'm fine with this

List sales of all black sneakers. Use two subqueries instead of a JOIN.

select * from sale where saleid IN (select saleid from saleitem where productid IN (select productid from product where color='black' and category='sneakers'))

List state, number of customers that made purchases (name it ActiveCust), number of customers that did not make purchases (name it PassiveCust). Order alphabetically by state. Hint: Write 2 queries. First query should output state and Active Customer. Second query should output state and Passive Customers. Join these two queries using the state and PK and FK between the two queries.

select SQ1.State, ActiveCust, PassiveCust from (select state, count(*) as ActiveCust from customer c join sale s on c.customerid=s.customerid group by state) SQ1 join (select state, count(*) as PassiveCust from customer c left join sale s on c.customerid=s.customerid where s.customerid is null group by state) SQ2 on SQ1.state=SQ2.state order by SQ1.state

List customer ID, first name, last name, and total amount of purchase as TotalSold. Use a subquery in the FROM clause that will provide Customer ID and the total amount purchased (SUM(Quantity * SalePrice))

select c.customerid, firstname, lastname, Totalsold from customer c join (select customerid, sum(quantity*saleprice) as TotalSold from sale s join saleitem si on s.saleid=si.saleid group by customerid) SQ on c.customerid=sq.customerid

For each product category, what is the average list price of black shoes? Consider only those categories whose average list price is below the average list price for all black shoes.

select category, avg(listprice) from product where color='black' group by category having avg(listprice) < (select avg(listprice) from product where color='black')

List names of all customers that purchased 1 or 2 shoes only in one order (sale). Use a subquery with the NOT IN operator. Hint: count the quantity in the SaleItem table to find the number of shoes purchased

select customerid, firstname, lastname from customer where customerid NOT IN (select c.customerid from customer c join sale s on c.customerid=s.customerid join saleitem si on s.saleid=si.saleid group by c.customerid, s.saleid having count(quantity) >= 3)

List product ID, product name, list price, category, and the average price of all products in the same category. This final field should be called AVG_Cat_Price. Order the output by list price.

select distinct productid, productname, listprice, p.category, Avg_Cat_Price from product p join (select category, avg(listprice) as Avg_Cat_Price from product group by category) as ACP on p.category=acp.category order by listprice

List employees who were hired before their managers. Use the following aliases: E for Employee table (Employee E) M for Manager table (Employee M) (correlated)

select employeeid, firstname, lastname, hiredate from employee e where e.hiredate< (select m.hiredate from employee m where m.employeeid=e.managerid)

Which employees receive a higher-than-average salary? List employee first name, last name, and salary.

select firstname, lastname, salary from employee e join salaryemployee se on e.employeeid=se.employeeid where salary > (select avg(salary) from salaryemployee)

Which employees receive a salary higher than the average for employees reporting to the employee's manager? List employee first and last name, and salary. (correlated)

select firstname, lastname, salary from employee e1 join salaryemployee se1 on e1.employeeid=se1.employeeid where salary > (select avg(salary) from employee e2 join salaryemployee se2 on e2.employeeid=se2.employeeid where e1.managerid=e2.managerid)

HW#2 - Q11: For each manufacturer (ManufacturerID) show the maximum list price of its products. Show only those manufacturers whose products have an average list price below the average list price of products from manufacturers in their state, and put the result in order by state. Use a correlated subquery in the HAVING clause. Show ManufacturerID, State, and MaxListPrice. (correlated)

select m1.manufacturerid, state, max(listprice) as MaxListPrice from product p1 join manufacturer m1 on p1.manufacturerid=m1.manufacturerid group by m1.manufacturerid, state having avg(listprice) < (select avg(listprice) from product p2 join manufacturer m2 on p2.manufacturerid=m2.manufacturerid where m2.state=m1.state) order by state

For each manufacturer, show its higher priced products. We define higher priced products to be those whose list price is higher than the average list price for that manufacturer. (correlated)

select manufacturerid, productid, productname, listprice from product P1 where listprice > (select avg(listprice) from product P2 where P1.manufacturerid=P2.manufacturerid)

List the product ID, product name, color, and the number of times a product was sold. Make sure that you include also those products that have NOT been sold. Sort the output by the count of number times the product was sold and then by product ID.

select p.productid, productname, color, count(saleid) from saleitem si right join product p on si.productid=p.productid group by p.productid, productname, color order by count(saleid), p.productid

List ProductID, ProductName, and List Price for every product that has a List Price greater than the average List Price. Hint: use a subquery in the WHERE clause.

select productid, productname, listprice from product where listprice > (select avg(listprice) from product)

Recall that an output of a query looks like a table: it has columns (list of items you put to the SELECT clause it has rows - data that met your criteria in the FROM, WHERE, GROUP BY, and HAVING clauses Additional hints: A subquery in the FROM clause is a temporary table. It must have a field that acts as a unique identified if you need to join this subquery/temporary table with a physical table. The subquery in the FROM clause must be given a name - alias - as an identifier for the primary/outer query. Are you ready to write your first subquery in the FROM clause?

true