D427 – CODE UPDATED ACTUAL Questions and CORRECT Answers

D427 – CODE UPDATED ACTUAL Questions and CORRECT Answers

1.47 Write the SQL to create a Product table with the
following columns:
ID - Integer
Name - Variable-length string with maximum 40 charac- CREATE TABLE Product (
ters ID INT,
ProductType - Variable-length string with maximum 3 Name VARCHAR(40),
characters ProductType VARCHAR(3),
OriginDate - Year, month, and day OriginDate DATE,
Weight - Decimal number with six significant digits and Weight DECIMAL(6,1)
one digit after the - decimal point );
Place your CREATE TABLE statement before the INSERT
and SELECT statements. Run your solution and verify the
result table contains the three inserted rows.
1.54 Write the SQL to create a Product table with the
following columns:
ID - Integer with range 0 to 65,535
CREATE TABLE Product (
Name - Variable-length string with maximum 40 charac-
ID SMALLINT UNSIGNED,
ters
Name VARCHAR(40),
ProductType - Fixed-length string with 3 characters
ProductType CHAR(3),
OriginDateTime - Year, month, day, and time
OriginDateTime DATETIME,
Weight - Decimal number with variable precision and 4
Weight FLOAT
bytes of storage
);
Place your CREATE TABLE statement before the INSERT
and SELECT statements. Run your solution and verify the
result table contains the three inserted rows.
1.6.9
The given SQL creates a Movie table and inserts some
movies. The SELECT statement selects all movies released
before January 1, 2000.
Modify the SELECT statement to select the title and release
date of PG-13 movies that are released after January 1,

,2008.
SELECT Title, ReleaseDate
Run your solution and verify the result table shows just the
FROM Movie
titles and release dates for The Dark Knight and Crazy Rich
WHERE Rating = 'PG-13' AND ReleaseDate > '2008-01-01';
Asians.
1.79
Modify the SELECT statement to only select songs that SELECT *
have a NULL Title value. Then run your solution, and verify FROM Song
the new query returns only songs by Taylor Swift and WHERE Title IS NULL;
Nirvana.
SELECT * FROM Song WHERE Title IS NULL OR Artist IS
1.7.11 NULL;
Modify the SELECT statement to select only songs that or
have a NULL Title or NULL Artist. SELECT * FROM Song WHERE Title = NULL OR Artist =
NULL;
UPDATE Song
SET Title = 'With Or Without You'
1.8.6 WHERE ID = 200;
Write three UPDATE statements to make the following
changes: UPDATE Song
Change the title from 'One' to 'With Or Without You'. SET Artist = 'Aretha Franklin'
Change the artist from 'The Righteous Brothers' to 'Aretha WHERE ID = 300;
Franklin'.
Change the release years of all songs after 1990 to 2021. UPDATE Song
SET ReleaseYear = 2021
WHERE ReleaseYear > 1990;
197
The given SQL creates a Movie table with an auto-incre-
menting ID column. INSERT INTO Movie (Title, Rating, ReleaseDate)
Write a single INSERT statement immediately after VALUES ('Raiders of the Lost Ark', 'PG', '1981-06-15'),
the CREATE TABLE statement that inserts the following
movies:


, Raiders of the Lost Ark PG June 15, 1981
('The Godfather', 'R', '1972-03-24'),
The Godfather R March 24, 1972
('The Pursuit of Happyness', 'PG-13', '2006-12-15');
The Pursuit of Happyness PG-13 December 15, 2006
CREATE TABLE Album (
ID INT,
Title VARCHAR(60),
1.10.6 ReleaseYear INT,
Modify the two CREATE TABLE statements: PRIMARY KEY (ID)
Add a primary key constraint to the Album table's ID col- );
umn. CREATE TABLE Song (
Add a primary key constraint to the Song table's ID col- ID INT,
umn. Title VARCHAR(60),
Add a foreign key constraint to the Song table so the Artist VARCHAR(60),
AlbumID column refers to the Album table's ID column. AlbumID INT,
PRIMARY KEY (ID),
FOREIGN KEY (AlbumID) REFERENCES Album(ID)
);
2.1.4
Modify the SELECT statement to select movies with the
word 'star' somewhere in the title. SELECT * FROM Movie
Run your solution and verify the result table shows just the WHERE Title LIKE '%star%';
movies
Rogue One: A Star Wars Story, Star Trek and Stargate.
2.2.3
Using the YEAR() and MONTH() functions, modify the 223SELECT * FROM Movie Where YEAR(ReleaseDate) >
SELECT statement to select movies that are released after 2017 OR MONTH (ReleaseDate) = 11;
2017 or in November.
2.3.6 The given SQL creates a Song table and inserts some
songs. The SELECT statement selects the genre and row
count for each genre group.
Add a new column to the SELECT statement that uses

MAX() to find the most recent release year for each genre.