SQL(structural query language)

 It is a standard language for storing ,manipulating and retrieving data.

Data
 It is a useful information which is stored in database.
Database
 All the useful information stored in a common place is called as database.
DBMS(database management system)
 It is a collection of programs written in order to manage the database
effectively is called as DBMS.
 Management means: storing data, retrieve data, delete data, update data,
insertion of data.
 By the use of DBMS we can interact with the database.

Notes by Champaka Shastry

Types of DBMS :
1. Hierarchical DBMS
2. Network management system
3. Relation DBMS (R DBMS)

Table structure

Student Table name / Entity

ID Name Year of passing
Column names Table structure
1 Y K Patil 2017

2 B K Thakur 2016 Records

3 P C Patel 2017 Data/ values

Notes by Champaka Shastry

1. To know the tables present in data base we use catalog.
Select * from cat ;
2. To know columns present in particular table we use Describe.
desc table name;
Example queries
1. Write a query to display employee name information from employee
table
2. Write a query to display employee name, salary, hiredate from
employee table
3. Query to display all the information from dept table

Note :
1. SQL is not case sensitive , but data present in SQL are case sensitive.
2. Column name & table name needs to be used as it is.

Notes by Champaka Shastry

Aliasing :
Renaming the column name or the table name while displaying output.
Column Aliasing:
Syntax :
Select column name as column name from table name;
Or
Select column name column name from table name;

Examples :
• Select ename as name ,Job as desg from emp;
• Select ename as “name” from emp;

Note :
For column aliasing, as keyword is optional

Notes by Champaka Shastry

Table Aliasing :
 Renaming table name
 For Table Aliasing as keyword is not required

Syntax
Select column name from table name table name;

Examples :
• Select ename from emp e;

Note :
For table aliasing, as keyword is not necessary.

Notes by Champaka Shastry

Arithmetic operations:
• Addition, Multiplication, Subtraction & division is performed using select
statement
• To hind the behavior aliasing is used

Query writing :
1. Query to display employee name & salary which has been increased by
1000/- for all the employees
Select ename, sal+1000 as isal from emp;
2. Query to display employee name, annual salary , daily salary for all the
employees
Select ename, sal*12 as asal, sal/30 as dsal from emp;
3. Query to display employee name, designation& salary where it has been
decreased by 500/- for all the employees
Select ename, job, sal-500 as dsal from emp;

Notes by Champaka Shastry

Literals:
Usage of data directly in the query is called as “literals”
Number

String

Date

• We can use literals when we want to print same value in each & every row
• Number can be written directly without single quotes
• If we want to use string & date kind of data, it should be used within single quotes

Examples :
1. Select 5 from emp;
2. Select ‘01-Jan-08’ from dept;
3. Select ‘SQL’ from dept;

Notes by Champaka Shastry

Concatenation:
1. Combining two are more column values or literal values is called as
concatenation.
2. Its is achieved by using ||(double vertical pipes)

Example:
select 'my name is' || ename from emp;

1. Smith earns Rs.800

select ename||’ earns Rs '|| sal as salary from emp;
2. My name is smith and holds designation as clerk
select 'my name is '||ename||'and holds designation as '||job from
emp;
3. Smith joined on 01-jan-08.
select ename||' joined on'||hiredate||'.' from emp;

Notes by Champaka Shastry

Order By:
Re- arranging the output as per ascending or descending order while
displaying the output is called as Order By.

Syntax:
Select column name from table name order by column name asc;
Select column name from table name order by column name desc;
Note:
By default output will be displayed in ascending order.
It is optional that we can mention “asc” keyword in the query.

Multiple column Order By:

 select job,deptno from emp order by job, deptno desc;
 select job as design, deptno from emp order by design;
 select job, deptno,hiredate from emp order by 2;

Notes by Champaka Shastry

Assignment:

1.Query to display employee name, annual salary, daily salary for all the
employees and rearrange the o/p as per descending order from annual salary.
select ename,sal*12 as asal,sal/30 dsal from emp order by asal desc;
2. Query to display all the information of an employee and rearrange the o/p of
hiredate as per their seniority.
select * from emp order by hiredate asc;
Note:
Execution order of order by
select 2
from 1
order by 3

Notes by Champaka Shastry

Where clause:
 To filter out rows we use “Where clause”. Whenever we have the condition
the particular condition needs to be specified in the Where clause.
 Where condition gets executed for each and every row if the condition
satisfies selects the rows or else rejects the rows.
 Syntax:
select column name from table name
where condition;
Examples:
1. Write a query to display ADAMS information.
Select * from emp where ename='ADAMS';
2.Write a query to display emp name,hiredate,sal information who has joined on
01-may-81.
select ename,hiredate,sal from emp where hiredate='01-may-81';

Notes by Champaka Shastry

3. Query to display employee name, annual salary, daily salary where employees
are earning annual salary as 36000.
select ename,sal*12 as asal, sal/30 as dsal from emp where sal*12=36000;
4. Write a query to display odd salary of employees.
select * from emp where mod(sal,2)=1;
5 Write a query to display even salary of employees.
select * from emp where mod(sal,2)=0;

Execution flow:
Select 3

From 1
Where 2
Order by 4

Notes by Champaka Shastry

 Relational operator or comparison operator:
>(greater), >=(greater than equal to), < (less than), <=(less than equal to),
=(equal to), !=(not equal to)
Note: All relational operator should be only used in where clause.

Examples:
1. Query to display all the information of an employee whose salary is more
than 2000.
select * from emp where sal> 2000;
2. Query to display all the information of an employee who is earning a salary
below 1500.
select * from emp where sal<1500;
3. Query to display all the information of an employee who is earning a salary of
Rs.3000.
select * from emp where sal=3000;

Notes by Champaka Shastry

 Logical operators:
 And operator: When both the conditions needs to be satisfied it is and
operator.
 OR operator: When any one of the condition needs to be satisfied we use OR
operator.
 Not operator: It is an opposite operator i.e. neither this nor that.
Examples:
1. Write a query to display all the employee information who holds the
designation as clerck, they should belong to a deptno 10.
Select * from emp where job=‘CLERK’ and deptno=10;
2. Write a query to display all the employee information who belongs to deptno
10,20.
select * from emp where deptno=10 or deptno=20;
3. Query to display all the inforation of employees who holds the designation as
clerk and they should belong to a deptno 10,30.
select * from emp where job='CLERK' AND (deptno=10 or deptno=30);

Notes by Champaka Shastry

 4. Write a query to display the employee information who are not working as a
salesman.
select * from emp where job!='SALESMAN';
OR
select * from emp where not job='SALESMAN';
5. Write a query to display employee name, annual salary, daily salary, deptno
where employee daily salary should be greater than or equals to 100 and they
should belong to deptno 20 and re-arrange O/P as per daily salary in descending
order.
select ename,sal*12 asal, sal/30 dsal, deptno from emp
where sal/30>=100 and deptno=20
order by dsal desc;
6 Write a query to display employee information who as joined in the year 81.
select * from emp where hiredate >='01-jan-81' and hiredate<='31-dec-81';

Notes by Champaka Shastry

Special operator(IN)
We can use IN operator when the given value is present in the same column.
If a query contains OR operator it can be replaced with IN operator.

Example:
1. Write a query to display managers and analyst information who belongs to
deptno 30.
select * from emp where job in('MANAGER','ANALYST') and deptno=30;
Between operator:
 It is used when the given values are within the range.

1. Write a query to display employees information who earns a salary in the

range of 2000 to 3000.
select * from emp where sal between 2000 and 3000;

Notes by Champaka Shastry

Like operator
It is used for pattern matching.
% specifies or replace zero characters to N characters.
_ specifies or replace exactly one character.

Examples:
1. Write a query to display the employee’s name whose name is starting with
letter “S”.
select * from emp where ename like 'S%';
2. Query to display the employee name and designation whose designation has
man word .
select ename, job from emp where job like '%MAN%‘;
3. Query to display all the information of employee whose salary last but one
digit is 5.
select * from emp where sal like '%5_';

Notes by Champaka Shastry

Null:
 It is an empty cell or blank space.
 Null is not equal to another value or null is not equal to another null also.
 To achieve null in SQL we have to use special operator called as “IS”.

Examples:
1. Write a query to display all the information of an employee whose
commission is null.
select * from emp where comm is null;
2. Write a query to display all the information of an employee whose commission
is not null.
select * from emp where comm is not null;

Notes by Champaka Shastry

Functions
 Single row functions: will take multiple input and provide corresponding
output.
Case manipulation functions: lower(), upper(), initial capital()Initcap()

Examples:

1.Write a query to display ename in upper case.

select upper(ename) from emp;
2. Write a query to display job in lower case.
select lower(ename) from emp;
3. Write a query to display job , ename in initial capitals.
select initcap(job),initcap(ename) from emp;

Notes by Champaka Shastry

1. Concat() : To combine column or literal values.

Syntax: Select concat(arg1,arg2) from table name;

arg1---column/literal values
arg2-- column/literal values

Examples:

1. Smith belongs to deptnumber 20.

select concat(concat(ename,'belongs to deptnumber'),deptno) from emp;
2. My name is smith and he earns Rs.800
select concat(concat(concat('My name is', ename),'and earns Rs'),sal)
from emp;

Notes by Champaka Shastry

2. Replace ()
Used to replace a particular character or a string from the given string.
Syntax: Select replace(arg1,arg2,arg3) from table name;
arg1col/literal value
arg2mention character which needs to be removed
arg3mention new character which needs to be replaced
Examples:
1. select replace('java','j','m') from dual;
2. select replace('ManualEngineer', 'Manual', 'automation') from dual;
3. select replace('ManualEngineer','Manual') from dual;

Note: Maximum 3 arguments and minimum 2 arguments can be passed.

Notes by Champaka Shastry

3. Length()
It finds the number of characters present in a given text or field.

Examples:
1. Write a query to display the length of every employee name.
select length(ename) from emp;
2. Write a query to display employee name who’s name is having exactly 6
characters.
select ename from emp where length(ename)=6;
3. Write a query to count number of “L” present in ename.
select length(ename)-length(replace(ename , 'L')) from emp;

Notes by Champaka Shastry

4. DISTINCT()
 It is used to get unique information from a column.
 It can be used in select statement not in where clause.
 Distinct can be used only once in the query.
 Distinct should be written first in the query.

Examples:
1. Write a query to display unique job from employee table.
select distinct(job) from emp;
2. Write a query to display distinct department number from employee.
select distinct(deptno) from emp;
Note: Below query is not possible.
select distinct(job),distinct(deptno) from emp;

Notes by Champaka Shastry

Multi -row functions

 MAX() : Returns highest value present in a particular column

 MIN(): Returns minimum value present in a particular column
 AVG(): Returns sum of the columns divided by number of rows
 SUM(): Returns sum of all rows
Note:
 For min() and max() we can pass numeric, string and date kind of data. Where
as for sum() and avg() function we have to pass only numeric data.
 Multi -row functions can be used only in select statement.
Examples:
1. select max(sal) from emp;
2. select min(ename) from emp;
3. select avg(sal) from emp;
4. select sum(mgr) from emp;

Notes by Champaka Shastry

Count :
Counts number of rows present in the column or table.
Examples:
1. Query to count number of employees present in department 30
select count(*) from emp where deptno=30;
2. Query to display count of distinct job of employee.
select count(distinct job) from emp;
Important Assignment:
1. Query to display total salary for all the “SALESMAN”.
2. Query to display average salary for all the “CLERK”.
3. Query to display least salary among “MANAGERS”.

Note:
Only for count function we can use * . Example: count(*)

Notes by Champaka Shastry

Group By:
 Group by is also called as multi-row function or aggregate function.
 It is a clause not a function.
 It divides the table into multiple groups and for each group produces output.

example:
select max(sal) ,deptno from emp group by deptno;

Restrictions for group by clause:

1. The columns which are present in select statement, same set of columns
should be present in group by clause.
2. The columns which are present in group by clause it’s not a mandatory to be
in select list.

Notes by Champaka Shastry

1. Write a query to display least salary for all the managers in each department.
select min(sal),deptno from emp where job='MANAGER' group by deptno;
2. Write a query to count number of employee present in each job.
select count(*), job from emp group by job;
(OR)
select count(ename), job from emp group by job;
3. Write a query to display min salary, max salary as per each designation.
select min(sal),max(sal) ,job from emp group by job;

Order of execution:
Select 3

From 1

Group by 2

Notes by Champaka Shastry

Having clause
 Having clause will get executed for each and every group if the condition
satisfies select group or else reject the group.
 When we need to provide condition for groups we use having.
 We cant use having without using group by.

Order of execution:
Select 5

From 1

Where 2

Group by 3
Having 4
Order by 6

Notes by Champaka Shastry

Examples:
1. Write a query to display sum of salary for each job select only those job
information where the sum of salary exceeds 6000;
select sum(sal),job
from emp
group by job
having sum(sal)>6000;
2. Write a query to count number of employees present in each deptno and
select only those deptno where the number of employees are more than 3.
select count(*), deptno
from emp
group by deptno
having count(deptno)>3;

Notes by Champaka Shastry

Sub queries:
 Query inside another query is called as sub queries.
 When ever we have indirect condition the query needs to be resolved using
sub queries.
1. Single row sub queries: Inner query will provide only a single output.
We can use one on one operator like >, <, >=, <=, =
2. Multi row sub queries: The inner query will produce multiple output.
We can use one on one operator like IN, ANY, ALL.

Note:
 Common column should exist between the table to write sub queries.
 When indirect condition is in same table then we can refer same column or
dependence column information.
 In inner query we cant use “*”

Notes by Champaka Shastry

Examples:
1. Write a query to display employee name who belongs to New York location.
select ename from emp
where deptno=(select deptno from dept where loc='NEW YORK');
2. Write a query to display all the information of an employee whose
designation is similar to employee number 7844.
select * from emp
where job=(select job from emp where empno=7844);
3. Write a query to display employee’s information whose salary is greater than
the average salary of deptno 10.
select * from emp
where sal>(select avg(sal) from emp where deptno=10);
4. Write a query to display turner’s location information.
select loc from dept
where deptno=(select deptno from emp where ename='TURNER');

Notes by Champaka Shastry

5. Write a query to display ename, sal, job whose salary is greater than Martin
salary but designation is similar to Blake.
select ename, job, sal from emp
where sal>(select sal from emp where ename='MARTIN')
and job=(select job from emp where ename='BLAKE');
6. Write a query to display employee’s information who’s reporting to JOHNS
manager.
select * from emp
where mgr=(select mgr from emp where ename='JONES');
7. Query to display employee’s information who are reporting to Jones.
select * from emp
where mgr=(select empno from emp where ename='JONES');
8. Query to display the employee’s name who gets highest salary.
select ename from emp
where sal=(select max(sal) from emp);

Notes by Champaka Shastry

Joins
 It is used to merge or join one or more table.
 We can display the information of both the tables.

Type of joins:
1. Cross or Cartesian joins
2. Equi joins
3. Self joins Inner joins Fetches only matched records

4. Non –Equi joins

5. Left outer joins Fetches matched and
Outer joins
6. Right outer joins unmatched records

7. Full outer joins

Notes by Champaka Shastry

1. Cross or Cartesian joins
 It is going to pick each and every value from first table and combine with
each and every value of second table.
 Here we will get both matched and unmatched records.
 Example: emp table consists of 14 rows and dept table consists of 4 rows
then output will be 56 rows.
Examples:
1. Query to display employee name and employee department name
information of all the employees.
select ename,dname from emp,dept;
2. Query to display designation, location information of all employees.
select job,loc from emp,dept;
3. Query to display manager, department number information from department
table for all employees.
select mgr, dept.deptno from emp,dept;

Notes by Champaka Shastry

2. Equi joins
 In equi joins we will be able to get only the matched records
 In equi joins we have to provide the join conditions, in conditions we will be
able to use equality operator
 It will eliminate the unmatched records. Hence this is the most frequently
used joins in real time industry
Examples:
1. Query to display emp name and dept name for all the employees.
select ename,dname from emp,dept
where emp.deptno=dept.deptno;
2. Query to display employee name, employee deptno,dname,sal for all the
employees whose salary is greater than 2000.
select ename,emp.deptno,dname,sal
from emp,dept
where emp.deptno=dept.deptno and sal>2000;

Notes by Champaka Shastry

3. Self joins
 Joining table to itself or by its own is called as self joins.
 When dependence is there then we will go with self join.
 In self join table aliasing is mandatory.
Examples:
1. Query to display employee name and employee manager name.
select e.ename,m.ename
from emp e,emp m
where e.mgr=m.empno;
2. Query to display employee name who are earning same salary.
select e.ename, se.ename
from emp e,emp se
where e.sal=se.sal and
e.ename!=se.ename;

Notes by Champaka Shastry

4 Non –Equi joins
 Without using equality of the data rather using some other operators those
joins are called as non equi joins.
 For salgrade and emp table we can build relationship through sal, losal,hisal.
Examples:
1. Query to display grade 2 and grade 3 employee’s name and grade
information.
select ename, grade from emp,salgrade
where sal between losal and hisal
and grade in(2,3);
2. Query to display MANAGER and ANALYST information who belongs to grade
3,4.
select * from emp, salgrade where sal between losal and hisal
and job in ('MANAGER', 'ANALYST')
AND grade in(3,4);

Notes by Champaka Shastry

Outer joins
 In outer joins we can get matched records pulse left out records from the
tables.
Inner join Left outer join A B
A B

Right outer join Full outer join

A B
A B

Notes by Champaka Shastry

Right outer joins
Write a query to display matched records from emp and dept table and left out
records from dept table.
select * from emp,dept
where emp.deptno(+)=dept.deptno;
Left outer join
Write a query to display matched records from emp and dept table and left out
records from emp table.
select * from emp,dept
where emp.deptno=dept.deptno(+);
Full outer join
Write a query to display matched records from both the table and left out
records of both the table.
select * from emp,dept
where emp.deptno(+)=dept.deptno(+);

Notes by Champaka Shastry

Note:
 In oracle full outer join is not achievable because we cannot provide (+) on
both the sides.
 Hence we have to use ANSI standards (American National Standards Of
Institute)

Syntax:
Select column name from table name JOIN table name on condition;

Full outer join using ANSI

select * from emp full outer join dept
on emp.deptno=dept.deptno;

Notes by Champaka Shastry

Examples of joins using ANSI standards

1. select * from emp cross join dept;

2. select ename,dname from emp join dept
on emp.deptno=dept.deptno;
3. select ename, grade from emp join salgrade
on sal between losal and hisal
and grade in(2,3);
4. select e.ename,m.ename
from emp e join emp m
on e.mgr=m.empno;
5. select * from emp right outer join dept
on emp.deptno=dept.deptno;
6. select * from emp left outer join dept
on emp.deptno=dept.deptno;

Notes by Champaka Shastry

Creation of tables
Before creating tables we should be knowing two concepts.
1. Data type
 int –stores number kind of data.
 Character- stores alpha numeric kind of data and it is fixed in nature.
 Varchar-Stores alpha numeric kind of data and it is variable in nature.
2. Constraints
1. unique: It will block the duplicate values.
2. Not null: It will block the null values.
3. Primary key: It is a unique identifier column in a table. It is a combination of
unique constraints and not null constraints.
4. Foreign key(Refferences): It is referential integrity constraints. It is used to
build relationship between tables. Duplicates are allowed.
Note: In a table we can have only one primary key, but we can have multiple
foreign keys. 90% numeric value will be there in the primary key.

Notes by Champaka Shastry

5. Candidate key: The columns which are eligible to become primary key is
called candidate key.
6. Check constraints: For a particular column when we have to put extra
validation (or)condition as per the customer requirement it can be handled in
check constraints.

DDL(Data definition language)

 Using DDL commands we can create new tables to the database.
 For an existing table we can make few changes.
 We can remove the tables present in the data base.
 All DDL commands are permanent changes to the data base.
 There are multiple commands under DDL: Create, Rename, Alter, Drop

Notes by Champaka Shastry

1. Create
Used to create new table.
Syntax:
Create table table name (
Column name1 datatype, column name2 datatype, column name3 datatype….);
Example:
Create table myclass(
Id int, name varchar(30), city varchar(20));
Creating table with constraints
1. Create table myclass(
id int unique, name varchar(30) not null, city varchar(20));

2. create table emp40(empno int primary key, deptno int,

constraint myconst foreign key(deptno) references dept(deptno));
constraint name it can be anything.

Notes by Champaka Shastry

Create table with check constraints:
Syntax:
create table table name(column1 datatype(size)………,
constraint constraint _name check(column name condition));
Example:
create table chandu1(empno int, sal int, constraint check_number
check(empno between 1 and 10000));

2. Alter – add
To add columns in the existing table.
Syntax:
Alter table table name
add(column name datatype (size));

Example: alter table myclas add(address varchar(30));

Notes by Champaka Shastry

Alter-Modify
Syntax:
Alter table table name modify(existing column name data type (size));
Example
alter table myclass modify(id varchar(30));
Alter-Rename
Syntax
Alter table table name rename column existing column to new column;
Example
alter table myclass rename column id to identefication_num;
Alter-drop
Syntax
Alter table table name drop column column name;
Example:
alter table myclass drop column IDENTEFICATION_NUM;

Notes by Champaka Shastry

3. Rename
To rename table already present.
Syntax:
Rename existing table name to new table name;
Example
Rename myclass to my_class;
4. Drop
It will drop whole table along with the records.
Syntax
Drop table table name;

Note: Truncate is used to drop only the records and table structure remains the
same. Truncated information cannot be retrieved back.
Syntax:
Truncate table table name;

Notes by Champaka Shastry

DML(Data manipulation language)
 Using this commands for any existing table we can start inserting the values
row wise,we can modify or change the values present in a table and also we
can remove the values present in a table.
 DML commands are temporary change.
 We can insert, update and delete using DML commands.
1. Insert Syntax
Insert into table name(column name1,column name2…)values(v1,v2,v3…);
Example:
insert into myclass(name,city,address) values('anu','pune','chikali');

To insert Multiple rows:

Insert all into myclass values('amith','pune','moshi')into myclass
values('rama','mumbai','moshi’)……………..
select * from dual;

Notes by Champaka Shastry

2. Update:
Syntax
Update table name set column name=new value
Where condition;
Example:
update emp set job='SPIDERMAN'
where empno=7902;
3. Delete:
Syntax
Delete table name where condition;
Example:
Delete emp where ename=‘ALLEN’;
In the above example entire one row will be deleted.
Note: create table tablename as select * from emp;
Used to copy the data from one table to another table.

Notes by Champaka Shastry

TCL(Transaction control language)
1. Commit :
 When we perform the DML commands data are not permanently changed
in the data base to make it as a permanent change we have to go for
commit.
Example:
Create table emp42 as select * from emp; (DDL)

delete emp42 where deptno=20; (DML)

commit; (TCL)

2. Roll Back:
 It will check for the previous commit if it is not found directly goes to the
auto commit.

Notes by Champaka Shastry

Example:
update emp42
set job='SALESMAN' where empno=7782;

ROLL BACK;

3. Flashback:
 Using flash back we can get back the dropped tables from the recycle bin.
Syntax:
flashback table table name to before drop;
Example:
drop table emp42;

flashback table emp42 to before drop;

Notes by Champaka Shastry

4. Recycle bin:
 It is used to see the droped tables present in recycle bin.
Syntax:
show recyclebin;
5. Purge:
 To delete table permanently from the recycle bin we use purge.
Syntax:
Purge recyclebin;
Example:
drop table MYCLASS;  (drop few more tables)

show recyclebin;

Purge recyclebin;

Notes by Champaka Shastry

DCL(Data control language)
1. Grant:
 Used to grant permission to the HR to access tables present in scott account.
Syntax:
grant select on emp to hr; (use it in scott account)
2. Revoke:
 Used to take back the permissions given to the HR.
Syntax:
revoke select on emp from hr; (use it in scott account)

Note:
 After granting permissions to the HR he can access the tables using the
below command.
select * from scott emp; (use it in HR account)

Notes by Champaka Shastry

Rownum:
 It is inbuilt column .
 It is numeric in nature.
 It is a pseudo column: A Pseudo column is a "column" that fetch's a value
when selected, but it is not an actual column of the table.
 By default rownum will be 1 for each and every row.
Examples:
1. Query to display first record of the emp table.
select * from emp where rownum=1;
2. Query to display 3rd record of employee table.
select * from emp where rownum=3;
 The above query should be resolved using co-related sub query concept.
 If outer query has been accessed by inner query then those types of queries
are called co-related sub queries.

Notes by Champaka Shastry

 select * from (select rownum a, ename,job from emp)
where a=3;
3. Query to display last record of the employee table.
select * from (select rownum a,ename,sal,job,hiredate from emp)
where a=(select count(*) from emp);
4. Query to display 2nd,5th record from emp table.
select * from (select rownum b, ename,job from emp)
where b in(2,5);
5. Query to display last five records from emp table.
select * from (select rownum a, ename, sal, job from emp)
where a>9;
6. Query to display third highest salary.
select min(sal) from(select distinct(sal) from emp
order by sal desc)
where rownum<=3;

Notes by Champaka Shastry

Assignments:
1. Query to display last but one record of a employee table.
2. Query to display 5th highest salary from emp table.
3. Query to display second lowest salary.

Normalization:
 It is a set of rules, its not mandatory to follow the normalization rules, but if
we follow the normalization rules it is a better practice.
 Normalization rules are followed while creating the table to the database.

Use of normalization rules:

 We eliminate unwanted data storage from the database.
 All the tables stored in the database will be organized.
 If we are not following normalization rules we will have few
“ANOMALIES”(issues or problems).

Notes by Champaka Shastry

 ANOMALIES are: update anomalies, insert anomalies, delete anomalies.
 The solution for all the anomalies are normalization forms.
 Normalization form are step by step procedure, if first normalization from is
satisfied then it is eligible to go for second normal forms.

1st normalization form:

 Multilevel value should be changed to atomic values.(single values)
 Remove the similar columns info from the tables.
 In a table we should have primary key column so that each and every row
values are identified uniquely.

2nd normalization form:

 Table should satisfies the first normal form.
 Divide the table and relate them through foreign key.
 All non prime attribute should be completely dependent on prime attribute.

Notes by Champaka Shastry

3rd normalization form:
 Table should satisfies the second normalization form.
 There should not be any transitive dependency (one non prime attribute
should not be dependent on another none-prime attribute ).

Notes by Champaka Shastry