WGU D427 - Chapter 7 and 8 questions with verified answers

WGU D427 - Chapter 7 and 8 questions with verified
answers
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. Ans✓✓✓-ALTER TABLE movie ADD COLUMN Producer VARCHAR(50);
ALTER TABLE movie DROP COLUMN Genre;
ALTER TABLE movie CHANGE COLUMN 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 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 Ans✓✓✓-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')


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. Ans✓✓✓-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:
Horse with ID 5.
All horses with breed Holsteiner or Paint.
All horses born before March 13, 2013. Ans✓✓✓-DELETE FROM Horse WHERE ID
= 5;
DELETE FROM Horse WHERE Breed = 'Holsteiner' OR Breed = 'Paint';
DELETE FROM Horse WHERE BirthDate < '2013-03-13';