abel each question beginning 1 to match the numbers below. I Primary Keys / Foreign Keys / Default Bring up your previous 2 assignments with the sql code: Write the statements to remove (drop) the previous tables: Actor, Castings, and Movie Modify your Create Table statements (from the last two assignments SQL Basics and SQL Continued) to use:

SQL – Relational Assignment

label each question beginning 1 to match the numbers below.

I Primary Keys / Foreign Keys / Default

Bring up your previous 2 assignments with the sql code:

1. Write the statements to remove (drop) the previous tables: Actor, Castings, and Movie
2. Modify your Create Table statements (from the last two assignments SQL Basics and

SQL Continued) to use:

1. Actor table: Instead of assigning a unique id to each actor manually try out

auto_increment by updating your CREATE TABLE statement’s id field to use

auto_increment example:

actorId INT auto_increment

1. A primary key for each of the three tables. Hint: One of them is a composite

key!

1. A default salary in table Castings.
2. Foreign keys where appropriate: actorId in the Castings table must refer to an

actorId in the Actor table and movieId in the Castings table must refer to a

movieId from the Movie table.

Paste the 3 new create statements.

Note: When running multiple sql statements you need semicolons (;) separating

them. Otherwise run them one at a time.

1. (a) Movie table – Rerun your insert statements (from the SQL basic assignment) to insert

the rows you did before in the Movie table. Verify it worked. List the insert statements

you used in the Word doc. Paste the results of a select * from Movie.

(b) Actor inserts: Modify your insert statements (from the SQL basic assignment) to

insert the rows you did before in the Actor table, however, remove the actorId’s.

Example:

INSERT INTO Actor (fname, lname)

VALUES ('Emma', 'Stone')

The above should generate the id number beginning with 1. Try it with your own inserts!

Include the revised insert statements in your Word doc and the results of a Select * from

Actor so I can verify it worked!

(c) Castings table: Modify your insert statements (from the SQL continued assignment)

by double-checking the actorid values to match the new actorIds generated in the

Actor table. Run the insert statements. Did they work (yes or no)? If not, explain

what happened and fix your errors. Paste only the updated inserts and a screenshot of

the results from running Select * from Castings into your Word doc.

1. Write the SQL statement to insert a row into the Movie table where the movieId is

NULL. Note: Specify the word NULL instead of a number.

1. Was there an error? (There should be) If so, paste it. Describe what happened.
2. Write the SQL to insert a row into Movie where the movieId is 1.
3. Was there an error? If so, paste it. Describe what happened.
4. Write a statement to insert a new entry into the Castings table, but use a movieId or

actorId that is not currently in that appropriate table.

1. What error did you receive? Describe what happened.
2. Write a statement to insert a new entry into the Castings table (with the appropriate

movieId, actorId, characterRole), but do not specify the salary.

Note: In the insert specify the columns that you do want to add.

1. Check out what value got entered into the salary, Paste the results of a select * from

Castings. Explain what happened.

1. When you run the same command from #10 again what happens? What error message do

you receive? Explain.

II Alter

1. Write one statement to alter the table Movie to add a column, rating, which is an integer.

(Example: 5 would be a great movie – 5 stars!)

1. Paste the result of Select * from Movie. Check out the rating attribute. What happened?
2. Write a statement to insert a new entry into the Movie table without specifying a rating.

You will need to specify the columns you are inserting values into.

1. What happened? What do you see in the rating field for that row in the table?
2. Write the statement to drop the column rating from the Movie table.
3. Paste the result of select * from Movie.
4. Write one statement to alter the table Movie to add a column, rating, which is an integer.

This time give it a default value of 5.

1. Paste the results of select * from Movie and explain what happened.
2. Write two sql statements to insert new movies the first without specifying a rating and the

second with a rating specified (choose a number that’s not 5).

1. Paste the results of a Select * from Movie. What happened to the entry you specified a

rating for? What happened to the one you did not specify a rating for?