Ch 8 Pretest

Question 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.

Answer 1

CREATE TABLE Member (
ID INT UNSIGNED,
FirstName VARCHAR(100),
MiddleInitial CHAR(1),
LastName VARCHAR(100),
DateOfBirth DATE,
AnnualPledge DECIMAL(8,2) UNSIGNED
);

Question 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 RatingCode column in the Rating table.

Answer 2

CREATE TABLE Movie (
Title VARCHAR(30),
RatingCode VARCHAR(5),
FOREIGN KEY(RatingCode) REFERENCES Rating(RatingCode)
);

Question 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.

Answer 3

ALTER TABLE Movie
ADD Score Decimal(3,1);

Question 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.

Answer 4

CREATE VIEW MyMovies AS
SELECT Title, Genre, Year
FROM Movie;

Question 5

A database has a view named MovieView.

Write a SQL statement to delete the view named MovieView from the database.

Answer 5

DROP VIEW MovieView;

Question 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.

Answer 6

ALTER TABLE Movie
ADD PRIMARY KEY (ID);

Question 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.

Answer 7

ALTER TABLE Movie
ADD FOREIGN KEY (Year) REFERENCES YearStats(Year);

Question 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.

Answer 8

CREATE INDEX idx_year
ON Movie (Year);

Question 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:

|Title|Genre|RatingCode|Year|
|—|—|—|—|
|Pride and Prejudice|Romance|G|2005|

Write a SQL statement to insert the indicated data into the Movie table.

Answer 9

INSERT Movie (Title, Genre, RatingCode, Year)
VALUES (‘Pride and Prejudice’, ‘Romance’, ‘G’, 2005);

Question 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.

Answer 10

To remove COLUMNS, you would use an ALTER TABLE statement. For this question it is asking to delete a row. We use a DELETE & WHERE clause.

DELETE FROM Movie
WHERE ID = 3;

Question 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.

Answer 11

UPDATE Movie
SET Year = 2022
WHERE Year = 2020;

Question 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.

Answer 12

SELECT *
FROM Movie;

Question 13

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 query to retrieve the Title and Genre values for all records in the Movie table with a Year value of 2020. Ensure your result set returns the columns in the order indicated.

Answer 13

Select Title, Genre
FROM Movie
WHERE Year = 2020;

Question 14

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 query to display all Title values in alphabetical order A–Z.

Answer 14

SELECT Title
FROM Movie
ORDER BY Title;

Question 15

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 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.

Answer 15

SELECT RatingCode, COUNT(*) AS RatingCodeCount
FROM Movie
GROUP BY RatingCode
ORDER BY RatingCode ASC;

Question 16

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 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.

Answer 16

SELECT Movie.Title, YearStats.TotalGross
FROM Movie
LEFT JOIN YearStats ON Movie.Year = YearStats.Year;

Question 17

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 query to return how many movies have a Year value of 2019.

Answer 17

SELECT COUNT(*)
FROM Movie
WHERE Year = 2019;

Question 18

There are different ways of referencing columns in different tables. What are the different scenarios and how do you reference in each of those scenarios?

Answer 18

Scenarios:

• Creating a foreign key inside of a table creation statement. We’ll say the col you are using is table A and the one you are referencing is table B.
  
  • After all of your columns are created, you say:

FOREIGN KEY(TblA_Col_Name) REFERENCES TblB_Name(TblB_Col)

• Doing joins

SELECT Department.Name AS Group,
Employee.Name AS Supervisor
FROM Department
Employee
ON Manager = ID;

Question 19

How do you get rid of ROWS containing specific values?

Answer 19

DELETE FROM TableName
WHERE ColName = X;

Question 20

How do you update ROWS containing specific values?

Answer 20

UPDATE TableName
SET ColName = NewValue
WHERE ColName = Condition;

Question 21

What does the GROUP BY statement do?

Answer 21

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.