WGU D427 Data Management Applications |MYSQL - SQL Programing Test | Intermediate Level with complete solutions | 100% Correct.

WGU D427 Data Management Applications |MYSQL -
SQL Programing Test | Intermediate Level with
complete solutions | 100% Correct.
Question:

The Movie table has the following columns:
i,- i,- i,- i,- i,- i,-




ID—integer, primary key, i,- i,-




Title—variable-length string, i,-




Genre—variable-length string, i,-




RatingCode—variable-length string, i,-




Year—integer,

Write a SQL statement to create a view named MyMovies that contains
i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,-


the Title, Genre, and Year columns for all movies. Ensure your result set
i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,-


returns the columns in the order indicated.?
i,- i,- i,- i,- i,- i,-




Answer:

CREATE VIEW MyMovies AS i,- i,- i,-




SELECT Title, Genre, Year
i,- i,- i,-




FROM Movie; i,-




Question:
The Movie table has the following columns:
i,- i,- i,- i,- i,- i,-




ID—integer,
Title—variable-length string, i,-

,Genre—variable-length string, i,-




RatingCode—variable-length string, i,-




Year—integer,

Write a SQL statement to modify the Movie table to make the ID column
i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,-



the primary key.?
i,- i,-




Answer:

ALTER TABLE Movie i,- i,-




ADD PRIMARY KEY (ID);
i,- i,- i,-




Question:
The Movie table has the following columns:
i,- i,- i,- i,- i,- i,-




ID—integer, primary key, i,- i,-




Title—variable-length string, i,-




Genre—variable-length string, i,-




RatingCode—variable-length string, i,-




Year—integer,
The YearStats table has the following columns:
i,- i,- i,- i,- i,- i,-




Year—integer,

TotalGross—bigint unsigned, i,-




Releases—integer,

Write a SQL statement to designate the Year column in the Movie table as
i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,-


a foreign key to the Year column in the YearStats table.?
i,- i,- i,- i,- i,- i,- i,- i,- i,- i,-

,Answer:
ALTER TABLE Moviei,- i,-




ADD CONSTRAINT Year FOREIGN KEY (Year)
i,- i,- i,- i,- i,-




References YearStats(Year); i,-




Question:
The Movie table has the following columns:
i,- i,- i,- i,- i,- i,-




ID—integer, primary key, i,- i,-




Title—variable-length string, i,-




Genre—variable-length string, i,-




RatingCode—variable-length string, i,-




Year—integer,

Write a SQL statement to create an index named idx_year on the Year
i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,-


column of the Movie table.?
i,- i,- i,- i,-




Answer:

CREATE INDEX idx_year ON Movie (Year);
i,- i,- i,- i,- i,-




Question:

The Member table will have the following columns:
i,- i,- i,- i,- i,- i,- i,-




ID—positive integer i,-




FirstName—variable-length string with up to 100 characters, i,- i,- i,- i,- i,- i,- i,-

, MiddleInitial—fixed-length string with 1 character, i,- i,- i,- i,- i,-




LastName—variable-length string with up to 100 characters, i,- i,- i,- i,- i,- i,-




DateOfBirth—date,

AnnualPledge—positive decimal value representing a cost of up to i,- i,- i,- i,- i,- i,- i,- i,- i,-



$999,999, with 2 digits for cents, i,- i,- i,- i,- i,-




Write a SQL statement to create the Member table.
i,- i,- i,- i,- i,- i,- i,- i,-




Do not add any additional constraints to any column beyond what is
i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,- i,-



stated.?

Answer:
CREATE TABLE Member ( i,- i,- i,-




ID INT PRIMARY KEY unsigned,
i,- i,- i,- i,-




FirstName VARCHAR(100), i,-




MiddleInitial CHAR(1), i,-




LastName VARCHAR(100), i,-




DateOfBirth DATE, i,-




AnnualPledge DECIMAL(8, 2) unsigned i,- i,- i,-




);




Question:

The Rating table has the following columns:
i,- i,- i,- i,- i,- i,-




RatingCode—variable-length string, primary key, i,- i,- i,-




RatingDescription—variable-length string, i,-