LAB 1 CREATE TABLE EMPLOYEE(EMP_ID INT PRIMARY KEY, EMP_NAME VARCHAR(20),EMP_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.

HERE IS THE CODE:

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 0,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 Dependents e id name Ma Daniel Lab 3 (10 points) Write SQL statements that delete all tuples associated with "James"

Explanation / Answer

Spooling is a process of transferring the data from SQL plus to a file. When we run command or do any activity whatever comes up gets saved into a file. For that you will need to create an empty file or just give the path on SQL prompt. Suppose a file name "spooltest" is created under folder named "test" in C drive. The path of "spooltest" file is "C: estspooltest.txt".

Now on SQL prompt type following commands :

SQL> spool C: estspooltest.txt (if you have not created this file manually in C drive, this command will create one in C drive)

now run some commands on already created tables :

SQL> select * from Employee

SQL> select * from Departments

now turn the spool off :

SQL> spool off

next open the file manually at location "C: estspooltest.txt " in your computer

you will find all the commands, execution and results happened on SQL prompt has been copied inside the file "spooltest.txt"

So next time you want to spool, please try above steps. i.e summarized as below :

SQL> path of the file where you want to copy all the command executions

SQL> run your commands

SQL> turn spool off

Note : if you want to keeping appending the command executions in the same spool file, then please use "append" keyword along with spool file path.

for example :

SQL > spool C: estspooltest.txt append; (if you don't use the "append" keyword then content of spool file will get overwritten)

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

---

---

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