SQL HACKS

SQL Zoo Practice

HACKS :

1. Running Total - Theta Join
 you can join two tables with 'on a.col1 >= b.col1' -- this is called theta join

2. Cross Join
This is when you want to create combinations and need to generate Cartesian join in the first place

3. Simple CASE WHEN clause

4. Left Outer Joins for null values

5. Add col C that shows the bigger of col A and col B  

6. Pivot!!!!!!!!!
To display rows as columns and columns as rows - using CASE WHEN(or left outer join) & Union
In the first query , there is a MAX in the clause, it is because group by needs either an aggregating function in the select statement or to use these col in group by clause!

7. Queries and web pages
 
a. AdventureWorks Schema

CustomerAW(CustomerID, FirstName, MiddleName, LastName, CompanyName, EmailAddress) 

CustomerAddress(CustomerID, AddressID, AddressType)

Address(AddressID, AddressLine1, AddressLine2, City, StateProvince, CountyRegion, PostalCode)

SalesOrderHeader(SalesOrderID, RevisionNumber, OrderDate, CustomerID, BillToAddressID, 
ShipToAddressID, ShipMethod, SubTotal, TaxAmt, Freight)

SalesOrderDetail(SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice, 
UnitPriceDiscount)

ProductAW(ProductID, Name, Color, ListPrice, Size, Weight, ProductModelID, ProductCategoryID)

ProductModel(ProductModelID, Name)

ProductCategory(ProductCategoryID, ParentProductCategoryID, Name)

ProductModelProductDescription(ProductModelID, ProductDescriptionID, Culture)

ProductDescription(ProductDescriptionID, Description) 

above schema detail could be found  HERE

Medium Questions -- Details and playground can be found HERE
(better type queries into playground field to see the result) 

6. A "Single Item Order" is a customer order where only one item is ordered. Show the SalesOrderID and the UnitPrice for every Single Item Order.

select s.SalesOrderID,p.ListPrice UnitPrice
from ProductAW p inner join SalesOrderDetail s
on p.ProductID = s.ProductID
group by s.SalesOrderID
having count(s.ProductID) = 1

7. Where did the racing socks go? List the product name and the CompanyName for all Customers who ordered ProductModel 'Racing Socks'. 

select p.Name,c.CompanyName
from CustomerAW c inner join SalesOrderHeader s
on c.CustomerID = s.CustomerID
inner join SalesOrderDetail sd on sd.SalesOrderID = s.SalesOrderID
inner join ProductAW p on p.ProductID = sd.ProductID
inner join ProductModel pm on pm.ProductModelID = p.ProductModelID
where pm.Name = 'Racing Socks'

8.Show the product description for culture 'fr' for product with ProductID 736. 

select pd.Description
from ProductDescription pd inner join ProductModelProductDescription pmpd
on pmpd.ProductDescriptionID = pd.ProductDescriptionID
inner join ProductAW p on p.ProductModelID = pmpd.ProductModelID
where pmpd.Culture = 'fr' and p.ProductID = 736

9. Use the SubTotal value in SaleOrderHeader to list orders from the largest to the smallest. For each order show the CompanyName and the SubTotal and the total weight of the order.

select c.CompanyName, s.SubTotal, s.Freight
from CustomerAW c inner join SalesOrderHeader s
on c.CustomerID = s.CustomerID
order by s.SubTotal DESC

10. How many products in ProductCategory 'Cranksets' have been sold to an address in 'London'?

select count(distinct(sd.ProductID)) as totalNumber
from SalesOrderHeader sh inner join Address a
on a.AddressID = sh.ShipToAddressID
inner join SalesOrderDetail sd on sd.SalesOrderID=sh.SalesOrderID
inner join ProductAW p on p.ProductID = sd.ProductID
inner join ProductCategory pc on pc.ProductCategoryID = p.ProductCategoryID
where a.City = 'London' and pc.Name = 'Cranksets'


Hard Questions -- Details and playground can be found HERE

11.For every customer with a 'Main Office' in Dallas show AddressLine1 of the 'Main Office' and AddressLine1 of the 'Shipping' address - if there is no shipping address leave it blank. Use one row per customer.

select c.CompanyName,ma.AddressLine1 'Main Office Address', 

case when s.AddressType = 'Shipping' then sa.AddressLine1 else '' end as 'Shipping Address'
from CustomerAW c, CustomerAddress m, CustomerAddress s, Address ma, Address sa
where c.CustomerID = m.CustomerID and ma.AddressID = m.AddressID and c.CustomerID = s.CustomerID and s.AddressID = sa.AddressID and m.AddressType = 'Main Office' and ma.City = 'Dallas'

 

12. For each order show the SalesOrderID and SubTotal calculated three ways:
A) From the SalesOrderHeader
B) Sum of OrderQty*UnitPrice
C) Sum of OrderQty*ListPrice 

select sh.SalesOrderID,sh.SubTotal, (sd.OrderQty*sd.UnitPrice) as b, (sd.OrderQty*p.ListPrice) as c
from SalesOrderHeader sh, SalesOrderDetail sd,
ProductAW p
where sh.SalesOrderID = sd.SalesOrderID and p.ProductID = sd.ProductID
group by sh.SalesOrderID

13. Show the best selling item by value. 

select p.Name, sum(sd.OrderQty) as 'sales amout'
from SalesOrderDetail sd, ProductAW p
where sd.ProductID = p.ProductID
group by p.ProductID
order by sum(sd.OrderQty) DESC
 

14. Show how many orders are in the following ranges (in $) like the format below:

    RANGE      Num Orders      Total Value
    0-  99
  100- 999
 1000-9999
10000-

select t.range as RANGE, count(t.range) as 'Num Orders', sum(t.SubTotal) as 'Total Value' from (select case
when SubTotal between 0 and 99 then '0-99' when SubTotal between 100 and 999 then '100-999'
when SubTotal between 1000 and 9999 then '1000-9999'
else '10000-' end as range, SubTotal from SalesOrderHeader) t
group by t.range

 THIS might be helpful to solve #14

15. Identify the three most important cities. Show the break down of top level product category against city.