oracle :- ------------ 1 SQL (structured query language) 2 PL/SQL (procedural language / sql) SQL

databases :- ------------------------ oracle sql server mysql postgresql NoSQL database :- ---------------
---------- mongodb Database :- --------------- => a db is a organized collection of interrelated data.
for ex a bank db stores data related to customers, accounts and transactions etc and a univ db
stores data related to students,courses and faculty etc. Types of Databases :- --------------------------
------ 1 OLTP DB (online transaction processing) 2 OLAP DB (online analytical processing) =>
organizations uses OLTP db for storing day-to-day transactions and OLAP for analysis. => OLTP
is for running business and OLAP is for to analyze business. oltp :- cust cid name addr 1000
sachin mum olap :- cust name addr start_date end_date 1000 sachin hyd 2020 2022- 1000
sachin blr 2023 2024 1000 sachin mum 2025 => day-to-day operations on db includes C create R
read U update D delete DBMS :- ----------- => DBMS stands for Database Management System
=> DBMS is a software used to create and to work with db => DBMS is an interface between user
and database. USER-----------DBMS---------------DB Evolution of DBMS (Types of DBMS) :- ---------
------------------------------------------- 1960 fms (file mgmt system) 1970 hdbms (hierarchical dbms)
ndbms (network dbms) 1980 rdbms (relational dbms) 1990 ordbms (object relational dbms)
RDBMS :- -------------- => RDBMS stands for relational database management system => rdbms
concepts introduced by [Link] => [Link] introduced 12 rules called codd rules => a db
software that supports all 12 rules is called perfect rdbms information rule :- --------------------------
=> according to information rule data must be organized in tables i.e. rows and columns cust cid
name addr => columns / fields / attributes 100 sachin hyd 101 rahul mum 102 vijay del => row /
record / tuple DB = collection of tables TABLE = collection of rows and columns ROW = collection
of field values COLUMN = collection of same field values => every table must contain primary key
to uniquely identify the rows ex :- empid,accno,aadharno,panno,voterid => one table related to
another table using foreign key PROJECTS projid pname duration cost client 1000 ABC 5 300
TATA MOTORS 1001 KLM 3 200 KLM 1002 PQR 4 400 L&T EMP empid ename job sal projid =>
foreign key 100 SE 40K 1000 101 SSE 50K 1001 102 TL 80K 1002 RDBMS features :- --------------
------------ 1 easy to access (R) and manipulate ( C,U,D) data 2 less redundency (duplication of
data) 3 more security 4 gurantees data quality 5 supports data sharing 6 supports transactions
(ACID properties) A atomocity C consistency I isolation D durability RDBMS softwares :- ------------
------------ SQL Databases :- ------------------------ ORACLE from oracle corp MYSQL from oracle
corp SQL SERVER from microsoft POSTGRESQL from postgresql forum RDS from amazon
NoSQL Databases :- ----------------------------- MongoDB cassandra ORDBMS :- ---------------- =>
object relational dbms => It is the combination of rdbms & oops ordbms = rdbms + oops
(reusability) => rdbms doesn't support reusability but ordbms supports reusability RDBMS :- -------
------ cust cid name hno street city state pin student sno sname hno street city state pin emp
empid ename hno street city state pin ORDBMS :- ----------------- ADDR hno street city state pin
CUST cid cname addr STUDENT sid sname addr EMP empid ename addr ORDBMS softwares :-
----------------------------- oracle postgresql sql server mysql DB Development Life Cycle :- -------------
--------------------------- Analysis Design Development Testing Implementation / Deployment
Maintenance Design :- ------------- => Designing db means designing tables => DB is designed by
DB Designers & Architects => DB is designed by using 1 ER Model (Entity Relationship) 2
Normalization Development :- ---------------------- => db is developed by DB Developers & DBAs =>
DB is developed by using any rdbms tools like oracle Developers DBAs creating tables
installation of oracle creating views creating database creating synonyms creating logins for users
creating sequences db backup & restore creating indexes db export & import creating procedures
creating functions creating triggers writing queries Testing :- -------------- => DB is tested by QA
(Quality Analyst) team by 1 manual testing 2 automation testing Deployment :- ------------------- =>
deploying db means moving db from DEV server to PROD server => after deploying db end users
can use db for day-to-day operations => oracle db can be deployed in two ways 1 on premises 2
on cloud => in " on premises " oracle db is deployed in server managed by client. => from 12c
onwards oracle db can also be deployed in cloud server managed by cloud service provider for ex
amazon. summary :- what is db ? what is dbms ? what is rdbms ? what is ordbms ? what is db
development life cycle ?
===========================================================================
ORACLE ======== => oracle is basically a rdbms software and also supports ordbms features
and used to create and to manage database. => oracle can be used for db development and
administration versions of oracle :- -----------------------------
2,3,4,5,6,7,8i,9i,10g,11g,12c,18c,19c,21c,23ai i => internet g => grid c => cloud ai => artificial
intelligence => from "8i" onwards oracle db can used for web applications => g stands for grid ,
from 10g onwards oracle db can be accessed through multiple servers (grid) and advantage of
grid is it improves db availability. => c stands for cloud , from 12c onwards oracle db can be
deployed in cloud server CLIENT / SERVER Architecture :- ========================== 1
SERVER 2 CLIENT server :- ----------- => server is a system where oracle is installed and running
=> server manages two memories 1 DB 2 INSTANCE => DB is created in harddisk and acts as
permanent storage => INSTANCE is created in ram and acts as temporary storage => data
temporarly stored in instance and after saving it is copied to db. client :- ----------- => client is also
a system from where users can 1 connects to server 2 submit requests to server 3 receive
response from server client tool :- --------------- 1 sqlplus (CUI based) 2 sql developer (GUI based)
user-------sqlplus-------------------------------oracle--------------db user-----mysqlworkbench-----------------
---mysql--------------db 6-oct-25 SQL :- ---------- => SQL stands for structured query language => It
is a language used to communicate with oracle => user communicates with oracle by sending
commands called queries. => a query is a command / instruction / question given to oracle to
perform some operation on db. => SQL is originally introduced by IBM and initial name of this
language was "SEQUEL" and later it is renamed to SQL. => SQL is common to all rdbms
database softwares user-----sqlplus---------------------sql----------------------oracle------------db user-----
mysqlworkbench----------sql------------------mysql-----------db user-------ssms------------------------sql-----
-------------sql server-------db => based on operations SQL is categorized into following 5
sublanguages DDL (Data Definition Lang) DML (Data Manipulation Lang) DQL / DRL (Data Query
Lang / Data Retrieval Lang) TCL (Transaction Control Lang) DCL (Data Control Lang) SQL DDL
DML DQL TCL DCL create insert select commit grant alter update rollback revoke drop delete
savepoint truncate merge rename flashback purge => SQL commands are not case sensitive =>
commands must be terminated with ';' SCHEMA :- --------------- => schema means user => a user
in oracle db is called schema SERVER DATABASE USER TABLE DATA SERVER ORCL SYS /
MANAGER DBA SYSTEM /MANAGER DBA How to connect to oracle :- ---------------------------------
--- => open sqlplus and enter username and password USERNAME :- SYSTEM PASSWORD :-
MANAGER OR USERNAME :- SYSTEM / MANAGER creating user/schema/account :- -------------
------------------------------- => only DBAs are having permissions to create new user step 1 :-
connect as DBA USERNAME :- SYSTEM PASSWORD :- MANAGER step 2 :- create user syntax
:- ------------ CREATE USER IDENTIFIED BY DEFAULT TABLESPACE USERS QUOTA
UNLIMITED ON USERS ; Ex :- SQL>CREATE USER BATCH53 IDENTIFIED BY ORACLE
DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS ; note :- user "BATCH53" can
use unlimited memory in users tablespace step 3 :- granting permissions to user SQL>GRANT
CONNECT,RESOURCE TO BATCH53 ; connect => to connect to db resource => to create
objects like tables etc dba => all permissions note :- if connect,resource are granted then user is
called normal user if dba permission is granted then user is called admin changing password :- ---
-------------------------- => both user & dba can change password 1 by user (BATCH53/ORACLE)
SQL>PASSWORD Changing password for BATCH53 Old password: ORACLE New password:
NARESH Retype new password: NARESH Password changed 2 by admin
(SYSTEM/MANAGER) SQL>ALTER USER BATCH53 IDENTIFIED BY TIGER ; Download &
Install :- ------------------------------ download :- ---------------
[Link] 1 create a folder on desktop
(for ex oracle21c) 2 copy zip file from downloads to oracle 21c folder 3 unzip 4 click on setup
installation :- ----------------- [Link]
database/21/xeinw/[Link] 7-oct-25 Datatypes in oracle :-
================ => a datatype specifies 1 what type of the data allowed in a column 2
amount of memory allocated for column DATATYPES CHAR NUMERIC DATE BINARY ASCII
UNICODE number(p) date bfile number(p,s) timestamp blob char nchar varchar2 nvarchar2 long
nclob clob char(size) :- -------------- => allows character data upto 2000 chars => recommended for
fixed length char columns ex :- NAME CHAR(10) SACHIN - - - - 10 bytes wasted RAVI - - - - - - 10
bytes wasted => in char datatype extra bytes are wasted , so don't use char for variable length
fields and use char for fixend length fields. ex :- GENDER CHAR(1) M F STATE_CODE CHAR(2)
AP TG MH COUNTRY_CODE CHAR(3) IND USA VARCHAR2(size) :- ---------------------------- =>
allows character data upto 4000 chars => recommended for variable length char fields. ex :-
NAME VARCHAR2(10) SACHIN - - - - 6 bytes released RAVI - - - - - - 4 bytes released upto ver 6
it is varchar from 7 onwards varchar2 LONG :- ------------- => allows character data upto 2GB ex :-
review LONG CLOB :- (character large object) ----------- => allows character data upto 4GB ex :-
review CLOB note :- ----------- => char/varchar2/long/clob allows ascii chars (256) that includes a-
z,A-Z,0-9,special chars. => char/varchar2 datatype allows alphanumeric data. ex :- panno
char(10) vehno char(10) emailid varchar2(20) nchar / nvarchar2 / nclob :- ( n => national)
===================== => allows unicode chars (65536) that includes all ascii chars and also
chars belongs to different languages => ascii char occupies 1 byte and unicode char occupies 2
bytes Number(p) :- ---------------- => allows numeric data upto 38 digits => allows numbers without
decimal (integers) ex :- empid Number(4) 10 100 1000 10000 => not allowed aadharno
Number(12) phone Number(10) accno Number(14) Number(p,s) :- ============== => allows
numbers with decimal (float) p => precision => total no of digits allowed s => scale => no of digits
allowed after decimal ex :- salary Number(7,2) 5000 5000.56 50000.56 500000.56 => not allowed
5000.5678 => allowed => 5000.57 5000.5637 => allowed => 5000.56 savg Number(5,2) balance
Number(11,4) DATE :- ----------- => allows date & time => time is optional , if not entered then
oracle stores 12:00AM => default date format in oracle is DD-MON-YY / YYYY => time format is
hh:mi:ss ex :- DOB DATE 10-OCT-05 => 10-OCT-2005 15-MAR-98 => 15-MAR-2098 15-MAR-
1998 => 15-MAR-1998 TIMESTAMP :- ------------------- => allows date,time and also milliseconds
ex :- T TIMESTAMP 07-OCT-25 15:41:20.123 --------------- ------------ ----- DATE TIME MS 8-oct-25
Binary Types :- =========== => binary types are used for storing multimedia objects like
audio,video,images => oracle supports two binary types 1 BFILE (binary file ) 2 BLOB (binary
large object) => BFILE is called external lob because lob (audio,video,img) is stored outside db
and db stores path. => BLOB is called internal lob because lob is stored inside db. ex :- photo
BFILE sign BLOB creating tables in database :- ======================= syntax :- -----------
CREATE TABLE ( colname datatype(size) , colname datatype(size), ----------------------- ) ; Rules :-
1 tabname should start with alphabet 2 name should not contain spaces & special chars but
allows _ $ # 3 name can be upto 128 chars 4 table can have max 1000 columns 5 no of rows
unlimited tabname :- 123emp invalid emp 123 invalid emp*123 invalid emp_123 valid Ex :- =>
create table with following structure ? EMP empid ename job sal hiredate dept CREATE TABLE
emp ( empid NUMBER(4) , ename VARCHAR2(10), job VARCHAR2(10), sal NUMBER(7,2) ,
hiredate DATE, dept VARCHAR2(10) ); above command created table structure / definition /
metadata that includes columns , datatype and size. DESC :- (DESCRIBE) ------------- =>
command used to see the structure of the table syntax :- DESC ex :- SQL>DESC emp EMPID
NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(10) SAL NUMBER(7,2) HIREDATE
DATE DEPT VARCHAR2(10) inserting data into table :- ---------------------------------- => " insert"
command is used to insert data into table. => insert command creates new row => we can insert
1 single row 2 multiple rows inserting single row :- ------------------------------- INSERT INTO
VALUES(v1,v2,v3,-----------) ; Ex :- SQL>INSERT INTO emp VALUES(100,'sachin','clerk',4000,'8-
oct-25','hr'); SQL>INSERT INTO emp VALUES(101,'arvind','manager',8000,sysdate,'sales');
inserting multiple rows :- ----------------------------------- => we can execute insert command multiple
times with different values by using variables prefixed with "&". SQL>INSERT INTO emp
VALUES(&empid,&ename,&job,&sal,&hiredate,&dept); Enter value for empid: 102 Enter value for
ename: 'david' Enter value for job: 'analyst' Enter value for sal: 9000 Enter value for hiredate: '20-
apr-20' Enter value for dept: 'it' old 1: INSERT INTO emp
VALUES(&empid,&ename,&job,&sal,&hiredate,&dept) new 1: INSERT INTO emp
VALUES(102,'david','analyst',9000,'20-apr-20','it') 1 row created. SQL> / (to execute previous
command) Enter value for empid: 103 Enter value for ename: 'vijay' Enter value for job: 'clerk'
Enter value for sal: 6000 Enter value for hiredate: '10-FEB-18' Enter value for dept: 'sales' old 1:
INSERT INTO emp VALUES(&empid,&ename,&job,&sal,&hiredate,&dept) new 1: INSERT INTO
emp VALUES(103,'vijay','clerk',6000,'10-FEB-18','sales') 1 row created. NOTE :- => above insert
commands inserted data into instance (ram) which is tempoary to save this data execute commit
and after executing commit data is copied to db SQL>COMMIT ; => in normal exit opeations are
saved and in abnormal exit operations not saved SQL> EXIT ; => normal exit closing window =>
abnormal exit Inserting nulls :- ----------------------- => a null means blank of empty => it is not equal
to 0 or space => nulls are inserted when value is absent => nulls can be inserted in two wasy
method 1 :- -------------- SQL>INSERT INTO emp VALUES(105,'ravi','' , null,'22-JAN-23','hr');
method 2 :- --------------- SQL>INSERT INTO emp(empid,ename,hiredate,dept)
VALUES(106,'satish',sysdate,'it'); remaining two fileds filled with nulls 9-oct-25 Operators in
ORACLE :- ---------------------------------- 1 Arithmetic operators => + - * / 2 Relational operators => >
>= < <= = <> != 3 Logical operators => AND OR NOT 4 Special operators => BETWEEN IN LIKE
IS ANY ALL EXISTS 5 Set operators => UNION UNION ALL INTERSECT MINUS Displaying Data
:- ------------------------ => "select" command is used to display data from table. => we can display
all rows and all columns => we can display specific rows and specific columns syntax :- SELECT
col1,col2,col3,--- / * FROM tabname SQL = ENGLISH QUERIES = SENTENCES CLAUSES =
WORDS FROM clause => specify tablename SELECT clause => specify column names * => all
columns => display employee names and salaries ? SELECT ename,sal FROM emp ; => display
employee names and jobs ? SELECT ename,job FROM emp ; => display names,jobs,salaries
and hiredates ? SELECT ename,job,sal,hiredate FROM emp; => display all the data from emp ?
SELECT * FROM emp ; WHERE clause :- ------------------------ => used to select specific row/rows
from table => where clause is always associated with condition SELECT columns FROM
tabname WHERE condition ; condition :- --------------- COLNAME OP VALUE => condition always
returns TRUE / FALSE. => OP must be any relational operator like > >= < <= = <> != => if cond =
true then row is selected => if cond = false row is not selected Ex :- => display employee details
whose id = 103 ? SELECT * FROM emp WHERE empid = 103 ; => display employee details
whose name is david ? SELECT * FROM emp WHERE ename = 'david' ; => employee details
earning more than 10000 ? SELECT * FROM emp WHERE sal > 10000 ; => employee details
joined after 2020 ? SELECT * FROM emp WHERE hiredate > 2020 ; => ERROR date number
note :- when comparing both values type must be same SELECT * FROM emp WHERE hiredate
> '31-DEC-2020' ; => employees joined before 2020 ? SELECT * FROM emp WHERE hiredate <
'01-JAN-2020' ; => employees not working for hr dept ? SELECT * FROM emp WHERE dept <>
'hr' ; compound condition :- ----------------------------- => multiple conditions combined with AND / OR
operators called compound condition WHERE cond1 AND cond2 RESULT T T T T F F F T F F F F
WHERE cond1 OR cond2 RESULT T T T T F T F T T F F F Ex :- => employees working as
clerk,manager ? SELECT * FROM emp WHERE job='clerk' OR job='manager' ; => employees
whose id = 100,103,105 ? SELECT * FROM emp WHERE empid=100 OR empid=103 OR
empid=105 ; => employees working as clerk earning more than 5000 ? SELECT * FROM emp
WHERE job='clerk' AND sal > 5000 ; => display sales dept manager details ? SELECT * FROM
emp WHERE dept='sales' AND job='manager' ; => employees earning more than 5000 and less
than 10000 ? SELECT * FROM emp WHERE sal > 5000 AND sal < 10000 ; => employees joined
in 2020 year ? SELECT * FROM emp WHERE hiredate >= '01-JAN-2020' AND hiredate <= '31-
DEC-2020' ; => employees working as clerk,manager and earning more than 5000 ? SELECT *
FROM emp WHERE job='clerk' OR job='manager' AND sal > 5000 ; ------------- -------------------------
------------------- above query returns clerks earning less than 5000 because sal > 5000 is applied
only to manager but not to clerk because operator AND has got more priority than operator OR ,
to overcome this group the conditions by using ( ) . SELECT * FROM emp WHERE ( job='clerk'
OR job='manager' ) AND sal > 5000 ; ------------------------------------------- ---------------- => CREATE
TABLE student ( sid NUMBER(2), sname VARCHAR2(10), s1 NUMBER(3), s2 NUMBER(3), s3
NUMBER(3) ); INSERT INTO student VALUES(1,'A',80,90,70); INSERT INTO student
VALUES(2,'B',30,60,50); INSERT INTO student VALUES(3,'C',60,30,20); INSERT INTO student
VALUES(4,'D',20,30,10); student sid sname s1 s2 s3 1 A 80 90 70 2 B 30 60 50 3 C 60 30 20 4 D
20 30 10 => list of students who are passed ? SELECT * FROM student WHERE s1>=35 AND
s2>=35 AND s3>=35 ; => list of students who are failed ? SELECT * FROM student WHERE
s1<35 OR s2<35 OR s3<35 ; => list of students who are failed exactly in 1 subject ? SELECT *
FROM student WHERE (s1<35 AND s2>=35 AND s3>=35) OR (s1>=35 AND s2<35 AND
s3>=35) OR (s1>=35 AND s2>=35 AND s3<35 ) ; => list of students who are failed exactly in 2
subjects ? SELECT * FROM student WHERE (s1<35 AND s2<35 AND s3>=35) OR (s1<35 AND
s2>=35 AND s3<35) OR (s1>=35 AND s2<35 AND s3<35 ) ; => list of students who are failed in
all 3 subjects ? SELECT * FROM student WHERE s1<35 AND s2<35 AND s3<35 ; IN operator :- -
----------------- => use IN operator for list comparison => use IN operator for "=" comparison with
multiple values WHERE colname = v1,v2,v3,--- => INVALID WHERE colname IN (v1,v2,v3,---) =>
VALID => employees whose id = 100,103,105 ? SELECT * FROM emp WHERE empid IN
(100,103,105) ; => employees working as clerk,manager ? SELECT * FROM emp WHERE job IN
('clerk','manager') ; => employees not working for dept hr,it ? SELECT * FROM emp WHERE dept
NOT IN ('hr','it') ; BETWEEN operator :- ----------------------------- => use between operator for range
comparison ex :- 5000 to 10000 2020 to 2025 WHERE colname BETWEEN v1 AND v2 =>
employees earning between 5000 and 10000 ? SELECT * FROM emp WHERE sal BETWEEN
5000 AND 10000 ; (sal >= 5000 AND sal <= 10000) => employees joined in 2020 year ? SELECT
* FROM emp WHERE hiredate BETWEEN '01-JAN-2020' AND '31-DEC-2020' ; => who are not
joined in 2020 ? SELECT * FROM emp WHERE hiredate NOT BETWEEN '01-JAN-2020' AND
'31-DEC-2020' ; COL = V1 OR COL = V2 ========================> COL IN (V1,V2,V3)
OR COL = V3 COL >= V1 AND ========================> COL BETWEEN V1 AND V2
COL <= V2 => employees working as clerk,manager and earning between 5000 and 10000 and
not joined in 2020 and not working for dept hr,it ? SELECT * FROM emp WHERE job IN
('clerk','manager') AND sal BETWEEN 5000 AND 10000 AND hiredate NOT BETWEEN '01-JAN-
2020' AND '31-DEC-2020' AND dept NOT IN ('hr','it') ; => list of samsung,redmi,realme mobiles
price between 10000 and 20000 ? products prodid pname price category brand SELECT * FROM
products WHERE brand IN ('samsung','redmi','realme') AND price BETWEEN 10000 AND 20000
AND category='mobiles' ; => list of male customers staying in hyd,blr,mum age between 20 and
40 ? CUST cid name city age gender SELECT * FROM cust WHERE gender='M' AND city IN
('hyd','blr','mum') AND age BETWEEN 20 AND 40 ; Question :- SELECT * FROM emp WHERE
sal BETWEEN 10000 AND 5000 ; a ERROR b returns rows c returns no rows d none ans :- c
WHERE sal BETWEEN 5000 AND 10000 (sal >= 5000 AND sal <=10000) WHERE sal
BETWEEN 10000 AND 5000 (sal >= 10000 AND sal <=5000) note :- => use between operator
with lower and upper but not with upper and lower LIKE operator :- -------------------- => use LIKE
operator for pattern comparison ex :- name starts with 's' name ends with 'd' name contains 'a'
WHERE colname LIKE 'pattern' => pattern contains alphabets,digits,special chars and also
wildcard chars wildcard chars :- --------------------- % => 0 or many chars _ => exactly 1 char =>
employees name starts with 's' ? SELECT * FROM emp WHERE ename LIKE 's%' ; => name
ends with 'd' ? SELECT * FROM emp WHERE ename LIKE '%d' ; => name contains 'a' ?
SELECT * FROM emp WHERE ename LIKE '%a%' ; => 'a' is the 4th char in their name ?
SELECT * FROM emp WHERE ename LIKE '___a%' => 'a' is the 4th char from last ? SELECT *
FROM emp WHERE ename LIKE '%a___' ; => name contains 4 chars ? SELECT * FROM emp
WHERE ename LIKE '____' ; 11-oct-25 => employees joined in october month ? DD-MON-YY
SELECT * FROM emp WHERE hiredate LIKE '%OCT%' ; WHERE hiredate LIKE '___OCT___' ;
=> employees joined in 2020 year ? SELECT * FROM emp WHERE hiredate LIKE '%20' ; =>
employees joined in 1st 9 days of any month any year ? SELECT * FROM emp WHERE hiredate
LIKE '0%' ; => CUST cid cname 10 sachin_tendulkar 11 virat%kohli 12 mahendra_singh_dhoni
list of customers name contains "_" ? SELECT * FROM cust WHERE cname LIKE '%_%' ; above
query returns all customer records because "_" is not treated as search char and it is treated as
wildcard char, to overcome this use escape char. SELECT * FROM cust WHERE cname LIKE
'%\_%' ESCAPE '\' ; => customers name contains "%" ? SELECT * FROM cust WHERE cname
LIKE '%\%%' ESCAPE '\' ; => customers name contains 2 "_" ? SELECT * FROM cust WHERE
cname LIKE '%\_%\_%' ESCAPE '\' ; IS operator :- ------------------- => use IS operator for NULL
comparison WHERE colname IS NULL WHERE colname IS NOT NULL => employees not
earning salary ? SELECT * FROM emp WHERE sal IS NULL ; => employees earning salary ?
SELECT * FROM emp WHERE sal IS NOT NULL ; => employees not assigned to any job ?
SELECT * FROM emp WHERE job IS NULL ; summary :- ----------------- WHERE colname IN
(v1,v2,v3,------) WHERE colname BETWEEN v1 AND v2 WHERE colname LIKE 'pattern' WHERE
colname IS NULL ALIAS :- ------------ => alias means another name => used to change column
heading COLNAME / EXPR AS ALIAS Ex :- => display ENAME ANNUAL SAL ? SELECT
ename,sal*12 AS annsal FROM emp ; SELECT ename,sal*12 AS "ANNUAL SAL" FROM emp ;
=> display ENAME EXPERIENCE ? SELECT ename, hiredate, SYSDATE-HIREDATE AS EXPR
FROM emp ; sachin 08-OCT-25 3.63824074 arvind 08-OCT-25 3.01428241 david 20-APR-20
2000.63824 vijay 10-FEB-18 2800.63824 SELECT ename, hiredate, (SYSDATE-HIREDATE)/365
AS EXPR FROM emp ; sachin 08-OCT-25 .00996921 arvind 08-OCT-25 .008259735 david 20-
APR-20 5.48120209 vijay 10-FEB-18 7.67298291 SELECT ename, hiredate,
ROUND((SYSDATE-HIREDATE)/365) AS EXPR FROM emp ; sachin 08-OCT-25 0 arvind 08-
OCT-25 0 david 20-APR-20 5 vijay 10-FEB-18 8 => display ENAME SAL HRA DA TAX TOTSAL ?
HRA = house rent allowance = 20% on sal DA = dearness allowance = 30% on sal TAX = 10% on
sal TOTSAL = SAL + HRA + DA - TAX SELECT ename ,sal, sal*0.2 as hra, sal*0.3 as da, sal*0.1
as tax, sal + (sal*0.2) + (sal*0.3) - (sal*0.1) as totsal FROM emp ; ename sal hra da tax totsal
sachin 4000 800 1200 400 5600 => display SNO TOTAL AVG ? STUDENT sno sname s1 s2 s3 1
A 80 90 70 2 B 60 50 40 SELECT sno,s1+s2+s3 as total , (s1+s2+s3)/3 as avg FROM student ; 1
240 80 2 150 50 13-oct-25 CASE statement :- ------------------------- => used to implement if-else in
sql queries => using case statement we can return values based on condition => case statements
are 2 types 1 simple case 2 searched case simple case :- --------------------- => use simple case
when conditions based on "=" operator CASE colname WHEN value1 THEN return value1 WHEN
value2 THEN return value2 ---------------------------- [ ELSE return value] END Ex :- => display
ENAME JOB ? IF job=clerk display WORKER job=manager BOSS others EXECUTIVE SELECT
ename , case job when 'clerk' then 'worker' when 'manager' then 'boss' else 'executive' end as job
FROM emp ; => display CID CNAME GENDER ? IF gender='M' display MALE F display FEMALE
CUST cid cname gender 10 A M 11 B F SELECT cid,cname, CASE gender WHEN 'M' THEN
'MALE' WHEN 'F' THEN 'FEMALE' END as gender FROM cust ; 10 A MALE 11 B FEMALE
searched case :- ---------------------- => use searched case when conditions not based on "="
operator CASE WHEN cond1 THEN return value1 WHEN cond2 THEN return value2 ----------------
----- [ELSE return value] END Ex :- => display ENAME SAL SALRANGE ? if sal<5000 display
LOSAL sal>5000 display HISAL otherwise AVGSAL SELECT ename,sal, CASE WHEN sal<5000
THEN 'LOSAL' WHEN sal>5000 THEN 'HISAL' ELSE 'AVGSAL' END as salrange FROM emp =>
display SID TOTAL AVG RESULT ? if all subject marks >=35 result is pass otherwise fail
STUDENT sid sname s1 s2 s3 1 A 80 90 70 2 B 60 50 30 SELECT sid , s1+s2+s3 as total,
(s1+s2+s3)/3 as avg, CASE WHEN s1>=35 AND s2>=35 AND s3>=35 THEN 'pass' ELSE 'fail'
END as result FROM student ;
================================================================= 14-oct-25
ORDER BY clause :- ================ => ORDER BY clause is used to sort table data =>
using ORDER BY clause we can sort based on one or more columns => we can sort either in
ascending or in descending order. SELECT columns / * FROM tabname [WHERE cond] ORDER
BY colname ASC / DESC , colname ASC/DESC , colname ASC/DESC => default sort order is
ASC ASC DESC NUMBERS small - high high - small CHAR a - z z - a DATE old - new new - old
Ex :- => display employee list name wise ascending order ? SELECT * FROM emp ORDER BY
ename ASC ; => display employee list sal highest paid employee should be displayed first ?
SELECT * FROM emp ORDER BY sal DESC ; => arrange employee list hiredate wise employee
who joined first arrange first ? SELECT * FROM emp ORDER BY hiredate ASC ; sorting based on
multiple columns :- ------------------------------------------------- => arrange employee list dept wise asc
and with in dept sal wise desc order ? SELECT empno,ename,sal,deptno FROM emp ORDER
BY deptno ASC , sal DESC ; 1 A 3000 20 5 E 6000 10 2 B 6000 30 3 C 4000 10 3 C 4000 10
==================> 4 D 5000 20 4 D 5000 20 1 A 3000 20 5 E 6000 10 2 B 6000 30 6 F
3000 30 6 F 3000 30 => arrange employee list dept wise asc and with in dept hiredate wise asc ?
SELECT empno,ename,hiredate,deptno FROM emp ORDER BY deptno ASC,hiredate ASC ; =>
arrange student list avg wise desc if avg is same then m wise desc if m also same then p wise
desc ? student sno sname m p c 1 A 80 90 70 2 B 60 50 70 3 C 90 80 70 4 D 90 70 80 SELECT
sno,sname,m,p,c,(m+p+c)/3 as avg FROM student ORDER BY (m+p+c)/3 DESC,m DESC,p
DESC ; 3 C 90 80 70 80 4 D 90 70 80 80 1 A 80 90 70 80 2 B 60 50 70 60 ORDER BY avg DESC
=> valid WHERE avg >= 70 => invalid note :- => column alias cannot be used in WHERE clause
because WHERE clause is executed before SELECT clause. => column alias can be used in
ORDER BY because ORDER BY clause is executed after SELECT clause. order of execution :- --
------------------------ FROM WHERE SELECT ORDER BY => display employees working as
clerk,manager arrange output sal wise desc order ? SELECT empno,ename,job,sal FROM emp
WHERE job IN ('CLERK','MANAGER') ORDER BY sal DESC ; FROM emp :- ------------------- 1 A
CLERK 3000 2 B MANAGER 5000 3 C SALESMAN 2000 4 D CLERK 4000 WHERE job IN
('CLERK','MANAGER') :- ------------------------------------------------------- 1 A CLERK 3000 2 B
MANAGER 5000 4 D CLERK 4000 SELECT empno,ename,job,sal :- -------------------------------------
------- 1 A CLERK 3000 2 B MANAGER 5000 4 D CLERK 4000 ORDER BY sal DESC :- -------------
---------------------- 2 B MANAGER 5000 4 D CLERK 4000 1 A CLERK 3000 => display employee
details joined in 1981 year and arrange output name wise asc ? SELECT empno,ename,hiredate
FROM emp WHERE hiredate LIKE '%81' ORDER BY ename ASC ; controlling NULLs :- ------------
---------------- => In sorting by default nulls are treated high. => In ascending order nulls arranged
last => In descending order nulls arranged first => To control this use NULLS FIRST / LAST
options Ex :- => arrange employee list comm wise desc order but display nulls last ? SELECT
empno,ename,sal,comm FROM emp ORDER BY comm DESC NULLS LAST ; EMPNO ENAME
SAL COMM ---------- ---------- ---------- ---------- 7844 TURNER 1500 0 7499 ALLEN 1600 300 7521
WARD 1250 500 7654 MARTIN 1250 1400 7788 SCOTT 3000 7839 KING 5000 7876 ADAMS
1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 7698 BLAKE 2850 7566 JONES
2975 7369 SMITH 800 7782 CLARK 2450 => arrange employee list comm wise asc display nulls
first ? SELECT empno,ename,sal,comm FROM emp ORDER BY comm ASC NULLS FIRST ;
EMPNO ENAME SAL COMM ---------- ---------- ---------- ---------- 7369 SMITH 800 7782 CLARK
2450 7902 FORD 3000 7900 JAMES 950 7876 ADAMS 1100 7566 JONES 2975 7698 BLAKE
2850 7934 MILLER 1300 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 0 7499
ALLEN 1600 300 7521 WARD 1250 500 7654 MARTIN 1250 1400 15-oct-25 DISTINCT clause :-
--------------------------- => distinct clause eliminates duplicates from select stmt output SELECT
DISTINCT col1,col2,---- FROM tabname [WHERE cond] [ORDER BY col ASC / DESC , ----] Ex 1
:- SELECT DISTINCT JOB FROM EMP ; JOB --------- CLERK SALESMAN ANALYST MANAGER
PRESIDENT Ex 2 :- SELECT DISTINCT DEPTNO FROM EMP ; DEPTNO ---------- 30 10 20
FETCH clause :- ---------------------- => FETCH clause is used to display TOP n rows from table
SELECT [DISTINCT] col1,col2,--- FROM tabname [WHERE cond] [ORDER BY col ASC/DESC ,--
-] [OFFSET ROWS ] FETCH FIRST / NEXT ROW/ROWS ONLY ; Ex :- => display first 5 rows
from emp table ? SELECT empno,ename,sal FROM emp FETCH FIRST 5 ROWS ONLY ; =>
display 5th row ? SELECT empno,ename,sal FROM emp OFFSET 4 ROWS FETCH NEXT 1
ROW ONLY ; => display 5th row to 10th row ? SELECT empno,ename,sal FROM emp OFFSET 4
ROWS FETCH NEXT 6 ROWS ONLY ; => display top 5 highest paid employees ? SELECT
empno,ename,sal FROM emp ORDER BY sal DESC FETCH FIRST 5 ROWS ONLY ; => display
top 3 max salaries ? SELECT DISTINCT sal FROM emp ORDER BY sal DESC FETCH FIRST 3
ROWS ONLY ; SAL ---------- 5000 3000 2975 => display 5th max salary ? SELECT DISTINCT sal
FROM emp ORDER BY sal DESC OFFSET 4 ROWS FETCH NEXT 1 ROW ONLY ; SAL ----------
2450 => display top 3 employees based on experience ? SELECT empno,ename,hiredate FROM
emp ORDER BY hiredate ASC FETCH FIRST 3 ROWS ONLY ; => latest 5 transactions of
particular customer ? TRANSACTIONS trid ttype tdate tamt accno 1 W 15-OCT-25 2000 100 2 W
15-OCT-25 1000 190 3 122 SELECT * FROM transactions WHERE accno = 100 ORDER BY
tdate DESC FETCH FIRST 5 ROWS ONLY summary :- WHERE => to select specific rows
ORDER BY => to sort rows DISTINCT => to eliminate duplicates FETCH => to select top n rows
================================================================ DML
commands :- (Data Manipulation Lang) ------------------------ INSERT UPDATE DELETE MERGE =>
all DML commands acts on table data. => DML commands acts on instance (ram). => to save the
operation execute commit command => to cancel the operation rollback command UPDATE
command :- ----------------------------- => command used to modify table data. => we can update all
rows and specific rows => we can update single column or multiple columns UPDATE tabname
SET colname = value , colname = value , ---------- [WHERE cond] ; Ex :- => update all employees
comm with 500 ? UPDATE emp SET comm = 500 ; => update employees comm with 500 whose
comm = null ? UPDATE emp SET comm = 500 WHERE comm IS NULL ; => update employees
comm with NULL whose comm <> null ? UPDATE emp SET comm = NULL WHERE comm IS
NOT NULL ; NULL assignment = NULL comparison IS => update sal with 1000 and comm with
500 whos empno = 7369 ? UPDATE emp SET sal = 1000 , comm = 500 WHERE empno = 7369 ;
=> increment sal by 20% and comm by 10% those working as salesman and joined in 1981 year
? UPDATE emp SET sal = sal + (sal*0.2) , comm = comm + (comm*0.1) WHERE
job='SALESMAN' AND hiredate LIKE '%81' ; => transfer employees from 10th dept to 20th dept ?
UPDATE emp SET deptno = 20 WHERE deptno = 10 ; => increase the price of
samsung,redmi,realme mobiles by 10% ? products prodid pname price category brand UPDATE
products SET price = price + (price*0.1) WHERE brand IN ('samsung','redmi','realme') AND
category='mobiles' ; => increment employee salaries as follows ? if deptno = 10 incr sal by 10%
20 15% 30 20% others 5% UPDATE emp SET sal = CASE deptno WHEN 10 THEN sal +
(sal*0.1) WHEN 20 THEN sal + (sal*0.15) WHEN 30 THEN sal + (sal*0.2) ELSE sal+(sal*0.05)
END ; => swap employee jobs whose empno = 7369,7499 ? UPDATE emp SET job = CASE
empno WHEN 7369 THEN 'SALESMAN' WHEN 7499 THEN 'CLERK' END WHERE empno IN
(7369,7499) ; ===============================================================
DELETE command :- ---------------------------- => command used to delete row/rows from table => it
can delete all rows or specific rows syn :- DELETE FROM [WHERE cond] Ex :- => delete all rows
from emp ? DELETE FROM emp ; => delete employees having more than 10 years experience ?
DELETE FROM emp WHERE (SYSDATE - hiredate)/365 > 10 ; FLASHBACK :- ---------------------
=> using flashback we can see the data that exists some time back. => a query that returns past
data is called flashback query => using flashback we can recover data after commit. => we can
flashback upto 15 mins syn :- SELECT * FROM tabname AS OF TIMESTAMP ( SYSDATE -
INTERVAL ) ; Ex :- => the following query returns 5 mins back data in emp table ? SELECT *
FROM emp AS OF TIMESTAMP (SYSDATE - INTERVAL '5' MINUTE) ; Recovering data after
commit :- ------------------------------------------ step 1 :- delete data SQL>DELETE FROM emp ; step 2
:- save the operation SQL>COMMIT step 3 :- get the data that exists 5 mins back and insert that
data into current emp table SQL>INSERT INTO emp SELECT * FROM emp AS OF TIMESTAMP
(SYSDATE - INTERVAL '5' MINUTE) ; data returned by query is inserted into current emp table
17-oct-25 DDL command :- (Data Definition Lang) ----------------------- CREATE ALTER DROP
TRUNCATE RENAME FLASHBACK PURGE => all DDL commands acts on table structure => all
DDL commands are auto committed. DDL command = DDL command + COMMIT scenario 1 :-
create table a(a number(2)); insert into a values(10); insert into a values(20); insert into a
values(30); insert into a values(40); insert into a values(50); rollback ; output :- create table =>
saved inserts => cancelled scenario 2 :- create table a(a number(2)); insert into a values(10);
insert into a values(20); insert into a values(30); create table b(b number(2)); insert into b
values(40); insert into b values(50); rollback ; create table a => saved insert 10,20,30 => saved
create table b => saved insert 40,50 => cancelled ALTER command :- ----------------- => command
used to modify the table structure => using ALTER command we can 1 add columns 2 drop
columns 3 rename a column 4 modify a column Adding a column :- ------------------------- => add
column gender to emp table ? ALTER TABLE emp ADD (gender CHAR(1)); => after adding by
default the new column is filled with nulls , to insert data into the new column use update
command. UPDATE emp SET gender='m' WHERE empno=7369; UPDATE emp SET gender='f'
WHERE empno = 7499; Droping column :- ------------------------- => drop column gender from emp
table ? ALTER TABLE emp DROP (gender) ; Renaming a column :- ----------------------------- =>
rename column comm to bonus ? ALTER TABLE emp RENAME COLUMN comm TO bonus ;
SELECT empno,ename,sal,comm as bonus FROM emp ; => diff b/w rename & alias ? rename
alias 1 permanent tempoarary 2 changes column name changes column heading in select stmt
output in table Modifying a column :- ---------------------- 1 incr/decr field size 2 changing datatype
=> increase size of ename to 20 ? ALTER TABLE emp MODIFY(ename VARCHAR2(20)); =>
decrease size of ename to 10 ? ALTER TABLE emp MODIFY(ename VARCHAR2(10)); ALTER
TABLE emp MODIFY(ename VARCHAR2(5)); => ERROR (some name contains more than 5
chars) NOTE :- 1 column must be empty to decrease precision or scale of numeric field ALTER
TABLE emp MODIFY (sal NUMBER(6,2)); => error 2 column must be empty to change datatype
ALTER TABLE emp MODIFY(empno VARCHAR2(10)); => error DROP command :- ----------------
=> command used to drop table from database. => drops table structure with data. syn :- DROP
TABLE ex :- DROP TABLE emp ; => prior to 10g ver when table is dropped it is removed
permanently and from 10g onwards when table is dropped it is moved to recyclebin. => to see the
recyclebin execute the following command SQL>SHOW RECYCLEBIN ; 18-oct-25 FLASHBACK
:- -------------------- 1 flashback data 2 flashback table flashbacking data :- -------------------- step 1 :-
SQL>DELETE FROM emp ; step 2 :- SQL>COMMIT; step 3 :- SQL> INSERT INTO emp SELECT
* FROM emp AS OF TIMESTAMP(SYSDATE - INTERVAL '2' MINUTE); flashbacking table :- ------
--------------------- SQL>DROP TABLE emp ; => before 10g when table is dropped then it is
permanently deleted from db => from 10g when table is dropped then it is moved to recyclebin
How to see the recyclebin SQL>SHOW RECYCLEBIN EMP
BIN$kODIJUI1Qo2wsHyyRXjqSA==$0 TABLE 2025-10-17:15:42:46 => the following command
introduced by oracle in 10g to restore table from recyclebin FLASHBACK TABLE TO BEFORE
DROP ; Ex :- SQL>FLASHBACK TABLE emp TO BEFORE DROP ; after executing above
command table is restored with columns and rows that exists before drop. PURGE command :- ---
------------------------ => command used to delete table from recyclebin => once table deleted from
recyclebin we cannot flashback the table PURGE TABLE ; Ex :- SQL>PURGE TABLE emp ;
DROP & PURGE :- ------------------------- SQL> DROP TABLE student PURGE ; above command
drops students table and also delete the table from recyclebin How to empty recyclebin :- -----------
----------------------- SQL>PURGE RECYCLEBIN ; TRUNCATE command :- -------------------------------
-- => command deletes all the data from table but keeps structure => command will empty the
table => command will release memory allocated for table TRUNCATE TABLE Ex :-
SQL>TRUNCATE TABLE emp ; => when above command is executed oracle goes to memory
and releases all the blocks allocated for table and when blocks are released then data stored in
memory also deleted. Memory allocation in oracle :- ---------------------------------------- => when table
is created oracle allocates memory for the table as follows segment = collection of extents => by
default it is created with 1 extent extent = collection of 8 blocks 1 block = 8kb => differentiate
between DROP & DELETE & TRUNCATE ? DROP DELETE & TRUNCATE drops structure with
data deletes only data but not structure => difference between DELETE & TRUNCATE ? DELETE
TRUNCATE 1 DML DDL 2 can delete all rows can delete only all rows and also specific rows
cannot delete specific rows 3 where cond can be used where cond cannot be with delete used
with truncate 4 opertation can be rolledback operation cannot be rolledback 5 after delete we can
flashback after truncate we cannot flashback 6 deletes row-by-row deletes all rows at a time 7
slower faster 8 will not release memory will release memory RENAME :- ---------------- =>
command used to change tablename RENAME TO ; Ex :- SQL>RENAME emp TO employees ;
============================================================== 21-oct-25 Built-
in Functions in ORACLE :- ------------------------------------------- => a function accepts some input and
performs some calculation and returns one value Types of functions :- ------------------------- 1 single
row functions 2 multi row functions / aggregate functions / group functions single row functions :- -
-------------------------------- => these functions process one row at a time and returns one value 1
character functions 2 numeric functions 3 date functions 4 conversion functions 5 special
functions 6 analytical functions / window functions character functions :- -----------------------------
UPPER() :- --------------- => converts string to uppercase. UPPER(arg) arg => string => 'hello'
colname => ename Ex :- SELECT UPPER('hello') FROM DUAL ; => HELLO what is DUAL ?
DUAL is a dummy table provided by oracle used to print non db values. LOWER() :- ----------------
=> converts string to lowercase LOWER(arg) Ex :- SELECT LOWER('HELLO') FROM DUAL ; =>
hello => display EMPNO ENAME SAL ? display names in lowercase ? SELECT empno ,
LOWER(ename) as ename, sal FROM emp ; => convert names to lowercase in table ? UPDATE
emp SET ename = LOWER(ename) ; => display employee details whose name is BLAKE ?
SELECT * FROM emp WHERE ename = 'BLAKE' ; => no rows in oracle string comparision is
case sensitive i.e. uppercase and lowercase strings are not same. To perform case insensitive
comparison use UPPER / LOWER functions. SELECT * FROM emp WHERE UPPER(ename) =
'BLAKE' ; INITCAP() :- ----------------- => converts initials into capitals INITCAP(arg) Ex :- SELECT
INITCAP('sachin tendulkar') FROM DUAL ; => Sachin Tendulkar LENGTH() :- ---------------- =>
returns string length i.e. no of chars . LENGTH(arg) Ex :- SELECT LENGTH('HELLO WELCOME')
FROM DUAL ; => 13 => display ENAME LENGTH ? SELECT ename , LENGTH(ename) as LEN
FROM emp ; smith 5 allen 5 ward 4 => display employee list name contains 4 chars ? SELECT *
FROM emp WHERE ename LIKE '____' ; SELECT * FROM emp WHERE LENGTH(ename) = 4 ;
=> employees name contains more than 4 chars ? SELECT * FROM emp WHERE
LENGTH(ename) > 4 ; => arrange employee list based on length of ename ? SELECT
empno,ename,sal,LENGTH(ename) as len FROM emp ORDER BY LENGTH(ename) ASC
SUBSTR() :- ------------------- => returns part of the string. SUBSTR(string,start,[no of chars]) Ex :-
SELECT SUBSTR('hello welcome',1,5) FROM DUAL ; => hello SELECT SUBSTR('hello
welcome',7,5) FROM DUAL ; => welco SELECT SUBSTR('hello welcome',10,3) FROM DUAL ;
=> com SELECT SUBSTR('hello welcome',10) FROM DUAL ; => come SELECT SUBSTR('hello
welcome',7) FROM DUAL ; => welcome SELECT SUBSTR('hello welcome', -7,5) FROM DUAL;
=> welco SELECT SUBSTR('hello welcome', -4) FROM DUAL; => come SELECT SUBSTR('hello
welcome', -10,5) FROM DUAL; => lo we => employees name starts with 's' ? WHERE ename
LIKE 's%' SELECT * FROM emp WHERE SUBSTR(ename,1,1) = 's' ; => name ends with 's' ?
WHERE ename LIKE '%s' SELECT * FROM emp WHERE SUBSTR(ename,-1,1) = 's' ; =>
employees name starting and ending with same char ? WHERE ename LIKE 'a%a' OR ename
LIKE 'b%b' SELECT * FROM emp WHERE SUBSTR(ename,1,1) = SUBSTR(ename,-1,1) ; =>
generate emailids for employees ? empno ename emailid 7369 smith smi736@[Link] 7499
allen all749@[Link] SELECT empno,ename, SUBSTR(ename,1,3) ||
SUBSTR(empno,1,3)||'@[Link]' as emailid FROM emp ; => store emailids in db ? STEP 1 :- add
emailid column to emp table ALTER TABLE emp ADD (emailid VARCHAR2(20)); STEP 2 :-
update the column with emailids UPDATE emp SET emailid = SUBSTR(ename,1,3) ||
SUBSTR(empno,1,3)||'@[Link]' ; INSTR() :- --------------- => returns position of a character in a
string. INSTR(string,char,[start,occurance]) Ex :- SELECT INSTR('HELLO WELCOME','O') FROM
DUAL; => 5 SELECT INSTR('HELLO WELCOME','K') FROM DUAL ; => 0 SELECT
INSTR('HELLO WELCOME','O',1,2) FROM DUAL ; => 11 SELECT INSTR('HELLO
WELCOME','E',7,2) FROM DUAL ; => 13 SELECT INSTR('HELLO WELCOME','E',-1,3) FROM
DUAL ; => 2 SELECT INSTR('HELLO WELCOME','E',-7,2) FROM DUAL ; => 0 => display
employees name contains 'a' without using LIKE operator ? SELECT * FROM emp WHERE
INSTR(ename , 'a') <> 0 ; scenario :- ----------------- CUST cid cname 10 sachin tendulkar 11 virat
kohli => display CID FNAME LNAME ? SUBSTR(string,start, [no of chars ]) INSTR(string,char,
[start,occurance]) FNAME = SUBSTR(cname , 1 , INSTR(cname,' ')-1) LNAME =
SUBSTR(cname, INSTR(cname,' ')+1 ) SELECT cid , SUBSTR(cname,1,INSTR(cname,' ')-1) as
fname, SUBSTR(cname,INSTR(cname,' ')+1) as lname FROM cust ; => CUST cid cname 10
sachin ramesh tendulkar 11 mahendra singh dhoni display CID FNAME MNAME LNAME ?
FNAME = SUBSTR(cname , 1 , INSTR(cname,' ')-1) LNAME = SUBSTR(cname, INSTR(cname,'
',1,2)+1) MNAME = SUBSTR(cname,INSTR(cname,' ')+1 , INSTR(cname,' ',1,2)-INSTR(cname,'
')-1) SELECT cid , SUBSTR(cname , 1 , INSTR(cname,' ')-1) as fname ,
SUBSTR(cname,INSTR(cname,' ')+1 , INSTR(cname,' ',1,2)-INSTR(cname,' ')-1) as mname,
SUBSTR(cname, INSTR(cname,' ',1,2)+1) as lname FROM cust ; LPAD & RPAD :- -------------------
---- => both functions used to fill string with a character LPAD(string,length,char) => fills on left
side RPAD(string,length,char) => fills on right side Ex :- LPAD('HELLO',10,'*') => *****HELLO
RPAD('HELLO',10,'*') => HELLO***** RPAD('*',10,'*') => ********** => display ENAME SAL ? ***
**** SELECT ename, RPAD('*',LENGTH(sal) , '*') as sal FROM emp ; => accounts accno actype
bal 123456789234 s 10000 your a/c no XXXX9234 debited -----? method 1
LPAD(SUBSTR(accno,-4,4) , 8 ,'X') ---------------------------- 9234 method 2
LPAD('X',4'X')||SUBSTR(accno,-4,4) XXXX 9234 => credit_cards card_no exp_dt cvv pin
1234567891234567 output :- 12xxxx567 SUBSTR(card_no ,
1,2)||RPAD('x',4,'x')||SUBSTR(card_no,-3,3) LTRIM,RTRIM,TRIM :- ------------------------------- =>
used to remove spaces and unwanted chars LTRIM(string , [char]) => removes on left side
RTRIM(string,[char]) => removes on right side TRIM(string) => removes both sides Ex :- LTRIM('
HELLO ') => 'HELLO ' RTRIM(' HELLO ') => ' HELLO' TRIM(' HELLO ') => 'HELLO'
LTRIM('@@@HELLO@@@' , '@') => HELLO@@@ RTRIM('@@@HELLO@@@' , '@') =>
@@@HELLO TRIM(BOTH '@' FROM '@@@HELLO@@@') => HELLO scenario :- ---------------
CREATE TABLE STUDENT ( SID NUMBER(2), SNAME CHAR(10) ); SQL> INSERT INTO
STUDENT VALUES(10,'sachin'); SQL> INSERT INTO STUDENT VALUES(11,'nitin'); SQL>
SELECT * fROM STUDENT ; SID SNAME ---------- ---------- 10 sachin 11 nitin employees name
ends with 'n' ? SQL> SELECT * FROM STUDENT WHERE SNAME LIKE '%n' ; no rows selected
note :- sname datatype is char , so names are not ended with 'n' and ended with space use
RTRIM function to remove spaces on right side. SQL> SELECT * FROM STUDENT WHERE
RTRIM(SNAME) LIKE '%n' ; SID SNAME ---------- ---------- 10 sachin 11 nitin 23-oct-25
REPLACE() :- -------------------- => used to replace one string with another string.
REPLACE(str1,str2,str3) => in str1 , str2 replaced with str3 Ex :- REPLACE('HELLO','ELL','ABC')
=> HABCO REPLACE('HELLO','L','ABC') => HEABCABCO REPLACE(
'@@@HE@@@L@@LO@@@@' , '@','') => HELLO REPLACE('HELLO','ELO','ABC') =>
HELLO => in hiredate column replace 'SEP' with 'OCT' ? UPDATE EMP SET HIREDATE =
REPLACE(HIREDATE,'SEP','OCT'); => employees name contains exactly 1 'a' ? SELECT *
FROM emp WHERE ename LIKE '%a%' ; above query returns names contains 1 'a' and also 2 'a'
SELECT * FROM emp WHERE LENGTH(ename) - LENGTH(REPLACE(ename,'a','')) = 1 ; ex :-
blake LENGTH(ename) = 5 LENGTH(REPLACE(ename,'a','')) = 4 TRANSLATE() :- ------------------
------ => used to translate one char to another char TRANSLATE(str1,str2,str3) Ex :-
TRANSLATE('HELLO','ELO','ABC') => HABBC E => A L => B O => C
TRANSLATE('HELLO','ELO','') => NULL scenario :- ----------------- => translate function can be
used to encrypt data i.e. converting plain text to cipher text Ex :- => display ENAME SAL ?
encrypt salaries ? SELECT ename , TRANSLATE(sal , '0123456789' , '$Tb*R@j#^%') as sal
FROM emp ; jones 2975 b%#@ => remove all special chars from '@#HE$%LL^*O!*' ? SELECT
REPLACE( TRANSLATE('@#HE$%LL^*O!*' , '@#$%^*!' ,'*******') , '*' , '') FROM DUAL ; ------------
------------------------------------------------------------- **HE**LL**O** o/p :- HELLO summary :- UPPER
LOWER INITCAP LENGTH SUBSTR INSTR LPAD RPAD LTRIM RTIRM TRIM REPLACE
TRANSLATE Numeric Functions :- ------------------------------ MOD() :- ------------ => returns
remainder MOD(num1,num2) Ex :- MOD(10,5) => 0 => display employees earning multiples of
100 ? SELECT * FROM emp WHERE MOD(sal,100) = 0 ; Rounding numbers :- -----------------------
----- ROUND TRUNC CEIL FLOOR 38.567894 => 38 39 38.56 38.5678 ROUND functions :- -------
------------------- => function used to round number to integer or to decimal places => round function
acts according to average ROUND( number , [decimal places]) Ex :- ROUND(38.5678) => 39 38--
--------------------------------38.5-----------------------------------------39 number >= avg => rounded to
highest number < avg => rounded to lowest ROUND(38.4678) => 38 ROUND(38.5678,1) => 38.6
ROUND(38.5478,1) => 38.5 ROUND(38.5678,2) => 38.57 ROUND(38.5672,3) => 38.567
ROUND(386 , -1) => 390 380-------------------------------385--------------------------390 ROUND(386,-2)
=> 400 300---------------------------------350----------------------------------400 ROUND(386,-3) => 0 0------
-----------------------------500-----------------------------------1000 Ex :- SELECT ROUND(4567,-1) ,
ROUND(4567,-2) , ROUND(4567,-3) FROM DUAL ; o/p :- 4570 4600 5000 => round all employee
salaries to hundreds in table ? UPDATE emp SET sal = ROUND(sal, -2) ; => display ENAME
EXPERIENCE in years round experience to integer ? SELECT ename, ROUND((SYSDATE -
hiredate) / 365) as exprr FROM emp ; TRUNC :- ---------------- => rounds number always to lowest
TRUNC(number , [decimal places]) Ex :- TRUNC(38.9567) => 38 TRUNC(38.5678,2) => 38.56
TRUNC(386,-2) => 300 TRUNC(999,-3) => 0 24-oct-25 CEIL() :- ------------- => rounds number
always to highest CEIL(number) Ex :- CEIL(3.1) => 4 FLOOR() :- ---------------- => rounds number
always to lowest FLOOR(number) Ex :- FLOOR(3.9) => 3 DATE functions :- ------------------------
sysdate + 10 => adds 10 days to sysdate sysdate - 10 => subtracts 10 days from sysdate sysdate
- hiredate => returns difference in days sysdate + hiredate => invalid ROUND & TRUNC :- ---------
------------------- => these two functions can also be used to round dates => dates can be rounded
to year / month /day Ex :- ROUND(SYSDATE,'YEAR') => 01-JAN-26 01-JAN-25------------------------
-----JUNE--------------------------------01-JAN-26 ROUND(SYSDATE,'MONTH') => 01-NOV-25 01-
OCT-25------------------------------15th---------------------------------------01-NOV-25
ROUND(SYSDATE,'DAY') => 26-OCT-25 19-OCT-25-----------------------------------THU------------------
--------------------26-OCT-25 ROUND(SYSDATE) => 24-OCT-25----------------------------------12PM-----
----------------------------25-OCT-25 TRUNC(SYSDATE,'YEAR') => 01-JAN-25
TRUNC(SYSDATE,'MONTH') => 01-OCT-25 TRUNC(SYSDATE,'DAY') => 19-OCT-25
TRUNC(SYSDATE) => 24-OCT-25 scenario :- INSERT INTO emp(empno,ename,job,sal,hiredate)
VALUES(110,'rahul','clerk',2000,sysdate) ; INSERT INTO emp(empno,ename,job,sal,hiredate)
VALUES(111,'kumar','clerk',2000,'24-OCT-25'); => list of employees joined today ? SELECT *
FROM emp WHERE hiredate = SYSDATE ; => NO ROWS 24-OCT-2025 02:49:31 = 24-OCT-25
2:57:20 24-OCT-2025 12:00:00 NOTE :- "=" comparison with SYSDATE always fails because
oracle not only compares dates but also compares time , To overcome this problem use TRUNC
function SELECT * FROM emp WHERE TRUNC(hiredate) = TRUNC(SYSDATE) ; 24-OCT-25
12:00:00 = 24-OCT-25 12:00:00 EXTRACT() :- ------------------------- => used to extract
year/month/day/hour/minute from date Ex :- EXTRACT(YEAR FROM SYSDATE) => 2025
EXTRACT(MONTH FROM SYSDATE) => 10 EXTRACT(DAY FROM SYSDATE) => 24 =>
employees joined in 1980 ? WHERE hiredate BETWEEN '01-JAN-1980' AND '31-DEC-1980'
WHERE hiredate LIKE '%80' SELECT * FROM emp WHERE EXTRACT(year FROM hiredate) =
1980 ; => employees joined in 1980,1983,1985 ? SELECT * FROM emp WHERE
EXTRACT(YEAR FROM hiredate) IN (1980,1983,1985) ; => employees joined in leap year ?
SELECT * FROM emp WHERE MOD(EXTRACT(year FROM hiredate),4) = 0 ; => employees
joined in jan,apr,dec months ? SELECT * FROM emp WHERE EXTRACT(month FROM hiredate)
IN (1,4,12) ; ADD_MONTHS :- ------------------------- => used to add / subtract months to/from a
date ADD_MONTHS(DATE , NUMBER) Ex :- ADD_MONTHS(SYSDATE,2) => 24-DEC-25
ADD_MONTHS(SYSDATE,-2) => 24-AUG-25 scenario :- --------------- GOLD_RATES DATEID
RATE 01-JAN-20 ? 02-JAN-20 ? 24-OCT-25 ? => display today's gold rate ? SELECT * FROM
GOLD_RATES WHERE TRUNC(DATEID) = TRUNC(SYSDATE) ; => display yeasterday's gold
rate ? SELECT * FROM GOLD_RATES WHERE TRUNC(DATEID) = TRUNC(SYSDATE-1) ; =>
display last month same day gold rate ? SELECT * FROM GOLD_RATES WHERE
TRUNC(DATEID) = TRUNC(ADD_MONTHS(SYSDATE,-1)) ; => display last year same day gold
rate ? SELECT * FROM GOLD_RATES WHERE TRUNC(DATEID) =
TRUNC(ADD_MONTHS(SYSDATE,-12)) ; => display last 1 month gold rates ? SELECT * FROM
GOLD_RATES WHERE TRUNC(DATEID) BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-1))
AND TRUNC(SYSDATE) WHERE TRUNC(DATEID) >= TRUNC(ADD_MONTHS(SYSDATE,-1)) -
----------------------------------------------------- 24-SEP-25 25-oct-25 MONTHS_BETWEEN() :- ------------
---------------------- => returns no of months between two dates MONTHS_BETWEEN(date1,date2)
Ex :- MONTHS_BETWEEN(sysdate,'25-OCT-24') => 12 Ex :- => display ENAME EXPERIENCE
in years ? SELECT ename , MONTHS_BETWEEN(sysdate , hiredate)/12 as expr FROM emp ;
=> display experience in months ? SELECT ename , MONTHS_BETWEEN(sysdate , hiredate) as
expr FROM emp ; smith 538.277537 allen 536.180762 SELECT ename ,
FLOOR(MONTHS_BETWEEN(sysdate , hiredate)) as expr FROM emp smith 538 allen 536 =>
display ENAME EXPERIENCE ? M years N months experience = 40 months = 3 years 4 months
years = months / 12 = FLOOR(40/12 ) = 3 months = MOD(months,12) = MOD(40,12) = 4
SELECT ename , hiredate, FLOOR(MONTHS_BETWEEN(sysdate,hiredate)/12) as years,
MOD(FLOOR(MONTHS_BETWEEN(sysdate,hiredate)),12) as months FROM emp ; Conversion
functions :- ----------------------------------- => these functions are used to convert one datatype to
another datatype => the following functions provided by oracle for conversion 1 TO_CHAR =>
converts date / number to char type 2 TO_DATE => converts string to date 3 TO_NUMBER =>
converts string to number converting date to char :- --------------------------------- => dates converted
to char type to display dates in different formats. TO_CHAR(date , 'format') Ex :- Formats :-
TO_CHAR(SYSDATE,'format') ----------------- yyyy 2025 yy 25 year twenty twenty-five mm 10 mon
oct month october ddd 298 (day of the year 1-365) dd 25 (day of the month 1-31) d 7 (day of the
week 1-7) dy sat day saturday hh hour hh24 24 hrs format mi minutes ss seconds AM/PM AM
time OR PM time Q quarter (1-4) jan - mar 1 apr -jun 2 jul-sep 3 oct-dec 4 Ex :- 1 SELECT
TO_CHAR(SYSDATE, 'yyyy yy year') FROM DUAL ; o/p :- 2025 25 twenty twenty-five 2 SELECT
TO_CHAR(SYSDATE, 'mm mon month') FROM DUAL ; o/p :- 10 oct october 3 SELECT
TO_CHAR(SYSDATE, 'ddd dd d dy day') FROM DUAL ; 298 25 7 sat saturday 4 SELECT
TO_CHAR(SYSDATE, 'DD/MM/YYYY HH:MI:SS AM') FROM DUAL ; 25/10/2025 03:14:18 PM 5
SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS AM') FROM DUAL ; 25/10/2025
15:15:05 PM => display ename day ? SELECT ename , hiredate,TO_CHAR(hiredate,'day') as day
FROM emp ; => display ENAME HIREDATE ? display hiredates in mm/dd/yyyy ? display day of
the week also ? SELECT ename, TO_CHAR(hiredate,'mm/dd/yyyy day') as hiredate FROM emp ;
=> display employees joined on sunday ? SELECT * FROM emp WHERE
TO_CHAR(hiredate,'dy') = 'sun' ; => employees joined in 2nd quarter of 1981 year ? SELECT *
FROM emp WHERE TO_CHAR(hiredate,'yyyy') = 1981 AND TO_CHAR(hiredate,'q') = 2 ;
converting number to char type :- --------------------------------------------- => numbers converted to
char type to display numbers in differnet formats TO_CHAR(number , 'format') formats :- ------------
9 represents a digit 0 represents a digit G thousand seperator D decimal seperator L currency
symbol C currency Ex :- TO_CHAR(1234, '99999') => 1234 TO_CHAR(1234,'999999') => 1234
TO_CHAR(1234,'00000') => 01234 TO_CHAR(1234,'000000') => 001234
TO_CHAR(1234,'9G999') => 1,234 TO_CHAR(500000,'9G99G999') => 5,00,000
TO_CHAR(5000,'9G999D99') => 5,000.00 TO_CHAR(5000,'L9G999') => $5,000
TO_CHAR(5000,'C9G999') => USD5,000 => display ENAME SAL ? display salaries with
thousand seperator and currency symbol ? SELECT ename , TO_CHAR(sal , 'C99G999D99') as
sal FROM emp ; => how to change currency ? SQL> ALTER SESSION SET NLS_TERRITORY =
'INDIA' ; display ENAME SAL ? display salaries with indian currency ? SELECT ename ,
TO_CHAR(sal , 'C99G999D99') as sal FROM emp ; NLS => National Language Specification 27-
oct-25 Converting Date string to Date :- ------------------------------------------- Date string => '27-OCT-
2025' '10/27/2025' '2025-10-27' TO_DATE(date string , 'format') Ex :- SQL> SELECT sysdate +
10 FROM dual ; => 06-NOV-25 SQL> SELECT '01-NOV-25' + 100 FROM dual ; => ERROR
number + number => valid date + number => valid date string + number => invalid SQL> SELECT
TO_DATE('01-NOV-25' ,'DD-MON-YY') + 100 FROM dual ; o/p :- 9-feb-26 => add 50 days to
'2026-01-01' SQL>SELECT TO_DATE('2026-01-01','YYYY-MM-DD') + 50 FROM DUAL ; o/p :-
20-FEB-26 => waq to display on which day india got independence ? SELECT TO_CHAR(
TO_DATE('15-AUG-1947' ,'DD-MON-YYYY'),'DAY') FROM DUAL ; o/p ;- FRIDAY Converting
numeric string to Number :- ------------------------------------------------------ numeric string => '1234'
'$1234' '$1,234' TO_NUMBER(numeric string , 'format') Ex :- SQL>SELECT 1000 + 5000 FROM
DUAL ; => 6000 SQL>SELECT 1000 + '5,000' FROM DUAL ; => ERROR SQL>SELECT 1000 +
TO_NUMBER('5,000' , '9G999') FROM DUAL ; => 6000 => calculate '$5,000' + 'USD4,000" ?
SQL>SELECT TO_NUMBER( '$5,000' , 'L9G999') + TO_NUMBER( 'USD4,000' , 'C9G999')
FROM DUAL ; o/p :- 9000 input output function 5000 $5,000 TO_CHAR $5,000 5000
TO_NUMBER dd-mon-yy mm/dd/yyyyy TO_CHAR mm/dd/yyyy dd-mon-yy TO_DATE default
other formats TO_CHAR other default TO_DATE 28-oct-25 Special Functions :- -----------------------
------ NVL() :- ---------- => used to convert null values NVL(arg1,arg2) if arg1 = null returns arg2 if
arg1 <> null returns arg1 only Ex :- NVL(100,200) => 100 NVL(NULL,200) => 200 => display
ENAME SAL COMM ? if comm = null display 0 ? SELECT ename,sal,NVL(comm,0) as comm
FROM emp ; => display ENAME SAL COMM ? if comm = null display N/A ? SELECT
ename,sal,NVL(comm,'N/A') as comm FROM emp ; => error SELECT
ename,sal,NVL(TO_CHAR(comm),'N/A') as comm FROM emp ; => display ENAME SAL COMM
TOTSAL ? TOTSAL = SAL + COMM SELECT ename,sal,comm , sal + comm as totsal FROM
emp ; jones 2975 null martin 1250 1400 2650 blake 2850 null clark 2450 null SELECT
ename,sal,comm , sal + NVL(comm,0) as totsal FROM emp ; jones 2975 2975 martin 1250 1400
2650 blake 2850 2850 clark 2450 2450 ASCII() :- ------------- => returns ascii value of a given char
ASCII(char) Ex :- ASCII('A') => 65 CHR() :- --------- => returns character for given ascii value
CHR(65) => A Ex :- CUST cid name hno street city state pin 10 A 100 ampt hyd tg 500036 output
:- name hno street city state - pin SELECT name||CHR(10)|| hno||CHR(10)|| street||CHR(10)||
city||CHR(10)|| state||'-'||pin FROM cust WHERE cid = 10 ; How to implement loop in SQL :- --------
------------------------------------- SELECT level FOR(level=1 ; level<=10;level++) FROM DUAL => {
CONNECT BY level <= 10 ; print level ; } level = 1 ; while(level<=10) { print level ; level = level + 1
} => level is system variable i.e. declared by oracle. => level is initialized with 1 => by default level
is incremented by 1 => waq to print numbers from 1 to 100 ? SELECT LEVEL FROM DUAL
CONNECT BY LEVEL <= 100 ; => waq to print even nos upto 20 ? SELECT LEVEL FROM DUAL
WHERE MOD(level,2) = 0 CONNECT BY LEVEL <= 20 ; => waq to print all ascii charscters ?
SELECT LEVEL , CHR(LEVEL) FROM DUAL CONNECT BY LEVEL <= 256 ; O/P :- ASCII
VALUE CHAR 65 A 66 B => waq to print 2025 calendar ? 01-jan-25 ? 02-jan-25 ? 31-dec-25 ?
SELECT TO_DATE( '31-DEC-2024' , 'DD-MON-YYYY')+ LEVEL AS DT, TO_CHAR(TO_DATE(
'31-DEC-2024' , 'DD-MON-YYYY')+ LEVEL,'DAY') AS DY FROM DUAL CONNECT BY LEVEL <=
365 ; => query to print triangle pattern ? SELECT LPAD(' ',10-LEVEL,' ')|| LPAD('*',LEVEL-1,'*')||
LPAD('*',LEVEL,'*') FROM DUAL CONNECT BY LEVEL <= 10 ; => SELECT
SUBSTR('WELCOME',LEVEL,1) AS F1, SUBSTR('WELCOME',1,LEVEL) AS F2 FROM DUAL
CONNECT BY LEVEL <= LENGTH('WELCOME') Analytical Functions :- -----------------------------
RANK DENSE_RANK ROW_NUMBER LAG LEAD RANK & DENSE_RANK :- -------------------------
----------- => both functions are used to find ranks => ranks are based on one or more columns for
sal,hiredate,total marks => for rank functions data must be sorted RANK() OVER (ORDER BY
colname ASC/DESC, -------) DENSE_RANK() OVER (ORDER BY colname ASC/DESC, ------) ex
:- => find ranks of the employees based on sal ? highest paid employee should get 1st rank ?
SELECT ename,sal, RANK() OVER (ORDER BY sal DESC) as rnk FROM emp ; SELECT
ename,sal, DENSE_RANK() OVER (ORDER BY sal DESC) as rnk FROM emp ; difference
between RANK & DENSE_RANK ? 1 rank function generates gaps but dense_rank will not
generate gaps 2 in rank function ranks may not be in sequence but in dense_rank ranks are
always in sequence SAL RANK DENSE_RANK 5000 1 1 4000 2 2 3000 3 3 3000 3 3 3000 3 3
2000 6 4 2000 6 4 1000 8 5 => find ranks of the employees based on sal , if salaries are same
then ranking should be based on hiredate ? SELECT ename,hiredate,sal, DENSE_RANK() OVER
(ORDER BY sal DESC,hiredate ASC) as rnk FROM emp ; king 17-NOV-81 5000 1 ford 03-DEC-
81 3000 2 scott 09-DEC-82 3000 3 jones 02-APR-81 2975 4 PARTITION BY clause :- ---------------
------------------- => partition by clause is used to divide the table based on one or more columns.
=> used to find ranks with in group , for ex to find ranks with in dept first divide the table dept wise
using partition by clause and apply rank / dense_rank functions on each partition Ex :- => find
ranks with in dept based on sal ? SELECT deptno,ename,sal, DENSE_RANK() OVER
(PARTITION BY deptno ORDER BY sal DESC) as rnk FROM emp 10 king 5000 1 clark 2450 2
miller 1300 3 20 scott 3000 1 ford 3000 1 jones 2975 2 adams 1100 3 smith 800 4
ROW_NUMBER() :- ------------------------ => returns record number => row_number is also based
on one or more column => for row_number also data must be sorted ROW_NUMBER() OVER
(ORDER BY colname ASC/DESC,---) Ex 1 :- row_number based on sal SELECT
empno,ename,sal, ROW_NUMBER() OVER (ORDER BY sal DESC) as rno FROM emp ; 7839
king 5000 1 7902 ford 3000 2 7788 scott 3000 3 7566 jones 2975 4 7698 blake 2850 5 7782 clark
2450 6 Ex 2 :- row_number based on empno SELECT ROW_NUMBER() OVER (ORDER BY
empno ASC) as rno, empno,ename,sal FROM emp ; 1 7369 smith 800 2 7499 allen 1600 3 7521
ward 1250 4 7566 jones 2975 5 7654 martin 1250 SAL RNK DRNK RNO 5000 1 1 1 4000 2 2 2
3000 3 3 3 3000 3 3 4 3000 3 3 5 2000 6 4 6 2000 6 4 7 1000 8 5 8 LAG & LEAD :- ------------------
--- LAG(colname,number) OVER (ORDER BY ---) => returns previous row value
LEAD(colname,number) OVER (ORDER BY --) => returns next row value Ex 1 :- SELECT
empno,ename,sal , LAG(sal,1) OVER (ORDER BY empno ASC) as prev_sal FROM emp ; 7369
smith 800 7499 allen 1600 800 7521 ward 1250 1600 7566 jones 2975 1250 7654 martin 1250
2975 7698 blake 2850 1250 Ex 2 :- CREATE TABLE population ( year NUMBER(4), population
NUMBER ) ; INSERT INTO population VALUES(2020,1328024498) (2021,1402617695),
(2022,1425423212), (2023,1438069596), (2024,1450935791) SELECT * FROM POPULATION :
SELECT * FROM POPULATION ; YEAR POPULATION ---------- ---------- 2020 1328024498 2021
1402617695 2022 1425423212 2023 1438069596 2024 1450935791 => display YEAR
POPULATION GROWTH ? SELECT year, population , population - LAG(population,1) OVER
(ORDER BY year ASC) as growth FROM population ; YEAR POPULATION GROWTH ---------- ---
------- ---------- 2020 1328024498 2021 1402617695 74593197 2022 1425423212 22805517 2023
1438069596 12646384 2024 1450935791 12866195 => display year population growth pct ? pct
= ((current year population - prev year population)/(prev year pop))*100 SELECT year,population,
population - LAG(population,1) OVER (ORDER BY year ASC) as growth , ((population -
LAG(population,1) OVER (ORDER BY year ASC)) / LAG(population,1) OVER (ORDER BY year
ASC) )*100 as pct FROM population ; 30-oct-25 Multi-row functions :- ---------------------------- =>
these functions process multiple rows and returns one value => these functions are also called
aggregate functions or group functions. MAX MIN SUM AVG COUNT MAX() :- ----------- => returns
maximum value MAX(arg) Ex :- SELECT MAX(sal) FROM emp ; => 5000 SELECT MAX(hiredate)
FROM emp ; => 12-jan-83 SELECT MAX(ename) FROM emp ; => ward MIN() :- ----------- =>
returns minimum value MIN(arg) SELECT MIN(sal) FROM emp ; => 800 SELECT MIN(ename)
FROM emp ; => adams SUM() :- ----------- => returns total SUM(arg) SELECT SUM(sal) FROM
emp ; => 29025 => round total sal to hundreds ? SELECT ROUND(SUM(sal) , -2) FROM emp ;
=> 29000 29000-------------29050----------------------29100 => after rounding display total sal with
thousand seperator and currency ? SELECT TO_CHAR (ROUND(SUM(sal) , -2) , 'L99G999')
FROM EMP ; O/P :- $29,000 C => currency =. USD L => currency symbol G => group =>
thousand seperator => calculate total sal including comm ? SELECT SUM(sal+comm) FROM
emp ; => 9100 SELECT SUM(sal+NVL(comm,0)) FROM emp ; => 31725 => calculate total sal
paid to managers ? SELECT SUM(sal) FROM emp WHERE job='MANAGER' ; How to use
aggregate functions as analytical function :- -----------------------------------------------------------------------
----- Ex :- SUM() OVER (ORDER BY ----) SELECT empno,ename,sal, SUM(sal) OVER (ORDER
BY empno ASC) as running_total FROM emp ; 7369 smith 800 800 7499 allen 1600 2400 7521
ward 1250 3650 7566 jones 2975 6625 7654 martin 1250 7875 what is difference between
analytical functions and aggregate functions ? => analytical functions returns one value for each
row => aggregate functions returns one value from group of rows AVG() :- ----------- => returns
average value AVG(arg) Ex :- SELECT AVG(sal) FROM emp ; => 2073.21429 => round avg sal to
lowest integer ? SELECT FLOOR(AVG(sal)) FROM emp ; => 2073 => round avg sal to highest
integer ? SELECT CEIL(AVG(sal)) FROM emp ; => 2074 NOTE :- => sum,avg cannot be applied
on char,date fields can be applied only on numeric fields SELECT EMPNO,ENAME,SAL,
AVG(SAL) OVER (ORDER BY EMPNO ASC) as moving_avg FROM EMP ; 7369 smith 800 800
7499 allen 1600 1200 7521 ward 1250 1216.66667 7566 jones 2975 1656.25 COUNT() :- ----------
--- => returns no of values present in a column COUNT(arg) Ex :- SELECT COUNT(empno)
FROM emp ; => 14 SELECT COUNT(comm) FROM emp ; => 5 => nulls are not counted
COUNT(*) :- ----------------- => returns no of rows in a table SELECT COUNT(*) FROM emp ; =>
14 T1 F1 10 NULL 20 NULL 30 COUNT(F1) => 3 COUNT(*) => 5 => no of employees joined in
1981 year ? SELECT COUNT(*) FROM emp WHERE EXTRACT(year from hiredate) = 1981 ; =>
no of employees joined on sunday ? SELECT COUNT(*) FROM emp WHERE
TO_CHAR(hiredate,'dy') = 'sun' ; day => sunday dy => sun d => 1 => no of employees joined in
2nd quarter of 1981 year ? SELECT COUNT(*) FROM emp WHERE TO_CHAR(hiredate,'yyyy') =
1981 AND TO_CHAR(hiredate,'q') = 2 ; NOTE :- => aggregate functions not allowed in where
clause SELECT ename FROM emp WHERE sal = MAX(sal) ; => ERROR => to overcome use
subquery CHAR :- upper,lower,initcap,length,substr,instr,lpad,rpad,ltrim,rtrim,trim,replace,translate
NUMERIC :- round,ceil , floor,trunc,mod DATE :- extract , add_months,months_between CONV :-
to_char ,to_date,to_number SPECIAL :- nvl , chr,ascii ANALYTICAL :- rank
,dense_rank,row_number,lag,lead AGGREGATE :- max,min,sum,avg,count,count(*)
===========================================================================
31-oct-25 GROUP BY clause :- ================= => GROUP BY clause is used to group
rows based on one more column to calculate min,max,sum,avg,count for each group , for ex to
calculate dept wise total sal first group the rows based on dept and apply sum function on each
group emp empno ename sal deptno 1 A 3000 10 2 B 4000 20 GROUP BY 10 9000 3 C 5000 30
=============> 20 7000 4 D 6000 10 30 5000 5 E 3000 20 detailed data summarized data =>
group by clause converts detailed data into summarized data which is useful for analysis Syntax :-
------------ SELECT columns FROM tabname [WHERE cond] GROUP BY col1,col2,--- [HAVING
cond] [ORDER BY col ASC/DESC,---] Execution :- --------------- FROM WHERE GROUP BY
HAVING SELECT ORDER BY Ex :- => display dept wise total salary ? SELECT
DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO ; FROM EMP :- -------------------- emp
empno ename sal deptno 1 A 3000 10 2 B 4000 20 3 C 5000 30 4 D 6000 10 5 E 3000 20
GROUP BY DEPTNO :- ------------------------------- 10 1 A 3000 4 D 6000 20 2 B 4000 5 E 3000 30 3
C 5000 SELECT DEPTNO,SUM(SAL) :- ---------------------------------------------- 10 9000 20 7000 30
5000 => display job wise summary ( min sal,max sal,total sal,avg sal,no of emps) ? SELECT
job,MIN(sal) as minsal, MAX(sal) as maxsal, SUM(sal) as totsal, AVG(sal) as avgsal, COUNT(*)
as cnt FROM emp GROUP BY job ; => display year wise no of employees joined ? SELECT
TO_CHAR(hiredate,'yyyy') as year, COUNT(*) as no_of_emps FROM emp GROUP BY
TO_CHAR(hiredate,'yyyy') ; YEAR NO_OF_EMPS ---------- ---------- 1981 10 1983 1 1980 1 1982
2 GROUP BY year => invalid ORDER BY year ASC => valid NOTE :- ------------ => alias cannot be
used in group by clause because group by clause is executed before select => alias can be used
in order by clause because order by is executed after select. => display month wise no of
employees joined in the year 1981 ? SELECT TO_CHAR(hiredate,'month') as month,COUNT(*)
FROM emp WHERE TO_CHAR(hiredate,'yyyy') = 1981 GROUP BY TO_CHAR(hiredate,'month') ;
=> find the departments having more than 3 employees ? SELECT deptno,COUNT(*) FROM emp
WHERE COUNT(*) > 3 GROUP BY deptno ; => ERROR => oracle cannot calculate dept wise
count before group by and it can calculate only after group by , so apply the condition COUNT(*)
> 3 after group by using HAVING clause. SELECT deptno,COUNT(*) FROM emp GROUP BY
deptno HAVING COUNT(*) > 3 ; 20 5 30 6 WHERE VS HAVING :- -------------------------------
WHERE HAVING 1 selects specific rows selects specific groups 2 conditions applied before
group by conditions applied after group by 3 use where clause if cond use having clause doesn't
contain aggregate if cond contains function aggregate function => find southern states having
more than 5cr population ? persons aadharno name gender dob addr city state region SELECT
state,COUNT(*) FROM persons WHERE region = 'south' GROUP BY state HAVING COUNT(*) >
50000000 ; => display job wise no of employees where job = clerk,manager no of emps > 3 ?
SELECT job,COUNT(*) FROM emp WHERE job IN ('CLERK','MANAGER') GROUP BY job
HAVING COUNT(*) > 3 ; 1-nov-25 Grouping based on multiple columns :- -------------------------------
-------------------------- => display dept wise and with in dept job wise total sal ? SELECT
deptno,job,SUM(sal) as totsal FROM emp GROUP BY deptno,job ORDER BY deptno ASC 10
clerk 1300 10 manager 2450 10 president 5000 20 analyst 6000 20 clerk 1900 20 manager 2975
30 clerk 950 30 manager 2850 30 salesman 5600 => display year wise and with in year quarter
wise no of employees joined ? 1980 1 ? 2 ? 3 ? 4 ? 1981 1 ? 2 ? 3 ? 4 ? SELECT
TO_CHAR(hiredate,'yyyy') as year, TO_CHAR(hiredate,'q') as qrt, COUNT(*) as no_of_emps
FROM emp GROUP BY TO_CHAR(hiredate,'yyyy') , TO_CHAR(hiredate,'q') ORDER BY year
ASC ,qrt ASC ; YEAR Q NO_OF_EMPS 1980 4 1 1981 1 2 2 3 3 2 4 3 1982 1 1 4 1 1983 1 1
ROLLUP & CUBE :- ---------------------------- => both functions are used to calculate subtotals and
grand total GROUP BY ROLLUP(col1,col2,--) GROUP BY CUBE(col1,col2,------) ROLLUP :- -------
--------- => rollup displays subtotals for each group and also displays grand total SELECT
deptno,job,SUM(sal) as totsal FROM emp GROUP BY ROLLUP(deptno,job) ORDER BY deptno
ASC ; 10 CLERK 1300 MANAGER 2450 PRESIDENT 5000 8750 20 ANALYST 6000 CLERK
1900 MANAGER 2975 10875 30 CLERK 950 MANAGER 2850 SALESMAN 5600 9400 29025
CUBE :- ----------- => cube displays subtotals for each group by column (deptno,job) and also
displays grand total. SELECT deptno,job,SUM(sal) as totsal FROM emp GROUP BY
CUBE(deptno,job) ORDER BY deptno ASC ; 10 CLERK 1300 MANAGER 2450 PRESIDENT
5000 8750 20 ANALYST 6000 CLERK 1900 MANAGER 2975 10875 30 CLERK 950 MANAGER
2850 SALESMAN 5600 9400 ANALYST 6000 CLERK 4150 MANAGER 8275 PRESIDENT 5000
SALESMAN 5600 29025 => display state wise and with in state gender wise population and also
display state wise and gender wise subtotals ? persons aadharno name gender dob addr city
state region SELECT state,gender,COUNT(*) as population FROM persons GROUP BY
CUBE(state ,gender) ORDER BY state ASC AP male ? female ? ?? AR male ? female ? ?? AS
male ? female ? ?? male ? female ? ?? 3-nov-25 GROUPING SETS :- --------------------------- => In
Oracle SQL, GROUPING SETS is an extension to the GROUP BY clause that allows you to
specify multiple groupings of data within a single query. Unlike ROLLUP or CUBE, which generate
a predefined set of aggregations, GROUPING SETS allows user to specify aggregations . Ex 1 :-
SELECT deptno,job,SUM(sal) as totsal FROM emp GROUP BY GROUPING SETS ((deptno,job))
ORDER BY deptno ASC ,job ASC ; 10 clerk 1300 10 manager 2450 10 president 5000 20 analyst
6000 20 clerk 1900 20 manager 2975 30 clerk 950 30 manager 2850 30 salesman 5600 Ex 2 :-
SELECT deptno,job,SUM(sal) as totsal FROM emp GROUP BY GROUPING SETS ((deptno,job)
, (deptno)) ORDER BY deptno ASC ,job ASC ; 10 clerk 1300 10 manager 2450 10 president 5000
8750 20 analyst 6000 20 clerk 1900 20 manager 2975 10875 30 clerk 950 30 manager 2850 30
salesman 5600 9400 Ex 3 :- SELECT deptno,job,SUM(sal) as totsal FROM emp GROUP BY
GROUPING SETS ((deptno,job) , (deptno) , (job) , ()) ORDER BY deptno ASC ,job ASC ; 10 clerk
1300 10 manager 2450 10 president 5000 8750 20 analyst 6000 20 clerk 1900 20 manager 2975
10875 30 clerk 950 30 manager 2850 30 salesman 5600 9400 analyst 6000 clerk 4150 29025
GROUPING_ID() :- -------------------------- => this function accepts group by columns and returns
subtotal belongs to which group by column Ex :- GROUPING_ID (deptno,job) 1 => if subtotal
belongs to 1st column i.e. deptno 2 => if subtotal belongs to 2nd column i.e. job 3 => grand total
SELECT deptno,job,SUM(sal) as totsal, CASE grouping_id(deptno,job) WHEN 1 THEN 'Dept
subtotal' WHEN 2 THEN 'Job subtotal' WHEN 3 THEN 'Grand Total' END as subtotal FROM emp
GROUP BY GROUPING SETS ((deptno,job) , (deptno) , (job) , ()) ORDER BY deptno ASC ,job
ASC ; summary :- ----------------- importance of group by writing queries using group by where vs
having rollup & cube grouping sets grouping_id
=============================================================== Integrity
Constraints ================= => Integrity Constraints are rules to maintain data integrity i.e.
data quality or data consistency => used to prevent users from entering invalid data. => used to
enforce rules like min bal must be 1000 Types of constraints :- ----------------------------- 1 NOT
NULL 2 UNIQUE 3 PRIMARY KEY 4 CHECK 5 FOREIGN KEY 6 DEFAULT => above constraints
can be declared in two ways 1 column level 2 table level column level :- -------------------- => if
constraints are declared immediately after declaring column then it is called column level.
CREATE TABLE ( colname datatype(size) constraint , colname datatype(size) constraint, -----------
---- ); NOT NULL :- ------------------ => NOT NULL constraint doesn't accept null values => a field
declared with NOT NULL is called mandatory field Ex :- CREATE TABLE emp11 ( empno
NUMBER(4), ename VARCHAR2(10) NOT NULL ); INSERT INTO emp11 VALUES(100,'') ; =>
ERROR INSERT INTO emp11 VALUES(101,'A') ; UNIQUE :- ------------- => unique constraint
doesn't accept duplicates Ex :- CREATE TABLE cust123 ( cid NUMBER(2), cname
VARCHAR2(10) NOT NULL, emailid VARCHAR2(20) UNIQUE ); INSERT INTO cust123
VALUES(10,'A','abc@[Link]') ; INSERT INTO cust123 VALUES(11,'B','abc@[Link]') ; =>
ERROR INSERT INTO cust123 VALUES(12,'C',''); INSERT INTO cust123 VALUES(13,'D',''); =>
allowed NOTE :- => UNIQUE constraint doesn't accept duplicates but allows nulls. PRIMARY
KEY :- ---------------------- => primary key doesn't accept duplicates and nulls. => primary key is
combination of unique and not null. primary key = unique + not null => In tables one column must
be there to uniquely identify and into that column duplicates and nulls are not allowed , so declare
that column with primary key. Ex :- CREATE TABLE emp15 ( empno NUMBER(4) PRIMARY KEY,
ename VARCHAR2(10) NOT NULL ); INSERT INTO emp15 VALUES(10,'A') ; INSERT INTO
emp15 VALUES(10,'B') ; => ERROR INSERT INTO emp15 VALUES(null,'C'); => ERROR
because it is not allowing duplicates and nulls into empno , so using empno we can uniquely
identify employees. NOTE :- ----------- => only one primary key is allowed per table , if we want
multiple primary keys then declare one column with primary key and other columns with unique
not null. Ex :- create table customers ( custid NUMBER(6) PRIMARY KEY, cname
VARCHAR2(10) NOT NULL, emailid VARCHAR2(20) UNIQUE, aadharno NUMBER(12) UNIQUE
NOT NULL, panno CHAR(10) UNIQUE NOT NULL ); difference between UNIQUE & PRIMARY
KEY ? UNIQUE PRIMARY KEY 1 allows nulls doesn't allow nulls 2 a table can have a table can
have multiple unique only one primary key constraints candidate key :- ----------------------- => a
field eligible for primary key is called candidate key Ex :- VEHICLES vehno name model cost
chassisno candidate keys :- vehno , chassisno primary key :- vehno secondary key :- chassisno
or alternate key => while creating table secondary keys are declared with UNIQUE NOT NULL. 4-
nov-25 CHECK :- --------------- => use check constraint when rule based on condition
CHECK(condition) Ex 1 :- sal must be min 3000 create table emp16 ( empno NUMBER(4)
PRIMARY KEY, ename VARCHAR2(10) NOT NULL, sal NUMBER(7) CHECK(sal >= 3000) NOT
NULL ); INSERT INTO emp16 VALUES(100,'A',1000); => ERROR INSERT INTO emp16
VALUES(101,'B',5000); INSERT INTO emp16 VALUES(102,'C',NULL); NOTE :- check constraint
allows null values Ex 2 :- gender must be 'm','f' GENDER CHAR(1) CHECK(gender='m' or
gender='f') Ex 3 :- amt must be multiple of 100 AMT NUMBER(6) CHECK(MOD(amt,100)=0) Ex 4
:- pwd must be min 6 chars PWD VARCHAR2(12) CHECK(LENGTH(pwd) >= 6) Ex 5 :- emailid
must contain '@' must end with '.com' or '.co' or '.in' EMAILID VARCHAR2(20) CHECK(emailid
LIKE '%@%' AND ( emailid LIKE '%.com' OR emailid LIKE '%.co' OR emailid LIKE '%.in' ))
FOREIGN KEY :- ------------------------ => foreign key is used to establish relationship between two
tables => To establish relationship between two tables then take primary key of one table and add
it to another table as foreign key and declare with references constraint. ex :- projects projid name
duration cost client 1000 AAA 5 YEARS 400 TATA MOTORS 1001 BBB 3 YEARS 200 DBS BANK
emp empid ename job sal projid REFERENCES projects(projid) 100 K SE 30K 1000 101 D SSE
80K 1001 102 S TL 100K 9999 => not accepted 103 J SE 25K 1000 104 G SE 30K NULL =>
values entered in fk column should match with values entered in pk column => fk allows
duplicates and nulls. => after declaring fk a relationship is created between two tables called
parent / child relationship => pk table is parent and fk table is child CREATE TABLE projects (
projid number(4) primary key, pname varchar2(10) ); INSERT INTO projects
VALUES(1000,'AAA'); INSERT INTO projects VALUES(1001,'BBB'); CREATE TABLE emp_proj (
empno NUMBER(3) PRIMARY KEY, ename VARCHAR2(10) NOT NULL, sal NUMBER(7)
CHECK(sal>=3000), projid NUMBER(4) REFERENCES projects(projid) ); INSERT INTO
emp_proj VALUES(100,'A',5000,1000); INSERT INTO emp_proj VALUES(101,'B',4000,9999); =>
ERROR INSERT INTO emp_proj VALUES(102,'C',3000,1000); INSERT INTO emp_proj
VALUES(103,'D',3000,NULL); Relationship Types :- ----------------------------- 1 one to one (1:1) 2
one to many (1:m) 3 many to one (m:1) 4 many to many (m:n) => by default oracle creates one to
many relationship between two tables How to establish 1:1 relationship :- --------------------------------
-------------- => To establish 1:1 relationship between two tables declare foreign key with unique
constraint. DEPT MGR DNO DNAME MGRNO MNAME DNO REFERENCES DEPT(DNO)
UNIQUE 10 HR 100 10 20 IT 101 20 30 SALES 102 30 How to establish m:n relationship :- --------
-------------------------------------- => To establish m:n relationship between two tables then create 3rd
table and add primary keys of both tables as foreign keys. Ex :- COURSE STUDENT cid cname
sid sname 10 JAVA 1 A 11 ORACLE 2 B REGISTRATIONS sid cid dor fee 1 10 -- 5000 1 11 ---
3000 2 10 -- 5000 ER Model Relational Model 1 model used in db design model used in db
development 2 Entities Tables 3 Attributes Fields 4 Relationship Foreign key Relaitonal Model for
the above ER Model :- -------------------------------------------------------- BANK CODE NAME ADDR -----
---- BRANCH BRANCH_ID NAME ADDR CODE(FK) ----------------- ACCOUNT ACCNO ACTYPE
BAL BRANCH_ID (FK) CUSTID (FK) ----------- LOAN LOAN_ID LOAN_TYPE AMOUNT
BRANCH_ID (FK) CUSTID(FK) ------------- CUSTOMER CUSTID NAME ADDR PHONE ------------
Rules for adding fk :- ---------------------------- 1 if relationship is 1:1 then fk can be added to any
table 2 if relationship is 1:m then fk must be added to many side table 3 if relationship is m:n then
create 3rd table add pks of both tables as fks 5-nov-25 DEFAULT :- --------------- => a column can
be declared with default value as follows ex :- hiredate date default sysdate => while inserting if
we skip hiredate then oracle inserts default value CREATE TABLE emp20 ( empno NUMBER(4)
PRIMARY KEY, ename VARCHAR2(10) NOT NULL, hiredate DATE DEFAULT SYSDATE );
INSERT INTO emp20(empno,ename) VALUES(100,'A'); INSERT INTO emp20
VALUES(101,'B','10-FEB-25'); INSERT INTO emp20 VALUES(102,'C',''); SELECT * FROM
emp20; 100 A 05-NOV-25 101 B 10-FEB-25 102 C Assignment :- ------------------- ACCOUNTS
accno actype bal rules :- ------- 1 accno should not be duplicate & null 2 actype must be 'S' OR 'C'
3 bal must be min 1000 TRANSACTIONS trid ttype tdate tamt accno rules :- --------- 1 trid should
not be duplicate & null 2 ttype must be 'w' or 'd' 3 tdate must be sysdate 4 tamt must be multiple
of 100 5 accno should match with accounts table accno 6 accno should not be null TABLE LEVEL
:- ------------------------ => if constraints are declared after declaring all columns then it is called table
level => use table level to declare constraints for multiple or combination of columns Declaring
check constraint at table level :- ----------------------------------------------------- products prodid pname
price mfd_dt exp_dt 100 A 50 5-NOV-25 1-NOV-25 => INVALID Rule :- exp_dt > mfd_dt CREATE
TABLE products ( prodid NUMBER(4) PRIMARY KEY, pname VARCHAR2(10) NOT NULL, price
NUMBER(5), mfd_dt DATE , exp_dt DATE , CHECK(exp_dt > mfd_dt) ) INSERT INTO products
VALUES(100,'A',50,SYSDATE,'01-NOV-25'); => ERROR INSERT INTO products
VALUES(100,'A',50,'01-NOV-25',SYSDATE); Composite primary key :- -----------------------------------
=> if primary key declared for combination of columns then it is called composite primary key =>
In some table we may not be able to uniquely identify using single column and we need
combination of columns to uniquely identify and that combination should be declared primary key
at table level. Ex 1 :- student course sid sname cid cname ---- ---- 1 A 10 java 2 B 11 oracle
registrations sid cid dor fee ------------------ 1 10 ? ? 1 11 ? ? 2 10 ? ? create table student ( sid
number(2) primary key, sname varchar2(10) not null ) ; insert into student values(1,'A'); insert into
student values(2,'B'); create table course ( cid number(2) primary key, cname varchar2(10) not
null ); insert into course values(10,'java'); insert into course values(11,'oracle'); create table
registrations ( sid number(2) references student(sid) , cid number(2) references course(cid) , dor
date , fee number(5) , PRIMARY KEY(sid,cid) ); insert into registrations
values(1,10,sysdate,5000); insert into registrations values(1,11,sysdate,5000); insert into
registrations values(2,10,sysdate,5000); insert into registrations values(1,10,sysdate,5000); =>
ERROR Ex 2 :- sales dateid prodid custid qty amt 4-nov 100 10 1 2000 4-nov 101 10 1 1000 4-
nov 100 11 1 2000 5-nov 100 10 1 2000 => identity primary key and write create table script ?
Composite foreign key :- --------------------------------- => if combination of columns declared foreign
key then it is called composite foreign key. => a composite foreign key refers composite primary
key Ex :- registrations sid cid dor fee ------------------ 1 10 ? ? 1 11 ? ? 2 10 ? ? certificates certno
doi sid cid 1000 4- 1 10 1001 4- 1 11 1002 4- 2 11 => in the above ex sid,cid combination should
match with registrations table sid,cid combination , so declare this combination as foreign key at
table level Questions :- 1 Which of the following constraint cannot be declared at table level ? A
UNIQUE B CHECK C NOT NULL D PRIMARY KEY E FOREIGN KEY ANS :- C 2 Which
statements are true regarding constraints ? A a foreign key cannot contain NULL value F B a
column with UNIQUE constraint can contain NULL value T C a constraint is enforced only for the
INSERT operation on a table F D all constraints can be defined at column level and table level. F
3 Which CREATE TABLE statement is valid? A. CREATE TABLE ord_details (ord_no NUMBER(2)
PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date DATE NOT NULL); B. CREATE
TABLE ord_details (ord_no NUMBER(2) UNIQUE, NOT NULL, item_no NUMBER(3), ord_date
DATE DEFAULT SYSDATE NOT NULL); C. CREATE TABLE ord_details (ord_no NUMBER(2) ,
item_no NUMBER(3), ord_date DATE DEFAULT NOT NULL, UNIQUE (ord_no), PRIMARY KEY
(ord_no)); D. CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3), ord_date
DATE DEFAULT SYSDATE NOT NULL, PRIMARY KEY (ord_no, item_no)); 4 Evaluate the
following CREATE TABLE commands CREATE TABLE orders (ord_no NUMBER(2) PRIMARY
KEY, ord_date DATE, cust_id NUMBER(4)); CREATE TABLE ord_items (ord_no NUMBER(2),
item_no NUMBER(3), qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200), expiry_date date
CHECK (expiry_date > SYSDATE), PRIMARY KEY (ord_no,item_no), FOREIGN KEY(ord_no)
REFERENCES orders(ord_no)); The above command fails when executed. What could be the
reason? A. SYSDATE cannot be used with the CHECK constraint. T B. The BETWEEN clause
cannot be used for the CHECK constraint. F C. The CHECK constraint cannot be placed on
columns having the DATE data type. F D. ORD_NO and ITEM_NO cannot be used as a
composite primary key because ORD_NO is also the FOREIGN KEY. F 6-nov-25 Adding
constraints to existing table :- -------------------------------------------------- => "ALTER" command is
used to add constraints to existing table Ex :- CREATE TABLE emp55 ( empno NUMBER(4),
ename VARCHAR2(10) , sal NUMBER(7,2) , emailid VARCHAR2(20), deptno NUMBER(2) );
Adding primary key :- ----------------------------- => add pk to empno ? ALTER TABLE emp55 ADD
PRIMARY KEY(empno) ; Adding check constraint :- ------------------------------------- => add check
constraint with condition sal >= 3000 ? ALTER TABLE emp55 ADD CHECK(sal >= 3000); ALTER
TABLE emp ADD CHECK(sal >= 3000); => ERROR => some of the employee salaries are less
than 3000 while adding constraint oracle also validates existing data , if existing data doesn't
satisfy the condition then constraint cannot be added. NOVALIDATE :- --------------------- => if
check constraint is added with NOVALIDATE then oracle will not validate existing data and it
validates only new data. ALTER TABLE emp ADD CHECK(sal >= 3000) NOVALIDATE ; Adding
unique constraint :- -------------------------------------- => add unique constraint to emailid ? ALTER
TABLE emp55 ADD UNIQUE (emailid) ; Adding foreign key :- ------------------------------ => add fk to
deptno that should match with dept table primary key i.e. deptno ? ALTER TABLE emp55 ADD
FOREIGN KEY(deptno) REFERENCES dept(deptno) ; Changing from NULL to NOT NULL :- ------
----------------------------------------------- => Modify the column ename to NOT NULL ? ALTER TABLE
emp55 MODIFY (ename NOT NULL) ; Data Dictionary Tables :- ----------------------------------- =>
oracle not only stores data and it also stores metadata that includes information about
users,tables,constraints etc. ALL_USERS :- ---------------------- => stores information about users
created in db. SQL>DESC ALL_USERS USERNAME CREATED => display list of users ?
SELECT USERNAME FROM ALL_USERS ; => list of users name starts with 'B' ? SELECT
USERNAME FROM ALL_USERS WHERE USERNAME LIKE 'B%' ; => find on which day (date)
batch53 user is created ? SELECT CREATED FROM ALL_USERS WHERE
USERNAME='BATCH53' ; USER_TABLES :- ------------------------- => stores tables created by user
list of tables created by user ? SELECT TABLE_NAME FROM USER_TABLES ; how many tables
created by user ? SELECT COUNT(*) FROM USER_TABLES ; USER_CONSTRAINTS :- ----------
-------------------------- => stores constraints declared in a table list of constraints declared in emp55
table ? SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE , SEARCH_CONDITION FROM
USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP55' ; CONSTRAINT_NAME C
SEARCH_CONDITION -------------------- --- ----------------------------- SYS_C0010396 P
SYS_C0010397 C sal >= 3000 SYS_C0010399 U SYS_C0010401 R SYS_C0010402 C
"ENAME" IS NOT NULL Droping constraints :- ---------------------------- ALTER TABLE DROP
CONSTRAINT ; Ex :- => drop check constraint in emp55 table ? ALTER TABLE emp55 DROP
CONSTRAINT SYS_C0010397 ; => drop primary key in emp55 table ? ALTER TABLE emp55
DROP CONSTRAINT SYS_C0010396 ; ALTER TABLE emp55 DROP PRIMARY KEY ; => drop
primary key in dept table ? ALTER TABLE dept DROP PRIMARY KEY ; => ERROR DROP TABLE
dept; => ERROR => pk constraint cannot be dropped if referenced by some fk => pk table cannot
be dropped if referenced by some fk CASCADE :- ------------------- ALTER TABLE dept DROP
PRIMARY KEY CASCADE ; => drops pk along with fk DROP TABLE dept CASCADE
CONSTRAINTS ; => drops table with dependent fk 7-nov-25 DELETE rules :- -------------------- 1
ON DELETE NO ACTION (DEFAULT) 2 ON DELETE CASCADE 3 ON DELETE SET NULL =>
These rules are declared with foreign key => delete rule specifies how child rows are affected if
parent row is deleted ON DELETE NO ACTION :- ----------------------------------------- => parent row
cannot be deleted if it has child rows CREATE TABLE dept77 ( dno NUMBER(2) PRIMARY KEY,
dname VARCHAR2(10) NOT NULL ); INSERT INTO dept77 VALUES(10,'HR'); INSERT INTO
dept77 VALUES(20,'IT'); CREATE TABLE emp77 ( empno NUMBER(4) PRIMARY KEY, ename
VARCHAR2(10) NOT NULL, dno NUMBER(2) REFERENCES dept77(dno) ); INSERT INTO
emp77 VALUES(1,'A',10); INSERT INTO emp77 VALUES(2,'B',10); DELETE FROM dept77
WHERE dno = 10; => ERROR DELETE FROM dept77 WHERE dno = 20 ; => DELETED
scenario :- --------------- ACCOUNTS accno actype bal 100 101 LOANS id type amt accno
REFERENCES accounts(accno) 1 H 30 100 2 C 10 100 Rule :- accout closing is not possible if
associated with loans ON DELETE CASCADE :- ------------------------------------- => parent row is
deleted along with child rows CREATE TABLE dept77 ( dno NUMBER(2) PRIMARY KEY, dname
VARCHAR2(10) NOT NULL ); INSERT INTO dept77 VALUES(10,'HR'); INSERT INTO dept77
VALUES(20,'IT'); CREATE TABLE emp77 ( empno NUMBER(4) PRIMARY KEY, ename
VARCHAR2(10) NOT NULL, dno NUMBER(2) REFERENCES dept77(dno) ON DELETE
CASCADE ); INSERT INTO emp77 VALUES(1,'A',10); INSERT INTO emp77 VALUES(2,'B',10);
DELETE FROM dept77 WHERE dno = 10; => 1 row deleted SELECT * FROM emp77 ; => no
rows scenario :- ------------- ACCOUNTS accno actype bal 100 101 TRANSACTIONS trid ttype
tdate tamt accno REFERENCES accounts(accno) 1 W ? 2000 100 ON DELETE CASCADE 2 D ?
4000 100 Rule :- if account is closed along with account delete transactions also ON DELETE
SET NULL :- ----------------------------------- => parent row is deleted without deleting child rows but fk
will be set to null CREATE TABLE dept77 ( dno NUMBER(2) PRIMARY KEY, dname
VARCHAR2(10) NOT NULL ); INSERT INTO dept77 VALUES(10,'HR'); INSERT INTO dept77
VALUES(20,'IT'); CREATE TABLE emp77 ( empno NUMBER(4) PRIMARY KEY, ename
VARCHAR2(10) NOT NULL, dno NUMBER(2) REFERENCES dept77(dno) ON DELETE SET
NULL ); INSERT INTO emp77 VALUES(1,'A',10); INSERT INTO emp77 VALUES(2,'B',10);
DELETE FROM DEPT77 WHERE DNO=10; => 1 ROW DELETED SELECT * FROM EMP77 ;
EMPNO ENAME DNO ---------- ---------- - -------- 1 A 2 B scenario :- --------------- PROJECT projid
pname duration cost client 1000 1001 EMP empno ename sal job projid REFERENCES
projects(projid) 1 1000 ON DELETE SET NULL 2 1001 rule :- => if project is completed (deleted)
then set employee project id to null summary :- purpose of constraints types of constraints
declaring constraints column level table level adding constraints to existing table droping
constraints delete rules getting constraints information
=========================================================================
JOINS ====== => join is an operation performed to display data from two or more table => to
display data from two tables we need to join those table => In DB , related data is not stored in
one table and it is stored in multiple tables , To gather or to combine data stored in multiple tables
we need to join those table. Ex :- ORDERS CUST ordid orddt deldt cid cid name addr 1000 10 10
A HYD 1001 11 11 B HYD 1002 12 12 C HYD OUTPUT :- ordid orddt deldt cname addr 1000 ? ?
A HYD Types of join :- --------------------- 1 Inner join equi join non equi join self join 2 Outer join left
outer join right outer join full outer join 3 cross / cartesian join Equi join :- ----------------- => Equi
join is performed between the tables sharing common field => Name of the common field need
not to be same => between the tables pk-fk relationship is not compulsory => Equi join is
performed based on the common field with same datatype. SELECT columns FROM tab1 INNER
JOIN tab2 ON join condition ; join condition :- ------------------------- => join condition determines
which record of 1st table joined with which record of 2nd table => based on the join condition
oracle joins the records of two tables [Link] = [Link] Ex :- EMP
DEPT empno ename sal deptno deptno dname loc 7369 smith 800 20 10 ACCOUNTS NEW
YORK 7499 allen 1600 30 20 RESEARCH 7521 ward 1250 30 30 SALES 7566 jones 2975 20 40
OPERATIONS 7782 clark 2450 10 9999 vijay 2000 null => display employee details with dept
details ? SELECT empno,ename,sal,dname,loc FROM emp INNER JOIN dept ON [Link] =
[Link] ; 7369 smith 700 research ? 7499 allen 1600 sales ? 7521 ward 1250 sales ? 7566
jones 2975 research ? 7782 clark 2450 accounts ? NOTE :- => In join queries declare table alias
and prefix column names with table alias for two reasons 1 to avoid ambiguity error 2 for faster
execution SELECT [Link],[Link],[Link], [Link],[Link],[Link] as city FROM emp e INNER
JOIN dept d ON [Link] = [Link] ; => display employee details with dept details working at
NEW YORK loc and earning more than 2000 ? SELECT [Link],[Link],[Link],
[Link],[Link],[Link] as city FROM emp e INNER JOIN dept d ON [Link] = [Link] /* join
condition */ WHERE [Link] = 'NEW YORK' AND [Link] > 2000 /* filter condition */ ; => display order
details with cust details to be delivered today ? ORDERS CUST ordid orddt deldt cid cid name
addr 1000 10 10 A HYD 1001 11 11 B HYD 1002 12 12 C HYD SELECT o.*, c.* FROM orders o
INNER JOIN cust c ON [Link] = [Link] WHERE TRUNC([Link]) = TRUNC(sysdate) ; 8-nov-25
joining more than two tables :- ----------------------------------------- => if no of tables increases no of
join conditions also increases => to join N tables N-1 join conditions required SELECT columns
FROM tab1 INNER JOIN tab2 ON join condition INNER JOIN tab3 ON join condition INNER JOIN
tab4 ON join condition ; ( tab1 join tab2) join tab3 ) join tab4 Ex :- employees departments
locations countries employee_id department_id location_id country_id first_name
department_name city country_name last_name location_id state salary country_id
department_id display ename dname city state country_name ? SELECT e.first_name||'
'||e.last_name as ename , d.department_name as dname, [Link] , [Link], c.country_name as
country FROM employees e INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id INNER JOIN countries c ON l.country_id
= c.country_id ; OUTER JOIN :- ------------------- => equi join returns only matching records but will
not return unmatched records to display unmatched records perform outer join. Ex :- EMP DEPT
empno ename sal deptno deptno dname loc 7369 smith 800 20 10 ACCOUNTS NEW YORK
7499 allen 1600 30 20 RESEARCH 7521 ward 1250 30 30 SALES 7566 jones 2975 20 40
OPERATIONS => unmatched row 7782 clark 2450 10 9999 vijay 2000 null => unmatche row =>
outer join is 3 types 1 left outer join 2 right outer join 3 full outer join LEFT OUTER JOIN :- ----------
-------------------- => returns all rows (matched + unmatched) from left side table and matching rows
from right side table. SELECT [Link],NVL([Link],'Not Assigned') as dname FROM emp e
LEFT OUTER JOIN dept d ON [Link] = [Link] ; smith research allen sales ward sales jones
research clark accounts vijay Not Assigned => unmatched from emp RIGHT OUTER JOIN :- ------
------------------------ => returns all rows (matched + unmatched) from right side table and matching
rows from left side table. SELECT NVL([Link],'Empty') as ename,[Link] FROM emp e
RIGHT OUTER JOIN dept d ON [Link] = [Link] ; smith research allen sales ward sales
jones research clark accounts Empty operations => unmatched from dept FULL OUTER JOIN :- --
---------------------------- => returns all rows from both tables SELECT [Link],[Link] FROM emp
e FULL OUTER JOIN dept d ON [Link] = [Link] ; smith research allen sales ward sales
jones research clark accounts vijay null => unmatched from emp null operations => unmatched
from dept Displaying only unmatched records :- ------------------------------------------------- left side
table :- --------------------- SELECT [Link], [Link] FROM emp e LEFT OUTER JOIN dept d ON
[Link] = [Link] WHERE [Link] IS NULL ; vijay null right side table :- -------------------------
SELECT [Link],[Link] FROM emp e RIGHT OUTER JOIN dept d ON [Link] = [Link]
WHERE [Link] IS NULL ; null operations both tables :- ---------------- SELECT
[Link],[Link] FROM emp e FULL OUTER JOIN dept d ON [Link] = [Link] WHERE
[Link] IS NULL OR [Link] IS NULL ; vijay null null operations Ex :- emp projects empno
ename sal projid projid name duration cost client 100 1000 1000 101 1001 1001 102 null 1002 1
display employee details with project details ? 2 display employee details with project details and
also dislay employees not assigned to any project ? 3 display only the projects where no
employee assigned to it ? GROUP BY & JOIN :- ---------------------------- SELECT columns FROM
tab1 JOIN tab2 ON join cond [WHERE cond] GROUP BY col1,---- [HAVING cond] [ORDER BY ---
-] ; => display dept wise no of employees ? display dept names ? SELECT
[Link],COUNT([Link]) as no_of_emps FROM emp e INNER JOIN dept d ON [Link] =
[Link] GROUP BY [Link] ; FROM :- ------------ EMP DEPT empno ename sal deptno deptno
dname loc 7369 smith 800 20 10 ACCOUNTS NEW YORK 7499 allen 1600 30 20 RESEARCH
7521 ward 1250 30 30 SALES 7566 jones 2975 20 40 OPERATIONS => unmatched row 7782
clark 2450 10 9999 vijay 2000 null => unmatche row ON [Link] = [Link] :- ------------------------
------------ 7369 smith 800 RESEARCH 7499 allen 1600 SALES 7521 ward 1250 SALES 7566
jones 2975 RESEARCH 7782 clark 2450 ACCOUNTS GROUP BY [Link] :- -------------------------
----- ACCOUNTS 7782 clark 2450 RESEARCH 7369 smith 800 7566 jones 2975 SALES 7499
allen 1600 7521 ward 1250 SELECT [Link],COUNT([Link]) as no_of_emps :- ------------------
--------------------------------------------------------- ACCOUNTS 1 RESEARCH 2 SALES 2 => display
dept wise no of employees ? display dept names ? display dept where no employees working ?
SELECT [Link],COUNT([Link]) as no_of_emps FROM emp e RIGHT OUTER JOIN dept d
ON [Link] = [Link] GROUP BY [Link] ; ACCOUNTS 1 RESEARCH 2 SALES 2
OPERATIONS 0 10-nov-25 Non Equi Join :- ---------------------- => non equi join is performed
between the tables not sharing a common field => this join is called non equi join because here
join condition is not based on "=" operator Ex :- EMP SALGRADE empno ename sal deptno
grade losal hisal 7369 smith 800 20 1 700 1000 7499 allen 1600 30 2 1001 2000 7521 ward 1250
30 3 2001 3000 7566 jones 2975 20 4 3001 4000 7782 clark 2450 10 5 4001 9999 => display
ename sal grade ? -------------- --------- emp salgrade SELECT [Link],[Link],[Link] FROM emp
e INNER JOIN salgrade s ON [Link] BETWEEN [Link] AND [Link] smith 800 1 allen 1600 2 ward
1250 2 jones 2975 3 clarks 2450 3 => display grade 3 employees list ? SELECT
[Link],[Link],[Link] FROM emp e INNER JOIN salgrade s ON [Link] BETWEEN [Link] AND
[Link] WHERE [Link] = 3 ; => display grade wise no of employees ? SELECT
[Link],COUNT([Link]) as cnt FROM emp e INNER JOIN salgrade s ON [Link] BETWEEN
[Link] AND [Link] GROUP BY [Link] ; => display ENAME DNAME GRADE ? SELECT
[Link], [Link], [Link] FROM emp e INNER JOIN dept d ON [Link] = [Link] INNER
JOIN salgrade s ON [Link] BETWEEN [Link] AND [Link] ; on [Link] = [Link] :- -----------------
---------------- EMP DEPT empno ename sal deptno deptno dname loc 7369 smith 800 20 10
ACCOUNTS NEW YORK 7499 allen 1600 30 20 RESEARCH 7521 ward 1250 30 30 SALES
7566 jones 2975 20 40 OPERATIONS => unmatched row 7782 clark 2450 10 SALGRADE R1
grade losal hisal 7369 smith 800 RESEARCH 1 700 1000 7499 allen 1600 SALES 2 1001 2000
7521 ward 1250 SALES 3 2001 3000 7566 jones 2975 RESEARCH 4 3001 4000 7782 clark 2450
ACCOUNTS 5 4001 999 on [Link] BETWEEN [Link] AND [Link] :- -----------------------------------------
--------------- R2 7369 smith 800 RESEARCH 1 7499 allen 1600 SALES 2 7521 ward 1250 SALES
2 7566 jones 2975 RESEARCH 3 7782 clark 2450 ACCOUNTS 3 SELECT
[Link],[Link],[Link] :- ------------------------------------------------------------ smith RESEARCH 1
allen SALES 2 ward SALES 2 jones RESEARCH 3 clark ACCOUNTS 3 SELF JOIN :- ---------------
-- => joining a table to itself is called self join => in self join a record in one table joined with
another record of same table => to perform self join the same must be declared two times with
different alias in FROM clause FROM emp x INNER JOIN emp y Ex :- emp x emp y empno
ename mgr empno ename mgr 7369 smith 7902 7369 smith 7902 7499 allen 7698 7499 allen
7698 7566 jones 7839 7566 jones 7839 7698 blake 7839 7698 blake 7839 7839 king null 7839
king null 7902 ford 7566 7902 ford 7566 => display ENAME MGRNAME ? SELECT [Link] || '
reports to ' ||[Link] as manager FROM emp x INNER JOIN emp y ON [Link] = [Link] ; smith
ford allen blake jones king blake king ford joines => list of employees reporting to blake ?
SELECT [Link] || ' reports to ' ||[Link] as manager FROM emp x INNER JOIN emp y ON
[Link] = [Link] WHERE [Link]='blake' ; => blake's manager name ? SELECT [Link]
FROM emp x INNER JOIN emp y ON [Link] = [Link] WHERE [Link] = 'blake' ; =>
employees earning more than their managers ? SELECT [Link],[Link],[Link] as
manager,[Link] as mgrsal FROM emp x INNER JOIN emp y ON [Link] = [Link] WHERE [Link] >
[Link] ; => employees who are senior to their managers ? SELECT [Link],[Link],[Link] as
manager,[Link] as mgrhire FROM emp x INNER JOIN emp y ON [Link] = [Link] WHERE
[Link] < [Link] ; => no of employees working under each manager ? manager
no_of_emps ? SELECT [Link],COUNT(*) as no_of_emps FROM emp x INNER JOIN emp y
ON [Link] = [Link] GROUP BY [Link] ; => display ENAME DNAME GRADE MNAME ?
SELECT [Link], [Link], [Link], [Link] as manager FROM emp e INNER JOIN dept d
ON [Link] = [Link] INNER JOIN salgrade s ON [Link] BETWEEN [Link] AND [Link] INNER
JOIN emp m ON [Link] = [Link] ; LISTAGG() :- ------------------- => function used to concatenate
column values LISTAGG(ename,seperator) WITHIN GROUP (ORDER BY ---) Ex 1 :- SELECT
LISTAGG(ename,',') WITHIN GROUP (ORDER BY SAL DESC) AS NAMES FROM EMP ;
king,ford,scott,jones,blake,clark,------ Ex 2 :- => display dept wise employee names ? SELECT
deptno, LISTAGG(ename,',') WITHIN GROUP (ORDER BY SAL DESC) AS NAMES FROM EMP
GROUP BY deptno ; Question :- TEAMS ID COUNTRY 1 IND 2 AUS 3 SA output :- IND VS AUS
IND VS SA AUS VS SA 11-nov-25 CROSS JOIN / CARTESIAN JOIN :- ----------------------------------
----------------- => cross join returns cross product or cartesian product of two tabels A = 1,2 B = 3,4
AXB = (1,3) (1,4) (2,3) (2,4) => if cross join performed between two tables then all records of 1st
table joined with all records of 2nd table. => To perform cross join submit the join query without
join condition. Ex 1 :- SELECT [Link],[Link] FROM emp e CROSS JOIN dept d ; Ex 2 :- T1
T2 ID NAME PRICE ID NAME PRICE 1 PIZZA 120 1 THUMSUP 40 2 BURGER 80 2 JUICE 60
output :- PIZZA + THUMSUP 160 PIZZA + JUICE 180 BURGER + THUMSUP 120 BURGER +
JUICE 140 SELECT [Link] || ' + ' ||[Link] , [Link] + [Link] AS TOTAL FROM T1
CROSS JOIN T2 scenario :- --------------- SALES dateid prodid custid qty amt 11-nov-25 100 10 1
2000 products prodid pname price category 100 KKK 2000 ELECTRONICS cust custid name
addr country 10 AA HYD IND => display year wise total amount ? SELECT EXTRACT(YEAR
FROM dateid) as year, SUM(amount) as total FROM sales GROUP BY EXTRACT(YEAR FROM
dateid) ; => display category wise total amount ? SELECT [Link] , SUM([Link]) as total
FROM sales s INNER JOIN products p ON [Link] = [Link] GROUP BY [Link] ; =>
display country wise total amount ? SELECT [Link] , SUM([Link]) as total FROM sales s
INNER JOIN cust c ON [Link] = [Link] GROUP BY [Link] => display year wise , country
wise , category wise total amount ? SELECT EXTRACT(year from [Link]) as year, [Link],
[Link], SUM([Link]) as total FROM sales s INNER JOIN products p ON [Link] =
[Link] INNER JOIN cust c ON [Link] = [Link] GROUP BY EXTRACT(year from [Link]) ,
[Link] , [Link] ORDER BY year ASC ; Natural join :- ------------------- => Natural join is
performed between the two tables based on the common field with same name emp dept empno
ename deptno deptno dname loc SELECT [Link],[Link] FROM emp e NATURAL JOIN dept
d ; => above query performs equi join between emp & dept based on deptno
==========================================================================
SET OPERATORS :- --------------------------- UNION UNION ALL INTERSECT MINUS A = 1,2,3,4 B
=1,2,5,6 A UNION B => 1,2,3,4,5.6 A UNION ALL B => 1,2,3,4,1,2,5,6 A INTERSECT B => 1,2 A
MINUS B => 3,4 B MINUS A => 5,6 => In ORACLE set operations performed between the rows
return by two queries SELECT statement 1 UNION / UNION ALL / INTERSECT / MINUS SELECT
statement 2 ; Rules :- ----------- 1 no of columns return by both queries must be same 2
corresponding columns datatype must be same Query 1 :- ------------------ SELECT job FROM emp
WHERE deptno = 20 ; CLERK MANAGER ANALYST CLERK ANALYST Query 2 :- ---------------
SELECT job FROM emp WHERE deptno = 30 ; SALESMAN SALESMAN SALESMAN
MANAGER SALESMAN CLERK UNION :- --------------- => combines rows return by two queries
=> duplicates are eliminated => result is sorted SELECT job FROM emp WHERE deptno = 20
UNION SELECT job FROM emp WHERE deptno = 30 ; ANALYST CLERK MANAGER
SALESMAN SELECT job,sal FROM emp WHERE deptno = 20 UNION SELECT job,sal FROM
emp WHERE deptno = 30 ; JOB SAL --------- ---------- ANALYST 3000 CLERK 800 CLERK 950
CLERK 1100 MANAGER 2850 MANAGER 2975 SALESMAN 1250 SALESMAN 1500
SALESMAN 1600 => difference between UNION & JOIN ? union join 1 combines rows combines
columns 2 horizontal merge vertical merge 3 performed between performed between two tables
two query outputs Ex 1 :- T1 T2 F1 C1 1 10 2 20 3 30 UNION :- JOIN :- --------------- ----------- 1 1
10 2 2 20 3 3 30 10 20 30 Ex 2 :- EMP_US ENO ENAME DNO 100 10 101 20 DEPT EMP_IND
DNO DNAME LOC ENO ENAME DNO 10 HR 200 10 20 IT 201 30 30 SALES => total employees
list ? SELECT * FROM EMP_US UNION SELECT * FROM EMP_IND ; => employees working at
US loc with dept details ? SELECT e.* , d.* FROM emp_us e INNER JOIN dept d ON [Link] =
[Link] ; => total employee list with dept details ? SELECT e.* , d.* FROM emp_us e INNER JOIN
dept d ON [Link] = [Link] UNION SELECT e.* , d.* FROM emp_ind e INNER JOIN dept d ON
[Link] = [Link] ; SELECT * FROM (SELECT * FROM emp_us UNION SELECT * FROM emp_ind)
e INNER JOIN dept d ON [Link] = [Link] ; UNION ALL :- ------------------ => combines rows return
by two queries => duplicates are not eliminated => result is not sorted SELECT job FROM emp
WHERE deptno = 20 UNION ALL SELECT job FROM emp WHERE deptno = 30 ; CLERK
MANAGER ANALYST CLERK ANALYST SALESMAN SALESMAN SALESMAN MANAGER
SALESMAN CLERK difference between UNION & UNION ALL ? UNION UNION ALL 1 eliminates
duplicates doesn't eliminate duplicates 2 result is sorted result is not sorted 3 slower faster
INTERSECT :- ------------------- => returns common values from the output of two select stmts
SELECT job FROM emp WHERE deptno = 20 INTERSECT SELECT job FROM emp WHERE
deptno = 30 ; CLERK MANAGER MINUS :- ------------- => returns values present in 1st query
output and not present in 2nd query output SELECT job FROM emp WHERE deptno = 20 MINUS
SELECT job FROM emp WHERE deptno = 30 ; ANALYST SELECT job FROM emp WHERE
deptno = 30 MINUS SELECT job FROM emp WHERE deptno = 20 ; SALESMAN Question 1 :- ---
------------- T1 T2 F1 F1 1 1 2 2 3 3 10 40 20 50 30 60 => write the output for following operations
? 1 EQUI JOIN 2 LEFT OUTER JOIN 3 RIGHT OUTER JOIN 4 FULL OUTER JOIN 5 UNION 6
UNION ALL 7 INTERSECT 8 MINUS Question 2 :- T1 T2 F1 F1 1 1 2 2 1 1 2 2 NULL NULL NULL
NULL => no of rows return by each operation ? EQUI JOIN => 8 LEFT OUTER JOIN => 10
RIGHT OUTER JOIN => 10 FULL OUTER JOIN => 12 UNION => 2 UNION ALL => 12
INTERSECT => 2 MINUS => 0 12-NOV-25 SUB-QUERIES / NESTED QUERIES :-
============================== => a query in another query is called sub-query or nested
query => one query is called inner / child / sub - query => other query is called outer / parent /
main query => first oracle executes inner next it executes outer query => output of inner query is
input to outer query => use sub-query when where cond based on unknown value Types of sub-
queries :- --------------------------------- 1 Non Co-related sub-queries single row sub-queries multi
row sub-queries 2 Co-related sub-queries 3 INLINE views and CTEs 4 scalar sub-queries single
row sub-queries :- ----------------------------------- => if inner query returns one value then it is called
single row sub-query SELECT columns FROM tabname WHERE colname OP (SELECT
statement) ; => sub-query output acts like a value for where cond => OP must be any relational
operator like = > < <> Ex :- => employees earning more than blake ? SELECT * FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename='blake') ; -----------------------------------------
------------------------- 2850 => employees who are senior to king ? SELECT * FROM emp WHERE
hiredate < (SELECT hiredate FROM emp WHERE ename='king') ; -----------------------------------------
---------------------------- 17-NOV-81 => name of the employee earning max salary ? SELECT ename
FROM emp WHERE sal = MAX(sal) ; => ERROR SELECT ename,MAX(sal) FROM emp ; =>
ERROR SELECT ename FROM emp WHERE sal = (SELECT MAX(sal) FROM emp) ; --------------
----------------------------- 5000 => name of the employee having max experience ? SELECT ename
FROM emp WHERE hiredate = (SELECT MIN(hiredate) FROM emp); ------------------------------------
------------ 17-dec-80 => names of the employees having min,max experience ? SELECT ename
FROM emp WHERE hiredate = (SELECT MIN(hiredate) FROM emp) OR hiredate = (SELECT
MAX(hiredate) FROM emp) ; SELECT ename FROM emp WHERE hiredate = (SELECT
MIN(hiredate) FROM emp) union SELECT ename FROM emp WHERE hiredate = (SELECT
MAX(hiredate) FROM emp) => display 2nd max salary ? SAL 5000 1000 3000 2000 2500
SELECT MAX(sal) FROM emp WHERE sal <> (SELECT MAX(sal) FROM emp) ; => name of the
employee earning 2nd max salary ? SELECT ename,sal FROM emp WHERE sal = (SELECT
MAX(sal) FROM emp WHERE sal <> (SELECT MAX(sal) FROM emp)); ---------------------------------
------- 5000 ---------------------------------------------------------------- 3000 => employees working at NEW
YORK loc ? sub-query :- ------------------ SELECT * FROM emp WHERE deptno = (SELECT
deptno FROM dept WHERE loc='NEW YORK') ; join :- ----------- SELECT e.* FROM emp e
INNER JOIN dept d ON [Link] = [Link] WHERE [Link] ='NEW YORK' ; => display employee
details with dept details working at NEW YORK loc ? join :- ----------- SELECT
[Link],[Link],[Link] FROM emp e INNER JOIN dept d ON [Link] = [Link] WHERE [Link]
='NEW YORK' ; sub-query :- ----------------- not possible difference between sub-query and join ? 1
to display data from one table and condition based on another table then we can use sub-query or
join 2 to display data from two tables then use join . => incr sal by 10% of the employee having
max experience ? UPDATE emp SET sal = sal + (sal*0.1) WHERE hiredate = (SELECT
MIN(hiredate) FROM emp) ; => swap employee salaries whose empno = 7369,7499 ? before
swap after swap 7369 800 7369 1600 7499 1600 7499 800 UPDATE emp SET sal = CASE
empno WHEN 7369 THEN (SELECT sal FROM emp WHERE empno=7499) WHEN 7499 THEN
(SELECT sal FROM emp WHERE empno=7369) END WHERE empno IN (7369,7499) ; =>
delete employee having max experience ? DELETE FROM emp WHERE hiredate = (SELECT
MIN(hiredate) FROM emp) ; => display dept having max no of employees ? SELECT DEPTNO
FROM EMP GROUP BY DEPTNO HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM EMP
GROUP BY DEPTNO); => display in which year max no of employees joined ? SELECT
EXTRACT(year from hiredate) as year FROM emp GROUP BY EXTRACT(year from hiredate)
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM emp GROUP BY EXTRACT(year from
hiredate)); Multi-row sub-queries :- -------------------------------- => if inner query returns more than
one value then it is called multi row sub-query SELECT columns FROM tabname WHERE
colname OP (SELECT statement); => OP must be IN,NOT IN,ANY,ALL single multi = IN <> NOT
IN > >ANY >ALL < employees working at NEW YORK,CHICAGO locations ? SELECT * FROM
emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc IN ('NEW YORK','CHICAGO'))
; ANY,ALL operators :- ---------------------------- => use ANY , ALL for > < comparision with multiple
values WHERE sal > ANY(1000,2000,3000) WHERE sal < ANY(1000,2000,3000) IF sal = 800
FALSE IF sal = 800 TRUE 1500 TRUE 1500 TRUE 4000 TRUE 4000 FALSE WHERE sal >
ALL(1000,2000,3000) WHERE sal < ALL(1000,2000,3000) IF sal = 800 FALSE IF sal = 800
TRUE 1500 FALSE 1500 FALSE 4000 TRUE 4000 FALSE => employees who are earning more
than all managers ? SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE
job='MANAGER'); ------------------------------------------------------------------- 2975 2850 2450 13-nov-25
co-related sub-queries :- ----------------------------------- => if inner query references values of outer
query then it is called co-related sub-query => execution starts from outer query and inner query
is executed no of times depends on no of rows return by outer query. => use co-related sub-query
to execute sub-query for each row return by outer query execution :- ------------------ 1 returns a
row from outer query 2 pass value to inner query 3 executes inner query 4 pass inner query
output to outer query 5 executes outer query where cond Ex :- emp empno ename sal deptno 1 A
5000 10 2 B 3000 20 3 C 4000 30 4 D 6000 20 5 E 3000 10 => find employees earning more than
avg sal of the organization ? SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM
emp) ; ------------------------------------------ 4200 => find employees earning more than avg sal of their
dept ? SELECT * FROM emp e WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno =
[Link]) ; emp empno ename sal deptno 1 A 5000 10 5000 > (where deptno = 10) 4000 TRUE 2
B 3000 20 3000 > (where deptno=20) 4500 FALSE 3 C 4000 30 4000 > (where deptno=30) 4000
FALSE 4 D 6000 20 6000 > (where deptno=20) 4500 TRUE 5 E 3000 10 3000 > (where
deptno=10) 4000 FALSE => employees earning max salary in their dept ? SELECT * FROM emp
e WHERE sal = (SELECT MAX(sal) FROM emp WHERE deptno = [Link]) ; emp empno
ename sal deptno 1 A 5000 10 5000 = (5000) TRUE 2 B 3000 20 3000 = (6000) FALSE 3 C 4000
30 4000 = (4000) TRUE 4 D 6000 20 6000 = (6000) TRUE 5 E 3000 10 3000 = (5000) FALSE =>
display top 3 max salaries ? SELECT DISTINCT [Link] FROM emp a WHERE 3 > (SELECT
COUNT(DISTINCT [Link]) FROM emp b WHERE [Link] < [Link]) ORDER BY [Link] DESC ; EMP a
EMP b SAL SAL 5000 5000 3 > (0) TRUE 1000 1000 3 > (4) FALSE 3000 3000 3 > (1) TRUE
2000 2000 3 > (3) FALSE 2975 2975 3 > (2) TRUE => display 3rd max salary ? SELECT
DISTINCT [Link] FROM emp a WHERE (3-1) = (SELECT COUNT(DISTINCT [Link]) FROM emp b
WHERE [Link] < [Link]) ORDER BY [Link] DESC ; => display Nth max salary ? SELECT DISTINCT
[Link] FROM emp a WHERE (&N-1) = (SELECT COUNT(DISTINCT [Link]) FROM emp b WHERE
[Link] < [Link]) ORDER BY [Link] DESC ; ROWID :- --------------- => rowid is a psuedo column
because it is not a column but acts like a column => rowid returns physical address of a row i.e.
where the record is stored in memory SELECT rowid,empno,ename,sal FROM emp ;
AAAeNjAAHAAAA5GAAA 7369 smith 800 => rowids are used to delete duplicate rows from table
Ex :- EMP44 ENO ENAME SAL ROWID 1 A 5000 AAA 2 B 6000 AAB 3 C 7000 AAC 1 A 5000
AAD 2 B 6000 AAE DELETE FROM emp44 WHERE rowid LIKE '%AAD' ; DELETE FROM emp44
WHERE rowid LIKE '%AAE' ; deleting duplicates row-by-row is difficulat if table contains lakhs of
duplicate rows , so execute the following query to delete all duplicate rows Method 1 :- (using co-
related sub-query) DELETE FROM emp44 a WHERE rowid <> (SELECT MIN(rowid) FROM
emp44 WHERE eno = [Link] AND ename=[Link] AND sal = [Link]) ; EMP44 ENO ENAME SAL
ROWID 1 A 5000 AAA <> (AAA) FALSE 2 B 6000 AAB <> (AAB) FALSE 3 C 7000 AAC <> (AAC)
FALSE 1 A 5000 AAD <> (AAA) TRUE 2 B 6000 AAE <> (AAB) TRUE Method 2 :- (not using co-
related sub-query) DELETE FROM emp44 WHERE rowid NOT IN (SELECT MIN(rowid) FROM
emp44 GROUP BY eno,ename,sal) ; AAA AAB AAC EMP44 ENO ENAME SAL ROWID 1 A 5000
AAA 2 B 6000 AAB 3 C 7000 AAC 1 A 5000 AAD 2 B 6000 AAE 14-nov-25 INLINE views :- ---------
------------ => sub-queries in FROM clause are called inline views or derived tables SELECT
columns FROM (SELECT statement) WHERE condition ; => sub-query output acts like a table for
outer query => INLINE views are used in following scenarios 1 to control order of execution of
clauses 2 to use result of one operation in another operation 3 to join two query outputs
controlling order of execution of clauses :- ---------------------------------------------------------- Default
order :- --------------------- FROM WHERE GROUP BY HAVING SELECT ORDER BY => To control
this order of execution use INLINE views SELECT SELECT FROM ----------------------------------------
---------------> FROM (SELECT WHERE FROM ORDER BY ORDER BY ) WHERE Ex 1 :- =>
display ranks of the employees based on sal and highest paid should get 1st rank ? SELECT
empno,ename,sal, DENSE_RANK() OVER (ORDER BY sal DESC) as rnk FROM emp ; above
query displays ranks of all the employees but to display top 5 employees SELECT
empno,ename,sal, DENSE_RANK() OVER (ORDER BY sal DESC) as rnk FROM emp WHERE
rnk <= 5 ; => ERROR column alias cannot be used in where clause because where clause is
executed before select , to overcome this use INLINE views SELECT * FROM ( SELECT
empno,ename,sal, DENSE_RANK() OVER (ORDER BY sal DESC) as rnk FROM emp ) E
WHERE rnk <= 5 => display top 3 max salaries ? SELECT DISTINCT sal FROM ( SELECT sal,
DENSE_RANK() OVER (ORDER BY sal DESC) as rnk FROM emp ) E WHERE rnk <= 3; =>
display 5th max salary ? SELECT DISTINCT sal FROM ( SELECT sal, DENSE_RANK() OVER
(ORDER BY sal DESC) as rnk FROM emp ) E WHERE rnk = 5 ; => display top 3 max salaries in
each dept ? step 1 :- find ranks of the employees with in dept based on sal SELECT deptno,sal,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rnk FROM emp ;
DEPTNO SAL RNK ---------- ---------- ---------- 10 5000 1 2450 2 300 3 20 3000 1 3000 1 2975 2
1100 3 800 4 30 2850 1 1600 2 1500 3 1250 4 1250 4 950 5 step 2 :- To display top 3 select rank
upto 3 SELECT DISTINCT deptno,sal,rnk FROM ( SELECT deptno,sal, DENSE_RANK() OVER
(PARTITION BY deptno ORDER BY sal DESC) as rnk FROM emp ) E WHERE rnk <= 3 ORDER
BY deptno ASC ; ROWNUM :- ----------------- => rownum generates row numbers i.e. record
numbers => used to display records with record number => rownum is also a psuedo column
because it is not a column but acts like a column Ex :- SELECT rownum,empno,ename,sal FROM
emp ; 9 7839 KING 5000 SELECT rownum,empno,ename,sal FROM emp WHERE sal>=2000; 5
7839 king 5000 SELECT rownum,empno,ename,sal FROM emp WHERE sal>=3000; 2 7839 king
5000 note :- => rownum is not based on table , it is based on query output , if query output
changes rownum also changes. => display first 5 rows from emp table ? SELECT
rownum,empno,ename,sal FROM emp WHERE rownum <= 5 ; => display 5th row from emp ?
SELECT rownum,empno,ename,sal FROM emp WHERE rownum = 5 ; => NO ROWS with
rownum in where conditions we cannot use = > only < <= can be used , to overcome this use
INLINE views. SELECT * FROM ( SELECT rownum as rno,empno,ename,sal FROM emp ) E
WHERE rno = 5 ; WHERE rno IN (5,10,15); WHERE rno BETWEEN 5 AND 10 ; WHERE
MOD(rno,2) = 0 => display last 3 rows from emp ? SELECT * FROM ( SELECT rownum as
rno,empno,ename,sal FROM emp ) E WHERE rno >= (SELECT COUNT(*)-2 FROM emp) ; CTE
:- ----------- => CTE stands for common table expression => CTE is alternative to INLINE view =>
using CTE also we can give name to the query output and we can use that in another query =>
using CTE we can simplify complex scenarios WITH AS (SELECT STATEMENT) , AS (SELECT
STATEMENT) SELECT statement ; Ex 1 :- WITH E AS (SELECT rownum as
rno,empno,ename,sal FROM emp) SELECT * FROM E where rno = 5; Ex 2 :- T1 T2 F1 F1 10 A
20 B 30 C output :- 10 A 20 B 30 C Query 1 :- SELECT ROWNUM as rno ,F1 FROM T1 ; RNO F1
---------- ---------- 1 10 2 20 3 30 Query 2 :- SELECT ROWNUM as rno,F1 FROM T2 ; RNO F -------
--- - 1 A 2 B 3 C WITH A AS ( SELECT ROWNUM as rno ,F1 FROM T1) , B AS (SELECT
ROWNUM as rno,F1 FROM T2) SELECT A.F1,B.F1 FROM A INNER JOIN B ON [Link] =
[Link] ; A B RNO F1 RNO F1 1 10 1 A 2 20 2 B 3 30 3 C Ex 3 :- T1 AMT 5000 -300 4000 -200
3000 -500 output :- POS NEG 5000 -300 4000 -200 3000 -500 Query 1 :- SELECT ROWNUM as
rno,AMT FROM T1 WHERE AMT > 0 ; 1 5000 2 4000 3 3000 Query 2 :- SELECT ROWNUM as
rno,AMT FROM T1 WHERE AMT < 0 ; 1 -300 2 -200 3 -500 WITH P AS ( SELECT ROWNUM as
rno,AMT FROM T1 WHERE AMT > 0) , N AS (SELECT ROWNUM as rno,AMT FROM T1
WHERE AMT < 0 ) SELECT [Link] , [Link] FROM P INNER JOIN N ON [Link] = [Link] ; Ex 4
:- CRICKET TEAMA TEAMB WINNER ENGLAND NEW ZEALAND NEW ZEALAND country
played won lost 17-nov-25 scalar sub-queries :- --------------------------- => sub-queries in SELECT
clause are called scalar sub-queries SELECT (select stmt1) , (select stmt2), ---------- FROM
tabname WHERE cond => sub-query output acts like a column => use scalar sub-query to show
the query output in seperate column Ex 1 :- SELECT (SELECT COUNT(*) FROM emp) AS EMP,
(SELECT COUNT(*) FROM dept) AS DEPT FROM DUAL ; EMP DEPT ---------- ---------- 14 4 Ex 2
:- => dislay dept wise total salary ? SELECT deptno,SUM(sal) as dept_totsal FROM emp GROUP
BY deptno ; DEPTNO SUM(SAL) ---------- ---------- 30 9400 10 8750 20 10875 => display
DEPTNO DEPT_TOTSAL TOTSAL ? SELECT deptno,SUM(sal) as dept_totsal , (SELECT
SUM(sal) FROM emp) as totsal FROM emp GROUP BY deptno ; DEPTNO DEPT_TOTSAL
TOTSAL ---------- ----------------- ---------- 30 9400 29025 10 8750 29025 20 10875 29025 => display
DEPTNO DEPT_TOTSAL TOTSAL PCT ? PCT = ( DEPT_TOTSAL / TOTSAL )*100 SELECT
deptno,SUM(sal) as dept_totsal , (SELECT SUM(sal) FROM emp) as totsal , ROUND( (SUM(sal)
/ (SELECT SUM(sal) FROM emp))*100,1) as pct FROM emp GROUP BY deptno ; WITH E AS (
SELECT deptno,SUM(sal) as dept_totsal, (SELECT SUM(sal) FROM emp) as totsal FROM emp
GROUP BY deptno ) SELECT deptno,dept_totsal,totsal,(dept_totsal/totsal)*100 as pct FROM E ;
========================================================================
PIVOT operator :- ------------------------ => operator used to convert rows into columns => operator
used for cross tabulation => operator used to display data in matrix form SELECT columns FROM
(SELECT required data) PIVOT ( AGGR-EXPR FOR colname IN (v1,v2,v3,---) ) ORDER BY col
ASC/DESC ; Ex 1 :- 10 20 30 analyst ? 2000 ? clerk manager president salesman 5600 SELECT
* FROM (SELECT deptno,job,sal FROM emp) E PIVOT ( SUM(sal) FOR deptno IN (10,20,30) )
ORDER BY job ASC ; Ex 2 :- 1 2 3 4 1980 ? ? ? ? 1981 1982 1983 SELECT * FROM (SELECT
TO_CHAR(hiredate,'yyyy') as year , TO_CHAR(hiredate,'q') as qrt, empno FROM emp) E PIVOT
( COUNT(empno) FOR qrt IN (1,2,3,4) ) ORDER BY year ASC ; Ex 3 :- STUDENT sno sname
subject marks 1 A MAT 80 1 A PHY 60 1 A CHE 50 2 B MAT 50 2 B PHY 60 2 B CHE 40 output :-
sno sname mat phy che 1 A 80 60 50 How to create new table from existing table (replica) :-
========================================== CREATE TABLE AS SELECT columns
FROM [WHERE --] ; Ex 1 :- copying complete table CREATE TABLE emp20 AS SELECT * FROM
emp ; => all the rows and columns of emp table copied to emp20 Ex 2 :- copying specific rows &
cols CREATE TABLE emp21 AS SELECT empno,ename,job,sal FROM emp WHERE job IN
('CLERK','MANAGER') ; Ex 3 :- copy only structure (cols) but not data (rows) CREATE TABLE
emp22 AS SELECT * FROM emp WHERE 1=2; Ex 4 :- copying table from one user to another
user copy emp table from user BATCH53 to user VIJAY ? USERNAME :- SYSTEM/TIGER
SQL>CREATE TABLE [Link] AS SELECT * FROM [Link] ; copying data from one
table to another table :- ---------------------------------------------------------------- INSERT INTO SELECT
column FROM [WHERE cond] ; Ex :- copy data from emp table emp22 ? INSERT INTO emp22
SELECT * FROM emp ; MERGE command :- ---------------------------- => command used to merge
data into a table => merge is the combination of insert & update => using merge command we
can apply changes made to one table to another table MERGE INTO USING ON (condition)
WHEN MATCHED THEN update ; WHEN NOT MATCHED THEN insert ; Ex :- step 1 :- create
source table CREATE TABLE custs ( cid NUMBER(2), cname VARCHAR2(10), city
VARCHAR2(10) ); INSERT INTO custs VALUES(10,'A','HYD') ; INSERT INTO custs
VALUES(11,'B','BLR') ; step 2 :- create replica CREATE TABLE custt AS SELECT * FROM custs ;
SELECT * FROM custs ; SELECT * FROM custt ; CID CNAME CITY CID CNAME CITY 10 A
HYD 10 A HYD 11 B BLR 11 B BLR step 3 :- modify source table data INSERT INTO custs
VALUES(12,'C','DEL') ; UPDATE custs SET city='MUM' WHERE cid=10 ; SELECT * FROM custs
; SELECT * FROM custt ; cid cname city cid cname city 10 A MUM => U 10 A HYD 11 B BLR 11 B
BLR 12 C DEL => I step 4 :- apply changes made to custs to custt MERGE INTO custt t USING
custs s ON ([Link] = [Link]) WHEN MATCHED THEN UPDATE SET [Link] = [Link] WHEN NOT
MATCHED THEN INSERT VALUES([Link],[Link],[Link]) ;