Question

2) What were the total rental fees for each member by member last name? (w3schools-> SQL Inner Join& SQL Functions Count, Avg, Sum & SOL Group By)- this requires 2 joins 13) What were the total rental fees for members treport Mem num who total fees were greater than $5-change the output label to FeesMoreThan5S? (w3schools->SQL Inner Join & SQL Functions Count, Avg, Sum& SQL Group By & SQL Having) 14) What was the highest fee paid by each member [report last name and fee]. (w3schools-> SQL Inner Join& SQL Functions Min Max & SQL Group By) 15) What are the movie titles, due dates and return dates for movies that have not been returned? (w3schools-> SQL Inner Join & SQL Null Values) -this requires 2 joins You only need to turn in the SQL statements (not your results or any screen shots -See the next page for some short cut hints -Note on Table Aliases and Fully Qualified Attribute [column] Names For those of you who are constructing these queries in the Oracle command line interface, you are noticing this is a lot of typing. the way we used to have create SQL statements-when we didn't have GUI interfaces), several shortcuts have been developed: To reduce the typing (because, remember this is Shortcut ONE- not needing to type in the fully qualified attribute name Most of what we have seen in the lecture notes and demos are the short version of an attribute name. However, attributes are really known by their fullname: TABLE attributeName for those of you using Access, you have already seen this For example, in this table the real name for the KMEM NUM member's first name is: MEMBERSHIP. Mem Fname case does not matter here-I use the format of all caps for TABLE names and elBack for attribute names-

Answer 1

If you have any doubts, please give me comment...

-- 12

SELECT SUM(DETAIL_FEE) total_rental_fee

FROM MEMBERSHIP M JOIN RENTAL R ON M.MEM_NUM = R.MEM_NUM JOIN DETAILRENTAL D ON R.RENT_NUM = D.RENT_NUM

GROUP BY MEM_LNAME;

-- 13)

SELECT MEM_NUM AS FeeMoreThan5$

FROM RENTAL R JOIN DETAILRENTAL D ON R.RENT_NUM = D.RENT_NUM

GROUP BY MEM_NUM

HAVING SUM(DETAIL_FEE)>5;

-- 14

SELECT MEM_NUM, MAX(DETAIL_FEE)

FROM RENTAL R JOIN DETAILRENTAL D ON R.RENT_NUM = D.RENT_NUM

GROUP BY MEM_NUM;

-- 15

SELECT MOVIE_TITLE, DETAIL_DUEDATE, DETAIL_RETURNDATE

FROM MOVIE M JOIN VIDEO V ON M.MOVIE_NUM = V.MOVIE_NUM JOIN DETAILRENTAL D ON V.VID_NUM = D.VID_NUM

WHERE DETAIL_RETURNDATE IS NULL;