Activity 2: (Select clause, Arithmetic Operators)
Database: employee
Create Following tables and insert tuples with suitable constraints
Table : EMPLOYEE
EMPID FIRSTANAME LASTNAME Hire_Date ADDRESS CITY
1001 George Smith 11-May-06 83 first street Paris
1002 Mary Jones 25-Feb-08 84 Vine Ave Losantiville
1012 Sam Tones 12-Sep-05 33 Elm St. Paris
1015 Peter Thompson 19-Dec-06 11 Red Road Paris
1016 Sarath Sharma 22-Aug-07 440 MG Road New Delhi
1020 Monika Gupta 07-Jun-08 9 Bandra Mumbai
Table : EMPSALARY
EMPID SALARY BENEFITS DESIGNATION
1001 10000 3000 Manager
1002 8000 1200 Salesman
1012 20000 5000 Director
1015 6500 1300 Clerk
1016 6000 1000 Clerk
1020 8000 1200 Salesman
Write queries for the following
1. To display FIRSTNAME, LASTNAME, ADDRESS AND CITY of all employees living in
PARIS.
2. To display the content of employee table in descending order of FIRSTNAME
3. Select FIRSTNAME and SALARY of salesman
4. To display the FIRSTNAME,LASTNAME, AND TOTAL SALARY of all employees from the
table EMPLOYEE and EMPSALARY. Where TOTAL SALARY is calculated as
SALARY+BENEFITS
5. List the Names of employees, who are more than 1 year old in the organization
6. Count number of distinct DESINGATION from EMPSALARY
7. List the employees whose names have exactly 6 characters
8. Add new column PHONE_NO to EMPLOYEE and update the records
Bhuvaneshwari M,GSC,Hassan Page 1
9. List employee names, who have joined before 15-Jun-08 and after 16-Jun-07
10. Generate Salary slip with Name, Salary, Benefits, HRA-50%, DA-30%, PF-12%, Calculate
gross. Order the result in descending order of the gross.
SQL> create table emp(empid number(4)primary key,fname varchar2(10),lname
varchar2(10),hdate date,address varchar2(15),city varchar2(10));
Table created.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NOT NULL NUMBER(4)
FNAME VARCHAR2(10)
LNAME VARCHAR2(10)
HDATE DATE
ADDRESS VARCHAR2(15)
CITY VARCHAR2(10)
SQL> insert into emp values(1001,'george','smith','11-may-06','83 first street','paris');
1 row created.
SQL> insert into emp values(1002,'mary','jones','25-feb-08','842 vine ave','losantivie');
1 row created.
SQL> insert into emp values(1012,'sam','tones','12-sep-05','33 elm st','paris');
1 row created.
SQL> insert into emp values(1015,'peter','thompson','19-dec-06','11 red road','paris');
1 row created.
SQL> insert into emp values(1016,'sarath','sharma','22-aug-07','440 mg road','new delhi');
1 row created.
SQL> insert into emp values(1020,'monika','gupta','07-jun-08','9 bandra','mumbai');
1 row created.
SQL> select * from emp;
EMPID FNAME LNAME HDATE ADDRESS CITY
---------- ---------- ---------- --------- --------------- ----------
1001 george smith 11-MAY-06 83 first street paris
1002 mary jones 25-FEB-08 842 vine ave losantivie
1012 sam tones 12-SEP-05 33 elm st paris
1015 peter thompson 19-DEC-06 11 red road paris
1016 sarath sharma 22-AUG-07 440 mg road new delhi
1020 monika gupta 07-JUN-08 9 bandra mumbai
6 rows selected.
Bhuvaneshwari M,GSC,Hassan Page 2
SQL> create table emps(empid number(4)references emp(empid),salary number(6),benefits
number(5),desig varchar2(10));
Table created.
SQL> desc emps;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NUMBER(4)
SALARY NUMBER(6)
BENEFITS NUMBER(5)
DESIG VARCHAR2(10)
SQL> insert into emps values(1001,10000,3000,'manager');
1 row created.
SQL> insert into emps values(1002,8000,1200,'salesman');
1 row created.
SQL> insert into emps values(1012,20000,5000,'director');
1 row created.
SQL> insert into emps values(1015,6500,1300,'clerk');
1 row created.
SQL> insert into emps values(1016,6000,1000,'clerk');
1 row created.
SQL> insert into emps values(1020,8000,1200,'salesman');
1 row created.
SQL> select * from emps;
EMPID SALARY BENEFITS DESIG
---------- ---------- ---------- ----------
1001 10000 3000 manager
1002 8000 1200 salesman
1012 20000 5000 director
1015 6500 1300 clerk
1016 6000 1000 clerk
1020 8000 1200 salesman
6 rows selected.
1. To display firstname,lastname, address and city of all employees living in paris.
SQL> select fname,lname,address,city from emp where city='paris';
FNAME LNAME ADDRESS CITY
---------- ---------- --------------- ----------
george smith 83 first street paris
sam tones 33 elm st paris
peter thompson 11 red road paris
Bhuvaneshwari M,GSC,Hassan Page 3
2. To display the content of employee table in descending order of firstname.
SQL> select * from emp order by fname desc;
EMPID FNAME LNAME HDATE ADDRESS CITY
---------- ---------- ---------- --------- --------------- ----------
1016 sarath sharma 22-AUG-07 440 mg road new delhi
1012 sam tones 12-SEP-05 33 elm st paris
1015 peter thompson 19-DEC-06 11 red road paris
1020 monika gupta 07-JUN-08 9 bandra mumbai
1002 mary jones 25-FEB-08 842 vine ave losantivie
1001 george smith 11-MAY-06 83 first street paris
6 rows selected.
3. Select firstname and salary of salesman.
SQL> select e.fname,es.salary from emp e,emps es where e.empid=es.empid and
es.desig='salesman';
FNAME SALARY
---------- ----------
mary 8000
monika 8000
4. To display the firstname, lastname and total salary of all employees from the table
employee and empsalary. Where total salary is calculated as salary+benefits.
SQL> select e.fname,e.lname,es.salary+es.benefits"TOTAL SALARY" from emp e,emps es
where e.empid=es.empid;
FNAME LNAME TOTAL SALARY
---------- ---------- ------------
george smith 13000
mary jones 9200
sam tones 25000
peter thompson 7800
sarath sharma 7000
monika gupta 9200
6 rows selected.
5. List the names of employees, who are more than 1 year old in the organization.
SQL> select fname from emp where months_between(sysdate,hdate)>12;
FNAME
----------
george
mary
sam
peter
sarath
monika
Bhuvaneshwari M,GSC,Hassan Page 4
6 rows selected.
6. Count number of distinct designation from empsalary.
SQL> select count(distinct(desig))from emps;
COUNT(DISTINCT(DESIG))
----------------------
4
7. List the employees whose names have exactly 6 characters.
SQL> select fname from emp where fname like'______';
FNAME
----------
george
sarath
monika
8. Add new column phone_no to employee and update the records.
SQL> alter table emp add phoneno number(10);
Table altered.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NOT NULL NUMBER(4)
FNAME VARCHAR2(10)
LNAME VARCHAR2(10)
HDATE DATE
ADDRESS VARCHAR2(15)
CITY VARCHAR2(10)
PHONENO NUMBER(10)
SQL> update emp set phoneno=123456 where empid=1001;
1 row updated.
SQL> update emp set phoneno=234567 where empid=1002;
1 row updated.
SQL> update emp set phoneno=2534567 where empid=1012;
1 row updated.
SQL> update emp set phoneno=2349567 where empid=1015;
1 row updated.
SQL> update emp set phoneno=2349787 where empid=1016;
1 row updated.
SQL> update emp set phoneno=5549787 where empid=1020;
1 row updated.
SQL> select * from emp;
Bhuvaneshwari M,GSC,Hassan Page 5
EMPID FNAME LNAME HDATE ADDRESS CITY PHONENO
---------- ---------- ---------- --------- --------------- ---------- ----------
1001 george smith 11-MAY-06 83 first street paris 123456
1002 mary jones 25-FEB-08 842 vine ave losantivie 234567
1012 sam tones 12-SEP-05 33 elm st paris 2534567
1015 peter thompson 19-DEC-06 11 red road paris 2349567
1016 sarath sharma 22-AUG-07 440 mg road new delhi 2349787
1020 monika gupta 07-JUN-08 9 bandra mumbai 5549787
6 rows selected.
9. List employee names, who have joined before 15-jun-08 and after 16-jun-07.
SQL> select fname from emp where hdate>'16-jun-07' and hdate<'15-jun-08';
FNAME
----------
mary
sarath
monika
10. Generate salary slip with name, salary, benefits, hra-50%, da-30%, pf-12%, calculate
gross. Order the result in descending order of the gross.
SQL> select
e.fname,es.salary,es.benefits,es.salary*0.5"HRA",es.salary*0.3"da",es.salary*0.12"PF",es.salary
+es.salary*0.5+es.salary*0.3+es.salary*0.12"GROSS" from emp e,emps es where
e.empid=es.empid order by es.salary+es.salary*0.5+es.salary*0.3+es.salary*0.12 desc;
FNAME SALARY BENEFITS HRA da PF GROSS
---------- ---------- ---------- ---------- ---------- ---------- ----------
sam 20000 5000 10000 6000 2400 38400
george 10000 3000 5000 3000 1200 19200
mary 8000 1200 4000 2400 960 15360
monika 8000 1200 4000 2400 960 15360
peter 6500 1300 3250 1950 780 12480
sarath 6000 1000 3000 1800 720 11520
6 rows selected.
Bhuvaneshwari M,GSC,Hassan Page 6
