WGU D427 Data Management Applications - MYSQL - SQL Programing - Intermediate Level Exam 2023

The Member table will have the following columns: ID—positive integer FirstName—variable-length string with up to 100 characters, MiddleInitial—fixed-length string with 1 character, LastName—variable-length string with up to 100 characters, DateOfBirth—date, AnnualPledge—positive decimal value representing a cost of up to $999,999, with 2 digits for cents, Write a SQL statement to create the Member table. Do not add any additional constraints to any column beyond what is stated. - ANSWER-CREATE TABLE Member ( ID INT PRIMARY KEY unsigned, FirstName VARCHAR(100), MiddleInitial CHAR(1), LastName VARCHAR(100), DateOfBirth DATE, AnnualPledge DECIMAL(8, 2) unsigned ); The Rating table has the following columns: RatingCode—variable-length string, primary key, RatingDescription—variable-length string, The Movie table should have the following columns: Title—variable-length string, maximum 30 characters, RatingCode—variable-length string, maximum 5 characters, Write a SQL statement to create the Movie table. Designate the RatingCode column in the Movie table as a foreign key to the RatingCode column in the Rating table. - ANSWER-CREATE TABLE Movie ( Title VARCHAR(30), RatingCode VARCHAR(5), FOREIGN KEY (RatingCode) REFERENCES Rating(RatingCode) ); The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, A new column must be added to the Movie table: Column name: Score, Data type: decimal(3,1), Write a SQL statement to add the Score column to the Movie table. - ANSWER-ALTER TABLE Movie ADD Score DECIMAL(3, 1); The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, Write a SQL statement to create a view named MyMovies that contains the Title, Genre, and Year columns for all movies. Ensure your result set returns the columns in the order indicated. - ANSWER-CREATE VIEW MyMovies AS SELECT Title, Genre, Year FROM Movie; The Movie table has the following columns: ID—integer, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, Write a SQL statement to modify the Movie table to make the ID column the primary key. - ANSWER-ALTER TABLE Movie ADD PRIMARY KEY (ID); The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, The YearStats table has the following columns: Year—integer, TotalGross—bigint unsigned, Releases—integer, Write a SQL statement to designate the Year column in the Movie table as a foreign key to the Year column in the YearStats table. - ANSWER-ALTER TABLE Movie ADD CONSTRAINT Year FOREIGN KEY (Year) References YearStats(Year);