Typed SQL Notes

SQL ( STRUCTURED QUERY LANGUAGE )

DATABASE
What is DATA ?
Properties or Attributes
I am creating an account for myself :
First name : Rohan
Surname : Singh
Phone number : 9876543210
Password : Rohan@123
Dob : 14-MAY-199X
Gender : MALE
Example : Water Bottle
DATABASE :
"Data is a raw-fact which describes the attributes of an Entity ".
Height : 20cms
Color : blue
Capacity : 500ml
Entity
Attributes
Attributes
Brand : Dell
RAM : 8gb
Touch : no
Brand : Apple
RAM : 16gb
Touch : yes
Laptop
Laptop
Contacts
Name : Dinga
Phone : 108
DOB: 14-feb-95
DAY 1
Thursday, July 16, 2020
9:35 AM

Online DAY 1 Page 1

STUDENT : YOU HAVE TO PRESENT

PPT
Dinga
CREATE / INSERT
▪
READ / RETRIEVE
▪
UPDATE / MODIFY
▪
DELETE / DROP
▪
The basic operations that can be performed on a database are
➢
These operations are referred as "CRUD" Operations .
➢
DATABASE MANAGEMENT SYSTEM ( DBMS ) :
"It is a software which is used to maintain and manage
The database "
Security and authorization are the two important features that
➢
DBMS provides .
QUERY
LANGUAGE
We use query language to communicate or interact with DBMS
➢
DBMS stores the data in the form of files .
➢
RELATIONAL DATABASE MANAGEMENT SYSTEM
( RDBMS ):
"It is a type of DBMS software in which we store the data
PPT
Database
PEN DRIVE
HARD DISK
"Database is a place or a medium in which we store the data in a
Systematic and organized manner "
Database
Data
Software
DBMS
A
C
D
E
B

Online DAY 1 Page 2

In the form of Tables ( rows & columns ) ".

Example :
Notes link : [Link]/roSQLQCDM34
Mail ID : [Link]@[Link]
Instagram : ro_sql_helpmate
STRUCTURED
QUERY
LANGUAGE
We use SQL to communicate or interact with RDBMS
•
RDBMS stores the data in the form of Tables.
•
Database
Data
Software
RDBMS
Names
A
B
C
D
E

Online DAY 1 Page 3

RELATIONAL MODEL :
Relational Model was designed by E.F CODD .
In Relational Model we can store the data in the from of tables .
Any DBMS which follows Relational Model becomes RDBMS .
DBMS. Relational Model RDBMS
Any DBMS which follows rules of EF CODD becomes RDBMS .
TABLE : "It is a logical organization of data which consists of
Columns &Rows .
Columns / Attribute / Fields

Example :
Employee :
EID
ENAME SALARY
1
SMITH
1000
2
ALLEN
1500
3
CLARK
2000
RULES OF E.F CODD :
Rows
/
Records
/
Tuples
Cell
Emp ( Entity )
Eid
-
Ename
-
Salary
-
DAY 2
Friday, 17 July 2020
8:59 AM

The data entered into a cell must always be a single valued data .
1.
Example :
EID
ENAME
PHONE_NO
1
SMITH
101
2
ALLEN
102 , 202
3
CLARK
103
EID
ENAME
PHONE_NO
ALTERNATE NO
1
SMITH
101
2
ALLEN
102
202
3
CLARK
103
According to E.F CODD we can store the data in Multiple Tables ,
2.
If needed we can establish a connection between the tables with the
Help of Key Attribute .
In RDBMS we store everything in the from of tables including
3.
Metadata .
Example : Metadata : The details about a data is knows as Metadata.
EID
ENAME
PHOTO
1
SMITH
2
ALLEN
3
CLARK
MetaTable
Image name
size
Format Resolution
Mypic
127
jpeg
400 x 600
PHOTO
DATA
Image Name : Mypic
size : 127kb
resolution : 400 x 600
format : jpeg

The data entered into the table can be validated in 2 steps .

By assigning Datatypes .
i.
By assigning Constraints .
ii.
4.
Datatypes are mandatory , whereas Constraints are Optional .
DATATYPES :
It is used to specify or determine the type of data that will be stored
In a particular memory location .
Datatypes in SQL :
CHAR
1.
VARCHAR / VARCHAR2
2.
DATE
3.
NUMBER
4.
LARGE OBJECTS
Character Large Object .
i.
Binary Large Object .
ii.
5.
NOTE : SQL is not a Case Sensitive Language .
CHAR : In character datatype we can store 'A-Z' , 'a-z' , '0-9'
And Special Characters( $ , & , @ , ! … ) .
Characters must always be enclosed within single quotes ' '.
Ø
Whenever we use char datatype we must mention size
Ø
Size : it is used to specify number of characters it can store .
The maximum number of characters it can store is
2000ch.
§
Ø
Char follows fixed length memory allocation .
Ø
1.
Example : CHAR ( 8 )
Q
S
P
Used Memory Unused Memory ( memory wastage )
Syntax: CHAR ( SIZE )

y
y(
y
g)
Example : VARCHAR ( 8 )
Example :
STUDENT
USN
SNAME
ADDRESS
PAN_NO
CHAR(4) VARCHAR(10) VARCHAR(10) CHAR(10)
QSP1
DINGA
BANGALORE
ABC123XYZ1
QSP2
DINGI
MYSORE
ABC123XYZ2
ASSIGNMENT :
DIFFERENTIATE BETWEEN CHAR & VARCHAR
1.
ASCII : [ American Standard Code For Information Interchange ]
VARCHAR : In varchar datatype we can store 'A-Z' , 'a-z' , '0-9'
And Special Characters( $ , & , @ , ! … ) .
Characters must always be enclosed within single quotes ' '.
○
Whenever we use char datatype we must mention size
○
Size : it is used to specify number of characters it can store .
The maximum number of characters it can store is
2000ch.
§
○
VarChar follows variable length memory allocation .
○
Syntax: VARCHAR ( SIZE )
2.
Q
S
P
Used Memory Unused Memory ( free memory )
NOTE : VARCHAR2 : it is an updated version of varchar where in
We can store up to 4000Ch.
Syntax: VARCHAR2( SIZE )

[
g]
'A'
65
'Z'
90
'a'
97
'z'
122
Basavanagudi Bangalore Contact Details :
Counselors Number :
1
9845687781
2
9686700900
HR Number :
1
9686700800 Freshers ( 2018 , 2019 , 2020 )
2
9663011671 Experienced ( Below 2018 )
3
7337885026 What's App Contact
Social Media :
Facebook Qspiders Basavanagudi
Instagram Qspiders_basavanagudi
YouTube
Qspiders Basavanagudi
My Contact :
Notes
[Link]/roSQLQCDM34
Software
[Link]/roSoftWIN
[Link]/roSoftMAC
Mail
[Link]@[Link]
Instagram Ro_sql_helpmate

NUMBER : It is used to store numeric values .

3.
SYNTAX: NUMBER ( Precision , [ Scale ] )
[ ] - Not Mandatory .
Precision : it is used to determine the number of digits used
To store integer value .
Scale : it is used to determine the number of digits used to store
Is zero ( 0 ) .
Scale is not mandatory , and the default value of scale
➢
Decimal ( floating ) value within the precision .
Example : Number ( 3 )
+/- 999
Example : Number ( 5 , 0 ) +/- 99999
Example : Number ( 5 , 2 ) +/- 999.99
Example : Number ( 7 , 3 ) +/- 9999.999
Example : Number ( 4 , 4 ) +/- .9999
Example : Number ( 5 , 4 ) +/- 9.9999
Example : Number ( 3 , 6 ) +/- .000999
Example : Number ( 5 , 8 ) +/- .00099999
Example : Number ( 2 , 7 ) +/- .0000099
EID
PHONE_NO SALARY
Number( 3 ) Number ( 10 ) Number ( 7 , 2 )
101
9876543210
9000.85
DATE : it is used to store dates in a particular format .
4.

It used Oracle specified Format .

'DD-MON-YY'
OR
'DD-MON-YYYY'
'22-JUN-20'
'22-JUN-2020'
SYNTAX: DATE
Example :
DOB
Hiredate
Anniversary
Date
Date
Date
DAY 3
Monday, July 20, 2020
9:40 AM

New Section 1 Page 1

'01-JAN-1945' '20-JUN-20' '15-APR-2008'

It is used to store characters up to 4 GB of size .
Character large object ( CLOB ) :
1.
Binary large object ( BLOB ) :
2.
It is used to store binary values of images , mp3 , mp4
Documents etc …. Up to 4GB of size .
LARGE OBJECTS
5.
NOTE :
FOR WINDOWS :
Name : SQL*Plus
To download : [Link]/roSoftWIN
Software : Oracle : Oracle 10g - Version
If getting errors !!!
Contact me on : [Link]@[Link]
Or
Send screenshot to INSTAGRAM : ro_sql_helpmate
New Section 1 Page 2

It is a rule given to a column for validation .

CONSTRAINTS :
Types of Constraints :
UNIQUE
1.
NOT NULL
2.
CHECK
3.
PRIMARY KEY
4.
FOREIGN KEY .
5.
UNIQUE : "It is used to avoid duplicate values into the column ".
1.
NOT NULL : "It is used to avoid Null ".
2.
If the condition is satisfied then the value is accepted else
Rejected ".
CHECK : "It is an extra validation with a condition
3.
Uniquely from the table " .
Characteristics of Primary key :
We can have only 1 PK in a table
➢
PK cannot accept duplicate / repeated values .
➢
PK cannot accept Null
➢
PK is always a combination of Unique and Not Null Constraint.
➢
PRIMARY KEY : "It is a constraint which is used to identify a record
4.
The tables "
Characteristics of Foreign key :
We can have only Multiple FK in a table
➢
FK can accept duplicate / repeated values .
➢
FK can accept Null
➢
FK is not a combination of Unique and Not Null Constraint.
➢
For an Attribute ( column ) to become a FK ,it is mandatory
➢
That it must be a PK in its own table .
FOREIGN KEY : "It is used to establish a connection between the
5.
Example :
EMP
Primary key
Check ( Salary >
Check
DAY 4
Tuesday, July 21, 2020
9:34 AM

New Section 1 Page 1

0 )
( length(phone) = 10 )
Not Null
Not Null
Not Null
Not Null
Not Null
Unique
Unique
EID
NAME
SALARY
DOJ
PHONE
Number(2)
Varchar(10) Number(7,2)
Date
Number(10)
1
A
10000
'20-JUN-20'
9876543210
2
B
20000
'20-JUN-19'
9876543222
3
C
35000
'01-JAN-18'
9876543333
4
D
50000
'01-OCT-19' 9876511111
Example for Foreign Key :
Emp
EID
NAME
SALARY DNO
CID
1
A
10000
20
2
2
B
20000
10
3
3
C
35000
20
1
4
D
50000
10
2
Child Table
Customer
CID
CNAME CNO
1
X
1001
2
Y
2002
3
Z
3003
Parent Table
ASSIGNMENT :
Differentiate between Primary key and Foreign key .
1.
PRIMARY KEY
FOREIGN KEY
It is used to identify a records
Uniquely from the table.
It is used to establish a connection
Between the tables
It cannot accept Null
It can accept Null
It cannot accept duplicate values
It can accept duplicate values
It is always a combination of
Not Null and Unique constraint
It is not a combination of
Not Null and Unique constraint
We can have only 1 PK in a table We can have Multiple FK in a table
Null Is a keyword which is used to represent Nothing / Empty Cell.
Characteristics of Null :
Null doesn’t represent 0 or Space .
➢
Any operations performed on a Null will result in Null itself
➢
NOTE : NULL
Dept
DNO
DNAME LOC
10
D1
L1
20
D2
L2
Parent Table
FK
FK

New Section 1 Page 2

Null doesn’t Occupy any Memory .

➢
We cannot Equate Null .
➢

New Section 1 Page 3

OVERVIEW OF SQL STATEMENTS :
DATA DEFINITION LANGUAGE ( DDL )
1.
DATA MANIPULATION LANGUAGE ( DML )
2.
TRANSCATION CONTROL LANGUAGE ( TCL )
3.
DATA CONTROL LANGUAGE ( DCL )
4.
DATA QUERY LANGUAGE ( DQL )
5.
" DQL is used to retrieve the data from the database " .
It had 4 statements :
SELECT
1.
PROJECTION
2.
SELECTION
3.
JOIN
4.
DATA QUERY LANGUAGE ( DQL _) :
SELECT : "It is used to retrieve the data from the table and display it.
1.
In projection all the records / values present in a particular column
are by default selected .
➢
PROJECTION : "It is a process of retrieving the data by selecting only
the columns is known as Projection " .
2.
SELECTION : "It is a process of retrieving the data by selecting both
the columns and rows is known as Selection " .
3.
JOIN :"It is a process of retrieving the data from Multiple tables
simultaneously is known as Join " .
4.
PROJECTION
"It is a process of retrieving the data by selecting only the columns is
known as Projection " .
➢
In projection all the records / values present in a particular column are
by default selected .
➢
SYNTAX :
SELECT * / [DISTINCT] Column_Name / Expression [ALIAS]
FROM Table_Name ;
FROM Clause
1.
SELECT Clause
2.
ORDER OF EXECUTION
Example : Write a query to display names of all the students .
DATABASE
DAY 5
Wednesday, July 22, 2020
9:02 AM

New Section 1 Page 1

Example : Write a query to display names of all the students .

DATABASE
Student
SID
SNAME BRANCH PER
1
A
ECE
60
2
B
CSE
75
3
C
ME
50
4
D
ECE
80
5
C
CSE
75
6
E
CIVIL
95
NOTE :
FROM Clause starts the execution .
➢
For FROM Clause we can pass Table_Name as an argument .
➢
The job of FROM Clause is to go to the Database and search for
the table and put the table under execution .
➢
SELECT Clause will execute after the execution of FROM Clause
➢
*

Column_Name

Expression

For SELECT Clause we pass 3 arguments
➢
The job of SELECT Clause is to go the table under execution and
select the columns mentioned .
➢
SELECT Clause is responsible for preparing the result table .
➢
Asterisk(*) : it means to select all the columns from the table .
➢
Semicolon : it means end of the query .
➢
WAQTD student id and student names for all the students.
➢
SELECT SID , SNAME
FROM STUDENT ;
WAQTD name and branch of all the students .
➢
SELECT SNAME , BRANCH
FROM STUDENT ;
WAQTD NAME , BRANCH AND PERCENTAGE FOR ALL THE
STUDENTS .
➢
SELECT SNAME , BRANCH , PER
FROM STUDENT ;
WAQTD details of all the students from students table .
➢
Student
Output of FROM Clause
SNAME
A
B
C
D
C
E
Output of SELECT Clause
SELECT SNAME
FROM STUDENT ;
New Section 1 Page 2

SELECT *
FROM STUDENT ;
WAQTD sname , sid , per , branch of all the students .
➢
SELECT SNAME , SID , PER , BRANCH
FROM STUDENT ;
EMP Table :
WAQTD name salary and commission given to all the employees .
➢
Select ename , sal , comm
From emp ;
WAQTD name of the employee along with their date of joining .
➢
Select ename , hiredate
From emp ;
DEPT :
DEPTNO DNAME
LOC
10
ACCOUNTING NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON
WAQTD dname and location for all the depts .
➢
Select dname , loc
From dept ;
QUESTIONS ON EMP AND DEPT TABLE:
[Link] A QUERY TO DISPLAY ALL THE DETAILS FROM THE

New Section 1 Page 3

EMPLOYEE TABLE.
[Link] NAMES OF ALL THE EMPLOYEES.
[Link] NAME AND SALARY GIVEN TO ALL THE
EMPLOYEES.
[Link] NAME AND COMMISSION GIVEN TO ALL THE
EMPLOYEES.
[Link] EMPLOYEE ID AND DEPARTMENT NUMBER OF ALL
THE EMPLOYEES
IN EMP TABLE.
[Link] ENAME AND HIREDATE OF ALL THE EMPLOYEES .
[Link] NAME AND DESIGNATION OF ALL THE
EMPLPOYEES .
[Link] NAME , JOB AND SALARY GIVEN ALL THE
EMPLOYEES.
[Link] DNAMES PRESENT IN DEPARTMENT TABLE.
[Link] DNAME AND LOCATION PRESENT IN DEPT TABLE.
Assignments have to Mailed TO : [Link]@[Link]
Subject : QCDM34 DAY 5 ASSIGNMENT
Name : Your Name
Mail ID : [Link]@[Link]
Batch Code : QCDM34
Phone : 9876543210
Please find the attachment below .
Thank You ,
Yours Faithfully ,
Rohan Singh R .
DISTINCT Clause
Result table " .
" It is used to remove the duplicate or repeated values from the
Example :
Student
SID
SNAME BRANCH PER
1
A
ECE
60
2
B
CSE
75
3
C
ME
50
4
D
ECE
80
5
C
CSE
75
6
E
CIVIL
95
Distinct clause has to be used
➢
As the first argument to
select clause .
We can use multiple columns
➢
As an argument to distinct
clause, it will remove the
combination of columns in
which the records are
duplicated .

New Section 1 Page 4

SELECT SNAME
➢
FROM STUDENT ;
SELECT DISTINCT SNAME
➢
SNAME
A
B
C
D
C
E
FROM STUDENT ;
SELECT DISTINCT BRANCH
➢
BRANCH
ECE
CSE
ME
ECE
CSE
CIVIL
FROM STUDENT ;
SELECT DISTINCT PER
➢
PER
60
75
50
80
75
95
FROM STUDENT ;
SELECT DISTINCT BRANCH , PER
➢
SNAME
A
B
C
D
C
E
SNAME
A
B
C
D
E
BRANCH
ECE
CSE
ME
CIVIL
PER
60
75
50
80
95

New Section 1 Page 5

FROM STUDENT ;
BRANCH PER
ECE
60
CSE
75
ME
50
ECE
80
CSE
75
CIVIL
95
BRANCH PER
ECE
60
CSE
75
ME
50
ECE
80
CIVIL
95

New Section 1 Page 6

EXPRESSION
"A statement which gives result is known as Expression ".
Expression is a combination Operand and Operator .
Operand : These are the values that we pass .
The Operand .
Example : 5 * 10
EMP
EID
ENAME SAL
1
A
100
2
B
200
2
C
100
WAQTD name and salary given to the employees .
1.
SELECT ENAME , SAL
FROM EMP ;
WAQTD name and annual salary of the employees .
2.
SELECT ENAME , SAL * 12
ENAME SAL*12
A
1200
B
2400
C
1200
FROM EMP ;
3.
WAQTD all the details of the employee along with annual salary
4.
Select eid , ename , sal , sal*12
From emp ;
Select emp.* , sal*12
From emp ;
WAQTD name and salary with a hike of 20% .
5.
Select ename , Sal + Sal*20/100
From emp ;
Operator : These are the Symbols which perform some Operation on
DAY 6
Thursday, July 23, 2020
9:00 AM

New Section 1 Page 1

Sal + Sal * a / 100 Sal * 1.a

Formulae to calculate percentage :
WAQTD name and salary of an employee with a deduction
6.
Of 10% .
Select ename , sal - sal * 10 /100
From emp ;
ALIAS
"It is an alternate name given to a Column or an Expression
We can assign alias name with or without using 'As'
keyword .
○
Example :
ANNUAL_SALARY
"ANNUAL SALARY"
Alias names have to be a single string which is separated
by An underscore or enclosed within double quotes .
○
In the result table " .
WAQTD annual salary for all the employees .
➢
Select sal*12
SAL*12
1200
2400
1200
From emp ;
Select sal*12 Annual_Salary
Annual Salary
1200
2400
1200
From emp ;
Select sal + sal * 10 / 100 Hike
From emp ;
WAQTD name and salary with a deduction 32% .
➢
Select Ename , sal-sal*32/100 as deduction
From emp ;
ASSIGNMENT ON EXPRESSION & ALIAS
[Link] NAME OF THE EMPLOYEE ALONG WITH THEIR
ANNUAL SALARY.
[Link] ENAME AND JOB FOR ALL THE EMPLOYEE WITH
THEIR HALF TERM SALARY.

New Section 1 Page 2

[Link] ALL THE DETAILS OF THE EMPLOYEES ALONG

WITH AN ANNUALBONUS OF 2000.
[Link] NAME SALARY AND SALARY WITH A HIKE OF
10%.
[Link] NAME AND SALARY WITH DEDUCTION OF 25%.
[Link] NAME AND SALARY WITH MONTHLY HIKE OF
50.
[Link] NAME AND ANNUAL SALARY WITH DEDUCTION
OF 10%.
[Link] TOTAL SALARY GIVEN TO EACH EMPLOYEE
(SAL+COMM).
[Link] DETAILS OF ALL THE EMPLOYEES ALONG WITH
ANNUAL SALARY.
[Link] NAME AND DESIGNATION ALONG WITH 100
PENALTY IN SALARY.
SELECTION :
"It is a process of retrieving the data by selecting both the columns
and rows is known as Selection " .
SYNTAX :
SELECT * / [DISTINCT] Column_Name / Expression [ALIAS]
FROM Table_Name
WHERE <Filter_Condition> ;
FROM
1.
WHERE
2.
SELECT
3.
ORDER OF EXECUTION
WHERE Clause
"Where clause is used to filter the records ".
Example :
WAQTD names of the employees working in dept 20 .
➢

New Section 1 Page 3

WAQTD names of the employees getting salary More than 300 .

➢
SELECT ENAME
FROM EMP
WHERE SAL > 300 ;
WAQTD names and salary of the employees working in dept 10.
➢
SELECT ENAME , SAL
FROM EMP
WHERE DEPTNO = 10 ;
WAQTD all the details of the employees whose salary is
➢
Less than 1000 rupees .
SELECT *
FROM EMP
WHERE SAL < 1000 ;
EMP :

New Section 1 Page 4

WAQTD name and hiredate of an employee hired on

➢
'09-JUN-1981'
SELECT ENAME , HIREDATE
FROM EMP
WHERE DATE = '09-JUN-1981' ;
WAQTD details of the employee whose name is 'Miller'
➢
SELECT *
FROM EMP
WHERE ENAME ='MILLER' ;
WAQTD details of the employee hired after '01-JAN-1982'
➢
SELECT *
FROM EMP
WHERE HIREDATE > '01-JAN-1982' > ;
WAQTD name sal and hiredate of the employees who were
➢
Hired before 1985 .
SELECT ENAME , SAL , HIREDATE
FROM EMP
WHERE HIREDATE < '01-JAN-1985' ;
WAQTD name sal and hiredate of the employees who were
➢
Hired after 1985 .
SELECT ENAME , SAL , HIREDATE
FROM EMP
WHERE HIREDATE > '31-DEC-1985' ;
WAQTD name of the employees who was hired on Valentine's
day 2020 .
➢
SELECT ENAME

New Section 1 Page 5

FROM EMP
WHERE HIREDATE = '14-FEB-2020' ;
ASSIGNMENT ON WHERE Clause .
[Link] THE ANNUAL SALARY OF THE EMPLOYEE WHOS
NAME IS SMITH
[Link] NAME OF THE EMPLOYEES WORKING AS CLERK
[Link] SALARY OF THE EMPLOYEES WHO ARE
WORKING AS SALESMAN
[Link] DETAILS OF THE EMP WHO EARNS MORE THAN
2000
[Link] DETAILS OF THE EMP WHOS NAME IS JONES
[Link] DETAILS OF THE EMP WHO WAS HIRED AFTER
01-JAN-81
[Link] NAME AND SAL ALONG WITH HIS ANNUAL
SALARY IF THE ANNUAL SALARY IS MORE THAN 12000
[Link] EMPNO OF THE EMPLOYEES WHO ARE WORKING
IN DEPT 30
[Link] ENAME AND HIREDATE IF THEY ARE HIRED
BEFORE 1981
[Link] DETAILS OF THE EMPLOYEES WORKING AS
MANAGER
[Link] NAME AND SALARY GIVEN TO AN EMPLOYEE IF
EMPLOYEE EARNS A COMMISSION OF RUPEES 1400
[Link] DETAILS OF EMPLOYEES HAVING COMMISSION
MORE THAN SALARY
[Link] EMPNO OF EMPLOYEES HIRED BEFORE THE
YEAR 87
[Link] DETAILS OF EMPLOYEES WORKING AS AN N
ANALYST
[Link] DETAILS OF EMPS EARNING MORE THAN 2000
RUPEES PER MONTH
COMMANDS ON SQL*Plus :
CLEAR SCREEN [ CL SCR ] : To clear the screen
1.
SET LINES 100 PAGES 100 : To set the dimensions of the
output page .
2.

New Section 1 Page 6

EXIT / QUIT : To Close the Software .

3.
Log in as SYSTEM
➢
Password TIGER
➢
ALTER USER SCOTT ACCOUNT UNLOCK ;
➢
ALTER USER SCOTT IDENTIFIED BY TIGER ;
➢
When account is Locked !!!
4.
EMP
➢
DEPT
➢
SALGRADE
➢
BONUS
➢
SELECT * FROM TAB ;
5.

New Section 1 Page 7

OPERATORS IN SQL
1. ARITHEMATIC OPERATORS :- ( + , - , * , / )
2. CONCATENATION OPERATOR :- ( || )
3. COMPARISION OPERATORS :- ( = , != or <> )
4. RELATIONAL OPERATOR :- ( > , < , >= , <= )
[Link] OP : ( AND , OR , NOT )
[Link]
[Link] OPERATOR :-
[Link] IN
[Link]
[Link] BETWEEN
[Link]
[Link] NOT
[Link]
[Link] LIKE
[Link]
[Link] OPERATORS:-
[Link]
[Link]
[Link] EXISTS
CONCATENATION Operator :
" It is used to join the strings ".
Symbol : ||
FROM EMP
WHERE JOB ='MANAGER' ;
Example : SELECT ENAME
Ename
ALLEN
MARTIN
SMITH
SELECT 'Hi ' || ename
FROM EMP
WHERE JOB ='MANAGER' ;
Ename
Hi ALLEN
Hi MARTIN
Hi SMITH
DAY 7
Friday, July 24, 2020
9:44 AM

New Section 1 Page 1

WAQTD name and deptno of the employees hired

➢
After '01-JAN-87' .
SELECT ENAME , DEPTNO
FROM EMP
WHERE HIREDATE > '01-JAN-1987' ;
WAQTD name and hiredate of the employees hired
before 31-JUL-88
➢
SELECT ENAME , HIREDATE
FROM EMP
WHERE HIREDATE < '31-JUL-88' ;
AND
1.
OR
2.
NOT
3.
LOGICAL OPERATORS
We use logical operators to write multiple conditions .
WAQTD name and deptno along with job for the
employee working in dept 10 .
1.
SELECT ENAME , DEPTNO , JOB
FROM EMP
WHERE DEPTNO = 10 ;
WAQTD name and deptno along with job for the
employee working as manager in dept 10 .
2.
SELECT ENAME , DEPTNO , JOB
FROM EMP
WHERE JOB ='MANAGER' AND DEPTNO = 10 ;
WAQTD name , deptno , salary of the employee working
in dept 20 and earning less than 3000 .
3.
SELECT ENAME, DEPTNO , SAL
FROM EMP
WHERE DEPTNO = 20 AND SAL < 3000 ;
WAQTD name and salary of the employee if emp earns
4.
More than 1250 but less than 3000 .
SELECT ENAME , SAL
FROM EMP
WHERE SAL > 1250 AND SAL < 3000 ;
WAQTD name and deptno of the employees if the works
in dept 10 or 20 .
5.

New Section 1 Page 2

SELECT ENAME , DEPTNO
FROM EMP
WHERE DEPTNO = 10 OR DEPTNO = 20 ;
WAQTD name and sal and deptno of the employees
6.
If emp gets more than 1250 but less than 4000 and works
in dept 20 .
SELECT ENAME , SAL , DEPTNO
FROM EMP
WHERE SAL > 1250 AND SAL < 4000 AND DEPTNO
=20 ;
WAQTD name , job , deptno of the employees working
as a manager in dept 10 or 30 .
7.
SELECT ENAME , JOB , DEPTNO
FROM EMP
WHERE JOB ='MANAGER' AND ( DEPTNO = 10 OR
DEPTNO = 20 ) ;
WAQTD name , deptno , job of the employees working
in dept 10 or 20 or 30 as a clerk .
8.
SELECT ENAME , JOB , DEPTNO
FROM EMP
WHERE JOB ='CLERK' AND ( DEPTNO = 10 OR
DEPTNO = 20 AND DEPTNO = 30 ) ;
WAQTD name , job and deptno of the employees
working as clerk or manager in dept 10 .
9.
SELECT ENAME , JOB , DEPTNO
FROM EMP
WHERE ( JOB = 'CLERK' OR JOB ='MANAGER' )
AND DEPTNO = 10 ;
WAQTD name , job , deptno , sal of the employees
working as clerk or salesman in dept 10 or 30 and
earning more than 1800 .
10.
SELECT ENAME , JOB , SAL
FROM EMP
WHERE ( JOB ='CLERK' OR JOB ='SALESMAN')
AND ( DEPTNO = 10 OR DEPTNO = 30 ) AND SAL >
1800 ;
ASSIGNMENT ON LOGICAL OPERATORS :
[Link] DETAILS OF THE EMPLOYEES WORKING
AS CLERK AND EARNING LESS THAN 1500
[Link] NAME AND HIREDATE OF THE EMPLOYEES
WORKING AS MANAGER IN DEPT 30

New Section 1 Page 3

[Link] DETAILS OF THE EMP ALONG WITH
ANNUAL SALARY IF THEY ARE WORKING IN DEPT
30 AS SALESMAN AND THEIR ANNUAL SALARY HAS
TO BE GREATER THAN 14000.
[Link] ALL THE DETAILS OF THE EMP WORKING
IN DEPT 30 OR AS ANALYST
[Link] NAMES OF THE EMPMLOYEES WHOS
SALARY IS LESS THAN 1100 AND THEIR
DESIGNATION IS CLERK
[Link] NAME AND SAL , ANNUAL SAL AND
DEPTNO IF DEPTNO IS 20 EARNING MORE THAN 1100
AND ANNUAL SALARY EXCEEDS 12000
[Link] EMPNO AND NAMES OF THE EMPLOYEES
WORKING AS MANAGER IN DEPT 20
[Link] DETAILS OF EMPLOYEES WORKING IN
DEPT 20 OR 30 .
[Link] DETAILS OF EMPLOYEES WORKING AS
ANALYST IN DEPT 10 .
[Link] DETAILS OF EMPLOYEE WORKING AS
PRESIDENT WITH SALARY OF RUPEES 4000
[Link] NAMES AND DEPTNO , JOB OF EMPS
WORKING AS CLERK IN DEPT 10 OR 20
[Link] DETAILS OF EMPLOYEES WORKING AS
CLERK OR MANAGER IN DEPT 10 .
[Link] NAMES OF EMPLOYEES WORKING IN
DEPT 10 , 20 , 30 , 40 .
[Link] DETAILS OF EMPLOYEES WITH EMPNO
7902,7839.
[Link] DETAILS OF EMPLOYEES WORKING AS
MANAGER OR SALESMAN OR CLERK
[Link] NAMES OF EMPLOYEES HIRED AFTER 81
AND BEFORE 87
[Link] DETAILS OF EMPLOYEES EARNING MORE
THAN 1250 BUT LESS THAN 3000
[Link] NAMES OF EMPLOYEES HIRED AFTER 81
INTO DEPT 10 OR 30
[Link] NAMES OF EMPLOYEES ALONG WITH
ANNUAL SALARY FOR THE EMPLOYEES WORKING
AS MANAGER OR CLERK INTO DEPT 10 OR 30
[Link] ALL THE DETAILS ALONG WITH ANNUAL
SALARY IF SAL IS BETWEEN 1000 AND 4000 ANNUAL
SALARY MORE THAN 15000
SPECIAL OPERATORS :
IN
1.
NOT IN
2.
BETWEEN
3.
NOT BETWEEN
4.
IS
5.
IS NOT
6.
LIKE
7.
NOT LIKE
8.

New Section 1 Page 4

IN : It is a multi-valued operator which can accept

multiple values At the RHS .
1.
Syntax: Column_Name / Exp IN ( v1 , v2 , . . Vn )
Example :
WAQTD name and deptno of the employees working in
dept 10 or 30 .
➢
SELECT ENAME , DEPTNO
FROM EMP
WHERE DEPTNO = 10 OR DEPTNO = 30 ;
SELECT ENAME , DEPTNO
FROM EMP
WHERE DEPTNO IN ( 10 , 30 ) ;
WAQTD name and job of the employee working as a
clerk or manager Or salesman .
➢
SELECT ENAME , JOB
FROM EMP
WHERE JOB IN ('CLERK' , 'MANAGER' ,
'SALESMAN' ) ;
WAQTD empno , ename and salary of the employees
whose empno Is 7902 or 7839 and getting salary more
than 2925.
➢
SELECT EMPNO , ENAME , SAL
FROM EMP
WHERE EMPNO IN ( 7902 , 7839 ) AND SAL> 2925 ;
NOT IN : It is a multi-valued operator which can accept
multiple values At the RHS . It is similar to IN op instead
of selecting it Rejects the values .
2.
Syntax: Column_Name / Exp NOT IN ( v1 , v2 , . . vn )
Example :
WAQTD name and deptno of all the employees except
the emp Working in dept 10 or 40 .
➢
SELECT ENAME , DEPTNO
FROM EMP
WHERE DEPTNO NOT IN ( 10 , 40 ) ;
WAQTD name , deptno and job of the employee
working in dept 20 but not as a clerk or manager .
➢

New Section 1 Page 5

SELECT ENAME , DEPTNO

FROM EMP
WHERE DEPTNO = 20 AND
JOB NOT IN ( 'CLERK' ,'MANAGER' ) ;
ANSWERS :
[Link] DETAILS OF THE EMPLOYEES WORKING AS
CLERK AND EARNING LESS THAN1500
SELECT *
FROM EMP
WHERE JOB ='CLERK' AND SAL< 1500 ;
[Link] NAME AND HIREDATE OF THE EMPLOYEES
WORKING AS MANAGER IN DEPT 30
SELECT ENAME , HIREDATE
FROM EMP
WHERE JOB ='MANAGER' AND DEPTNO=30 ;
[Link] DETAILS OF THE EMP ALONG WITH ANNUAL
SALARY IF THEY ARE WORKING INDEPT 30 AS
SALESMAN AND THEIR ANNUAL SALARY HAS TO BE
GREATER THAN 14000
SELECT EMP.* , SAL*12 ANNUAL_SALARY
FROM EMP
WHERE DEPTNO = 30 AND JOB ='SALESMAN' AND SAL*
12 > 14000 ;
[Link] ALL THE DETAILS OF THE EMP WORKING IN
DEPT 30 OR AS ANALYST
SELECT *
FROM EMP
WHERE DEPTNO = 30 OR JOB ='ANALYST' ;
[Link] NAMES OF THE EMPMLOYEES WHOS SALARY IS
LESS THAN 1100 AND THEIR DESIGNATION IS CLERK
SELECT ENAME
FROM EMP
WHERE SAL< 1100 AND JOB ='CLERK' ;
[Link] NAME AND SAL , ANNUAL SAL AND DEPTNO IF
DEPTNO IS 20 EARNING MORE THAN 1100 AND ANNUAL
SALARY EXCEEDS 12000
SELECT ENAME , SAL , SAL*12 , DEPTNO
FROM EMP
WHERE DEPTNO = 20 AND SAL > 1100 AND SAL*12 > 12000 ;
[Link] EMPNO AND NAMES OF THE EMPLOYEES
WORKING AS MANAGER IN DEPT 20
SELECT EMPNO , ENAME
FROM EMP
WHERE DEPTNO = 20 AND JOB ='MANAGER' ;
[Link] DETAILS OF EMPLOYEES WORKING IN DEPT 20
OR 30
SELECT *
FROM EMP

New Section 1 Page 6

WHERE DEPTNO = 10 OR DEPTNO = 30 ;

[Link] DETAILS OF EMPLOYEES WORKING AS
ANALYST IN DEPT 10
SELECT *
FROM EMP
WHERE DEPTNO = 10 AND JOB ='ANALYST' ;
[Link] DETAILS OF EMPLOYEE WORKING AS
PRESIDENT WITH SALARY OF RUPEES 4000
SELECT *
FROM EMP
WHERE SAL=4000 AND JOB ='PRESIDENT' ;
[Link] NAMES AND DEPTNO , JOB OF EMPS WORKING
AS CLERK IN DEPT 10 OR 20
SELECT ENAME, DEPTNO, JOB
FROM EMP
WHERE JOB = ‘CLERK’ AND ( DEPTNO =10 OR DEPTNO =
20);
12. WAQTD DETAILS OF EMPLOYEES WORKING AS CLERK
OR MANAGER IN DEPT 10
SELECT *
FROM EMP
WHERE ( JOB = ‘CLERK’OR JOB = ‘MANAGER’ ) AND
DEPTNO = 10;
13. WAQTD NAMES OF EMPLOYEES WORKING IN DEPT 10 ,
20 , 30 , 40
SELECT ENAME
FROM EMP
WHERE DEPTNO = 10 OR DEPTNO = 20 OR DEPTNO = 30 OR
DEPTNO =40 ;
14. WAQTD DETAILS OF EMPLOYEES WITH EMPNO 7902,
7839
SELECT *
FROM EMP
WHERE EMPNO = 7902 OR EMPNO = 7839;
15. WAQTD DETAILS OF EMPLOYEES WORKING AS
MANAGER OR SALESMAN OR CLERK
SELECT *
FROM EMP
WHERE JOB = 'MANAGER' OR JOB = 'SALESMAN' OR JOB =
'CLERK';
[Link] NAMES OF EMPLOYEES HIRED AFTER 81
AND BEFORE 87
SELECT ENAME
FROM EMP
WHERE HIREDATE > '31-DEC-81' AND HIREDATE <'01-
JAN-87'
[Link] DETAILS OF EMPLOYEES EARNING MORE
THAN 1250 BUT LESS THAN 3000
SELECT *

New Section 1 Page 7

FROM EMP
WHERE SAL > 1250 AND SAL < 3000 ;
[Link] NAMES OF EMPLOYEES HIRED AFTER 81
INTO DEPT 10 OR 30
SELECT ENAME
FROM EMP
WHERE HIREDARE > '31-DEC-81' AND ( DEPTNO = 10 OR
DEPTNO = 20 ) ;
[Link] NAMES OF EMPLOYEES ALONG WITH
ANNUAL SALARY FOR THE EMPLOYEES WORKING
AS MANAGER OR CLERK INTO DEPT 10 OR 30
SELECT ENAME , SAL*12
FROM EMP
WHERE ( JOB = 'MANAGER' OR JOB ='CLERK') AND
( DEPTNO = 10 OR DEPTNO = 30 ) ;
[Link] ALL THE DETAILS ALONG WITH ANNUAL
SALARY IF SAL IS BETWEEN 1000 AND 4000 ANNUAL
SALARY MORE THAN 15000
SELECT EMP.* , SAL*12
FROM EMP
WHERE SAL > 1000 AND SAL < 4000 AND SAL*12 > 15000 ;

New Section 1 Page 8

[ Start value and Stop Value ] .

BETWEEN : "It is used whenever we have range of values "
3.
Syntax:
Column_Name BETWEEN Lower_Range AND Higher_Range ;
Between Op works including the range .
-
Example :
WAQTD name and salary of the employees if the emp is earning
➢
Salary in the range 1000 to 3000 .
SELECT ENAME , SAL
FROM EMP
WHERE SAL BETWEEN 1000 AND 3000 ;
WAQTD name and deptno of the employees working in dept 10
➢
And hired during 2019 (the entire year of 2019) .
SELECT ENAME , DEPTNO
FROM EMP
WHERE DEPTNO = 10 AND HIREDATE BETWEEN '01-
JAN-2019' AND '31-DEC-2019' ;
WAQTD name , sal and hiredate of the employees hired during
➢
2017 into dept 20 with a salary greater that 2000 .
SELECT ENAME , SAL , HIREDATE
FROM EMP
WHERE DEPTNO = 20 AND SAL> 2000 AND HIREDATE
BETWEEN '01-JAN2017' AND 31-DEC-2017' ;
NOT BETWEEN : It is Opposite of Between .
4.
Syntax:
Column_Name NOT BETWEEN Lower_Range AND Higher_Range ;
Example :
WAQTD name and salary of the employees if the emp is not
earning Salary in the range 1000 to 3000 .
➢
SELECT ENAME , SAL
FROM EMP
WHERE SAL NOT BETWEEN 1000 AND 3000 ;
DAY 8
Monday, July 27, 2020
9:32 AM

New Section 1 Page 1

WAQTD name and deptno of the employees working in dept 10

➢
And not hired during 2019 .
SELECT ENAME , DEPTNO
FROM EMP
WHERE DEPTNO = 10 AND HIREDATE NOT BETWEEN '01-
JAN-2019' AND '31-DEC-2019' ;
WAQTD name , sal and hiredate of the employees who were not
hired during 2017 into dept 20 with a salary greater that 2000 .
➢
SELECT ENAME , SAL , HIREDATE
FROM EMP
WHERE DEPTNO = 20 AND SAL> 2000 AND HIREDATE NOT
BETWEEN '01-JAN2017' AND 31-DEC-2017' ;
IS : "It is used to compare only NULL "
5.
Syntax: Column_Name IS NULL ;
Example :
EID
ENAME SAL
COMM
1
A
1000
100
2
B
null
null
3
C
null
200
4
D
2000
null
WAQTD name of the employee who is not getting salary .
➢
SELECT ENAME
FROM EMP
WHERE SAL IS NULL ;
WAQTD name of the emp who doesn’t get commission .
➢
SELECT ENAME
FROM EMP
WHERE COMM IS NULL ;
WAQTD name , sal and comm of the emp if the emp doesn’t earn
both .
➢
SELECT ENAME , SAL , COMM
FROM EMP
WHERE COMM IS NULL AND SAL IS NULL ;
IS NOT : "It is used to compare the values with NOT NULL ".
6.
Syntax: Column_Name IS NOT NULL ;

New Section 1 Page 2

Example :
WAQTD name of the employee who is getting salary .
➢
SELECT ENAME
FROM EMP
WHERE SAL IS NOT NULL ;
WAQTD name of the emp who gets commission .
➢
SELECT ENAME
FROM EMP
WHERE COMM IS NOT NULL ;
WAQTD name , sal and comm of the emp if the emp doesn’t earn
commission but gets salary .
➢
SELECT ENAME , SAL , COMM
FROM EMP
WHERE COMM IS NULL AND SAL IS NOT NULL ;
LIKE : "It is used for Pattern Matching ".
7.
To achieve pattern matching we use special characters .
Percentile (%)
➢
Underscore ( _ )
➢
Syntax: Column_Name LIKE 'pattern' ;
Example :
WAQTD details of an employee whose name is SMITH .
➢
SELECT *
FROM EMP
WHERE ENAME ='SMITH' ;
WAQTD details of the employee who's name starts with 'S' .
➢
SELECT *
FROM EMP
WHERE ENAME LIKE 'S%' ;
WAQTD details of the employee who's name ends with 'S' .
➢
SELECT *
FROM EMP
WHERE ENAME LIKE '%S' ;
WAQTD names of the employees who have character 'S' in their
names .
➢
SELECT *
FROM EMP

New Section 1 Page 3

WHERE ENAME LIKE '%S%' ;

WAQTD names that starts with 'J' and ends with 'S' .
➢
SELECT ENAME
FROM EMP
WHERE ENAME LIKE 'J%S' ;
WAQTD names of the employee if the emp has char 'A' as his
second character .
➢
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '_A%' ;
WAQTD names of the employee if the emp has char 'A' as his Third
character .
➢
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '__A%' ;
WAQTD names of the employee if the emp has char 'A' as his
second character and 'S' is last character .
➢
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '_A%S' ;
WAQTD names of the employee if the emp has char 'A' present at at
least 2 times .
➢
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '%A%A%' ;
WAQTD names of the employee if the emp name starts with 'A' and
ends with 'A' .
➢
SELECT ENAME
FROM EMP
WHERE ENAME LIKE 'A%A' ;
WAQTD names of the employee if the emp's salary's last 2 digit is
50 rupees .
➢
SELECT ENAME
FROM EMP
WHERE SAL LIKE '%50' ;
WAQTD names of the employees hired in November .
➢
SELECT ENAME
FROM EMP

New Section 1 Page 4

WHERE HIREDATE LIKE '%NOV%' ;

NOT LIKE :Opposite of Like .
8.
Syntax: Column_Name NOT LIKE 'pattern' ;
ASSIGNMENT ON SEPCIAL OPERATORS :
1) LIST ALL THE EMPLOYEES WHOSE COMMISSION IS
NULL
2) LIST ALL THE EMPLOYEES WHO DON’T HAVE A
REPORTING MANAGER
3) LIST ALL THE SALESMEN IN DEPT 30
4) LIST ALL THE SALESMEN IN DEPT NUMBER 30 AND
HAVING SALARY GREATER THAN 1500
5) LIST ALL THE EMPLOYEES WHOSE NAME STARTS
WITH ‘S’ OR ‘A’
6) LIST ALL THE EMPLOYEES EXCEPT THOSE WHO ARE
WORKING IN DEPT 10 & 20.
7) LIST THE EMPLOYEES WHOSE NAME DOES NOT START
WITH ‘S’
8) LIST ALL THE EMPLOYEES WHO ARE HAVING
REPORTING MANAGERS IN DEPT 10
9) LIST ALL THE EMPLOYEES WHOSE COMMISSION IS
NULL AND WORKING AS CLERK
10) LIST ALL THE EMPLOYEES WHO DON’T HAVE A
REPORTING MANAGER IN DEPTNO 10 OR 30
11) LIST ALL THE SALESMEN IN DEPT 30 WITH SAL MORE
THAN 2450
12) LIST ALL THE ANALYST IN DEPT NUMBER 20 AND
HAVING SALARY GREATER THAN 2500
13) LIST ALL THE EMPLOYEES WHOSE NAME STARTS
WITH ‘M’ OR ‘J’
14) LIST ALL THE EMPLOYEES WITH ANNUAL SALARY
EXCEPT THOSE WHO ARE WORKING IN DEPT 30
15) LIST THE EMPLOYEES WHOSE NAME DOES NOT END
WITH ‘ES’ OR ‘R’
16) LIST ALL THE EMPLOYEES WHO ARE HAVING
REPORTING MANAGERS IN DEPT 10 ALONG WITH 10%
HIKE IN SALARY
17) DISPLAY ALL THE EMPLOYEE WHO ARE
‘SALESMAN’S HAVING ‘E’ AS THE LAST BUT ONE
CHARACTER IN ENAME BUT SALARY HAVING EXACTLY
4 CHARACTER
18) DISPLAY ALL THE EMPLOYEE WHO ARE JOINED
AFTER YEAR 81
19) DISPLAY ALL THE EMPLOYEE WHO ARE JOINED IN
FEB
20) LIST THE EMPLOYEES WHO ARE NOT WORKING AS
MANAGERS AND CLERKS IN DEPT 10 AND 20 WITH A
SALARY IN THE RANGE OF 1000 TO 3000.

New Section 1 Page 5

New Section 1 Page 6

New Section 1 Page 7

FUNCTIONS
Are a block of code or list of instructions which are used to perform a
specific task .
Function_Name
1.
Number_of_arguments ( no of inputs )
2.
Return type
3.
There are 3 main components of a function
Types of Functions in SQL :
SINGLE ROW FUNCTIONS
1.
MUTLI ROW FUNCTIONS / AGGREGATE / GROUP
FUNCTIONS.
2.
Multi Row Functions;
It takes all the inputs at one shot and then executes and provides
A single output .
If we pass 'n' number of inputs to a MRF( ) it returns '1' Output .
➢
List of MRF ( )
MAX( ) : it is used to obtain the maximum value present in the
column
1.
MIN ( ) : it is used to obtain the minimum value present in the
2.
DAY 9
Tuesday, July 28, 2020
9:37 AM

New Section 1 Page 1

column
SUM ( ) : it is used to obtain the summation of values present in the
column
3.
AVG( ) : it is used to obtain the average of values present in the
column
4.
COUNT( ) : it is used to obtain the number of values present in the
column
5.
NOTE :
Multi row functions can accept only one argument , i.e a
Column_Name or an Expression
➢
MRF ( Column_Name / Exp )
Along with a MRF( ) we are not supposed to use any other
Column_Name in the select clause .
➢
MRF( ) ignore the Null .
➢
We cannot use a MRF( ) in where clause .
➢
COUNT( ) is the only MRF which can accept * as an
Argument .
➢
Examples :
WAQTD maximum salary given to a manager .
1.
SELECT MAX( SAL )
FROM EMP
WHERE JOB ='MANAGER' ;
WAQTD Total salary given to dept 10
2.
SELECT SUM( SAL )
FROM EMP
WHERE DEPTNO =10 ;
WAQTD number of employees earing more than 1500 in dept 20
3.
SELECT COUNT(*)
FROM EMP
WHERE SAL > 1500 AND DEPTNO = 20 ;
WAQTD number of employee having 'E' in their names .
4.
SELECT COUNT(*)
FROM EMP
WHERE ENAME LIKE '%E%' ;
WAQTD minimum salary given to the employees working as clerk
in Dept 10 or 20 .
5.

New Section 1 Page 2

SELECT MIN( SAL )

FROM EMP
WHERE JOB='CLERK' AND DEPTNO IN ( 10 , 20 ) ;
WAQTD number of employees hired after 1982 and before 1985
into Dept 10 or 30 .
6.
SELECT COUNT(*)
FROM EMP
WHERE JIREDATE >'31-DEC-1982' AND HIREDATE <'01-
JAN-1985' AND DEPTNO IN ( 10 , 30 ) ;
WAQTD number of employees getting commission .
7.
SELECT COUNT(*)
FROM EMP
WHERE COMM IS NOT NULL ;
SELECT COUNT( COMM )
FROM EMP ;
WAQTD maximum salary given to employees if the emp has
character 'S' in the name and works as a Manager in dept 10 with as
salary of more than 1800 .
8.
SELECT MAX( SAL )
FROM EMP
WHERE ENAME LIKE '%S%' AND JOB ='MANAGER' AND
DEPTNO = 10 AND SAL> 1800 ;
WAQTD number of employees working in dept 10 or 30 and
getting commission without the salary .
9.
SELECT COUNT(*)
FROM EMP
WHERE DEPTNO IN ( 10 , 30 ) AND COMM IS NOT NULL
AND SAL IS NULL ;
SELECT COUNT( COMM )
FROM EMP
WHERE DEPTNO IN ( 10 , 30 ) AND SAL IS NULL ;
WAQTD maximum salary given to a manager working in dept 20
and also his comm must be greater than his salary .
10.
SELECT MAX( SAL )
FROM EMP
WHERE JOB ='MANAGER' AND DEPTNO = 20 AND COMM >
SAL ;
ASSIGNEMENT ON MRF( )

New Section 1 Page 3

[Link] NUMBER OF EMPLOYEES GETTING SALARY LESS

THAN 2000 IN DEPTNO 10
[Link] TOTAL SALARY NEEDED TO PAY EMPLOYEES
WORKING AS CLERK
[Link] AVERAGE SALARY NEEDED TO PAY ALL
EMPLOYEES
[Link] NUMBER OF EMPLOYEES HAVING 'A' AS THEIR
FIRST CHARACTER
[Link] NUMBER OF EMPLOYEES WORKING AS CLERK OR
MANAGER
[Link] TOTAL SALARY NEEDED TO PAY EMPLOYEES
HIRED IN FEB
[Link] NUMBER OF EMPLOYEES REPORTING TO 7839 (MGR)
[Link] NUMBER OF EMPLOYEES GETTING COMISSION IN
DEPTNO 30
[Link] AVG SAL , TOTAL SAL , NUMBER OF EMPS AND
MAXIMUM SALARY GIVEN TO EMPLOYEES WORKING AS
PERSIDENT
[Link] NUMBER OF EMPLOYEES HAVING 'A' IN THEIR
NAMES
[Link] NUMBER OF EMPS AND TOTAL SALARY NEEDED
TO PAY THE EMPLOYEES WHO HAVE 2 CONSICUTIVE L's IN
THEIR NAMES
[Link] NUMBER OF DEPARTMENTS PRESENT IN
EMPLOYEE TABLE
[Link] NUMBER OF EMPLOYEES HAVING CHARACTER 'Z'
IN THEIR NAMES
[Link] NUMBER OF EMPLOYEES HAVING '$' IN THEIR
NAMES .
[Link] TOTAL SALARY GIVEN TO EMPLOYEES WORKING
AS CLERK IN DEPT 30
[Link] MAXIMUM SALARY GIVEN TO THE EMPLOYEES
WORKING AS ANALYST
[Link] NUMBER OF DISTINCT SALARIES PRESENT IN
EMPLOYEE TABLE
[Link] NUMBER OF JOBS PRESENT IN EMPLOYEE TABLE
[Link] AVG SALARY GIVEN TO THE CLERK
[Link] MINIMUM SALARY GIVEN TO THE EMPLOYEES
WHO WORK IN DEPT 10 AS MANAGER OR A CLERK
ANSWERS :
SELECT COUNT(*)
FROM EMP
WHERE DEPTNO = 10 AND SAL < 2000 ;
[Link] NUMBER OF EMPLOYEES GETTING SALARY LESS
THAN 2000 IN DEPTNO 10
SELECT SUM(SAL)
FROM EMP
WHERE JOB ='CLERK';
[Link] TOTAL SALARY NEEDED TO PAY EMPLOYEES
WORKING AS CLERK

New Section 1 Page 4

SELECT AVG(SAL)
FROM EMP ;
[Link] AVERAGE SALARY NEEDED TO PAY ALL
EMPLOYEES
SELECT COUNT(*)
FROM EMP
WHERE ENAME LIKE 'A%';
[Link] NUMBER OF EMPLOYEES HAVING 'A' AS THEIR
FIRST CHARACTER
SELECT COUNT(*)
FROM EMP
WHERE JOB IN ('MANAGER' , 'CLERK' );
[Link] NUMBER OF EMPLOYEES WORKING AS CLERK OR
MANAGER
SELECT SUM(SAL)
FROM EMP
WHERE HIREDATE LIKE '%FEB%';
[Link] TOTAL SALARY NEEDED TO PAY EMPLOYEES
HIRED IN FEB
SELECT COUNT(*)
FROM EMP
WHERE MGR = 7839 ;
[Link] NUMBER OF EMPLOYEES REPORTING TO 7839 (MGR)
SELECT COUNT(*)
FROM EMP
WHERE COMM IS NOT NULL AND DEPTNO = 30 ;
OR
SELECT COUNT(COMM)
FROM EMP
WHERE DEPTNO = 30 ;
[Link] NUMBER OF EMPLOYEES GETTING COMISSION IN
DEPTNO 30
SELECT AVG(SAL) , SUM(SAL) , COUNT(*) , MAX(SAL)
FROM EMP
WHERE JOB = 'PRESIDENT' ;
[Link] AVG SAL , TOTAL SAL , NUMBER OF EMPS AND
MAXIMUM SALARY GIVEN TO EMPLOYEES WORKING AS
PERSIDENT
SELECT COUNT(*)
FROM EMP
WHERE ENAME LIKE '%A%';
[Link] NUMBER OF EMPLOYEES HAVING 'A' IN THEIR
NAMES
SELECT COUNT(*) , SUM(SAL)
FROM EMP
WHERE ENAME LIKE '%LL%';
[Link] NUMBER OF EMPS AND TOTAL SALary needed to pay
THE EMPLOYEES WHO HAVE 2 CONSICUTIVE L's IN THEIR
NAMES
SELECT COUNT( DISTINCT DEPTNO )
FROM EMP ;
[Link] NUMBER OF DEPARTMENTS PRESENT IN
EMPLOYEE TABLE
[Link] NUMBER OF EMPLOYEES HAVING CHARACTER '_'

New Section 1 Page 5

SELECT COUNT(*)
FROM EMP
WHERE ENAME LIKE '%!_%' ESCAPE '!' ;
IN THEIR NAMES
SELECT COUNT(*)
FROM EMP
WHERE ENAME LIKE '%!%%!%%' ESCAPE '%' ;
[Link] NUMBER OF EMPLOYEES HAVING ATLEAST 2
PERCENTILES IN THEIR NAMES
SELECT SUM(SAL)
FROM EMP
WHERE JOB ='CLERK' AND DEPTNO = 30 ;
[Link] TOTAL SALARY GIVEN TO EMPLOYEES WORKING
AS CLERK IN DEPT 30
SELECT MAX(Sal)
FROM EMP
WHERE JOB ='ANALYST' ;
[Link] MAXIMUM SALARY GIVEN TO THE EMPLOYEES
WORKING AS ANALYST
SELECT COUNT( DISTINCT SAL )
FROM EMP ;
[Link] NUMBER OF DISTINCT SALARIES PRESENT IN
EMPLOYEE TABLE
SELECT COUNT( DISTINCT JOB )
FROM EMP ;
[Link] NUMBER OF JOBS PRESENT IN EMPLOYEE TABLE
FROM EMP
WHERE JOB ='CLERK' ;
[Link] AVG SALARY GIVEN TO THE CLERK
SELECT AVG(SAL)
SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO = 10 AND JOB IN ( 'MANAGER' , 'CLERK' ) ;
[Link] MINIMUM SALARY GIVEN TO THE EMPLOYEES
WHO WORK IN DEPT 10 AS MANAGER OR A CLERK
.

New Section 1 Page 6

GROUP & FILTERING

GROUPING : GROUP BY Clause
Group by clause is used to group the records .
SYNTAX:
SELECT group_by_expression / group_function
FROM table_name
[WHERE <filter_condition>]
GROUP BY column_name/expression ;
ORDER OF EXECUTION:
1-FROM
2-WHERE(if used) [ROW-BY-ROW]
3-GROUP BY [ROW-BY-ROW]
4-SELECT [GROUP-BY-GROUP]
EMP
EID
ENAME
SAL
DEPTNO
1
A
100
20
2
B
200
10
3
C
300
30
4
D
100
10
5
E
200
10
6
A
400
30
7
C
500
20
8
F
200
30
Example :
WAQTD number of employees working in each dept .
➢
SELECT COUNT(*)
FROM EMP
GROUP BY DEPTNO ;
DAY 10
Wednesday, July 29, 2020
9:44 AM

New Section 1 Page 1

Group By clause is used to group the records .

➢
Group By clause executes row by row .
➢
After the execution of Group By clause we get Groups .
➢
Therefore any clause that executes after group by must execute
➢
Group By Group .
The Column_Name or expression used for grouping can be used
➢
In select clause .
Group By clause can be used without using Where clause .
➢
NOTE :
Questions :
WAQTD number of employees working in each dept except the
1.
Employee working as analyst .
SELECT DEPTNO , COUNT(*)
FROM EMP
WHERE JOB NOT IN 'ANALYST'
GROUP BY DEPTNO ;
WAQTD maximum salary given to each job .
2.
SELECT JOB , MAX( SAL )
FROM EMP
GROUP BY JOB ;
WAQTD number of employees working in each job if the
employees Have character 'A' in their names .
3.
SELECT JOB , COUNT(*)
FROM EMP
WHERE ENAME LIKE '%A%'
GROUP BY JOB ;
WAQTD number of employees getting commission in each
dept .
4.
SELECT DEPTNO , COUNT( COMM )

New Section 1 Page 2

FROM EMP
GROUP BY DEPTNO ;
ASSIGNMENT QUESTIONS ON GROUP BY
[Link] NUMBER OF EMPLOYEES WORKING IN EACH
DEPARTEMENT EXCEPT PRESIDENT.
[Link] TOTAL SALARY NEEDED TO PAY ALL THE
EMPLOYEES IN EACH JOB.
[Link] NUMBER OF EMPLOYEEES WORKING AS
MANAGER IN EACH DEPARTMENT .
[Link] AVG SALARY NEEDED TO PAY ALL THE
EMPLOYEES IN EACH DEPARTMENT EXCLUDING THE
EMPLOYEES OF DEPTNO 20.
[Link] NUMBER OF EMPLOYEES HAVING CHARACTER
'A' IN THEIR NAMES IN EACH JOB .
[Link] NUMBER OF EMPLOYEES AND AVG SALARY
NEEDED TO PAY THE EMPLOYEES WHO SALARY IN
GREATER THAN 2000 IN EACH DEPT.
[Link] TOTAL SALARY NEEDED TO PAY AND NUMBER
OF SALESMANS IN EACH DEPT.
[Link] NUMBER OF EMPLOYEES WITH THEIR MAXIMUM
SALARIES IN EACH JOB.
[Link] MAXIMUM SALARIES GIVEN TO AN EMPLOYEE
WORKING IN EACH DEPT.
[Link] NUMBER OF TIMES THE SALARIES PRESENT IN
EMPLOYEE TABLE .
FILTERING : HAVING Clause
" Having Clause is used to Filter the Group "
SYNTAX:
SELECT group_by_expression / group_function
FROM table_name
[WHERE <filter_condition>]
GROUP BY column_name/expression
HAVING <group_filter_condition>
ORDER OF EXECUTION:
1-FROM
2-WHERE(if used) [ROW-BY-ROW]
3-GROUP BY(if used) [ROW-BY-ROW]
4-HAVING (if used ) [GROUP-BY-GROUP]
5-SELECT [GROUP-BY-GROUP]
Example :
WAQTD to find number of employees working in each
➢
Dept if there are at least 3 employees in each dept .

New Section 1 Page 3

SELECT DEPTNO , COUNT(*)

FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*)>=3 ;
Questions :
WAQTD the designations in which there are at lest 2 employees
1.
Present .
SELECT JOB , COUNT(*)
FROM EMP
GROUP BY JOB
HAVING COUNT(*)>=2 ;
WAQTD the names that are repeated .
2.
SELECT ENAME , COUNT(*)
FROM EMP
GROUP BY ENAME
HAVING COUNT(*) > 1 ;
WAQTD names that are repeated exactly twice .
3.

New Section 1 Page 4

SELECT ENAME , COUNT(*)

FROM EMP
GROUP BY ENAME
HAVING COUNT(*) = 2 ;
WAQTD the salary that is repeated .
4.
SELECT SAL, COUNT(*)
FROM EMP
GROUP BY SAL
HAVING COUNT(*) > 1 ;
WAQTD number of employees working in each dept having
5.
At least 2 emp's Character 'A' or 'S' in their names .
SELECT DEPTNO , COUNT(*)
FROM EMP
WHERE ENAME LIKE '%A%' OR ENAME LIKE '%S%'
GROUP BY DEPTNO
HAVING COUNT(*)>=2 ;
WAQTD job and total salary of each job , if the total salary
6.
Of each job is greater than 3450 .
SELECT JOB , SUM( SAL )
FROM EMP
GROUP BY JOB
HAVING SUM( SAL ) > 3450 ;
WAQTD job and total salary of the employees if the employees
7.
Are earning more than 1500.
SELECT JOB , SUM( SAL )
FROM EMP
WHERE SAL > 1500
GROUP BY JOB ;
NOTE :
Differentiate between Where and Having .
WHERE
HAVING
Where clause is used to
➢
Filter the records
Having clause is used to
➢
Filter the groups .
Where clause executes row
➢
By row .
Having clause executes
➢
Group by group
In Where Clause we cannot
➢
Use MRF( )
Can use MRF( ).
➢
Where clause executes before
➢
Group by clause .
Having clause executes
➢
After group by clause .

New Section 1 Page 5

WAQTD Job wise maximum salary if the maximum salary
8.
Of each job exceeds 2000 .
SELECT JOB , MAX( SAL )
FROM EMP
GROUP BY JOB
HAVING MAX( SAL ) > 2000 ;
WAQTD number of emp earning sal more than 1200 in each job
and the total sal needed to pay emp of each job must exceeds
3800.
9.
SELECT JOB , COUNT(*) , SUM( SAL )
FROM EMP WHERE SAL > 1200
GROUP BY JOB
HAVING SUM( SAL ) > 3800 ;
ASSIGNMENT QUESTIONS ON HAVING CLAUSE
[Link] DNO AND NUMBER OF EMP WORKING IN EACH
DEPT IF THERE
ARE ATLEAST 2 CLERKS IN EACH DEPT
[Link] DNO AND TOTAL SAALARYNEEDED TO PAY ALL
EMP
IN EACH DEPT IF THERE ARE ATLEAST 4 EMP IN EACH
DEPT
[Link] NUMBER OF EMP EARNING SAL MORE THAN 1200
IN EACH JOB
AND THE TOTAL SAL NEEDED TO PAY EMP OF EACH JOB
MUST EXCEES 3800
[Link] DEPTNO AND NUMBER OF EMP WORKING ONLY
IF THERE ARE 2 EMP WORKING IN EACH DEPT AS
MANAGER .
[Link] JOB AND MAX SAL OF EMP IN EACH JOB IF THE
MAX SAL EXCEEDS 2600
[Link] THE SALARIES WHICH ARE REPEATED IN EMP
TABLE
[Link] THE HIREDATE WHICH ARE DUPLICATED IN EMP
TABLE
[Link] AVG SALARY OF EACH DEPT IF AVG SAL IS LESS
THAN 3000
[Link] DEPTNO IF THERE ARE ATLEAST 3 EMP IN EACH
DEPT WHOS NAME
HAS CHAR 'A' OR 'S' .
[Link] MIN AND MAX SALARIES OF EACH JOB IF MIN
SAL IS MORE THAN 1000 AND MAX SAL IS LESS THAN 5000 .
ANSWERS :
[Link] NUMBER OF EMPLOYEES WORKING IN EACH
DEPARTEMENT EXCEPT PRESIDENT

New Section 1 Page 6

SELECT DEPTNO, COUNT(*)
FROM EMP
WHERE JOB NOT IN 'PRESIDENT'
GROUP BY DEPTNO;
[Link] TOTAL SALARY NEEDED TO PAY ALL THE
EMPLOYEES IN EACH JOB
SELECT JOB , SUM(SAL)
FROM EMP
GROUP BY JOB
[Link] NUMBER OF EMPLOYEEES WORKING AS
MANAGER IN EACH DEPARTMENT
SELECT DEPTNO, COUNT(*)
FROM EMP
WHERE JOB='MANAGER'
GROUP BY DEPTNO;
[Link] AVG SALARY NEEDED TO PAY ALL THE
EMPLOYEES IN EACH DEPARTMENT EXCLUDING THE
EMPLOYEES OF DEPTNO 20
SELECT DEPTNO, AVG(SAL)
FROM EMP
WHERE DEPTNO NOT IN 20
GROUP BY DEPTNO;
[Link] NUMBER OF EMPLOYEES HAVING CHARACTER
'A' IN THEIR NAMES IN EACH JOB
SELECT JOB, COUNT(*)
FROM EMP
WHERE ENAME LIKE '%A%'
GROUP BY JOB;
[Link] NUMBER OF EMPLOYEES AND AVG SALARY
NEEDED TO PAY THE EMPLOYEES WHO SALARY IN
GREATER THAN 2000 IN EACH DEPT
SELECT DEPTNO, COUNT(*) , AVG(SAL)
FROM EMP
WHERE SAL > 2000
GROUP BY DEPTNO;
[Link] TOTAL SALARY NEEDED TO PAY AND NUMBER
OF SALESMANS IN EACH DEPT
SELECT DEPTNO, COUNT(*) , SUM(SAL)
FROM EMP
WHERE JOB='SALESMAN'
GROUP BY DEPTNO;
[Link] NUMBER OF EMPLOYEES WITH THEIR MAXIMUM

New Section 1 Page 7

SALARIES IN EACH JOB
SELECT JOB, COUNT(*) , MAX(SAL)
FROM EMP
GROUP BY JOB;
[Link] MAXIMUM SALARIES GIVEN TO AN EMPLOYEE
WORKING IN EACH DEPT
SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO;
[Link] NUMBER OF TIMES THE SALARIES PRESENT IN
EMPLOYEE TABLE
SELECT SAL , COUNT(*)
FROM EMP
GROUP BY SAL;

New Section 1 Page 8

SUB-QUERY
" A QUERY WRITTEN INSIDE ANOTHER QUERY IS KNOWN
AS SUB QUERY "
Working Principle :
Inner Query executes first and produces an Output .
➢
The Output of Inner Query is given / fed as an Input to Outer Query .
➢
The Outer Query generates the Result.
➢
Therefore we can state that 'the Outer Query is dependent on
➢
Inner Query' and this is the Execution Principle of Sub Query .
Let us consider two queries Outer Query and Inner Query .
Why / When Do we use SUB QUERY :
Case 1 : Whenever we have Unknowns present in the Question
We use sub query to find the Unknown .
Example :
EMP
EID
ENAME
SAL
DEPTNO
1
ALLEN
1000
20
2
BLAKE
2000
10
3
CLARK
3000
30
4
MILLER
1500
10
5
SMITH
2500
10
WAQTD names of the employees earning more than 2500 .
1.
SELECT ENAME
FROM EMP
DAY 11
Thursday, July 30, 2020
9:40 AM

New Section 1 Page 1

WHERE SAL > 2500 ;

WAQTD names of the employees earning less than MILLER .
2.
SELECT ENAME
FROM EMP
FROM EMP
WHERE ENAME = 'MILLER' );
WHERE SAL < ( SELECT SAL
WAQTD name and deptno of the employees working in the same
Dept as SMITH .
3.
SELECT ENAME , DEPTNO
FROM EMP
FROM EMP
WHERE ENAME ='SMITH' ) ;
WHERE DEPTNO = ( SELECT DEPTNO
WAQTD name and hiredate of the employees if the employee
4.
Was hired after JONES .
SELECT ENAME , HIREDATE
FROM EMP
FROM EMP
WHERE ENAME ='JONES' ) ;
WHERE HIREDATE > ( SELECT HIREDATE
WAQTD all the details of the employee working in the same
5.
Designation as KING .
SELECT *
FROM EMP
FROM EMP
WHERE ENAME ='KING' );
WHERE JOB = ( SELECT JOB
WAQTD name , sal , deptno of the employees if the employees
6.
Earn more than 2000 and work in the same dept as JAMES .
SELECT ENAME , SAL , DEPTNO
FROM EMP
WHERE SAL > 2000 AND DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='JAMES' ) ;
WAQTD all the details of the employees working in the
7.
Same designation as MILLER and earning more than 1500.
SELECT *
FROM EMP
FROM EMP
WHERE ENAME ='MILLER' ) ;
WHERE SAL > 1500 AND JOB = ( SELECT JOB

New Section 1 Page 2

SELECT *
FROM EMP
WHERE JOB = ( SELECT JOB
FROM EMP
WHERE ENAME ='MILLER' ) AND SAL > 1500 ;
WAQTD details of the employees earning more than SMITH
8.
But less than KING .
SELECT *
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='SMITH' ) AND SAL < ( SELECT SAL
FROM EMP
WHERE ENAME ='KING' ) ;
WAQTD name , sal and deptno of the employees if the employee Is
earning commission in dept 20 and earning salary more than Scott .
9.
SELECT ENAME , SAL , DEPTNO
FROM EMP
WHERE COMM IS NOT NULL AND DEPTNO = 20 AND
SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='SCOTT' ) ;
WAQTD name and hiredate of the employees who's name ends with
'S' and hired after James .
10.
SELECT ENAME , HIREDATE
FROM EMP
WHERE ENAME LIKE '%S' AND
HIREDATE > ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='JAMES' ) ;
WAQTD names of the employees working in the same dept as
JAMES and earning salary more than ADAMS and working in the
same job role as MILLER and hired after MARTIN .
11.
SELECT ENAME
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO
FROM EMP
WHERE ENAME='JAMES') AND
SAL>(SELECT SAL
FROM EMP
WHERE ENAME='ADAMS') AND
JOB=(SELECT JOB
FROM EMP
WHERE ENAME='MILLER') AND

New Section 1 Page 3

HIREDATE>(SELECT HIREDATE
FROM EMP
WHERE ENAME='MARTIN');
WAQTD all the details of the employees working as salesman in the
dept 20 and earning commission more than Smith and hired after
KING .
12.
SELECT *
FROM EMP
WHERE JOB ='SALESMAN' AND
DEPTNO = 20 AND
COMM > ( SELECT COMM
FROM EMP
WHERE ENAME ='SMITH' ) AND
HIREDATE > ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='KING' ) ;
WAQTD number of employees earning more than SMITH and less
than MARTIN .
13.
SELECT COUNT(*)
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='SMITH') AND
SAL < ( SELECT SAL
FROM EMP
WHERE ENAME ='MARTIN' )
WAQTD Ename and SAL for all the employees earning more than
JONES .
14.
SELECT ENAME , SAL
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME =JONES' ) ;
WAQTD all the details of the employees working as a manager .
15.
SELECT *
FROM EMP
WHERE JOB ='MANAGER' ;
In the Inner Query / Sub Query we cannot select more than One
column .
➢
The corresponding columns need not be same , but the datatypes
of those has to be same .
➢
NOTE :
ASSIGNMENT ON CASE 1

New Section 1 Page 4

[Link] NAME OF THE EMPLOYEES EARNING MORE THAN

ADAMS
[Link] NAME AND SALARY OF THE EMPLOYEES EARNING
LESS THAN KING
[Link] NAME AND DEPTNO OF THE EMPLOYEES IF THEY ARE
WORKING IN THE SAME DEPT AS JONES
[Link] NAME AND JOB OF ALL THE EMPLOYEES WORKING
IN THE SAME DESIGNATION AS JAMES
[Link] EMPNO AND ENAME ALONG WITH ANNUAL SALARY
OF ALL THEEMPLOYEES IF THEIR ANNUAL SALARY IS
GREATER THAN WARDS ANNUAL SALARY.
[Link] NAME AND HIREDATE OF THE EMPLOYEES IF THEY
ARE HIRED BEFORE SCOTT
[Link] NAME AND HIREDATE OF THE EMPLOYEES IF THEY
ARE HIRED AFTER THE PRESIDENT
[Link] NAME AND SAL OF THE EMPLOYEE IF THEY ARE
EARNING SAL LESS THAN THE EMPLOYEE WHOS EMPNO IS 7839
[Link] ALL THE DETAILS OF THE EMPLOYEES IF THE
EMPLOYEES ARE HIRED BEFORE MILLER
[Link] ENAME AND EMPNO OF THE EMPLOYEES IF
EMPLOYEES ARE EARNING MORE THAN ALLEN
[Link] ENAME AND SALARY OF ALL THE EMPLOYEES WHO
ARE EARNING MORE THAN MILLER BUT LESS THAN ALLEN .
[Link] ALL THE DETAILS OF THE EMPLOYEES WORKING IN
DEPT 20 AND WORKING IN THE SAME DESIGNATION AS SMITH
[Link] ALL THE DETAILS OF THE EMPLOYEES WORKING AS
MANAGER IN THE SAME DEPT AS TURNER
[Link] NAME AND HIREDATE OF THE EMPLOYEES HIRED
AFTER 1980 AND BEFORE KING
[Link] NAME AND SAL ALONG WITH ANNUAL SAL FOR ALL
EMPLOYEES WHOS SAL IS LESS THAN BLAKE AND MORE THAN
3500
[Link] ALL THE DETAILS OF EMPLOYEES WHO EARN MORE
THAN SCOTT BUT LESS THAN KING
[Link] NAME OF THE EMPLOYEES WHOS NAME STARTS
WITH 'A' AND WORKS IN THE SAME DEPT AS BLAKE
[Link] NAME AND COMM IF EMPLOYEES EARN COMISSION
AND WORK IN THE SAME DESIGNATION AS SMITH
[Link] DETAILS OF ALL THE EMPLOYEES WORKING AS
CLERK IN THE SAME DEPT AS TURNER .
[Link] ENAME, SAL AND DESIGNATION OF THE EMPLOYEES
WHOS ANNUAL SALARY IS MORE THAN SMITH AND LESS THAN
KING.
[Link] NAME OF THE EMPLOYEES EARNING MORE THAN
ADAMS
SELECT ENAME
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='ADAMS' );
New Section 1 Page 5

[Link] NAME AND SALARY OF THE EMPLOYEES EARNING

LESS
THAN KING
SELECT ENAME , SAL
FROM EMP
WHERE SAL < ( SELECT SAL
FROM EMP
WHERE ENAME ='KING' );
[Link] NAME AND DEPTNO OF THE EMPLOYEES IF THEY ARE
WORKING
IN THE SAME DEPT AS JONES
SELECT ENAME , DEPTNO
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME =JONES');
[Link] NAME AND JOB OF ALL THE EMPLOYEES WORKING
IN THE SAME
DESIGNATION AS JAMES
SELECT ENAME , JOB
FROM EMP
WHERE JOB = ( SELECT JOB
FROM EMP
WHERE ENAME ='JAMES' );
[Link] EMPNO AND ENAME ALONG WITH ANNUAL SALARY
OF ALL THE
EMPLOYEES IF THEIR ANNUAL SALARY IS GREATER THAN
WARDS
ANNUAL SALARY.
SELECT EMPNO , ENAME . SAL*12
FROM EMP
WHERE SAL * 12 > ( SELECT SAL*12
FROM EMP
WHERE ENAME = 'WARD' );
[Link] NAME AND HIREDATE OF THE EMPLOYEES IF THEY
ARE HIRED
BEFORE SCOTT
SELECT ENAME , HIREDATE
FROM EMP
WHERE HIREDATE < ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='SCOTT' );
[Link] NAME AND HIREDATE OF THE EMPLOYEES IF THEY
ARE HIRED
AFTER THE PRESIDENT
SELECT ENAME , HIREDATE
FROM EMP
WHERE HIREDATE > ( SELECT HIREDATE
FROM EMP

New Section 1 Page 6

WHERE JOB = 'PRESIDENT' );

[Link] NAME AND SAL OF THE EMPLOYEE IF THEY ARE
EARNING SAL
LESS THAN THE EMPLOYEE WHOS EMPNO IS 7839
SELECT ENAME , SAL
FROM EMP
WHERE SAL < ( SELECT SAL
FROM EMP
WHERE EMPNO = 7839 );
[Link] ALL THE DETAILS OF THE EMPLOYEES IF THE
EMPLOYEES ARE
HIRED BEFORE MILLER
SELECT *
FROM EMP
WHERE HIREDATE < ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='MILLER' );
[Link] ENAME AND EMPNO OF THE EMPLOYEES IF
EMPLOYEES ARE
EARNING MORE THAN ALLEN
SELECT ENAME , EMPNO
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='ALLEN' );
[Link] ENAME AND SALARY OF ALL THE EMPLOYEES WHO
ARE EARNING
MORE THAN MILLER BUT LESS THAN ALLEN
SELECT ENAME , SAL
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='MILLER ) AND SAL < ( SELECT SAL
FROM EMP
WHERE ENAME ='ALLEN' );
[Link] ALL THE DETAILS OF THE EMPLOYEES WORKING IN
DEPT 20
AND WORKING IN THE SAME DESIGNATION AS SMITH
SELECT *
FROM EMP
WHERE DEPTNO = 20 AND JOB = ( SELECT JOB
FROM EMP
WHERE ENAME ='SMITH');
[Link] ALL THE DETAILS OF THE EMPLOYEES WORKING AS
MANAGER
IN THE SAME DEPT AS TURNER
SELECT *
FROM EMP

New Section 1 Page 7

WHERE JOB ='MANAGER' AND DEPTNO = ( SELECT DEPTNO

FROM EMP
WHERE ENAME ='TURNER' ) ;
[Link] NAME AND HIREDATE OF THE EMPLOYEES HIRED
AFTER 1980
AND BEFORE KING
SELECT ENAME , HIREDATE
FROM EMP
WHERE HIREDATE > '31-DEC-1980 ' AND HIREDATE < ( SELECT
HIREDATE
FROM EMP
WHERE ENAME ='KING' );
[Link] NAME AND SAL ALONG WITH ANNUAL SAL FOR ALL
EMPLOYEES
WHOS SAL IS LESS THAN BLAKE AND MORE THAN 3500
SELECT ENAME , SAL , SAL*12
FROM EMP
WHERE SAL > 3500 AND SAL < ( SELECT SAL
FROM EMP
WHERE ENAME ='BLAKE );
[Link] ALL THE DETAILS OF EMPLOYEES WHO EARN MORE
THAN SCOTT
BUT LESS THAN KING
SELECT *
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='SCOTT') AND SAL < ( SELECT SAL
FROM EMP
WHERE ENAME ='KING' );
[Link] NAME OF THE EMPLOYEES WHOS NAME STARTS
WITH ‘A’ AND
WORKS IN THE SAME DEPT AS BLAKE
SELECT ENAME
FROM EMP
WHERE ENAME LIKE 'A%' AND DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='BLAKE' ) ;
[Link] NAME AND COMM IF EMPLOYEES EARN COMISSION
AND WORK IN
THE SAME DESIGNATION AS SMITH
SELECT ENAME , COMM
FROM EMP
WHERE COMM IS NOT NULL AND JOB = ( SELECT JOB
FROM EMP
WHERE ENAME ='SMITH');
[Link] DETAILS OF ALL THE EMPLOYEES WORKING AS

New Section 1 Page 8

CLERK IN THE
SAME DEPT AS TURNER
SELECT *
FROM EMP
WHERE JOB ='CLERK' AND DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='TURNER' ) ;
[Link] ENAME, SAL AND DESIGNATION OF THE EMPLOYEES
WHOS
ANNUAL SALARY IS MORE THAN SMITH AND LESS THAN KING
SELECT ENAME , SAL , JOB
FROM EMP
WHERE SAL*12 > ( SELECT SAL *12
FROM EMP
WHERE ENAME ='SMITH') AND SAL < ( SELECT SAL *12
FROM EMP
WHERE ENAME ='KING' );

New Section 1 Page 9

CASE-2 : Whenever the data to be selected and the condition to be

executed are present in different tables we use Sub Query .
Example :
Emp
EID
ENAME
SAL
DEPTNO
1
ALLEN
1000
20
2
BLAKE
2000
10
3
CLARK
3000
30
4
MILLER
1500
10
5
ADAMS
2500
20
WAQTD deptno of the employee whose name is Miller .
1.
SELECT DEPTNO
FROM EMP
WHERE ENAME ='MILLER' ;
WAQTD dname of the employee whose name is Miller .
2.
SELECT DNAME
FROM DEPT
FROM EMP
WHERE ENAME ='MILLER' ) ;
WHERE DEPTNO = ( SELECT DEPTNO
WAQTD Location of ADAMS
3.
SELECT LOC
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='ADAMS' ) ;
WAQTD names of the employees working in Location L2.
4.
SELECT ENAME
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE LOC ='L2' ) ;
WAQTD number of employees working in dept D3 .
5.
SELECT COUNT(*)
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
DEPT
DEPTNO DNAME LOC
10
D1
L1
20
D2
L2
30
D3
L3
DAY 12
Saturday, August 1, 2020
9:38 AM

New Section 1 Page 1

FROM DEPT
WHERE DNAME ='D3' ) ;
WAQTD ename , sal of all the employee earning more than
6.
Scott and working in dept 20 .
SELECT ENAME , SAL
FROM EMP
WHERE DEPTNO = 20 AND SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='SCOTT' ) ;
WAQTD all the details of the employee working as a Manager
7.
In the dept Accounting .
SELECT *
FROM EMP
WHERE JOB ='MANAGER' AND
DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME ='ACCOUNTING' ) ;
WAQTD all the details of the employee working in the same
designation as Miller and works in location New York .
8.
SELECT *
FROM EMP
WHERE JOB = ( SELECT JOB
FROM EMP
WHERE ENAME ='MILLER' ) AND DEPTNO = ( SELECT
DEPTNO FROM DEPT WHERE LOC ='NEW YORK' ) ;
WAQTD number of employees working as a clerk in the same
deptno as SMITH and earning more than KING hired after
MARTIN in the location BOSTON .
9.
SELECT COUNT(*)
FROM EMP
WHERE JOB ='CLERK' AND
DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='SMITH') AND
SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='KING' ) AND
HIREDATE > ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='MARTIN' ) AND
DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE LOC ='BOSTON' ) ;
WAQTD maximum salary given to a person working in
10.

New Section 1 Page 2

DALLAS .
SELECT MAX( SAL )
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE LOC ='DALLAS' ) ;
ASSIGNMENT ON CASE 2 :
[Link] DNAME OF THE EMPLOYEES WHOS NAME IS
SMITH
[Link] DNAME AND LOC OF THE EMPLOYEE WHOS
ENAME IS KING
[Link] LOC OF THE EMP WHOS EMPLOYEE NUMBER IS
7902
[Link] DNAME AND LOC ALONG WITH DEPTNO OF THE
EMPLOYEE WHOS NAME ENDS WITH 'R' .
[Link] DNAME OF THE EMPLOYEE WHOS DESIGNATION
IS PRESIDENT
[Link] NAMES OF THE EMPLOYEES WORKING IN
ACCOUNTING DEPARTMENT
[Link] ENAME AND SALARIES OF THE EMPLOYEES WHO
ARE WORKING IN THE LOCATION CHICAGO
[Link] DETAILS OF THE EMPLOYEES WORKING IN SALES
[Link] DETAILS OF THE EMP ALONG WITH ANNUAL
SALARY IF EMPLOYEES ARE WORKING IN NEW YORK
[Link] NAMES OF EMPLOYEES WORKING IN
OPERATIONS DEPARTMENT
ASSIGNMENT ON CASE 1 & 2
[Link] NAMES OF THE EMPLOYEES EARNING MORE
THAN SCOTT IN ACCOUNTING DEPT
[Link] DETAILS OF THE EMPLOYEES WORKING AS
MANAGER IN THE LOCATION CHICAGO
[Link] NAME AND SAL OF THE EMPLOYEES EARNING
MORE THAN KING IN THE DEPT ACCOUNTING
[Link] DETAILS OF THE EMPLOYEES WORKING AS
SALESMAN IN THE DEPARTEMENT SALES
[Link] NAME , SAL , JOB , HIREDATE OF THE EMPLOYEES
WORKING IN OPERATIONS DEPARTMENT AND HIRED
BEFORE KING
[Link] ALL THE EMPLOYEES WHOSE DEPARTMET
NAMES ENDING 'S'.
[Link] DNAME OF THE EMPLOYEES WHOS NAMES HAS
CHARACTER 'A' IN IT .
[Link] DNAME AND LOC OF THE EMPLOYEES WHOS
SALARY IS RUPEES 800 .
[Link] DNAME OF THE EMPLOYEES WHO EARN
COMISSION
[Link] LOC OF THE EMPLOYEES IF THEY EARN
COMISSION IN DEPT 40

New Section 1 Page 3

MAX & MIN :

EID
ENAME
SAL
DEPTNO
1
ALLEN
1000
20
2
BLAKE
2000
10
3
CLARK
3000
30
4
MILLER
1500
10
5
ADAMS
2500
20
WAQTD maximum salary of an employee .
1.
SELECT MAX( SAL )
FROM EMP ;
WAQTD name of the employee getting maximum salary .
2.
SELECT ENAME , MAX( SAL )
FROM EMP ;
SELECT ENAME
FROM EMP
WHERE SAL = MAX( SAL ) ;
SELECT ENAME
FROM EMP
WHERE SAL = ( SELECT MAX( SAL )
FROM EMP ) ;
WAQTD name and salary earned by the employee getting
3.
Minimum salary .
SELECT ENAME , SAL
FROM EMP
WHERE SAL = ( SELECT MIN( SAL )
FROM EMP ) ;
ASSIGNMENT ON MAX & MIN :
[Link] NAME OF THE EMPLOYEE EARNING MAXIMUM
SALARY
[Link] NAME OF THE EMPLOYEE EARNING MINIMUM
SALARY
[Link] NAME AND HIREDATE OF THE EMPLOYEE HIRED
BEFORE
ALL THE EMPLOYEES (FIRST EMP)
[Link] NAME AND HIREDATE OF THE EMPLOYEES HIRED
AT THE LAST
[Link] NAME, COMM OF THE EMPLOYEE WHO EARNS
MIN COMISSION
[Link] NAME, SAL AND COMM OF THE EMPLOYEE
EARNING MAXIMUM
COMISSION
New Section 1 Page 4

[Link] DETAILS OF THE EMPLOYEE WHO HAS GREATEST

EMPNO
[Link] DETAILS OF THE EMPLOYEES HAVING THE LEAST
HIREDATE
[Link] DETAILS OF THE EMPLOYEES EARNING LEAST
ANNUAL SALARY
[Link] NAME , ANNUAL SALARY OF THE EMPLOYEES IF
THEIR ANNUAL SALARY IS MORE THAN ALL THE SALESMAN
ASSIGNMENT ANSWERS ON CASE 2 :
[Link] DNAME OF THE EMPLOYEES WHOS NAME IS
SMITH
SELECT DNAME
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME ='SMITH' ) ;
[Link] DNAME AND LOC OF THE EMPLOYEE WHOS
ENAME IS KING
SELECT DNAME ,LOC
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME =''KING' ) ;
[Link] LOC OF THE EMP WHOS EMPLOYEE NUMBER IS
7902
SELECT LOC
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE EMPNO=7902 ) ;
[Link] DNAME AND LOC ALONG WITH DEPTNO OF THE
EMPLOYEE WHO’S NAME ENDS WITH ‘R’.
SELECT DNAME , LOC
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE ENAME LIKE '%R' ) ;
[Link] DNAME OF THE EMPLOYEE WHOS DESIGNATION
IS PRESIDENT
SELECT DNAME
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE JOB = 'PRESIDENT' ) ;
[Link] NAMES OF THE EMPLOYEES WORKING IN

New Section 1 Page 5

ACCOUNTING DEPARTMENT
SELECT ENAME
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME = 'ACCOUNTING' );
[Link] ENAME AND SALARIES OF THE EMPLOYEES WHO
ARE WORKING IN
THE LOCATION ‘CHICAGO’
SELECT ENAME ,SAL
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE LOC = 'CHICAGO' );
[Link] DETAILS OF THE EMPLOYEES WORKING IN SALES
SELECT *
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME ='SALES' );
[Link] DETAILS OF THE EMP ALONG WITH ANNUAL
SALARY IF EMPLOYEES
ARE WORKING IN NEW YORK
SELECT EMP.* , SAL*12
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE LOC ='NEW YORK' );
[Link] NAMES OF EMPLOYEES WORKING IN
OPERATIONS DEPARTMENT
SELECT ENAME
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME ='OPERATIONS' );
ANSWERS ON CASE 1 & 2 :
[Link] NAMES OF THE EMPLOYEES EARNING MORE
THAN SCOTT IN
ACCOUNTING DEPT
SELECT ENAME
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='SCOTT' ) AND DEPTNO = ( SELECT DEPTNO
FROM DEPT

New Section 1 Page 6

WHERE DNAME =
'ACCOUNTING');
[Link] DETAILS OF THE EMPLOYEES WORKING AS
MANAGER IN THE
LOCATION CHICAGO
SELECT *
FROM EMP
WHERE JOB ='MANAGER' AND DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE LOC
='CHICAGO');
[Link] NAME AND SAL OF THE EMPLOYEES EARNING
MORE THAN KING
IN THE DEPT ACCOUNTING
SELECT ENAME ,SAL
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP
WHERE ENAME ='KING' ) AND DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME =
'ACCOUNTING');
[Link] DETAILS OF THE EMPLOYEES WORKING AS
SALESMAN IN THE
DEPARTEMENT SALES
SELECT *
FROM EMP
WHERE JOB ='SALESMAN' AND DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME
='SALES');
[Link] NAME , SAL , JOB , HIREDATE OF THE EMPLOYEES
WORKING IN OPERATIONS DEPARTMENT AND HIRED
BEFORE KING
SELECT ENAME ,SAL , JOB , HIREDATE
FROM EMP
WHERE HIREDATE < ( SELECT HIREDATE
FROM EMP
WHERE ENAME ='KING') AND DEPTNO = ( SELECT DEPTNO
 FROM DEPT
WHERE DNAME
='OPERATIONS');
[Link] ALL THE EMPLOYEES WHOSE DEPARTMET
NAMES ENDING 'S'.
SELECT ENAME
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM DEPT
WHERE DNAME LIKE '%S' );

New Section 1 Page 7

[Link] DNAME OF THE EMPLOYEES WHOS NAMES HAS

CHARACTER ‘A’ IN IT .
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN( SELECT DEPTNO
FROM EMP
WHERE ENAME LIKE '%A%' ) ;
[Link] DNAME AND LOC OF THE EMPLOYEES WHOS
SALARY IS RUPEES 800 .
SELECT DNAME , LOC
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE SAL = 800) ;
[Link] DNAME OF THE EMPLOYEES WHO EARN
COMISSION
SELECT DNAME
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE COMM IS NOT NULL );
[Link] LOC OF THE EMPLOYEES IF THEY EARN
COMISSION IN DEPT 40
SELECT LOC
FROM DEPT
WHERE DEPTNO = 40 AND DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE COMM IS NOT NULL ) ;
SELECT LOC
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE COMM IS NOT NULL AND DEPTNO = 40 ) ;
ANSWERS ON MAX & MIN :
[Link] NAME OF THE EMPLOYEE EARNING MAXIMUM
SALARY
SELECT ENAME
FROM EMP
WHERE SAL = ( SELECT MAX(SAL)
FROM EMP );
[Link] NAME OF THE EMPLOYEE EARNING MINIMUM
SALARY
SELECT ENAME
FROM EMP
WHERE SAL = ( SELECT MIN(SAL)
FROM EMP );

New Section 1 Page 8

[Link] NAME AND HIREDATE OF THE EMPLOYEE HIRED

BEFORE
ALL THE EMPLOYEES (FIRST EMP)
SELECT ENAME , HIREDATE
FROM EMP
WHERE HIREDATE = ( SELECT MIN(HIREDATE)
FROM EMP );
[Link] NAME AND HIREDATE OF THE EMPLOYEES HIRED
AT THE LAST
SELECT ENAME , HIREDATE
FROM EMP
WHERE HIREDATE = ( SELECT MAX(HIREDATE)
FROM EMP );
[Link] NAME, COMM OF THE EMPLOYEE WHO EARNS
MIN COMISSION
SELECT ENAME , COMM
FROM EMP
WHERE COMM= ( SELECT MIN(COMM)
FROM EMP );
[Link] NAME, SAL AND COMM OF THE EMPLOYEE
EARNING MAXIMUM
COMISSION
SELECT ENAME ,SAL, COMM
FROM EMP
WHERE COMM= ( SELECT MAX(COMM)
FROM EMP );
[Link] DETAILS OF THE EMPLOYEE WHO HAS GREATEST
EMPNO
SELECT *
FROM EMP
WHERE EMPNO= ( SELECT MAX(EMPNO)
FROM EMP );
[Link] DETAILS OF THE EMPLOYEES HAVING THE LEAST
HIREDATE
SELECT *
FROM EMP
WHERE EMPNO= ( SELECT MIN(EMPNO)
FROM EMP );
[Link] DETAILS OF THE EMPLOYEES EARNING LEAST
ANNUAL SALARY
SELECT ENAME
FROM EMP
WHERE SAL*12= ( SELECT MIN(SAL*12)
FROM EMP );
[Link] NAME , ANNUAL SALARY OF THE EMPLOYEES IF

New Section 1 Page 9

THEIR ANNUAL
SALARY IS MORE THAN ALL THE SALESMAN
SELECT ENAME , SAL*12
FROM EMP
WHERE SAL*12 > ( SELECT MAX(SAL*12)
FROM EMP
WHERE JOB ='SALESMAN' );
OR
SELECT ENAME , SAL*12
FROM EMP
WHERE SAL*12 > ALL ( SELECT SAL*12
FROM EMP
WHERE JOB ='SALESMAN' );

New Section 1 Page 10

TYPES OF SUB - QUERY :

SINGLE ROW SUB QUERY
1.
MULTI ROW SUB QUERY
2.
Example :
Emp
EID
ENAME
SAL
DEPTNO
1
ALLEN
1000
20
2
BLAKE
2000
10
3
CLARK
3000
30
4
MILLER
1500
10
5
SMITH
2500
10
WAQTD dname of ALLEN .
1.
SELECT DNAME
FROM DEPT
FROM EMP
WHERE ENAME = 'ALLEN' ) ;
WHERE DEPTNO = ( SELECT DEPTNO
WAQTD dnames of allen and smith .
2.
SELECT DNAME
FROM DEPT
FROM EMP
WHERE ENAME IN
( 'ALLEN' ,'SMITH' ) ) ;
WHERE DEPTNO = ( SELECT DEPTNO
SINGLE ROW SUB QUERY :
1.
If the sub query returns exactly 1 record / value we call it as
➢
Single Row Sub Query .
If it returns only 1 value then we can use the normal
operators Or the Special Operators to compare the values .
➢
MULTI ROW SUB QUERY :
2.
DEPT
DEPTNO DNAME LOC
10
D1
L1
20
D2
L2
30
D3
L3
20
20
10
DEPTNO
20
10
30
10
10
Here , since the sub query returns
2 records we cannot use '=' Op .
We've to use IN Op .
DAY 13
Monday, August 3, 2020
9:47 AM

New Section 1 Page 1

If the sub query returns more than1 record / value we call it

as Multi Row Sub Query .
➢
If it returns more than 1 value then we cannot use the
normal operators We have to use only Special Operators to
compare the values .
➢
Note : It is difficult to identify whether a query Belongs Single or Multi
row So , it is always recommended to use Special Operators to
Compare The values .
WAQTD ename and salary of the employees earning more than
1.
Employees of dept 10 .
EID
ENAME
SAL
DEPTNO
1
ALLEN
1000
20
2
BLAKE
2000
10
3
CLARK
3000
30
4
MILLER
1500
10
5
SMITH
2500
10
SELECT ENAME , SAL
FROM EMP
FROM EMP
WHERE DEPTNO = 10 ) ;
WHERE SAL > ( SELECT SAL
Sub Query Operators :
ALL :
1.
"It is special Op used along with a relational Op ( > , < , > = , <= )
to compare the values present at the RHS ".
ALL Op returns true if all the values at the RHS have satisfied the
condition .
➢
Example :
2000
1500
2500
Here we cannot use > symbol to compare
Multiple values .
We cant use IN or. NOT IN as well because
It is used for = and != symbols .
Therefore we have to use Sub Query Operators
For Comparing Relational Operators such as
( > , < , >= , <= ) .
2000
CLARK 3000

New Section 1 Page 2

Example :
SELECT ENAME , SAL
FROM EMP
FROM EMP
WHERE DEPTNO = 10 ) ;
1000 > ALL ( 2000 , 1500 , 2500 )
1000 > 2000 False
1000 > 1500 False
1000 > 2500
False
2000 > ALL ( 2000 , 1500 , 2500 )
2000 > 2000 False
2000 > 1500
True
2000 > 2500
False
3000 > ALL ( 2000 , 1500 , 2500 )
3000 > 2000
True
3000 > 1500
True
3000 > 2500
True
1500 > ALL ( 2000 , 1500 , 2500 )
1500 > 2000
False
1500 > 1500
False
1500 > 2500
False
2500 > ALL ( 2000 , 1500 , 2500 )
2500 > 2000
True
2500 > 1500
True
2500 > 2500
False
WHERE SAL > ALL ( SELECT SAL
ANY :
2.
"It is special Op used along with a relational Op ( > , < , > = , <= )
to compare the values present at the RHS ".
ANY Op returns true if one of the values at the RHS have satisfied
the condition .
•
2000
1500
2500
SAL
1000
2000
3000
1500
2500
CLARK ,3000

New Section 1 Page 3

Example :
SELECT ENAME , SAL
FROM EMP
FROM EMP
WHERE DEPTNO = 10 ) ;
1000 > ANY ( 2000 , 1500 , 2500 )
1000 > 2000 False
1000 > 1500 False
1000 > 2500
False
2000 > ANY ( 2000 , 1500 , 2500 )
2000 > 2000 False
2000 > 1500
True
2000 > 2500
False
3000 > ANY ( 2000 , 1500 , 2500 )
3000 > 2000
True
3000 > 1500
True
3000 > 2500
True
1500 > ANY ( 2000 , 1500 , 2500 )
1500 > 2000
False
1500 > 1500
False
1500 > 2500
False
2500 > ANY ( 2000 , 1500 , 2500 )
2500 > 2000
True
2500 > 1500
True
2500 > 2500
False
WHERE SAL > ANY ( SELECT SAL
WAQTD name of the employee if the employee earns less than
1.
The employees working as salesman .
SELECT ENAME
FROM EMP
WHERE SAL < ALL ( SELECT SAL
2000
1500
2500
SAL
1000
2000
3000
1500
2500

New Section 1 Page 4

FROM EMP
WHERE JOB='SALESMAN' ) ;
WAQTD name of the employee if the employee earns less than
2.
At least a salesman .
SELECT ENAME
FROM EMP
WHERE SAL < ANY ( SELECT SAL
FROM EMP
WHERE JOB ='SALESMAN' ) ;
WAQTD names of the employees earning more than ADAMS .
3.
SELECT ENAME
FROM EMP
WHERE SAL > ALL ( SELECT SAL
FROM EMP
WHERE ENAME ='ADAMS' ) ;
ASSIGNMENT ON TYPES OF SUB QUERY .
[Link] NAME OF THE EMPLOYEES EARNING SALARY
MORE THAN THE SALESMAN
[Link] DETAILS OF THE EMPLOYEES HIRED AFTER ALL
THE CLERKS
[Link] NAME AND SALARY FOR ALL THE EMPLOYEES IF
THEY ARE EARNING LESS THAN ATLEST A MANAGER
[Link] NAME AND HIREDATE OF EMPLOYEES HIRED
BEFORE ALL THE MANAGERS
[Link] NAMES OF THE EMPLOYEES HIRED AFTER ALL
THE MANAGERS AND EARNING SALARY MORE THAN ALL
THE CLERKS
[Link] DETAILS OF THE EMPLOYEES WORKING AS
CLERK AND HIRED BEFORE ATLEST A SALESMAN
[Link] DETAILS OF EMPLOYEES WORKING IN
ACCOUNTING OR SALES DEPT
[Link] DEPARTMENT NAMES OF THE EMPOYEES WITH
NAME SMITH , KING AND MILLER
[Link] DETAILS OF EMPLOYEES WORKING NEWYORK
OR CHICAGO
[Link] EMP NAMES IF EMPLOYEES ARE HIRED AFTER
ALL THE EMPLOYEES OF DEPT 10
INSTAGRAM - ( ro_sql_helpmate )
NESTED SUB QUERY :
" A sub query written inside a sub query is known as Nested Subquery "
SAL
1000
WE CAN NEST ABOUT 255 SUB QUERIES
➢

New Section 1 Page 5

1000
2000
4000
3000
5000
WAQTD maximum salary given to an employee .
1.
SELECT MAX( SAL )
FROM EMP ;
WAQTD second maximum salary given to an employee .
2.
SELECT MAX( SAL )
FROM MP
FROM EMP ) ;
WHERE SAL < ( SELECT MAX( SAL)
WAQTD 3rd maximum salary .
3.
SELECT MAX( SAL ) 3000
FROM EMP
WHERE SAL < ( SELECT MAX( SAL ) 4000
FROM EMP
WHERE SAL < ( SELECT MAX( SAL ) 5000
FROM EMP ) )
WAQTD 4th maximum salary .
4.
SELECT MAX( SAL ) 2000
FROM EMP
WHERE SAL < ( SELECT MAX( SAL ) 3000
FROM EMP
WHERE SAL < ( SELECT MAX( SAL ) 4000
FROM EMP
WHERE SAL < ( SELECT MAX( SAL ) 5000
FROM EMP ) ) )
WAQTD 3 minimum salary .
5.
SELECT MIN(SAL )
5000
5000
SAL
1000
2000
4000
3000
5000
4000
Q

New Section 1 Page 6

FROM EMP
FROM EMP
FROM EMP ) ) ;
WHERE SAL > ( SELECT MIN ( SAL )
WHERE SAL > ( SELECT MIN(SAL )
WAQTD Dept name of the employee getting 2nd Minimum
salary .
6.
SELECT DNAME
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE SAL = (SELECT MIN( SAL )
FROM EMP
WHERE SAL > ( SELECT MIN( SAL )
FROM EMP ) ) );
REMEMBER :
MAXIMUM MAX( ) <
MINIMUM
MIN( )
>
ASSIGNMENT ON NESTED SUB QUERY :
[Link] 2ND MINIMUM SALARY
[Link] 5TH MAXIMUM SALARY
[Link] NAME OF THE EMPLOYEE EARNING 3RD
MAXIMUM SALARY
[Link] EMPNO OF THE EMPLOYEE EARNING 2D
MAXIMUM SALARY
[Link] DEPARTMENT NAME OF AN EMPLOYEE GETTING
4TH MAX SAL
[Link] DETAILS OF THE EMPLOYEE WHO WAS HIRED 2nd
[Link] NAME OF THE EMPLOYEE HIRED BEFORE THE
LAST EMPLOYEE
[Link] LOC OF THE EMPLOYEE WHO WAS HIRED FIRST
[Link] DETAILS OF THE EMPLOYEE EARNING 7TH
MINIMUM SALARY
[Link] DNAME OF EMPLOYEE GETTING 2ND MAXIMUM
SALARY

New Section 1 Page 7

EMPLOYEE AND MANAGER RELATION :

EID
ENAME MGR
1
ALLEN
3
2
SMITH
1
3
JAMES
2
4
KING
3
CASE 1 :
WAQTD name of Allen's manager .
➢
SELECT ENAME
FROM EMP
FROM EMP
WHERE ENAME ='ALLEN' )
WHERE EID = ( SELECT MGR
WAQTD name of SMITH's manager .
➢
SELECT ENAME
FROM EMP
FROM EMP
WHERE ENAME ='SMITH' ) ;
WHERE EID = ( SELECT MGR
WAQTD name of SMITH's manager's manager .
➢
EID
ENAME MGR
1
ALLEN
3
2
SMITH
1
3
JAMES
2
4
KING
3
SELECT ENAME
FROM EMP
WHERE EID = ( SELECT MGR
FROM EMP
WHERE EID = ( SELECT MGR
FROM EMP
3
1
2
3
4
JAMES
DAY 14
Tuesday, August 4, 2020
9:37 AM

New Section 1 Page 1

WHERE ENAME ='SMITH' ) ) ;

WAQTD dname of King's Manager .
➢
SELECT DNAME
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE EID = ( SELECT MGR
FROM EMP
WHERE ENAME ='KING' ) ) ;
WAQTD Location of Adams's manager's manager .
➢
SELECT LOC
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE EID = ( SELECT MGR
FROM EMP
WHERE EID = ( SELECT MGR
FROM EMP
WHERE ENAME ='ADAMS' ) ) ) ;
CASE -2
WAQTD Names of the employees reporting to KING.
➢
SELECT ENAME
FROM EMP
WHERE MGR = ( SELECT EID
FROM EMP
WHERE ENAME ='KING' ) ;
WAQTD Name and salary given to the employees reporting
➢
To James .
SELECT ENAME , SAL
FROM EMP
WHERE MGR = ( SELECT EID
FROM EMP
WHERE ENAME ='JAMES' ) ;
To find Manager
Select MGR in Sub Q
To find Employees Select EID in Sub Q
WAQTD dname of the employee reporting to President .
➢
SELECT DNAME
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO

New Section 1 Page 2

FROM EMP
WHERE MGR = ( SELECT EID
FROM EMP
WHERE JOB ='PRESIDENT' ) ) ;
WAQTD Department details of the employees who are reporting
to MILLER .
➢
SELECT *
FROM DEPT
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE MGR = ( SELECT EID
FROM EMP
WHERE ENAME ='MILLER' ));
ASSIGNMENT ON EMP AND MANAGER RELATION .
[Link] SMITHS REPORTING MANAGER'S NAME
[Link] ADAMS MANAGER'S MANAGER NAME
[Link] DNAME OF JONES MANAGER
[Link] MILLER'S MANAGER'S SALARY
[Link] LOC OF SMITH'S MANAGER'S MANAGER.
[Link] NAME OF THE EMPLOYEES REPORTING TO
BLAKE
[Link] NUMBER OF EMPLPOYEES REPORTING TO KING
[Link] DETAILS OF THE EMPLOYEES REPORTING TO
JONES
[Link] ENAMES OF THE EMPLOYEES REPORTING TO
BLAKE'S MANAGER
[Link] NUMBER OF EMPLOYEES REPORTING TO FORD'S
MANAGER
SUB QUERY :
What is Sub Query ?
➢
Explain ? ( draw )
➢
Why ? When ?
➢
Single Row Sub Query
▪
Multi Row Sub Query
▪
Types of Sub Query
➢
ALL
▪
ANY
▪
Sub Query Operators
➢
Nested Sub Query .
➢
[Link] SMITHS REPORTING MANAGER'S NAME
SELECT ENAME
FROM EMP
New Section 1 Page 3

WHERE EID=( SELECT MGR

FROM EMP
WHERE ENAME ='SMITH' );
[Link] ADAMS MANAGER'S MANAGER NAME
SELECT ENAME
FROM EMP
WHERE EID=( SELECT MGR
FROM EMP
WHERE ENAME ='ADAMS' );
[Link] DNAME OF JONES MANAGER
SELECT DNAME
FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO
FROM EMP
WHERE EID=( SELECT MGR
FROM EMP
WHERE ENAME ='JONES' ));
[Link] MILLER'S MANAGER'S SALARY
SELECT SAL
FROM EMP
WHERE EID=( SELECT MGR
FROM EMP
WHERE ENAME ='MILLER' );
[Link] LOC OF SMITH'S MANAGER'S MANAGER.
SELECT LOC
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM EMP
WHERE EID = ( SELECT MGR
FROM EMP
WHERE EID=( SELECT MGR
FROM EMP
WHERE ENAME ='JONES' )));
[Link] NAME OF THE EMPLOYEES REPORTING TO BLAKE
SELECT ENAME
FROM EMP
WHERE MGR=( SELECT EID
FROM EMP
WHERE ENAME ='BLAKE' );
[Link] NUMBER OF EMPLPOYEES REPORTING TO KING
SELECT COUNT(ENAME)
FROM EMP
WHERE MGR=( SELECT EID
FROM EMP
WHERE ENAME ='KING' );
[Link] DETAILS OF THE EMPLOYEES REPORTING TO

New Section 1 Page 4

JONES
SELECT *
FROM EMP
WHERE MGR=( SELECT EID
FROM EMP
WHERE ENAME ='JONES' );
[Link] ENAMES OF THE EMPLOYEES REPORTING TO
BLAKE'S MANAGER
SELECT ENAME
FROM EMP
WHERE MGR =( SELECT EID
FROM EMP
WHERE EID = ( SELECT MGR
FROM EMP
WHERE ENAME ='BLAKE' ) );
OR
SELECT ENAME
FROM EMP
WHERE MGR =( SELECT MGR
FROM EMP
WHERE ENAME ='BLAKE' );
[Link] NUMBER OF EMPLOYEES REPORTING TO FORD'S
MANAGER
SELECT COUNT(ENAME)
FROM EMP
WHERE MGR =( SELECT MGR
FROM EMP
WHERE ENAME ='FORD' );

New Section 1 Page 5

JOINS
"The process of retrieval of data from multiple tables simultaneously
is known as JOINS ".
Types of JOINS .
We have 5 types of joins
CARTESIAN JOIN / CROSS JOIN
1.
INNER JOIN / EQUI JOIN
2.
LEFT OUTER JOIN
i.
RIGHT OUTER JOIN
ii.
FULL OUTER JOIN
iii.
OUTER JOIN
3.
SELF JOIN
4.
NATURAL JOIN .
5.
CARTESIAN JOIN / CROSS JOIN :
1.
In Cartesian Join a record from table 1 will be merged with
All the records of table 2 .
EMP
ENAME DEPTNO
A
20
B
30
DNAME DEPTNO
D1
10
D2
20
DEPT
DAY 15
Wednesday, August 5, 2020
9:33 AM

New Section 1 Page 1

A
20
B
30
C
10
Number of Columns in the Result table : will be equivalent to
the summations of columns present in both the tables .
➢
2+2
= 4 Columns
Number of Col = Number of Col T1 + Number of Col T2
Number of Rows in the Result table : will be equivalent to the
product of number of rows present in the both the tables .
➢
3x3
= 9 Rows .
Number of Rows = Number of Rows T1 x Number of Rows T2
Result Table :
ENAME DEPTNO DNAME DEPTNO
A
20
D1
10
A
20
D2
20
A
20
D3
30
B
30
D1
10
B
30
D2
20
B
30
D3
30
C
10
D1
10
C
10
D2
20
C
10
D3
30
SYNTAX:
ANSI [ American National Standard Institute ]
1.
SELECT Column_Name
FROM Table_Name1 CROSS JOIN Table_Name2 ;
Oracle
2.
SELECT Column_Name
FROM Table_Name1 , Table_Name2 ;
Example :
D1
10
D2
20
D3
30

New Section 1 Page 2

WAQTD ename and dept name for all the employees .

1.
SELECT ENAME , DNAME
FROM EMP , DEPT ;
SELECT ENAME , DNAME
FROM EMP CROSS JOIN DEPT ;
INNER JOIN :
2.
"It is used to Obtain only Matching Records "
Or " A records which has a Pair " .
Are merged .
JOIN Condition : It is a condition on which the two tables
Syntax: Table_Name1.Col_Name = Table_Name2.Col_Name
Join Condition :[Link] = [Link]
20 = 10 False
20 = 20 True
20 = 30 False
Result Table :
ENAME [Link] DNAME [Link]
A
20
D2
20
B
30
D3
30
C
10
D1
10
SYNTAX:
ANSI [ American National Standard Institute ]
1.
SELECT Column_Name
FROM Table_Name1 INNER JOIN Table_Name2
ON < JOIN_CONDITION> ;
EMP
ENAME DEPTNO
A
20
B
30
C
10
DNAME DEPTNO
D1
10
D2
20
D3
30
DEPT
30 = 10 False
30 = 20 False
30 = 30 True
10 = 10 True
10 = 20 False
10 = 30 False

New Section 1 Page 3

FROM Table_Name1 INNER JOIN Table_Name2

ON < JOIN_CONDITION> ;
SELECT *
FROM EMP INNER JOIN DEPT
ON [Link] = [Link] ;
Oracle
2.
SELECT Column_Name
FROM Table_Name1 , Table_Name2
WHERE <JOIN_CONDITION > ;
SELECT *
FROM EMP , DEPT
WHERE [Link] = [Link] ;
WAQTD ename and dept name for all the employees .
1.
SELECT ENAME , DNAME
FROM EMP , DEPT
WHERE [Link] = [Link] ;
WAQTD ename and loc for all the employees working as
Manager .
2.
SELECT ENAME , LOC
FROM EMP , DEPT
WHERE [Link] = [Link] AND JOB
='MANAGER' ;
WAQTD ename , sal and dname of the employee working as
Clerk in dept 20 with a salary of more than 1800 .
3.
SELECT ENAME , SAL , DNAME
FROM EMP , DEPT
WHERE [Link] =[Link] AND
[Link] = 20 AND JOB ='CLERK' AND SAL > 1800 ;
WAQTD ename deptno , dname and loc of the employee earning
more than 2000 in New York .
4.
SELECT ENAME , [Link] , DNAME
FROM EMP , DEPT
WHERE [Link] = [Link] AND SAL > 2000
AND LOC ='NEW YORK' ;
ASSIGNMENT ON INNER JOIN :
[Link] OF THE EMPLOYEE AND HIS LOCATION OF ALL THE
EMPLOYEES .
[Link] DNAME AND SALARY FOR ALL THE EMPLOYEE
WORKING IN ACCOUNTING.

New Section 1 Page 4

[Link] DNAME AND ANNUAL SALARY FOR ALL

EMPLOYEES WHOS SALARY IS MORE THAN 2340
[Link] ENAME AND DNAME FOR EMPLOYEES HAVING
CAHARACTER 'A' IN THEIR DNAME
[Link] ENAME AND DNAME FOR ALL THE EMPLOYEES
WORKING AS SALESMAN
[Link] DNAME AND JOB FOR ALL THE EMPLOYEES WHOS
JOB AND DNAME STARTS WITH CHARACTER 'S'
[Link] DNAME AND MGR NO FOR EMPLOYEES
REPORTING TO 7839
[Link] DNAME AND HIREDATE FOR EMPLOYEES HIRED
AFTER 83 INTO ACCOUNTING OR RESEARCH DEPT
[Link] ENAME AND DNAME OF THE EMPLOYEES WHO
ARE GETTING COMM IN DEPT 10 OR 30
[Link] DNAME AND EMPNO FOR ALL THE EMPLOYEES
WHO'S EMPNO ARE (7839,7902) AND ARE WORKING IN LOC
NEW YORK.
Answers :
[Link] OF THE EMPLOYEE AND HIS LOCATION OF ALL THE
EMPLOYEES .
SELECT ENAME , LOC
FROM EMP , DEPT
WHERE [Link] = [Link] ;
[Link] DNAME AND SALARY FOR ALL THE EMPLOYEE
WORKING IN
ACCOUNTING.
SELECT DNAME , SAL
FROM EMP , DEPT
WHERE [Link] = [Link]
AND DNAME ='ACCOUNTING';
[Link] DNAME AND ANNUAL SALARY FOR ALL
EMPLOYEES WHOS SALARY
IS MORE THAN 2340
SELECT DNAME , SAL*12
FROM EMP , DEPT
WHERE [Link] = [Link]
AND SAL > 2340 ;
[Link] ENAME AND DNAME FOR EMPLOYEES HAVING
CAHARACTER 'A'
IN THEIR DNAME
SELECT ENAME , DNAME
FROM EMP , DEPT
WHERE [Link] = [Link]
AND ENAME LIKE '%A%' ;
[Link] ENAME AND DNAME FOR ALL THE EMPLOYEES
WORKING AS SALESMAN
SELECT ENAME , DNAME
FROM EMP DEPT
CNAME CID
Customer

New Section 1 Page 5

[Link] ENAME AND DNAME FOR ALL THE EMPLOYEES

WORKING AS SALESMAN
SELECT ENAME , DNAME
FROM EMP , DEPT
WHERE [Link] = [Link]
AND JOB ='SALESMAN' ;
[Link] DNAME AND JOB FOR ALL THE EMPLOYEES WHOS
JOB AND DNAME
STARTS WITH CHARACTER 'S'
SELECT DNAME ,JOB
FROM EMP , DEPT
WHERE [Link] = [Link]
AND JOB LIKE 'S%' AND DNAME LIKE 'S%' ;
[Link] DNAME AND MGR NO FOR EMPLOYEES
REPORTING TO 7839
SELECT DNAME , MGR
FROM EMP , DEPT
WHERE [Link] = [Link]
AND MGR = 7839 ;
[Link] DNAME AND HIREDATE FOR EMPLOYEES HIRED
AFTER 83 INTO
ACCOUNTING OR RESEARCH DEPT
SELECT DNAME , HIREDATE
FROM EMP , DEPT
WHERE [Link] = [Link]
AND HIREDATE > '31-DEC-83' AND DNAME IN
('ACCOUNTING' ,'RESEARCH' );
[Link] ENAME AND DNAME OF THE EMPLOYEES WHO
ARE GETTING COMM
IN DEPT 10 OR 30
SELECT ENAME , DNAME
FROM EMP , DEPT
WHERE [Link] = [Link]
AND COMM IS NOT NULL AND [Link] IN ( 10 , 30 ) ;
[Link] DNAME AND EMPNO FOR ALL THE EMPLOYEES
WHO'S EMPNO ARE
(7839,7902) AND ARE WORKING IN LOC NEW YORK.
SELECT DNAME , EMPNO
FROM EMP , DEPT
WHERE [Link] = [Link]
AND EMPNO IN (7839,7902) AND LOC = 'NEW YORK' ;
CNAME CID
X
101
Y
102
Z
103
Customer

New Section 1 Page 6

OUTER JOIN
"It is used to Obtain Un-Matched Records "
Left Outer Join :
1.
"It is used to obtain Un-Matched Records of Left Table
Along with Matching Records ".
Example :
EMP
ENAME DEPTNO
A
20
B
Null
C
10
D
Null
Result Table :
ENAME [Link] DNAME [Link]
A
20
D2
20
C
10
D1
10
B
Null
Null
Null
D
Null
Null
Null
SYNTAX:
ANSI [ American National Standard Institute ]
1.
SELECT Column_Name
FROM Table_Name1 LEFT [OUTER] JOIN Table_Name2
ON < JOIN_CONDITION> ;
SELECT *
FROM EMP LEFT JOIN DEPT
ON [Link] = [Link] ;
Oracle
2.
SELECT Column Name
DNAME DEPTNO
D1
10
D2
20
D3
30
D4
40
DEPT
Left
Right
DAY 16
Thursday, August 6, 2020
9:34 AM

New Section 1 Page 1

Oracle
2.
SELECT Column_Name
FROM Table_Name1 , Table_Name2
WHERE Table1.Col_Name = Table2.Col_Name (+) ;
SELECT *
FROM EMP , DEPT
WHERE [Link] = [Link] (+) ;
WAQTD names and dnames of all the employees even though
the employees Don’t work in any dept .
➢
SELECT ENAME , DNAME
FROM EMP , DEPT
WHERE [Link] = [Link](+) ;
ENAME DNAME
A
D2
C
D1
B
Null
D
Null
Right Outer Join :
2.
"It is used to obtain Un-Matched Records of Right
Table Along with Matching Records ".
Example :
EMP
ENAME DEPTNO
A
20
B
Null
C
10
D
Null
Result Table :
ENAME [Link] DNAME [Link]
A
20
D2
20
C
10
D1
10
Null
Null
D3
30
DNAME DEPTNO
D1
10
D2
20
D3
30
D4
40
DEPT
Left
Right

New Section 1 Page 2

A
20
D2
20
C
10
D1
10
Null
Null
D3
30
Null
Null
D4
40
SYNTAX:
ANSI [ American National Standard Institute ]
1.
SELECT Column_Name
FROM Table_Name1 RIGHT[OUTER] JOIN Table_Name2
ON < JOIN_CONDITION> ;
SELECT *
FROM EMP RIGHT JOIN DEPT
ON [Link] = [Link] ;
Oracle
2.
SELECT Column_Name
FROM Table_Name1 , Table_Name2
WHERE Table1.Col_Name (+) = Table2.Col_Name ;
SELECT *
FROM EMP , DEPT
WHERE [Link](+) = [Link] ;
WAQTD names and dnames of all the employees even though
the there are no employees in a dept .
➢
SELECT ENAME , DNAME
FROM EMP , DEPT
WHERE [Link](+) = [Link] ;
ENAME DNAME
A
D2
C
D1
Null
D3
Null
D4
Full Outer Join :
3.
"It is used to obtain Un-Matched Records of both Left
& Right Table Along with Matching Records ".
Example :
EMP
DEPT
New Section 1 Page 3

Example :
EMP
ENAME DEPTNO
A
20
B
Null
C
10
D
Null
Result Table :
ENAME [Link] DNAME [Link]
A
20
D2
20
C
10
D1
10
B
Null
Null
Null
D
Null
Null
Null
Null
Null
D3
30
Null
Null
D4
40
SYNTAX:
ANSI [ American National Standard Institute ]
1.
SELECT Column_Name
FROM Table_Name1 FULL [OUTER] JOIN Table_Name2
ON < JOIN_CONDITION> ;
SELECT *
FROM EMP FULL JOIN DEPT
ON [Link] = [Link] ;
WAQTD names and dnames of all the employees and depts
even though the employees Don’t work in any dept and a dept
having no employees .
➢
SELECT ENAME , DNAME
FROM EMP FULL OUTER DEPT
ON [Link] = [Link];
ENAME DNAME
A
D2
C
D1
B
Null
D
Null
DNAME DEPTNO
D1
10
D2
20
D3
30
D4
40
DEPT
Left
Right

New Section 1 Page 4

Null
D3
Null
D4
SELF JOIN :
"Joining a table by itself is known as Self Join "
Why ? / When ?
"Whenever the data to select is in the same table but present
In different records we use self-join ".
Example :
EMP E1
EID
ENAME [Link]
1
ALLEN
3
2
SMITH
1
3
MILLER 2
Join Condition : [Link] = [Link]
Result table :
[Link]
[Link] [Link] [Link]
[Link] [Link]
1
ALLEN
3
3
MILLER
2
2
SMITH
1
1
ALLEN
3
3
MILLER
2
2
SMITH
1
Employees Details - E1 Managers Details - E2
SYNTAX:
ANSI [ American National Standard Institute ]
1.
SELECT Column_Name
FROM Table_Name1 JOIN Table_Name2
ON < JOIN_CONDITION> ;
SELECT *
FROM EMP E1 JOIN EMP E2
ON [Link] = [Link] ;
Oracle
2.
SELECT Column_Name
FROM Table_Name1T1 , Table_Name2 T2
WHERE < J i
C
diti
>
EMP E2
[Link] ENAME MGR
1
ALLEN
3
2
SMITH
1
3
MILLER 2

New Section 1 Page 5

SELECT Column_Name
FROM Table_Name1T1 , Table_Name2 T2
WHERE < Join_Condition > ;
SELECT *
FROM EMP E1 , EMP E2
WHERE [Link] = [Link] ;
WAQTD Ename and Manager's name for all the employees .
1.
SELECT [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link] ;
WAQTD Ename , sal along with manager's name and
manager's salary for all the employees .
2.
SELECT [Link] , [Link] , [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link] ;
WAQTD ename , manager's name along with their deptno
3.
If employee is working as clerk .
SELECT [Link] , [Link] , [Link] ,
[Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link] AND [Link]='CLERK' ;
WAQTD ename , manager's job if manager works as Analyst .
4.
SELECT [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] ='ANALYST' ;
WAQTD ename and manager's name along with their job if
emp and manager are working for same designation .
5.
SELECT [Link] , [Link] , [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] = [Link] ;
WAQTD ename emp salary manager's name manager's salary
6.
If manager earns more than employee .
SELECT [Link] , [Link] , [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] > [Link] ;

New Section 1 Page 6

WAQTD ename and manager's name along with manager's

commission if manager earns commission .
7.
SELECT [Link] , [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] IS NOT NULL ;
NOTE : TO join 'N' number of tables we need to write 'N-1'
number of join conditions
ASSIGNMENT ON SELF JOIN :
[Link] NAME OF THE EMPLOYEE AND HIS MANAGER'S
NAME IF EMPLOYEE IS WORKING AS CLERK
[Link] NAME OF THE EMPLOYEE AND MANAGER'S
DESIGNATION IF MANAGER WORKS IN DEPT 10 OR 20
[Link] NAME OF THE EMP AND MANAGERS SALARY IF
EMPLOYEE AND MANAGER BOTH EARN MORE THAN 2300
[Link] EMP NAME AND MANAGER'S HIREDATE IF
EMPLOYEE WAS HIRED BEFORE1982
[Link] EMP NAME AND MANAGER'S COMM IF
EMPLOYEE WORKS AS SALESMAN AND MANAGER
WORKS IN DEPT 30
[Link] EMP NAME AND MANAGER NAME AND THEIR
SALARIES IF EMPLOYEE EARNS MORE THAN MANAGER
[Link] EMP NAME AND HIREDATE , MANAGER NAME
AND HIREDATE IF
MANAGER WAS HIRED BEFORE EMPLOYEE
[Link] EMP NAME AND MANAGER NAME IF BOTH ARE
WORKING IN SAME JOB
[Link] EMP NAME AND MANAGER NAME IF MANAGER
IS WORKING AS ACTUAL MANAGER
[Link] EMP NAME AND MANAGER NAME ALONG
WITH THEIR ANNUAL SALARIES IF EMPLOYEE WORKS IN
DEPT 10 , 20 AND MANAGER'S SAL IS GREATER THAN
EMPLOYEES SALARY .
[Link] EMPLOYEE'S NAME AND MANAGER'S
DESIGNATION FOR ALL THE EMPLOYEES
[Link] EMPLOYEE'S NAME AND MANAGER'S SALARY
FOR ALL THE EMPLOYEES IF MANAGER'S SALARY ENDS
WITH 50

New Section 1 Page 7

Examples :
E1
NATURAL JOIN :
5.
"It behaves as INNER JOIN if there is a relation between the given
two tables , else it behaves as CROSS JOIN" .
Syntax:
SELECT Col_Name
FROM Table_Name1 NATURAL JOIN Table_Name2;
ANSI :
Emp
ENAME DEPTNO
A
20
B
30
C
10
Result Table : has a relation ( inner join )
EMP
DEPT
[Link] = [Link]
E2
[Link] = [Link]
E1
E2
E3
[Link] = [Link]
[Link] = [Link]
E1
D1
E2
D2
[Link] = [Link] [Link] = [Link]
[Link] = [Link]
DNAME DEPTNO
D1
10
D2
20
D3
30
DEPT

New Section 1 Page 8

DEPTNO ENAME DNAME

20
A
D2
30
B
D3
10
C
D1
Emp
ENAME DEPTNO
A
20
B
30
C
10
Result Table : has no relation ( cross join )
ENAME DEPTNO CNAME CID
A
20
X
101
A
20
Y
102
A
20
Z
103
B
30
X
101
B
30
Y
102
B
30
Z
103
C
10
X
101
C
10
Y
102
C
10
Z
103
QUESTIONS:
----------
[Link] NAME OF THE EMPLOYEE AND HIS MANAGER'S
NAME IF EMPLOYEE IS WORKING AS CLERK
SELECT [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] = 'CLERK';
[Link] NAME OF THE EMPLOYEE AND MANAGER'S
DESIGNATION IF MANAGER WORKS IN DEPT 10 OR 20
SELECT [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] IN ( 10 , 20 );
[Link] NAME OF THE EMP AND MANAGERS SALARY IF
EMPLOYEE AND MANAGER BOTH EARN MORE THAN 2300
SELECT [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
CNAME CID
X
101
Y
102
Z
103
CUSTOMER
New Section 1 Page 9

AND [Link] > 2300 AND [Link]>2300 ;

[Link] EMP NAME AND MANAGER'S HIREDATE IF
EMPLOYEE WAS HIRED BEFORE1982
SELECT [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] < '01-JAN-82' ;
[Link] EMP NAME AND MANAGER'S COMM IF
EMPLOYEE WORKS AS SALESMAN AND MANAGER
WORKS IN DEPT 30
SELECT [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] = 'SALESMAN' AND [Link] = 30 ; ;
[Link] EMP NAME AND MANAGER NAME AND THEIR
SALARIES IF EMPLOYEE EARNS MORE THAN MANAGER
SELECT [Link], [Link] , [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] > [Link] ;
[Link] EMP NAME AND HIREDATE , MANAGER NAME
AND HIREDATE IF
MANAGER WAS HIRED BEFORE EMPLOYEE
SELECT [Link] ,[Link] , [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] < [Link] ;
[Link] EMP NAME AND MANAGER NAME IF BOTH ARE
WORKING IN SAME JOB
SELECT [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] = [Link] ;
[Link] EMP NAME AND MANAGER NAME IF MANAGER
IS WORKING AS ACTUAL MANAGER
SELECT [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] = 'MANAGER';
[Link] EMP NAME AND MANAGER NAME ALONG
WITH THEIR ANNUAL SALARIES IF EMPLOYEE WORKS IN
DEPT 10 , 20 AND MANAGER'S SAL IS GREATER THAN
EMPLOYEES SALARY .
SELECT [Link] , [Link]*12 , [Link] , [Link]*12
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] IN ( 10,20) AND [Link] > [Link] ;

New Section 1 Page 10

[Link] EMPLOYEE'S NAME AND MANAGER'S

DESIGNATION FOR ALL THE EMPLOYEES
SELECT [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link] ;
[Link] EMPLOYEE'S NAME AND MANAGER'S SALARY
FOR ALL THE EMPLOYEES IF MANAGER'S SALARY ENDS
WITH 50
SELECT [Link] , [Link]
FROM EMP E1 , EMP E2
WHERE [Link] = [Link]
AND [Link] LIKE '%50' ;

New Section 1 Page 11

CO - RELATED SUB QUERY

" A query written inside another query such that the outer query and the inner
query are Dependent on each other , this is known as Co-Related Sub-Query
".
WORKING PRINCIPLE :
Outer query executes first but partially
1.
The partially executed output is given as an input to the inner Query
2.
The inner query executes completely and generates an output
3.
The output of inner query is fed as an input to the Outer query and
Outer
4.
Query produces the result .
Therefore, we can state that the outer query and the inner query both are
INTERDEPENDENT ( dependent on each other ) .
5.
Let us consider two queries inner and outer query respectively ,
In co-related sub query a Join condition is a must , And must be
written only in the Inner Query .
i.
Co-Related sub query works with the principles of both SUB
QUERY & JOINS .
ii.
NOTE :
DIFFERENCE BETWEEN SUB QUERY AND CO RELATED SUB
QUERY .
SUB QUERY
CO-RELATED SUB QUERY
Inner query executes first
Outer query executes first
Outer query is dependent on inner
query
Both are interdependent
DAY 17
Friday, August 7, 2020
9:50 AM

New Section 1 Page 1

Join condition not mandatory

Join condition is mandatory and
must be written in inner query
Outer query executes Once
Outer query executes Twice .
Example :
DEPT
DNAME DNO
D1
10
D2
20
D3
30
D4
40
SELECT DNAME
FROM DEPT D
FROM EMP E
WHERE [Link]= [Link] ) ;
WHERE [Link] IN ( SELECT [Link]
EMP
ENAME DNO
A
20
B
10
C
20
D
30
10
20
30
40
10 = 20 False
10 = 10 True
10 = 20 False
10 = 30 False
20 = 20 True
20 = 10 False
20 = 20
True
20 = 30 False
30 = 20 False
30 = 10 False
30 = 20
False
30 = 30 True
40 = 20 False
40 = 10 False
40 = 20
False
40 = 30 False
10
10 IN 10 True
20 IN ( 20 , 20 )
20 = 20
TRUE
20 = 20
TRUE
30 IN 30 True
40 IN Null
Null
D1
( 20 , 20 )
D2
30
D3
null
null

New Section 1 Page 2

WAQTD dnames in which there are employees working .
1.
SELECT DNAME
FROM DEPT D
FROM EMP E
WHERE [Link] = [Link] ) ;
WHERE [Link] IN ( SELECT [Link]
WAQTD dname in which there are no employees working .
2.
SELECT DNAME
FROM DEPT D
FROM EMP E
WHERE [Link] = [Link] ) ;
WHERE [Link] NOT IN ( SELECT [Link]
EXISTS & NOT EXISTS OPERATORS :
One Operand Towards RHS and that Operand has to be
A Co-related Sub Query "
Exists Op returns true if the Sub Query returns Any value
➢
other than Null.
EXISTS : " Exists Op is a Unary Op ( One Operand ) which can accept
1.
SELECT DNAME
FROM DEPT D
FROM EMP E
WHERE [Link]= [Link]. ) ;
WHERE EXISTS ( SELECT [Link]
10 = 20 False
10 = 10 True
10 = 20 False
10 = 30 False
20 = 20 True
20 = 10 False
20 = 20
True
20 = 30 False
30 = 20 False
30 = 10
False
10
D1
( 20 , 20 )
D2
30
D3
null

New Section 1 Page 3

One Operand Towards RHS and that Operand has to be
A Co-related Sub Query "
NOT EXISTS : " Not Exists Op is a Unary Op ( One Operand ) which
can accept
2.
Not Exists Op returns true if the Sub Query returns NULL .
➢
To Find MAX & MIN salary :
To find MAXIMUM salary :
SELECT SAL
FROM EMP E1
FROM EMP E2
WHERE [Link] < [Link] ) = N-1 ;
WHERE ( SELECT COUNT( DISTINCT SAL )
[Link]
1000
3000
2000
3000
2000
4000
5000
30 = 20 False
30 = 10 False
30 = 20
False
30 = 30 True
40 = 20 False
40 = 10 False
40 = 20
False
40 = 30 False
3 MAX SAL :
3000
SELECT SAL
FROM EMP E1
FROM EMP E2
WHERE [Link] < [Link] ) = 2 ;
WHERE ( SELECT COUNT( DISTINCT SAL )
[Link]
1000
3000
2000
3000
2000
4000
5000
[Link]
1000
3000
2000
3000
2000
4000
5000
2

New Section 1 Page 4

To find MINUMUM salary :

SELECT SAL
FROM EMP E1
FROM EMP E2
WHERE [Link] > [Link] ) = N-1 ;
WHERE ( SELECT COUNT( DISTINCT SAL )
2nd , 4th , 5th , 7th MAX salary
SELECT SAL
FROM EMP E1
FROM EMP E2
WHERE [Link] < [Link] ) in ( 1 , 3, 4 , 6 ) ;
WHERE ( SELECT COUNT( DISTINCT SAL )
4000
5000
4000
5000

New Section 1 Page 5

SINGLE ROW FUNCTIONS

1. LENGTH( )
2. CONCAT( )
3. UPPER( )
4. LOWER( )
5. INITCAP( )
6. REVERSE( )
7. SUSBTR( )
8. INSTR( )
9. REPLACE( )
10. MOD( )
11. TRUNC( )
12. ROUND( )
13. MONTHS_BETWEEN( )
14. LAST_DAY( )
15. TO_CHAR( )
16. NVL( )
In the given string ".
SYNTAX: LENGTH ( 'string' )
LENGTH : "It is used to count the number of characters present
1.
Example :
WAQT count number of characters present in 'SMITH' .
➢
SELECT LENGTH ( ENAME )
FROM EMP
WHERE ENAME ='SMITH' ;
SELECT LENGTH( 'SMITH' )
FROM DUAL ;
SELECT LENGTH( 'HELLO WORLD' ) 11
FROM DUAL;
It is a DUMMY table which has 1 col and 1 row .
Which is used to output the result .
DESC DUAL ;
➢
SELECT *
➢
FROM DUAL ;
NOTE : DUAL TABLE
CONCAT( ) : "It is used to join the given two strings '
2.
LENGTH(ENAME)
5
DAY 18
Monday, August 10, 2020
9:47 AM

New Section 1 Page 1

SYNTAX : CONCAT ( 'string1' , 'String2' )

Example :
Input : Smith
Output : Mr. Smith
SELECT CONCAT( 'Mr. ' , ENAME )
FROM EMP
WHERE ENAME ='SMITH' ;
UPPER( ) : "It is used to convert a given string to upper case "
3.
SYNTAX: UPPER ( 'string' )
LOWER( ) :"It is used to convert a given string to lower case "
4.
INITCAP( ):"It is used to convert a given string to initial capital letter
case ".
5.
REVERSE( ): "It is used to reverse a given string ".
6.
Example :
SELECT REVERSE( 'SMITH' ).
FROM DUAL ;
SELECT UPPER( 'smith' ).
FROM DUAL ;
SELECT LOWER( 'SMITH' ).
FROM DUAL ;
SELECT INITCAP( 'SMITH' ).
FROM DUAL ;
Original string " .
SUBSTR : "It is used to extract a part of string from the given
7.
SYNTAX: SUBSTR ( 'Original_String' , Position [ , Length ] )
Example :
SYNTAX: LOWER( 'string' )
SYNTAX: INITCAP( 'string' )
SYNTAX: REVERSE( 'string' )
REVERSE( 'SMITH' )
HTIMS
UPPER( 'smith' )
SMITH
LOWER( 'SMITH' )
smith
INITCAP( 'SMITH' )
Smith
NOTE: Length is not mandatory ,
If length is not mentioned then

New Section 1 Page 2

SYNTAX: SUBSTR ( Original_String , Position [ , Length ] )

Example :
-7
-6
-5
-4
-3
-2
-1
Q
S
P
I
D
E
R
1
2
3
4
5
6
7
Example : SUBSTR( 'QSPIDER' , 2 , 3 )
SPI
Example : SUBSTR( 'QSPIDER' , 3 , 3 )
PID
Example : SUBSTR( 'QSPIDER' , 2 )
SPIDER
Example : SUBSTR( 'QSPIDER' , 1 , 6 )
QSPIDE
Example : SUBSTR( 'QSPIDER' , 4, 1)
I
Example : SUBSTR( 'QSPIDER' , 1 , 1 )
Q
Example : SUBSTR( 'QSPIDER' , 7 , 1)
R
Example : SUBSTR( 'QSPIDER' , 6 )
ER
Example : SUBSTR( 'QSPIDER' , 0 , 3 )
QSP
Example : SUBSTR( 'QSPIDER' , 6 , 6 )
ER
Example : SUBSTR( 'QSPIDER' , -2 , 1 ) E
Example : SUBSTR( 'QSPIDER' , -5 , 3 ) PID
Example : SUBSTR( 'QSPIDER' , -7 , 2 ) QS
Example : SUBSTR( 'QSPIDER' , -1 )
R
WAQT extract first 3 characters of the emp names .
➢
SELECT SUBSTR(ENAME, 1,3)
FROM EMP;
WAQT extract last 3 characters of the employee names .
➢
SELECT SUBSTR(ENAME, -3 )
FROM EMP;
WAQT to display first half of employee names .
➢
ENAME OUTPUT
SMITH
SM
MILLER
MIL
JONES
JO
WARD
WA
SELECT SUBSTR( ENAME , 1 , LENGTH( ENAME ) / 2 )
FROM EMP ;
SMITH
SUBSTR( ENAME , 1 , LENGTH( ENAME ) / 2 )
+ve
-ve
NOTE: Length is not mandatory ,
If length is not mentioned then
Consider the complete string .

New Section 1 Page 3

SUBSTR( 'SMITH' , 1 , LENGTH ( 'SMITH' ) / 2 )

SUBSTR( 'SMITH' , 1 , 5 / 2 )
SUBSTR( 'SMITH' , 1 , 2 )
SM
WARD
SUBSTR( ENAME , 1 , LENGTH( ENAME ) / 2 )
SUBSTR( 'WARD' , 1 , LENGTH ('WARD' ) / 2 )
SUBSTR( 'WARD' , 1 , 4 / 2 )
SUBSTR( 'WARD' , 1 , 2 )
WA
WAQT to display second half of employee names .
➢
ENAME
OUTPUT
SMITH
ITH
MILLER
LER
JONES
NES
WARD
RD
SELECT SUBSTR( ENAME , LENGTH( ENAME ) / 2 + 1 )
FROM EMP ;
SMITH
SUBSTR( ENAME , LENGTH( ENAME ) / 2 +1)
SUBSTR( 'SMITH' , LENGTH ( 'SMITH' ) / 2 +1)
SUBSTR( 'SMITH' , 5 / 2 +1)
SUBSTR( 'SMITH' , 3 )
ITH
WARD
SUBSTR( ENAME , LENGTH( ENAME ) / 2+1 )
SUBSTR( 'WARD' , LENGTH ('WARD' ) / 2+1 )
SUBSTR( 'WARD' , 4 / 2 +1)
SUBSTR( 'WARD' , 3)
RD
The original string.
REPLACE ( ) : "It is used to replace a string with another string in
8.
SYNTAX:REPLACE ( 'Original_String' , 'string' [, 'new_String' ] )
Example :
REPLACE ( 'BANANA' , 'A' , 'C' )
BCNCNC
Example :
REPLACE ( 'BANANA' , 'N' , 'ABC' )
BAABCAABCA
Example :
REPLACE ( 'OPPO' , 'O' , 'J' )
JPPJ
Example :
REPLACE ( 'BANANA' , 'A' )
BNN
Example :
REPLACE ( 'ENGINEERING' , 'E' )
NGINRING
Null

New Section 1 Page 4

Example :
REPLACE ( 'ENGINEERING' , 'E' , '123' ) 123N123123GINRING
Value of it is Null .
NOTE : if the third argument is not mentioned the default
WAQTD the number of times char 'A' is present in BANANA !!!
1.
SELECT LENGTH('BANANA') - LENGTH ( REPLACE( 'BANANA','A' )
FROM DUAL ;
Length ( 'BANANA' ) - LENGTH( REPLACE('BANANA','A') )
Length ('BANANA') - LENGH ('BNN' )
6-3
= 3 times 'A' is present in BANANA
WAQTD to count number of time 'A' is present in 'MALAYALAM'
2.
SELECT LENGTH('MALAYALAM') - LENGTH
( REPLACE( 'MALAYALAM','A' )
FROM DUAL ;

New Section 1 Page 5

It is used to search for a string in the Original string if present it returns the POSITION
Else it returns 0 ".
Syntax: INSTR( 'Original_String' , 'String' , Position [, Occurrence] )
INSTR ( ) : "it is used to obtain the position in which the string is present in the Original string ".
9.
Note : if occurrence is not Mentioned then , the default value of Occurrence is 1 .
B
A
N
A
N
A
1
2
3
4
5
6
WAQTD NAMES OF THE EMPLOYEES IF THEY HAVE CHAR 'A' PRESENT IN THEIR NAMES
1.
SELECT ENAME
FROM EMP
WHERE INSTR( ENAME , 'A' , 1 ,1 ) > 0 ;
WAQTD NAMES OF THE EMPLOYEES IF THEY HAVE CHAR 'A' PRESENT ATLEAST TWICE IN
THEIR NAMES
2.
SELECT ENAME
FROM EMP
WHERE INSTR( ENAME , 'A' , 1 , 2 ) > 0 ;
WAQTD NAMES OF THE EMPLOYEES IF THEY HAVE CHAR 'A' PRESENT ATLEAST THRICE IN
THEIR NAMES
3.
SELECT ENAME
FROM EMP
Example : INSTR( 'BANANA' , 'A' , 1 , 1 )
POS: 2
Example : INSTR( 'BANANA' , 'A' , 2 , 1 )
POS: 2
Example : INSTR( 'BANANA' , 'A' , 1 , 2 )
POS: 4
Example : INSTR( 'BANANA' , 'A' , 1 , 3 )
POS: 6
Example : INSTR( 'BANANA' , 'A' , 1 , 4 )
POS: 0
Example : INSTR( 'BANANA' , 'A' , 4 , 2 )
POS: 6
Example : INSTR( 'BANANA' , 'A' , 2 )
POS: 2
Example : INSTR( 'BANANA' , 'N' , 2 , 1)
POS: 3
Example : INSTR( 'BANANA' , 'O' , 1 ,1 )
POS: 0
Example : INSTR( 'BANANA' , 'NA' , 2 , 2 )
POS: 5
Example : INSTR( 'BANANA' , 'A' , 3 , 3 )
POS: 0
Example : INSTR( 'BANANA' , 'ANA' , 1 , 2 ) POS: 4
DAY 19
Tuesday, August 11, 2020
9:56 AM

New Section 1 Page 1

WHERE INSTR( ENAME , 'A' , 1 , 3 ) > 0 ;

WAQTD NAMES OF THE EMPLOYEES IF THEY HAVE CHAR 'A' EXACTLY TWICE
4.
SELECT ENAME
FROM EMP
WHERE INSTR( ENAME , 'A' , 1 , 2 ) > 0 AND INSTR( ENAME , 'A' , 1 , 3 ) = 0 ;
OR
SELECT ENAME
FROM EMP
WHERE ( LENGTH( ENAME ) - LENGTH( REPLACE( ENAME ,'A') ) ) = 2;
ALLEN
INSTR('ALLEN','A',1,2)
Pos:0 INSTR('ALLEN','A',1,3)
Pos:0
ADAMS
INSTR('ADAMS','A',1,2)
Pos:3 INSTR('ADAMS','A',1,3)
Pos:0
AATISH
INSTR('AATISH','A',1,2)
Pos:2 INSTR('AATISH','A',1,3)
Pos:0
AAA
INSTR('AAA' ,'A',1 ,2)
Pos:2 INSTR('AAA' ,'A',1 ,3)
Pos:3
MALAYALAM
INSTR('MALAYALAM' , 'A' , 1, 2 ) Pos:4 INSTR('MALAYALAM' , 'A' , 1, 3 )
Pos:6
ALLEN LENGTH( 'ALLEN' ) - LENGTH( REPLACE( 'ALLEN' ,A' ) ) = 2
5 - LENGTH( 'LLEN' )
5-4
1
!= 2
ADAMS 5 - LENGTH('DMS' )
5-3
2
=2
AAAAO 5 - LENGTH('O')
5-1
4
!= 2
SINGLE ROW FUNCTIONS
10. MOD( )
11. TRUNC( )
12. ROUND( )
13. MONTHS_BETWEEN( )
14. LAST_DAY( )
15. TO_CHAR( )
16. NVL( )

New Section 1 Page 2

MOD( ) : "It is used to obtain modulus/remainder of the given number "

10.
Syntax: MOD ( m , n )
FROM DUAL ;
Example : SELECT MOD( 5 , 2 )

SELECT ENAME
FROM EMP
WHERE MOD( SAL , 3 ) = 0 ;
SELECT *
FROM EMP
WHERE MOD( EID , 2 ) = 1 ;
WAQTD DETAILS OF THE EMPLOYEE WHO HAVE ODD EID
2.
1 . WAQTD ENAMES OF THE EMPLOYEES WHO EARN SALARY IN MULTIPLES OF 3
ROUND( ) : " It is used to Round-off the given number based on the scale value "
11.
Syntax: ROUND ( Number [, Scale ] )
Example : ROUND ( 5.6 )
6
Example : ROUND ( 5.5 )
6
Example : ROUND ( 5.4 )
5
Example : ROUND ( 9.9 )
10
Example : ROUND ( 9.4 )
9
Example : ROUND (8.6 , 0 ) 9
When the scale is -ve it indicated the digits before the decimal
And the digit count begins from 1 .
Example : ROUND ( 8421.12 , -1 )
8420
Example : ROUND ( 8426.12 , -1 )
8430
Example : ROUND ( 154264.12 , -2 ) 154300
Example : ROUND ( 338222 , -4 )
340000
Example : ROUND ( 2514 , -3 )
3000
When the scale is +ve it indicated the digits after the decimal
And the digit count begins from 0 .
Example : ROUND ( 124 23541 0 )
124
n)m(
1
The default value of scale is 0
RHS
LHS
After the Decimal
Before the Decimal
Decimal
+ve SCALE
-ve SCALE
ROUND ( 8421 .12 , -1 )
5.6
6
Rounding off to units place
( 124.23541 , 0 ) = 124
New Section 1 Page 3

And the digit count begins from 0 .

Example : ROUND ( 124.23541 , 0 )
124
Example : ROUND ( 124. 23541 , 1 )
124.2
Example : ROUND ( 124. 23541 , 2 )
124.24
Example : ROUND ( 124. 2354391 , 5 ) 124.23544
TRUNC( ): "It is similar to ROUND() but it always rounds-off the given number to the lower value "
12.
DATE COMMANDS :
SYSDATE : " it is used to obtain Todays Date "
i.
CURRERNT_DATE : " it is also used to obtain todays date "
ii.
SYSTIMESTAMP : "It is used to obtain date , time and time zone "
iii.
NOTE :
SQL> SELECT SYSDATE
2 FROM DUAL ;
SYSDATE
---------------
17-MAY-20
SQL> SELECT CURRENT_DATE
2 FROM DUAL ;
CURRENT_D
------------------
17-MAY-20
SQL> SELECT SYSTIMESTAMP
2 FROM DUAL ;
SYSTIMESTAMP
---------------------------------------------------
17-MAY-20 05.05.52.356000 PM +05:30
Example : TRUNC ( 5.6 )
5
Example : TRUNC ( 5.5 )
5
Example : TRUNC ( 5.4 )
5
Example : TRUNC ( 9.9 )
9
Example : TRUNC ( 9.4 )
9
Example : TRUNC (8.6 , 0 )
8
Example: TRUNC( 451258.32541 , -5) 400000
Syntax: TRUNC( Number [, Scale ] )
( 124.23541 , 0 ) = 124
( 124.23541 , 1 ) = 124.2
( 123.6712638723 , 6 ) =
123.671264

New Section 1 Page 4

Given two dates "

Syntax: MONTHS_BETWEEN ( DATE1 , DATE2 )
MONTHS_BETWEEN( ) :"It is used to Obtain the number of months present between the
13.
SELECT TRUNC( MONTHS_BETWEEN( SYSDATE , HIREDATE ) ) || ' Months'
FROM EMP
TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE))||'MONTH
----------------------------------------------------------------------------------------
473 Months
470 Months
LAST_DAY( ): " it is used to Obtain the last day in the particular of the given date" .
14.
Syntax: LAST_DAY( DATE ) ;
SQL> SELECT LAST_DAY( SYSDATE ) SYSDATE = 08-JUL-2020
2 FROM DUAL ;
LAST_DAY
----------------
31-JUL-20
TO_CHAR( ) :"It is used to convert the given date into String format based on the Model given "
15.
Syntax: TO_CHAR( DATE , 'Format _ Models')
YEAR : TWENTY TWENTY
i.
YYYY : 2020
ii.
YY : 20
iii.
MONTH : JULY
iv.
MON : JUL
v.
MM : 07
vi.
DAY : WEDNESDAY
vii.
DY : WED
viii.
DD : 08
ix.
D : 4 ( day of the week )
x.
HH24 : 17 hours
xi.
HH12 : 5 hours
xii.
MI : 22 minutes
xiii.
SS : 53 seconds
xiv.
'HH12:MI:SS' : 5 : 22 : 53
xv.
'DD-MM-YY' : 17 - 05 - 20
xvi.
'MM-DD-YYYY' : 05 - 17 - 2020
xvii.
Format Models :

New Section 1 Page 5

1. WAQTD DETAILS OF THE EMPLOYEE WHO WAS HIRED ON A SUNDAY .

SELECT *
FROM EMP
WHERE TO_CHAR( HIREDATE , 'DAY' ) = 'SUNDAY' ;
SELECT *
FROM EMP
WHERE TO_CHAR( HIREDATE , 'D' ) = 2 AND TO_CHAR( HIREDATE , 'HH24' ) = 10 ;
2. WAQTD DETAILS OF AN EMPLOYEE HIRED ON MONDAY AT 10AM
NVL( ) : [ NULL VALUE LOGIC ] " It is used to eliminate the side effects of using null in arithmetic
operations " .
16.
ENAME SAL COMM
A
500
100
B
1000 NULL
C
2000 200
D
2000 NULL
Null value logic : Syntax : NVL ( Argument1 , Argument2 )
 SELECT ENAME , SAL + NVL ( COMM , 0 )
FROM EMP ;
A
500 + NVL ( 100 , 0 )
500 + 100
600
B
1000 + NVL ( null , 0 )
1000 + 0
1000
C
2000 + NVL ( 200 , 0 )
2000+200
2200
D
2000 + NVL( null , 0 )
2000 + 0
2000
WAQTD NAME AND TOTAL SALALRY OF ALL THE EMPLOYEES?
SELECT ENAME , SAL + COMM
FROM EMP ;
ENAME SAL+COMM
A
600
B
NULL
C
2200
D
NULL
Argument 1 : Here write any column / exp which can result In null .
Argument 2 : Here we write a numeric value which will be substituted
if argument 1 results in Null ,
If argument 1 is NOT NULL then the same value will be considered .
ENAME SAL+nvl(COMM ,0)
A
600
After using NVL

New Section 1 Page 6

ENAME SAL+nvl(COMM ,0)

A
600
B
1000
C
2200
D
2000
g
1. List employees whose name having 4 characters
SELECT *
FROM EMP
WHERE LENGTH(ENAME)=4 ;
2. List employees whose job is having 7 characters
SELECT *
FROM EMP
WHERE LENGTH(JOB)=4;
3. Find out how many times letter 'S' occurs in 'qspiders'
SELECT LENGTH('QSPIDERS') - LENGTH( REPLACE( 'QSPIDERS' , 'S' ) )
FROM DUAL ;
4. List the employees whose job is having last 3 characters as 'man'
SELECT *
FROM EMP
WHERE SUBSTR( JOB , -3 ) = 'MAN' ;
5. List employees whose job is having first 3 characters as 'man'.
SELECT *
FROM EMP
WHERE SUBSTR( JOB , 1 , 3 ) = 'MAN' ;
6. Display all the names whose name is having exactly 1 'L'
SELECT ENAME
FROM EMP
WHERE INSTR( ENAME , 'L' , 1,1 ) != 0 AND INSTR( ENAME , 'L' , 1, 2 ) = 0 ;
OR
SELECT ENAME
FROM EMP
WHERE LENGTH( ENAME ) - LENGTH( REPLACE( ENAME , 'L' ) ) = 1 ;
7. Display dept names which are having letter 'O'
SELECT DNAME
FROM DEPT
WHERE INSTR(DNAME,'O',1,1 ) !=0 ;
9. Calculate number of L in string 'HELLLLL'
SELECT LENGTH('HELLLLL') - LENGTH( REPLACE( 'HELLLLL' , 'L' ) )
FROM DUAL ;

New Section 1 Page 7

10. Display all the employees whose job has a string 'MAN'
SELECT *
FROM EMP
WHERE INSTR(JOB,'MAN',1,1 ) !=0 ;
11. Display all the employees whose job starts with string 'MAN'
SELECT *
FROM EMP
WHERE INSTR(JOB,'MAN',1,1 ) =1 ;
OR
SELECT *
FROM EMP
WHERE SUBSTR( JOB ,1,3) = 'MAN' ;
12. Display all the employees whose job ends with string 'MAN'
SELECT *
FROM EMP
WHERE SUBSTR( JOB , -3 ) = 'MAN' ;
13. Display first 3 characters of ename in lower case and rest everything in upper case.
If ename is 'QSPIDERS' then display this as ‘qspIDERS’
SELECT LOWER(SUBSTR('QSPIDERS',1,3)) || UPPER( SUSBTR('QSPIDERS' , 4) )
FROM DUAL ;
14. Display the result from emp table as below.
SMITH is a CLERK and gets salary 2000
Here SMITH is ename column, CLERK is JOB and 2000 is SAL column and rest everything is literal
strings.
SELECT ENAME || ' IS A '|| JOB || ' AND GETS SALARY ' || SAL
FROM EMP
WHERE ENAME = 'SMITH' ;
[Link] the employees hired on a Wednesday
SELECT *
FROM EMP
WHERE TO_CHAR( HIREDATE , 'DY' ) = WED ;
[Link] the employees hired on a leap year
SELECT *
FROM EMP
WHERE MOD( TO_CHAR( HIREDATE , 'YY' ) , 4 ) = 0 ;
[Link] the employees hired on a Sunday in the month of may
SELECT *
FROM EMP
WHERE TO_CHAR( HIREDATE , 'DY' ) = 'SUN' AND TO_CHAR( HIREDATE , 'MON' ) = 'MAY' ;

New Section 1 Page 8

DATA DEFINITION LANGUAGE ( DDL )

➢
DATA MANIPULATION LANGUAGE ( DML )
➢
TRANSACTION CONTROL LANGUAGE ( TCL )
➢
DATA CONTROL LANGUAGE ( DCL )
➢
DATA QUERY LANGUAGE ( DQL )
➢
STATEMENTS ARE CLASSIFIED INTO 5 DIFFERENT TYPES
" DDL is used to construct an object in the database and deals with the Structure of the
Object "
CREATE
1.
RENAME
2.
ALTER
3.
TRUNCATE
4.
DROP
5.
It has 5 statements :
DATA DEFINITION LANGUAGE ( DDL ):
1.
CREATE : " IT IS USED TO BUILD / CONSTRUCT AN OBJECT "
1.
Object / Entity can be a Table or a View ( Virtual Table ) .
How to Create a Table :
Tables cannot have same names .

Name of the table
➢
Number of Columns .
➢
Names of the columns .
➢
Assign datatypes for the Columns.
➢
Assign Constraints [ NOT MANDATORY ] .
➢
Example 1:
Table_Name : CUSTOMER
Customer
Number of Columns : 4
Column_Name CID
CNAME
CNO
ADDRESS
Datatypes
Number(2)
Varchar(10) Number (10)
Varchar(15)
Null / Not Null Not Null
Not Null
Not Null
Null
Unique
Unique
Unique
Check
Check ( length( CNO ) = 10 )
Primary Key
Primary Key
F
i
K
DAY 20
Wednesday, August 12, 2020
8:48 AM

New Section 1 Page 1

Check
Check ( length( CNO ) = 10 )
Primary Key
Primary Key
Foreign Key
Syntax to create a table :
Column_Name1 datatype constraint_type ,
Column_Name2 datatype constraint_type ,
Column_Name3 datatype constraint_type ,
.
.
Column_NameN datatype constraint_type
(
);
CREATE TABLE Table_Name
Example :
CID Number(2) primary key ,
CNAME Varchar(10) ,
CNO Number(10) not null check( length( CNO ) = 10 ) ,
ADDRESS Varchar(15)
(
);
CREATE TABLE CUSTOMER
To Describe the table:
Syntax: DESC Table_Name ;
NOTE :
Not Mandatory
Example 2:
Table_Name : PRODUCT
Number of Columns : 4
Product
Column_Name PID
PNAME
PRICE
CID
Datatypes
Number(2)
Varchar(10) Number (7,2)
Number(2)
Null / Not Null Not Null
Not Null
Not Null
Null
Unique
Unique
Check
Check ( Price > 0 )
Primary Key
Primary Key
Foreign Key
Foreign Key
CUSTOMER
PRODUCT
CID

New Section 1 Page 2

Syntax to create a table :

Column_Name1 datatype constraint_type ,
Column_Name2 datatype constraint_type ,
Column_Name3 datatype constraint_type ,
.
.
Column_NameN datatype ,
Constraint Foreign key references Parent_Table_Name(Column_Name)
(
);
CREATE TABLE Table_Name
Example :
PID Number(2) primary key ,
PNAME Varchar(10) ,
PRICE Number(7,2) check( Price > 0) ,
CID Number(2) ,
Constraint CID_FK Foreign Key(CID) references CUSTOMER( CID )
(
);
CREATE TABLE PRODUCT
Syntax: RENAME Table_Name TO New_Name ;
[Link] : "IT IS USED TO CHANGE THE NAME OF THE OBJECT "
RENAME Customer TO Cust ;
Example :
ALTER :" IT IS USED TO MODIFY THE STRUCTURE OF THE TABLE "
3.
TO ADD A COLUMN :
➢
ADD Column_Name Datatype Constraint_type ;
Syntax: ALTER TABLE Table_Name
ADD MAIL_ID Varchar(15) ;
Example : ALTER TABLE Cust
DROP COLUMN Column_Name ;
Syntax: ALTER TABLE Table_Name
TO DROP A COLUMN :
➢

New Section 1 Page 3

DROP COLUMN MAIL_ID ;

Example : ALTER TABLE Cust
RENAME COLUMN Column_Name TO new_Column_Name ;
Syntax: ALTER TABLE Table_Name
TO RENAME A COLUMN :
➢
RENAME COLUMN CNO TO PHONE_NO ;
Example : ALTER TABLE Cust
MODIFY COLUMN_NAME New_Datatype;
Syntax: ALTER TABLE Table_Name
TO MODIFY THE DATATYPE :
➢
MODIFY CNAME CHAR(10) ;
Example : ALTER TABLE Cust
TO MODIFY NOT NULL CONSTRAINTS :
➢
MODIFY COLUMN_NAME Existing_datatype [NULL]/NOT NULL;
Syntax: ALTER TABLE Table_Name
MODIFY ADDRESS Varchar(15) Not Null ;
Example : ALTER TABLE Cust
TRUNCATE : " IT IS USED TO REMOVE ALL THE RECORDS FROM THE TABLE
PREMANENTLY "
4.
Syntax: TRUNCATE TABLE Table_Name ;
Cust
Cid
Cname Phone_no
Address
1
A
1234567890 BANGALORE
2
B
1234567899 MYSORE
3
C
1234567880 MANGALORE
Example : TRUNCATE TABLE Cust ;
Cust
Cid
Cname Phone_no Address
DROP :" IT IS USED TO REMOVE THE TABLE FROM THE DATABASE "
5.
Syntax: DROP TABLE Table_Name ;

New Section 1 Page 4

Syntax: DROP TABLE Table_Name ;

Example :
Cust
Cid
Cname Phone_no
Address
1
A
1234567890 BANGALORE
2
B
1234567899 MYSORE
3
C
1234567880 MANGALORE
TO RECOVER THE TABLE :
TO BEFORE DROP ;
Syntax: FLASHBACK TABLE Table_Name
TO DELETE THE TABLE FROM BIN FOLDER :
Syntax: PURGE TABLE Table_Name ;
Example : PURGE TABLE Cust ;
FLASHBACK
DROP
Bin Folder
Address
Bin$123abc
DATABASE
Example :
Bin Folder
Address : BIN$123ABCXYZ
DATABASE
Cust
FLASHBACK
FLASHBACK TABLE Cust
TO BEFORE DROP ;
Database
Bin folder
Trash
Cust
DROP
BIN$123ABC
PURGE
Gone
Forever

New Section 1 Page 5

FLASHBACK
NOTE : DDL STATEMENTS ARE AUTO-COMMIT STATEMENTS

New Section 1 Page 6

DATA MANIPULATION LANGUAGE ( DML )

INSERT
1.
UPDATE
2.
DELETE
3.
It is used to Manipulate the Object by performing insertion , updating and deletion .
INSERT : It is used to insert / create records in the table .
1.
Syntax: INSERT INTO Table_Name VALUES( v1 , v2 , v3 …… ) ;
CUSTOMER
CID
CNAME
CNO
ADDDRESS
NUMBER(2) VARCHAR(10) NUMBER(10) VARCHAR(20)
INSERT INTO CUSTOMER VALUES( 1 , 'DINGA' , 9876543210 ,'BANGALORE' ) ;
CID
CNAME
CNO
ADDDRESS
NUMBER(2) VARCHAR(10) NUMBER(10) VARCHAR(20)
1
DINGA
9876543210
BANGALORE
INSERT INTO CUSTOMER VALUES( 2 , 'DINGI' , 9876543211 ,'MANGALORE' ) ;
CID
CNAME
CNO
ADDDRESS
NUMBER(2) VARCHAR(10) NUMBER(10) VARCHAR(20)
1
DINGA
9876543210
BANGALORE
2
DINGI
9876543211
MANGALORE
PRODUCT
PID
PNAME
PRICE
CID
NUMBER(2) VARCHAR(10) NUMBER(6,2)
NUMBER(3)
INSERT INTO PRODUCT VALUES( 11 , 'iPhone' , 10000 , 2 );
PID
PNAME
PRICE
CID
NUMBER(2) VARCHAR(10) NUMBER(6,2)
NUMBER(3)
11
iPhone
10000
2
INSERT INTO PRODUCT VALUES( 22 , 'Mac Book' , 20000 , 1 );
PID
PNAME
PRICE
CID
NUMBER(2) VARCHAR(10) NUMBER(6,2)
NUMBER(3)
DAY 21
Thursday, August 13, 2020
9:41 AM

New Section 1 Page 1

11
iPhone
10000
2
22
Mac Book
20000
1

[Link] :It is used to modify an existing value .

SET Col_Name = Value , Col_Name = Value ,,,,,
[WHERE stmt ] ;
Syntax: UPDATE Table_Name
CID
CNAME
CNO
ADDDRESS
NUMBER(2) VARCHAR(10) NUMBER(10) VARCHAR(20)
1
ABHI
1234567890
BANGALORE
2
ABDUL
9876543210
MANGALORE
WAQT update the phone number of Abdul to 7778889994
➢
UPDATE CUSTOMER
SET CNO = 7778889994
WHERE CNAME ='ABDUL' ;
CID
CNAME
CNO
ADDDRESS
NUMBER(2) VARCHAR(10) NUMBER(10) VARCHAR(20)
1
ABHI
1234567890
BANGALORE
2
ABDUL
7778889994
MANGALORE
WAQT change the address of the customer to Mysore whose cid is 1 .
➢
UPDATE CUSTOMER
SET ADDRESS = 'MYSORE'
WHERE CID = 1 ;

CID
CNAME
CNO
ADDDRESS
NUMBER(2) VARCHAR(10) NUMBER(10) VARCHAR(20)
1
ABHI
1234567890
MYSORE
2
ABDUL
7778889994
MANGALORE
[Link] : It is used to remove a particular record from the table .
Syntax: DELETE FROM Table_Name
[ WHERE stmt ];
CID
CNAME
CNO
ADDDRESS
NUMBER(2) VARCHAR(10) NUMBER(10) VARCHAR(20)

New Section 1 Page 2

WAQT remove abdul from the list of customers .

➢
DELETE FROM CUSTOMER
WHERE CNAME ='ABDUL' ;
1
ABHI
1234567890
BANGALORE
2
ABDUL
1234567891
MANGALORE
CID
CNAME
CNO
ADDDRESS
NUMBER(2) VARCHAR(10) NUMBER(10) VARCHAR(20)
1
ABHI
1234567890
BANGALORE
ASSIGNMENT ON DML STATEMENTS :
WAQT update the salary of employee to double their salary if
1.
He is working as a manager .
WAQT change the name of SMITH to SMIITH .
2.
WAQT modify the job of KING to 'PRESIDENT' .
3.
WAQT to change name of ALLEN to ALLEN MORGAN .
4.
WAQT hike the salary of the employee to 10% . If employees earn less than
5.
2000 as a salesman .
WAQ TO delete the employees who don’t earn commission .
6.
WAQ to remove all the employees hired before 1987 in dept 20
7.
Differentiate between TRUNCATE and DELETE statements .
8.
TRUNCATE
DELETE
Belongs to DDL
Belongs to DML

New Section 1 Page 3

Removes all the records from the

Table permanently .
Removes a particular record from the
Table .
Auto COMMIT
Not auto COMMIT .
TRANSACTION CONTROL LANGUAGE ( TCL )
3.
"It is used to control the transactions done on the database ".
The DML Operations performed on the Database are known as Transactions
such as Insertion , Updating and Deletion .
COMMIT
1.
ROLLBACK
2.
SAVEPOINT
3.
We have 3 Statements :
[Link] : "This statement is used to SAVE the transactions into the DB ".
Syntax: COMMIT ;
Example :
Query WORKPLACE DATABASE
CREATE T1
➢
INSERT INTO T1
➢
VALUES( 'A' , 100 ) ;
COMMIT ;
INSERT INTO T1
➢
VALUES( 'B' , 200 ) ;
INSERT INTO T1
➢
VALUES( 'C' , 300 ) ;
COMMIT ;
NAME SAL
NAME SAL
A
100
NAME SAL
A
100
B
200
C
300
NAME SAL
NAME SAL
A
100
NAME SAL
A
100
B
200
C
300

New Section 1 Page 4

INSERT INTO T1
➢
VALUES( 'C' , 300 ) ;
COMMIT ;
UPDATE T1
➢
SET SAL = 1000
WHERE NAME = 'A' ;
COMMIT ;
➢
ROLLBACK :
2.
This statement is used to Obtain only the saved data from the DB .
It will bring you to the point where you have committed for the last time .
SYNTAX: ROLLBACK ;
SAVEPOINT :
3.
This statement is used to mark the positions or restoration points . (nothing
related to DB ) .
SYNTAX: SAVEPOINT Savepoint_Name ;
Example :
Query WORKPLACE SAVEPOINT
INSERT INTO T1
➢
VALUES( 'A',100) ;
SAVEPOINT S1 ;
➢
INSERT INTO T1
➢
VALUES( 'B' , 200 ) ;
INSERT INTO T1
➢
B
200
C
300
NAME SAL
A
1000
B
200
C
300
B
200
C
300
NAME SAL
A
1000
B
200
C
300
NAME SAL
A
100
B
200
C
300
D
400
Savepoints
S1
S2

New Section 1 Page 5

INSERT INTO T1
➢
VALUES( 'B' , 200 ) ;
INSERT INTO T1
➢
VALUES( 'C' , 300 ) ;
SAVEPOINT S2 ;
➢
INSERT INTO T1
➢
VALUES( 'D' , 400 ) ;
INSERT INTO T1
➢
VALUES( 'E' , 500 ) ;
SAVEPOINT S3 ;
➢
INSERT INTO T1
➢
VALUES( 'F' , 600 ) ;
SYNTAX: ROLLBACK TO Savepoint_Name ;
DATA CONTROL LANGUAGE :
4.
"This statement is used to control the flow of data between the users ".
GRANT
1.
REVOKE
2.
We have 2 statements :
[Link] : THIS STATEMENT IS USED TO GIVE PERMISSION TO A
USER .
ON TABLE_NAME
TO USER_NAME ;
SYNTAX: GRANT SQL_STATEMENT
ON TABLE_NAME
FROM USER_NAME ;
SYNTAX: REVOKE SQL_STATEMENT
[Link] : THIS STATEMENT IS USED TO TAKE BACK THE
PERMISSION FROM THE USER .
Example :
User 1 : SCOTT User 2 : HR
C
300
D
400
E
500
F
600
S2
S3
ROLLBACK TO S3
ROLLBACK TO S2
ROLLBACK TO S1

New Section 1 Page 6

User 1 : SCOTT User 2 : HR

EMP
GRANT SELECT ON EMP
TO HR ;
REVOKE SELECT ON EMP
FROM HR ;
TRY !!!!
SQL> SHOW USER ;
USER is "SCOTT"
SQL> CONNECT
Enter user-name: HR
Enter password: *****
Connected.
SQL> SHOW USER ;
USER is "HR"
ENAME SAL
A
100
B
200
SELECT *
FROM [Link] ;
ENAME SAL
A
100
B
200
EMP
SELECT *
FROM [Link] ;
ENAME SAL
A
100
B
200
SELECT *
FROM [Link] ;

New Section 1 Page 7

p
Connected.
SQL> SHOW USER ;
USER is "HR"
SQL> SELECT *
2 FROM [Link];
FROM [Link]
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> CONNECT
Enter user-name: SCOTT
Enter password: *****
Connected.
SQL> GRANT SELECT ON EMP TO HR;
Grant succeeded.
SQL> CONNECT
Enter user-name: HR
Enter password: *****
Connected.
SQL> SELECT *
2 FROM [Link];
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

New Section 1 Page 8

T1
T2
T3
What is Normalization ?
" It is the process of reducing a large table into smaller tables in order to remove
redundancies and anomalies by identifying their functional dependencies is
known as Normalization . "
Or
"The process of decomposing a large table into smaller table is known as
Normalization ."
Or
"Reducing a table to its Normal Form is known as Normalization . "
What is Normal Form ?
A table without redundancies and anomalies are said to be in Normal Form .
Levels of Normal From .
First Normal Form ( 1NF )
1.
Second Normal Form ( 2NF )
2.
Third Normal Form ( 3NF )
3.
Boyce - Codd Normal Form ( BCNF )
4.
Note : If any Table / entity is reduced to 3NF , then the table is said to be normalized.
T
DAY 22
Friday, August 14, 2020
9:39 AM

New Section 1 Page 1

QID NAME C1
C2
C3
1
A
JAVA
MT
2
B
JAVA SQL
3
C
SQL MT
Eid
ename sal
Deptno dname Loc
1
A
100
10
D1
L1
2
B
120
20
D2
L2
3
C
320
10
D1
L1
4
D
251
10
D1
L1
Eid - ename ,sal
Deptno - dname , loc
:- ( Eid , deptno ) -> ( Ename , Sal , Dname , Loc ) composite key attribute
results in PFD
R1 - ( EID , ENAME , SAL )
R2 - ( DEPTNO , DNAME , LOC )
First Normal Form ( 1NF ) :
1.
- No duplicates records .
- Multivalued data should not be present .
QSPIDERS
QID NAME COURSE
1
A
JAVA
2
B
JAVA , SQL
3
C
MT , SQL
1
A
MT
Second Normal Form ( 2NF )
2.
- Table should be in 1NF
- Table should not have Partial Functional Dependency .
EMPLOYEE - ( EID , ENAME , SAL , DEPTNO , DNAME , LOC )
Third Normal Form ( 3NF )
3.
- Table should be in 2NF .
- Table should not have Transitive Functional Dependency .
Eid
ename sal
1
A
100
2
B
120
3
C
320
4
D
251
Deptno dname Loc
10
D1
L1
20
D2
L2

New Section 1 Page 2

:- Transitive Functional Dep

R1- ( eid , ename , comm )
R2- ( pincode , state , country )
Employee - ( EID , Ename , Sal , comm , Pin code , state , country )
EID -> ENAME
SAL
COMM
COUNTRY
PINCODE -> STATE

Prepare a RESUME !!
➢
Reference Notes : [Link]/roSQLQCDM34
➢
Mail_Id : [Link]@[Link] [ resume ]
➢
Instagram : ro_sql_helpmate / rohan_singh_ro
➢
Any further details contact your respective branches
➢
Qspiders Basavanagudi [ BANGALORE ]
➢
PHNO: 9845687781 , 9686700900
For Reference : [Link]/hVjjxE
➢

New Section 1 Page 3