Homework #7


A)  select * from customer;
    select * from invoice;
    select * from invoice_item;


B)  select Phone, LastName from customer;


C)  select Phone, LastName from customer where FirstName='Nikki';


D)  select Phone, DateIn, DateOut from invoice where TotalAmt>100;


E)  select Phone, FirstName from customer where FirstName like 'B%';


F)  select Phone, FirstName from customer where FirstName like '%cat%';


G)  select Phone, FirstName, LastName from customer where Phone like '_23%';


H)  select max(TotalAmt), min(TotalAmt) from invoice;


I)  select avg(TotalAmt) from invoice;


J)  select count (distinct Phone) from customer;


K)  select * from customer group by LastName, FirstName;





L)  select lastName, FirstName, count(*) from customer group by LastName, FirstName;




M)  select FirstName, LastName from customer

    where Phone in (select Phone from invoice where TotalAmt>100)
    order by LastName asc, FirstName desc;


N)  select FirstName, LastName from customer
, invoice
    where customer.Phone=invoice.Phone and invoice.TotalAmt>100
    order by LastName asc, FirstName desc;


O)  select FirstName, LastName from customer

    where Phone in (select Phone from invoice 
          where Number in (select Number from invoice_item
                where Item='Dress Shirt'))
    order by LastName asc, FirstName desc;


P)  select FirstName, LastName from customer
, invoice, invoice_item
    where customer.Phone=invoice.Phone and invoice.Number=invoice_item.Number
          and invoice_item.Item='Dress Shirt'
    order by LastName asc, FirstName desc;


Q)  select FirstName, LastName, TotalAmt from customer
, invoice
    where customer.Phone=invoice.Phone and invoice.Number in
          (select Number from invoice_item where Item='Dress Shirt')
    order by LastName asc, FirstName desc;