Ad Class Activity.docx

1 select CustomerName from Customer where CustomerName like '*Four*'

Query1 CustomerName Deerfield's Four Seasons 2 select CustomerNum, CustomerName, Street, CreditLimit from Customer CustomerName ORDER BY CustomerName

Query1 CustomerNum CustomerName Street CreditLimit 842 All Season 28 Lakeview $7,500.00 148 Al's Appliance and Sport 2837 Greenway $7,500.00 462 Bargains Galore 3829 Central $10,000.00 282 Brookings Direct 3827 Devon $10,000.00 725 Deerfield's Four Seasons 282 Columbia $7,500.00 356 Ferguson's 382 Wildwood $7,500.00 608 Johnson's Department Store 372 Oxford $10,000.00 524 Kline's 838 Ridgeland $15,000.00 687 Lee's Sport and Appliance 282 Evergreen $5,000.00 408 The Everything Shop 1828 Raven $5,000.00 3 select CustomerNum, CustomerName, Street, CreditLimit from Customer ORDER BY CreditLimit DESC

Query1 CustomerNum CustomerName Street CreditLimit 524 Kline's 838 Ridgeland $15,000.00 608 Johnson's Department Store 372 Oxford $10,000.00 462 Bargains Galore 3829 Central $10,000.00 282 Brookings Direct 3827 Devon $10,000.00 842 All Season 28 Lakeview $7,500.00 725 Deerfield's Four Seasons 282 Columbia $7,500.00 356 Ferguson's 382 Wildwood $7,500.00 148 Al's Appliance and Sport 2837 Greenway $7,500.00 687 Lee's Sport and Appliance 282 Evergreen $5,000.00 408 The Everything Shop 1828 Raven $5,000.00

4 select COUNT(*) from Part where Class = "HW"

Query1 Expr1000 3 5 select count(*), sum(Balance) from Customer

Query1 Expr1000 Expr1001 10 RM47,651.75 6 select count(*) as CustomerCount, sum(Balance) as BalanceTotal from Customer

Query1 CustomerCount BalanceTotal 10 RM47,651.75 7 select OrderNum from OrderLine where PartNum in (select PartNum from Part where Warehouse = '3')

Query1 OrderNum 21608 21610 21614 8 select RepNum, Count(*), AVG(balance) from Customer group by RepNum order by RepNum

Query1 RepNum Expr1001 Expr1002 20 3 RM9,177.67 35 4 RM2,203.94 65 3 RM3,767.67

9 select RepNum, Count(*), AVG(balance) from Customer group by RepNum having Count(*) < 4 order by RepNum

Query1 RepNum Expr1001 Expr1002 20 3 RM9,177.67 65 3 RM3,767.67 10 select Customer.RepNum, Rep.FirstName, Rep.LastName, Customer.CustomerName, Customer.CustomerNum from Customer, Rep where Customer.RepNum = Rep.RepNum order by CustomerNum

Query1 RepNum FirstName LastName CustomerName CustomerNum 20 Valerie Kaiser Al's Appliance and Sport 148 35 Richard Hull Brookings Direct 282 65 Juan Perez Ferguson's 356 35 Richard Hull The Everything Shop 408 65 Juan Perez Bargains Galore 462 20 Valerie Kaiser Kline's 524 65 Juan Perez Johnson's Department Store 608 35 Richard Hull Lee's Sport and Appliance 687 35 Richard Hull Deerfield's Four Seasons 725 20 Valerie Kaiser All Season 842