EXPERIMENT NO.

2
Aim- To perform various DDL, DML and TCL Commands on Employee Database.
Query 1: Write a SQL code to create the employee table with name EMP_1 with following columns
and their data types.
COLUMN DATATYPE
EMP_NUM char (3)
EMP_LNAME char (15)
EMP_FNAME char (15)
EMP_INITIAL char (1)
EMP_HIREDATE date
JOB_CODE char (3)

Solution –
create table EMP_1(
EMP_NUM char(3),
EMP_LNAME char(15),
EMP_FNAME char(15),
EMP_INITIAL char(1),
EMP_HIREDATE date,
JOB_CODE char(3)
);
Output-

Query 2- After creating the structure, enter the corresponding entries in the table EMP_1.
EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE
101 News John G 11/08/92 502
102 Senior David H 07/12/87 501
103 Arbough June E 12/01/94 500
104 Ramoras Anne K 11/15/85 501
105 Johnson Alice K 02/01/91 502

Solution –
insert into EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE,
JOB_CODE) values ('101', 'News', 'John', 'G', to_date('11-8-92',' MM-DD-YY'), '502');
insert into EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE,
JOB_CODE) values ('102', 'Senior', 'David', 'H', to_date('07-12-87',' MM-DD-YY'), '501');
insert into EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE,
JOB_CODE) values ('103', 'Arbough', 'June', 'E', to_date('12-1-94',' MM-DD-YY'), '500');
insert into EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE,
JOB_CODE) values ('104', 'Ramoras', 'Anne', 'K', to_date('11-15-85',' MM-DD-YY'), '501');
insert into EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE,
JOB_CODE) values ('105', 'Johnson', 'Alice', 'K', to_date('02-01-91',' MM-DD-YY'), '502');
insert into EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE,
JOB_CODE) values ('106', 'Smithfield', 'William', ' ', to_date('06-23-90',' MM-DD-YY'), '500');
insert into EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE,
JOB_CODE) values ('107', 'Alonzo', 'Maria', 'D', to_date('10-10-91',' MM-DD-YY'), '500');
insert into EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE,
JOB_CODE) values ('108', 'Washington', 'Ralph', 'B', to_date('08-22-89',' MM-DD-YY'), '501');
insert into EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE,
JOB_CODE) values ('109', 'Smith', 'Larry', 'W', to_date('07-18-95',' MM-DD-YY'), '501');
Output-

Query 3: Write a SQL code that will list all attributes for a job code of 502.
Solution –
select * from EMP_1 where JOB_CODE = '502';
Output-
Query 4: Write a SQL code that will save the EMP_1 table.
Solution –
commit;
Output-

Query 5: Write a SQL code to change the job code to 501 for the person whose personnel number is
106. After doing the change see the content of changed database, and then restore the original value.
Solution –
update EMP_1 set JOB_CODE = '501' where EMP_NUM = '106';
select * from EMP_1;
Output-

Solution –
rollback;
Output-
Query 6: Write a SQL code to delete the row the for the person William Smithfield, who was hired on
23 whose job code is 500. (Note: Specify all condition, since there may be more than one William )
Solution –
delete from EMP_1 where EMP_FNAME = 'Smithfield' and EMP_LNAME = 'William' and
EMP_HIREDATE = to_date('06-23-90','MM-DD-YY') and JOB_CODE = '500';
Output-

Query 7: Write a SQL code that will restore the data to its original status that was after executing query in
Query 4.
Solution –
rollback;
Output-
Query 8:Create a copy of EMP_1, name it EMP_2, and then add the attribute EMP_PCT and
PROJ_NUM to its structure.
(EMP_PCT is bonus percent)
Specification of attributes are
EMP_PCT Decimal(4,2)
PROJ_NUM char(3)
Solution –
create table EMP_2 as select * from EMP_1;
alter table EMP_2 ADD (EMP_PCT decimal(4,2),PROJ_NUM char(3));
Output-

Query 9:Enter the following data in newly introduced attributes:

EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIRE JOB_CODE EMP_PCT PROJ_NUM
DATE
101 News John G 11/8/92 502 5.00
102 Senior David H 7/12/87 501 8.00
103 Arbough June E 12/1/94 50 3.85
104 Ramoras Anne K 11/15/85 501 10.00
105 Johnson Alice K 2/1/91 502 5.00
106 Smithfield William 6/23/90 500 6.20
107 Alonzo Maria D 10/10/91 500 5.15
108 Washington Ralph B 8/22/89 501 10.00
109 Smith Larry W 7/18/95 501 2.00
Solution –
update EMP_2 set EMP_PCT = '5.00' where EMP_NUM = '101';
update EMP_2 set EMP_PCT = '8.00' where EMP_NUM = '102';
update EMP_2 set EMP_PCT = '3.85' where EMP_NUM = '103';
update EMP_2 set EMP_PCT = '10.00' where EMP_NUM = '104';
update EMP_2 set EMP_PCT = '5.00' where EMP_NUM = '105';
update EMP_2 set EMP_PCT = '6.20' where EMP_NUM = '106';
update EMP_2 set EMP_PCT = '5.15' where EMP_NUM = '107';
update EMP_2 set EMP_PCT = '10.00' where EMP_NUM = '108';
update EMP_2 set EMP_PCT = '2.00' where EMP_NUM = '109';
Output-

Query 10: Using a simple query, enter the PROJ_NUM =18 for all employees whose job code is 500 in
EMP_2 table.
Solution –
update EMP_2 set PROJ_NUM = '18' where JOB_CODE = '500';
Output-
Query 11:Similarly, enter the PROJ_NUM = 25 for all employees whose job code is 501 and above in
EMP_2 table.
Solution –
update EMP_2 set PROJ_NUM = '25' where JOB_CODE >= '501';
Output-

Query 12: Now enter the PROJ_NUM = 14 for those employees who were hired before January 1, 1992
and whose job code is at least 501 in EMP_2 table.
Solution –
update EMP_2 set PROJ_NUM = '14' where JOB_CODE = '501' and EMP_HIREDATE < to_date('01-01-
92',' MM-DD-YY');
Output-
Query 13: A) Create a temporary table TEMP_1, whose structure is same as that of EMP_2. (NOTE:
Do not create structure explicitly; just copy the structure, not records.)
B) Now copy the records from EMP_2, do not add them explicitly.
Solution –
A. create table TEMP_1 as (select * from EMP_2 );
B. insert into TEMP_1 select * from EMP_2;
Output-

Query 14: Delete newly created TEMP_1 table from the database.
Solution –
drop table TEMP_1.

Query 15: List all employees from EMP_2 table whose last name starts with ‘smith’.
Solution –
select EMP_FNAME, EMP_LNAME from EMP_2 where EMP_LNAME like 'Smith%';
Output-

Query 16: Add another table Project_1 to your database with structure and values as shown:
PROJ_NUM PROJ_NAME EMP_NUM
15 Evergreen 103
18 Amber Wave 108
22 Rolling Tide 102
25 Starflight 107
The EMP_NUM in this Project table refers EMP_NUM in EMP_2.
Write SQL code that will produce the following output
 PROJ_NAME EMP_LNAME EMP_FNAME EMP_INITIAL JOB_CODE
Evergreen Arbough June E 500
Amber Wave Washington Ralph B 501
Rolling Tide Senior David H 501
Starflight Alonzo Maria D 500
Solution –
create table Project_1(
PROJ_NUM char(3),
PROJ_NAME char(15),
EMP_NUM char(3)
);
insert into Project_1 (PROJ_NUM, PROJ_NAME, EMP_NUM) values ('15', 'Evergreen', '103');
insert into Project_1 (PROJ_NUM, PROJ_NAME, EMP_NUM) values ('18', 'Amber Wave', '108');
insert into Project_1 (PROJ_NUM, PROJ_NAME, EMP_NUM) values ('22', 'Rolling Tide', '102');
insert into Project_1 (PROJ_NUM, PROJ_NAME, EMP_NUM) values ('25', 'Starflight', '107');
select Project1.PROJ_NAME, EMP_2.EMP_LNAME, EMP_2.EMP_FNAME, EMP_2.EMP_INITIAL,
EMP_2.JOB_CODE from EMP_2 inner join Project1 on EMP_2.EMP_NUM = Project1.EMP_NUM order
by Project1.PROJ_NUM;
Output-

Query 17: Find the average bonus percentage in the EMP_2 table.
Solution –
select avg(EMP_PCT) from EMP_2;
Output-