Get 2 days of unlimited access
Class Notes (1,000,000)
US (420,000)
U of M (7,000)
IOE (7)
Lecture 13

IOE 373 Lecture 13: IOE373_Lab9


Department
Industrial And Operations Engineering
Course Code
IOE 373
Professor
Luis Garcia- Guzman
Lecture
13

This preview shows half of the first page. to view the full 2 pages of the document.
IOE$373$–$Lab$9$$
Fall$2015$
Querying$in$SQL$to$Create$Tables$for$Analysis$
SQL$Topics$Covered:$$
$
1) IIF$($boolean_expression,$true_value,$false_value$)$
$
2) Using$INNER$JOIN$and$AND$together$
$
3) GROUP$BY$with$Aggregates$
$
To$Do:$$
$
Using$the$Access$File$PuchasesDataBase_Lab9$in$CTools,$complete$the$following$queries.$Then,$submit$
each$of$the$queries$to$CTools$in$a$Word$Document.$$
$
1) You$want$to$analyze$spending$by$payment$type.$In$order$to$do$this,$create$a$SQL$query$which$
gives$you$the$count$of$transactions$between$0$and$10,$the$count$of$transactions$between$10$and$
100,$the$count$of$transactions$between$100$and$1000,$the$count$of$transactions$over$1000,$the$
number$of$purchases,$and$the$total$revenue$for$each$payment$type$in$ascending$order$of$
payment$type.$$
(NumTransactionbyAmountRange)$$
SELECT$paymenttype,$SUM(IIF(0$<=$totalprice$AND$totalprice$<$10,$1,$0))$AS$cnt_0_10_USD,$
SUM(IIF(10$<=$totalprice$AND$totalprice$<$100,1,0))$AS$cnt_10_100USD,$SUM(IIF(100$<=$
totalprice$AND$totalprice$<$1000,1,0))$AS$cnt_100_1000USD,$SUM(IIF(totalprice$>=$1000,1,0))$AS$
cnt_1000USD,$COUNT(*)$AS$cnt,$format(SUM(totalprice),"CURRENCY")$AS$revenue$
FROM$orders$
GROUP$BY$paymenttype$
ORDER$BY$paymenttype;$
2) You$want$to$analyze$the$states$by$median$income.$In$order$to$do$this,$create$a$SQL$query,$which$
gives$you$a$count$of$orders$for$each$state$where$the$total$price$is$greater$than$the$median.$$
(NumOrderGreaterThanMedianByState)$
SELECT$Zipcensus.state,$count(*)$AS$NumOrders$
FROM$Orders$INNER$JOIN$Zipcensus$ON$(Orders.totalprice>Zipcensus.hhumediancashrent)$AND$
(Orders.zipcode$=$Zipcensus.zipcode)$
GROUP$BY$Zipcensus.state;$
$
3) You$want$to$analyze$the$top$performing$states.$In$order$to$do$this,$create$a$SQL$query$which$
shows$the$count,$the$count$of$AMEX$orders,$and$the$percent$of$AMEX$orders$for$the$top$10$
states$by$percent$of$AMEX$orders$that$have$100$or$more$total$orders.$$
(TOP10States_AMEX)$$
SELECT$TOP$10$state,$COUNT(*)$AS$NumOrders,$SUM(IIF(paymenttype$=$"AE",1,0))$AS$
NumAMEX,$SUM(IIF(paymenttype$=$"AE",1,0))/COUNT(*)$AS$AvgPercentAMEX$
FROM$orders$
You're Reading a Preview

Unlock to view full version