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

WGU - D427 Data Management - Applications ZyBooks Labs 7 and 8 23 studiers today 4.5 (11 reviews) Students also studied Terms in this set (27) Western Governors UniversityD 333 Save

WGU D426 V2

160 terms MoDhochesPreview D426 Study Guide (Red Text ONLY) 225 terms brattynnPreview

WGU C952

239 terms njlasjdPreview WGU - 66 terms hele 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:

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