PLEASE WRITE UP/ SCREENSHOT SPOOL FILE. ONLY NEED SPOOL FILE. CODE IS PROVIDED B

Question

PLEASE WRITE UP/ SCREENSHOT SPOOL FILE. ONLY NEED SPOOL FILE. CODE IS PROVIDED BELOW. NEED ACTUAL SPOOL FILE NOT DIRECTIONS!

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');

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 budget 10,000,000 20,000,000 name Market 2 Sales Employees id name James John Patricia sala 100,000 200,000 300,000 2 Works In e id d id work time 2 2 40 Dependents e id name Ma Daniel Lab 3 (10 points) Write SQL statements that delete all tuples associated with "James"

Explanation / Answer

Spool file is nothing but using Spool command to write the results of your SQL statements in a output file. So basically the process of writing SQL query result to a file is called a spooling. It will save your results in the path and filenname provided. Below is the syntax. Since it writes the results to a file, SQL "SELECT" statement will be applicable, as the select statement only outputs the data.

Spool C: empbcd.txt (This is path where you want to save the file, you can given any path you like)
<SQL SELECT Statement/Code>
Spool Off

Below is the Spool file for all your Lab1, Lab2 and Lab3 SQL codes.

1. For Lab1

Spool C:labsLab1Spool.txt

SELECT * from EMPLOYEE;
SELECT * from DEPENDENTS;
SELECT * from DEPARTMENTS;
SELECT * from WORK_IN;

Spool Off

The file will be empty in this case, as table is just created and table has no records as of now, so SELECT will output no results.

2. For Lab2

Spool C:labsLab2Spool.txt

SELECT * from Employees;
SELECT * from Dependants;
SELECT * from Departments;
SELECT * from Works_In;

Spool Off

The file "Lab2Spool.txt" will have the output result of all the SELECT statements.

3. For Lab 3

Spool C:labsLab3Spool.txt

SELECT * from Employees;
SELECT * from Dependants;
SELECT * from Departments;
SELECT * from Works_In;

Spool Off

The file "Lab3Spool.txt" will contain the result of the SELECT. The results will not contain records associated with James as they have been deleted.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.