Q1. Update table EMPLY, add 15 years of age to employee with EMPCODE ‘E107’.
-----------------------------------------------------------------------------
SQL> update emply set age=age+25 where empcode='E107';
1 row updated.
SQL> select * from emply;
EMPC EMPNAME ADDRESS AGE DEPT
---- -------------------- ------------------------------ ---------- ----
E101 Anjaly Anjaly Nivas, Thiruvalla 25 D302
E102 Bobby Alapuzha 28 D305
E103 Aravind Chennai 31 D305
E104 Lakshmi Mannar 55 D707
E105 Daisy Chaithram, Angamaly 35 D707
E106 Esha Mumbai 23 D707
E107 George Pala 70 D909
E108 Prakash Vennikulam 36 D110
E109 Madhavan Mynakum, Kottayam 46 D202
E110 Anugraha Aparna, Angamaly 47 D301
E111 Deva Trichy 38 D301
EMPC EMPNAME ADDRESS AGE DEPT
---- -------------------- ------------------------------ ---------- ----
E112 Saju Dhanya, Ernakulam 27 D202
E113 Priyesh Priya Nivas, Kottayam 26 D302
13 rows selected.
Q2. Create a view which shows details of employees whose age is between 35 and 45.
----------------------------------------------------------------------------------
SQL> create view empview as select * from emply where age between 35 and 45;
View created.
SQL> select * from empview;
EMPC EMPNAME ADDRESS AGE DEPT
---- -------------------- ------------------------------ ---------- ----
E105 Daisy Chaithram, Angamaly 35 D707
E108 Prakash Vennikulam 36 D110
E111 Deva Trichy 38 D301
Q3. Retrieve empcode, empname, address, Netpay from EMPY1 and PAYSLIP.
Display in ascending order of empcode.
----------------------------------------------------------------------
SQL> select e.empcode, e.empname, e.address, p.basic+p.hra+p.da as netpay
2 from emply e, payslip p where e.empcode=p.empcode order by e.empcode;
EMPC EMPNAME ADDRESS NETPAY
---- -------------------- ------------------------------ ----------
E101 Anjaly Anjaly Nivas, Thiruvalla 7400
E102 Bobby Alapuzha 8700
E103 Aravind Chennai 10000
E104 Lakshmi Mannar 12700
E105 Daisy Chaithram, Angamaly 5700
E106 Esha Mumbai 11600
E107 George Pala 12500
E108 Prakash Vennikulam 12400
E109 Madhavan Mynakum, Kottayam 12705
E110 Anugraha Aparna, Angamaly 5400
E111 Deva Trichy 6320
11 rows selected.
Q4. Create an English like sentence to display the following output from payslip table.
“Employees with empcode E101 draws a basic salary 4000”.
---------------------------------------------------------------------------------------
SQL> select 'Employee with empcode ' || trim(empcode) || ' draws a basic salary ' || trim(basic)
2 from payslip;
'EMPLOYEEWITHEMPCODE'||TRIM(EMPCODE)||'DRAWSABASICSALARY'||TRIM(BASIC)
--------------------------------------------------------------------------------
Employee with empcode E101 draws a basic salary 4000
Employee with empcode E102 draws a basic salary 4500
Employee with empcode E103 draws a basic salary 5000
Employee with empcode E104 draws a basic salary 7000
Employee with empcode E105 draws a basic salary 3000
Employee with empcode E106 draws a basic salary 5700
Employee with empcode E107 draws a basic salary 6200
Employee with empcode E108 draws a basic salary 5700
Employee with empcode E109 draws a basic salary 7500
Employee with empcode E110 draws a basic salary 5000
Employee with empcode E111 draws a basic salary 6000
11 rows selected.
Q5. List the names and address of employees drawing a basic salary between 5000 and 7000.
-----------------------------------------------------------------------------------------
SQL> select e.empname as Name, e.address as Address from emply e, payslip p
2 where e.empcode = p.empcode and p.basic between 5000 and 7000;
NAME ADDRESS
-------------------- ------------------------------
Aravind Chennai
Lakshmi Mannar
Esha Mumbai
George Pala
Prakash Vennikulam
Anugraha Aparna, Angamaly
Deva Trichy
7 rows selected.
Q6. Add new column Total_Sal in the PAYSLIP table and display all details including
total salary for Employees with Empcode > E107.
-----------------------------------------------------------------------------------
SQL> alter table payslip add(total_sal number(6));
Table altered.
SQL> desc payslip
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPCODE NOT NULL VARCHAR2(4)
BASIC NOT NULL NUMBER(6)
HRA NOT NULL NUMBER(6)
DA NOT NULL NUMBER(6)
TOTAL_SAL NUMBER(6)
SQL> update payslip set total_sal=basic+hra+da;
11 rows updated.
SQL> select * from payslip where empcode > 'E107';
EMPC BASIC HRA DA TOTAL_SAL
---- ---------- ---------- ---------- ----------
E108 5700 3700 3000 12400
E109 7500 4660 545 12705
E110 5000 300 100 5400
E111 6000 200 120 6320
SQL> select e.empcode, e.empname, e.address, e.age, e.deptcode,
2 p.basic, p.hra, p.da, p.total_sal
3 from emply e, payslip p
4 where e.empcode = p.empcode and e.empcode > 'E107';
EMPC EMPNAME ADDRESS AGE DEPT
---- -------------------- ------------------------------ ---------- ----
BASIC HRA DA TOTAL_SAL
---------- ---------- ---------- ----------
E108 Prakash Vennikulam 36 D110
5700 3700 3000 12400
E109 Madhavan Mynakum, Kottayam 46 D202
7500 4660 545 12705
E110 Anugraha Aparna, Angamaly 47 D301
5000 300 100 5400
EMPC EMPNAME ADDRESS AGE DEPT
---- -------------------- ------------------------------ ---------- ----
BASIC HRA DA TOTAL_SAL
---------- ---------- ---------- ----------
E111 Deva Trichy 38 D301
6000 200 120 6320
|
