I have to run ANY 2 queries with sub queries based on these tables: Customer Tab
ID: 3825886 • Letter: I
Question
I have to run ANY 2 queries with sub queries based on these tables:
Customer Table
cust_id (PK), first_name, last_name, address_1, address_2, cc_number
Movie Table
movie_ID (PK), title, release_date, rental, rating, qty
Transaction table
trans_id (PK), rental date, return_date, rental_fee, cust_id (FK), movie_id (FK)
I've tried these two to no avail:
Query 1: ( To get All highest rated movies)
Select title FROM
Movie Where rating = ( Select top 1 M.rating as Rating,
FROM
MOVIE M
INNER JOIN TRANSACTIONS T ON M.movie_ID = T.movie_ID
Order By M.rating DESC)
Query 2: (To get all customer paid maximum rented fees)
first_name AS Name
From CUSTOMER
Where rental_fee = (Select top 1
T.rental_fee AS [Rental fee]
FROM Customer C
INNER JOIN TRANSACTIONS T ON C.cust_id = T.cust_id
Order By T.rental_fee DESC)
Thanks!
Explanation / Answer
By using MAX Function solve both the query...
Query 1: ( To get All highest rated movies)
Select title FROM
Movie Where rating = ( Select max(M.rating )as Rating,
FROM
MOVIE M
INNER JOIN TRANSACTIONS T ON M.movie_ID = T.movie_ID
Order By M.rating DESC);
Query 2: (To get all customer paid maximum rented fees)
first_name AS Name
From CUSTOMER
Where rental_fee = (Select max(T.rental_fee) AS [Rental fee]
FROM Customer C
INNER JOIN TRANSACTIONS T ON C.cust_id = T.cust_id
Order By T.rental_fee DESC);
Thanks...