Chapter 7 & 8 Lab

ALTER TABLE Movie ADD Producer VARCHAR(50); ALTER TABLE Movie DROP Genre; ALTER TABLE Movie CHANGE Year ReleaseYear SMALLINT;

Write ALTER statements to make the following modifications to the Movie table:

DELETE FROM Horse WHERE ID = 5; DELETE FROM Horse WHERE Breed = 'Holsteiner' OR Breed = 'Paint'; DELETE FROM Horse WHERE BirthDate < '2013-03-13';

Write SQL statements to make the following changes to the Horse table:

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';

Write SQL statements to make the following changes:

SELECT Movie.Title, YearStats.TotalGross FROM Movie LEFT JOIN YearStats ON Movie.Year = YearStats.Year;

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 DISTINCT RatingCode, COUNT(*) AS RatingCodeCount FROM Movie GROUP BY RatingCode ORDER BY RatingCode ASC;

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 Title, Genre FROM Movie WHERE Year = 2020;

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 * FROM Movie;

Write a SQL query to return all data from the Movie table without directly referencing any column names.

UPDATE Movie SET Year = 2022 WHERE Year = 2020;

Write a SQL statement to change the Year value to be 2022 for all movies with a Year value of 2020.

CREATE INDEX idx_year ON Movie (Year);

Write a SQL statement to create an index named idx_year on the Year column of the Movie table.

CREATE TABLE Member ( ID INT UNSIGNED, FirstName VARCHAR(100), MiddleInitial CHAR(1), LastName VARCHAR(100), DateOfBirth DATE, AnnualPledge DECIMAL (8,2) UNSIGNED CHECK(AnnualPledge<=999999.99) );

Write a SQL statement to create the Member table. The Member table will have the following columns:

CREATE TABLE Movie ( Title VARCHAR(30), RatingCode VARCHAR(5), FOREIGN KEY (RatingCode) REFERENCES Rating(RatingCode) );

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.

DROP View MovieView;

Write a SQL statement to delete the view named MovieView from the database.

ALTER TABLE Movie ADD FOREIGN KEY (Year) REFERENCES YearStats(Year);

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.

INSERT INTO Movie (Title, Genre, RatingCode, Year) VALUES ('Pride and Prejudice', 'Romance', 'G', 2005);

Write a SQL statement to insert the indicated data into the Movie table.

ALTER TABLE Movie ADD PRIMARY KEY (ID);

Write a SQL statement to modify the Movie table to make the ID column the primary key.

CREATE TABLE Horse ( ID SMALLINT UNSIGNED AUTO_INCREMENT, RegisteredName VARCHAR(15) NOT NULL, Breed VARCHAR(20) CHECK(Breed IN('Egyptian Arab', 'Holsteiner', 'Quarter Horse', 'Paint', 'Saddlebred')), Height DECIMAL(3,1) CHECK(Height BETWEEN 10 AND 20), BirthDate DATE CHECK (BirthDate >='2015-01-01'), PRIMARY KEY (ID) );

Create a Horse table with the following columns, data types, and constraints. NULL is allowed unless 'not NULL' is explicitly stated.

CREATE TABLE Movie ( ID SMALLINT UNSIGNED, Title VARCHAR(50), Rating CHAR(4), ReleaseDate DATE, Budget DECIMAL(8,2) CHECK(Budget<= 999999.99) );

Create a Movie table with the following columns:

CREATE TABLE Student ( ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 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, Phone CHAR(10) NOT NULL, Email VARCHAR(30) UNIQUE, PRIMARY KEY (ID) );

Create a Student table with the following column names, data types, and constraints:

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 third table, named LessonSchedule, with columns:

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');

Insert the following data into the Horse table:

SELECT RegisteredName, Height, BirthDate FROM Horse WHERE Height BETWEEN 15.0 AND 16.0 OR BirthDate >= '2020-01-01';

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 Title FROM Movie ORDER BY Title ASC;

Write a SQL query to display all Title values in alphabetical order A-Z. Even though A-Z is the default, be sure to include ASC.

SELECT COUNT(*) FROM Movie WHERE Year = 2019;

Write a SQL query to return how many movies have a Year value of 2019.

ALTER TABLE Movie ADD Score DECIMAL (3,1);

Write a SQL statement to add the Score column to the Movie table.

CREATE VIEW MyMovies AS SELECT Title, Genre, Year FROM Movie;

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.

DELETE FROM Movie WHERE ID = 3;

Write a SQL statement to delete the row with the ID value of 3 from the Movie table.