WGU - D427 Data Management - Applications ZyBooks Labs 8

WGU - D427 Data Management - Applications ZyBooks Labs 8 Leave the first rating Students also studied Terms in this set (17) Western Governors UniversityD 426 Save WGU - D427 Data Management - Ap...27 terms helen_robertson4 Preview WGU - D427 - Data Management - ...66 terms helen_robertson4 Preview

zyBooks Chapter 7: SQL Labs Practi...

10 terms briannayyyyPreview

D426 S

225 term bra 8.1 Practice Lab 1

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 wha CREATE TABLE Member (

ID INT UNSIGNED,

FirstName VARCHAR(100), MiddleInitial CHAR(1), LastName VARCHAR(100), DateOfBirth DATE, AnnualPledge DECIMAL(8,2) UNSIGNED );

8.2 Practice Lab 2

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 RatingCodecolumn in the Rating table.CREATE TABLE Movie ( Title VARCHAR(30), RatingCode VARCHAR(5), FOREIGN KEY (RatingCode) REFERENCES Rating(RatingCode) ); 8.3 Practice Lab 3

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); 8.4 Practice Lab 4

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; 8.5 Practice Lab 5 A database has a view named MovieView.Write a SQL statement to delete the view named MovieView from the database.DROP VIEW MovieView;

8.6 Practice Lab 6

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)

; 8.7 Practice Lab 7

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.ALTER TABLE Movie ADD CONSTRAINT Year FOREIGN KEY (Year) REFERENCES YearStats (Year) ; 8.8 Practice Lab 8

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 an index named idx_year on the Year column of the Movie table.CREATE INDEX idx_year ON Movie(Year);

8.9 Practice Lab 9

The Movie table has the following columns:

ID—integer, primary key, auto-increment Title—variable-length string Genre—variable-length string RatingCode—variable-length string Year—integer The following data needs to be added to the Movie

table:

TitleGenreRatingCodeYearPride and PrejudiceRomanceG2005 Write a SQL statement to insert the indicated data into the Movie table.INSERT INTO Movie VALUES (NULL, 'Pride and Prejudice', 'Romance', 'G', '2005') ; 8.10 Practice Lab 10

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 delete the row with the ID value of 3 from the Movie table.DELETE FROM Movie

WHERE ID = 3;

8.11 Practice Lab 11

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 update the Year value to be 2022 for all movies with a Year value of 2020.UPDATE Movie SET Year = 2022 WHERE Year = 2020 ; 8.12 Practice Lab 12 The database contains a table named Movie.Write a SQL query to return all data from the Movie table without directly referencing any column names.

SELECT *

FROM Movie;