Ch 7 Labs

Question 1

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.

Answer 1

ALTER TABLE Movie
ADD Producer VARCHAR(50);

ALTER TABLE Movie
DROP Genre;

ALTER TABLE Movie
CHANGE Year ReleaseYear SMALLINT;

Question 2

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

Answer 2

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

Question 3

7.3 LAB - Update rows in Horse table

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:

Change the height to 15.6 for horse with ID 2.
Change the registered name to Lady Luck and birth date to May 1, 2015 for horse with ID 4.
Change every horse breed to NULL for horses born on or after December 22, 2016.

Answer 3

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

Question 4

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.

Answer 4

DELETE FROM Horse
WHERE ID = 5;

DELETE FROM Horse
WHERE Breed = ‘Holsteiner’ OR Breed = ‘Paint’;

DELETE FROM Horse
WHERE BirthDate < ‘2013-03-13’;

Question 5

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.

Answer 5

SELECT RegisteredName, Height, BirthDate
FROM Horse
WHERE Height >= 15 AND Height <= 16
OR BirthDate >= ‘2020-01-01’;

Question 6

Create a Movie table with the following columns:

ID - positive integer with maximum value of 65,535
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

Note: Your SQL code does not display any results in Develop mode. Use Submit mode to test your code.

Answer 6

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

Question 7

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

Answer 7

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,
Phone CHAR(10) NOT NULL,
Email VARCHAR(30) UNIQUE
);

Question 8

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

Answer 8

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

Question 9

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.

Answer 9

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

Question 10

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.

NOTE: In submit-mode tests that generate multiple result tables, the results are merged. Although the tests run correctly, the results appear in one table.

Answer 10

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;