LAB 1

CREATE TABLE EMPLOYEE(EMP_ID INT PRIMARY KEY, EMP_NAME VARCHAR(20),EMP_AGE INT,SALARY INT);

CREATE TABLE DEPENDENTS(DEPNDT_NAME VARCHAR(20),EMP_ID INT,DEPNDT_AGE INT,FOREIGN KEY(EMP_ID)REFERENCES EMPLOYEE(EMP_ID));

CREATE TABLE DEPARTMENTS(DEPT_ID INT PRIMARY KEY,DEPT_NAME VARCHAR(20),BUDGET INT);

CREATE TABLE WORK_IN(EMP_ID INT,DEPT_ID INT,WORK_TIME INT,FOREIGN KEY(EMP_ID)REFERENCES EMPLOYEE(EMP_ID),FOREIGN KEY(DEPT_ID)REFERENCES DEPARTMENTS(DEPT_ID));

LAB 2

CREATE TABLE Employees( id INT NOT NULL PRIMARY KEY, name VARCHAR(50), age INT, salary REAL(10,2) ); CREATE TABLE Departments( id INT NOT NULL PRIMARY KEY, name VARCHAR(50), budget REAL(10,2) ); CREATE TABLE Work_In( E_id INT, D_id INT, work_time INT, PRIMARY KEY(E_id, D_id), FOREIGN KEY(E_id) REFERENCES Employees(id), FOREIGN KEY(D_id) REFERENCES Departments(id) ); CREATE TABLE Dependents( name VARCHAR(50), age INT, E_id INT, PRIMARY KEY(name, E_id), FOREIGN KEY(E_id) REFERENCES Employees(id) ); INSERT INTO Departments VALUES(1, 'Marketing', 10000000), (2, 'Sales', 20000000); INSERT INTO Employees VALUES(1, 'James', 32, 100000), (2, 'John', 33, 200000), (3, 'Patricia', 35, 30000); INSERT INTO Works_In VALUES(1, 1, 30), (2,2, 40), (3, 1, 50); INSERT INTO Dependants VALUES(1, 'Mary', 7), (1, 'Daniel', 5);

LAB 3

DELETE FROM Works_In WHERE e_id =(SELECT e_id FROM Employees WHERE name = 'James'); DELETE FROM Dependents WHERE e_id = (SELECT e_id FROM Employees WHERE name = 'James'); DELETE FROM Employees WHERE id = (SELECT e_id FROM Employees WHERE name = 'James');

ONLY NEED SPOOL FILE!!

I HAVE THE ANSWERS I ONLY NEED THE SPOOL FILE PLEASE.

   

 

   

Lab 1 (20 points) Write SQL statements that construct four relational tables, including domain (i.e., data type), entity integrity (i.e., primary key), and referential integrity (i.e., foreign key). Lab 2 (20 points) Write SQL statements that populate the following datasets. Departments id 1 2 Employees Works_In id 1 2 3 Dependents e_id 1 2 3 name Marketing Sales name James John Patricia d_id 1 2 1 budget 10,000,000 20,000,000 name Mary Daniel age 32 33 35 e_id 1 1 Lab 3 (10 points) Write SQL statements that delete all tuples associated with "James". salary 100,000 200,000 300,000 work_time 30 40 50 Age 75 5