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
Question:
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.
Modern Database Management
ISBN: 978-0133544619
12th edition
Authors: Jeff Hoffer, Ramesh Venkataraman, Heikki Topi