Q.1 List details of employee with empcode ‘E105’.
--------------------------------------------------
SQL> select * from emply where empcode='E105';
EMPC EMPNAME ADDRESS AGE DEPT
---- -------------------- ------------------------------ ---------- ----
E105 Daisy Chaithram, Angamaly 35 D707
Q.2 Count the number of employees whose age is less than 45.
------------------------------------------------------------
SQL> select count(*) from emply where age < 45;
COUNT(*)
----------
9
Q.3 Compute net salary (Netsal = Basic + HRA +DA) for employees in the Payslip table.
-------------------------------------------------------------------------------------
SQL> alter table payslip add(netsal number(6));
Table altered.
SQL> update payslip set netsal=basic+hra+da;
11 rows updated.
SQL> select * from payslip;
EMPC BASIC HRA DA NETSAL
---- ---------- ---------- ---------- ----------
E101 4000 1900 1500 7400
E102 4500 2200 2000 8700
E103 5000 2800 2200 10000
E104 7000 3000 2700 12700
E105 3000 1500 1200 5700
E106 5700 3000 2900 11600
E107 6200 3300 3000 12500
E108 5700 3700 3000 12400
E109 7500 4660 545 12705
E110 5000 300 100 5400
E111 6000 200 120 6320
11 rows selected.
Q.4 Give the employee with empcode ‘E110’ a bonus of 15% of basic
-----------------------------------------------------------------
and update the value held in column Netsal.
-------------------------------------------
SQL> update payslip set netsal=netsal+basic*0.15 where empcode='E110';
1 row updated.
SQL> select * from payslip;
EMPC BASIC HRA DA NETSAL
---- ---------- ---------- ---------- ----------
E101 4000 1900 1500 7400
E102 4500 2200 2000 8700
E103 5000 2800 2200 10000
E104 7000 3000 2700 12700
E105 3000 1500 1200 5700
E106 5700 3000 2900 11600
E107 6200 3300 3000 12500
E108 5700 3700 3000 12400
E109 7500 4660 545 12705
E110 5000 300 100 6150
E111 6000 200 120 6320
11 rows selected.
Q.5 Display Name, Address, Empcode, Net salary, & age from emply and
payslip tables respectively.
SQL> select e.empname, e.address, e.empcode, p.netsal, e.age
2 from emply e, payslip p
3 where e.empcode = p.empcode;
EMPNAME ADDRESS EMPC NETSAL AGE
-------------------- ------------------------------ ---- ---------- ----------
Anjaly Anjaly Nivas, Thiruvalla E101 7400 25
Bobby Alapuzha E102 8700 28
Aravind Chennai E103 10000 31
Lakshmi Mannar E104 12700 55
Daisy Chaithram, Angamaly E105 5700 35
Esha Mumbai E106 11600 23
George Pala E107 12500 70
Prakash Vennikulam E108 12400 36
Madhavan Mynakum, Kottayam E109 12705 46
Anugraha Aparna, Angamaly E110 6150 47
Deva Trichy E111 6320 38
11 rows selected.
Q.6 Retrieve all information from EMPLY table for employees whose basic salary
is more than 6000.
SQL> select * from emply where empcode in (select empcode from payslip where basic > 6000);
EMPC EMPNAME ADDRESS AGE DEPT
---- -------------------- ------------------------------ ---------- ----
E104 Lakshmi Mannar 55 D707
E107 George Pala 70 D909
E109 Madhavan Mynakum, Kottayam 46 D202
|
