Download SQL Fundamentals: Mastering Database Management and more Exams Service Management in PDF only on Docsity!
Pre-Assessment Studying for OA
San Francisco, CA 94110, USA How many attributes are present in the address fragment? - correct answer ✔4. Each entity is considered an attribute. -San Francisco -CA
-USA The Package table has the following columns: Weight Description LastChangedDate TrackingNumber Which column should be designated the primary key for the Package table? - correct answer ✔TrackingNumber. This is the only option that will always have to be UNIQUE. Which SQL command is an example of data definition language (DDL)?
UPDATE
ALTER
SELECT
DELETE - correct answer ✔ALTER The Member table will have the following columns: ID FirstName MiddleInitial LastName DateOfBirth AnnualPledge Write a SQL statement to create the Member table - correct answer ✔CREATE TABLE Member ( ID INT UNSIGNED, FirstName VARCHAR(100), MiddleInitial VARCHAR(1), LastName VARCHAR(100), DateOfBirth DATE, AnnualPledge DECIMAL(8, 2) UNSIGNED ); The Rating table has the following columns:
Write a SQL statement to modify the Movie table to make the ID column the primary key. - correct answer ✔ALTER TABLE Movie ADD PRIMARY KEY (ID); 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. - correct answer ✔ALTER TABLE Movie ADD FOREIGN KEY (Year) REFERENCES YearStats(Year); Write a SQL statement to create an index named idx_year on the Year column of the Movie table. - correct answer ✔CREATE INDEX idx_year ON Movie (Year); Write a SQL query to retrieve the Title and Genre values for all records in the Movie table with a Year value of 2020. - correct answer ✔SELECT Title, Genre FROM Movie WHERE Year=2020; Write a SQL statement to insert the indicated data into the Movie table. - correct answer ✔INSERT INTO Movie (Title, Genre, RatingCode, Year) VALUES ("Pride and Prejudice", "Romance", "G", 2005); Write a SQL statement to delete the row with the ID value of 3 from the Movie table. - correct answer ✔DELETE FROM Movie WHERE ID = 3; Write a SQL statement to update the Year value to be 2022 for all movies with a Year value of 2020. - correct answer ✔UPDATE Movie SET Year= WHERE Year=2020; Write a SQL query to return all data from the Movie table without directly referencing any column names. - correct answer ✔SELECT * FROM Movie;
Write a SQL query to display all Title values in alphabetical order A-Z. - correct answer ✔SELECT Title FROM Movie ORDER BY Title ASC; Write a SQL query to output the unique RatingCode values and the number of movies with each rating value from the Movie table as RatingCodeCount. Sort the results by the RatingCode in alphabetical order A-Z. Ensure your result set returns the columns in the order indicated. - correct answer ✔SELECT RatingCode, COUNT() AS RatingCodeCount FROM Movie GROUP BY RatingCode ORDER BY RatingCode ASC; Write a SQL query to return how many movies have a Year value of 2019. - correct answer ✔SELECT COUNT() AS Count2019 FROM Movie WHERE Year = 2019; Write a SQL query to display both the Title and the TotalGross (if available) for all movies. Ensure your result set returns the columns in the order indicated. - correct answer ✔SELECT Movie.Title, YearStats.TotalGross FROM Movie LEFT JOIN YearStats ON Movie.Year = YearStats.Year;
