Movie_category(CATEGORY_CODE, category_name)
Client(CLIENT_NUM, client_lname, client_fname, client_phone, client_credit_rtg, client_fave_cat)
foreign key (client_fave_cat) references movie_category(category_code)
CS 325 - Fall 2019 - Homework 9
p.6 of 7
Movie(MOVIE_NUM, movie_title, movie_director_lname, movie_yr_released, movie_rating, category_code)
foreign key(category_code) references movie_category
Video(VID_ID, vid_format, vid_purchase_date, vid_rental_price, movie_num)
foreign key (movie_num) references movie
Rental(RENTAL_NUM, client_num, vid_id, date_out, date_due, date_returned)
foreign key (client_num) references client,
foreign key(vid_id) references video
Problem 5-2
Write a query that uses JUST the counter_client_info view to project all of the contents of the counter_client_info view, displaying the rows in order of client last name. Then write another query that uses JUST the counter_client_info view, but this time projecting the fave_category column first and then the client last name column, now displaying the rows in order of the name of the client's favorite category.
Problem 5-3
Drop and create a view called movie_list of the movie and movie_category tables, containing only the category name, movie rating, and movie title for each movie.
Problem 5-4
Write a query that uses JUST the movie_list view to project all of the contents of the movie_list view, displaying the rows in order of category name, with a secondary ordering by movie rating, and a third ordering by movie title.
Problem 5-5
Now, using ONLY the view movie_list, write a query projecting two columns: the name of a movie category, and the number of movies in that category, giving this second column the column heading CATEGORY_QUANT, and displaying the rows in order of decreasing number of movies.
Problem 5-6
Drop and create a new table (not already used in movies-create.sql nor in lab) that has at least three columns. Then, drop and create a sequence suitable for use in setting the primary key of this new table. Have your sequence start at a value other than 1.
Problem 5-7
Insert at least 3 rows into your table from Problem 5-6, using your sequence from Problem 5-6 to set each primary key.