SQL Database IT240 For all screen shot requests be sure to show your object, query, and results in your screenshots. Verifying you have no tables in your database Right Click on your database (your login name in the Object Explorer) and click Refresh Expand your database (your login name) Expand Tables You should not have any tables listed there Download the SQL Code 1. Run the first part of the code to create the DEPARTMENT table. a. Right Click on your database (your login name in the Object Explorer) and click Refresh b. Expand your database (your login name) if you it isnt visible c. Expand Tables you should see a new DEPARTMENT table. d. Insert a screen shot showing you have a DEPARTMENT table be sure to include the area showing your database name (your login name) Screenshot Hot Keys: Windows WindowsKey + Shift Key + s Mac CmdKey + ShiftKey + 4 OR CmdKey + ShiftKey + 5 Insert Graphic Here 2. Run the second part of the code to create the EMPLOYEE table. a. Right Click on your database (your login name in the Object Explorer) and click Refresh b. Expand your database (your login name) if you it isnt visible c. Expand Tables you should see a new EMPLOYEE table. d. Insert a screen shot showing you have a EMPLOYEE table be sure to include the area showing your database name (your login name) Insert Graphic Here 3. Run the third part of the code to create the PROJECT table. a. Right Click on your database (your login name in the Object Explorer) and click Refresh b. Expand your database (your login name) if you it isnt visible c. Expand Tables you should see a new PROJECT table. IT 240 ASSIGNMENT RUNNING SQL CODE d. Insert a screen shot showing you have a PROJECT table be sure to include the area showing your database name (your login name) Insert Graphic Here 4. Run the fourth part of the code to create the ASSIGNMENT table. a. Right Click on your database (your login name in the Object Explorer) and click Refresh b. Expand your database (your login name) if you it isnt visible c. Expand Tables you should see a new ASSIGNMENT table. d. Insert a screen shot showing you have a ASSIGNMENT table be sure to include the area showing your database name (your login name) Insert Graphic Here In the Query Window execute the following command SELECT * FROM EMPLOYEE; Did you see any rows returned? 5. Run the fifth part of the code to INSERT 4 rows to your EMPLOYEE table. a. In the Query Window execute the following command b. SELECT * FROM EMPLOYEE; c. Did you see any rows returned? d. Insert a screen shot showing what data you have in the EMPLOYEES table be sure to include the area showing your database name (your login name) Insert Graphic Here 6. Run the sixth part of the code to INSERT only 6 more rows to your EMPLOYEE table. a. In the Query Window execute the following command b. SELECT * FROM EMPLOYEE; c. How many rows were returned? d. Insert a screen shot showing what data you have in the EMPLOYEES table be sure to include the area showing your database name (your login name) Insert Graphic Here 7. Try these insert rows (you may want to DELETE your existing rows first). What is wrong with the following INSERT commands (include answer in your submission): INSERT IN EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress) VALUES (5,'Alan','Adams','Human','H R 1',4,'360-285-8320','AlanAdams@WP.com'); IT 240 ASSIGNMENT RUNNING SQL CODE INSERT INTO (EmployeeNumber, FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress) VALUES (5,'Alan','Adams','Human','H R 1',4,'360-285-8320','AlanAdams@WP.com'); INSERT INTO EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress) VALUES ('Alan','Adams','Human','H R 1',4,'360-285-8320','AlanAdams@WP.com'); INSERT INTO EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress) VALUES ('Alan','Adams','Human','H R 1',4,'360-285-8320','AlanAdams@WP.com') /* Question 1 ************************************************************** Create DEPARTMENT table Run this code first and submit the results per the assignment. ************************************************************** */ DROP TABLE IF EXISTS DEPARTMENT; CREATE TABLE DEPARTMENT ( DepartmentName CHAR(35) NOT NULL, BudgetCode CHAR(30) NOT NULL, OfficeNumber CHAR(15) NOT NULL, DepartmentPhone CHAR(12) NOT NULL ); /* Question 2 ************************************************************** Create EMPLOYEE table Run this code next and submit the results per the assignment. ************************************************************** */ DROP TABLE IF EXISTS EMPLOYEE; CREATE TABLE EMPLOYEE ( EmployeeNumber CHAR(20) NOT NULL, FirstName CHAR(25) NOT NULL, LastName CHAR(25) NOT NULL, Department CHAR(30) NOT NULL, Position CHAR(35) NULL, Supervisor INTEGER NULL, OfficePhone CHAR(12) NULL, EmailAddress VARCHAR(100) NOT NULL ); /* Question 3 ************************************************************** Create PROJECT table Run this code next and submit the results per the assignment. ************************************************************** */ DROP TABLE IF EXISTS PROJECT; CREATE TABLE PROJECT ( ProjectID INTEGER NOT NULL, ProjectName CHAR(50) NOT NULL, Department CHAR(35) NOT NULL, MaxHours NUMERIC(8,2) NOT NULL, StartDate DATE NULL, EndDate DATE NULL ); /* Question 4 ************************************************************** Create ASSIGNMENT table Run this code next and submit the results per the assignment. ************************************************************** */ DROP TABLE IF EXISTS ASSIGNMENT; CREATE TABLE ASSIGNMENT ( ProjectID CHAR(20) NOT NULL, EmployeeNumber INTEGER NOT NULL, HoursWorked NUMERIC(6,2) NULL ); /* Question 5 - Four Rows of Employee */ INSERT INTO EMPLOYEE(EmployeeNumber, FirstName, LastName, Department, Position, OfficePhone, EmailAddress)VALUES( 1,'Mary', 'Jacobs', 'Administration', 'CEO', '360-285-8110', 'Mary.Jacobs@WP.com'); INSERT INTO EMPLOYEE(EmployeeNumber, FirstName, LastName, Department, Position, OfficePhone, EmailAddress)VALUES( 2,'Rosalie', 'Jackson', 'Administration', 'Admin Assistant', '360-285-8120', 'Rosalie.Jackson@WP.com'); INSERT INTO EMPLOYEE(EmployeeNumber, FirstName, LastName, Department, Position, OfficePhone, EmailAddress)VALUES( 3,'Richard', 'Bandalone', 'Legal', 'Attorney','360-285-8210', 'Richard.Bandalone@WP.com'); INSERT INTO EMPLOYEE(EmployeeNumber, FirstName, LastName, Department, Position, OfficePhone, EmailAddress)VALUES( 4,'George', 'Smith', 'Human Resources', 'HR3','360-285-8310', 'George.Smith@WP.com'); /* Question 6 - Remaining Employees */ INSERT INTO EMPLOYEE(EmployeeNumber, FirstName, LastName, Department, Position, OfficePhone, EmailAddress)VALUES( 5,'Alan', 'Adams', 'Human Resources', 'HR1', '360-285-8320', 'Alan.Adams@WP.com'); INSERT INTO EMPLOYEE(EmployeeNumber, FirstName, LastName, Department, Position, OfficePhone, EmailAddress)VALUES( 6,'Ken', 'Evans', 'Finance', 'CFO', '360-285-8410', 'Ken.Evans@WP.com'); INSERT INTO EMPLOYEE(EmployeeNumber, FirstName, LastName, Department, Position, OfficePhone, EmailAddress)VALUES( 7,'Mary', 'Abernathy', 'Finance', 'FA3', '360-285-8420', 'Mary.Abernathy@WP.com'); INSERT INTO EMPLOYEE(EmployeeNumber, FirstName, LastName, Department, Position, OfficePhone, EmailAddress)VALUES( 8,'Tom', 'Caruthers', 'Accounting', 'FA2', '360-285-8430', 'Tom.Caruthers@WP.com'); INSERT INTO EMPLOYEE(EmployeeNumber, FirstName, LastName, Department, Position, OfficePhone, EmailAddress)VALUES( 9,'Heather', 'Jones', 'Accounting', 'FA2', '360-285-8440', 'Heather.Jones@WP.com'); INSERT INTO EMPLOYEE(EmployeeNumber, FirstName, LastName, Department, Position, OfficePhone, EmailAddress)VALUES( 10,'Ken', 'Numoto', 'Sales and Marketing', 'SM3', '360-287-8510', 'Ken.Numoto@WP.com');