D427
Create a Movie table with the following columns: ID - positive integer with maximum value of 50,000 Title - variable-length string with up to 50 characters Rating - fixed-length string with 4 characters ReleaseDate - date Budget - decimal value representing a cost of up to 999,999 dollars, with 2 digits for cents
CREATE TABLE Movie( ID SMALLINT Unsigned CHECK (ID > 0 AND ID <= 50000), Title VARCHAR(50), Rating CHAR(4), ReleaseDate DATE, Budget DECIMAL(8,2) );
The Rating table has the following columns: RatingCode—variable-length string, primary key RatingDescription—variable-length string The Movie table should have the following columns: Title—variable-length string, maximum 30 characters RatingCode—variable-length string, maximum 5 characters Write a SQL statement to create the Movie table. Designate the RatingCode column in the Movie table as a foreign key to the RatingCode column in the Rating table.
CREATE TABLE Movie( Title VARCHAR(30), RatingCode VARCHAR(5), FOREIGN KEY (RatingCode) REFERENCES Rating(RatingCode) );
The Movie table has the following columns: ID - positive integer Title - variable-length string Genre - variable-length string RatingCode - variable-length string Year - integer Write ALTER statements to make the following modifications to the Movie table: Add a Producer column with VARCHAR data type (max 50 chars). Remove the Genre column. Change the Year column's name to ReleaseYear, and change the data type to SMALLINT.
ALTER TABLE Movie ADD Producer VARCHAR(50), DROP COLUMN Genre, CHANGE Year ReleaseYear SMALLINT;
The Movie table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer A new column must be added to the Movie table: Column name: Score Data type: decimal(3,1) Write a SQL statement to add the Score column to the Movie table.
ALTER TABLE Movie ADD Score DECIMAL(3,1);
The Movie table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer Write a SQL statement to create an index named idx_year on the Year column of the Movie table.
CREATE INDEX idx_year ON Movie (Year);
Create a Horse table with the following columns, data types, and constraints. NULL is allowed unless 'not NULL' is explicitly stated. ID - integer with range 0 to 65535, auto increment, primary key RegisteredName - variable-length string with max 15 chars, not NULL Breed - variable-length string with max 20 chars, must be one of the following: Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred Height - number with 3 significant digits and 1 decimal place, must be ≥ 10.0 and ≤ 20.0 BirthDate - date, must be ≥ Jan 1, 2015
CREATE TABLE Horse ( ID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, RegisteredName VARCHAR(15) NOT NULL, Breed VARCHAR(20) CHECK (Breed IN ('Egyptian Arab', 'Holsteiner', 'Quarter Horse', 'Paint', 'Saddlebred')), Height DECIMAL(3,1) CHECK (Height >= 10.0 AND Height <= 20.0), BirthDate DATE CHECK (BirthDate >= '2015-01-01') );
The Member table will have the following columns: ID—positive integer FirstName—variable-length string with up to 100 characters MiddleInitial—fixed-length string with 1 character LastName—variable-length string with up to 100 characters DateOfBirth—date AnnualPledge—positive decimal value representing a cost of up to $999,999, with 2 digits for cents Write a SQL statement to create the Member table.
CREATE Table Member( ID INT UNSIGNED, FirstName VARCHAR(100), MiddleInitial CHAR(1), LastName VARCHAR(100), DateOfBirth DATE, AnnualPledge DECIMAL(8,2) UNSIGNED );
The Movie table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer Write a SQL statement to create a view named MyMovies that contains the Title, Genre, and Year columns for all movies. Ensure your result set returns the columns in the order indicated.
CREATE VIEW MyMovies AS SELECT Title, Genre, Year FROM Movie;
The Horse table has the following columns: ID - integer, auto increment, primary key RegisteredName - variable-length string Breed - variable-length string Height - decimal number BirthDate - date Delete the following rows: Horse with ID 5. All horses with breed Holsteiner or Paint. All horses born before March 13, 2013.
DELETE FROM Horse WHERE ID = 5; DELETE FROM Horse WHERE Breed IN ('Holsteiner', 'Paint'); DELETE FROM Horse WHERE BirthDate < '2013-03-13';
The Movie table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer Write a SQL statement to delete the row with the ID value of 3 from the Movie table.
DELETE FROM Movie WHERE ID = 3;
The Horse table has the following columns: ID - integer, auto increment, primary key RegisteredName - variable-length string Breed - variable-length string, must be one of the following: Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred Height - decimal number, must be between 10.0 and 20.0 BirthDate - date, must be on or after Jan 1, 2015 Insert the following data into the Horse table: RegisteredName | Breed | Height | BirthDate Babe | Quarter Horse | 15.3 | 2015-02-10 Independence | Holsteiner | 16.0 | 2017-03-13 Ellie | Saddlebred | 15.0 | 2016-12-22 NULL | Egyptian Arab | 14.9 | 2019-10-12
INSERT INTO Horse (RegisteredName, Breed, Height, BirthDate) VALUES ('Babe', 'Quarter Horse', 15.3, '2015-02-10'), ('Independence', 'Holsteiner', 16.0, '2017-03-13'), ('Ellie', 'Saddlebred', 15.0, '2016-12-22'), (NULL, 'Egyptian Arab', 14.9, '2019-10-12');
The Movie table has the following columns: ID—integer, primary key, auto-increment Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer The following data needs to be added to the Movie table: Title | Genre | RatingCode | Year Pride and Prejudice | Romance | G | 2005 Write a SQL statement to insert the indicated data into the Movie table.
INSERT INTO Movie (Title, Genre, RatingCode, Year) VALUES ('Pride and Prejudice', 'Romance', 'G', 2005);
The Movie table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer Write a SQL query to return how many movies have a Year value of 2019.
SELECT COUNT(*) FROM Movie WHERE Year = 2019;
The Movie table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer Write a SQL query to output the unique RatingCode values and the number of movies with each rating value from the Movie table as RatingCodeCount. Sort the results by the RatingCode in alphabetical order A-Z. Ensure your result set returns the columns in the order indicated.
SELECT DISTINCT RatingCode, COUNT(*) AS RatingCodeCount FROM Movie GROUP BY RatingCode ORDER BY RatingCode ASC;
A database has a view named MovieView. Write a SQL statement to delete the view named MovieView from the database.
DROP VIEW MovieView;
The database contains a table named Movie. Write a SQL query to return all data from the Movie table without directly referencing any column names.
SELECT * FROM Movie;
The Movie table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer Write a SQL query to display all Title values in alphabetical order A-Z.
SELECT Title FROM Movie ORDER BY Title ASC;
The Horse table has the following columns: ID - integer, primary key RegisteredName - variable-length string Breed - variable-length string Height - decimal number BirthDate - date Write a SELECT statement to select the registered name, height, and birth date for only horses that have a height between 15.0 and 16.0 (inclusive) or have a birth date on or after January 1, 2020.
SELECT RegisteredName, Height, Birthdate FROM Horse WHERE (Height BETWEEN 15.0 AND 16.0) OR (BirthDate >= '2020-01-01');
The Movie table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer Write a SQL query to retrieve the Title and Genre values for all records in the Movie table with a Year value of 2020. Ensure your result set returns the columns in the order indicated.
SELECT Title, Genre FROM Movie WHERE Year = 2020;
The Movie table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer The YearStats table has the following columns: Year—integer TotalGross—bigint unsigned Releases—integer Write a SQL query to display both the Title and the TotalGross (if available) for all movies. Ensure your result set returns the columns in the order indicated.
SELECT Title, TotalGross FROM Movie LEFT JOIN YearStats ON Movie.Year = YearStats.Year;
Refer to the actor table of the Sakila database. The table in this lab has the same columns and data types but fewer rows. Start a transaction and: Insert a new actor with values 999, 'NICOLE', 'STREEP', '2021-06-01 12:00:00' Set a SAVEPOINT. Delete the actor with first name 'CUBA'. Select all actors. Roll back to the savepoint. Select all actors a second time. The actor with first name 'CUBA' should appear in the second SELECT but not the first.
START TRANSACTION; INSERT INTO actor VALUES (999, 'NICOLE', 'STREEP', '2021-06-01 12:00:00'); SAVEPOINT my_savepoint; DELETE FROM actor WHERE first_name = 'CUBA'; SELECT * FROM actor; ROLLBACK TO SAVEPOINT my_savepoint; SELECT * FROM actor;
The Horse table has the following columns: ID - integer, auto increment, primary key RegisteredName - variable-length string Breed - variable-length string, must be one of the following: Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred Height - decimal number, must be ≥ 10.0 and ≤ 20.0 BirthDate - date, must be ≥ Jan 1, 2015 Make the following updates: 1. Change the height to 15.6 for horse with ID 2. 2. Change the registered name to Lady Luck and birth date to May 1, 2015 for horse with ID 4. 3. Change every horse breed to NULL for horses born on or after December 22, 2016.
UPDATE Horse SET Height = 15.6 WHERE ID = 2; UPDATE Horse SET RegisteredName = 'Lady Luck', BirthDate = '2015-05-01' WHERE ID = 4; UPDATE Horse SET Breed = NULL WHERE BirthDate >= '2016-12-22';
The Movie table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer Write a SQL statement to update the Year value to be 2022 for all movies with a Year value of 2020.
UPDATE Movie SET Year = 2022 WHERE Year = 2020;
The Movie table has the following columns: ID—integer, primary key Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer The YearStats table has the following columns: Year—integer TotalGross—bigint unsigned Releases—integer Write a SQL statement to designate the Year column in the Movie table as a foreign key to the Year column in the YearStats table.
ALTER TABLE Movie ADD FOREIGN KEY (Year) REFERENCES YearStats(Year);
The Movie table has the following columns: ID—integer Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer Write a SQL statement to modify the Movie table to make the ID column the primary key.
ALTER TABLE Movie ADD PRIMARY KEY (ID);
The database contains a Horse table, with columns: ID - integer, primary key RegisteredName - variable-length string The database contains a Student table, with columns: ID - integer, primary key FirstName - variable-length string LastName - variable-length string Create a third table, named LessonSchedule, with columns: HorseID - integer with range 0 to 65 thousand, not NULL, foreign key references Horse(ID) StudentID - integer with range 0 to 65 thousand, foreign key references Student(ID) LessonDateTime - date/time, not NULL Primary key is (HorseID, LessonDateTime) If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically. If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically.
CREATE TABLE Horse ( ID SMALLINT UNSIGNED AUTO_INCREMENT, RegisteredName VARCHAR(15), PRIMARY KEY (ID) ); CREATE TABLE Student ( ID SMALLINT UNSIGNED AUTO_INCREMENT, FirstName VARCHAR(20), LastName VARCHAR(30), PRIMARY KEY (ID) ); CREATE TABLE LessonSchedule ( HorseID SMALLINT UNSIGNED NOT NULL, StudentID SMALLINT UNSIGNED, LessonDateTime DATETIME NOT NULL, PRIMARY KEY (HorseID, LessonDateTime), FOREIGN KEY (HorseID) REFERENCES Horse(ID) ON DELETE CASCADE, FOREIGN KEY (StudentID) REFERENCES Student(ID) ON DELETE SET NULL );
Create a Student table with the following column names, data types, and constraints: ID - integer with range 0 to 65 thousand, auto increment, primary key FirstName - variable-length string with max 20 chars, not NULL LastName - variable-length string with max 30 chars, not NULL Street - variable-length string with max 50 chars, not NULL City - variable-length string with max 20 chars, not NULL State - fixed-length string of 2 chars, not NULL, default "TX" Zip - integer with range 0 to 16 million, not NULL Phone - fixed-length string of 10 chars, not NULL Email - variable-length string with max 30 chars, must be unique
CREATE TABLE Student ( ID SMALLINT Unsigned AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(30) NOT NULL, Street VARCHAR(50) NOT NULL, City VARCHAR(20) NOT NULL, State CHAR(2) NOT NULL DEFAULT 'TX', Zip MEDIUMINT UNSIGNED NOT NULL CHECK (Zip >= 0 AND Zip <= 16000000), Phone CHAR(10) NOT NULL, Email VARCHAR(30) UNIQUE );
