Q.1 Display records from EMPLY table for employees whose age is between 25 and 45.
-----------------------------------------------------------------------------------
SQL> select * from emply where age between 25 and 45;
EMPC EMPNAME ADDRESS AGE DEPT
---- -------------------- ------------------------------ ---------- ----
E101 Anjaly Anjaly Nivas, Thiruvalla 25 D302
E102 Bobby Alapuzha 28 D305
E103 Aravind Chennai 31 D305
E105 Daisy Chaithram, Angamaly 35 D707
E108 Prakash Vennikulam 36 D110
E111 Deva Trichy 38 D301
E112 Saju Dhanya, Ernakulam 27 D202
E113 Priyesh Priya Nivas, Kottayam 26 D302
8 rows selected.
Q. 2 Retrieve the Deptcode and total no of employees in each department.
------------------------------------------------------------------------
SQL> select deptcode, count(*) as No_of_Employees from emply group by deptcode order by deptcode;
DEPT NO_OF_EMPLOYEES
---- ---------------
D110 1
D202 2
D301 2
D302 2
D305 2
D707 3
D909 1
7 rows selected.
Q.3 Retrieve empcode, empname, address, deptcode for all employees in “account” and “stock”
-------------------------------------------------------------------------------------------
departments.
------------
SQL> select e.empcode, e.empname, e.address, e.deptcode from emply e, dept d
2 where e.deptcode=d.deptcode and (d.deptname='Account' or d.deptname='Stock');
EMPC EMPNAME ADDRESS DEPT
---- -------------------- ------------------------------ ----
E109 Madhavan Mynakum, Kottayam D202
E112 Saju Dhanya, Ernakulam D202
E101 Anjaly Anjaly Nivas, Thiruvalla D302
E113 Priyesh Priya Nivas, Kottayam D302
Q. 4 Display average, maximum and minimum age of employees.
-----------------------------------------------------------
SQL> select avg(age) as Average_Age, max(age) as Maximum_Age, min(age) as Minimum_Age
2 from emply;
AVERAGE_AGE MAXIMUM_AGE MINIMUM_AGE
----------- ----------- -----------
37.4615385 70 23
Q. 5 Delete all records belonging to research department in the EMPY1 table.
-----------------------------------------------------------------------------
SQL> delete from emply where deptcode in (select deptcode from dept where deptname='Research');
3 rows deleted.
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
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
E112 Saju Dhanya, Ernakulam 27 D202
E113 Priyesh Priya Nivas, Kottayam 26 D302
10 rows selected.
|
