SQL Query Question. Use the PVFC database if you have it.

These are my notes:

RETRIEVE ALL COLUMNS AND ROWS

select * from product_t

select * from employee_t

select * from order_t

RETRIEVE INDIVIDUAL COLUMNS

select productid, productstandardprice, productdescription from product_t

RETRIEVE AND RENAME COLUMNS

select productid as id, productstandardprice as price, productdescription as description from product_t

RETRIEVE ROWS WHERE CRITERIA ARE MET

select productdescription, productstandardprice from product_t where productstandardprice

select employeename, employeeaddress from employee_t where employeestate = 'TN'

select salespersonname, salespersontelephone from salesperson_t where salesterritoryid = 2

USING SIMPLE MATH

select productid, productstandardprice, productstandardprice + 100 as plus100 from product_t

select productid, productstandardprice, productstandardprice * 1.1 as plus10percentmarkup from product_t

USING FUNCTIONS

SUM and AVG only for numeric data types; COUNT, MIN, MAX for any data type

AVG

select avg(productstandardprice) as averageprice from product_t

COUNT

select count(*) as numitems from ordelline_t where orderid = 4

MIN / MAX

select min(paymentamount) as minimumpayment, max(paymentamount) as maximumpayment from payment_t

USING COMPARISON OPERATORS

select orderid, orderdate from order_t where orderdate > '1-jan-2010'

select productdescription, productfinish from product_t where productfinish != 'cherry'

RETRIEVING NULL VALUES

select * from customer_t where customerpostalcode is null

USING TEXT WILDCARDS

select productdescription from product_t where productdescription like '%desk'

select productdescription from product_t where productdescription like '%drawer%'

USING BOOLEAN OPERATORS - COMPOUND WHERE STATEMENTS

select productdescription, productstandardprice

from product_t where productstandardprice > 300 and productstandardprice

select productdescription, productfinish, productstandardprice

from product_t

where productdescription like '%table'

or productdescription like '%bookcase'

select productdescription, productfinish, productstandardprice

from product_t

where productdescription like '%table'

or productdescription like '%bookcase'

and productstandardprice > 175

select productdescription, productfinish, productstandardprice

from product_t

where (productdescription like '%table'

or productdescription like '%bookcase')

and productstandardprice > 175

select productdescription, productstandardprice

from product_t where productstandardprice > 300 and productstandardprice

RETRIEVING ROWS WITH DISTINCT VALUES

select orderid from ordelline_t

select distinct orderid from ordelline_t

RETRIEVING ROWS WITH VALUES IN / NOT IN -- LISTS

select customername, customercity, customerstate from customer_t where customerstate in ('FL', 'TX', 'CA', 'HI')

SORTING RESULTS USING ORDER BY

select customername, customercity, customerstate from customer_t where customerstate in ('FL', 'TX', 'CA', 'HI') order by customerstate, customername

GROUPING RESULTS USING GROUP BY

paired with aggregate functions - COUNT, SUM, AVG, MIN, MAX

select productfinish, avg(productstandardprice) as price from product_t where productfinish in ('cherry', 'pine', 'oak', 'birch') group by productfinish

CONSTRAINING AGGREGATE RESULTS WITH HAVING

constraint on aggregate function - like another where clause

select productfinish, avg(productstandardprice) as price from product_t where productfinish in ('cherry', 'pine', 'oak', 'birch') group by productfinish having avg(productstandardprice) > 500

ROUNDING / CASTING

select productid, productstandardprice, productstandardprice * 1.1 as plus10percentmarkup from product_t

select productid, productstandardprice, cast(productstandardprice * 1.1 as decimal(6,2)) as plus10percentmarkup from product_t

select materialname, materialstandardprice, materialstandardprice * 1.33 as markup, round(materialstandardprice * 1.33, 2) as roundedMarkup from rawmaterial_t

DATES

select getdate()

select employeeid, employeename, employeebirthdate, datediff(year, employeebirthdate, getdate()) as age from employee_t

RETRIEVE ROWS WHERE CRITERIA ARE MET

Retrieve names of customers from New York

select customername, customerstate from customer_t where customerstate = 'NY'

Retrieve product description for products with birch finish

select productdescription, productfinish from product_t where productfinish = 'birch'

USING SIMPLE MATH

Retrieve raw material name, standardprice, and a markup of 33%

select materialname, materialstandardprice, materialstandardprice * 1.33 as markup from rawmaterial_t

USING FUNCTIONS

SUM and AVG only for numeric data types; COUNT, MIN, MAX for any data type

AVG / SUM

Return average payment amount and total of all payments

select avg(paymentamount) as avgpayment, sum(paymentamount) as totalpayments from payment_t

COUNT

Count the number of customers in New Mexico

select count(*) as numcustomers from customer_t where customerstate = 'NM'

MIN / MAX

Retrieve the cost of the least and most expensive raw material types

select min(materialstandardprice), max(materialstandardprice) from rawmaterial_t

Retrieve the birthdate of the oldest employee and the date of the most recent employee hire

select min(employeebirthdate), max(employeedatehired) from employee_t

USING COMPARISON OPERATORS

Retrieve names of employees outside of Tennessee

select employeename from employee_t where employeestate != 'TN'

Retrieve product descriptions for products with standard price less than or equal to 200

select productdescription from product_t where productstandardprice

RETRIEVING NULL VALUES

Retrieve product descriptions for products with a photo

select productdescription from product_t where photo is not null

USING TEXT WILDCARDS

Retrieve customers with 'furniture' in their name

select customername from customer_t where customername like '%furniture%'

USING BOOLEAN OPERATORS - COMPOUND WHERE STATEMENTS

Retrieve names of customers with 'furniture' in their name within the state of New York

select customername, customercity, customerstate from customer_t where customername like '%furniture%' and customerState = 'NY'

Retrieve product description and standard price of products with finish of 'oak' or 'walnut' and price greater than 300

select productdescription, productfinish, productstandardprice from product_t where productfinish = 'oak' or productfinish = 'walnut' and productstandardprice > 300

select productdescription, productfinish, productstandardprice from product_t where (productfinish = 'oak' or productfinish = 'walnut') and productstandardprice > 300

RETRIEVING ROWS WITH DISTINCT VALUES

Retrieve the unique skill ids possessed by employees

select skillid from employeeskills_t

select distinct skillid from employeeskills_t

RETRIEVING ROWS WITH VALUES IN / NOT IN -- LISTS

Retrieve the product descriptions and finishes of products with finish of cherry, pine, or leather

select productdescription, productfinish from product_t where productfinish in ('cherry', 'pine', 'leather')

Retrieve the employee ids of employees with band saw (BS12) or router (RT1) skills

select employeeid, skillid from employeeskills_t where skillid in ('BS12', 'RT1')

SORTING RESULTS USING ORDER BY

Retrieve the order ids and dates of orders between '1/1/2010' and '6/1/2010' in ascending order of date

select orderid, orderdate from order_t where orderdate > '1/1/2010' and orderdate

Retrieve the product descriptions and standard prices of products with price greater than 300 in descending order of price

select productdescription, productstandardprice from product_t where productstandardprice > 300 order by productstandardprice desc

GROUPING RESULTS USING GROUP BY

paired with aggregate functions - COUNT, SUM, AVG, MIN, MAX

Retrieve the states and count of customers from each state

select customerstate, count(customerstate) as count from customer_t group by customerstate

Retrieve the material and average material standard price of ash, walnut, pine, birch raw materials

select material, avg(materialstandardprice) from rawmaterial_t where material in ('ash', 'walnut', 'pine', 'birch') group by material

CONSTRAINING AGGREGATE RESULTS WITH HAVING

constraint on aggregate function - like another where clause

Retrieve the states and count of customers from each state; limit results to states with more than one customer

select customerstate, count(customerstate) as count from customer_t group by customerstate having count(customerstate) > 1

Retrieve the material and average material standard price of ash, walnut, pine, birch raw materials; limit results to materials with average material standard price greater than 60

select material, avg(materialstandardprice) from rawmaterial_t where material in ('ash', 'walnut', 'pine', 'birch') group by material having avg(materialstandardprice) > 60

*********************

SUMMARY TEST QUERY

Retrieve the material and average material standard price of ash, walnut, pine, birch raw materials; limit results to materials with average material standard price greater than 60; display in descending order of average material standard price

select material, avg(materialstandardprice) as avgprice from rawmaterial_t where material in ('ash', 'walnut', 'pine', 'birch') group by material having avg(materialstandardprice) > 60 order by avg(materialstandardprice) desc

SUBQUERIES

Retrieve products and quantities ordered in orders placed between 3/10/2010 and 3/12/2010

First, create a subquery for all orders between 3/10/2010 and 3/12/2010

Then use the extracted order ids to retrieve product ids, product descriptions, and ordered quantities

select product_t.productid, product_t.productdescription, ordelline_t.orderedquantity

from product_t, ordelline_t

where product_t.productid = ordelline_t.productid

and ordelline_t.orderid in

(select order_t.orderid from order_t where order_t.orderDate '3/10/2010')

=============================================

EQUI/INNER/NATURAL JOIN

TWO TABLES

Link salesperson information to orders

Order id, order date, salesperson id, salesperson name

select order_t.orderid, order_t.orderdate, order_t.salespersonid, salesperson_t.salespersonname

from order_t, salesperson_t where order_t.salespersonid = salesperson_t.salespersonid

Link product information to order lines

Order id, product id, ordered quantity, product description

select ordelline_t.orderid, ordelline_t.productid, ordelline_t.orderedquantity, product_t.productdescription

from ordelline_t, product_t where ordelline_t.productid = product_t.productid

THREE TABLES

SALESPERSON INFO WITH CUSTOMERS THEY'VE HELPED PLACE ORDERS

First, link salesperson information to orders

Order id, order date, salesperson id, salesperson name, salesperson state

select order_t.orderid, order_t.orderdate, order_t.salespersonid, salesperson_t.salespersonname

from order_t, salesperson_t where order_t.salespersonid = salesperson_t.salespersonid

Now add customer information

Customer id, customer name, customer state

select order_t.orderid, order_t.orderdate, customer_t.customerid, customer_t.customername, customer_t.customerstate, order_t.salespersonid, salesperson_t.salespersonname, salesperson_t.salespersonstate

from order_t, customer_t, salesperson_t where order_t.customerid = customer_t.customerid and order_t.salespersonid = salesperson_t.salespersonid

HOW MANY ORDERS DID EACH SALESPERSON PLACE IN EACH CUSTOMER STATE?

First, how many orders per salesperson?

Salesperson name, count of orders

select salesperson_t.salespersonname, count(order_t.orderid) as numberorders

from salesperson_t, order_t where salesperson_t.salespersonid = order_t.salespersonid

group by salesperson_t.salespersonid, salesperson_t.salespersonname

Now how many orders per salesperson per customer state?

Add customer and state information

Customer state, salesperson state

select salesperson_t.salespersonname, salesperson_t.salespersonstate, customer_t.customerstate, count(order_t.orderid) as numberorders

from salesperson_t, order_t, customer_t where salesperson_t.salespersonid = order_t.salespersonid and order_t.customerid = customer_t.customerid

group by salesperson_t.salespersonid, salesperson_t.salespersonname, salesperson_t.salespersonstate, customer_t.customerstate

ORDER TOTAL

What is the total amount for each order?

Order id, order date, customer id, order total

select order_t.orderid, order_t.orderdate, order_t.customerid, sum(ordelline_t.orderedquantity * product_t.productstandardprice) as ordertotal

from order_t, ordelline_t, product_t where order_t.orderid = ordelline_t.orderid and ordelline_t.productid = product_t.productid

group by order_t.orderid, order_t.orderdate, order_t.customerid

FOUR TABLES

COMPLETE ORDER LINE ITEMS FOR ORDER ID 4

Customer id, customer name, order id, order date,

product id, ordered quantity, product description, product standard price, order line total

select customer_t.customerid, customer_t.customername, order_t.orderid, order_t.orderdate,

ordelline_t.productid, ordelline_t.orderedquantity, product_t.productdescription, product_t.productstandardprice,

(ordelline_t.orderedquantity * product_t.productstandardprice) as linetotal

from customer_t, order_t, ordelline_t, product_t

where customer_t.customerid = order_t.customerid

and order_t.orderid = ordelline_t.orderid

and ordelline_t.productid = product_t.productid

and order_t.orderid = 4

WHAT IS THE AVERAGE ORDER TOTAL FOR EACH CUSTOMER?

Customer id, customer name, average order total

select customer_t.customerid, customer_t.customername,

(sum(ordelline_t.orderedquantity * product_t.productstandardprice) / count(distinct(order_t.orderid))) as avgorder

from customer_t, order_t, ordelline_t, product_t

where customer_t.customerid = order_t.customerid and order_t.orderid = ordelline_t.orderid and ordelline_t.productid = product_t.productid

group by customer_t.customerid, customer_t.customername

order by customer_t.customerid

Retrieve a list of salespersons and the orders they have each facilitated, including order ID, order date, and order total. Each order facilitated by a salesperson contributes to their total sales. Also provide the contribution of each order to the salesperson's total sales, as a percentage of total sales. Round the percentage of total sales to two digits beyond the decimal point using the 'round' function - round(#,2) where # is the number to round. The query should output the salesperson name, order ID, order date, order total, and percentage of salesperson total sales. Sort the list first in alphabetical order of salesperson name, then by ascending order date, and then with larger order totals listed first.

select salesperson_t.salespersonname, order_t.orderid,

order_t.orderdate,

sum(product_t.productstandardprice * ordelline_t.orderedquantity) as

ordertotal,

round(sum(product_t.productstandardprice *

ordelline_t.orderedquantity) / derivedtable.salespersontotal, 2) as

percentoftotal

from product_t, ordelline_t, order_t, salesperson_t,

(select salesperson_t.salespersonid,

sum(product_t.productstandardprice * ordelline_t.orderedquantity) as

salespersontotal

from product_t, ordelline_t, order_t, salesperson_t

where product_t.productid = ordelline_t.productid and

ordelline_t.orderid = order_t.orderid and order_t.salespersonid =

salesperson_t.salespersonid

group by salesperson_t.salespersonid) as derivedtable

where product_t.productid = ordelline_t.productid and

ordelline_t.orderid = order_t.orderid and order_t.salespersonid =

salesperson_t.salespersonid

and derivedtable.salespersonid = salesperson_t.salespersonid

group by salesperson_t.salespersonname, order_t.orderid,

order_t.orderdate, derivedtable.salespersontotal

order by salesperson_t.salespersonname, order_t.orderdate, ordertotal

desc

Provide a list of the salespeople who have sold to both customers within their state and outside their state. Orders are considered sold within their state when the salesperson state and customer state are the same. Orders are considered sold outside of their state when the salesperson state and customer state differ. For each salesperson, calculate their total sales dollar value sold within their state, total sales dollar value sold outside of their state, and total sales dollar value overall. Also calculate the percentage of their total sales dollar value sold within their state and the percentage of their total sales dollar value sold outside of their state. Sort the results with the best-selling salesperson listed first (with greatest total sales dollar value overall)