HOMEWORK ASSIGNMENT 2

docx

School

Columbia University *

*We aren’t endorsed by this school

Course

5310

Subject

Computer Science

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by DeaconExploration13239

-- HOMEWORK ASSIGNMENT 2 /* * NOTES: * * - Type your SQL statements between the START and END tags for each * question, as shown in the example. Do not alter this template .sql file * in any other way other than adding your answers. Do not delete the * START/END tags. The .sql file you submit will be validated before * grading and will not be graded if it fails validation due to any * alteration of the commented sections. * * - Our course is using PostgreSQL. We grade your assignments in PostgreSQL. * You risk losing points if you prepare your SQL queries for a different * database system (MySQL, MS SQL Server, Oracle, etc). * * - It is highly recommended that you insert additional, appropriate data * to test the results of your queries. You do not need to include your * sample data in your answers. * * - Make sure you test each one of your answers. If a query returns an * error it will earn no points. * */ /* * EXAMPLE * ------- * * Provide the SQL statement that returns all attributes and tuples from * a relation named "table1". * */ -- START EXAMPLE -- SELECT * FROM table1; -- END EXAMPLE -- -------------------------------------------------------------------------------

/* * QUESTION 1 (8 points) * --------------------- * * Provide the SQL statement that creates a table named "movies" with the * following attributes: * * - movie_id : fixed length character string with length of 8 * - title : variable length character string with of length 125 * - director : variable length character string of length 80 * - genres : variable length character string of length 100 * - year: (release year) small-range integer * - revenue_generated: typical integer * - short_description : variable length character string of length 500 * - runtime : small integer (minutes) * - rating : fixed point number with 3 digits, 1 decimals * * No need to implement integrity constraints. * */ -- START ANSWER 1 -- CREATE TABLE movies ( movie_id char(8) NOT NULL, title varchar(125), director varchar(80), genres varchar(100), year SMALLINT, revenue_generated INT, short_description varchar(500), runtime SMALLINT, rating DECIMAL(3,1), PRIMARY KEY (movie_id) ); -- END ANSWER 1 -- ------------------------------------------------------------------------------- /*

* QUESTION 2 (8 points) * -------------------- * * Provide the SQL statement that populates the "movies" relation with values as * shown in the following table. (Note: you must insert data with a single * statement, not multiple INSERT statements) * * - movie_id : 29 * - title : Top Gun * - director : Joseph Kosinski * - genres : Action Film * - year: 2022 * - revenue_generated: 1.35 * - short_description : After more than thirty years of service as one of the Navy’s top aviators, * Pete “Maverick” Mitchell is where he belongs, pushing the envelope as a courageous test pilot. * and dodging the advancement in rank that would ground him. * - runtime : 2 hours 11 minutes * - rating : 9.9 * * * - movie_id : 30 * - title : Rya and The Last Dragon * - director : Don Hall * - genres : Animated Action * - year: 2021 * - revenue_generated: 54.7 * - short_description : Long ago, in the fantasy world of Kumandra, humans and dragons lived together in harmony. * But when sinister monsters known as the Druun threatened the land, the dragons sacrificed themselves to save humanity. * Now, 500 years later, those same monsters have returned and it's up to a lone warrior, Raya, to track down the last dragon * in order to finally stop the Druun for good. However, along her journey, she'll learn that it'll take more than dragon. * - runtime : 1 hour 48 minutes * - rating : 8.7 * * * - movie_id : 31 * - title : Soul * - director : Peter Docter * - genres : Animated * - year: 2020

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

* - revenue_generated: 121.1 * - short_description : Joe is a middle-school band teacher whose life hasn't quite gone the way he expected. * His true passion is jazz -- and he's good. But when he travels to another realm to help someone find their passion, * he soon discovers what it means to have soul. * - runtime : 1 hour 40 minutes * - rating : 8.9 */ -- START ANSWER 2 -- INSERT INTO movies (movie_id, title, director, genres, year, revenue_generated, short_description, runtime, rating) VALUES ('29', 'Top Gun', 'Joseph Kosinski', 'Action Film', 2022, 1.35, 'After more than thirty years of service as one of the Navy’s top aviators, Pete “Maverick” Mitchell is where he belongs, pushing the envelope as a courageous test pilot and dodging the advancement in rank that would ground him.', 131, 9.9), ('30', 'Rya and The Last Dragon', 'Don Hall', 'Animated Action', 2021, 54.7, 'Long ago, in the fantasy world of Kumandra, humans and dragons lived together in harmony. But when sinister monsters known as the Druun threatened the land, the dragons sacrificed themselves to save humanity. Now, 500 years later, those same monsters have returned and it''s up to a lone warrior, Raya, to track down the last dragon in order to finally stop the Druun for good. However, along her journey, she''ll learn that it''ll take more than dragon.', 108, 8.7), ('31', 'Soul', 'Peter Docter', 'Animated', 2020, 121.1, 'Joe is a middle-school band teacher whose life hasn''t quite gone the way he expected. His true passion is jazz -- and he''s good. But when he travels to another realm to help someone find their passion, he soon discovers what it means to have soul.', 100, 8.9); -- END ANSWER 2 -- ------------------------------------------------------------------------------- /* * QUESTION 3 (6 points) * --------------------- * * Provide the SQL statement that returns all attributes and tuples from relation * "movies", with the genre "Action" that are shorter than two and a half hours. * * ---- Remember you may need to create additional data in order to test your query ---- *

*/ -- START ANSWER 3 -- SELECT * FROM movies WHERE genres = 'Action' AND runtime < 150 ; -- END ANSWER 3 -- ------------------------------------------------------------------------------- /* * QUESTION 4 (6 points) * --------------------- * * * Assume that box office revenue attribute in the "movies" relation * is in million $. Provide the SQL statement that returns the movie_id, title, director, * genre, year as well as revenue_generated in billion $ * (note: 1 billion = 1000 million) for the movies where the box office revenue is more than half a billion. Rename revenue_generated to revenue_in_billion. * */ -- START ANSWER 4 -- SELECT movie_id, title, director, genres, year, revenue_generated/1000 AS revenue_in_billion FROM movies WHERE revenue_generated > 500 ; -- END ANSWER 4 -- ------------------------------------------------------------------------------- /* * QUESTION 5 (6 points) * --------------------- * * Provide the SQL statement that returns the title and rating of all movies * with the genre "Animated". *

*/ -- START ANSWER 5 -- SELECT title, rating FROM movies WHERE genres = 'Animated' ; -- END ANSWER 5 -- ------------------------------------------------------------------------------- /* * QUESTION 6 (8 points) * --------------------- * * Provide the SQL statement that creates a relation named "actors" with the * following attributes: * * - id : fixed length character string with length of 10 * - name : variable length character string of length 100 * - dob (date of birth) : date only (no time of day) * - nationality : variable length character string of length 40 * * No need to implement integrity constraints. * * Note: You may insert sample data for actors to test your queries, we do not * need to check your sample values, do not provide them with your answer. * */ -- START ANSWER 6 -- CREATE TABLE actors ( id char(10) NOT NULL, name varchar(100), dob DATE, nationality varchar(40), PRIMARY KEY (id) ); -- END ANSWER 6 --

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

------------------------------------------------------------------------------- /* * QUESTION 7 (8 points) * --------------------- * * Provide the SQL statements that alter relation "movies" adding an attribute * called "actor_id" (char) as well as defining a foreign key on this attribute * that references the id attribute from the "actors" relation. (note: adding * the new attribute will result in the existing records in the table to have * a NULL value for actor_id. It is up to you to insert meaningful values in * order to test your queries, we do not need to see/check these values.) * */ -- START ANSWER 7 -- ALTER TABLE movies ADD COLUMN actor_id char(10); ALTER TABLE movies ADD CONSTRAINT fk_actor FOREIGN KEY (actor_id) REFERENCES actors(id) ; -- END ANSWER 7 -- ------------------------------------------------------------------------------- /* * QUESTION 8 (6 points) * --------------------- * * Provide the SQL statement that returns the title, director, year, * actor_id, and name of actors of all movies released in 2021. * */ -- START ANSWER 8 -- SELECT m.title, m.director, m.year, m.actor_id, a.name FROM movies m LEFT JOIN actors a ON m.actor_id = a.id

WHERE m.year = 2021 ; -- END ANSWER 8 -- ------------------------------------------------------------------------------- /* * QUESTION 9 (12 points) (Read closely, this requires trhee seperate queries) * --------------------- * * * 1. Provide the SQL statement that alters relation "movies" adding an attribute * called "rated" (fixed length character string of length 6). * 2. Provide a single SQL statement to update rated = 'PG' for movie 'SOUL' * 3. Provide a single SQL statement to update rated = 'PG13' for movies 'TOP GUN' and 'Rya and The Last Dragon' * */ -- START ANSWER 9 -- ALTER TABLE movies ADD COLUMN rated char(6); UPDATE movies SET rated = 'PG' WHERE title = 'Soul'; UPDATE movies SET rated = 'PG-13' WHERE title IN ('Top Gun', 'Rya and The Last Dragon') ; -- END ANSWER 9 -- ------------------------------------------------------------------------------- /* * QUESTION 10 (6 points) * ---------------------- * * Provide the SQL statement that returns the movie_id, title, genre and revenue

* of highest revenue generating movie for each genre. (hint: use GROUP BY) * */ -- START ANSWER 10 -- SELECT m.movie_id, m.title, m.genres, m.revenue_generated FROM movies m JOIN (SELECT genres, MAX(revenue_generated) AS max_revenue FROM movies GROUP BY genres) AS subquery ON m.genres = subquery.genres AND m.revenue_generated = subquery.max_revenue; -- END ANSWER 10 -- ------------------------------------------------------------------------------- /* * QUESTION 11 (6 points) * ---------------------- * * Provide the SQL statement that returns the genre, rated and total number of * movies for each genre. * */ -- START ANSWER 11 -- SELECT genres, rated, COUNT(*) AS number_of_movies FROM movies GROUP BY genres, rated; -- END ANSWER 11 -- ------------------------------------------------------------------------------- /* * QUESTION 12 (6 points) * ---------------------- * * Provide the SQL statement that returns the average movie revenue generated per director. * * - Remember you may need to add data in order to test your query, you do not need to provide

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

* - the any additional data you have added when submitting. */ -- START ANSWER 12 -- SELECT director, AVG(revenue_generated) AS avg_revenue FROM movies GROUP BY (director); -- END ANSWER 12 -- ------------------------------------------------------------------------------- /* * QUESTION 13 (6 points) * ---------------------- * * Provide the SQL statement that returns the title, genre and release date of * all "Sci-Fi" movies. * * - Remember you may need to add data in order to test your query, you do not need to provide * - the any additional data you have added when submitting. */ -- START ANSWER 13 -- SELECT title, genres, year AS release_date FROM movies WHERE genres = 'Sci-Fi'; -- END ANSWER 13 -- ------------------------------------------------------------------------------- /* * QUESTION 14 (4 points) * ---------------------- * * Provide the SQL statement that deletes all movies with a genre "Animated". * */

-- START ANSWER 14 -- DELETE FROM movies WHERE genres = 'Animated'; -- END ANSWER 14 -- ------------------------------------------------------------------------------- /* * QUESTION 15 (4 points) * ---------------------- * * Provide the SQL statement that removes the "actors" relation from the * database. * */ -- START ANSWER 15 -- ALTER TABLE movies DROP CONSTRAIN fk_actor; DROP TABLE actors; -- END ANSWER 15 -- -------------------------------------------------------------------------------

HOMEWORK ASSIGNMENT 2

Related Documents