sql queries - joining, multi-tables, subqueries

join

• Joins 2 or more tables into one single table or view

equi-join

• The columns that you are joining should have equal values

  • They will appear redundantly in the result table

inner join

• An equi-join where the duplicate columns are eliminated

• ex. To find all sales persons that have at least one order

  • The left table is the salesperson table

  • The right table is the order table

select *

from Salesperson_T sp;

 

select*

from Order_T o;

 

forms one single large table:

select*

from Salesperson_T sp inner join Order_T o on o.SalesPersonID=sp.SalespersonID

 

If you don't want to use the inner join function:

select *

from Salesperson_T sp,  Order_T o

 where sp.SalesPersonID=o.SalespersonID

 

ex. of inner join

Produce a summary of sales (price*quantity), group by product

 

select p.ProductDescription,sum(p.ProductStandardPrice*ol.OrderedQuantity) [Total Sales ($)]

from Product_T p inner join OrderLine_T ol on p.ProductID=ol.ProductID

group by p.ProductDescription

 

ex.

Produce a summary of sales, group by year

 

select year(o.orderDate) [year], sum(p.ProductStandardPrice*ol.OrderedQuantity) [Total Sales ($)]

from Order_T o inner join OrderLine_T ol on o.OrderID=ol.OrderID inner join Product_T p on ol.ProductID=p.ProductID

group by year(o.orderdate)

 

ex.

• List product line names with the number of products and the average product price being higher than $200 per product line

 

select pl.ProductLineName, count(p.productid) [NbrofProducts], avg(p.Productstandardprice) [Average Price]

from Product_T p inner join ProductLine_T pl on p.ProductLineID=pl.ProductLineID

group by pl.ProductLineName

having avg(p.productstandardprice)>200

outer join

• Can be left, right, full

• A join where you don't have matching values but they are included in the result table

left join

• It gives you all of the information on the left side of the join

  • The right side is treated as a characteristic of the left side

• ex.

  • Finding all the salespersons and list their orders

select*

from Salesperson_T sp left join Order_T o on sp. SalespersonID=o.SalesPersonID

right join

• It gives you all of the information on the right side of the join

  • The left side is treated as a characteristic Of the right side

• ex

  • Find all orders and list the salesperson assigned to each order

select*

from Salesperson_T sp right join Order_T o on sp. SalespersonID=o.SalesPersonID

ex. of left join

List of salespersons that have not been a part of any order

 

select*

from Salesperson_T sp left join Order_T o on sp. SalespersonID=o.SalesPersonID

where o.OrderID is null

List of salespersons along with the number of orders that they served

 

select sp.SalespersonID, sp.SalespersonName, count(o.orderid) [Number of Orders Served]

from Salesperson_T sp left join Order_T o on sp. SalespersonID=o.SalesPersonID

group by sp. SalespersonID, sp.SalespersonName

full join

• Find all of the things on the right side of the join and all of the things on the left side of the join

• ex.

  • Find all the salespersons and each of the orders they served along with all of the orders that have no sales person either

 

select*

from Salesperson_T sp full join Order_T o To join multiple tables 2on sp. SalespersonID=o.SalesPersonID

 

to join multiple diff. tables

• 2 methods

  • Inner join

  • Where

• ex.

  • Find all the customers who purchased products that have table in the description

    • For this query you will need the customer, order, order line, product tables

  • inner join

select distinct c.*

from Customer_T c inner join Order_T o on c.CustomerId=o.CustomerID

inner join OrderLine_T ol on ol.OrderID=o.OrderID

inner join Product_T p on p.ProductID=ol.ProductID

where p.ProductDescription like '%table%'

 

• Where

select distinct c.*

from Customer_T c, Order_T o, OrderLine_T ol, Product_T p

where c. CustomerId=o.CustomerID and o.OrderID=ol.OrderID and ol.ProductID=p.ProductID

and p.ProductDescription Produce a summary of sales group by product like '%table%'

self join

• 2 methods

  • Inner join

  • Where

• ex.

  • Show all supervisors and the employees they supervise

  • inner join

select s.EmployeeID [SupervisorID], s.EmployeeName [supervisorname], e. EmployeeID, e.EmployeeName, e. EmployeeSupervisor

from Employee_T s inner join Employee_T e on e.EmployeeSupervisor=s.EmployeeID

• where

select s.EmployeeID [SupervisorID], s.EmployeeName [supervisorname], e. EmployeeID, e.EmployeeName, e. EmployeeSupervisor

from Employee_T s, Employee_T e

where e. EmployeeSupervisor = s.EmployeeID

ex. of self join

List how many other employees every employee supervises

 

select s.EmployeeID,s.EmployeeName, count(e.EmployeeID) [Managing Power]

from Employee_T s left join Employee_T e on e.EmployeeSupervisor=s.EmployeeID

group by s.EmployeeID,s.EmployeeName

order by [Managing Power] desc

 

 

left join allows all employees to be listed instead of just the supervisors

ex.

List the names of the employees, employee birth date, manager name, managers birthdate for all employees that were born before their manager

• Think of it in the sense where employees are older than their supervisor

 

select e.*

from Employee_T s left join Employee_T e on e.EmployeeSupervisor=s.EmployeeID

where s.EmployeeBirthDate>e.EmployeeBirthDate

sub query

• Having an inner query inside an outer query

• Processed before the outer query

• There is another select statement inside the query:

  • As a condition of the where clause

  • As a table of the from clause

    • this gives a derived table

  • As the field you want to return from the select clause

  • In the having clause

• Can be non correlated

  • Only used once for the entire outer query

  • They do not depend on data from the outer query

• Can be correlated

  • Used once for every row returned by the outer query

  • The sub query always refers to an attribute referenced in the outer query

  • In this instance you use the operators EXISTS (true or false), ALL

  • ex.

select*

from OrderLine_T ol

where exists (select*

from Product_T p

where p.ProductID=ol.productid and p.ProductFinish='oak')

 

ex. a subquery in a where clause

List the name and the address of the customer who placed order #34

 

select *

from Customer_T c

where c.CustomerId=(select o.CustomerID*

from Order_T o

where o.OrderID=34);

 

 

List all the employees that have the same supervisor as Laura

 

select *

from Employee_T e

where e.EmployeeSupervisor=(select e1.EmployeeSupervisor*

from Employee_T e1

where e1.EmployeeName like '%laura%')

 

 

 

List all the products that have a price that is above average

 

select*

from Product_T p

where p.ProductStandardPrice>( select avg(p1.ProductStandardPrice)

from Product_T p1)

 

 

List the names and addresses of all the customers who made a purchase in the year 2018

 

select*

from Customer_T c

where c.CustomerId in (select o.CustomerID

from Order_T o

where year(o.OrderDate)=2018)

 

list customers with no orders

 

select*

from Customer_T c

where c.CustomerId not in (

select distinct o.CustomerID

from Order_T o)

 

 

list orders that have products with 3 then 4 in id

 

select *

from Order_T o

where o.OrderID in (select ol.OrderID

from OrderLine_T ol

where ol.ProductID=3)

and o.OrderID in (select ol.OrderID

from OrderLine_T ol

where ol.ProductID=4)

 

 

 

 

the subquery can run by itself

ex. a subquery in the from clause

 

list orders that have products with 3 then 4 in id

 

select*

from (select o.*

from Order_T o inner join OrderLine_T ol on o.OrderID=ol.OrderID

where ProductID=3) t3, (select o.*

from Order_T o inner join OrderLine_T ol on o.OrderID=ol.OrderID

where ProductID=4) t4

where t3.orderid=t4.orderid

ex. subqueries using inner joining

list most expensive product of each product line

 

1.  

select*

from Product_T p

where p.ProductStandardPrice=(select max(pl.productstandardprice)

from Product_T pl

where pl.ProductLineID=p.ProductLineID)

 

 

 

list oldest employee of each supervisor

 

1.  

select *

from Employee_T e

where e.EmployeeBirthDate=(select min(es.employeebirthdate)

from Employee_T es

where es.EmployeeBirthDate=e.employeebirthdate)

 

 

employee info for all employees hired in each state before most recently hired person in that state

 

1.  

select*

from Employee_T e

where e.EmployeeDateHired < (select max(e1.EmployeeDateHired)

from Employee_T e1

where e1.EmployeeState=e.employeestate)

 

intersect

ex.

 

Orders that have the products with the ID three and four in consecutive order

 

select o.*

from Order_T o inner join OrderLine_T ol on o.OrderID=ol.OrderID

where ol.ProductID=3

intersect

select o.*

from Order_T o inner join OrderLine_T ol on o.OrderID=ol.OrderID

where ol.ProductID=4

union

Orders that have the products with the ID #3 or 4

 

select o.*

from Order_T o inner join OrderLine_T ol on o.OrderID=ol.OrderID

where ol.ProductID=3

union

select o.*

from Order_T o inner join OrderLine_T ol on o.OrderID=ol.OrderID

where ol.ProductID=4

except

Orders that have a product ID with the number 3 but not have the number four in the same order

 

select o.*

from Order_T o inner join OrderLine_T ol on o.OrderID=ol.OrderID

where ol.ProductID=3

except

select o.*

from Order_T o inner join OrderLine_T ol on o.OrderID=ol.OrderID

where ol.ProductID=4