WGU - D427 Data Management - Applications ZyBooks Labs 7 and 8

7.1 LAB - Alter Movie table

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:

1. Add a Producer column with VARCHAR data type (max 50 chars).

2. Remove the Genre column.

3. Change the Year column's name to ReleaseYear, and change the data type to SMALLINT.

ALTER TABLE Movie

ADD Producer VARCHAR(50);

ALTER TABLE Movie

DROP Genre;

ALTER TABLE Movie

CHANGE Year ReleaseYear SMALLINT;

7.2 LAB - Insert rows into 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 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

INSERT INTO Horse (RegisteredName, Breed, Height, BirthDate)

VALUES ('Babe', 'Quarter Horse', 15.3, '2015-02-10');

INSERT INTO Horse (RegisteredName, Breed, Height, BirthDate)

VALUES ('Independence', 'Holsteiner', 16.0, '2017-03-13');

INSERT INTO Horse (RegisteredName, Breed, Height, BirthDate)

VALUES ('Ellie', 'Saddlebred', 15.0, '2016-12-22');

INSERT INTO Horse (Breed, Height, BirthDate)

VALUES ('Egyptian Arab', 14.9, '2019-10-12');

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:

1.- Change the height to 15.6 for horse with ID 2.

2.- Change the registered name to Lady

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

7.4 LAB - Delete rows from Horse table

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:

1.- Horse with ID 5.

2.- All horses with breed Holsteiner or Paint.

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

7.5 LAB - Select horses with logical operators

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

7.6 LAB - Create Movie table

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,

Title VARCHAR(50),

Rating CHAR(4),

ReleaseDate DATE,

Budget DECIMAL(8,2)

);

7.7 LAB - Create Student table with constraints

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, def

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

);

7.8 LAB - Create Horse table with constraints

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

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

);

7.9 LAB - Create LessonSchedule table with FK constraints

Two tables are created:

1.- Horse with columns:

ID - integer, primary key

RegisteredName - variable-length string

2.- Student with columns:

ID - integer, primary key

FirstName - variable-length string

LastName - variable-length string

Create the LessonSchedule table with columns:

HorseID - integer with range 0 to 65 thousand, not NULL, partial primary key, foreign key references Horse(ID)

StudentID - integer with range 0 to 65 thousand

CREATE TABLE LessonSchedule(

HorseID SMALLINT UNSIGNED NOT NULL,

StudentID SMALLINT UNSIGNED,

LessonDateTime DATETIME NOT NULL,

PRIMARY KEY (HorseID, LessonDateTime),

CONSTRAINT fk_HorseID FOREIGN KEY (HorseID) REFERENCES Horse(ID) ON DELETE CASCADE,

CONSTRAINT fk_StudentID FOREIGN KEY (StudentID) REFERENCES Student(ID) ON DELETE SET NULL

);

or

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

);

7.10 LAB - Rollback and savepoint (Sakila)

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;

8.1 Practice Lab 1

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.

Do not add any additional constraints to any column beyond wha

CREATE TABLE Member (

ID INT UNSIGNED,

FirstName VARCHAR(100),

MiddleInitial CHAR(1),

LastName VARCHAR(100),

DateOfBirth DATE,

AnnualPledge DECIMAL(8,2) UNSIGNED

);

8.2 Practice Lab 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 RatingCodecolumn in the Rating table.

CREATE TABLE Movie (

Title VARCHAR(30),

RatingCode VARCHAR(5),

FOREIGN KEY (RatingCode) REFERENCES Rating(RatingCode)

);

8.3 Practice Lab 3

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

8.4 Practice Lab 4

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;

8.5 Practice Lab 5

A database has a view named MovieView.

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

DROP VIEW MovieView;

8.6 Practice Lab 6

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)

;

8.7 Practice Lab 7

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 CONSTRAINT Year FOREIGN KEY (Year) REFERENCES YearStats (Year)

;

8.8 Practice Lab 8

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

8.9 Practice Lab 9

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:

TitleGenreRatingCodeYearPride and PrejudiceRomanceG2005

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

INSERT INTO Movie

VALUES (NULL, 'Pride and Prejudice', 'Romance', 'G', '2005')

;

8.10 Practice Lab 10

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;

8.11 Practice Lab 11

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

;

8.12 Practice Lab 12

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;

8.13 Practice Lab 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 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;

8.14 Practice Lab 14

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;

8.15 Practice Lab 15

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 RatingCode, COUNT(*) AS RatingCodeCount

FROM Movie

GROUP BY RatingCode

ORDER BY RatingCode;

8.16 Practice Lab 16

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;

8.17 Practice Lab 17

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(*) AS NumOfMovies

FROM Movie

WHERE Year = 2019;