My tables are as follows: Customer Table cust_id (PK), first_name, last_name, ad

Question

My tables are as follows:

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

Transactions table

trans_id (PK), rental date, return_date, rental_fee, cust_id (FK), movie_id (FK)

I need help creating syntax for the following:

1. Join the MOVIE and TRANSACTION tables and display a list of movies that have been rented. Include the following columns: Title, rating, rentaldate, return date.

2. Join the CUSTOMER and TRANSACTION tables and display a list of customers and how much they spent in movie rentals. Include the following tables: Name, Address, CreditCard, Rental fee.

3. Creating a VIEW by joining the MOVIE and TRANSACTION tabes and dipslaying a list of movies that not been rented (hint: left join, where clause is null) Include the following: title, rating

4. Creating a VIEW by joining the MOVIE and TRANSACTION tables and displaying a list of movies along with the quantites available for rent. Include the following columns: title, qty available

5. run 2 queries of your choice using 1 or 2 sub queries.

Thanks so much.

Explanation / Answer

Answer:

1. Join the MOVIE and TRANSACTION tables and display a list of movies that have been rented. Include the following columns: Title, rating, rentaldate, return date.

Select M.title as Title,
M.rating as Rating,
T.rentaldate as Rental_date,
T.returndate as Return_date
FROM
MOVIE M
INNER JOIN TRANSACTIONS T ON M.movie_ID = T.movie_ID

2. Join the CUSTOMER and TRANSACTION tables and display a list of customers and how much they spent in movie rentals. Include the following tables: Name, Address, CreditCard, Rental fee.
Select
C.first_name AS Name,
C.address_1 AS Address,
C.cc_number AS CreditCard,
T.rental_fee AS [Rental fee]
FROM Customer C
INNER JOIN TRANSACTIONS T ON C.cust_id = T.cust_id

3. Creating a VIEW by joining the MOVIE and TRANSACTION tabes and dipslaying a list of movies that not been rented (hint: left join, where clause is null) Include the following: title, rating

CREATE VIEW [V_GetNotRentedMovies] AS
Select M.title as Title,
M.rating as Rating
FROM
MOVIE M
LEFT JOIN TRANSACTIONS T ON M.movie_ID = T.movie_ID
Where T.movie_id is null

4. Creating a VIEW by joining the MOVIE and TRANSACTION tables and displaying a list of movies along with the quantites available for rent. Include the following columns: title, qty available

CREATE VIEW [V_GetMoviesQtyAvailableForRent] AS
Select M.title as Title,
M.qty as AvailableQuantity
FROM
MOVIE M
LEFT JOIN TRANSACTIONS T ON M.movie_ID = T.movie_ID
Where T.movie_id is null

5. run 2 queries of your choice using 1 or 2 sub queries.

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)

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.