Description

Objectives:
The purpose of this lab is to introduce you to the DML set of statements in SQL. By writing SQL to insert, update and delete data 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:
• inserting new data into tables, update data in tables, and delete data from tables while considering referential integrity,
• Import data into a table from other tables.
Submission:
Your submission will be a single text-based .sql file with the solutions provided.
DBS211_L03_FirstName_LastName.sql
Your submission needs to include a comment header block and be commented to include the questions and the solutions. Make sure every SQL statement terminates with a semicolon.
Example Submission
— ***********************
— Name: Your Name
— ID: #########
— Purpose: Lab 03 DBS211
— ***********************

SET AUTOCOMMIT ON;

— Q1 SOLUTION — INSERT INTO ……….;

— Q2 SOLUTION –
UPDATE table_name ……..;

Locate, select, and submit the file to the Lab 03 link.

Setup

Style Guide
Your SQL should be written using the standard coding style:
• all keywords are to be upper case,
• all user-defined names are to be lower case, (example: table and field names)
• there should be a carriage return before each major part of the SQL statements
See the following sample:
INSERT INTO table1( column1, column2,
.. ..
columnN)
VALUES( value1, value2,
.. ..
valueN);

Marking Scheme (although Qs 3 & 5 don’t carry marks, they are mandatory for the Lab completion)
Question Points
1 0.5
2 0.5
3 0
4 1
5 0
6 0.5
7 0.5
8 0.5
9 0.5
Total: 4

Grade Policy
▪ Solutions with errors do not get any marks (they get zero). Execute your .sql file using the “Run Script” button to make sure there are no errors in your file.
▪ You do not receive marks for the missing or incomplete solutions.
▪ The name of the tables you will create in this lab have to match exactly with the names given this lab document.
▪ Submission in incorrect file formats would get 0.

Tasks:
Remember to comment the question number for each solution.

DML
Execute the below set of statements in an SQL Worksheet before starting work on your Lab (do not submit these statements along with your Lab work). This will create a new table called employees2 which you will use to work on this Lab. DO NOT try to work on any other table as part of this Lab as it will tamper your existing data.

CREATE TABLE employees2 AS
SELECT *
FROM employees
WHERE 1 = 2;

ALTER TABLE employees2
ADD PRIMARY KEY (employeenumber);

ALTER TABLE employees2
ADD FOREIGN KEY (reportsto)
REFERENCES employees2 (employeenumber);

Open a new SQL Worksheet (this will be your submission .sql file so save and format it as per the convention specified). Type and execute the below command at the top of this SQL file and keep it as part of your submission.
SET AUTOCOMMIT ON;

Now work on the below questions for your Lab work in the same .sql file just below the above executed command.
1. Insert all the data from the employees table into your new table employees2 using a single DML statement.

2. Create a statement that will insert yourself as an employee into employees2.
a. Use a unique employee number of your choice (Hint: Find the highest value of the employee number in the employees table, increase the value by one and use it as your employee number). To find the highest value of the employee number you can sort the rows in the descending order. The first row will then contain the highest value.
c. Your extension will be ‘x2222’
d. Your job title will be “Head Cashier”
e. Office code will be 4
f. You will report to employee 1088

3. Create a statement to insert another fictional employee into employees2. This employee will be a “Cashier” and will report to you. Make up fake data for the other fields.

4. Create a statement to delete yourself from employees2. Did it work? If not, why?

5. Create a statement to delete the fake employee from employees2 and then rerun the statement to delete yourself. Did it work? Explain why?

6. Create a single statement that will insert both yourself and the fake employee at the same time. This time you and the fake employee will report to 1088.

7. Create a single statement to delete both yourself and the fake employee from employees2.

8. In table employees2, write an SQL statement to change the first name and the last name of employee with ID 1002 to your name.

9. In table employees2, remove all the rows using a single DML statement.

Lastly, execute the below SQL statement once your Lab work is complete so as to drop the table employees2 which was created above for this Lab.

DROP TABLE employees2;