D427 - Data Management Applications
1. The Movie table has the following ALTER TABLE Movie ADD
columns: Producer VARCHAR(50);
ID - positive integer
Title - variable-length string
Genre - variable-length string
RatingCode - variable-length string
Year - integer
Write ALTER statements to make the fol-
lowing modifications to the Movie table:
Add a Producer column with VARCHAR
data type (max 50 chars).
2. The Movie table has the following ALTER TABLE Movie DROP
columns: COLUMN Genre;
ID - positive integer
Title - variable-length string
Genre - variable-length string
RatingCode - variable-length string
Year - integer
Write ALTER statements to make the fol-
lowing modifications to the Movie table:
Remove the Genre column.
3. The Movie table has the following ALTER TABLE Movie
columns: CHANGE COLUMN Year Re-
leaseYear SMALLINT;
ID - positive integer
Title - variable-length string
Genre - variable-length string
RatingCode - variable-length string
Year - integer
Write ALTER statements to make the fol-
, D427 - Data Management Applications
lowing modifications to the Movie table:
Change the Year column's name to Re-
leaseYear, and change the data type to
SMALLINT.
4. The Movie table has the following ALTER TABLE Movie ADD
columns: Producer VARCHAR(50);
ALTER TABLE Movie DROP
ID - positive integer COLUMN Genre;
Title - variable-length string ALTER TABLE Movie
Genre - variable-length string CHANGE COLUMN Year Re-
RatingCode - variable-length string leaseYear SMALLINT;
Year - integer
Write ALTER statements to make the fol-
lowing 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 Re-
leaseYear, and change the data type to
SMALLINT.
5. The Horse table has the following INSERT INTO Horse (Reg-
columns: isteredName, Breed, Height,
BirthDate) VALUES
ID - integer, auto increment, primary key ('Babe', 'Quarter Horse', 15.3,
RegisteredName - variable-length string '2015-02-10'),
Breed - variable-length string, must be one ('Independence', 'Holsteiner',
of the following: Egyptian Arab, Holsteiner, 16.0, '2017-03-13'),
Quarter Horse, Paint, Saddlebred ('Ellie', 'Saddlebred', 15.0,
Height - decimal number, must be between '2016-12-22'),
10.0 and 20.0 (NULL, 'Egyptian Arab', 14.9,
BirthDate - date, must be on or after Jan 1, '2019-10-12');
2015
Insert the following data into the Horse
, D427 - Data Management Applications
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
6. The Horse table has the following UPDATE Horse
columns: SET Height = 15.6
WHERE ID = 2;
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 e 10.0 and
d 20.0
BirthDate - date, must be e Jan 1, 2015
Make the following updates:
Change the height to 15.6 for horse with ID
2.
7. The Horse table has the following UPDATE Horse
columns: SET RegisteredName =
'Lady Luck', BirthDate =
ID - integer, auto increment, primary key '2015-05-01'
RegisteredName - variable-length string WHERE ID = 4;
Breed - variable-length string, must be one
of the following: Egyptian Arab, Holsteiner,
Quarter Horse, Paint, Saddlebred
Height - decimal number, must be e 10.0 and
d 20.0
BirthDate - date, must be e Jan 1, 2015
Make the following updates:
, D427 - Data Management Applications
Change the registered name to Lady Luck
and birth date to May 1, 2015 for horse with
ID 4.
8. The Horse table has the following UPDATE Horse
columns: SET Breed = NULL
WHERE BirthDate >=
ID - integer, auto increment, primary key '2016-12-22';
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 e 10.0 and
d 20.0
BirthDate - date, must be e Jan 1, 2015
Make the following updates:
Change every horse breed to NULL for
horses born on or after December 22, 2016.
9. The Horse table has the following UPDATE Horse
columns: SET Height = 15.6
WHERE ID = 2;
ID - integer, auto increment, primary key UPDATE Horse
RegisteredName - variable-length string SET RegisteredName =
Breed - variable-length string, must be one 'Lady Luck', BirthDate =
of the following: Egyptian Arab, Holsteiner, '2015-05-01'
Quarter Horse, Paint, Saddlebred WHERE ID = 4;
Height - decimal number, must be e 10.0 andUPDATE Horse
d 20.0 SET Breed = NULL
BirthDate - date, must be e Jan 1, 2015 WHERE BirthDate >=
'2016-12-22';
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
