D427 - SQL Code UPDATED ACTUAL Questions and CORRECT Answers

D427 - SQL Code UPDATED ACTUAL Questions and CORRECT Answers

Write a SQL statement to create the Member table. The
Member table will have the following columns: ID—
positive integer CREATE TABLE Member (
FirstName—variable-length string with up to 100 char- ID INT UNSIGNED,
acters FirstName VARCHAR(100),
MiddleInitial—fixed-length string with 1 character MiddleInitial CHAR(1),
LastName—variable-length string with up to 100 charac- LastName VARCHAR(100),
ters DateOfBirth DATE,
DateOfBirth—date AnnualPledge DECIMAL (8,2) UNSIGNED CHECK(Annu-
AnnualPledge—positive decimal value representing a alPledge<=999999.99));
cost of up to $999,999, with 2 digits for cents (can be .99
or .00)
Write a SQL statement to create the Movie table. Des-
CREATE TABLE Movie (
ignate the RatingCode column in the Movie table as a
Title VARCHAR(30),
foreign key to the RatingCode column in the Rating table.
RatingCode VARCHAR(5),
FOREIGN KEY (RatingCode) REFERENCES Rating(Rating-
Title VARCHAR 30
Code));
RatingCode VARCHAR 5
Write a SQL statement to add the Score column to the ALTER TABLE Movie
Movie table with 3 digits and 1 decimal point. ADD COLUMN Score DECIMAL (3,1);
Write a SQL statement to create a view named MyMovies
CREATE VIEW MyMovies AS
that contains the Title, Genre, and Year columns for all
SELECT Title, Genre, Year
movies. Ensure your result set returns the columns in the
FROM Movie;
order indicated.
Write a SQL statement to delete the view named
DROP View MovieView;
MovieView from the database.
Write a SQL statement to modify the Movie table to make ALTER TABLE Movie
the ID column the primary key. ADD PRIMARY KEY (ID);

, Write a SQL statement to designate the Year column in
ALTER TABLE Movie
the Movie table as a foreign key to the Year column in the
ADD FOREIGN KEY (Year) REFERENCES YearStats(Year);
YearStats table.
Write a SQL statement to create an index named idx_year CREATE INDEX idx_year
on the Year column of the Movie table. ON Movie (Year);
Write a SQL statement to insert the indicated data into the
Movie table.
INSERT INTO Movie (Title, Genre, RatingCode, Year)VAL-
Title: Pride and Prejudice
UES ('Pride and Prejudice', 'Romance', 'G', 2005);
Genre: Romance
RatingCode: G
Year: 2005
Write a SQL statement to delete the row with the ID value DELETE FROM Movie
of 3 from the Movie table. WHERE ID = 3;
UPDATE Movie
Write a SQL statement to change the Year value to be 2022
SET Year = 2022
for all movies with a Year value of 2020.
WHERE Year = 2020;
Write a SQL query to return all data from the Movie table SELECT *
without directly referencing any column names. FROM Movie;
Write a SQL query to retrieve the Title and Genre values for
SELECT Title, Genre
all records in the Movie table with a Year value of 2020.
FROM Movie
Ensure your result set returns the columns in the order
WHERE Year = 2020;
indicated.
Write a SQL query to display all Title values in alphabetical SELECT Title
order A-Z. Even though A-Z is the default, be sure to FROM Movie
include ASC. ORDER BY Title ASC;

Write a SQL query to output the unique RatingCode values SELECT DISTINCT RatingCode, COUNT(*) AS RatingCode-
and the number of movies with each rating value from the Count
Movie table as RatingCodeCount. Sort the results by the FROM Movie