1.'AND' OPERATOR:
QUERY: find the employees details whose salary is
between 20000 and 22000.
select* from EMPLOYEE1 where esal>20000 and esal<22000;
ENAME EID EDESI EEXYR ESAL EDOB ELOCAT DCODE BCODE
------- ----- ----- ------ ---- ---- ----------- ----- -----
bhagya e18 emp 2 21000 17-APR-81 banglore b33 b3
2.'OR' OPERATOR:
QUERY: find the employees details whose salary is
equal to 20000 or 22000.
select* from EMPLOYEE1 where esal=20000 or esal=22000;
ENAME EID EDESI EEXYR ESAL EDOB ELOCAT DCODE BCODE
--------- ----- ----- ------ ---------- --------- ----------
thirupati e2 emp 2 22000 06-MAY-79 delhi b11 b1
raghu e1 crm 2 22000 06-MAY-79 delhi b11 b1
saurab e10 emp 3 22000 30-JAN-87 bombay b22 b2
lalitha e20 emp 4 22000 06-SEP-81 calcutta b41 b4
krishna e26 emp 6 22000 23-AUG-67 hyderabadb51 b5
3.'NOT' OPERATOR:
QUERY: find the employees details whose experience in not 2 years.
select* from EMPLOYEE1 where eexyr!=2 and eexyr!=3 and eexyr!=4
and eexyr!=5;
ENAME EID EDESI EEXYR ESAL EDOB ELOCAT DCODE BCODE
------- ---- ----- ----- ---- ------- --------- ---- -----
safi e3 fm 7 25000 12-NOV-71 delhi b12 b1
sandyae4 emp 1 35000 12-FEB-81 delhi b12 b1
divya e5 cd 6 32000 24-MAR-85 delhi b13 b1
amith e8 emp 6 35000 12-FEB-81 bombay b21 b2
sandeepe11 cd 7 35000 14-OCT-81 bombay b23 b2
susheel e24 emp 6 23000 03-MAR-71 calcutta b43 b4
krishna e26 emp 6 22000 23-AUG-67 hyderabadb51 b5
harry e42 mgr 1 35000 05-OCT-71 chennai b6 b6
4.'LIKE(%)' OPERATOR:
QUERY:find the details of employee whose name starts with letter 'm'
or ends with letter 'n'.
select* from EMPLOYEE1 where ename like 'm%' or ename like '%n';
ENAME EID EDESI EEXYR ESAL EDOB ELOCAT DCODE BCODE
------ ---- ----- ----- ---- -------- --------- ----- ----
mukesh e14 emp 3 23000 29-SEP-78 banglore b31 b3
nareen e17 cd 5 35000 06-JUN-72 banglore b33 b3
kiran e34 emp 4 26000 22-FEB-72 chennai b62 b6
john e37 mgr 4 40000 01-JAN-71 delhi b1 b1
mathew e38 mgr 4 45000 02-FEB-72 bombay b2 b2
martin e39 mgr 2 30000 03-MAR-76 banglore b3 b3
5'LIKE(_)' OPERATOR:
QUERY:find the details of employee whose name has letter 'a'
as third letter.
select* from EMPLOYEE1 where ename like '__a%';
ENAME EID EDESI EEXYR ESAL EDOB ELOCAT DCODE BCODE
----- ----- ----- ----- ---- ------ -------- ---- -----
bhagya e18 emp 2 21000 17-APR-81 banglore b33 b3
chakri e33 fm 5 25000 22-FEB-81 chennai b62 b6
6.'IN' OPERATOR:
QUERY:find the details of clients whose establish years are
1983or1984.
select* from COMPANY_CLIENT where cesyr in(1983,1984);
CNAME CREGNO
--------------------------------------------------
CESYR CPHNO# CFNO
---------- ---------- ----------
CEMAIL
-------------------------
CLOCAT PCODE
-------------------------------- ------------------
bajaj c501
1983 4412345678 4412345679
bajaj@yahoo.com
banglore s19
herohonda c601
1984 4412345680 4412345681
honda@yahoo.com
banglore s19
7.'BETWEEN_AND' OPERATOR:
QUERY:find the details of products where the cost of products
between 20000 and 40000.
select* from PRODUCT where ppriceapproxrs between 20000 and 40000;
PNAME PCODE PPRICEAPPROXRS PWEIGHT
-------- ----- -------------- ----------
scooter s19 40000 100
8.'CONCATENATION(||)' OPERATOR:
QUERY:use concate operator on ad_branch table.
select bcode||'branch is in'||blocat from AD_BRANCH;
BCODE||'BRANCHISIN'||BLOCAT
---------------------------
b1branch is indelhi
b2branch is inbombay
b3branch is inbanglore
b4branch is incalcutta
b5branch is inhyderabad
b6branch is inchennai
9.'ORDER BY' OPERATOR:
QUERY:list the all products order by its code.
select* from PRODUCT order by pcode;
PNAME PCODE PPRICEAPPROXRS PWEIGHT
------- ----- -------------- --------
biscut b2 20 .25
car c3 300000 250
drinks d4 20 .6
laptop l12 45000 3
pen p16 15 .1
scooter s19 40000 100
QUERY:list the all products in descending order by its code.
select* from PRODUCT order by pcode desc;
PNAME PCODE PPRICEAPPROXRS PWEIGHT
------- ----- -------------- ----------
scooter s19 40000 100
pen p16 15 .1
laptop l12 45000 3
drinks d4 20 .6
car c3 300000 250
biscut b2 20 .25
10.'MONTHS_BETWEEN' OPERATOR:
QUERY:find the employee whose id is e12 is younger or elder than
employee whose id is e14.
select months_between((select edob from EMPLOYEE1 where eid='e12'),
(select edob from EMPLOYEE1 where eid='e14')) as YOUNG_OLD from dual;
YOUNG_OLD
----------
48.1290323
11.'SUM' FUNCTION:
QUERY:find the the total balances of clients of ad_company.
select sum(balan) from ACCOUNT_SEC;
SUM(BALAN)
---------
9400000
13.'MAX' FUNCTION:
QUERY:find the details of clients of ad_company whose balance
is maximum.
select* from COMPANY_CLIENT
where cregno in(select cregno from ACCOUNT_SEC
where balan in(select max(balan) from ACCOUNT_SEC));
CNAME CREGNO
------------------------------ --------------------
CESYR CPHNO# CFNO
---------- ---------- ----------
CEMAIL
------------------------------
CLOCAT PCODE
-------------------------------- --------------------
maruthi c101
1987 1112345678 1112345679
maruthi@yahoo.com
delhi c3
zen c201
1980 1112345680 1112345681
zen@yahoo.com
delhi c3
14.'MIN' FUNCTION:
QUERY:find the details of clients of ad_company whose balance
is minimum.
select* from COMPANY_CLIENT
where cregno in(select cregno from ACCOUNT_SEC
where balan in(select min(balan) from ACCOUNT_SEC));
CNAME CREGNO
---------------------------------- --------------------
CESYR CPHNO# CFNO
---------- ---------- ----------
CEMAIL
----------------------------
CLOCAT PCODE
---------------------------------- --------------------
cello c111
19985 4012345680 4012345681
cello@yahoo.com
hyderabad p16
reynolds c901
1994 4012345678 4012345679
ren@yahoo.com
hyderabad p16
15.'AVG' FUNCTION:
QUERY:find the average balance of clients of ad_company.
select avg(balan) from ACCOUNT_SEC;
AVG(BALAN)
----------
783333.333
16.'COUNT' FUNCTION:
QUERY:find the total number of employees in ad_company.
select count(eid) as TOT_EMP from EMPLOYEE1;
TOT_EMP
--------
42
17.'UPDATE' COMMAND:
QUERY:update emplotee salary to 30000 whose eid is e23.
update EMPLOYEE1 set esal=30000 where eid='e23';
select* from EMPLOYEE1 where eid='e23';
ENAME EID EDESI EEXYR ESAL EDOB ELOCAT DCODE BCODE
-------------- --------------------------------------------
sunil e23 cd 3 30000 14-NOV-81 calcutta b43 b4
18.'EQUI_JOIN' OPERATOR:
QUERY:find adtype and adduration of clients of ad_company.
select cname,adtype,adduram from COMPANY_CLIENT,ADS_ON
where COMPANY_CLIENT.pcode=ADS_ON.pcode;
CNAME ADTYPE ADDURAM
-------------- ---------- ----------
parle tvad 1month
britania tvad 1month
maruthi tvad 2months
zen tvad 2months
pepsi internetad 1month
cocacola internetad 1month
pepsi internetad 1month
cocacola ternetad 1month
hcl tvad 3months
dell tvad 3months
bajaj paperad 11months
herohonda paperad 11months
19.'CROSS_JOIN' OPERATOR:
QUERY:use cross join on finance and creative departmets.
select* from FINANCE_DEPT,CREATIVE_DEPT;
FDNAME FDCOD FDPHNO# FDFNO ACODE CDNAME CDCOD CDPHNO# CDFNO
------- ----- ------- --------- ----- -------- ----- ------- ------
finance1b12 1112345694 1112345695 a1 creative1 b13 1112345696 1112345697
finance2b22 2212345694 2212345695 a2 creative1 b13 1112345696 1112345697
finance3b32 4412345696 4412345697 a3 creative1 b13 1112345696 1112345697
20.'UNION' OPERATOR:
QUERY:use union on product and ads_on relations.
select pcode from PRODUCT
union
select pcode from ADS_ON;
PCODE
-----
b2
c3
d4
l12
p16
s19
21.'INTERSECT' OPERATOR:
QUERY:use intersect on department and finance_dept relations.
select dname from DEPARTMENT
intersect
select fdname from FINANCE_DEPT;
DNAME
----------
finance1
finance2
finance3
finance4
finance5
finance6
22.'VIEW' CREATING:
create view STUDIO(stname,stcity)
as select stname,stcity from PRODUCTION_STUDIO
where stcity='hyderabad';
View created.
23.'GROUP BY' OPERATOR:
QUERY:find the total amount paid to different branches of
ad_company as salary.
select bcode as branch,sum(esal) from EMPLOYEE1 group by bcode;
BRANC SUM(ESAL)
----- ----------
b1 199000
b2 210000
b3 186000
b4 201000
b5 197000
b6 192000
QUERY:find the number of employees in each branch.
select bcode as branch,count(*) as noofemp from EMPLOYEE1
group by bcode;
BRANC NOOFEMP
----- ----------
b1 7
b2 7
b3 7
b4 7
b5 7
b6 7
QUERY:find the maximum salary of each designation.
select edesig,max(esal) from EMPLOYEE1 group by edesig;
EDESI MAX(ESAL)
----- ----------
cd 35000
crm 29000
emp 35000
fm 29000
mgr 45000
24.'GROUP BY'with 'HAVING'CLAUSE:
select bcode,count(*) as nofemp,sum(esal) as salofbranch
from EMPLOYEE1 group by bcode having count(*)=7;
BCODE NOFEMP SALOFBRANCH
----- ------ -----------
b1 7 199000
b2 7 210000
b3 7 186000
b4 7 201000
b5 7 197000
b6 7 192000
No comments:
Post a Comment