Krishna Logo
CLICK HERE TO SEE THE LOCATIONS
Divied
Ria / Kumar @ 1-(877) 864-8462

 

Loading
Latest News
Home Navigation Divied
SQL/UNIX Navigation Divied SQL
Showing 1 - 10 of 28 Previous | 1 | 2 | 3 | Next
SQL
Subcategories
 

Class Notes and Queries

 
 

SQL - Simple Interview SQL questions

 
 

SQL BY MAMATHA DAY 3 

 
 

SQL BY MAMATHA DAY 2 CONTINUED....

 
 

Class queries

 
 

SQL DOCUMENTATION FROM RAJESH

 
 

Class queries

 
 

SQL Part 4

 
 
9

CLICK HERE TO DOWNLOAD THE FILE

 
DRL - SELECT 
DDL - CREATE, ALTER, TRUNCATE, RENAME, RECYCLEBIN, FLASHBACK, PURGE, DROP
 
SELECT * FROM EMP;
 
CREATE TABLE STUD (ROLLNUM NUMBER(10), NAME VARCHAR2(20), MARKS NUMBER(3,2));
 
SELECT * FROM STUD;
 
ALTER TABLE STUD ADD CITY VARCHAR2(10);
 
CREATE TABLE EMP_DUMMY AS (SELECT * FROM EMP);
 
SELECT * FROM EMP_DUMMY;
---------------------------------------------------
 
Truncate:
--------
it deletes the data in the table permanantly 
 
sytax:
 
truncate table table_name
 
truncate table EMP_DUMMY;
 
Rename:
------
 
It helps to change the name of the table or object
 
syntax:
 
rename old_name to new_name
 
rename EMP_DUMMY to EMP_NEW;
 
Drop:
-----
it is used to delete the table or object from the database
 
syntax:
 
drop table table_name
 
drop table emp_new;
 
Recyclebin:
-----------
 
show recyclebin;
 
flashback:
----------
helps restoring items from Recyclebin
 
Purge:
-----
deletes the items from the recyclebin
 
purge recyclebin
 
-------------------------------------------------
desc stud;
 
DML - Data manipulation Language
--------------------------------
 
Insert:
------
 
it helps to enter data into the table
 
syntax:
insert into table_name values (col1_val, col2_val, cal3_val ....);
 
desc stud;
 
Insert into stud values (1, 'Raj', 1, 'Herndon');
 
Insert into stud values (2, 'Ravi', 3.42, 'Manasas');
 
Insert into stud values (3, 'Ram', 4.00, 'DC');
 
insert into stud values (4, 'reston', 5.44, 'Tim');
 
insert into stud(marks, city, rollnum, name) values (4.57, 'Mclean', 5, 'Rob');
 
insert into stud(marks, rollnum, name) values (7.77, 6, 'Sam');
 
insert into stud values (7, 'Sim', null, ' ');
 
select * from stud;
 
create table emp_new as (select * from emp where 1=2);
 
select * from emp_new;
 
insert into emp_new (select * from emp); 
 
Update:
-------
it is used to modify the data in the existing table
 
syntax:
 
update table_name set condition where condition
 
select * from stud;
 
update stud set city = 'HERNDON';
 
update stud set name = 'Tim' where rollnum = 4;
 
Delete:
------
it is used to delete entire row from the table
 
syntax:
------
 
delete from table_name where condition
 
delete from stud where rollnum = 7;
 
select * from stud;
 
delete from stud where name = 'Rob';
 
Difference Between Truncate and Delete:
---------------------------------------
 
Truncate:
1. Data deleted from truncate is permanant
2. we cannot delete specific records
3. Truncated data cannot be retrieved
4. it is permanant transaction which directly effect on the database table
 
Delete:
1. it deletes the entire data this is not permanant deletion
2. We can delete specific rows
3. deleted data can be retrieved(restored)
4. it is temporary transaction which directly effect the database table
 
------------------------------
 
Alias -- empno - emp_num, ename - emp_Name, sal - salary
-----
 
select e.empno*56+89-9+57.5 as "sinsns sindis",
  e.ename as emp_name,
  e.sal as salary
from emp e;
 
select e.empno, e1.empno
from emp e, emp1 e1
 
select emp as e
 
DCL - Data control Language:
----------------------------
grant 
- it will give access to the objects or tables to a user
 
grant on to
 
and 
 
revoke 
- it will remove access to the objects or tables to a user
revoke on from
 
TCL - Transaction Control Language
----------------------------------
 
Commit - it will physically save data into the database which cant be deleted using rollback
Rollback -  it will be used to go back to the first created point - something like 'UNDO'
savepoint - It will be helpful to create multiple savepoints and can always roll back to a specific
            savepoint. - something like system restore points
 
COMMIT - 
-------
 
create table t1 (marks number(4));
 
select * from t1;
 
insert into t1 values(100);
 
insert into t1 values(200);
 
commit;
 
rollback;
 
savepoint s1;
 
insert into t1 values(300);
 
savepoint s2;
 
insert into t1 values(400);
 
savepoint s3;
 
insert into t1 values(500);
 
savepoint s4;
 
select * from t1;
 
rollback to s2;
 
rollback to s1;
 
drop table t1;
 
--------------------------------------------
 
Operators:
---------
 
Numerical Operators
Relational Operators
Logical Operators
 
Numerical Operators:
-------------------
 
 +, -, *, /
 
DUAL: 
----
It a dummy table from oracle, where you cant delete or add anything. This table has 1 row and 1 column
 
select 9 * 10 
from dual
 
select * from emp;
 
select empno as EMP_NUM, 
  ename as ENAME,
  sal + (sal/0.15) as Total_SAL
from emp;
 
Relational Operators:
--------------------
 
 >, <, >=, <=, !=, <>, any
 
 select * from emp;
 
 select * from emp where sal <> 2000;
 
 select * from emp where deptno = any(10,20);
 
 select * from emp where deptno = (10,20); wrong
 
 Logical Operators:
 -----------------
 
 AND, OR, NOT, IN, BETWEEN, LIKE(%)
 
 select * from emp where deptno = 10 or deptno = 20;
 
 select * from emp where job = 'CLERK' and sal < 1000;
 
 select * from emp where deptno in (10,20);
 
 select * from emp where deptno not in (10,20);
 
 select * from emp where job not in ('MANAGER','CLERK');
 
 select * from emp where job <> 'CLERK' and job <> 'MANAGER';
 
 select * from emp where sal between 1000 and 2000;
 
 select * from emp where sal >= 1000 and sal <= 2000;
 
LIKE(%)
 
% - percentile
_ - 1 character
 
select * from emp;
 
select * from emp where ename like ('_U%R');
 
--------------------------------------------------------
 
Functions:
----------
I. Single Row Functions
   A. Character Functions
   B. Nuemeric Functions
   C. Date Functions
   D. Conversion Functions
II. Group Functions
 
A. Character FUnctions
-----------------------
 
1. Length
 
- it gives number of characters in a string
- Gives the size of the string
 
syntax: length('string') or length(column_name)
 
select length('HELLO WORLD') from dual;
 
select length(ename) from emp;
 
select * from emp where length(ename) <= 4;
 
select * from emp where length(ename) > 4;
 
2. UPPER
 
- displays all the letter in the string or column in upper case(Capital Letters)
 
syntax: UPPER('string') or UPPER(column_name)
 
select upper('hello world') from dual;
 
select upper(ename) from emp;
 
select * from emp where ename = upper('smith'); 
 
3. LOWER
 
- displays all the letters in the string or column to be in lower case 
 
syntax: LOWER('string') or LOWER(column_name)
 
select lower('HELLO WORLD') from dual;
 
select ename, lower(ename), job from emp;
 
4. INITCAP
 
- Displays the first letter in the string or column to be in Upper case
 
select initcap('HELLO WORLD') from dual;
 
5. SUBSTR
 
- This is called sub string. 
- is is used to display parts of a string. 
- it is used to retrieve number of characters from a string.
 
syntax: substr('string',m,n)
 
m = starting point
n = number of charcters
 
select * from emp;
 
select ename,substr(ename,3) from emp;
 
select ename,substr(ename,2,3) from emp;
 
select ename,substr(ename,-3,2) from emp;
 
6. INSTR 
 
- This is used for positioning the cursor.
- Moves the cursor to a specific point in the string searching for a character
 
syntax: instr('string','chr',m,n)
 
chr = search charcter
m = starting postion (1 OR -1)
n = number of occarance
 
select INSTR('ABC$ABC','$',1,1) from DUAL;
 
select SUBSTR('ABC$ABC',(INSTR('ABC$ABC','$',1,1)+1)) from DUAL;
 
7. LTRIM
 
- Trims unwanted spaces or charcters from left side of the String.
 
***WELCOME
 
select '***WELCOME' from dual;
 
select Ltrim(ltrim('***Wel*come**', '*'),'*') from dual;
 
eg: 
 
1. welcome
 
2.     welcome
 
select length('welcome') as A, Length('welcome      ') as B,
       length(ltrim(rtrim('welcome      ')))
from dual;
 
 
 
8. RTRIM
 
- Trims unwanted spaces or characters from right side of the string.
 
select RTRIM('***WELCOME****', '*') from dual;
 
select length('welcome'), length('  welcome      '), length(rtrim('  welcome      '))
from dual;
 
select Ltrim(RTRIM('***WELCOME****', '*'),'*') from dual;
 
9. TRIM
 
- removes spaces from right side and left side 
 
select trim('   WELCOME      ') from dual;
 
select trim('**WELCOME$$$') from dual;
 
10. LPAD
 
- Adds charaters to left side
 
11.RPAD
 
- Adds charaters to right side
 
1500.00
 
select lpad('1500.00',10,'$') from dual;
 
Raj_OLD
Ravi_OLD
Ram_OLD
---
Sam_NEW
Sim_NEW
Kom_NEW
 
with name as (
Select ('Raj') A from dual
union
select ('Ravi') from dual
union
select ('Ram') from dual)
select rpad(A,length(A)+4,'_OLD') from name;
 
12. Least
 
- displays the least values in the array selected
 
select least(1,2,4,5,6,7,8, 0.1, -10) from dual;
 
select least(empno,mgr,sal,deptno) from emp;
 
select e.*,least(empno,mgr,sal,deptno) from emp e;
 
13. greatest
 
- displays the greatest value in the array selected
 
select e.*,greatest(empno,mgr,sal,deptno) from emp e;
 
14. NVL
 
- Replaces the null values in a column to a known(or specified) value
 
syntax: NVL(COl_Name,0) => If Col_Name = NULL then 0 else it will display Col_Name value
 
select * from emp;
 
Net_Sal = Sal + Comm
 
display Empno, ename, job, deptno, sal, comm, Net_Sal from emp table for all the employees
 
select empno, ename, job, deptno, sal, comm, sal + NVL(comm,0) as Net_Sal from emp;
 
15. NVL2
 
syntax: 
NVL2(Col,m,n) => If Col is NOT NULL then 'm', If COl is NULL then 'n'
 
select comm, nvl2(comm, 1, 2) from emp;
 
display the employees who got commission
 
select *
from emp e
where nvl2(comm,1,2) = 1;
 
16. Concat
 
It helps to merge multiple strings
 
select concat(ename,job) from emp;
 
select ename||JOB||MGR from emp;
 
17. CASE
 
Syntax:
 
Case when then
when then
Else
END
 
select * from emp;
 
If Sal < 1000 then display 'Low_Sal'
If sal > 1000 and Sal < 2000 then display 'Mid_Sal'
For all other cases display 'Hi_Sal'
 
case sal < 1000 then 'Low_sal', when sal between 1000 and 2000 then 'Mid_sal' else 'Hi_sal' end;
 
select Empno, Ename, Sal,
  ( Case when sal < 1000 then 'Low_sal'
         when sal between 1000 and 2000 then 'Mid_sal'
 else 'Hi_sal' 
    End
  ) 
from emp;
 
select * from emp;
 
select Empno, Ename, Sal, JOB,
  ( Case     when job = 'ANALYST' then 'a'
             when job = 'CLERK' then 'C'
     when job = 'MANAGER' then 'm'
     when job = 'PRESIDENT' then 'P'
     when job = 'SALESMAN' and sal > 1000 then 'S'
    End
  ) as Short_Job
from emp;
 
Display details of all the employees, add another column in the output which should include
the Name of the DEPARTMENT that employee belongs too. Columns is DEPARTMENT
 
Conditions:
If the DEPTNO = 10 then he/she is from a IT DEPARTMENT
If the DEPTNO = 20 then he/she is from a SALES DEPARTMENT
If the DEPTNO = 30 then he/she is from a MARKETING DEPARTMENT
If the DEPTNO = 40 then he/she is from a HR DEPARTMENT
For all the other DEPTNOs display it as 'OTHER'
 
SELECT A.*, (CASE DEPTNO WHEN 10 THEN 'IT DEPARTMENT'
      WHEN 20 THEN 'SALES DEPARTMENT'
  WHEN 30 THEN 'MARKETING DEPARTMENT'
  WHEN 40 THEN 'HR DEPARTMENT'
  ELSE 'OTHER'
END) AS DEPARTMENT
FROM EMP A;
 
18. DECODE
 
- It also works same as if else condition
 
syntax: 
 
decode(col_name, conditon_1, result_1, condition_2, result_2, condition_3, result_3, result_4)
 
If job = Salesman then display Sales
   job = President then display Prsdnt
   job = Clerk then display Clk
   for all other jobs N/A
   
select empno, ename, job, 
  decode(job, 'SALESMAN','Sales', 'PRESIDENT', 'Prsdnt', 'CLERK', 'Clk', 'N/A') as job
from emp;
 
select * 
from emp
order by sal desc;
 
19. Rank ()
 
Marks Rank   Dense_rank   
------------------------------
100     1   1
80 2   2
80 2   2
75 4   3
75 4   3
75 4   3
65 7   4
 
syntax:
 
rank() over (order by hiredate)
 
select ename, hiredate, rank() over (order by hiredate) from emp;
 
select ename, sal, rank() over (order by sal), dense_rank() over (order by sal)  from emp;
 
 
20. dense_rank()
 
select ename, sal, rank() over (order by sal desc), dense_rank() over (order by sal desc) from emp;
 
select * from emp; 
 
select ename, comm, rank() over (order by comm desc nulls last), 
              dense_rank() over (order by comm desc nulls last) 
from emp;
 
21. ROWNUM
 
select * from emp;
 
select e.*, rownum 
from emp e
order by sal;
 
select Empno, sal,
       rownum, row_number() over (order by sal desc nulls last) 
from emp;
 
22. ROW_NUMBER()
 
select e.*,  row_number() over (order by sal)
from emp e;
 
23. REPLACE 
 
- it used to replace existing string by New string 
 
syntax: replace('STRING', 'old_string', 'new_string')
 
select ('This is Bombay') from dual;
 
select replace('This is Bombay', 'Bombay', 'Mumbai') from dual;
 
24. TRANSLATE
 
- it is used to replace existing character by new character
 
select ('walcoma') from dual;
 
select translate('walcoma','a','e') from dual;
 
select translate('This was Krishna Training','w','i') from dual;
 
------------------------------------------------------------------
 
Neumeric Functions:
-------------------
 
25. abs
 
select abs(-100), abs(100) from dual;
 
26. sqrt
 
select sqrt(16), sqrt(100), sqrt(2) from dual;
 
27. Power
 
select power(3,2), power(4,3), power(2,4) from dual;
 
28. sign
 
select sign(-100), sign(789) from dual;
 
29. floor
 
select floor(129.99) from dual;
 
30. ceil
 
select ceil(129.34) from dual;
 
31. trunc
 
select trunc(129.34567,2) from dual;
 
32. round 
 
select round(129.34567,2) from dual;
 
33. mod
 
select mod(8,2) from dual;
 
select mod(9,2) from dual;
 
34. remainder
 
select remainder(8,2) from dual;
 
select remainder(9,2) from dual;
 
select mod(9,3) from dual;
 
select remainder(11,3) from dual;
 
Date Functions:
---------------
 
dd-mon-yy
 
08-feb-13
 
35. ADD_MONTHS
 
syntax: add_months(date, n) 
n -> no of months to add to the date
 
select sysdate, add_months(sysdate,4) from dual;
 
select empno, ename, hiredate, add_months(hiredate,2) from emp;
 
select add_months('08-feb-2013') from dual;
 
select sysdate, add_months(sysdate+10,3) from dual;
 
36. MONTHS_BETWEEN
 
it used to display the number of months between 2 dates
 
select empno, ename, hiredate, trunc(months_between(sysdate, hiredate)) from emp;
 
select empno, ename, hiredate, months_between(sysdate, '04-SEP-2010') from emp;
 
select sysdate from dual;
 
37. next_day
 
select sysdate, next_day(sysdate,'MONDAY') from dual;
 
38. last_day
 
select sysdate, last_day('01-feb-2012') from dual;
 
select hiredate, last_day(hiredate) from emp;
 
--------------------------------------------------------------
 
Conversion Functions:
--------------------
 
1. TO_CHAR - This helps to convert the data into Varchar2 format (Text Format)
2. TO_NUMBER - This help to convert the data into NUmber format 
3. TO_DATE - This converts the text into a date
 
1. TO_CHAR
 
select sysdate, to_char(sysdate, 'DY'), to_char(sysdate, 'DAY') from dual;
 
D - Day of the week
DD - day of the month
DDD - day of the year
DY - First 3 characters from the Day of the week (Sun-Mon)
Day - full name of the day
 
select hiredate, to_char(hiredate, 'DDth') from emp;
 
select hiredate, to_char(hiredate, 'DDsp') from emp;
 
select hiredate, to_char(hiredate, 'DDspth') from emp;
 
y - 2
yy - 12
yyy - 012
yyyy - 2012
 
w - week of the month (1-5)
ww - week of the year (1-52)
mm - month of the year (1-12)
mon - short month (Jan - Dec)
month - full month (January - DEcember)
 
select to_char(sysdate,'year') from dual;
 
select sysdate, to_char(sysdate,'HH') from dual;
 
select sysdate, to_char(sysdate,'MI') from dual;
 
select sysdate, to_char(sysdate,'SS') from dual;
 
select sysdate, current_timestamp from dual;
 
select empno, ename, to_char(hiredate, 'YYYY') from emp;
 
2. TO_NUMBER
 
it converts the text fields to number datatype
 
select to_number('10'), '10' from dual;
 
3. TO_DATE
 
It converts the text fields in to DATE datatype
 
to_date('date','date format')
 
select '02-feb-2012',to_date('02-feb-2012'), to_date('feb-02-2012','mon-dd-yyyy'),
       to_date('2012-feb-02','yyyy-mon-dd'),
  to_char(to_date('2012-feb-02','yyyy-mon-dd'),'yyyy/dd/mm')
 from dual;
 
select '02-feb-2012',to_date('02-feb-2012'), 'feb-02-2012', to_date('feb-02-2012','mon-dd-yyyy'),
  to_char(to_date('feb-02-2012','mon-dd-yyyy'),'mon-dd-yyyy'),
  to_char(to_date('feb-02-2012','mon-dd-yyyy'),'ddth-mon-yyyy')
from dual;
 
oracle default format: dd-mon-yyyy
                       mon-dd-yyyy
 
-----------------------------------------------------------
 
GROUP FUNCTIONS
---------------
 
max(), min(), sum(), avg(), count()
 
select max(sal), min(sal), sum(sal), avg(sal), count(*) from emp;
 
select deptno,ename, max(sal) 
from emp
group by deptno, ename;
 
select * from emp;
 
display the sum of salary of each job type
 
select job, sum(sal) 
from emp 
group by job;
 
-------------------------------------
 
Clauses:
--------
 
Select
Distinct
from
where
group by
order by
having 
 
Distinct:
 
- deletes the duplicate records and displays the unique records
 
select distinct deptno
from emp;
 
select distinct to_char(hiredate, 'yyyy')
from emp;
 
Having:
 
- It is used to filter the grouped data 
 
select deptno, max(sal), min(sal), count(empno)
from emp
where deptno between 10 and 20
group by deptno
having count(empno) >= 5;
 
select deptno, count(*)
from emp
group by deptno
having count(*) > 1;
 
ORDER BY
 
select * 
from emp
order by deptno, ename, sal;
 
select empno, ename, job, deptno
from emp
order by 4, 2;
 
Clauses Usage Order:
---------------------
 
select empno, max(sal)
from emp
where ename = 'SMITH'
group by empno;
 
select - from - where - group by - having - order by
 
or
 
select - from - where - having - group by - order by
 
-----------------------------------------------
 
Database Constraints:
--------------------
 
Primary Key
- It doesnt accept any duplicates and Null Values
- One table has only one primary key
 
Unique
- It doesnt accept any duplicates
- It can accept any number of NULL Values
- Any number of unique columns can be created in a table
 
Check
- It specifically searches for a specified values
 
Foreign Key
- It used to maintain the relationship between database tables
 
NOT NULL
- This database constraint will not allow NULLS values in the column
 
select * from emp;
 
drop table t3;
 
create table t3 (sno number(10) primary key, name varchar2(10), 
                 gender varchar2(1), check (gender in ('m','f')));
 
insert into t3 values (1, 'raj', 'm');
 
insert into t3 values (2, 'Ravi','f');
 
insert into t3 values (3, 'Ram', 'M');
 
insert into t3 values (NULL, 'Ram');
 
select * from t3;
 
desc t3;
 
select * from emp;
 
select * from dept;
 
create table t4 (sno number(10) primary key, 
                 name varchar2(10) not null, 
                 gender varchar2(1), check (gender in ('m','f')), 
deptno number(5) constraint dept_fk references dept(deptno));
 
desc t4;
 
------------------------------------------------------------------------------
 
Data Merging:
------------
 
Vertical Merging: => Joins
 
Horizontal Merging: => Set Operators
 
 
SET OPERATORS:
--------------
 
UNION, UNION ALL, INTERSECT, MINUS
 
Rule 1:
-------
 
Number of columns from table 1 and table 2 should be same 
 
Rule 2:
------
 
The merging columns should have same datatype
 
UNION
-----
 
select * from emp;
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
 
select * from dept;
(DEPTNO, DNAME, LOC)
 
select empno, ename, job from emp
union
select deptno, dname, LOC from dept;
 
select * 
from emp
where deptno in (30) and comm is not null
union
select * 
from emp
where deptno = 20;
 
query_1 => 5 = 3
union
query_2 => 7 = 3
 
select * from emp
union
select * from emp
 
select * from emp
union all
select * from emp
 
select * from emp where deptno in (10,20)
intersect
select * from emp where deptno in (20,30);
 
select * from emp
minus
select * from emp;
 
create table EMP_TRGT as (select * from emp where deptno in (20,30));
 
select * from emp_trgt;
 
select * 
from emp
where deptno in (20,10)
minus
select * from emp_trgt;
 
select * from emp_trgt
minus
select * 
from emp
where deptno in (20,10);
 
---------------------------------
 
Joins:
 
Cross Join: Displays Cartesian Product of the rows from both the tables
  No conditions are applied on this join
 
select * 
from emp e, dept d;
 
Inner Join: Displays the matching records from both the tables
-----------
 
select *
from emp e, dept d
where e.deptno = d.deptno;
 
select d.*
from emp e inner join dept d on e.deptno = d.deptno;
 
select d.*
from dept d inner join emp e on e.deptno = d.deptno;
 
Equi Join:
---------
 
select * 
from emp e, dept d
where e.deptno = d.deptno and ;
 
Non-Equi Join:
--------------
 
select * from emp;
 
select * from salgrade;
 
select * 
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
 
select * 
from emp e, salgrade s, dept d
where e.sal between s.losal and s.hisal 
      and e.deptno = d.deptno;
 
Left Join or Left Outer Join: Displays all the matching records from both the tables and also 
       displays all the unmatched records from left table.
 All the unmatched records have the NULL values
 
select *
from emp e left outer join dept d on e.deptno = d.deptno;
 
select *
from dept d left outer join emp e on e.deptno = d.deptno;
 
select * 
from emp e, dept d
where e.deptno = d.deptno (+);
 
Right Join Or Right Outer Join: Displays all the matching records from both the tables and also 
       displays all the unmatched records from right table.
 All the unmatched records have the NULL values
 
select *
from emp e right outer join dept d on e.deptno = d.deptno;
 
select * 
from emp e, dept d
where e.deptno (+)= d.deptno;
 
Full Join Or Full Outer Join: Displays all the matching records from both the tables and also 
       displays all the unmatched records from both the tables.
 All the unmatched records have the NULL values
 
select *
from emp e Full Outer join dept d on e.deptno = d.deptno;
 
Self Join: Joining the same table
 
select * 
from emp e, emp e1
where e.empno = e1.empno;
 
select count(*)
from emp
where to_char(hiredate,'yyyy') in (1980,1981);
 
select e1.
from emp e, emp e1, emp e2
where e.empno = e1.empno and e.empno = e2.empno
      and (to_char(e.hiredate,'yyyy') = 1980
 or to_char(e1.hiredate,'yyyy') = 1981
 or to_char(e2.hiredate,'yyyy') = 1982)
 
emp, dept 
 
emp 
(empno, ename, job, sal, deptno)
 
dept
(deptno, dname, loc)
 
LOcation of an employee
 
Equi Join & Non Equi Join
 
Rules: 1.atleast you should have 2 tables
  2. you should have a common column or a matching column 
  
select * 
from emp e, dept d
where e.deptno = d.deptno;
 
emp 
(empno, ename, job, sal, deptno)
 
dept
(deptno, dname, loc)
 
salgrade
(grade, losal, hisal)
 
select * from salgrade;
 
select * from emp;
 
select * 
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
 
select * 
from emp e, salgrade s, dept d
where e.sal between s.losal and s.hisal
      and e.deptno = d.deptno;
 
---------------------
 
Inner Join:
----------
 
create table EMP_New1 as (select * from emp);
 
SELECT * FROM EMP_NEW1;
 
SELECT * FROM DEPT;
 
INsert into emp_new1 values (3123, 'RAJ', 'WAITER',NULL,NULL, 500, 1500, 60);
 
INsert into emp_new1 values (4126, 'RAVI', 'DRIVER',7839,NULL, 1000, 100, 70);
 
SELECT *
FROM EMP_NEW1 E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
 
SELECT * 
FROM EMP_NEW1 E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
 
LEFT OUTER JOIN:
 
SELECT * 
FROM EMP_NEW1 E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
 
SELECT *
FROM EMP_NEW1 E, DEPT D
WHERE E.DEPTNO = D.DEPTNO (+);
 
RIGHT OUTER JOIN:
 
SELECT * 
FROM EMP_NEW1 E RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
 
SELECT *
FROM EMP_NEW1 E, DEPT D
WHERE E.DEPTNO (+) = D.DEPTNO;
 
FULL OUTER JOIN:
 
SELECT * 
FROM EMP_NEW1 E FULL OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
 
SELF JOIN:
 
SELECT * 
FROM EMP E, EMP E1, DEPT D
WHERE E.EMPNO = E1.EMPNO AND E1.DEPTNO = D.DEPTNO;
 
SELECT * 
FROM EMP E INNER JOIN EMP E1 ON E.EMPNO = E1.EMPNO
           INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
  INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
  
DESC EMP;
 
SELECT * 
FROM DEPT D LEFT OUTER JOIN EMP_NEW1 E ON E.DEPTNO = D.DEPTNO;
 
SELECT * 
FROM DEPT D, EMP_NEW1 E
WHERE D.DEPTNO = E.DEPTNO (+);
 
SUB QUERYS:
----------
 
Writing a query inside a query is Subquery
 
SELECT - Scalar Subquery 
FROM   - Inline View Subquery
WHERE  - Subquery
 
 
Scalar Subquery:
---------------
 
The output of the subquery should result in only one cell
 
select Max(sal)
from emp
group by deptno;
 
select e.*, (select min(sal)
             from emp) as max_sal
from emp e;
 
select e.empno, 
       e.ename, 
  e.job, 
  e.comm, 
  (select max(hiredate) 
        from emp
        where deptno = 10
        group by deptno) as Max_Date
from emp e;
 
select e.empno, 
       e.ename, 
  e.job, 
  e.comm, 
  (select dname from dept where deptno = 10) as Max_Date
from emp e;
 
Writing a subquery in Where:
----------------------------
 
select *
from emp e
where e.sal not IN (select max(sal) 
                 from emp
group by deptno); 
 
select max(sal) 
from emp
group by deptno;
 
 ;
 
BATCH 122- IN CLASS SQL STATEMENTS-FOR PRACTICE
Date Posted: 01/09/2014

CLICK HERE TO DOWNLOAD THE FILE   DRL - SELECT  DDL - CREATE, ALTER, TRUNCATE, RENAME, RECYCLEBIN, FLASHBACK, PURGE, DROP   SELECT * FROM EMP;   CREATE TABLE STUD (ROLLNUM NUMBER(10), NAME VARCHAR2(2...  

 
 

CLICK HERE TO TAKE YOU TO THE WEBSITE

 
Showing 1 - 10 of 28 Previous | 1 | 2 | 3 | Next
Shadow Bottom
   
© 2005 - 2015 Krishna Training.
Designed & Developed By BitraNet