create table employee( empid varchar(9) NOT NULL, fname varchar(8), bdate DATE,
salary number(7) not null, dno number(1) not null);
insert into employee values( 4, 'rathna' , '10-dec-1955' ,1200000 , 4);
select * from employee where salary > 200000;
update employee set salary=salary+200 where salary > 10000;
alter table employee rename to newemp;
alter table newemp add sex varchar2(5) not null;
delete from newemp where salary>1;
alter table newemp modify sex number(5);
alter table newemp drop column sex;
alter table newemp add constraint prim primary key(empid);
create table empnew as select * from newemp;
alter table newemp add constraint fk foreign key(empid) references empnew(empid);
create index ix on newemp (empid,fname);
drop index ix;
create sequence newempseq
increment by 1
start with 100
minvalue 0
maxvalue 10000
cache 10;
drop sequence newempseq;
create view idname as select empid,fname from newemp;
drop view idname;
describe idname;
select * from user_constraints;
select distinct * from newemp;
select * from newemp where salary between 1000000 and 1300000;
select * from newemp where salary in (1000000 , 1300000);
select * from newemp where fname like '%na' or fname like '%th' or fname like '%en';
select * from newemp where salary not between 1000000 and 1300000;
select * from newemp where salary not in (1000000 , 1300000);
select * from newemp where fname not like '%na' and fname not like '%th' and fname like '%en';
SQL> select * from newemp where sex is null
2 ;
EMPID FNAME BDATE SALARY DNO SEX
--------- -------- --------- ----------- ---------- -----
2 praveen 16-NOV-87 $500,000 2
4 rathna 10-DEC-55 $1,200,000 4
3 sambath 25-DEC-54 $1,000,000 3
SQL> select * from newemp where sex is not null
2 ;
no rows selected
SQL> select * from newemp where fname like '%r%';
EMPID FNAME BDATE SALARY DNO SEX
--------- -------- --------- ----------- ---------- -----
2 praveen 16-NOV-87 $500,000 2
4 rathna 10-DEC-55 $1,200,000 4
SQL> select * from emp1 where fname between 'p' and 't';
EMPID FNAME BDATE SALARY DNO SEX
--------- -------- --------- ----------- ---------- -----
2 praveen 16-NOV-87 $500,000 2
4 rathna 10-DEC-55 $1,200,000 4
3 sambath 25-DEC-54 $1,000,000 3
equivalent is ===>>>
select * from emp1 where fname > 'p' and fname < 't';
SQL> create table emp1 as select * from newemp;
Table created.
SQL> select ROWNUM, fname from emp1 where ROWNUM=2;
no rows selected
SQL> select * from emp1 order by fname;
EMPID FNAME BDATE SALARY DNO SEX
--------- -------- --------- ----------- ---------- -----
5 kani 30-SEP-91 $100,000 5 f
2 praveen 16-NOV-87 $500,000 2
4 rathna 10-DEC-55 $1,200,000 4
3 sambath 25-DEC-54 $1,000,000 3
SQL> select * from emp1 order by empid;
EMPID FNAME BDATE SALARY DNO SEX
--------- -------- --------- ----------- ---------- -----
2 praveen 16-NOV-87 $500,000 2
3 sambath 25-DEC-54 $1,000,000 3
4 rathna 10-DEC-55 $1,200,000 4
5 kani 30-SEP-91 $100,000 5 f
SQL> select * from emp1 order by 2 desc;
EMPID FNAME BDATE SALARY DNO SEX
--------- -------- --------- ----------- ---------- -----
3 sambath 25-DEC-54 $1,000,000 3
4 rathna 10-DEC-55 $1,200,000 4
2 praveen 16-NOV-87 $500,000 2
5 kani 30-SEP-91 $100,000 5 f
-------------------------------------------------------------------
column alias
SQL> select fname as "FirstName" , bdate "Birthday", salary from emp1 ;
FirstNam Birthday SALARY
-------- --------- -----------
praveen 16-NOV-87 $500,000
rathna 10-DEC-55 $1,200,000
sambath 25-DEC-54 $1,000,000
kani 30-SEP-91 $100,000
SQL> select fname as "FirstName" , bdate "Birthday", salary from emp1 order by "FirstName";
FirstNam Birthday SALARY
-------- --------- -----------
kani 30-SEP-91 $100,000
praveen 16-NOV-87 $500,000
rathna 10-DEC-55 $1,200,000
sambath 25-DEC-54 $1,000,000
-------------------------------------------------------------------------------
order by anamoly
SQL> select distinct fname , bdate from emp1 order by salary;
select distinct fname , bdate from emp1 order by salary
*
ERROR at line 1:
ORA-01791: not a SELECTed expression
SQL> select fname , bdate from emp1 order by salary;
FNAME BDATE
-------- --------- >>>>Distinct!!!
kani 30-SEP-91
praveen 16-NOV-87
sambath 25-DEC-54
rathna 10-DEC-55
SQL> select fname , bdate from emp1 order by salary desc;
FNAME BDATE
-------- ---------
rathna 10-DEC-55
sambath 25-DEC-54
praveen 16-NOV-87
kani 30-SEP-91
-------------------------------------------------------------------------------
Null value and Order by
SQL> select fname , bdate, salary, sex from emp1 order by sex desc;
FNAME BDATE SALARY SEX
-------- --------- ----------- -----
praveen 16-NOV-87 $500,000
rathna 10-DEC-55 $1,200,000
sambath 25-DEC-54 $1,000,000
kani 30-SEP-91 $100,000 f
SQL> select fname , bdate, salary, sex from emp1 order by sex ;
FNAME BDATE SALARY SEX
-------- --------- ----------- -----
kani 30-SEP-91 $100,000 f
sambath 25-DEC-54 $1,000,000
praveen 16-NOV-87 $500,000
rathna 10-DEC-55 $1,200,000
SQL> select fname , bdate, salary, sex from emp1 order by sex null first;
select fname , bdate, salary, sex from emp1 order by sex null first
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select fname , bdate, salary, sex from emp1 order by sex nulls first;
FNAME BDATE SALARY SEX
-------- --------- ----------- -----
praveen 16-NOV-87 $500,000
rathna 10-DEC-55 $1,200,000
sambath 25-DEC-54 $1,000,000
kani 30-SEP-91 $100,000 f
-----------------------------------------------------------------------------
SQL> select upper(fname), upper("firstname") from emp1;
select upper(fname), upper("firstname") from emp1
s *
ERROR at line 1:
ORA-00904: "firstname": invalid identifier
SQL> select upper(fname), upper('firstname') from emp1;
UPPER(FN UPPER('FI
-------- ---------
PRAVEEN FIRSTNAME
RATHNA FIRSTNAME
SAMBATH FIRSTNAME
KANI FIRSTNAME
------------------------------------------------------------------------------
SQL> select rpad(fname,10,'*') from emp1;
RPAD(FNAME,10,'*')
----------------------------------------
praveen***
rathna****
sambath***
kani******
>>>>>>>>>>> also we have left pad function lpad
------------------------------------------------------------------------------
Dual table - Dummy table
-----------------------------------------------------------------------------
SQL> select substr(fname, 1,3) from emp1;
SUBSTR(FNAME
------------
pra
rat
sam
kan
SQL> select substr(fname, 4) from emp1;
SUBSTR(FNAME,4)
--------------------
veen
hna
bath
i
-----------------------------------------------------------------------------
SQL> select fname,length(fname) from emp1;
FNAME LENGTH(FNAME)
-------- -------------
praveen 7
rathna 6
sambath 7
kani 4
------------------------------------------------------------------------------
SQL> select fname, instr(fname,'r') from emp1;
FNAME INSTR(FNAME,'R')
-------- ----------------
praveen 2
rathna 1
sambath 0
kani 0
----------------------------------------------------------------
SQL> select fname from emp1 where instr(fname,'n') >0
2 ;
FNAME
--------
praveen
rathna
kani
-------------------------------------------------------------
SQL> select fname , substr(fname, instr(fname,'.')-1 ) firstname, substr(fname,1, instr(fname,'.')-2 ) inal from emp1;
FNAME
------------------------------
FIRSTNAME
--------------------------------------------------------------------------------
INAL
--------------------------------------------------------------------------------
praveens.
s.
praveen
rathnas.
s.
rathna
FNAME
------------------------------
FIRSTNAME
--------------------------------------------------------------------------------
INAL
--------------------------------------------------------------------------------
sambaths.
s.
sambath
kanis.
s.
FNAME
------------------------------
FIRSTNAME
--------------------------------------------------------------------------------
INAL
--------------------------------------------------------------------------------
kani
<<<<<<<<<<<<<<<<<<<<<<<----------------------------------------------->>>>>>>>>
Concatenation Operator:
SQL> update emp1 set fname= fname || 's.' ;
-------------------------------------------------------------------------------
SQL> select concat(fname,empid) from emp1;
CONCAT(FNAME,EMPID)
---------------------------------------
praveens.2
rathnas.4
sambaths.3
kanis.5
SQL> select fname || 'Employee id is ' || empid || ' and salary is ' || salary f
rom emp1;
FNAME||'EMPLOYEEIDIS'||EMPID||'ANDSALARYIS'||SALARY
--------------------------------------------------------------------------------
praveens.Employee id is 2 and salary is 500000
rathnas.Employee id is 4 and salary is 1200000
sambaths.Employee id is 3 and salary is 1000000
kanis.Employee id is 5 and salary is 100000
-------------------------------------------------------------------------------
SQL> select replace(fname,'r','1') from emp1;
REPLACE(FNAME,'R','1')
------------------------------
p1aveens.
1athnas.
sambaths.
kanis.
-------------------------------------------------------------------------------
SQL> select substr(fname,-5,3) from emp1;
SUBSTR(FNAME
------------
een
hna >>>>>>>>>> displays 3 letters from last 5 position
ath
ani
------------------------------------------------------------------------------
Finding the count of occurance of a substring using length and replace
SQL> select ( length('fredm fedm tedm bread') - length( replace( 'fredm fedm ted
m bread','edm',null) ) ) /3 as occur from dual;
OCCUR
----------
3
-----------------------------------------------------------------------------
SQL> select 'The absolute value of -768763 is ' || abs(-768763) from dual;
'THEABSOLUTEVALUEOF-768763IS'||ABS(-768
---------------------------------------
The absolute value of -768763 is 768763
-------------------------------------------------------------------------------
SQL> select sign(-212),sign(0) from dual;
SIGN(-212) SIGN(0)
---------- ----------
-1 0
------------------------------------------------------------------------------
SQL> select round(234.349,2),trunc(31231.3221,2) from dual;
ROUND(234.349,2) TRUNC(31231.3221,2)
---------------- -------------------
234.35 31231.32
SQL> select mod(23,8) from dual;
MOD(23,8)
----------
7
SQL> select mod(5,8) from dual;
MOD(5,8)
----------
5
SQL> select remainder(25,8) from dual;
REMAINDER(25,8)
---------------
1
------------------------------------------------------------------------------
NVL - null value
SQL> select sex, nvl(sex,'Not Specified') from emp1;
SEX NVL(SEX,'NOTS
----- -------------
Not Specified
Not Specified
Not Specified
f f
------------------------------------------------------------------------------
Decode - Expands value
SQL> select sex, decode(sex, null,'not specified'), decode(sex,'f','Female') fro
m emp1;
SEX DECODE(SEX,NU DECODE
----- ------------- ------
not specified
not specified
not specified
f Female
select distinct state,
Decode(state, 'ny' , 'New York',
'nj' , 'New Jersey',
'other')
from tablename;
-------------------------------------------------------------------------------
SQL> select fname, salary ,
2 case
3 when salary > 1000000 then 1
4 else salary
5 end "My case"
6 from emp1;
FNAME SALARY My case
------------------------------ ---------- ----------
praveens. 500000 500000
rathnas. 1200000 1
sambaths. 1000000 1000000
kanis. 100000 100000
SQL> select fname, salary ,
2 case
3 when salary > 1000000 then 1
4 when salary < 1000000 then 100
5 when salary = 1000000 then 0
6 else null
7 end "Test case"
8 from emp1;
FNAME SALARY Test case
------------------------------ ---------- ----------
praveens. 500000 100
rathnas. 1200000 1
sambaths. 1000000 0
kanis. 100000 100
SQL> select fname, salary
2 from emp1 where
3 salary * ( case
4 when salary > 1000000 then 1
5 when salary < 1000000 then 100
6 when salary = 1000000 then 0
7 else 0
8 end ) > 0;
FNAME SALARY
------------------------------ ----------
praveens. 500000
rathnas. 1200000
kanis. 100000
------------------------------------------------------------------------------
SQL> select count(sex) from emp1;
COUNT(SEX)
---------- >>> excludes null
1
SQL> select count(*) from emp1;
COUNT(*)
----------
4
-----------------------------------------------------------------------------
SQL> select min(bdate), max(bdate) from emp1;
MIN(BDATE MAX(BDATE
--------- ---------
25-DEC-54 30-SEP-91
SQL> select min(fname), max(fname) from emp1;
MIN(FNAME) MAX(FNAME)
------------------------------ ------------------------------
kanis. sambaths.
-----------------------------------------------------------------------------
Using AVG and CASE
SQL> select avg( case when salary > 1000000 then 7735
2 when salary < 1000000 then 8898
3 when salary = 1000000 then 0
4 else 6
5 end) as AVG
6 from emp1;
AVG
----------
6885.8
------------------------------------------------------------------------------
Group by and distinct return the same result
See what is ora-00979 - wowwwwwwwwwww!! i found the answer , it says every column in the select list except the aggregate must be repeated in the group by clause.
SQL> select fname,bdate,sex from emp1 where sex='m' group by sex,bdate,fname;
FNAME BDATE SEX
------------------------------ --------- -----
sambaths. 25-DEC-54 m
venkat 13-SEP-13 m
praveens. 16-NOV-87 m
Group by + order by - column in order by must be in selected list
SQL> select fname,bdate,sex from emp1 group by sex,bdate,fname order by bdate;
FNAME BDATE SEX
------------------------------ --------- -----
sambaths. 25-DEC-54 m
rathnas. 10-DEC-55 f
praveens. 16-NOV-87 m
radhika 28-SEP-91 f
kanis. 30-SEP-91 f
venkat 13-SEP-13 m
shanthini 29-OCT-13 f
7 rows selected.
Having Clause - purpose is to filter group result
SQL> select fname,bdate,sex from emp1 group by sex,bdate,fname having bdate > '3
0-sep-1991' order by bdate;
FNAME BDATE SEX
------------------------------ --------- -----
venkat 13-SEP-13 m
shanthini 29-OCT-13 f
>>>>>>>>>> where clause eliminate rows
>>>>>>>>>> columns in having clause must be either found in group by list or it should be a aggregate function
SQL> select fname,bdate,sex from emp1 group by sex,bdate,fname having count(fname) > 0;
FNAME BDATE SEX
------------------------------ --------- -----
radhika 28-SEP-91 f
rathnas. 10-DEC-55 f
sambaths. 25-DEC-54 m
shanthini 29-OCT-13 f
venkat 13-SEP-13 m
praveens. 16-NOV-87 m
kanis. 30-SEP-91 f
7 rows selected.
SQL> select fname,bdate,sex from emp1 group by sex,bdate,fname having count(fname) > 1;
FNAME BDATE SEX
------------------------------ --------- -----
praveens. 16-NOV-87 m
>>>>>>>>> constant such as text, number literal or function that doesnt take any parameter may be listed in the select list without being repeated in group by clause
SQL> select 'hi' greetings , fname, sex , sysdate Today , salary from emp1 group
by fname,sex,salary;
GR FNAME SEX TODAY SALARY
-- ------------------------------ ----- --------- ----------
hi praveens. m 10-NOV-13 232434
hi rathnas. f 10-NOV-13 1200000
hi kanis. f 10-NOV-13 100000
hi venkat m 10-NOV-13 23213
hi sambaths. m 10-NOV-13 1000000
hi praveens. m 10-NOV-13 500000
hi shanthini f 10-NOV-13 23213
hi radhika f 10-NOV-13 45000
8 rows selected.
SQL> rename department to dept;
Table renamed.
Nested Aggregate!!!
SQL> select max(count(*)) from emp group by deptno;
MAX(COUNT(*))
-------------
3
-------------------------------------------------------------------------------
<<<<<<<<<<<<<<<<<<<<<<<<Joins>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1. Narrowing down results
SQL> select ename, salary, location,manager_no,sex from dept d, emp e where d.dept_no=e.deptno and (ename like 'a%' or ename like 's%') order by ename asc;
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
adharsh 87644 GHT1 8 m
adhi 89342 GHT1 9 m
sam 1000 GHT1 5
soumya 43581 GHT1 5 f
surya 23231 GHT3 7 f
SQL> select ename, salary, location,manager_no,sex from dept d join emp e using
(deptno);
select ename, salary, location,manager_no,sex from dept d join emp e using (dept
no)
*
ERROR at line 1:
ORA-00904: "D"."DEPTNO": invalid identifier
>>> you should have the same column for using the USING keyword and also alias are not allowed
ON
SQL> select ename, salary, location,manager_no,sex from dept d join emp e on(e.d
eptno=d.dept_no);
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
praveen 23213 GHT2 6 m
sam 1000 GHT1 5
kani 43222 GHT3 7 f
surya 23231 GHT3 7 f
naveensai 25567 GHT1 8 m
matlang 67565 GHT1 8 m
adharsh 87644 GHT1 8 m
adhi 89342 GHT1 9 m
soumya 43581 GHT1 5 f
9 rows selected.
Natural join :
SQL> select ename, salary, location,manager_no,sex from dept d join emp e on(e.
eptno=d.dept_no);
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
praveen 23213 GHT2 6 m
sam 1000 GHT1 5
kani 43222 GHT3 7 f
surya 23231 GHT3 7 f
naveensai 25567 GHT1 8 m
matlang 67565 GHT1 8 m
adharsh 87644 GHT1 8 m
adhi 89342 GHT1 9 m
soumya 43581 GHT1 5 f
9 rows selected.
SQL> select ename, salary, location,manager_no,sex from dept natural join emp;
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
praveen 23213 GHT1 5 m
sam 1000 GHT1 5
kani 43222 GHT1 5 f
surya 23231 GHT1 5 f
naveensai 25567 GHT1 5 m
matlang 67565 GHT1 5 m
adharsh 87644 GHT1 5 m
adhi 89342 GHT1 5 m
soumya 43581 GHT1 5 f
praveen 23213 GHT2 6 m
sam 1000 GHT2 6
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
kani 43222 GHT2 6 f
surya 23231 GHT2 6 f
naveensai 25567 GHT2 6 m
matlang 67565 GHT2 6 m
adharsh 87644 GHT2 6 m
adhi 89342 GHT2 6 m
soumya 43581 GHT2 6 f
praveen 23213 GHT3 7 m
sam 1000 GHT3 7
kani 43222 GHT3 7 f
surya 23231 GHT3 7 f
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
naveensai 25567 GHT3 7 m
matlang 67565 GHT3 7 m
adharsh 87644 GHT3 7 m
adhi 89342 GHT3 7 m
soumya 43581 GHT3 7 f
praveen 23213 GHT1 8 m
sam 1000 GHT1 8
kani 43222 GHT1 8 f
surya 23231 GHT1 8 f
naveensai 25567 GHT1 8 m
matlang 67565 GHT1 8 m
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
adharsh 87644 GHT1 8 m
adhi 89342 GHT1 8 m
soumya 43581 GHT1 8 f
praveen 23213 GHT1 9 m
sam 1000 GHT1 9
kani 43222 GHT1 9 f
surya 23231 GHT1 9 f
naveensai 25567 GHT1 9 m
matlang 67565 GHT1 9 m
adharsh 87644 GHT1 9 m
adhi 89342 GHT1 9 m
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
soumya 43581 GHT1 9 f
45 rows selected.
-----------------------------------------------------------------------------
CARTESIAN PRODUCT
SQL> select count(*) from emp,dept;
COUNT(*)
----------
45
-----------------------------------------------------------------------------
Sub-Query
Scalar sub query aka single row sub query - returns one row and one column
SQL> select ename, sex, salary from emp where salary=(select max(salary) from emp);
ENAME SEX SALARY
------------------------------ ---- ----------
soumya f 99999
SQL> select ename, sex, salary from emp where salary IN (select salary from emp
where ename like 's%');
ENAME SEX SALARY
------------------------------ ---- ----------
sam 1000
soumya f 99999
surya f 34234
shanthini f 54323
SQL> select ename, sex, salary from emp where salary NOT IN (select salary from
emp where ename like 's%');
ENAME SEX SALARY
------------------------------ ---- ----------
praveen m 23243
naveensai m 34453
kani f 34532
Subquery nesting max goes to a depth of 5 levels !!!
>>>>>>>>>Sub queries and join
SQL> select ename,sex,salary from emp where deptno IN (select dept_no from dept)
;
ENAME SEX SALARY
------------------------------ ---- ----------
praveen m 23243
sam 1000
naveensai m 34453
kani f 34532
soumya f 99999
surya f 34234
shanthini f 54323
7 rows selected.
Finding the department with the highest number of employees
SQL> select deptno, count(*) from emp group by(deptno) having count(*)= ( select max(count(empno)) from emp group by deptno);
DEPTNO COUNT(*)
---------- ----------
4 2
5 2
3 2
SQL> select * from emp;
EMPNO ENAME SALARY DEPTNO SEX
---------- ------------------------------ ---------- ---------- ----
1004 praveen 23243 4 m
1003 sam 1000 3
1005 naveensai 34453 5 m
1006 kani 34532 6 f
1007 soumya 99999 3 f
1008 surya 34234 4 f
1009 shanthini 54323 5 f
7 rows selected.
-------------------------------------------------------------------------------
Correlated subquery - it references the outer query plus it executes repeatedly also
EXISTS : checks if a particular value from a table exists in another table...!!
SQL> select ename, salary from emp e where EXISTS ( select 'X' from dept where e.deptno= dept_no);
ENAME SALARY
------------------------------ ----------
praveen 23243
sam 1000
naveensai 34453
kani 34532
soumya 99999
surya 34234
shanthini 54323
Finding the departments with less than 2 employees
SQL> select dept_no, dept_name, location from dept outer where 2 > (select count(*) from emp where emp.deptno=outer.dept_no);
DEPT_NO DEPT_NAME LOCATION
---------- --------------- ---------------
6 HSE GHT3
7 IS GHT3
7 rows selected.
-----------------------------------------------------------------------------
<<<<<<<<<<<<<<<<<<INLINE VIEWS AND SCALAR SUBQUERY EXPRESSIONS>>>>>>>>>>>>
Inline view allows to use a virtual table in from clause - useful in many ways some example are below
SQL> select deptno, d.location, d.manager_no from ( select deptno, count(*) from
emp where rownum=1 group by(deptno) order by count(*) desc), dept d where d.dep
t_no=deptno;
DEPTNO LOCATION MANAGER_NO
---------- --------------- ----------
4 GHT1 6
Scalar Sub query can be used in following clause
1. select city, state, zip, (select count(*) from student s where s.zip=z.zip ) as student_count from zipcode z where state = 'ct';
2.select student_id, last_name from student s where ( select count(*) from enrollment e where s.student_id = e.student_id) > ( select avg(count(*)) from enrollment
group by student_id) order by 1
3. Select student_id, lastname from student s where student_id between 230 and 335 order by ( select count(*) from enrollment e where s.student_id=e.student_id) desc
4. select course_no, cost , case when cost <= ( select avg(cost) from course ) then cost*1.5
when cost= ( select max(cost) from course ) then ( select cost from course where course_no=20)
else cost
end "Testcase"
from course
where course_no in (20,80)
order by 2
---------------------------------------------------------------------------
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<ANY SOME ALL >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> select ename, sex, deptno from emp where salary < any ( 100000,1200000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
praveen m 4
sam 3
naveensai m 5
kani f 6
soumya f 3
surya f 4
shanthini f 5
7 rows selected.
SQL>
SQL> select ename, sex, deptno from emp where salary < any ( 100000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
praveen m 4
sam 3
naveensai m 5
kani f 6
soumya f 3
surya f 4
shanthini f 5
7 rows selected.
SQL> select ename, sex, deptno from emp where salary < any ( 10000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
sam 3
<<<<<<<<<<<<<<<<<<<ALL>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> select ename, sex, deptno from emp where salary < all ( 10000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
sam 3
SQL> select ename, sex, deptno from emp where salary > all ( 10000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
praveen m 4
naveensai m 5
kani f 6
soumya f 3
surya f 4
shanthini f 5
6 rows selected.
SQL> select ename, sex, deptno from emp where salary > all ( 100000,12000);
no rows selected
SQL> select ename, sex, deptno from emp where salary > all ( 10000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
praveen m 4
naveensai m 5
kani f 6
soumya f 3
surya f 4
shanthini f 5
6 rows selected.
SQL> select ename, sex, deptno from emp where salary > all ( 20000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
praveen m 4
naveensai m 5
kani f 6
soumya f 3
surya f 4
shanthini f 5
6 rows selected.
SQL> select ename, sex, deptno from emp where salary > all ( 50000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
soumya f 3
shanthini f 5
----------------------------------------------------------------------------
salary number(7) not null, dno number(1) not null);
insert into employee values( 4, 'rathna' , '10-dec-1955' ,1200000 , 4);
select * from employee where salary > 200000;
update employee set salary=salary+200 where salary > 10000;
alter table employee rename to newemp;
alter table newemp add sex varchar2(5) not null;
delete from newemp where salary>1;
alter table newemp modify sex number(5);
alter table newemp drop column sex;
alter table newemp add constraint prim primary key(empid);
create table empnew as select * from newemp;
alter table newemp add constraint fk foreign key(empid) references empnew(empid);
create index ix on newemp (empid,fname);
drop index ix;
create sequence newempseq
increment by 1
start with 100
minvalue 0
maxvalue 10000
cache 10;
drop sequence newempseq;
create view idname as select empid,fname from newemp;
drop view idname;
describe idname;
select * from user_constraints;
select distinct * from newemp;
select * from newemp where salary between 1000000 and 1300000;
select * from newemp where salary in (1000000 , 1300000);
select * from newemp where fname like '%na' or fname like '%th' or fname like '%en';
select * from newemp where salary not between 1000000 and 1300000;
select * from newemp where salary not in (1000000 , 1300000);
select * from newemp where fname not like '%na' and fname not like '%th' and fname like '%en';
SQL> select * from newemp where sex is null
2 ;
EMPID FNAME BDATE SALARY DNO SEX
--------- -------- --------- ----------- ---------- -----
2 praveen 16-NOV-87 $500,000 2
4 rathna 10-DEC-55 $1,200,000 4
3 sambath 25-DEC-54 $1,000,000 3
SQL> select * from newemp where sex is not null
2 ;
no rows selected
SQL> select * from newemp where fname like '%r%';
EMPID FNAME BDATE SALARY DNO SEX
--------- -------- --------- ----------- ---------- -----
2 praveen 16-NOV-87 $500,000 2
4 rathna 10-DEC-55 $1,200,000 4
SQL> select * from emp1 where fname between 'p' and 't';
EMPID FNAME BDATE SALARY DNO SEX
--------- -------- --------- ----------- ---------- -----
2 praveen 16-NOV-87 $500,000 2
4 rathna 10-DEC-55 $1,200,000 4
3 sambath 25-DEC-54 $1,000,000 3
equivalent is ===>>>
select * from emp1 where fname > 'p' and fname < 't';
SQL> create table emp1 as select * from newemp;
Table created.
SQL> select ROWNUM, fname from emp1 where ROWNUM=2;
no rows selected
SQL> select * from emp1 order by fname;
EMPID FNAME BDATE SALARY DNO SEX
--------- -------- --------- ----------- ---------- -----
5 kani 30-SEP-91 $100,000 5 f
2 praveen 16-NOV-87 $500,000 2
4 rathna 10-DEC-55 $1,200,000 4
3 sambath 25-DEC-54 $1,000,000 3
SQL> select * from emp1 order by empid;
EMPID FNAME BDATE SALARY DNO SEX
--------- -------- --------- ----------- ---------- -----
2 praveen 16-NOV-87 $500,000 2
3 sambath 25-DEC-54 $1,000,000 3
4 rathna 10-DEC-55 $1,200,000 4
5 kani 30-SEP-91 $100,000 5 f
SQL> select * from emp1 order by 2 desc;
EMPID FNAME BDATE SALARY DNO SEX
--------- -------- --------- ----------- ---------- -----
3 sambath 25-DEC-54 $1,000,000 3
4 rathna 10-DEC-55 $1,200,000 4
2 praveen 16-NOV-87 $500,000 2
5 kani 30-SEP-91 $100,000 5 f
-------------------------------------------------------------------
column alias
SQL> select fname as "FirstName" , bdate "Birthday", salary from emp1 ;
FirstNam Birthday SALARY
-------- --------- -----------
praveen 16-NOV-87 $500,000
rathna 10-DEC-55 $1,200,000
sambath 25-DEC-54 $1,000,000
kani 30-SEP-91 $100,000
SQL> select fname as "FirstName" , bdate "Birthday", salary from emp1 order by "FirstName";
FirstNam Birthday SALARY
-------- --------- -----------
kani 30-SEP-91 $100,000
praveen 16-NOV-87 $500,000
rathna 10-DEC-55 $1,200,000
sambath 25-DEC-54 $1,000,000
-------------------------------------------------------------------------------
order by anamoly
SQL> select distinct fname , bdate from emp1 order by salary;
select distinct fname , bdate from emp1 order by salary
*
ERROR at line 1:
ORA-01791: not a SELECTed expression
SQL> select fname , bdate from emp1 order by salary;
FNAME BDATE
-------- --------- >>>>Distinct!!!
kani 30-SEP-91
praveen 16-NOV-87
sambath 25-DEC-54
rathna 10-DEC-55
SQL> select fname , bdate from emp1 order by salary desc;
FNAME BDATE
-------- ---------
rathna 10-DEC-55
sambath 25-DEC-54
praveen 16-NOV-87
kani 30-SEP-91
-------------------------------------------------------------------------------
Null value and Order by
SQL> select fname , bdate, salary, sex from emp1 order by sex desc;
FNAME BDATE SALARY SEX
-------- --------- ----------- -----
praveen 16-NOV-87 $500,000
rathna 10-DEC-55 $1,200,000
sambath 25-DEC-54 $1,000,000
kani 30-SEP-91 $100,000 f
SQL> select fname , bdate, salary, sex from emp1 order by sex ;
FNAME BDATE SALARY SEX
-------- --------- ----------- -----
kani 30-SEP-91 $100,000 f
sambath 25-DEC-54 $1,000,000
praveen 16-NOV-87 $500,000
rathna 10-DEC-55 $1,200,000
SQL> select fname , bdate, salary, sex from emp1 order by sex null first;
select fname , bdate, salary, sex from emp1 order by sex null first
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select fname , bdate, salary, sex from emp1 order by sex nulls first;
FNAME BDATE SALARY SEX
-------- --------- ----------- -----
praveen 16-NOV-87 $500,000
rathna 10-DEC-55 $1,200,000
sambath 25-DEC-54 $1,000,000
kani 30-SEP-91 $100,000 f
-----------------------------------------------------------------------------
SQL> select upper(fname), upper("firstname") from emp1;
select upper(fname), upper("firstname") from emp1
s *
ERROR at line 1:
ORA-00904: "firstname": invalid identifier
SQL> select upper(fname), upper('firstname') from emp1;
UPPER(FN UPPER('FI
-------- ---------
PRAVEEN FIRSTNAME
RATHNA FIRSTNAME
SAMBATH FIRSTNAME
KANI FIRSTNAME
------------------------------------------------------------------------------
SQL> select rpad(fname,10,'*') from emp1;
RPAD(FNAME,10,'*')
----------------------------------------
praveen***
rathna****
sambath***
kani******
>>>>>>>>>>> also we have left pad function lpad
------------------------------------------------------------------------------
Dual table - Dummy table
-----------------------------------------------------------------------------
SQL> select substr(fname, 1,3) from emp1;
SUBSTR(FNAME
------------
pra
rat
sam
kan
SQL> select substr(fname, 4) from emp1;
SUBSTR(FNAME,4)
--------------------
veen
hna
bath
i
-----------------------------------------------------------------------------
SQL> select fname,length(fname) from emp1;
FNAME LENGTH(FNAME)
-------- -------------
praveen 7
rathna 6
sambath 7
kani 4
------------------------------------------------------------------------------
SQL> select fname, instr(fname,'r') from emp1;
FNAME INSTR(FNAME,'R')
-------- ----------------
praveen 2
rathna 1
sambath 0
kani 0
----------------------------------------------------------------
SQL> select fname from emp1 where instr(fname,'n') >0
2 ;
FNAME
--------
praveen
rathna
kani
-------------------------------------------------------------
SQL> select fname , substr(fname, instr(fname,'.')-1 ) firstname, substr(fname,1, instr(fname,'.')-2 ) inal from emp1;
FNAME
------------------------------
FIRSTNAME
--------------------------------------------------------------------------------
INAL
--------------------------------------------------------------------------------
praveens.
s.
praveen
rathnas.
s.
rathna
FNAME
------------------------------
FIRSTNAME
--------------------------------------------------------------------------------
INAL
--------------------------------------------------------------------------------
sambaths.
s.
sambath
kanis.
s.
FNAME
------------------------------
FIRSTNAME
--------------------------------------------------------------------------------
INAL
--------------------------------------------------------------------------------
kani
<<<<<<<<<<<<<<<<<<<<<<<----------------------------------------------->>>>>>>>>
Concatenation Operator:
SQL> update emp1 set fname= fname || 's.' ;
-------------------------------------------------------------------------------
SQL> select concat(fname,empid) from emp1;
CONCAT(FNAME,EMPID)
---------------------------------------
praveens.2
rathnas.4
sambaths.3
kanis.5
SQL> select fname || 'Employee id is ' || empid || ' and salary is ' || salary f
rom emp1;
FNAME||'EMPLOYEEIDIS'||EMPID||'ANDSALARYIS'||SALARY
--------------------------------------------------------------------------------
praveens.Employee id is 2 and salary is 500000
rathnas.Employee id is 4 and salary is 1200000
sambaths.Employee id is 3 and salary is 1000000
kanis.Employee id is 5 and salary is 100000
-------------------------------------------------------------------------------
SQL> select replace(fname,'r','1') from emp1;
REPLACE(FNAME,'R','1')
------------------------------
p1aveens.
1athnas.
sambaths.
kanis.
-------------------------------------------------------------------------------
SQL> select substr(fname,-5,3) from emp1;
SUBSTR(FNAME
------------
een
hna >>>>>>>>>> displays 3 letters from last 5 position
ath
ani
------------------------------------------------------------------------------
Finding the count of occurance of a substring using length and replace
SQL> select ( length('fredm fedm tedm bread') - length( replace( 'fredm fedm ted
m bread','edm',null) ) ) /3 as occur from dual;
OCCUR
----------
3
-----------------------------------------------------------------------------
SQL> select 'The absolute value of -768763 is ' || abs(-768763) from dual;
'THEABSOLUTEVALUEOF-768763IS'||ABS(-768
---------------------------------------
The absolute value of -768763 is 768763
-------------------------------------------------------------------------------
SQL> select sign(-212),sign(0) from dual;
SIGN(-212) SIGN(0)
---------- ----------
-1 0
------------------------------------------------------------------------------
SQL> select round(234.349,2),trunc(31231.3221,2) from dual;
ROUND(234.349,2) TRUNC(31231.3221,2)
---------------- -------------------
234.35 31231.32
SQL> select mod(23,8) from dual;
MOD(23,8)
----------
7
SQL> select mod(5,8) from dual;
MOD(5,8)
----------
5
SQL> select remainder(25,8) from dual;
REMAINDER(25,8)
---------------
1
------------------------------------------------------------------------------
NVL - null value
SQL> select sex, nvl(sex,'Not Specified') from emp1;
SEX NVL(SEX,'NOTS
----- -------------
Not Specified
Not Specified
Not Specified
f f
------------------------------------------------------------------------------
Decode - Expands value
SQL> select sex, decode(sex, null,'not specified'), decode(sex,'f','Female') fro
m emp1;
SEX DECODE(SEX,NU DECODE
----- ------------- ------
not specified
not specified
not specified
f Female
select distinct state,
Decode(state, 'ny' , 'New York',
'nj' , 'New Jersey',
'other')
from tablename;
-------------------------------------------------------------------------------
SQL> select fname, salary ,
2 case
3 when salary > 1000000 then 1
4 else salary
5 end "My case"
6 from emp1;
FNAME SALARY My case
------------------------------ ---------- ----------
praveens. 500000 500000
rathnas. 1200000 1
sambaths. 1000000 1000000
kanis. 100000 100000
SQL> select fname, salary ,
2 case
3 when salary > 1000000 then 1
4 when salary < 1000000 then 100
5 when salary = 1000000 then 0
6 else null
7 end "Test case"
8 from emp1;
FNAME SALARY Test case
------------------------------ ---------- ----------
praveens. 500000 100
rathnas. 1200000 1
sambaths. 1000000 0
kanis. 100000 100
SQL> select fname, salary
2 from emp1 where
3 salary * ( case
4 when salary > 1000000 then 1
5 when salary < 1000000 then 100
6 when salary = 1000000 then 0
7 else 0
8 end ) > 0;
FNAME SALARY
------------------------------ ----------
praveens. 500000
rathnas. 1200000
kanis. 100000
------------------------------------------------------------------------------
SQL> select count(sex) from emp1;
COUNT(SEX)
---------- >>> excludes null
1
SQL> select count(*) from emp1;
COUNT(*)
----------
4
-----------------------------------------------------------------------------
SQL> select min(bdate), max(bdate) from emp1;
MIN(BDATE MAX(BDATE
--------- ---------
25-DEC-54 30-SEP-91
SQL> select min(fname), max(fname) from emp1;
MIN(FNAME) MAX(FNAME)
------------------------------ ------------------------------
kanis. sambaths.
-----------------------------------------------------------------------------
Using AVG and CASE
SQL> select avg( case when salary > 1000000 then 7735
2 when salary < 1000000 then 8898
3 when salary = 1000000 then 0
4 else 6
5 end) as AVG
6 from emp1;
AVG
----------
6885.8
------------------------------------------------------------------------------
Group by and distinct return the same result
See what is ora-00979 - wowwwwwwwwwww!! i found the answer , it says every column in the select list except the aggregate must be repeated in the group by clause.
SQL> select fname,bdate,sex from emp1 where sex='m' group by sex,bdate,fname;
FNAME BDATE SEX
------------------------------ --------- -----
sambaths. 25-DEC-54 m
venkat 13-SEP-13 m
praveens. 16-NOV-87 m
Group by + order by - column in order by must be in selected list
SQL> select fname,bdate,sex from emp1 group by sex,bdate,fname order by bdate;
FNAME BDATE SEX
------------------------------ --------- -----
sambaths. 25-DEC-54 m
rathnas. 10-DEC-55 f
praveens. 16-NOV-87 m
radhika 28-SEP-91 f
kanis. 30-SEP-91 f
venkat 13-SEP-13 m
shanthini 29-OCT-13 f
7 rows selected.
Having Clause - purpose is to filter group result
SQL> select fname,bdate,sex from emp1 group by sex,bdate,fname having bdate > '3
0-sep-1991' order by bdate;
FNAME BDATE SEX
------------------------------ --------- -----
venkat 13-SEP-13 m
shanthini 29-OCT-13 f
>>>>>>>>>> where clause eliminate rows
>>>>>>>>>> columns in having clause must be either found in group by list or it should be a aggregate function
SQL> select fname,bdate,sex from emp1 group by sex,bdate,fname having count(fname) > 0;
FNAME BDATE SEX
------------------------------ --------- -----
radhika 28-SEP-91 f
rathnas. 10-DEC-55 f
sambaths. 25-DEC-54 m
shanthini 29-OCT-13 f
venkat 13-SEP-13 m
praveens. 16-NOV-87 m
kanis. 30-SEP-91 f
7 rows selected.
SQL> select fname,bdate,sex from emp1 group by sex,bdate,fname having count(fname) > 1;
FNAME BDATE SEX
------------------------------ --------- -----
praveens. 16-NOV-87 m
>>>>>>>>> constant such as text, number literal or function that doesnt take any parameter may be listed in the select list without being repeated in group by clause
SQL> select 'hi' greetings , fname, sex , sysdate Today , salary from emp1 group
by fname,sex,salary;
GR FNAME SEX TODAY SALARY
-- ------------------------------ ----- --------- ----------
hi praveens. m 10-NOV-13 232434
hi rathnas. f 10-NOV-13 1200000
hi kanis. f 10-NOV-13 100000
hi venkat m 10-NOV-13 23213
hi sambaths. m 10-NOV-13 1000000
hi praveens. m 10-NOV-13 500000
hi shanthini f 10-NOV-13 23213
hi radhika f 10-NOV-13 45000
8 rows selected.
SQL> rename department to dept;
Table renamed.
Nested Aggregate!!!
SQL> select max(count(*)) from emp group by deptno;
MAX(COUNT(*))
-------------
3
-------------------------------------------------------------------------------
<<<<<<<<<<<<<<<<<<<<<<<<Joins>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1. Narrowing down results
SQL> select ename, salary, location,manager_no,sex from dept d, emp e where d.dept_no=e.deptno and (ename like 'a%' or ename like 's%') order by ename asc;
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
adharsh 87644 GHT1 8 m
adhi 89342 GHT1 9 m
sam 1000 GHT1 5
soumya 43581 GHT1 5 f
surya 23231 GHT3 7 f
SQL> select ename, salary, location,manager_no,sex from dept d join emp e using
(deptno);
select ename, salary, location,manager_no,sex from dept d join emp e using (dept
no)
*
ERROR at line 1:
ORA-00904: "D"."DEPTNO": invalid identifier
>>> you should have the same column for using the USING keyword and also alias are not allowed
ON
SQL> select ename, salary, location,manager_no,sex from dept d join emp e on(e.d
eptno=d.dept_no);
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
praveen 23213 GHT2 6 m
sam 1000 GHT1 5
kani 43222 GHT3 7 f
surya 23231 GHT3 7 f
naveensai 25567 GHT1 8 m
matlang 67565 GHT1 8 m
adharsh 87644 GHT1 8 m
adhi 89342 GHT1 9 m
soumya 43581 GHT1 5 f
9 rows selected.
Natural join :
SQL> select ename, salary, location,manager_no,sex from dept d join emp e on(e.
eptno=d.dept_no);
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
praveen 23213 GHT2 6 m
sam 1000 GHT1 5
kani 43222 GHT3 7 f
surya 23231 GHT3 7 f
naveensai 25567 GHT1 8 m
matlang 67565 GHT1 8 m
adharsh 87644 GHT1 8 m
adhi 89342 GHT1 9 m
soumya 43581 GHT1 5 f
9 rows selected.
SQL> select ename, salary, location,manager_no,sex from dept natural join emp;
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
praveen 23213 GHT1 5 m
sam 1000 GHT1 5
kani 43222 GHT1 5 f
surya 23231 GHT1 5 f
naveensai 25567 GHT1 5 m
matlang 67565 GHT1 5 m
adharsh 87644 GHT1 5 m
adhi 89342 GHT1 5 m
soumya 43581 GHT1 5 f
praveen 23213 GHT2 6 m
sam 1000 GHT2 6
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
kani 43222 GHT2 6 f
surya 23231 GHT2 6 f
naveensai 25567 GHT2 6 m
matlang 67565 GHT2 6 m
adharsh 87644 GHT2 6 m
adhi 89342 GHT2 6 m
soumya 43581 GHT2 6 f
praveen 23213 GHT3 7 m
sam 1000 GHT3 7
kani 43222 GHT3 7 f
surya 23231 GHT3 7 f
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
naveensai 25567 GHT3 7 m
matlang 67565 GHT3 7 m
adharsh 87644 GHT3 7 m
adhi 89342 GHT3 7 m
soumya 43581 GHT3 7 f
praveen 23213 GHT1 8 m
sam 1000 GHT1 8
kani 43222 GHT1 8 f
surya 23231 GHT1 8 f
naveensai 25567 GHT1 8 m
matlang 67565 GHT1 8 m
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
adharsh 87644 GHT1 8 m
adhi 89342 GHT1 8 m
soumya 43581 GHT1 8 f
praveen 23213 GHT1 9 m
sam 1000 GHT1 9
kani 43222 GHT1 9 f
surya 23231 GHT1 9 f
naveensai 25567 GHT1 9 m
matlang 67565 GHT1 9 m
adharsh 87644 GHT1 9 m
adhi 89342 GHT1 9 m
ENAME SALARY LOCATION MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
soumya 43581 GHT1 9 f
45 rows selected.
-----------------------------------------------------------------------------
CARTESIAN PRODUCT
SQL> select count(*) from emp,dept;
COUNT(*)
----------
45
-----------------------------------------------------------------------------
Sub-Query
Scalar sub query aka single row sub query - returns one row and one column
SQL> select ename, sex, salary from emp where salary=(select max(salary) from emp);
ENAME SEX SALARY
------------------------------ ---- ----------
soumya f 99999
SQL> select ename, sex, salary from emp where salary IN (select salary from emp
where ename like 's%');
ENAME SEX SALARY
------------------------------ ---- ----------
sam 1000
soumya f 99999
surya f 34234
shanthini f 54323
SQL> select ename, sex, salary from emp where salary NOT IN (select salary from
emp where ename like 's%');
ENAME SEX SALARY
------------------------------ ---- ----------
praveen m 23243
naveensai m 34453
kani f 34532
Subquery nesting max goes to a depth of 5 levels !!!
>>>>>>>>>Sub queries and join
SQL> select ename,sex,salary from emp where deptno IN (select dept_no from dept)
;
ENAME SEX SALARY
------------------------------ ---- ----------
praveen m 23243
sam 1000
naveensai m 34453
kani f 34532
soumya f 99999
surya f 34234
shanthini f 54323
7 rows selected.
Finding the department with the highest number of employees
SQL> select deptno, count(*) from emp group by(deptno) having count(*)= ( select max(count(empno)) from emp group by deptno);
DEPTNO COUNT(*)
---------- ----------
4 2
5 2
3 2
SQL> select * from emp;
EMPNO ENAME SALARY DEPTNO SEX
---------- ------------------------------ ---------- ---------- ----
1004 praveen 23243 4 m
1003 sam 1000 3
1005 naveensai 34453 5 m
1006 kani 34532 6 f
1007 soumya 99999 3 f
1008 surya 34234 4 f
1009 shanthini 54323 5 f
7 rows selected.
-------------------------------------------------------------------------------
Correlated subquery - it references the outer query plus it executes repeatedly also
EXISTS : checks if a particular value from a table exists in another table...!!
SQL> select ename, salary from emp e where EXISTS ( select 'X' from dept where e.deptno= dept_no);
ENAME SALARY
------------------------------ ----------
praveen 23243
sam 1000
naveensai 34453
kani 34532
soumya 99999
surya 34234
shanthini 54323
Finding the departments with less than 2 employees
SQL> select dept_no, dept_name, location from dept outer where 2 > (select count(*) from emp where emp.deptno=outer.dept_no);
DEPT_NO DEPT_NAME LOCATION
---------- --------------- ---------------
6 HSE GHT3
7 IS GHT3
7 rows selected.
-----------------------------------------------------------------------------
<<<<<<<<<<<<<<<<<<INLINE VIEWS AND SCALAR SUBQUERY EXPRESSIONS>>>>>>>>>>>>
Inline view allows to use a virtual table in from clause - useful in many ways some example are below
SQL> select deptno, d.location, d.manager_no from ( select deptno, count(*) from
emp where rownum=1 group by(deptno) order by count(*) desc), dept d where d.dep
t_no=deptno;
DEPTNO LOCATION MANAGER_NO
---------- --------------- ----------
4 GHT1 6
Scalar Sub query can be used in following clause
1. select city, state, zip, (select count(*) from student s where s.zip=z.zip ) as student_count from zipcode z where state = 'ct';
2.select student_id, last_name from student s where ( select count(*) from enrollment e where s.student_id = e.student_id) > ( select avg(count(*)) from enrollment
group by student_id) order by 1
3. Select student_id, lastname from student s where student_id between 230 and 335 order by ( select count(*) from enrollment e where s.student_id=e.student_id) desc
4. select course_no, cost , case when cost <= ( select avg(cost) from course ) then cost*1.5
when cost= ( select max(cost) from course ) then ( select cost from course where course_no=20)
else cost
end "Testcase"
from course
where course_no in (20,80)
order by 2
---------------------------------------------------------------------------
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<ANY SOME ALL >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> select ename, sex, deptno from emp where salary < any ( 100000,1200000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
praveen m 4
sam 3
naveensai m 5
kani f 6
soumya f 3
surya f 4
shanthini f 5
7 rows selected.
SQL>
SQL> select ename, sex, deptno from emp where salary < any ( 100000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
praveen m 4
sam 3
naveensai m 5
kani f 6
soumya f 3
surya f 4
shanthini f 5
7 rows selected.
SQL> select ename, sex, deptno from emp where salary < any ( 10000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
sam 3
<<<<<<<<<<<<<<<<<<<ALL>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> select ename, sex, deptno from emp where salary < all ( 10000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
sam 3
SQL> select ename, sex, deptno from emp where salary > all ( 10000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
praveen m 4
naveensai m 5
kani f 6
soumya f 3
surya f 4
shanthini f 5
6 rows selected.
SQL> select ename, sex, deptno from emp where salary > all ( 100000,12000);
no rows selected
SQL> select ename, sex, deptno from emp where salary > all ( 10000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
praveen m 4
naveensai m 5
kani f 6
soumya f 3
surya f 4
shanthini f 5
6 rows selected.
SQL> select ename, sex, deptno from emp where salary > all ( 20000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
praveen m 4
naveensai m 5
kani f 6
soumya f 3
surya f 4
shanthini f 5
6 rows selected.
SQL> select ename, sex, deptno from emp where salary > all ( 50000,12000);
ENAME SEX DEPTNO
------------------------------ ---- ----------
soumya f 3
shanthini f 5
----------------------------------------------------------------------------
