SQL

Index

Sr.No Topic Page No

1 Introduction to SQL
2 Restricting and sorting data
3 Single row functions
4 Multiple row functions
5 SQL Joins
6 Sub-Queries
7 Manipulating data .
8 Creating and managing tables.
9 Set operators
10 Psuedocolumns
11 SQL Constraints.
12 SQL*Plus
13 Sequences.
14 Views
15 Indexes
16 Views.
17 Synonyms
18 Oracle datetime functions
19 Advanced subqueries.
20 Rollup and Cube operators.

1.Introduction to SQL
 Structured Query Language
 Standard Query Language

1
 Common for all RDBMS
 Pronounced as ‘sequel’

TABLE/RELATION:-

 It is a basic unit of storage for a RDBMS.

 It is 2-dimensional.
 Column/attribute.(Empno,sal,Ename,city)
 Row/Record/Tuple.
 A Field is a intersection of a row and a column.(cell)

Eg: EMP table

Empno Ename Sal City

1 Arun 6767 Mumbai
2 Jack 5005 Chennai
3 Vijay 8989 Delhi
4 Atul 6768 Mumbai
5 Raj 8900 Calcutta

Components of IBM SQL:-

DDL –Create, Drop, Alter

DML – Insert, update, Delete.
DCL—Grant ,Revoke.
DQL--Select

Commit,Rollback,Savepoint— Transaction control statements.

Components of ORACLE --SQL:

 DDL - Data Definition Language (Create, Alter, Drop, Rename,Truncate).

Rename and Truncate available fr+om Ov7

 DML – Data Manipulation Language (Insert, Update, Delete, Merge(upsert)).

Merge available from Ov9

 DCL - Data Control Language (Grant and Revoke).

 DQL – Data Query language (select).

Commit, Rollback, Savepoint—Transaction control statements.

Note: All DDL and DCL Commands are Autocommit.

Rules for Table Naming Conventions:

 Max of 30 characters.
 A-Z, a – z, 0 – 9.
 Case insensitive. EMP or emp
 Has to begin with alphabet
 Only 3 Special characters: $, #, _ Allowed in oracle
(Avoid their use).

2
  SQL reserve words not used.

Basic Data types of SQL: Oracle 9 / 9i

1. VARCHAR2 (size)
Variable-length character data
Minimum size is 1; maximum size is 4000.
Specifying the size is compulsory.

2. CHAR (size)
Fixed-length character data
(Default and minimum size is 1; maximum size is 2000)

3. NUMBER (p,s)
Number having precision p and scale s (The precision is the total
Number of digits, and the scale is the number of digits to the right of the decimal point.

NUMBER(5):-99999

NUMBER (5,3)
e.g. 73.312

Precision is 5 and scale is 3.

Number(2,2)—There can be 2 digits after the decimal ,and the precision is 2 so there
can be no digit before the decimal point. (0.99)OR(0.9) 0.9 33

(If precision is less than scale, then oracle creates the table,but it is of no use as we
cannot store any value).

4. DATE

Range:-Between January 1, 4712 B.C., and December 31, 9999 A.D.

Date Stores time also.

5. LONG
Variable-length character data up to 2 gigabytes.
Can have only one long column per table.
(Advanced data types later)

NVARCHAR OR NVARCHAR2  MULTI BYTE CHARATERS

Questions:-
1. What is difference between Varchar2 and char?
2. What is the difference between varchar and varchar2?

DDL Commands:

Create:-

3
 1. CREATE TABLE EMP /*can type in lowercase*/
(Empno number (4),
Ename varchar2 (20),
Sal number (7,2),
City varchar2 (15),
Comm number (7, 2)
)
Alter Command:-

Drop Command: - Drop table emp;

DML Commands:

2. INSERT INTO EMP

VALUES (1,’Arun’,6767,’Mumbai’,200);

/*read from left to right */

/* char & varchar2 use single quote, not required for numbers*/

/*if not sure of sequence of column*/

3. INSERT INTO EMP (Empno, Ename, City,SAL)

VALUES (2,’Jack’,’Chennai’,8000);

4. INSERT INTO EMP (Empno, Ename, City)

VALUES (‘5’,’Amit’,’Chennai’);

5. INSERT INTO EMP

VALUES (‘6’, ‘vijay’,’8888’,5400,75);

6. SELECT * FROM EMP;

/* shows all data from the rows and columns of the table*/
* ---------is known as the metacharacter

7. SELECT Ename, City, Sal

FROM EMP;
/*output will be shown in the order selected in the query*/

To modify existing data :

UPDATE command: it is an DML command.

1) UPDATE EMP
SET Sal = 10000

4
 WHERE ENAME=’Amit’;

2) UPDATE EMP
SET Sal = 10000;
/*change sal=10000 at all records*/

3. UPDATE EMP
SET Sal = 27000,COMM = 1500
Where ENAME = ‘vijay’

DELETE command: it is an DML command.

1) delete emp
Where city = ‘Mumbai’;

/* Delete all the rows having city=’Mumbai’*/

2) Delete emp; // deletes all rows but table remains there for future additions

3) Drop table emp; // to drop (DDL stmnt) the table itself

COMMIT will save changes made by DML command till the last commit
stmnt.Whenever u commits its end of Transaction.

Rollback will undo the changes ,made the DML statement till the last
commited stmnt.

Transaction:-Statements between two successive commit or two successive

rollback,or a rollback and commit will form a transaction.

Eg:-
1.Insert into…….
2.Insert into…..
Commit.
3.Insert into ….
4.Insert into …
Rollback;
Insert into ….
Insert into …
Commit;

All DDL Commands are AUTOCOMMIT

Only DML stmnts are affected by COMMIT and ROLLBACK stmnts.

2.Restricting and Sorting data.

Where Clause:-Used to restrict the data.It works like a filter.

5
 SQL> Select * from EMP
Where sal > 5000;

The given query works as follows:-

1. Emp table from the server hard disk comes to server Ram.

2. Filtering occurs on the Server ram.

3. Only those rows that satisfy the condition are sent from server to the client m/c.

4. The data comes in the form of a Cursor AND ITS DISPLAYED.

WHERE clause acts as a filter.

Logical Operator:

Operator Meaning
AND Returns TRUE if both
conditions are true
OR Returns TRUE if either
condition is true
NOT Returns TRUE if the following
Condition is false

AND Operator:-
Select * from EMP where city=’Mumbai’ and sal >3000;

OR Operator:-

Select * from EMP where city=’Mumbai’ or sal >3000;

NOT Operator:-

Select * from EMP where SAL is null;

Select * from EMP where comm is not null;

Relational Operator:
>, < , >= , <= , = , != or <>

9. SQL> Select * from EMP where sal > 5000 and sal<8000;

Select * from EMP where sal !=25000

10. SQL> Select * from emp

where fname=’mandar’;

SQL is not case-sensitive, but data is case sensitive.

6
Select Ename “EMPLOYEE NAME”, Sal, Sal*0.4 HRA, Sal*0.15 DA from Emp;

Here “sal * 0.1 “is a derived or computed field.

Column Alias :

13. SQL> Select Ename, Sal, Sal*0.1 ” H R A”

from Emp;

The derived field will be named as “ H R A “ known as an ‘alias’.

Double quotes is optional. If blank space or dot in the alias then double quotes is
compulsory. But it is preferred to use double quotes.

Eg: SQL> Select Ename “EMP NAME”, Sal “SALARY”, Sal * 0.1 “H R A” From Emp;

Arithmetic Operator :

() brackets, / division, * multiplication, + addition , - subtraction.

15. SQL> Select Ename, Sal, Sal*0.1 “H R A”, Sal*0.15 “D.A”, Sal + Sal * 0.1 + Sal *
0.15 “TOTAL”
From EMP;

16. SQL> CL SCR;

/*clears the screen*/

Duplicate Rows:-

Insert into emp values (3,'raj', 24000,'Mumbai', 1000);

Insert into emp values (3,'raj', 24000,'Mumbai', 1000);

Eliminating Duplicate Rows:-

18. SQL> select distinct ename

From EMP;

19. SQL> select distinct city, ename

From emp;

20. SQL> select unique city

From emp;

Distinct/unique clause :--

1.It sorts the data in the table .

2. Then selects the distinct values.

So distinct clause makes the query work slower.

Proof:---Look at the o/p of the query, the op is in sorted order.

7
WILD CARDS:

(% Indicates 0 to any number of characters)

“– “(Underscore) indicates 1 character.

28. SQL> select *

From EMP
Where ename like ‘A%’;

/*Shows all names begin with A */

29. SQL> select *

From EMP
Where ename like ‘%G’;
/*Shows all names ending with G */

‘_ _ A%’ indicates having capital A as a 3rd character

‘_ _ _ _’ all enames with 4 characters

30. SQL> select * From EMP

Where ename not like ‘A%’;

/*shows all names that doesn’t begin with A */

SQL> select *
From EMP
Where ename like ‘A__’;

BETWEEN: data in ranges.

1. SQL> Select *
From EMP
Where sal between 2000 and 3000;

/*it includes both 2000 AND 3000 values also*/

2. SQL> Select *
From EMP
Where sal not between 2000 and 3000;

/*it includes both 2000 OR 3000 values also*/

i.e. will not show emp having salary=2000 or 3000
Note:-
BETWEEN … AND … is actually translated by Oracle server to a pair of
AND conditions: (a >=lower limit) AND (a <= upper limit).

IN Operator: searching for specific values.

3. SQL> Select *
From EMP
Where empno in (2, 3);

8
/*empno=2 or empno=3*/

4. SQL> Select *
From EMP
Where empno not in (2, 3);

/*empno=2 and empno=3*/

SQL>Select * from EMP where city in (‘Chennai’,’mumbai’);

Note:-
Explain that IN ( ...) is actually translated by Oracle server to a set of OR conditions: a
=value1 OR a = value2 OR a = value3.

Logical operator(contnd)

……………….. WHERE sal NOT BETWEEN 10000 AND 15000

…………….……...and ename NOT LIKE ’%A%’

……………….. WHERE commission_pct IS NOT NULL

Drop table emp;

Create table Emp

(Empno Number (4),
Fname varchar2 (20),
Lname varchar2 (20),
Job varchar2 (20),
Sal number (7,2),
Deptno number (4),
Hiredate date);

Insert into Emp values (1,'raj','mehta','Programmer','25000','10',’1-jan-2003’);

Insert into Emp values (2,'Mandar','vaidya','Analyst','35000','10',’21-jun-2003’);
Insert into Emp values (3,'Mehul','sharma','Clerk','5000','20',’4-may-2004’);
Insert into Emp values (4,'Suresh','Kulkarni','Manager','50000','10',’2-sep-2004’);
Insert into Emp values (5,'Naresh','Mishra','Officer','10000','30',’12-dec-2003’);
Insert into Emp values (6,'vijay','Mishra','Analyst','16000','20',’20-jul-2003’);

Sorting:
Order by clause

21. SQL> select *

From EMP
Order by SAL ;
/*by default it is ascending*/

9
SQL> select *
From EMP
Order by sal asc

Keywords:

ASC, DESC
Ov7 can combine upto 16 columns in order by clause
Ov8 onwards there is no limit.

23. SQL> select fname, SAL, sal*0.1

From EMP
Order by sal*0.1;

24. SQL> select fname , sal*0.1”H R A”

From EMP
Order by “H R A”;

25. SQL> select fname, sal*0.1”H R A”

From EMP
Order by 2;

26. SQL> select *

From EMP
Order by 2;

Select * from emp order by deptno,sal;

Select * from emp order by deptno asc,sal desc;

3.SINGLE ROW FUNCTIONS

This lesson covers the following single-row functions:

• Character functions: Accept character input and can return both character or number
values.

• Number functions:-Accept numeric input and returns numeric values.

10
• Date functions:- Operate on values of the DATE data type (All date functions return a
value of DATE data type except the MONTHS_BETWEEN function, which returns a
number.)

• Conversion functions:-Convert a value from one data type to another data type.

• General functions:-
– NVL
– NVL2
– COALSECE
– CASE
– DECODE

1. Character functions.

a) Case manipulation functions.

Lower
Upper
Initcap

UPPER:

5. SQL> select fname, upper (fname) from Emp;

6. SQL> select * from Emp where upper (fname) =’RAJ’;

Case insensitive query

LOWER:

7. SQL> select fname, lower (fname) from EMP;

8. SQL> select * from EMP where lower (fname)=’raj’;

// Case insensitive query

INITCAP:

9. SQL> select fname, initcap (fname) from EMP; //

first alphabet of word is capital others in lowercase

b) Character manipulation functions.

11
 Character functions:
CONCAT ---- joins two strings
LPAD ---- left padded
RPAD ---- right padded
LTRIM ---- trim left
RTRIM --- Trim Right
TRIM ---- all TRIM
SUBSTR ---- extracting string from required position of string
REPLACE ---- replace char from specified characters
TRANSLATE---- goes for a 1 character at a time
INSTR ---- returns starting position of string (numeric value)
LENGTH ---- returns no of characters
ASCII ---- returns ASCII value of 1 st character
CHR ---- opposite to ASCII .gives character of ASCII value

Concatenation operator: ||

2. SQL> select fname || lname from EMP;

/*it will join two strings without space in between*/

3. SQL> select fname ||’ ‘|| lname from EMP;

select fname ||' is a '|| job from EMP

CONCAT : This function takes two arguments

12
SELECT CONCAT (FNAME, lname) from EMP;

Select concat (concat (fname,' '), lname) from EMP;

Select concat (concat (fname,' is a '), job) from EMP;

LPAD:
10. SQL> select fname, lpad (fname,25) from EMP;

/*Output is right justified by default blank space */

11. SQL> select fname, lpad (fname,25,’*’) from EMP;

//output will be
*********arun

It is very useful for cheque printing.

RPAD:
12. SQL> select fname, rpad (fname,25) from EMP;

/*Output is left justified by default blank space */

13. SQL> select fname, rpad (fname, 25,’*’) from EMP;

//output will be
Arun*************

For center padding use rpad and lpad both.

Ans:=select fname,rpad(lpad(fname,15,'*'),30,'*') from EMP

LTRIM: trims from Left side

RTRIM: trims from right side

TRIM :Trims from both sides

Create table emptest

(empno number(10),
fname varchar2(30));

Insert into emptest values (1,’Raj ‘);

Insert into emptest values (2,’ Vikas’);
Insert into emptest values (3,’ Mihir ‘);

Insert into emptest values (4,’D’’souza’);

Select * from emptest;

select empno,trim (fname) from emptest;

Select * from emptest

13
where fname ='Raj';

Select * from emptest

where Rtrim(fname)=’Raj’;

Select * from emptest

Where ltrim (fname) like ‘Vikas’;

Select * from emptest

Where trim (fname) like ‘Vikas’;

Select * from emptest

Where trim (fname) like ‘Mihir’

SUBSTR:- It is used to divide a string into number of sub-strings.

1. SQL > select fname, substr (fname, 3)

from emp;

// From LHS 3rd position till the end of string.

2. SQL> select fname, substr (fname, - 3 )

from emp;
// from RHS 3rd position till end of string.

3. SQL > select fname, substr (fname, 3,2 )

From EMP;
// from LHS, from 3rd position extract 2 characters i.e. 3rd and 4th character

4. SQL > select fname, substr (fname, -3,2 )

From emp;

REPLACE

Select fname, replace (fname,'aj','xy')

From Emp;

Select fname, replace (fname,'a','xy')

From Emp

TRANSLATE(Translate works character by character.)

Select fname, Translate (fname,'aj','xy')

From Emp;

Select fname, Translate (fname,'a','xy')

From Emp

INSTR:-

It is used to get the position of a character in a string.

10. SQL> Select fname, instr (fname,’a’)

14
 From Emp;

11. SQL> Select fname, instr (fname,’a’,3)

From Emp;
It will start searching from the third location from the LHS ,and go till the end.

12. SQL> Select fname, instr (fname,’a’,-3)

from Emp;
It will start searching from the third location from the RHS, and go till the START of the
string.

DUAL is a dummy table it has only 1 row and 1 column.

Select instr('excellent','e',3) from dual

Select instr ('excellent','e',3,2) from dual

Select instr ('excellent','ex',1,1) from dual

Select 2+2 from dual;

LENGTH

14. SQL> Select fname, length(fname)

From Emp;

ASCII

Select fname , ascii(fname)

from Emp;
Eg: Arun o/p 65

SQL> Select ascii(‘z’) from dual;

17. SQL> Select 100+100 from dual;

O/p 200

18. SQL> Select 100+100, ‘Hello’ from dual;

O/p 200 Hello

CHR : returns character .

Select chr(‘65’) from dual;

2.NUMBER FUNCTIONS:

Round: round off to specified decimal places

Trunc: truncate the no. Simply removes decimal part

Ceil: adds 1 to LHS

Floor: same as trunc

Sign: return sign of a number

15
Mod: returns the remainder. Also works with floating numbers.

Sqrt: returns square root. Only for positive nos.

Power: (a)^ b.

Absolute: returns absolute value.

Still different functions like sin(),sinh(),cos(),cosh(),tan(),tanh(),ln()/*natural

ROUND:

20. SQL> Select Round (1235.577) From dual;

EG: 1235.577 o/p 1236

21. SQL> Select Round (1235.577,1) From dual;

EG: 1235.577 o/p 1235.6

SQL> Select Round (1235.577,-2) from dual;

(when –ve sign it works before the decimal, as -1 so it checks the first digit to the left of
decimal, if that digit is >=5 then make it 0 and increment the second digit to the left by
1,else if <5 then make it 0 and do not increment the second digit)

TRUNCATE:

23. SQL> Select trunc (1234.577) from dual;

EG: 1234.577 o/p 1234

24. SQL> Select trunc (1234.577,1) From dual;

EG: 1235.577 o/p 1234.5

Select trunc (1235.577,-1) from dual;

CEIL: will 1 to LHS

25. SQL>

Select ceil (1234.577) from dual;

EG: 1235.577 o/p 1235

26. SQL> Select ceil(1001.1001)

From dual;
o/p 1002

FLOOR: same as trunc function.

SIGN:

27. SQL> Select SIGN (-15) FROM DUAL;

o/p -1 for negative no
+1 for positive no

16
 0 for zero

MOD:

28. SQL> Select mod(9,5) FROM DUAL;

o/p 4

SQRT:

29. SQL> Select sqrt (81) FROM DUAL;

O/p 9

POWER:

30. SQL> Select power (10,3) FROM DUAL;

o/p  1000

31. SQL> Select power (10,1/3) FROM DUAL;

o/p  2.154

ABS:

32. SQL> Select abs (-10) FROM DUAL;

o/p  10

3.DATE FUNCTIONS

SYSDATE: it returns server date and time.

Select sysdate from dual;

1. SQL> Select sysdate-hiredate

from Emp;

o/p  Returns no.of days . If decimal part then it is a time component.

2. SQL> Select round (sysdate-hiredate)

From EMP;
o/p  eliminates decimal part

3. SQL> Select sysdate+10

From dual;
o/p  add 10 days to current date

Select round( (sysdate-hiredate) /7) as “Weeks”

From EMP

Date arithmetic is possible bcoz internally date is stored as a number.

Months_between:-

4. SQL> Select months_between(sysdate,hiredate)

From Emp;

17
 o/p  returns no.of Months between the two dates,the decimal part is the day .

5. SQL> Select add_months (sysdate, 2)

From dual;

O/p  adds two months to hiredate

6. SQL> Select add_months (sysdate,-2) from dual;

O/p  subtract two months to hiredate

7. SQL> Select last_day (sysdate) from dual

O/p  returns the last day of the month

8. SQL> Select next_day (sysdate,’wednesday’)

From dual;

O/p  returns the date of next Wednesday

How will u find the date of last Wednesday:-

9. SQL> Select next_day (sysdate-7,’wednesday’)

From dual;

Using Date Functions

Assume SYSDATE = ’23-JUL-2006’:

Select ROUND (SYSDATE,’MONTH’) from dual;

Select ROUND(SYSDATE ,’YEAR’) from dual;
Select TRUNC(SYSDATE ,’MONTH’) from dual;
Select TRUNC(SYSDATE ,’YEAR’) from dual;

CONVERSION FUNCTIONS:-

1.Implicit Conversion
create table test1
(col1 number,
col2 varchar2 (10));

There is a implicit conversion from Character to Number.

There is a implicit conversion from Number to character.

Insert into test1 values ('1',12);

2.Explicit Conversion

To_char:--It is used to convert a number or a date data type to character

To_date-It is used to convert a character datatype to a date data type.
To_number-It is used to convert a character datatype to a number.

SQL> Select to_char(sysdate,’DD-MM-YY’)

from dual;
o/p  27-02-02

18
Various facilities for date:
 DD/MM/YYYY // 4 bytes of the year
27/02/2001
 DD/MM/YYY // 3 bytes
27/02/001
 DD/MONTH/YY // spell out the whole month string of 9 chars
27/FEBRUARY/01
 DD-Month-YY
27-February-01
 DD-month-YY
27-february-01
 DD-MON-YY
27-FEB-01

1. SQL> Select to_char(sysdate,’day’) from dual;

o/p  Tuesday // displays day of the month

2. SQL> Select to_char(sysdate,’dy’) from dual;

o/p  Tue // displays abbreviated day of the month

3. SQL> Select to_char(sysdate,’year’) from dual;

o/p  two thousand two // used for conversion from no. to word

Select to_char (sysdate,'yyyy') from dual

4. SQL> Select to_char(sysdate,’DD’) from dual;

// displays day of the month

5. SQL> Select to_char (sysdate,’DDth’) from dual;

6. SQL> Select to_char(sysdate,’DDsp’) from dual;

7. SQL> Select to_char(sysdate,’DDspth’) from dual;

8. SQL> Select to_char(sysdate,’d’) from dual;

// day of the week

9. SQL> Select to_char (sysdate,’DDD’) from dual;

returns day of the year

10.SQL> Select to_char(sysdate,’w’) from dual;

returns week of the month

11. SQL> Select to_char(sysdate,’ww’) from dual;

returns week of the year

Select to_char(sysdate,'mm') from dual

Select fname,hiredate, to_char(add_months (hiredate,6),'dd-mon-yyyy') “Conf. date”

from emp;

For time part:

1. SQL> Select to_char(sysdate,’DD/MM/YY HH:MI:SS’) from dual;

19
 o/p  27/02/02 01:42:23 /*it displays server time & not the machine time*/

2. SQL> Select to_char(sysdate,’DD/MM/YY HH:MI:SS AM’) from dual;

o/p  27/02/02 01:42:23 PM

Select to_char(sysdate,'DD/MM/YY HH:MI:SS pm') from dual

3. SQL> Select to_char(sysdate,’DD/MM/YY HH24:MI:SS’) from dual;

o/p  27/02/02 13:42:23 /*its in 24 hour format*/

CHAR TO DATE CONVERSION:

SQL> Select to_date (’10-MAY-01’,’DD/MON/YY’) + 10 from dual;

o/p  2O-MAY-01

Create table s1(col1 date);

insert into s1 values (TO_DATE('01-jun-480 08 30 BC','DD-MON-YYYY HH:MI BC'))

SELECT TO_CHAR(COL1,'DD-MON-YYYY HH:MI AD') FROM S1;

CHAR TO Number CONVERSION:

Select to_number (‘10’)+10 from dual;

GENERAL FUNCTIONS:

GREATEST AND LEAST

1. SQL> Select SAL, greatest (SAL,15000) from emp;

/*returns the greatest value of the two*/

3. SQL> Select SAL, least (SAL,15000) from emp;

/*returns the least value of the two*/

Conditional Expressions:-

Provide the use of IF-THEN-ELSE Logic within an SQL Statement.

1.Using Decode statement.

2.Using CASE Statement.

Amazing Power in a single word:- DECODE

1. SQL>

select deptno, decode (deptno,10,’ten’,20,’twenty’,’others’)

from emp;
o/p10 ten
20 twenty

If the default value is omitted ,a null value is returned.

20
 Select deptno, decode (deptno,10,'ten',20,'twenty') from emp

Decode works with character also:-

Select fname,sal,job,
decode(job,'Programmer',sal*.10,'Analyst',sal*.15,'Manager',sal*.20,200) Bonus from
emp;

CASE Statement:-
It is available only from oracle9i
It is more flexible than decode.

The above two sql statements can also be done with

case statement.

Select deptno ,case deptno When 10 then 'Ten'

When 20 then 'Twenty'
Else 'others' end “Label” from emp;

Select deptno ,case When deptno=10 then 'Ten'

When deptno=20 then 'Twenty'
Else 'others' end from emp

Select deptno ,case deptno When 10 then 'Ten'

When 20 then 'Twenty'
end from emp;

Select deptno ,case When deptno=10 then 'Ten'

When deptno=20 then 'Twenty'
Else 'others' end from emp

2. Select fname,job,sal ,CASE job When 'Programmer' then sal*.10

When 'Analyst' then sal*.15
When 'Manager' then sal*.20
else 200 end comm from emp;

The following statement is possible only with Case,and not with decode:-

3. Select fname ,Sal, case when sal > 1 and sal < 5000 then'low salary '
when sal between 5000 and 15000 then 'medium salary '
when sal between 15001 and 25000 then 'high salary'
Else 'very high salary' end Typesal from emp;

So Decode works with (=) operator and case works with (=,>,<,or,and….)
So Case is more flexible.

USER and UID

3. SQL>select user from dual;
Returns oracle user name
4. SQL>select user, UID from dual;
Returns user ID also used for maintaining record & user actions

21
NVL function : Null value

Create table emp_sal

(empno number,
Sal number,
comm number);

insert into emp_sal values(1,5000,200);

insert into emp_sal values(2,7000,null);
insert into emp_sal values(3,12000,700);
insert into emp_sal values(4,25000,null);

Select empno,sal ,nvl(comm,100) from emp_sal

Select empno, sal, comm, sal+nvl (comm,0) "Total Sal” from emp_sal

 If comm is NULL it will assume it as zero using this function

Select sal, comm, sal+ NVL(comm,100) “Total sal” from emp_sal;

o/p sal comm sal+comm
5100 5100
 If comm is NULL then consider it as 100.

Use of NVL:
Eg: NVL(job,’manger’)
NVL(city,’mumbai’)

NVL2 Function:-

NVL2 (Exp1,Exp2, Exp3)

If the first exp is not null, it will return the second expr else it willl return the third exp.

Select empno,sal,comm,nvl2(comm,sal+comm,sal) “Total sal” from emp_sal

COALESCE Function:-

Coalesce(Exp1,Exp2,Exp3,Exp4,exp5,…………………………….Expn)

This function returns the first not null exp from the list.

Select LNAME, FNAME,coalesce(lname,fname) from emp;

Update emp
set lname =null
where fname like 'raj';

22
 4.Multiple Row Functions:-
Group Functions/ Aggregate Functions

Eg: TABLE emp

Empno Ename Sal Deptno Job Manager

1 Arun 8000 1 M 4
2 Ali 7000 1 C 1
3 Kiran 3000 1 C 1
4 Jack 9000 2 M
5 Thomas 8000 2 C 4

Select SUM (sal) from emp;

Returns sum of sal = 35000 i.e it returns single value

2. Select AVG (sal) from emp;

Returns avg of sal = 35000/5=7000 i.e it returns single value

Group Functions will ignore the null values.

Insert into emp values (11,'ankur','mathur','Clerk', null, 10, sysdate);

Select AVG (Nvl(sal,0)) from emp;

Rollback;

3. Select MIN(sal) from emp;

Returns min of sal = 3000 i.e it returns single value

4. Select MAX(sal) from emp;

Returns max of sal = 9000 i.e it returns single value

5. Select COUNT(sal) from emp;

23
 // count no. of sal where sal ! = NULL

6. Select count(*) from emp;

returns total no.of rows from the table even if some records have null
values.

8. Select SUM(sal)/ count(*) from emp;

9. Select SUM(sal) from emp where deptno=10;

10. Select count(*) from emp where job=’Clerk’;

o/p  1

11. Select count(*), sum(sal), min (sal),max(sal) from emp;

GROUP BY CLAUSE:

select deptno,sum(sal) from emp group by deptno;

o/p  DEPTNO SUM(SAL)
10 20000
20 12700
30 9000

1 ABC 5000 10
4 AAA 8000 10
6 QGG 7000 10
3 PQR 6000 20
2 AVC 6700 20
5 QAA 9000 30

It works internally as follows:

 Table is fetched from server disk to RAM
asc
10 abc 2000
10 xyz 1000
10 raj 2300
20 swati 50000
30 Vijay 7500
 Sorting is done in RAM dept-wise according to column in group by
clause in asc order.
 Grouping will be done dept-wise
 it will send response to the node/client mc

Important rules:
Select deptno,JOB ,sum(sal) from emp GROUP BY DEPTNO,JOB ;

// ERROR SINCE sum (sal) returns single value while deptno returns
multiple value.
So, whatever cols are there in select should be there in group by
clause.But Vice-versa not true.

24
  Select deptno,sum(sal) from emp group by deptno; //CORRECT

Select deptno, MAX(sal) from emp group by deptno;

O/p  deptno MAX(SAL)
1 8000
2 9000

Select deptno, sum (sal) from emp WHERE sal>7000 group by deptno;
// where clause is used to retrieve the rows
o/p  deptno sum(SAL)
1 8000
2 17000
Execution as follows:-

1. Table comes in Ram

2. Where clause applied
3.data is sorted
4.summation is done.
5.result is sent back to the node.

Note: if u give where clause after group by it gives error bcoz u r trying to
retrieve the rows from the disk of server after the grouping is done.

HAVING CLAUSE :

// We cannot use where clause to restrict the groups.

Select deptno, sum(sal) from emp

Where sum(sal) >4000
Group by deptno;

We use Having clause to restrict the groups:-

Select deptno, sum(sal) from emp

group by deptno
having sum(sal)>4000

Select deptno, sum(sal) from emp

Where sal >1000
group by deptno
having sum(sal)>4000
order by 2 desc;

Sequence of execution is a s follows:-

1.Table comes from server hard disk to Ram.

2.Where clause is applied.
3.Sorting is done
4. Grouping is done.
5.having clause fires
6.Order by fires.

25
Multiple grouping:

Insert into Emp values (8,'varun','xyz','Analyst','30000','10',’21-jun-2003’);

Select deptno, job, sum(sal) from emp group by deptno , job;

Roll;

ORDER BY CLAUSE is always last at the select statement.

Select deptno, sum(sal) from EMP

Group by deptno
order by 2;

2. Select deptno, sum(sal) from EMP where job=’Clerk’

Group by deptno
having sum(SAL)> 2000
order by 2;

3. Select max(sum(sal)) from emp

Group by deptno;

MATRIX REPORT: This report is also known as Cross tabular report.

Select deptno ,count(*) as “Number of Emp” , sum(sal), min(sal),max(sal)

from emp
Group by deptno ;

5.SQL Joins
SQL Joins are used to join the tables and to retrieve records based on a join condition

1. Equijoin/Natural join.
The Tables are joined using an (=) sign.

Create table dept

(deptno number(5),
dname varchar2 (20),
locid number);

Insert into dept values(10,’Trn’,1);

26
Insert into dept values(20,’Exp’,2);
Insert into dept values(30,’Mktg’,3);

Create table locations

(locid number(5),
locname varchar2(30),
locdetails varchar2(100));

Insert into locations values(‘1’,’Mumbai’,’dwdwd’);

Insert into locations values (2,’delhi’,’gkgkjghjk’);
Insert into locations values (3,’kolkatta’,’sdsds’);

Eg: TABLE dept .Common field between dept & emp is deptno.

Deptno Dname Locid

10 Trn 1
20 Exp 2
30 Mrktng 3

Case 1:-
Select dname, fname from emp,dept
Where dept.deptno=emp.deptno;

3 passes length 100

Case 2:-

Select dname, fname from dept ,emp

Where dept.deptno=emp.deptno;
100 passes length 3

Table on the RHS: driving table (in this case it is dept) should be the table with lesser
no. of rows.

Table on the LHS: driven table (in this case it is EMP)

For every row of the driving table, a pass is carried out through the driven table.

To join two tables, the column names need not be same.

Also the data type can be different.

Deptno
dno

Create table dept1

(dno varchar2(4),
dname varchar2 (10));

Insert into dept1 values (10,'acc');

Select dname, fname from EMP, dept1

Where dept1.dno=emp.deptno

27
--------------------------------------------------------------------------------
2. Select dname,fname, sal from EMP, dept
Where dept.deptno=emp.deptno AND
Dept.deptno=10;

3. Select deptno, dname, fname from EMP e, dept d

Where e.deptno=d.deptno;

If we write just deptno in select then there will be ambiguity of which deptno to
call. Therefore we‘ve to specify it.

Select d.deptno, dname, fname from EMP e, dept d

Where e.deptno=d.deptno;

4. Select * from EMP, dept

Where dept.deptno= emp.deptno;

 Shows all the columns in both tables.

6. Select dname , sum (sal)

From EMP, dept
Where emp.deptno=dept.deptno
Group by dname;

O/p  dname sum (sal)

Trn 18000
Exp 17000

2. Non-equijoin:-

A non-equijoin is a join condition that does not uses the Equality operator.

Select dname, fname,dept.deptno

From emp, dept
Where dept.deptno!= emp.deptno

Professor table and subject table (Assume one subject is taken by only one prof).
If u want to see, which subjects are not taken by that professor
u will use non equijoin.

3. OUTER JOIN:-

The join of two tables returning only

matched rows is an inner join./EQUI JOIN.

The join of two tables returning

Matched and unmatched rows is an outer join.

A join between two tables that returns the results

of the inner join as well as unmatched rows of the left (or
right) table is a left (or right) outer join.

28
Inner join+left outer join +right outer join =full outer join.

A join between two tables that returns the results

of an inner join as well as the results of a left and
right join is a full outer join

insert into dept values(70,'Trading',5);

The outer join operator is the (+) sign

Select fname ,dname,dept.deptno

From EMP, dept
Where dept.deptno= emp.deptno (+);

 Here the join shows all rows of dept table, even if the the dept has no employees.
(Right outer join)

Delete dept where deptno=70;

Insert into emp values(7,'vivek','mathur','clerk',2000,null,sysdate);

8. Select fname ,dname,dept.deptno

From emp, dept
Where dept.deptno(+)= emp.deptno;

 Here the query shows all rows of emp table,even which are not assigned to any
dept.(Left outer join)

Note:-we cannot use (+) on both sides.

CROSS JOIN/CARTESIAN JOIN:-

Select fname,dname
From EMP, dept

O/p  for every row of driving table it gives every row of driven table.

Eg: if EMP table has 5 rows and dept has 3 rows then according to above example we
‘ve o/p as 15 rows.

 This concept is known as CARTESIAN join or THETA join or CROSS

PRODUCT join. Eg: printing mark sheet, every student in the student table
should be joined with the all the subjects in the subject table.

SELF JOIN:-

 SELF join: open multiple copies of same table. Give them alias and then join the
table to itself is self join.
 Alias is compulsory.

29
 Create table emp_manager_tbl
(Empno number(5),
ename varchar2 (20),
Job varchar2(20),
Sal number(5),
Deptno number(5),
Mgrid number);

Insert into emp_manager_tbl values (1,’vivek’,’clerk’, 2000, 10,101);

Insert into emp_manager_tbl values (101, ’Raj’,’Manager’ ,50000, 10,777);

Insert into emp_manager_tbl values (777,’Ram’,’VP’,’90000’,10,777);

Worker manager
Vivek raj
Raj ram
Ram Ram

Emp ---worker A

1 vivek 101
101 Raj 777
777 ram 777

Emp ---Manager B

1 vivek 101
101 Raj 777
777 ram 777

Select a.ename "Worker", b.ename "Manager"

From emp_manager_tbl b, emp_manager_tbl a
Where a.MGRid= b.empno;

 After query execution temporary tables are cleared from the RAM.

Joining More than Two tables at time:

Select a.fname,a.sal,b.dname,c.locname from

Emp a,dept b,locations c
Where
a.deptno=b.deptno and
b.locid =c.locid;

Oracle 9i Introduces New syntax for Joining tables.

The new syntax does not provide any performance benefits.

30
SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)];

Cross join:-

SELECT fname, dname

FROM emp
CROSS JOIN dept;

SELECT fname,dname
FROM emp
CROSS JOIN dept Cross Join Locations;

Natural join

• The NATURAL JOIN clause is based on all columns

in the two tables that have the same name and same datatype.

• It selects rows from the two tables that have equal

values in all matched columns.

Select fname,sal, job,dname

From emp natural join dept

Select fname,sal,job,dname
From emp natural join dept
Where sal >20000

Select fname,dname,locname from emp natural join dept natural join locations;

Creating joins with the Using clause:-

1. Natural joins use all columns with matching names and data types to join the tables.
The USING clause can be used to specify only those columns that should be
used for an equijoin.

2. For this clause the column name has to be same, but datatype can be different.

3. The columns referenced in the USING clause should not have a qualifier (table
name or alias) anywhere in the SQL

Select a.fname,a.sal,a.job,b.dname
from emp a join dept b
using (deptno);

31
Select a.fname,a.sal,a.job,b.dname
from emp a join dept b
using (deptno) where sal >20000

Select e.fname, l.locname, d.dname

from emp e
join dept d
using (deptno)
join locations l
Using (locid)

Note: -We can multiple col’s in the using clause as follows:-

Select * from emp join dept
using(deptno,locid)

Creating Joins with the ON Clause:-

The ON clause can also be used as follows to join columns that have different
names:

Create table dept1

(dno number,
dname varchar2(30));

select e.fname ,e.sal,e.job,d.dname

From emp e join dept1 d
On (e.deptno = d.dno);

Note:-
select * from emp a join dept b
on a.deptno =b.deptno
and a.locid =b.locid

select * from emp a join dept b

on a.deptno =b.deptno where sal >20000

or
select * from emp a join dept b
on a.deptno =b.deptno and sal >20000

Select e.fname ,e.sal,e.job,d.dname ,l.locname

From emp e join dept d
on (e.deptno = d.deptno)
join locations l
on (d.locid=l.locid);

OUTER Joins:-

Insert into emp values(8,'Ganesh','mehta','Analyst',45000,null,sysdate);

Select e.fname,e.sal,e.job,d.dname
from emp e
left outer join dept d

32
On (e.deptno=d.deptno)

Select e.fname,e.sal,e.job,d.dname
from emp e
right outer join dept d
on (e.deptno=d.deptno)

Full outer join is possible only from oracle 9i onwards:-

insert into dept values(70,'Trading',5);

Select e.fname,e.sal,e.job,d.dname
from emp e
full outer join dept d
On (e.deptno=d.deptno)

6.SUB-QUERIES:
1. Single row sub-queries

2. Multiple row subqueries

Single row sub-queries:

Query: Select * from EMP where sal =
Subquery: (Select min (sal) from EMP);

Here we want employees with min (sal). Min (sal) is a group function, so we require
group function but this changes the meaning. Therefore we require subquery.
 First child query executes it returns value 3000 in this case then parent query
executes.

33
  Sub-queries upto 255 levels in OV7. OV8 onwards there is no limit.

To find all employees working in same department as that of Raj

8. Select * from EMP

Where deptno=
(Select deptno
From EMP
Where fname like ‘raj’);

To find all employees doing the same job as that of Raj

9. Select * from EMP

Where job =
(Select job
From EMP
Where fname like ‘raj’);

Sub-query with DELETE and UPDATE:

Delete employees, who are working in the same dept as that of raj.

17. Delete EMP

Where deptno=
(Select deptno from emp
Where fname = ‘raj’);

18. Update EMP

Set sal = sal + 0.1 * sal
Where job =
(Select job from EMP
Where fname= ‘raj’);

Find all employees doing same job as that of Mandar,and earning salary greater than
‘Mehul’

Select * from emp

where job = (select job
from emp
Where fname = ‘Mandar’)
and sal >
(select sal from emp
where fname=’Mehul’)
/

Multiple Row sub-queries:-

IN
ANY
ALL

34
Find the employees who earn the same salary as the minimum salary for each
department

Select fname, sal, deptno

from emp
where sal in (select min(sal)
from emp
group by deptno);

The below example displays employees who are not Analyst and whose salary is less
than that of any Analyst.

Select empno, fname, job, sal

from emp
where sal < any
(Select sal
from emp
where job like ‘Analyst’)
and job not like ‘Analyst’

<ANY means less than the maximum. >ANY means more than the minimum. =ANY is
equivalent to IN Operator.

Here, ANY & IN operates as logical OR.

Select empno, fname, job, sal

from emp
where sal < ALL
(select sal
from emp
where job like ‘Analyst’)
and job not like ‘Analyst’;

<ALL means less than the minimum. And >ALL means more than the maximum.
ALL works like logical AND.

7.Manipulating Data

A DML statement is executed when you:

Add new rows to a table (Insert Statement.)
Modify existing rows in a table(Update Statement)
Remove existing rows from a table(Delete Statement.)

INSERT INTO dept

(deptno, dname, locid)
VALUES (&department_id,’&department_name’,&locationID)

& is a placeholder for the variable value.

INSERT statement with a subquery

35
1.No of cols and datatype has to be same.

Create table sales_reps

(srepid number,
fname varchar2(30),
lname varchar2(30),
sal number
);

UPDATE EMP
SET JOB= ‘sales_reps’ WHERE DEPTNO=10;

insert into sales_reps

select empno,fname,lname,sal from emp
where job='sales_reps'

roll;

/structure of emp and copy_emp has to be same.

(create table copy_emp
As select * from emp where 1=2)

insert into copy_emp select * from emp;

Changing Data in a Table:-

You can modify existing rows by using the UPDATE statement.

update emp
set job='SalesRep'
where job='Officer';

Updating a Column with a Subquery:-

Update emp
Set sal= (select sal from emp
Where fname like ’raj’)
Where empno=5;

Updating Two Columns with a Subquery:-

Update emp
Set sal=(select sal from emp
Where fname like ’raj’),
Job=(select job from emp
Where fname like ’raj’)
Where empno=5;

UPDATE copy_emp
SET deptno= (SELECT deptno

36
FROM emp
WHERE empno = 1)
WHERE job = (SELECT job
FROM emp
WHERE empno = 2);

//tables can be different.

Deleting Rows:--

Delete from emp

Where fname like ’raj’;

Delete from emp;

Delete from emp

Where deptno in(10,20);

Delete Rows using a Subquery:-

Delete from emp

Where deptno=(select deptno from emp
Where fname like ‘ram’);

The MERGE(upsert) Statement

• Provides the ability to conditionally update or insert data into a table.

• Performs an UPDATE if the row exists, and an

INSERT if it is a new row:
– Avoids separate updates
– Increases performance and ease of use

The decision whether to update or insert into the target table is based on a condition in
the ON clause.

Target table :-copy_emp

Source table:-emp

MERGE INTO copy_emp c

USING emp e
ON (c.empno = e.empno)
WHEN MATCHED THEN
UPDATE SET
c.fname = e.fname,
c.lname = e.lname,
c.hiredate = e.hiredate,
c.job = e.job,
c.sal = e.sal,
c.deptno = e.deptno
WHEN NOT MATCHED THEN
INSERT VALUES(e.empno, e.fname, e.lname,
e.job, e.sal, e.deptno, e.hiredate);

37
Transaction:-

A transaction begins when the first DML statement is encountered and ends when one
of the
following occurs:

• A COMMIT or ROLLBACK statement is issued

• A DDL statement, such as CREATE, is issued
• A DCL statement is issued
• The user exits from sql prompt. (Commit)
• A machine fails or the system crashes. (Rollback)

Controlling Transactions:-savepoint
(Using savepoints)

Insert ……….
Update………
Savepoint abc;
Delete ………..
Savepoint pqr;
Update……..
Rollback to pqr;

Savepoints are not schema objects and cannot be referenced in the data dictionary.
If you create a second savepoint with the same name as an earlier savepoint, the earlier
savepoint is deleted.
Savepoints are automatically cleared when a commit or full rollback is issued.
You cannot commit to a savepoint.

Autocommit: -
The AUTOCOMMIT command can be toggled on or
Off. If set to on, each individual DML statement is committed as soon as it is executed.
You cannot roll
back the changes.

Set autocommit on
Insert into test1 values (2,2);
Set autocommit off;

State of the Data

Before COMMIT or ROLLBACK:-

• The previous state of the data can be recovered.

• The current user can view the results of the DML

operations by using the SELECT statement.

• Other users cannot view the results of the DML

statements by the current user.

• The affected rows are locked; other users cannot change

the data within the affected rows.

State of the Data after COMMIT:-

38
• Data changes are made permanent in the database.

• The previous state of the data is permanently lost.

• All users can view the results.

• Locks on the affected rows are released; those rows

are available for other users to manipulate.

• All savepoints are erased.

State of the Data After ROLLBACK:-

• Data changes are undone.

• Previous state of the data is restored.

• Locks on the affected rows are released

Savepoints are erased.

Locking:-
In an Oracle database, locks:

• Prevent destructive interaction between

Concurrent transactions
• Are held for the duration of the transaction.
• Are of two types: explicit locking and implicit
Locking

Implicit locking occurs for

All DDL and DML statements

The users can also lock data manually, which is called explicit locking.
(Done with the help of CURSORS).

Implicit Locking

• Two lock modes:

– Exclusive lock.
– Share lock.

• Locks held until commit or rollback

DML locks occur at two levels:

• A share lock is automatically obtained at the table level during DML operations.
several transactions can acquire share locks on the same resource.(will restrict other
users from drop table or alter table).

39
• An exclusive lock is acquired automatically for each row modified by a DML
statement.

Exclusive locks prevent the row from being changed by other transactions until the
transaction is committed or rolled back. This lock ensures that no other user can modify
the same row at the same time and overwrite changes not yet committed by another
user.

8.Creating and Managing Tables:-

A table is a basic unit of storage.

Tables can be created at any time, even while users are using the database.

Tables can have up to 1,000 columns

create table emp1

(empno number,
Ename varchar2(30));

The DEFAULT Option:-

Create table test2

40
 (col1 number,
col2 date default sysdate);

Insert into test2 values (1,default);

Select * from test2;

Create table empp

( empno number,
ename varchar2(30),
job varchar2(30) default 'clerk');

Insert into empp values (2,'prashant', default);

Insert into empp(empno,ename) values (2, 'prashant');

Insert into empp values (2,'prashant', default);

select * from empp;

Default can be used with update:-

Update empp
set job=default;

Tables in the Oracle Database

• User Tables:

– Are a collection of tables created and maintained by

the user

---Contain user information

System tables

• Data Dictionary:
– Is a collection of 350 system tables created and maintained by
the Oracle Server
– Contain database information.
User_tables
select table_name from user_tables;
Desc user_tables

User_indexes
User_constraints
User_objects
User_views
User_sequences
User_synonymns

Select distinct object_type

41
From user_objects;

Advanced Data Types:-

LONG :- Variable-length character data up to 2 gigabytes

Constraints on long column:-

• A LONG column is not copied when a table is created using a sub query.
(It gives an error).
• A LONG column cannot be included in a GROUP BY or an ORDER BY clause.
(It gives an error).
• Only one LONG column can be used per table.
• No constraints can be defined on a LONG column.
• You may want to use a CLOB column rather than a LONG column.

CLOB : variable length Character data up to 4 gigabytes.

RAW : Binary data of variable length upto 2000 bytes.

(a maximum size must be specified.

Maximum size is 2000 )

Note:-Only in case of Raw datatype we need to specify the size.

LONG RAW Binary data of variable length up to 2 gigabytes.

BLOB Binary data up to 4 gigabytes.

BFILE Binary data stored in an external

file; up to 4 gigabytes.

Creating a Table by Using a Subquery

Create table empdept10

As
Select empno, fname,sal,
sal*12 annsal,
hiredate
from emp
where deptno = 10;

Create table copy_emp1234 as

(select *
from emp
where 1 = 2);

The ALTER TABLE Statement

Use the alter table statement to:

• Add a new column

42
• Modify an existing column
• Define a default value for the new column
• Drop a column

Adding a Column

Alter table empdept10

add (job_id varchar2(9));

Modifying a Column:

You can modify a column definition by using the ALTER TABLE statement with the
MODIFY clause.

Column modification can include changes to a column’s data type, size, and default
value.

Guidelines

• You can increase the width or precision of a numeric column.

• You can increase the width of character columns.

• You can decrease the width of a column only if the column contains only null values or
if the table has no rows.

• You can change the data type only if the column contains null values or if the table
has no rows.

• You can convert a CHAR column to the VARCHAR2 data type or convert a
VARCHAR2 column to the CHAR data type only if the column contains null values or if
you do not change the size.

Increse the size:-

Alter table empdept10

Modify (fname varchar2(50));

Set the default value:-

Alter table empdept10

Modify (fname varchar2(50) default 'AAA')

Change the data type:-

Alter table empdept10

Modify (empno varchar2(30))

Dropping a Column

Use the drop column clause to drop columns you no

longer need from the table.

43
Alter table empdept10
drop column fname;

The SET UNUSED Option

After a column has been marked as unused, you have no access to that column. A
SELECT * query will not retrieve data from unused columns.

In addition, the names and types of columns marked unused will not be displayed during
a DESCRIBE, and you can add to the table a new column with the same name as an
unused column

ALTER TABLE empdept10

SET unused (sal);

Select * from empdept10

ALTER TABLE empdept10

DROP UNUSED COLUMNS;

Note:-Usefull during peak loads.

Dropping a Table

• All data and structure in the table is deleted.

Drop table dept10;

Changing the Name of an Object

• To change the name of a table, view, sequence, or

Synonym, you execute the RENAME statement.

Rename empdept10 to detail_dept;

Truncating a Table

• The TRUNCATE TABLE statement:

– Removes all rows from a table

• You cannot roll back row removal when using

TRUNCATE.

• Alternatively, you can remove rows by using the

DELETE statement.

TRUNCATE TABLE detail_dept;

Difference of Truncate with delete:

44
1. It’s a DDL Command.
2. Its faster as it does not generates the rollback information.
3. It does not fires the delete triggers.
4. We cannot have a where clause in truncate statement.
5. Releases the storage space used by that table

9.SET OPERATORS:-
The SET operators combine the results of two or more queries into one query.
Queries containing SET operators are called compound queries.

1. UNION Operator: -

Create table emp1

(empno1 number,
ename varchar2(30));

Create table emp2

(empno2 number,
ename varchar2(30));

insert into emp1 values(1,'A');

insert into emp1 values(2,'B');
insert into emp1 values(3,'C');
insert into emp2 values(3,'C');

45
insert into emp2 values(4,'D');
insert into emp2 values(5,'E');

The UNION operator returns results from both queries

after eliminating duplications.

Structure of the 2 queries must be same independent of structure of two tables.

No. of columns and respective datatype must be same.

Select empno1, ename from emp1

Union
Select empno2, ename from emp2;

Column name here is picked up from 1st query alias for column name can be
given in 1st query.

3. Select empno1, ename from emp1

Union
Select empno2, ename from emp2
Order by 1;

UNION ALL will not suppress the duplicate.

4. Select empno1, ename from emp1

Union all
Select empno2, ename from emp2;

INTERSECT displays common records from both the tables.

5. Select empno1, ename from emp1

Intersect
Select empno2, ename from emp2;
O/p 

MINUS OPERATOR displays records that are present in 1st table and not present in the
2nd.

Select empno1, ename from emp1

Minus
Select empno2, ename from emp2;
O/p 

 These operators can be used any number of select stmnts.

Eg: select……………..
Union
Select……………..

46
 Minus
Select ……………...
Union
Select……………….
Union all
Select………………...
Intersect
Select…………………
Hierarchy of execution is from top to bottom. Hierarchy can be changed by putting
brackets.

Select empno1, ename from emp1

Minus
( Select empno2, ename from emp2
Union
Select empno1, ename from emp1);

EG: Table EMP

Ename Sal
A 500
B 1200
C 2500
D 3000
E 5000

7.

Select Fname, sal, ‘Low Sal’ “Remarks”

From EMP
Where sal<10000
Union
Select Fname, sal, ‘Medium Sal’ “Remarks”
from emp
where sal between 10000 and 30000
Union
Select Fname, sal, ‘High Sal’ “Remarks”
From EMP
Where sal>30000
Order by 2;
O/p ename sal Remarks
A 500 Low sal
B 1200 Medium sal
C 2500 Medium sal
D 3000 Medium sal
E 5000 High sal

10.Pseudocolumns

Pseudo columns are a fake column that can be selected.

47
Rownum: it is just a counter. Its initialized in the server RAM,not stored in the
database.

8. Select rownum, fname from EMP;

9. Select rownum, fname from emp

Where rownum=5;

// doesn’t work. Impossible condn. Only works for

rownum = 1

10. Select rownum, fname from EMP

Where rownum < 5;

In this case rows are fetched one by one. So it goes upto 4.

O/p 
Rownum Ename
1 Rich
2 Edward
3 John
4 Gaurav

11.Constraints
The Oracle Server uses constraints to prevent invalid data entry into tables.

The constraints defined on backend are valid for all front ends.

Constraint types:

NOT NULL Specifies that the column cannot contain a null value

UNIQUE Specifies a column or combination of columns whose values must be

Unique for all rows in the table.(can contain null values)

PRIMARY KEY :-Uniquely identifies each row of the table. It cannot contain null values.

FOREIGN KEY:-Establishes a Master-Detail (Parent child) relationship between the

column of the detail table and a column of the master table.

48
CHECK:- specifies a condition on a column or set of columns that must be true

• Column constraint level

Defined on only one column.

• Table constraint level

The NOT NULL Constraint

Ensures that null values are not permitted for the

Column:

The NOT NULL constraint can be specified only at the column level, not at the table
level.

Create table employees(

employee_id number(6),
last_name varchar2(25) constraint emp_name not null,
salary number(8,2),
commission_pct number(2,2),
hire_date date
constraint emp_hire_date_nn
not null);

insert into employees(employee_id,last_name) values(1,'a');

The UNIQUE Constraint

A UNIQUE key integrity constraint requires that every value in a column or set of
columns (key) be unique—that is, no two rows of a table can have duplicate values in a
specified column or set of columns.

The column (or set of columns) included in the definition of the UNIQUE key constraint is
called the unique key.

If the UNIQUE constraint comprises more than one column, then that group of columns
is called a composite unique key.

Drop table employees;

Create table employees(

employee_id number(6),
last_name varchar2(25) not null,
email varchar2(25),
salary number(8,2),
commission_pct number(2,2),
hire_date date not null,

49
constraint emp_email_uk unique(email));

Composite Unique constraint:-

Drop table employees;

Create table employees(

employee_id number(6),
ename varchar2(25) not null,
phoneno number,
email varchar2(25),
Salary number(8,2),
commission_pct number(2,2),
hire_date date not null,
Constraint emp_name_no unique(ename,phoneno));

The PRIMARY KEY Constraint

Primary key constraints can be defined at the column level or table level.
A composite primary key is created by using the table-level definition.

Difference between primary/unique (v.v.imp )

1. A table can have only one primary key constraint but can have several unique
constraints.
2. Primary key will not allow null values
Egs like passport no,emailed are unique keys

Create table departments1 (

department_id number (4),
department_name varchar2 (30)
constraint dept_name_nn1 not null,
manager_id number (6) unique ,
location_id number(4),
Constraint dept_id_pk1 primary key(department_id));

Drop table employees

Create table emp222

(empno number primary key,
ename varchar2(30));

Composite Primary key

Create table employees (

employee_id number(6),
ename varchar2(25) not null,
phoneno number,
email varchar2(25),
Salary number(8,2),
commission_pct number(2,2),
hire_date date not null,
constraint emp_name_no primary key (ename,phoneno));

FOREIGN KEY Constraint:-

50
It is used to build a Parent-Child relationship.

The child table references parent table.

Foreign key constraints can be defined at the column or table constraint level.
A composite foreign key is created by using the table-level definition.
The example on the slide defines a foreign key constraint on the department_id column
of the employees table, using table-level syntax.
The name of the constraint is emp_deptid_fk.

The foreign key can also be defined at the column level, provided the constraint is based
on a single Column.

Drop table employees;

Create table employees (

employee_id number(6) ,
last_name varchar2(25) not null,
email varchar2(25),
mgr_id number(6),
salary number(8,2),
commission_pct number(2,2),
hire_date date not null,
department_id number(4),
constraint emp_dept_fk foreign key (department_id)
references departments1(department_id) ,
constraint emp_email_uk unique(email),
constraint emp_pk primary key (employee_id,email));

on delete set null

• FOREIGN KEY is used to define the column in the child table at the table constraint
level.

• REFERENCES identifies the table and column in the parent table.

• ON DELETE CASCADE indicates that when the row in the parent table is deleted, the
dependent rows in the child table will also be deleted.

• ON DELETE SET NULL:-converts foreign key values to null when the parent value is
removed.

RESTRICT RULE:-
The default behavior is called the restrict rule, which will not allow to delete or update the
parent record when the child records are existing.

Composite Foreign key:-

Create table testfk

(col1 number,
col2 varchar2(30),
col3 varchar2(45),
constraint test_fk foreign key(col1,col2)

51
references employees(employee_id,email) );

The CHECK Constraint

Defines a condition that each row must satisfy

Create table empsal_chk

(empno number(6),
sal number(7),
ename varchar2(25),
constraint empsal_chk check (sal > 1000));

Check constraint is also a table level constraint.

Create table empsal_chk

(empno number(6),
sal number(7),
comm number(6),
ename varchar2(25),
constraint empsal_chk check (sal+comm > 1000));

Adding a Constraint Syntax

Drop table test_fk;

Drop table employees

Create table employees (

employee_id number (6) ,
last_name varchar2(25) not null,
email varchar2(25),
mgr_id number(6),
salary number(8,2),
commission_pct number(2,2),
hire_date date not null,
department_id number(4),
constraint emp_dept_fk foreign key (department_id)
References departments1 (department_id) on delete set null,
Constraint emp_pk primary key (employee_id));

Self –Referencing:-

Alter table employees

add constraint emp_manager_fk
Foreign key(mgr_id)
references employees(employee_id);

Create table acb

(col1 number,
col2 varchar2(20),
col3 number);

Alter table acb

Add constraint pk_1 primary key(col1);

52
Alter table employees
Add constraint email_unq unique (email)

Alter table acb

Add constraint acb_chk check(col1>10);

Alter table acb

Modify col3 NOT NULL;

Dropping a Constraint

Alter table acb

drop constraint pk_1 ;

Disabling Constraints

Alter table acb

Add constraint pk_1 primary key(col1);

Alter table acb

disable constraint pk_1

Enabling Constraints

If you enable a constraint, that constraint applies to all the data in the table.
All the data in the table must fit the constraint.

Alter table acb

Enable constraint pk_1

Viewing Constraints:-

Select b.constraint_name, b.constraint_type,a.position,

a.Column_name
From user_cons_columns a ,user_constraints b
where a.constraint_name=b.constraint_name and
lower(b.table_name) = ’employees’;

Select constraint_name, constraint_type,

search_condition
From user_constraints
where lower(table_name) = ’employees’;

Select constraint_name, constraint_type,

search_condition
From user_constraints
where lower(table_name) = ’acb’;

C----CHECK Constraints
C----NOT NULL Constraint.

53
Search _condition will help us to identify.

12.SQL * plus
1. Setting the environment
2. Reporting tool

Note:-
 For SQL commands “;” is compulsory
SQL * PLUS “;” is optional

 IN SQL last command goes to the buffer

SQL * PLUS last command doesn’t go into the buffer

1. SQL> Select * from EMP

Where deptno=&x

// asks the user to enter the value of deptno

2. SQL> SET VERIFY OFF

It suppresses substitution text
I.e. old: ___________

54
 New: __________
will not be shown.
3. SQL> SET VERIFY ON : It shows the substitution text. By default it is ON.

4. SQL> Select * from emp

Where job = ‘& designation’

5. SQL> Select deptno, job, fname, sal

From EMP order by deptno, job;

6. SQL> SET FEEDBACK OFF

It doesn’t show messages like “14 rows selected”.

7. SQL> SET FEEDBACK ON

It show messages like “14 rows selected”.

BREAK COMMAND: (temporary command)

8. SQL> BREAK on deptno;
SQL> /
O/p 
10 Clerk
----------------------
----------------------
20 Analyst
----------------------
----------------------

It affects all the tables with same column.

9. SQL> Clear BREAK

10. SQL> Break on deptno skip 2

SQL> /
o/p 
10 Clerk
----------------------
----------------------
Skips two lines whenever dept changes

20 Analyst
----------------------
----------------------
11. SQL> Break on deptno skip page
SQL> /
12. SQL> Break on deptno DUP skip page /*Dup-------display duplicates*/
SQL> /
13. SQL> Break on deptno on job
SQL> /
14. SQL> Break on deptno skip page on job skip 2
SQL> /
Whenever dept changes skip page & whenever job changes skip 2 lines.
15. SQL> break
Shows break settings.

55
17. SQL> SET PAGESIZE 5
SQL> /
Sets 5 Lines / Page.

Page size by default is 14. Max value is 32767

18. SQL> SET LINESIZE 25

SQL> /
Sets linsize to 25 characters.

LINESIZE:
Default value 80 . Maximum value 32000.

21. SQL> Break on deptno skip page

SQL> compute sum of sal on deptno
SQL> /

 For compute to work BREAK is necessary . Without it will not work

compute.

22. SQL> compute sum label ‘Total:-’ of sal on deptno

SQL> /
o/p 
Deptot:-
23. SQL> Clear computes

SQL> /
24. SQL> compute avg min max sum of sal on deptno
SQL> /
 Everything is calculated . And grand total is displayed.

24. Clear computes

SQL> break on report on deptno skip page on job skip 1

SQL> /
SQL> compute sum of sal on report
SQL> compute sum of sal on deptno

26. SQL> SET HEAD OFF

Column heading is not displayed i.e. it is turned off.

27. SQL> SET HEAD ON

28. SQL> SET SQLPROMPT ‘Excellent>’

30. SQL> SET SQLPROMPT ‘SQL>’

31.SQL> SET TIME ON /*MACHINE TIME IS DISAPLAYED*/

32. SQL> column sal heading ‘salary’

56
33. SQL> column sal heading ‘salary’ format 99,9999.99
o/p salary
1,300.00
34. SQL> column sal off // original format
35. SQL> column sal on // salary in place again
36. SQL> clear column // original setting comes again
37. SQL> column Fname heading ‘employee_name’
SQL> /
38. SQL> column fname heading ‘emp/name’ format a35
lSQL

Width of 35 characters is allocated to column empl/name.0

Header and Footer:

column fname format a50

clear column

Select fname,job,sal from emp;

Set lin 70

39. SQL> TTITLE CENTER ‘Excellent’

Page header ‘EXCELLENT’ center justified

40. SQL> TTITLE CENTER ‘Excellent’ skip 2

After header two lines are skipped

41. SQL> TTITLE CENTER ‘EXCELLENT’ SKIP 1 CENTER ‘MUMBAI’ SKIP 2

O/P  EXCELLENT
MUMBAI
42. SQL> BTITLE CENTER ‘PAGE ’ SQL.USER
System variables:
SQL.PNO  Page no.
SQL.USER  user name
SQL.RELEASE  version of oracle
SQL.SQLCODE  stores error no. of the last error that took place.
If the sql stat is successful then it returns a value 0.

43. SQL> TTITLE OFF

Switch off page headings

44. SQL> BTITLE OFF

Switch off bottom headings

49. SQL> SPOOL C:\ABC.txt

ABC.LST is created in current folder . After all the commands & output are
saved to this file .
50. SQL> SPOOL OFF // to stop spooling

13.Sequences

57
A sequence:
• Automatically generates unique numbers

• Is a sharable object.

• Is typically used to create a primary key value.

• Speeds up the efficiency of accessing sequence

values when cached in memory.

CREATE SEQUENCE sequence

[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}] 100
[{MINVALUE n | NOMINVALUE}] 1
[{CYCLE | NOCYCLE}] cycle
[{CACHE n | NOCACHE}];

Create sequence abc

Minvalue 4
Maxvalue 1000
Increment by 1
Cycle

Create sequence abc

Start with 4
Minvalue 1
Maxvalue 1000
Increment by 1
Cycle

Only for the first time the seq starts from 4,then it starts from 1.

(Can be used in a scenario when some data entry has happened already)

Default values in a Sequence:--

Create sequence abc;

Or
Create sequence abc
increment by 1
start with 1
minvalue 1
nomaxvalue
nocycle
cache 20;

nomaxvalue:-10 p27

58
Note:-Start with cannot be less than min value.

Note:-If not specified a value for start with,it will take the same value as
min value.

Create sequence abc

Confirming Sequences

• Verify your sequence values in the

USER_SEQUENCES data dictionary table.

• The LAST_NUMBER column displays the next

available sequence number if NOCACHE is
specified.

SELECT sequence_name, min_value, max_value,

increment_by, last_number,cache_size,cycle_flag
FROM user_sequences where lower(sequence_name) like ‘abc’

Viewing the Next Available Sequence Value without Incrementing It

If the sequence was created with NOCACHE, it is possible to view the next available
sequence value without incrementing it by querying the USER_SEQUENCES table.

NEXTVAL and CURRVAL Pseudocolumns

The NEXTVAL pseudocolumn is used to extract successive sequence numbers from a
specified sequence. You must qualify NEXTVAL with the sequence name. When you
reference sequence.NEXTVAL, a new sequence number is generated.

The CURRVAL pseudocolumn is used to refer to a sequence number that the current
user has just generated.

1. SQL> create sequence ABC;

create table emp_seq

(empno number,

ename varchar2(20),

sal number,

deptno number);

2. SQL> insert into emp_seq values (abc.nextval , ‘A’ , 5000 , 1);

3. SQL> insert into emp_seq values (abc.nextval , ‘B’ , 6000 , 1);

4. SQL> Rollback;

insert into emp_seq values (abc.nextval , ‘B’ , 6000 , 1);

59
If we login again , then it continues from empno 3 bcoz stored object in database

is 2.

Create sequence ABC

Minvalue 1

Maxvalue 1000

Increment by 2

Cycle;

SQL> create sequence ABC12

Minvalue 1

Maxvalue 1000

Increment by -1;

Note: The sequence will start from 1000.

Note: Decrementing seq. is used in airline res. System to show no of tickets balance.
 Rollback and commit have no effect on value of sequence.

Gaps in sequence values can occur when: A rollback occurs

The system crashes:--auto rollback

A sequence is used in another table
Sequence.nextval

Altering the Sequence: -

Create sequence xyz

Minvalue 1

Maxvalue 10

Increment by 1

Cycle

NOcache;

Select xyz.nextval from dual;

SQL> alter sequence xyz

Minvalue 2;

Note: - MINVALUE cannot exceed the current value.

60
Change the increment value, maximum value,
Minimum value, cycle option, or cache option.
Drop sequence abc;
Create sequence abc;

ALTER SEQUENCE ABC

INCREMENT BY 3
MAXVALUE 9000
NOCACHE
CYCLE;
Sequence altered

Note:-1.The START WITH option cannot be changed using ALTER SEQUENCE

2. MAXVALUE cannot be made to be less than the current

Value

3. MINVALUE cannot exceed the current value.

14.VIEWS

A view is a logical table based on a table or another view.

The tables on which a view is based are called base tables.

A view does not contain any data, the base table has data.

Constraints defined on the base table are enforced by the view.

Advantages of Views:-

Views restrict access to the data because the view can display selective columns from
the table.

It is data encapsulation.

Types of views:

• A simple view is one that:

Derives data from only one table
Contains no functions or groups of data
Can perform DML operations through the simple view

61
• A complex view is one that:
Derives data from many tables.(view based on joins)
Contains group functions (avg,sum,max,min) of data
Contains expressions .e.g. sal*12 annual_salary
Distinct clause
Rownum keyword.

Creating a View:

Create or replace view emp_view

as select empno, lname, job,sal
from emp;

Create a view by using column aliases in the

Subquery:

Create view emp_sal10

as select empno Employee_no, lname lastname,
sal*12 annual_salary
from emp
where deptno = 10;

Desc emp_sal10;

Create view empsal10 (Employee_no, lastname, annual_salary)

as select empno, lname, sal*12
from emp
where deptno = 10;

NOTE:-

Create or replace view v1

as
Select fname,lname,job,sal from emp
Order by sal asc;

Select * from v1
Order by sal desc;

The outer ORDER BY clause takes the precedence and displays sal in desc order.

Select TEXT from user_views

where view_name=’V1’;

The select statement of the view is kept in a column named TEXT which is of the type
LONG.

Modifying a View

62
Create or replace view emp_sal10
(Employee_no, empname, salary, deptno)
as select empno, fname || ’ ’ || lname,
sal, deptno
from emp
where deptno = 10;

Creating a Complex View

Create a complex view that contains sql joins

to display values from two tables.

Create view emp_dept

As
Select empno, fname, lname,job, dname
From emp,dept
Where emp.deptno=dept.deptno;

Insert into emp_dept values(……);

Select * from emp_dept;

(This will in turn fire the sql statement of the above view,so view will not give any
performance benefit ,but it makes queries simpler)

Create view empdept_sum_view

(deptname, minsal, maxsal, avgsal)
as select d.dname, min(e.sal),
max(e.sal),avg(e.sal)
from emp e, dept d
where e.deptno = d.deptno
group by d.dname;

DML Operations on a View

• You can perform DML operations on simple views.

Create view v1
As select empno,fname,sal from emp;

Update v1
Set sal=7777
Where empno=2;

Insert into v1 values (12,’aaa’,2000);

Select * from emp;

Roll;

63
DML Operations are not possible for view based on joins

( instead of triggers)

• You cannot remove a row if the view contains the

following:
– Group functions.
– A GROUP BY clause
– The DISTINCT keyword.
– The pseudo column ROWNUM keyword

You cannot modify data in a view if it contains:

• Group functions
• A GROUP BY clause
• The DISTINCT keyword
• The pseudocolumn ROWNUM keyword
• Columns defined by expressions(eg sal * 12 annual_salary)

You cannot add data through a view if the view

includes:
• Group functions
• A GROUP BY clause
• The DISTINCT keyword
• The pseudocolumn ROWNUM keyword
• Columns defined by expressions
• NOT NULL/pk columns in the base tables that are not
Selected by the view

WITH CHECK OPTION Clause/constraint

The WITH CHECK OPTION clause specifies that INSERTs and UPDATEs
performed through the view cannot create rows which the view cannot select.

Create or replace view empvu20

As select *
From emp
Where deptno = 20
With check option constraint empvu20_ck ;

Select * from empvu20;

Update empvu20
Set deptno = 10
Where empno = 3;

Denying DML Operations

• You can ensure that no DML operations occur by adding the WITH READ ONLY option
to your view definition.

64
Create or replace view empvu10
(employee_number, employee_name, job_title)
as select empno, lname, job
from emp
where deptno = 10
with read only;

Delete empvu10
where employee_number = 2;

Inline Views

• An inline view is a subquery with an alias

that you can use within a SQL statement.

• A named subquery in the FROM clause of the main

query is an example of an inline view.

Query:-
Select a.lname, a.sal, a.deptno, b.maxsal
from emp a, (select deptno, max(sal) maxsal
from emp
group by deptno) b
where a.deptno = b.deptno
and a.sal < b.maxsal;

In the following example, the inline view b returns the details of all department numbers
and the maximum salary for each department from the EMPLOYEES table.
The WHERE a.department_id = b.department_id
AND a.salary < b.maxsal clause of the main query displays employee names, salaries,
department numbers, and maximum salaries for all the employees who earn less than
the maximum salary in their department.

Practice :-
Name amt type Date
Vijay 2000 c 1/1/2005
Vijay 1500 d 1/1/2005
Raj 25000 c 1/1/2005
Vijay 300 d 1/1/2005
Raj 1400 c 2/1/2005
Raj 2000 d 2/1/2005

Name credit_amt Debit_amt Date

Vijay 2000 1800 1/1/2005
Raj 25,000 0 1/1/2005
Raj 2000 1400 2/1/2005

select sum(decode(type,cr,amt,0))
creditamt, sum(decode(type,db,amt,0))debitamt,name,date
from tablename

65
group by name,date.

15.Indexes(V.Vimp Topic for Interview)

 To speed up the search operation(faster access).

 All indexes automatically maintained by oracle as & when required.

 No upper limit on no.of indexes per table

 Larger the no.of indexes, slower would be the DML operations.

 Cannot index long and long raw

 Null values not stored in an index

Conditions where an index should be created:

 Select retrieves < 15- 20 % of table data.

 Primary key should always be indexed(column that uniquely identifies the row).

 Common fields in join operations should always be indexed.(Where clause)

Conditions when an index is not invoked even though u’ve created it:

 Absence of where clause in select

 Absence of index key in the where clause.

66
 Select * from emp where ename like ‘aaa’;

Select * from emp where empno!=3;

 !=

 Is null / is not null

e.g.: select * from emp where sal = null;

 Anytime u use only the secondary index key

Composite index (deptno,empno)

Deptno:-primary index key

Empno:- secondary index key.

Select * from emp where empno=7;

Select * from emp where deptno =10 and empno=7;

Select * from emp where deptno =10 ;

(i.e. created a composite index on (empno,email) and in where clause we have only

email column.)

Index :-ename

 function or expression on LHS of relational operator

select * from emp where upper(ename)= ’A’;

Command

SQL> create index indexname on table (column);

Create index empname_ind on emp(fname)

Unique index:-

SQL> create unique index i_emp_empno on emp(empno);

Duplicates not allowed.

Whenever we create a PK or unique constraint a unique index is automatically

created.

Composite Index:-

67
When a index is defined on two or more cols it is called as composite index.

SQL> create index i_emp_empno on emp(deptno, empno);

Here deptno is a primary index key

empno is a secondary index key.

Select * from emp where empno=5;

SQL> drop index indexname;

SQL> drop index i_emp_empno;

 Whenever u drop the table, all indexes for that table are automatically dropped

by oracle.

SQL> select index_name from user_indexes where table_name = ‘EMP’;

 list of all indexes of emp table

SQL>select index_name,column_position ,column_name from

user_ind_columns

where table_name=’EMP’

order by index_name;

 list of indexes & respective column names from emp table

16.Controlling User Access

1. System privileges: Gaining access to the database.

2. Object privileges: Manipulating the content of the

database objects.

System Privileges

• The database administrator has high-level system

privileges for tasks such as:

CREATE USER: Grantee can create other Oracle users (a privilege required
for a DBA role).

DROP USER: Grantee can drop another user.

DROP ANY TABLE:Grantee can drop a table in any schema.

BACKUP ANY TABLE: Grantee can back up a table in any schema with the

68
export utility.

SELECT ANY TABLE :Grantee can query tables, views, or snapshots in any
Schema.

CREATE ANY TABLE: Grantee can create tables in any schema.

Creating Users

The DBA creates users by using the CREATE USER Statement.

CREATE USER RAJ

IDENTIFIED BY tiger;

Grant connect, resource to RAJ;

Connect and Resource:-ROLES(Set of privledges).

Connect role has following priv.:-

1.connect to db using his own password.
2.Alter his own pwd.
3. Select data from public tables.

Resource role is made up of following priv:-

Alter_own,
create_own,
drop_own,
grant_own

Own :-own schema.

The DBA can grant a user specific system privileges also.

GRANT create table, create sequence, create view

TO raj;

GRANT DROP USER TO RAJ;

REVOKE DROP USER FROM RAJ;

Roles

A role is a named group of related privileges that can be granted to the user.

This method makes it easier to maintain (grant and revoke) privileges.

A user can have access to several roles, and several users can be assigned the same
role. Roles are typically created for a database application.
Only a dba,or a user having create role priv can create a role.

Logon to system/excellent:-

CREATE ROLE manager;

Role created.

69
GRANT create table, create view
TO manager;
Grant succeeded.

GRANT manager TO jack,john;

Grant succeeded.

So we can create various roles in an organization like manager, supervisor, operator.

CREATE ROLE V3;

GRANT MANAGER TO V3;

Changing Your Password

• The DBA creates your user account and initializes

Your password.

• You OR DBA can change your password by using the

ALTER USER statement.

ALTER USER RAJ

IDENTIFIED BY sddd;

Object Privileges

An object privilege is a privilege or right to perform a particular action on a specific table

Granting Object Privileges

• Grant query privileges on the EMPLOYEES table.

• Grant privileges to update specific columns to users and roles.

Logon:-scott/tiger

GRANT insert,update,delete,select
ON emp
TO jack, john;

Grant all on emp to jack;

Grant succeeded.

GRANT update (dname, locid)

ON dept
TO jack,manager;

The WITH GRANT OPTION Keyword

70
A privilege that is granted with the WITH GRANT OPTION clause can be passed on to
other users and roles by the grantee.

GRANT select, insert

ON dept
TO john
WITH GRANT OPTION;

Grant succeeded.

Scott—dept

GRANT select
ON scott.dept
TO PUBLIC;

REVOKE select, insert

ON emp
FROM john;

17.Synonyms:-
It is handle to an object.

It is used in multiuser environment.

Scott sql >

Create table emp_sy

(Empno number,
ename varchar2(30));

create following users

Logon to system/manager:---

SYSTEM/excellent

Create user jack Identified by lion;

Grant connect, resource to jack;

Create user john Identified by john;

Grant connect, resource to john;

Jack creates a (private) synonym for scott.emp_sy table

Conn jack/lion

Create synonym semp_sy For scott.emp_sy;

Scott:---- grant all on emp_sy to jack;

71
Jack can access data of scott table.

Now if john wants to access same table

He has to fire:-

Select * from scott.emp_sy

Or
Select * from jack.semp_sy;
(so he will see the data provided he has access to base table emp_sy)

or he has to create his own private synonym

Public synonym:-

DBA can create a public synonym.

(or a user having create public synonym privilege)

Create public synonym spemp_sy

for scott.emp_sy;

grant all on spemp_sy to public;

So all users jack, john etc can access emp_sy table.

DBA can issue:-

(grant create public synonym to jack;)

18.Oracle9i DateTime Functions:-

The default date format of Oracle 8i is DD-MON-YY.

The default date format of Oracle 9i is DD-MON-RR.

To change the date format:-

alter session set nls_date_format=’DD-MON-RR ';

select sysdate from dual;

RR Date Format(New in Oracle 9i)

Insert into emp values(11,'Harish','Gupta','Engineer',24000,10,'19-may-1987');

Select * from emp

where hiredate < to_date('10-jan-90','dd-mon-rr');

/This query shows all emp hired before 1990*/

Select * from emp

where hiredate < to_date('10-jan-90','dd-mon-yy');

72
/This query shows all emp hired before 2090 as yy works with the current century*/

Explantion Of RR date format:-

1.If the current year is in the second half of the century(50-99),and a two-digit year
between ‘00’ and ‘49’ is entered,then this be stored as a ‘next century’ year.

For eg:-‘02’ entered in 1996 will be stored as 2002.

2.If the current year is in the second half of the century(50-99),and a two-digit year
between ‘50’ and ‘99’ is entered,then this be stored as a ‘current century’ year.

For eg:-’97’ entered in 1996 will be stored as 1997.

3. If the current year is in the first half of the century(0-49),and a two-digit year between
‘00’ and ‘49’ is entered,then this be stored as a ‘current century’ year.

For eg:-‘02’ entered in 2007 will be stored as 2002.

4.If the current year is in the first half of the century(0-49),and a two-digit year between
‘50’ and ‘99’ is entered,then this be stored as a ‘prev century’ year.

For eg:-‘87’ entered in 2007 will be stored as 1987.

DateTime Data Types

Timestamp:-

• The timestamp data type is an extension of the

date data type.

• It stores the year, month, and day of the date

Data type, plus hour, minute, and second values as Well as the fractional
second value.

• The timestamp data type is specified as follows:

Timestamp[(fractional_seconds_precision)]
Note:-
The fractional_seconds_precision optionally specifies the number of digits in the
Fractional part of the second datetime field and can be a number in the range 0 to 9. The
default is 6.

Example: -

Create table Emp_time_tbl

(empno number,
start_time Timestamp(4),
end_time timestamp(8));

insert into emp_time_tbl values(1,sysdate,sysdate);

73
TIMESTAMP WITH TIME ZONE Data Type

• Timestamp with time zone is a variant of timestamp that includes a time zone
displacement in its value.

• The time zone displacement is the difference, in Hours and minutes, between local
time and utc. UTC stands for Coordinated Universal Time—formerly Greenwich Mean
Time.

Timestamp[(fractional_seconds_precision)]
With time zone

Create table Emp_time_tbl

(empno number,
start_time Timestamp(4) with time zone,
end_time timestamp(8) with time zone);

insert into emp_time_tbl values(1,sysdate,sysdate);

TIMESTAMP WITH LOCAL TIME ZONE Data Type

• Timestamp with local time zone is another

variant of timestamp that includes a time zone
displacement in its value.

• The time zone displacement is not stored as part

of the column data; oracle returns the date in the
users’ local session time zone.

• Timestamp with local time zone data type is

specified as follows:

Timestamp[(fractional_seconds_precision)]
with local time zone

Create table Emp_time_tbl

(empno number,
start_time Timestamp(4) with local time zone,
end_time timestamp(8) with local time zone);

insert into emp_time_tbl values(1,sysdate,sysdate);

=============================================================

74
 19.Advanced Sub-Queries
Multiple-Column Subqueries

Each row of the main query is compared to values from a multiple-rows and multiple-
columns in the subquery.

Create table emp123

(empno number,
Fname varchar2(40),
Sal number,
Deptno number,
Mgrid number);

Insert into emp123 values (1,’vijay’,7000,10,1);

Insert into emp123 values (2,’Ravi’,4000,10,1);
Insert into emp123 values (3,’vikas’,2000,10,1);
Insert into emp123 values (4,’Raj’,3000,10,2);

Column Comparisons
Column comparisons in a multiple-column subquery
can be:
• Pair wise comparisons
• Nonpairwise comparisons

Pairwise Comparison Subquery

Display the details of the employees who are managed

by the same manager and work in the same department
as the employees with EMPLOYEE_ID 3 or 13

SELECT empno,fname, mgrid, deptno

FROM emp123
WHERE (mgrid, deptno) IN
(SELECT mgrid, deptno
FROM emp123

75
WHERE empno IN (3,13))
AND empno NOT IN (3,13);

Nonpairwise Comparison Subquery

Display the details of the employees who are managed by

the same manager as the employees with EMPLOYEE_ID
2 or 13 and work in the same department as the
employees with EMPLOYEE_ID 3 or 13.

SELECT empno,fname, mgrid, deptno

FROM emp123
WHERE mgrid IN
(SELECT mgrid
FROM emp123
WHERE empno IN (2,3))
AND deptno IN
(SELECT deptno
FROM emp123
WHERE empno IN (3,13))
AND empno NOT IN(3,13,2);

Using a Subquery in the FROM Clause

The example on the slide displays employee last names,

salaries, department numbers, and average salaries for all the employees who
earn more than the average Salary in their department.

SELECT a.lname, a.sal,

a.deptno, b.salavg
FROM emp a, (SELECT deptno,
AVG (sal) salavg
FROM emp
GROUP BY deptno) b
WHERE a.deptno = b.deptno
AND a.sal > b.salavg;

Correlated Subqueries

The Oracle Server performs a correlated subquery when the subquery refers to a
column from a table referred to in the parent query.

A correlated subquery is evaluated once for each row processed by the parent
statement.

Using Correlated Subqueries:-

Display details of those employees who have switched jobs at least twice.

Create table job_history

(empno number,
ename varchar2(30),

76
start_date date,
end_date date);

insert into job_history values(11,’harish’,’23-jun-2002’,’12-jul-2004’);

insert into job_history values(1,'raj','13-aug-2000','20-aug-2002');
insert into job_history values(1,'raj','23-aug-2002','12-aug-2004');

SELECT e.empno,fname,job
FROM emp e
WHERE 2 <= (SELECT COUNT (*)
FROM job_history
WHERE empno = e.empno);

Execution is as follows:-

1. Outer query executes.(but the result is not displayed)

2. For every row of the outer query ,the inner query executes
3. Use the values of the inner query to qualify /disqualify the outer row.

Correlated DELETE
Use a correlated subquery to delete rows in one table
based on rows from another table.

Use a correlated subquery to delete only those rows

from the EMPLOYEES table that also exist in the
JOB_HISTORY table.

DELETE FROM emp E

WHERE empno in
(SELECT empno
FROM job_history
WHERE empno = E.empno);

Correlated UPDATE
• ADD a extra column deptname in the emp table.
• Populate the table by using a correlated
update.

ALTER TABLE emp

ADD(dname VARCHAR2(14));

UPDATE emp e
SET dname =
(SELECT dname
FROM dept d
WHERE e.deptno= d.deptno);

Select empno,deptno,dname from emp;

77
 20.Rollup and Cube Operators
Rollup is an extension to the group by clause.

SELECT deptno, job, SUM(sal)

FROM emp
WHERE deptno < 40
GROUP BY rollup(deptno, job);

• Total salaries for every job ID within a department for those departments whose
department ID is less than 40 are displayed by the GROUP BY clause

The ROLLUP operator displays:

– Total salary for those departments whose department ID is less than 40

– Total salary for all departments whose department ID is less than 40, irrespective of
the job IDs

Cube Operator

CUBE is an extension to the GROUP BY clause.

You can use the CUBE operator to produce crosstabulation

values with a single SELECT statement.

SELECT deptno, job, SUM(sal)

FROM emp
WHERE deptno < 60
GROUP BY cube(deptno, job)

Additionally, the CUBE operator displays the total salary for every job irrespective of
the department.

78
79