1TABLE Employee (
FName VARCHAR2(20),
LName VARCHAR2(20),
SSN NUMBER(9) PRIMARY KEY,
Address VARCHAR2(50),
Salary NUMBER(10,2),
Bdate DATE,
Sex CHAR(1),
SuperSSN NUMBER(9),
DNo NUMBER(2)
);
Output: Table created.
2. Create Table Department
CREATE TABLE Department (
DNo NUMBER(2) PRIMARY KEY,
DName VARCHAR2(20),
ManagerSSN NUMBER(9),
MgrStartDate DATE
);
Output: Table created.
3. Insert data into Employee, Department and Project
tables
INSERT INTO Employee VALUES ('John','Smith',123456789,
'731 Fondren, Houston TX',30000,'09-JAN-1965','M',333445555,5);
INSERT INTO Employee VALUES ('Franklin','Wong',333445555,
'638 Voss, Houston TX',40000,'08-DEC-1955','M',888665555,5);
INSERT INTO Employee VALUES ('Alicia','Zelaya',999887777,
'3321 Castle, Spring TX',25000,'19-JUL-1968','F',987654321,4);
INSERT INTO Employee VALUES ('Jennifer','Wallace',987654321,
'291 Berry, Bellaire TX',43000,'20-JUN-1941','F',888665555,4);
INSERT INTO Employee VALUES ('Ramesh','Narayan',666884444,
'975 Fire Oak, Humble TX',38000,'15-SEP-1962','M',333445555,5);
INSERT INTO Employee VALUES ('Joyce','English',453453453,
'5631 Rice, Houston TX',25000,'31-JUL-1972','F',333445555,5);
INSERT INTO Employee VALUES ('Ahmad','Jabbar',987987987,
'980 Dallas, Houston TX',25000,'29-MAR-1959','M',987654321,4);
INSERT INTO Employee VALUES ('James','Borg',888665555,
'450 Stone, Houston TX',55000,'10-NOV-1937','M',NULL,1);
INSERT INTO Department VALUES (1,'Headquarters',888665555,'19-JUN-1981');
INSERT INTO Department VALUES (4,'Administration',987654321,'01-JAN-1985');
INSERT INTO Department VALUES (5,'Research',333445555,'22-MAY-1988');
INSERT INTO Project VALUES (1,'ProductX','Bellaire',5);
INSERT INTO Project VALUES (2,'ProductY','Sugarland',5);
INSERT INTO Project VALUES (3,'ProductZ','Houston',5);
INSERT INTO Project VALUES (10,'Computerization','Stafford',4);
INSERT INTO Project VALUES (20,'Reorganization','Houston',1);
INSERT INTO Project VALUES (30,'Newbenefits','Stafford',4);
COMMIT;
Output: 8 rows inserted into Employee, 3 into Department, 6 into Project. Commit complete.
4. Display all the employees' information
SELECT * FROM Employee;
FName
LName
SSN
Address
Salary
Bdate
Sex
SuperSSN
DNo
John
Smith
123456789
731 Fondren, Houston TX
30000
09-JAN-1965
333445555
Franklin
Wong
333445555
638 Voss, Houston TX
40000
08-DEC-1955
888665555
Alicia
Zelaya
999887777
3321 Castle, Spring TX
25000
19-JUL-1968
987654321
Jennifer
Wallace
987654321
291 Berry, Bellaire TX
43000
20-JUN-1941
888665555
Ramesh
Narayan
666884444
975 Fire Oak, Humble TX
38000
15-SEP-1962
333445555
Joyce
English
453453453
5631 Rice, Houston TX
25000
31-JUL-1972
333445555
Ahmad
Jabbar
987987987
980 Dallas, Houston TX
25000
29-MAR-1959
987654321
4
James
Borg
888665555
450 Stone, Houston TX
55000
10-NOV-1937
null
Output: 8 rows returned (as above).
5. Display Employee name along with SSN and
Supervisor SSN
SELECT FName, LName, SSN, SuperSSN
FROM Employee;
FName
LName
SSN
SuperSSN
John
Smith
123456789
333445555
Franklin
Wong
333445555
888665555
Alicia
Zelaya
999887777
987654321
Jennifer
Wallace
987654321
888665555
Ramesh
Narayan
666884444
333445555
Joyce
English
453453453
333445555
Ahmad
Jabbar
987987987
987654321
James
Borg
888665555
NULL
6. Display employee names whose bdate is '29-MAR-
1959'
SELECT FName, LName
FROM Employee
WHERE Bdate = '29-MAR-1959';
FName
LName
Ahmad
Jabbar
7. Display salary of the employees without duplication
SELECT DISTINCT Salary FROM Employee;
Salary
55000
43000
40000
38000
30000
25000
8. Display MgrSSN and MgrStartDate of manager of
'Finance' department
SELECT ManagerSSN, MgrStartDate
FROM Department
WHERE DName = 'Finance';
Output: No rows selected (the sample Department data does not contain a 'Finance' department; using an
existing department, e.g. 'Administration', gives: ManagerSSN = 987654321, MgrStartDate = 01-JAN-
1985).
9. Modify department number of employee 'Joyce' to 5
UPDATE Employee
SET DNo = 5
WHERE FName = 'Joyce';
Output: 1 row updated.
10. Alter Department table to add DepartmentPhoneNum
column and insert values
ALTER TABLE Department
ADD DepartmentPhoneNum NUMBER(10);
UPDATE Department SET DepartmentPhoneNum = 7135551234 WHERE DNo = 1;
UPDATE Department SET DepartmentPhoneNum = 7135552345 WHERE DNo = 4;
UPDATE Department SET DepartmentPhoneNum = 7135553456 WHERE DNo = 5;
DNo
DName
ManagerSSN
MgrStartDate
DepartmentPhoneNum
Headquarters
888665555
19-JUN-1981
7135551234
4
Administration
987654321
01-JAN-1985
7135552345
Research
333445555
22-MAY-1988
7135553456
11. Alter table Department to modify size of
DepartmentPhoneNum
ALTER TABLE Department
MODIFY DepartmentPhoneNum NUMBER(15);
Output: Table altered.
12. Rename column DepartmentPhoneNum to PhNo
ALTER TABLE Department
RENAME COLUMN DepartmentPhoneNum TO PhNo;
Output: Table altered.
13. Rename Table Department as DEPT
RENAME Department TO DEPT;
Output: Table renamed.
14. Alter Table DEPT remove column PhNo
ALTER TABLE DEPT
DROP COLUMN PhNo;
Output: Table altered.
15. Create table COPYOFDEPT as a copy of DEPT
CREATE TABLE COPYOFDEPT AS
SELECT * FROM DEPT;
Output: Table created (3 rows copied).
16. Delete all rows from COPYOFDEPT table
DELETE FROM COPYOFDEPT;
Output: 3 rows deleted.
17. Remove COPYOFDEPT table
DROP TABLE COPYOFDEPT;
Output: Table dropped.
18. Add Foreign Keys using Alter Table
ALTER TABLE Employee
ADD CONSTRAINT fk_supersn
FOREIGN KEY (SuperSSN) REFERENCES Employee(SSN);
ALTER TABLE Employee
ADD CONSTRAINT fk_dno
FOREIGN KEY (DNo) REFERENCES DEPT(DNo);
ALTER TABLE DEPT
ADD CONSTRAINT fk_mgrssn
FOREIGN KEY (ManagerSSN) REFERENCES Employee(SSN);
Output: Table altered (foreign key constraints added).
19. Drop and re-add Foreign Key on SuperSSN
ALTER TABLE Employee
DROP CONSTRAINT fk_supersn;
ALTER TABLE Employee
ADD CONSTRAINT fk_supersn
FOREIGN KEY (SuperSSN) REFERENCES Employee(SSN);
Output: Table altered (constraint dropped and re-created).
20. Employee names having salary greater than Rs.25000
SELECT FName, LName
FROM Employee
WHERE Salary > 25000;
FName
LName
John
Smith
Franklin
Wong
Jennifer
Wallace
Ramesh
Narayan
James
Borg
21. Employee names whose salary lies between 30000
and 70000
SELECT FName, LName
FROM Employee
WHERE Salary BETWEEN 30000 AND 70000;
FName
LName
John
Smith
Franklin
Wong
Jennifer
Wallace
Ramesh
Narayan
James
Borg
22. Employees who have no supervisor
SELECT FName, LName
FROM Employee
WHERE SuperSSN IS NULL;
FName
LName
James
Borg
23. Display bdate of all employees in format 'DDthMonthYYYY'
SELECT FName, LName,
TO_CHAR(Bdate,'DD') || 'th ' || TO_CHAR(Bdate,'Month') ||
TO_CHAR(Bdate,'YYYY') AS Formatted_Bdate
FROM Employee;
FName
LName
Formatted_Bdate
John
Smith
09th January 1965
Franklin
Wong
08th December 1955
Alicia
Zelaya
19th July 1968
Jennifer
Wallace
20th June 1941
Ramesh
Narayan
15th September 1962
Joyce
English
31th July 1972
Ahmad
Jabbar
29th March 1959
James
Borg
10th November 1937
24. Employee names whose bdate is on or before 1978
SELECT FName, LName
FROM Employee
WHERE Bdate <= '31-DEC-1978';
Output: All 8 employees are returned since every Bdate in the sample data is on or before 1978.
25. Employee names having 'salt lake' in their address
SELECT FName, LName
FROM Employee
WHERE LOWER(Address) LIKE '%salt lake%';
Output: No rows selected (no employee in the sample data has 'Salt Lake' in the address).
26. Department name that starts with 'M'
SELECT DName
FROM DEPT
WHERE DName LIKE 'M%';
Output: No rows selected (add a department such as 'Marketing' to see a matching row).
27. Department names that end with 'E'
SELECT DName
FROM DEPT
WHERE UPPER(DName) LIKE '%E';
DName
Headquarters
28. Names of employees having supervisor SSN
554433221 or 333445555
SELECT FName, LName
FROM Employee
WHERE SuperSSN IN (554433221, 333445555);
FName
LName
John
Smith
Ramesh
Narayan
Joyce
English
29. Department names in upper case and lower case
SELECT UPPER(DName) AS Upper_Name, LOWER(DName) AS Lower_Name
FROM DEPT;
Upper_Name
Lower_Name
HEADQUARTERS
headquarters
ADMINISTRATION
administration
RESEARCH
research
30. First four and last four characters of department
names using SUBSTR
SELECT DName,
SUBSTR(DName,1,4) AS First_Four,
SUBSTR(DName,-4,4) AS Last_Four
FROM DEPT;
DName
First_Four
Last_Four
Headquarters
Head
ters
Administration
Admi
tion
Research
Rese
arch
31. Substring of Address (5th to 11th position) of all
employees
SELECT FName, LName, SUBSTR(Address,5,7) AS Address_Substr
FROM Employee;
FName
LName
Address_Substr
John
Smith
ondren,
Franklin
Wong
Voss, H
Alicia
Zelaya
1 Castl
Jennifer
Wallace
Berry,
Ramesh
Narayan
Fire Oa
Joyce
English
1 Rice,
Ahmad
Jabbar
Dallas,
James
Borg
Stone,
32. MgrStartDate on adding three months to it
SELECT ManagerSSN,
ADD_MONTHS(MgrStartDate,3) AS New_StartDate
FROM DEPT;
ManagerSSN
New_StartDate
888665555
19-SEP-1981
987654321
01-APR-1985
333445555
22-AUG-1988