Data Management - Applications D427 - MYSQL - SQL Programing - Intermediate Level Exam Questions And Answers

Data Management - Applications D427 - MYSQL - SQL Programing - Intermediate Level Exam Questions And Answers 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. 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. 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. 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. 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. ALTER TABLE Movie ADD PRIMARY KEY (ID); 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. 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. 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. 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. 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. ALTER TABLE Movie ADD PRIMARY KEY (ID);