Description

Objective:

The purpose of this lab is to introduce you to the DDL set of statements in SQL.  By writing SQL to create tables, constraints, and views, you will have the tools needed to implement database designs that you will create later in the course.  By finishing this lab, the student will be able to:

• create, modify, and drop tables based on design specifications provided,
• inserting new data into tables, update data in tables, and delete data from tables while considering referential integrity,
• enforce constraints on tables to ensure data integrity and consistency,
• create a table using the structure and data from an existing table,
• import data into a table from other tables.

Submission:

Your submission will be a single WORD file with the solutions provided.

Your submission needs to include a comment header block and be commented to include the question and the solutions. Make sure every SQL statement terminates with a semicolon.

Tasks:

Add
SET AUTOCOMMIT ON;
under the comment header and execute it

Consider the following table specifications:

Part A (DDL) :

1. Create all the following tables and their given constraints:

MOVIES (movieid:int, title:varchar(35), releaseYear:int, director:int,score:decimal(3,2))

ACTORS (actorid:int, firstname:varchar(20), lastname:varchar(30))

CASTINGS (movieid:int, actorid:int)

DIRECTORS(directorid:int, firstname:varchar(20), lastname:varchar(30))

2. Modify the movies table to create a foreign key constraint that refers to table directors.
3. Modify the movies table to create a new constraint so the uniqueness of the movie title is guaranteed.
4. Write insert statements to add the following data to table directors and movies.

Director

Movies

5. Write SQL statements to remove all above tables.
   Is the order of tables important when removing? Why?

Part B (More DML):

6. Create a new empty table (that means the table will not have any data after creating) employeecopy the same as table Use a single statement to create the table and insert the data at the same time (Hint use a WHERE clause that is false like 1=2)
7. Modify table employeecopy and add a new column username to this table. The value of this column is not required and does not have to be unique.
8. Re-insert all data from the  table into your new table employeecopy using a single statement.
9. In table employeecopy, generate the email address for column username for each student by concatenating the employeeid and the string “@seneca.ca”. For instance, the username of employee 123 will be “123@seneca.ca’.
10. Delete all the employeecopy data and display the data in the table. Does employeecopy exist? If not how can you delete table employeecopy.

From Lab 03-PART2– SQL – Single Table Queries will be implemented in this lab

11. Create a statement that will insert yourself as an RETAILEMPLOYEE of the company.

• Use a unique RETAILEMPLOYEE number of your choice
• Use your school email address and
• Your job title will be “Cashier”
• Office code will be 4
• You will report to RETAILEMPLOYEE 1088

12. Create a query that displays your, and only your, RETAILEMPLOYEE data
13. Create a statement to update just your job title to “Head Cashier”
14. Create a statement to insert another fictional RETAILEMPLOYEE into the database.  This RETAILEMPLOYEE will be a “Cashier” and will report to you.  Make up fake data for the other fields.
15. Create a statement to Delete yourself from the database.  Did it work?  If not, why?
16. Create a statement to delete the fake RETAILEMPLOYEE from the database and then rerun the statement to delete yourself.  Did it work?
17. Create a singlestatement that will insert both yourself and the fake RETAILEMPLOYEE at the same time.  This time the fake RETAILEMPLOYEE will report to 1088 as well.
18. Create a singlestatement to delete both yourself and the fake RETAILEMPLOYEE.
19. Create a new order in RETAILORDER table with required date Sep 22^nd,2021 and order date as Sep 17^th,2021. Make up the reset of the fields and then display the only the new order that you have created just now.
20. Insert a new product into product table with product name as “2020 Bugatti Veyron” and productcode as “S111_111” and make up the rest of the fields.