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:
- 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:
- 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
- A primary key for each of the three tables. Hint: One of them is a composite
key!
- A default salary in table Castings.
- 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.
- (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.
- 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.
- Was there an error? (There should be) If so, paste it. Describe what happened.
- Write the SQL to insert a row into Movie where the movieId is 1.
- Was there an error? If so, paste it. Describe what happened.
- 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.
- What error did you receive? Describe what happened.
- 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.
- Check out what value got entered into the salary, Paste the results of a select * from
Castings. Explain what happened.
- When you run the same command from #10 again what happens? What error message do
you receive? Explain.
II Alter
- 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!)
- Paste the result of Select * from Movie. Check out the rating attribute. What happened?
- 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.
- What happened? What do you see in the rating field for that row in the table?
- Write the statement to drop the column rating from the Movie table.
- Paste the result of select * from Movie.
- 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.
- Paste the results of select * from Movie and explain what happened.
- 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).
- 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?
Step by step
Solved in 2 steps with 1 images
