Project 6

Required Queries:

* Create a query that includes at least two INNER JOINS (i.e. spans at least three tables), a search criteria, and orders the resulting data. * >select M.MID, M.MFName * >from MEMBER as M * >inner join LINEITEM as LI on LI.MID=M.MID * >inner join ITEM as I on I.IID=LI.IID * >inner join DVD as D on D.IID=I.IID; * This shows which members have checked out DVDs

* Create a query that includes a calculation (e.g. average, sum, etc.). * >Select M.MID, M.MFName, count(B.IID) as “Books Checked Out” * >from MEMBER as M * >inner join LINEITEM as LI on LI.MID=M.MID * >inner join ITEM as I on I.IID=LI.IID * >inner join BOOK as B on B.IID=I.IID; * This query shows how many books a member has checked out

* Create a query that includes at least one RIGHT or LEFT outer join. * >select MEMBER.MID, MEMBER.MFName, LIBRARIAN.LFName * >from MEMBER * >left outer join LINEITEM on LINEITEM.MID=MEMBER.MID * >left outer join LIBRARIAN on LINEITEM.LID=LIBRARIAN.LID * >order by MEMBER.MID; * This query uses a left join to show the relations between which librarians have checked out which members

* Create a query that uses a GROUP BY to perform a calculation on information spanning at least two tables.

* Create a query that includes a GROUP BY, but selects items from the results of the GROUP BY query and lists them in order (e.g. using our COOKIE example, list everyone who has spent over $100 in total).

* Create a query that includes a nested query.