Data::-----

--->The Raw Facts which describes the attribute(pr) of an Enitity(Object)

or a transcation is known as Data.
------>Data can be valid or inValid..
--->A data can be valid or invalid based on situation/Scenario

Information:---
-->Collection of data becomes information.

====================================
Note::--now simply storing the data is not important ,how fast we can retreieve
it is very important.
DataBase:---
---A medium to store data/infomation in an organized manner and
systematic manner is known as database.
---Every Database will havs some functionalities::---
1.inserting the data
2.Reading the data/Retrieving the data.
3.Modify the data
4.Delete the data.
5.Manage the data..............etc...
=========================================================================
DBMS:--(Data Base Management System):----
--->A software (set of program) which is used to create,manage and maniuplate the database.

===========
Ex of DBMS are:--
1.Oracle RDBMS.
2.Mongo DB.
3.Sql Server.
4.MySql
5.Postgre
6.DB2 etc...............

===================
Query Language:--
--It is usted to Communicate with the DBMS system software..
or
A medium which helps user/programmer to Communicate with DBMS
is known as Query lang...
==================
A DBMS s/w may have any of the following model for Storing the data::---
--Hieararchial Model
--Network Model
***(Sql)--Relational Model
--Object Model
**(MONGO DB)--Documnet model etc................
======================================================================================
==========
Hiearchical Model::--
This types of DBMS employs the parent child relationship of Storing data.
This type of DBMS is rarely used because of:--
1.Complexity Increase
2.Maintaince cost will be high
3.It will not have good perf..while retrieving
==========================
Network Model::--
--It supports many-many relations which leads to complex structure of DBMS.

====================================================================================
Relational Model::---
--This Model was given By E.F Codd.In this type of Model data will be stored data in the
form of
row and COl...(i.e Table)
===================================================
RDBMS:--(Relational Data BAse Management System)::---
-->A type of DBMS system software which stores and organises the data in the using
relational model,
such DBMS is known as RDBMS.

Table:--Table is a logical Structure which contains column,row and cell

column:-- A column represents an attribute of an entity.

column is also know as Field or Attribute

Row:-- A row represnts all the attributes of one entity.

Row is also known as tuple or record.

Cell:--cell is the smallest unit of table ,which represnts data.

or Cell is a building block of table.

==============================================================
Types of Stmt/Query Language:---
1.DDL
2.DML
3.TCL
4.DCL
5.DQL
===============================================================
DDL:--Data Definition Language---
create,rename,alter,truncate,drop

create:-- It is used to create a table in data Base.

rename:-Rename is used to change the table/object name in DataBase.
alter:--it is used to modify(Adding col,delete colm...) the definition of a table
Truncate:--Truncate is used to delete all/entire the rows present in a table but
table structure remains same.
drop:--drop is used to delete all the rows and even table structure will be deleted.
===============================================================
DML:--Data MAnipulation Language:--

insert,update,delete..

insert::--it is used to insert a data into the table.

update::--update stmt is used to modify the data present in the table
delete::--delete is used to eraise/data of specified row.

========
**** what is diff b/w truncate,delete and drop.
Ans:--
Trucate will delete entire row of a table where drop will delete rowas well as table
structure
and delete is used to delete only specific data.

================================================================
TCL:-Transcation Control Langauge):--
commit,rollback,savepoint.
commit::-It is used to save a transaction in Database.
Rollback:--Rollback is used to undo a transaction which is not saved.
Savepoint:--It is used to mark a position of a transaction.
=================================================================
DCL(Data Control Langauge)---
grant,revoke
grant:--it is used to give permission to another user to access the table.
revoke:--It is used to take back the permission to another user.
==================================================================
DQL(Data Query Language):
--Fetching a data from data base.

select :--reterieve the data from table.

=============================================================================
Data Type:--
data types is the one which will specifiy what type
of value variable is goining to store.
1.char
2.varchar/varchar2
3.date
4.number
5.LOB(Large Object) --4GB file
-- CLOB(Char Large Object)--->text files,pdf,excel etc.....
-- BLOB(Binary Large Object)-->image,video,audio etc...
====================================================================
1.char:--
char datatype is used to store character such as :--
A-Z,a-z,0-9,and special character(@,#,*,%,$...)
 For char data type we can pass an arg as Size.
Tha maximum size of char datatypes is 2000bytes
(2000 characters can be stored)
syntax:--
char(size)
where size specifies the max. no of
characters that can be stored.
ex:--
char(10)......10 blocks will be
allocated
Dis-advanatge:--
- In char data types there will be fixed length
memory allocation.

ex:--Let us assume we are going to store "SMITH",

only 5 blocks of memory will be used
remaining 5 blocks are not used.
these unused memory is known as Memory Leakage.

====================================================================
2.varchar:--
--varchar datatype is used to store character
such as :--
A-Z,a-z,0-9,and special
character(@,#,*,%,$...)
--For varchar data type we can pass an arg as Size.
--Tha maximum size of varchar datatypes is 4000bytes
syntax:--
varchar(size)
where size specifies the max. no of
characters that can be stored.
---Varchar data is a variable length data type,
therfore there will be no-memory wastage.
---in varchar memory will be allocated at the
time of storing the value.
======================================================================================
========
number:
--this data type is used to store int value as well as double value
--this data types cab accept upto 2 arg(precision,[scale]),where 2nd arg is optional.
--if we want to store only int then no need to pass scale arg.

number:--
number(precision,scale(optional))
i.e:
precision:--it represnts total no of digit that can be stored in single cell

scale:--it represnts the no of numeric/digit values to stored after decimal point

ex:-- number(5):--we can store upto 5 int value.(12345)

356.36855 number(8,5)::--we can store upto 3 int and 5 decimal value
0.2589 number(4,4):: it will store only dec..value
number(3,5)--wrong
number(5,0)--5 int value.
number(5,5) :--double value
can scale value be greater than precision??-
--Always precision >= scale
--But scale cannot be greater than precision.
===========================================================================
null:--
--in sql null is a keyword which is which represnts an empty cell
--when an entitiy does not have any value,it will be represnted by null.

notes:--
1.null value does not require any memory.
2.null does not represents zero or space.
3.in oracle-RDBMS two null are unique
ie. null1 != null2;

4.Any operation perfome don null results in null value.

5+null==>null
5*null==>null...........etc...
============================================================================
DQL(Data Query Language):---

1.select:---The retrival of data from the table in the database is done using select stmt.

2.Projection:--the retrival of data by selecting only column name is known as Projection

3.Selection::-the retrival of data by selecting either column or by selecting row

is known as Selection.

4.join::-- the retrival of data by selecting mutiple tables is known as join.

 ===========================================================================
Projection::--
---the retrival of data by selecting only column name is known as Projection.

syntax:-
select */(Distinct)Colm_Name/expression[Alias]
from table_Name;

Order Of execution:--
1.from clause
-->From clause will execute 1st and it used to search the table present
in database under execution.
2.select clause
--After the execution of from clause we will get some table and from the
table we are going to select the colm..which is required

--varchar is an external data type and varchar2 is an internal data type.

Query:--
1.WATD deatils of all employe.
--->>select * from emp;
2.WATD name,sal,deptno of employe.
-->>select ename,sal,deptno from emp;
-------------------------------------------------------------------------
Expression::---

--Anything which yields(Gives) a result is known as Expression.

--An Expression consists of :--
1.operand:-the resources/variable on which any opertions can be performed.
2.operators:-it is used to perform some operation(+,-,*...........)
--In sql operand are of 2 types:--
1.colun_Name(sal,ename........)
2.Direct value/Immediate value.

====================
1.WATD Annual Sal of all employe..
---->
select sal*12
from emp;

2.WATQD employe sal with hike of 10%..

>--
select sal+(sal*10/100)
from emp;
=================================
ALIAS:--
--It is an alternate name give to a column in result table.
--ALIASING can be done in 2 ways:--
1.By using AS operator.
 ---
syntax:-- select old_ColmNAme as New_ColName
from tableName;

2.Without using AS operator.

syntax:-- select old_ColmNAme New_ColName

from tableName;
=========================
1.WATD Annual Sal of all employe..
---->
select sal*12 as "ANNUAL SALARY"
from emp;

2.WATQD employe sal with hike of 10%..

---->
select sal+(sal*10/100) "SAL WITH HIKE OF 10%"
from emp;

note:--
select sal*12 ANNUAL SALARY
from emp;
-
Error:--
FROM keyword not found where expected
****If user want to Give 2 string as Alias name then we need to use " "

select sal*12 "ANNUAL SALARY"

from emp;
===================================
Assignm::---
1.WATD name and half term sal for all the employees.
2.Watd name,deptno,sal with hike of rs100 for all employees
3.watd name and annual sal with bnous of rs2000
4.watd name,deptno,empno and sal with deduction of 10%
5.Watd name,desgination of all employees along with deduction of 30% in aunnual sal.
==============================================================
Distinct:--
--Distinct clause is used to remove duplicate records present in the result table,
which reults in unique record in result table.

ex:--
WAQTD unique job from employe table
--->select distinct job from emp;

WAQTD unique deptno,job from employe table.

--->select distinct job,deptno from emp;

======================================================================================
========
Selection:--
-->The retrival of data by selecting either columns and rows is known as Selection.

syantx:--
select */(Distinct)Colm_Name/expression[Alias]
from table_Name;
where<filter_Condition.

Where Clause:--

--where clause is used to filter the records.

--It executes row-by-row
--For where clause we can pass filter condition as an argum...

Order of EXecution:---
1.from clause :--brings the req..table under execution.
2.where clause:--filters the unwanted record.
3.select clause:--selects the wanted record.

note:-- sql is a case insensitive.. language(i.e function,table,column) but data present

in table is
case sensitive.
===================================================================================
-->WATQD all the details of employee who are getting sal greater tahn 1500.

-->WAQTD name of employee and his job if and only if he is working as president
>>>
-->WAQTD name,sal with 10% hike if his sal less than 2000

-->WAQTD name and hiredate of employee only if they are working as MANAGER.

-->WAQTD details of employees working as SALESMAN

-->WAQTD employee id and name of employees if he is working in deptno 10

-->WAQTD all the details of employee hired before 1982

select *
from emp
where hiredate<'01-JAN-82';

-->WAQTD enam,job,sal and anuual sal if annual sal exceeds 14000

>> select ename,job,sal,sal*12 "ANNUAL SAL"
from emp
where sal*12 >14000;

-->WAQTD ename,mgrno,deptno if he works as manager in deptno 20

select ename,mgr,deptno
from emp
where deptno=20 and job='MANAGER'

-->WAQTD all the details of employe and annual sal if he works as anaylst..

==============================================================================
Order by Clause:--
---Order by Clause is used to sort/arrange the records either in
ascending or or in dec.. order
---By default Order by Clasue sorts the record in ascending order.
synatx:--
select */(Distinct)Colm_Name/expression[Alias]
from table_Name;
where<filter_Condition.
order by colm_name/expression asc/desc;
order of execution:--
1.from clause
2.where by Clause---row by row
3.select Clause-----row by row
4.order by Clause.--row by row
=========================================================
waqtd sort sal in desc order.
--->
select *
from emp
order by sal desc
=======================================================>>>
Can be pass more than one column for Order by clause??
--->Yes,we can pass more than one column for Order by clause.
--->But First prority will be given to first.

==================================================================
WAQTD and sort table emp based on sal,deptno.
->In this case complier will give priority for
sal column because it is the first col name we passed in order by,
then complier will even check deptno colm also
and if we have any duplicate sal in sal colm then that duplicate sal will
be sorted based on deptno.
SAL COMM DEPTNO
---- ---------- ----------
 800 20
950 30
1100 20
1250 1400 20
1250 500 30
1300 10
1500 0 30
1600 300 30
2450 10
2850 30
2975 20
3000 20
3000 30
5000 10
=============================================================================
Operators:
--it is used to perform some operation.
-Operators IN SQL:--
a.Arithmetic Operator
b.Concatination Operator
c.Logical Operator
d.Comparision operator
e.Relational operator.
f.Special operator
1.In
2.not In
3.between
4.not between
5.is
6.not IS
7.Like
8.not Like
g.Sub query operator
1.All
2.ANY
3.exists
4.not exists.
=============================================================
b.Concatination Operator:-(||)(Pipe Operator)
--This operator is used to join the given two String.
SO it eanbles usto display output in sentence formate.

ex:---
HI SMITH

WAQTD HI repsective name your sal has been hiked by 10% (hiked sal should be printed)
and you are working in repsective deptno.
=========================================================
Logical Operator:--
AND :It will check both/Multiple cond..
if all cond..is true we wll get output as true otherwise false
0 0 -------0
0 1 -------0
1 0 -------0
1 1 -------1
OR :It will check all the condition and if either of
cond..is true we wll get output as true.
0 0 -------0
0 1 -------1
1 0 -------1
1 1 -------1

Not:it will give just compliment

0----1
1----0
=====================================================================
waqtd all the details of an employee whose mgr no is either 7839,7566
-- select *
from emp
where mgr=7839 or mgr=7566;
waqtd names of all managers hired after april 81
--
select ename
from emp
where hiredate>'30-APR-81' and job='MANAGER';

waqtd name of the clerk if he works either in deptno 10,20,30.

---
select ename
from emp
where job='CLERK' and (deptno=10 or deptno=20or deptno=30);
===========================================================
WQATD all the details of an employee who are working as
manager or clerk in deptno 10 or 30
WAQTD details of the employees working as anaylst hired after 1985 getting sal
>2500 but less than 4000
WAQTD details of employes either getting annual sal b/w 9600 to 15000

===================================================================
Special operator:---

1.In operator:--
--In operator is a multi valued opertor which can accept single value(COl_name) at LHS
and multiple value at RHS.
--In operator returns true if any of the value present on RHS satifies the condition,
otherwise it returns false
sntax:--
col_name/expression in(va,v2,v3.............vn);

ex:-- WAQTD all the managers either working in dept 10 or 20 or 30.

2.Not In operator:--
--It performs reverse operation of in opertor

=======================================================================
Like Operator:----

======================================================
what should be done if i want to select a name which contains specail character.
ex:--Bahu_Bali

Ans:-we need to make use Escape character.

==========================================================================
Function:---
--Functions are the named block of code or list of instructions which are
used to perform a particular operation
--Function conatins/Consists of 3 major componenets:--
1.Function name
2.No of argu.. or input
3.Return types.
--There are 2 types of function present in SQl namely:
1.Single Row Function.(SRF)
2.Multi Row Function.(MRF)

1.Single Row Function.(SRF):::--

--SRF takes 1 input,processes and generates o/p for single i/p.
--If we pass n-no of i/p to a single row function then we will get n-no of o/p.
--Types of SRF are:--
1.Number SRF/Math SRF
2.Character SRF
3.Date SRF
4.conversion SRF
5.General SRF.

2.Multi Row Function.(MRF):::---

--MRF will aggregate or combines all the input at once,processes it and generates a Single
output.
--If we pass n-no of i/p to MRF then we will get single o/p.
--Types of MRF are:--
1.max()
2.min()
3.sum()
4.avg()
5.count()
==================================================================================
Note:-Dual is a dummy table,which is used to output the result.

1.Number SRF/Math SRF:--

--It is used to perform Math.......operation
--Types:--
1.mod()
2.power()
3.abs()
4.sqrt()
5.round()
6.trunc()
============================================
1.mod():--This function is used to obtain the remainder(modules)
--It can accept 2 argu...
synax:-- mod(m,n)
m=divident
n=divisor.

ex:-- select mod(10,3)

from dual;
o/p:--1
================================
2.power():--Power function is used to obtain exponential value for a given number.
--It can accept 2 arg::
syntax:--power(x,n)
x=is the base
n=power
ex:--- select power(2,4)
from dual;
o/p:-16
=============================
3.abs():--It is used to obtain magnitude of the given number.
--it will take only one arg.

syntax:- abs(10)
o/p:10

abs(-240)
o/p:240

===============================
4.SQRT():--this function is used to obtain square root of a number.
--I will take only one arg

syntax:--sqrt(number)

ex:--sqrt(81)
ex::-9
 sqrt(2)
ex:-1.414
============================
0.0-0.4::--0
0.5-0.9::--1.0

5.round()::--
--Round funct is used to round-off the given number to the nearest number.
--It can accept upto 2 arg..where 2 and arg..is optional;

syn::-- round(number,[scale])
scale is optional,it can be -ve as well as +ve
-ve scale::---means before the decimal point and the count starts from 1
+ve scale:--means after the the decimal point and count starts frm 0.
==========================================

select round(66.967888,-1) from dual;

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

0.0-0.4=0
0.5-0.9=1

Trunc():--
--This function is used to round off the given number always to a lower value.
--Trunc(number,[scale])
scale is optional,it can be -ve as well as +ve
-ve scale::---means before the decimal point and the count starts from 1
+ve scale:--means after the the decimal point and count starts frm 0.

round(5.5)

--6

trunc(5.5)
5
round(56.96,-1):--
==================================================================
Character SRF::--
Case Manipulation Function
--upper()
--lower()
--initcap()
Character Manipulation Function
---length()
---concat()
---reverse()
---substr()
 ---instr()
---replace()
---trim()
====================================================================
1.Upper():--Converts given String to upper case.
synax:-- upper('String')
ex:-- upper('jspiders')
---JSPIDERS
2.lower():--converts given String to lower case.
syn:-- lower('String')
ex:-- lower('JSPIDERS')
--- jspiders

3.initcap():--It converts the first character of each word of the sentence into uppercase.
syntx:--initcap('String')
===================================================================
1.length():--
--This function is used to obtain length of the given String,even space will be counted.
synx:--length('String')

ex:-- length('JSPIDERS')
---8
length('India Is Great')
--
WAQTD name of employees having 4 characters in their name.
select ename
from emp
where length(ename)=4;

WAQTD details of employee who getting sal in 4digit.

select *
from emp
where length(sal)=4;
=======================================
2.reverse():--Used to reverse the given String..

Syntax:--reverse('String')

ex:--reverse('MADAM')

reverse('RAM'):---MAR

..WAQ to reverse the character present in the name of each employee.

select reverse(ename)
from emp;

=========================================
3.Concat():--
--This function is used to merge the given two String.
syn:--
concat('Str1','Str2')

ex:--wat print HI SMITH...

select concat('HI','SMITH')
from dual;
Note::-If we want to concat n-String how many concat function is required??
:--(n-1) concat is required is required.

--WQAT print hi............How are you for all the employess.......

select concat( concat(' HI ',ename),' HOW ARE YOU..!!!!!!!!!!')

from emp;

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

-WAQTD HI repsective name your sal has been hiked by 10% (hiked sal should be printed)
and you are working in repsective deptno(print the deptno of each employe)
======================================================
Substr():---
-this function is used to obtain substring(part of a string) from a given origianl String.
-This function can accept 3 arg..

syntax:--
substr('Original String','Position',[Length])

Arg1:--Original String
--This is the String from which subString will be extracted.

Arg2::-Position
--It specifies from where to begin the extraction.

Arg3:Length:--
--Length arg is not manadtory arg..
--If the lenght is not mentioned we have to consider the complete length of the String after
the starting position
--It specifies the number of character to be extracted.

====

Position::starts from 1
here position can be positive and it can be negative.
But ,length can be only +ve.

ex:---- ------------------>+ve
1 2 3 4 5 6 7 8
 J S P I D E R S
-8 -7..........-1

-ve <-------------------
ex:---
J S P I D E R S

S P I
substr('JSPIDERS',2,3)

substr('JSPIDERS',2)

JSPIDERS:---ERS.....
1.WAT to extract first 3 char of employe name.

select substr(ename,1,3)
from emp;
2.WATD first 3 char of employe working as CLERK;

select substr(ename,1,3)
from emp
where job='CLERK';

3.WATD name of employee whose name starts by vowel.

select ename
from emp
where substr(ename,1,1) in ('A','E','I','O','U');

-------->>>
substr(ename,round(lenght(ename)/2),1)

4.WAQTD 2nd half of all the employee name.

--->
select substr(ename,(round(length(ename)/2)+1))
from emp.
5.WAQTD name of employee from 2nd char to last but one char.

SMITH
5-3==3

6-2=4...............................

MIT

select substr(ename,round(length(ename)-2))
 from emp.

6.
QSPIDERS:----qspiDers

o/p:--select lower(substr('JSPIDERS', 1, 4))||initcap(substr('JSPIDERS', 5, 8)) from

dual

concat(lower(substr(ename,1,length(ename)/2)) ,initcap(substr(ename,length(ename)/2)+1)
)

select concat(lower(substr(ename,1,length(ename)/2)),
initcap(substr(ename((length(ename)/2)+1))))
======================================================================
instr():--

--instr function is used to obtain the index value if the sub string is present in the
original.

-This function can accept 4 arg:--

syn:--
instr('Original String','SUBSTR',position to start checking,[n th occurence])

1.Orgiginal String:--
--This is the String in which substring to be searched

2.substr:---
--it is the String whose occurence is to be searched in the Original String.

3.position:-
--It specifies from where to begin the search

4.nth occurence:--
--it is not the manda... arg..
-If the nth occurence is not mentioned ,the deafult value given to is is 1st occurence
-It is to indicate the no of times a susbtr occurs in the original String..

ex:-- JSPIDERS

S:--1 occurence

instr('JSPIDERS','S',1)
select instr('JSPIDERSAFFGS','S',2,3)
from dual
13..
============================================
replace:---

this function is used to overwrite substring with new String in the original String..

syn:---
replace('Original String','SUBSTR',['New String to replaced']).

ex:-- DINGA
---PINGA

replace('DINGA','D','P')

WAT replace A with @ in ename column of emp table.

WAT replace A with @ and S with # in ename column of emp table.

replace('DINGA', 'D') how INGA

INGA

replace('DINGA', 'DINGA')
======================================================================================
========
trim():--

--Trim function is used to remove characters from the begining or from the end or from both
the ends but not from middle.

syntax:--
trim(leading/trailing/both 'CHAR' from 'String')

leading:--then char will be removed from begining

trailing:--then char will be removed from end
both:--then char will be removed from begining as well as from end

note:--if we missed leading/trailing/both then char will be trimmed from both

the end.
=================================================================
SYSDATE:--this command is used to obtain the current date of the system in which RDBMS is
installed.
ex:-- select sysdate
from dual;
SYSTIMESTAMP:--this command is used to obtain the date,time,and the time zone of the system.
 ex:-- select systimestamp
from dual;
Date SRF:--
1.ADD_MONTHS();
2.MONTHS_BETWEEN();
3.EXTRACT()
4.LAST_DAY()

1.ADD_MONTHS():--

--This function is used to add the no of months to a given date.

syntax:-- add_months('DATE',no_of_months)

ex:--
select add_months(sysdate,24)
from dual;

o/p:--ADD_MONTH
---------
21-JUL-22

2.MONTHS_BETWEEN():--
--This function is used to obtain the number of months
between the given two date.

syntax:-- months_between('DATE1','DATE2')
 ex:--
select months_between('21-JUL-22',sysdate)
from dual;

o/p:---

MONTHS_BETWEEN('21-JUL-22',SYSDATE)
-----------------------------------
24

==================================
WAQ to fetch expre....of all employess in years from emp date.

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

extract():--

--This function is used to extract the day,the month or

the year from the given date in number/int formate

syntax:--- extract(day/month/year from Given_Date)

ex:--
select extract(day from sysdate)
 from dual;

EXTRACT(DAYFROMSYSDATE)
-----------------------
21
select extract(month from sysdate)
from dual

EXTRACT(MONTHFROMSYSDATE)
-------------------------
7
select extract(year from sysdate
from dual

EXTRACT(YEARFROMSYSDATE)
------------------------
2020
Note:--Userdefined date can not be passed directly
if user wants to pass then 1st date string should be converted
to date formate.

===========================================
waqtd ename a
nd hiredate of employee hired in may month.

waqtd ename and hiredate of employee hired on 1st of the month

waqtd ename,sal,hiredate of employee hired in may or dec month

waqtd ename and hiredate of employee hired in the year 1982.

===================================================================
last_day():--
--This function is used to obtain the last day of the
given month in the given date.
syntax:--
last_day('date')

select last_day(sysdate)
from dual;

290-161-029
======================================================================================
=====
General SRF:--
1.nvl()
2.nvl2()

1.nvl():--Null value logic

-NVL function is used to overcome the drawbacks of opertions perfomed on null.

syntax:--nvl(arg1,arg2)

Arg1:--
--In arg1 we can write the column namewhich has null value
-If the value present in column is null then the value present in arg2 is returned.
-If the value present in column is not null then the same value of arg1 will be returned

Arg2:
Here we write any value which has be to substituted if the value present in arg1 is null

======================================================================================
====
nvl2()::---
--The oracle nvl2() function is an extension of nvl() function with diff option based on
whether a null value exists.

syntax:-- nvl2(arg1,arg2,arg3)

--in nvl2() function we can pass upto 3 arg.If the 1st arg.. is not null,then it returns
the 2nd arg.In case the second arg..is null then it returns third arg.

======================================================================================
======
Conversion SRF:---
1.ascii()
2.to_date()
3.to_char()

1.to_date():--this function is used to convert the date String

to date format.

syntax:--to_date('date in String','Formate model');

2.to_char():--This function is used to convert the given date

to String format
and it is used to formate the date model a/c user choice

syntax:-- to_char(date,'FORMAT-MODEL').

FORMAT-MODELS are:--
1.Year:---twenty twenty
2.YYYY :--2020
3.YY :--20
4.MONTH :--JULY
5.MON :--JUL
6.MM :--07
7.DAY :--MONDAY
8.DY :--MON
9.DD :--19
10 D :-- 9......

===================================================================
Multi Row Function:----
1.max():--
 --this function is used to obtain the maximum value present in
a column.
syn:
max(column_name/exp).
2.min():--
--this function is used to obtain the min value present
in a column.
syn:
min(column_name/exp).
3.sum():--
--This func.. is used to obtain the summation of all the values
present in a given column.
synx:--sum(column_name/exp).
4. avg():--
--this function is used to obtain the average of all the
values present in the column.
synx:--avg(column_name/exp).

5.count:
---it is used to count the no of records present in the result table.
--Count() is the only funct....for which we can pass *
or column name or exp as an arg..

synx:--count(*/column_name/exp).

note:--
1.for sum() and avg() function we can pass only those
column which contains number data type.
2.MRF() will ignore all the null values.
3.we cannot pass MRF() in where clause.

===========================================================================
note:-
1.For MRF we can pass only single arg i.ecolm_name/exp
2.MRF() will ignore all the null values.
3.we cannot pass MRF() in where clause.
--------------------------------------------------------------------------------------
--------------------------
1.WAQTD max sal,min sal, and total sal needed to pay for the
all the employess
2.WAQTD number of employess getting sal less than 2000 in detno 20,10
3.waqtd average sal needed to pay for employes working as "SALESMAN";
4.WAQTD no. of employees working as clerk;
5.WAQTD no.. of employees having A as their first char.. in ename;

6.WAQTD total sal needed to pay for the employees hired in the
month of FEB.
7.WAQTD no..of employees earning comm in deptno 20
8.WAQTD avg sal,total sal,number of employees and max..
sal given to employees working as PRESIDENT

9.WAQTD no of emp and total sal needed to pay for

employees who have consec.. LL in their ename.
-->

--------------------------------------------------------------------------------------
--------------
*** in case of MRF only one column name should be passed in select clasue.
==============================
waqtd name and max sal of the employees who are working as clerk.
waqtd max(sal),min(sal) and total sal needed to pay fro all employees.
===========================================
waqtd no of employees getting sal less than 2000 in deptno 20
---
waqtd average sal needed to pay all the employees.
---
WAQTD no of employees having 'A' as their first character.
Waqtd no of employees working as clerk or manager...
WAQTD total sal needed to pay employess hired in FEB... month
-->

WAQTD no of employees reporting to 7839(mgr)

WAQTD no of employees earning comm in deptno 20

-->
waqtd avg sal,total sal,number of employees and maximum sal
given to employees working as president
-->
WAQTD no of emp.. and total sal needed to pay for the employees
who have 2 consecutive L in their name.
-->

=============================================================
Group by Clause:---
--Group by clause is used to group the record based on certian
attribute.
--Group by clause executes 'ROW-BY-ROW'.
--For group by clause we can pass column_name as an arg..
--After the execution of group by clause we will get some
groups.
--Any clause which executes after group by clause will exceute
'GROUP-BY- GROUP'.
--we can pass group by clause without using where clause.

syntax:--- select group-by-expression/Group by function

from table_name
[where <Filter-Condition>]
group by col_name/expression
order by col_name/expression;
Order of execution:---
1.from clause
2.where clause :-----Row-By-Row
3.Group by clause :--Row-By-Row.
4.select clause:-----Group-by Group.
5.order by clause:---Group by group....

=============================================================
WAQTD no of employees working in each dept..except president.
-->

WAQTD total salary needed to pay to all the employees in each dept.

WAQTD no of employees working as manager in each dept..

waqtd avg sal needed to pay all the employees in each dept..
exculding the employess of deptno 10
waqtd no of employees having charcater 'A' in their
ename in each job.
waqtd no of employees and avg sal needed to pay
the employees whose sal is greater than 2000 in each dept.
watd total sal needed to pay and no of salesman in each job.
waqtd no of employees hired on the same day into the same dept.
-->

waqtd no of employees getting the same sal in same dept...

****note:--multiple argumnet can be passed in group by clause.

=======================================================================
Having clause:--
--Having clause is used to filter the groups.
--This means having clause executes group by group.
--Since having clause executes after the group by clause we
can write MRF in it as a condition.
--In having clause we can passs the expression used in
group by clause.
--Multiple condit.. can be passed in having clause.
==============
syntax:--select group-by-expression/Group by function
from table_name
[where <Filter-Condition>]
group by col_name/expression
having <group-Filter-Condition]
order by colm_name/expression.
Flow Of Exceution:--
1.From clause
2.where clause :---Row by Row
3.group by clause :---Row by Row
4.having clause :--Group by Group
5.Select clause:--Group by Group
6.order clause:--Group by Group.

============================================================================
waqtd atleast 4 employees working in each dept except president.
waqtd total sal needed to pay all the employees in each job if the
total sal of each exceeds 3450

waqtd no of employees working as manager in each dept if their

average sal is less than 2300
-->>

waqtd avg sal needed to pay all the employees in each dept
exculding the employee of deptno 20.If there are atleast
3 employees working in each dept.

waqtd no of employees of having char 'A' in their names in

each job if count of employees is equal to 2
----------------------------------------------------------------------
waqtd the hiredate and deptno if employees hired on the same
day into the same dept.

waqtd the sal and deptno if employees getting same

sal in same dept....

waqtd the hiredate which appears more than once in employee table.

waqtd the salries which are repeated or duplicate.

-->
---------------------------------------------------------------------
Constraints:--
--Constraints are the rules provided to a column to validate
a data during insertion or update
--Constraints in sql are:---
1.Not Null Constraint
2.Unique Constraint.
3.check() Constraint
4.Primary Key Constraint
5.Foreign key Constraint
1.Not null Constraint:--
--Not null Constraint is used to restrict null
values into a cloumn,in other words if a cloumn is defined as
not null then it should not accept null value.

--Which Column should be decl..as not null.

 --->The attributes or columns which is supposed to be manadatory
for each and every enitiy
then such column should be decla... as not null.
ex:--name,usn,gender etc............

2.Unique Constraint:--
--this Constraint will not allow any duplicate value for a column.
-->Which Column should be decl..as unique.
----The attributes which are supposed to be different for each
and every enitity must be defined as Unique Constraint
ex:--empid,usn,pancard etc.................

3.check() Constraint:---
--check() Constraint is used to provide additional validation
for a column.
ex:-- The phone number should conatin exactly 10 digit.

check(length(phone)=10).

4.Primary Key:---
--For each table having Primary Key is highly recom....,
which helps to access a record in a table without having
any confusion(Ambiguity)
--It is a combination of not null and unique.
--A table should have one primary key.

Candidate Key:--
--The columns which are eligi... for PK is known Candidate Key
ex:--USN+PHONE NUM.

ALternate Key:--
--The column which is eligi.. for PK but not selected as PK,
it is knwon as Alternate Key

i.e:---Candidate Key= Primary Key + ALternate Key.

5.Foregin key:---
--It is used to build a relationship between the tables.
--in a table we can have any no of foregin key..
--A table in which foreign key is present is known as child table and table to which foreign
key belongs is known as
Parent table.
--A foreign key must and should be defined as primary key in its parent table.
--A column defined as foregin key can accept the null and duplicate vlues in child table.
--A column defiend as foreign key cannot accept a value which is not present in its parent
table column.
===========================================================
***SUB-QUERY:--
--An query written inside another query is known as SUB-QUERY.
WHEN/WHY do we use sub-query::?
case 1:--whenever we have unknown values .we will use sub-query.
ex:--waqtd name of the employees who are earning more than SMITH.
select ename
from emp
where sal >(select sal
from emp
where ename='SMITH')
WATD name of the employees who are working in same dept as TURNER.

select ename from emp

where deptno =(
select deptno

from emp where ename='TURNER');

-----------------------------------------------------------------------------------
case 2:-- whenever the data to be fetched/selected and the
condition to be executed belongs to two different table,
then we need to go for sub-query.

ex:--Waqtd the dept name of jones.

select dname
from dept
where deptno=(select deptno
from emp
where ename='JONES');

notes:--
--we can pass only 1 column from inner query(because
we cannot compare with multiple values)
--Tha data types on the conditions must be
exacatly same ,irrespective of column..name

===============================================
WATQD name and sal of employees earning less than jones.

waqtd all the details of th employess working in

the same dept..as JAMES

-->
waqtd number of employess working for king.

--------------------------------------------------------------
waqtd name and hiredate of employees who are getting sal
more than SMITH but less than CLARK
-->

select ename,hiredate
from emp
where sal>(select sal from emp
where ename='SMITH')and sal<(select sal from emp where ename='CLARK');

waqtd number of employees who are hired after MARTIN and

getting sal less than KING
->
select count(*)
from emp
where hiredate > (
select hiredate from emp
where ename='MARTIN')
and sal<(select sal
 from emp
where ename='KING');

waqtd name and job of the employees who earns comm and getting
sal less than SCOTT
-->

select ename,job
from emp
where comm is not null and sal < (select sal from emp where ename='SCOTT');

WAqtd name and deptno of the employees who works in ACCOUNTING dept;

waqtd name and deptno of the employees who earn more

than WARD and works in research dept.
-->
watd name and sal of the employees who are working as
CLERK in CHICAGO
-->

select ename,sal
from emp
where job='CLERK' and deptno =(select deptno from dept where loc='CHICAGO')

watd all the details of employees who are

earning less than king and working in SMITHS dept.
-->

WAQTD details of dept if all the deptno has managers

-->

****Watd name of employees working in same job as SCOTT ,

getting sal more than ADAMS and working in dept in which MARTIN
works.

select ename
from emp
where job=(select job from emp where ename='SCOTT') and sal>
(select sal from emp where ename='ADAMS') and
deptno=(select deptno from emp where ename='MARTIN');
---------------
WAQTD details of dept if all the deptno has managers:

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

WAQTD details of dept if all the deptno has managers:

select *
from dept
where deptno =(select deptno
from emp
where job='MANAGER');

ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
========================================
Types of SUB-QUERY..
1.Single Row Subquery.
2.Multi Row Subquery.
3.Nested Subquery.
4.Correalted Subquery.

1.Single Row Subquery:--

--If the inner query returns exactly 1 record then it is knwon as Single Row Subquery.
--If it returns 1 record,we can use normal operators i.e: = ,!=,>=,<= etc..

ex:--watd name and sal of the employees who are working as CLERK in CHICAGO
select ename, sal from emp
where job='CLERK' and deptno=(select deptno from dept
where loc='CHICAGO');

2.Multi Row Subquery:--

--If the subquery returns more than 1 record then it is known as Multi Row Subquery.
--In case of Multi Row Subquery we can not use noraml operators.
--So need to use Operators Such as in,All,Any..etc.

----------------------------------------------------------------------
**3.Nested Sub-Query:--
--When we write more than one Sub-query then it is known
as Nested Sub-Query.
--we can nest upto 255 sub-queries.
WAQTD 2nd max sal

-----------------------------------------------------------------------

4.Correalted Subquery:--
--A Correalted Subquery is a sub query which uses values from outer query.

======================
**3.Nested Sub-Query:--
--When we write more than one Sub-query then it is known
as Nested Sub-Query.
--we can nest upto 255 sub-queries.

--WAQTD 2 min sal from emp;

>>--
select min(sal)
from emp
where sal>(select min(sal)
from emp);
--Waqtd 5 max sal from emp;
=====================
2.Multi Row Subquery:--
WAQTD details of dept if all the deptno has managers.
---
select *
from dept
where deptno in(select deptno
from emp
where job='MANAGER')

Get employee details working under either accounting or research.

ex:--
select *
from emp
where deptno in(select deptno
from dept
where dname in('ACCOUNTING','RESEARCH'))
Get sal,ename details of employees working under either in CHICAGO or BOSTON
--------->>>
list the emps whose job same as SMITH or ALLEN.
select job
from emp
where ename in('SMITH','ALLEN');

ANY and ALL operator::---------------------

--These operator are used in where or having clause.
--The ANY Operator returns true if any of the sub-Query
values meet the condition.
--The ALL opertaors returns true if all of the subquery
values meet the condition and if 1st cond.. is false then
it wil not check other condition's.
ANY Syntax:--
select column_name
from table_name
where column_name operator ANY
(select column_name from table_name where condition).
ALL Syntax:--
select column_name
from table_name
where column_name operator ALL
(select column_name from table_name where condition).
note:--operator must be used here are all the
comparision opertor i.e =,<=,>=,!=.....

note:--

<ANY :--Less than Maximum.

>ANY :--Greater than Minumum

=ANY :--Equivalent to IN operator

>ALL :--Greater than Maximum value

<All :--Lesser Than Minumum.

Get employee details working under either accounting or research

select *
from emp
where deptno=(
====================================

========================================
IN :-- Equal to any member in the list.

ANY :--Compare(=,<=,>=) value to each value in the list.

ALL:--Compare(=,<=,>=) value to every value in the list.

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

select *
from dept
where deptno <Any(select deptno
from emp
where job='MANAGER')
/

DEPTNO DNAME LOC

------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS

(select deptno
from emp
where job='MANAGER');

DEPTNO
------
20
 30
10

=====
<Any (20 30 10) :-- 20 10 O/P

=========================================
>Any
select *
from dept
where deptno >Any(select deptno
from emp
where job='MANAGER')
/

DEPTNO DNAME LOC

------ -------------- -------------
40 OPERATIONS BOSTON
30 SALES CHICAGO
20 RESEARCH DALLAS
=============================================================
select *
from dept
where deptno >All(select deptno
from emp
where job='MANAGER')
/

DEPTNO DNAME LOC

------ -------------- -------------
40 OPERATIONS BOSTON

=============================================================
<All

select *
from dept//10 20 30 40 < 10,20,30
where deptno <All(select deptno
from emp
where job='MANAGER')
/
======================================================================================
=====================================

Co-related SubQuery:---
--In SQL database a correalted sub-query is a subquery that uses values from the outer
query.
--Because the subquery may be evaluated once for each row processed by the outer query,it
can show as:--

i.e :--for(int i=1;i<=10;i++)//t

{
for(int j=1;j<=10;j++)
{

}
}

=============================================================
waqtd to display 4th min sal from emp;

select * from emp a

where 4 =(select count(distinct b.sal) from emp b where(a.sal>=b.sal));

=============================================================
watd 11th min sal from emp;

select * from emp a

where 11=(select count(distinct b.sal) from emp b where(a.sal>=b.sal));

=========
waqtd 3rd and 5th max sal from emp;

select * from emp a

where (select count(distinct b.sal) from emp b where(a.sal<=b.sal)) in (3,5);
=====================================================================================
Write a co-realted query to display 1st employee(Who has been hired 1st) from each dept.
select e1.ename,e1.deptno,e1.hiredate
from emp e1
where 1=(select count(distinct e2.hiredate) from emp e2 where e1.deptno=e2.deptno or
e1.hiredate==e2.hiredate)

Write a co-realted query to display details of employees

hired in same dept on same day

select e1.ename,e1.deptno,e1.hiredate
from emp e1
where 1>(select count(distinct e2.hiredate) from emp e2 where e1.deptno=e2.deptno and
e1.hiredate=e2.hiredate);

select *
from emp a
where 1=(select count(hiredate) from emp b where a.deptno=b.deptno and
a.hiredate<=b.hiredate)

select *
from emp a
where a.hiredate=(select min(hiredate) from emp b where a.deptno=b.deptno)
-----------------------------------------------------------
write a co-related subquery to display the employees who are getting min com`-`1ahj m in
each dept.

write a co-related subquery to display the employees who are getting min comm in each
dept and comm should be greater than 500
------------------------------------------------------------
select *
from emp a
where a.comm=(select min(comm) from emp b where a.deptno=b.deptno)

write a co-related subquery to display the employees who are getting min comm in each
dept and comm should be greater than 500

select *
from emp a
where a.comm=(select min(comm) from emp b where a.deptno=b.deptno and comm>500);
============================
Write a query to display the employees details who are earning less than their manager
sal.

select * from emp a

where a.sal<(select b.sal from emp b where a.mgr=b.empno);
=========================

Waqtd details of employess if they are getting sal greater than 2000,3000,4000.

note:

Or logical === Any special Operator

And Logical == All special Operator.

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

<ANY :--Less than Maximum.

>ANY :--Greater than Minumum

=ANY :--Equivalent to IN operator

>ALL :--Greater than Maximum value

<All :--Lesser Than Minumum.

Get employee details working under either accounting or research.

select *
from emp
where deptno in (select deptno from dept where dname in('ACCOUNTING','RESEARCH'))

=============================
waqtd to display all the details of an employees addition we need to display deptno column
again.

================================================
In-Line Sub-query:--

--If the subqueries are written in from clause then it is known as inline subquery.
synatx:--
select column_Name/*
from(Inner Query To be executed)

select *
from (select sal,comm,deptno from emp);

======================================================
Pseudo Column:--
--Pseudo Column are the columns which are present in each and every database object in
the
RDBMS but it is not visible until we explictily display these column.
Rownum::-
--Rownum is a column with sequence of numbers given to every record in the table from
1 to n int value.
--using Rownum we can display first or all the record in the table.

ex:--
Display 1st record from emp table;

select *
from emp
where rownum=1

Display the first 10 reecord from emp.

select *
from emp
where rownum<=10;
==========================
select *
from emp
where rownum not in(3,4,5)//it returns 1 and 2nd records.
/

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

==========================================
Rowid:--
--Rowid is a unique random set of characters,or it is a address given for each and every
record.
--Using rowid we can display first,intermediate or all the record from the table

ex:--
SQL> select rowid
2 from emp;

ROWID
------------------
AAAMfPAAEAAAAAgAAA
AAAMfPAAEAAAAAgAAB
AAAMfPAAEAAAAAgAAC
AAAMfPAAEAAAAAgAAD
AAAMfPAAEAAAAAgAAE
AAAMfPAAEAAAAAgAAF
AAAMfPAAEAAAAAgAAG
AAAMfPAAEAAAAAgAAH
AAAMfPAAEAAAAAgAAI
AAAMfPAAEAAAAAgAAJ
AAAMfPAAEAAAAAgAAK
AAAMfPAAEAAAAAgAAL
AAAMfPAAEAAAAAgAAM
AAAMfPAAEAAAAAgAAN
=====================================================
Display the 12th record from emp table.
=====================================================
Write a inline sub-query to display top 25% sal employee records.

select *
from (select *
from emp
order by sal) where rownum <=(select round(count(*)/4) from emp)

Write a inline sub-query to display least 25% sal employee records.

select *
from (select *
from emp
order by sal) where rownum <=(select round(count(*)/4) from emp);

****display middle 50% sal employees record.

===========================================================================
Dis-davantage Of Sub-Query:---
--The query becomes lengthy and complicated.
--in case of Sub query first inner query executes
follwed by outer query,so if we are going to write
multiple inner query the time complexity of Queries
will increase which leads in decrease in performance.
--By using Sub-Query data can be fetched from
one table at a time.
===========================================================================
Joins:--
--The retrival of data from multiple table simula.. is known as JOINS.
--Types Of Joins:--
1.Cartesian join/Cross Join
2.Inner join/Equi join
3.Outer join/Non-Equi join
a.Left outer join
b.Right outer join
c.Full outer join
4.Natural Join
5.Self join
=================================

1.Cartesian join/Cross Join:--

--In cross join record from table1 are merged
with all the records
of table2(here we are going to get o/p as
Cross Product of table1,table2.....table-n)
--In this type of joins we will get matching
records as well as un-matching record.
--To apply Cartesian join/Cross Join
relation(foregin key) b/w 2 tables are not mandatory.
syntax:--
ANSI::(American National standards institute):--
select *
from table_name1 cross join table_name2 cross join.....table_name..n;
Oracle:-
select *
from table_name1,table_name2,.....table_name..n

================================
note:
--In industry we are not using Cartesian join/Cross
Join becuase it returns unmatching records too.
--We Will use cross join only if there is no
relaio....b/w the tables.
======================================================================================
===============
2.Inner join:--
--This join will return only the matching records
from mutliple the table,to filter un-matching
record here we can use where clause.
--To use Inner join common column(Foregin key)
should be their.
Syntax:--
ANSI :--
select *
from table_name1 inner join table_name2 inner join table_name..n
on<Join_condition>
Oracle:--
select *
from table_name1,table_name2,table_name..n
where<Join_condition>

=========================================================
WAQTD ename,sal,dname,deptno for all the meployees.

select ename,sal,dname,deptno
from emp,dept
where emp.deptno=dept.deptno;
select ename,sal,dname,deptno
*
ERROR at line 1:
ORA-00918: column ambiguously defined

-----------------------------------------
select ename,dname,sal,e.deptno,d.deptno
from emp e,dept d
where e.deptno=d.deptno

select ename,dname,sal,dept.deptno
from emp ,dept
where emp.deptno=dept.deptno

-------------------------
=========
This column ambiguously defined can be
prevented by using 2 ways:

1.By providing table alisa.. name

--->
select ename,sal,dname,e.deptno,d.deptno
from emp e,dept d
where e.deptno=d.deptno

2.by using table_name.column_name for the column

which is common b/w the tables.
--->
select ename,sal,dname,emp.deptno
from emp inner join dept
on emp.deptno=dept.deptno

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

WAQTD name of the employees along with his location for

all the employes.

WAQTD name of employees and his deptname if the

employees works as SALESMAN.
-->
WAQTD dname and annual sal for employees who
earns more than 2450.
-->
WAQTD dname,hiredate of all the employees
who were hired during 81.
-->>

WAQTD name and dname for the employees

if name of employees job and dname starts
with the same character 'S'.
-->

WAQTD ename,sal,dname and comm for all the employees

who earns commi..
WAQTD dname,sal and sal with 25% hike if the employees
salary is less than 2500
and works as CLERK in NEW YORK.

WAQTD ename,dname for all the eployees if they are

working as MANAGER or CLERK in ACCOUNTING or RESEARCH dept
with sal more than jones.
-->select ename,dname
from emp,dept
where emp.deptno=dept.deptno and job in('MANAGER','CLERK')
and dname in('ACCOUNTING','RESEARCH') and sal >(select sal
from emp where ename='JONES');

----------------------------------------------------
WAQTD employees name,dept name if the employee gets max
sal
-->

WAQTD name,dname of SMITHS manager.

-->
------------------------------------------------

=======================================================
3. OUTER JOIN:--(NON-EQUI JOIN)
1.Left outer join::--
--Left outer join is used to obtain unmatch records
of left table along with matched record.
(But from right table Only matching record will
be fetched).

synatx:--

ANSI:-
select *
from table_name1 left join table_name2
on<Join_condiiton>

Oracle:--
select *
 from table_name1,table_name2
where table_name1.col_name=table_name2.col_name(+);
========================
2.Right Outer Join:--
--Right outer join is used to obtain unmatch records
from Right table along with matched record.
(But from Left table Only matching record will
be fetched).
synatx:--

ANSI:-
select *
from table_name1 right join table_name2
on<Join_condiiton>

Oracle:--
select *
from table_name1,table_name2
where table_name1.col_name(+)=table_name2.col_name;
-------------------------------------------------
3.Full outer join:--
--Full outer join is used to obtain unmatching
record records from both the tables.
syntx:--

ANSI :-
select *
from table_name1 full join table_name2.....
on<JOIN_COND..>

Oracle:-- No syntax

-----------
SQL> select *
2 from student full join department
3 on student.branchid=department.branchid;

BRANCHID SNAME GUIDE BRANC BRANCHID GUIDE

-------- --------------- --------------- ----- ---------- ---------------
10 DINGA PUSPHA CSE 10 PUSHPA
40 CHINKI NISHA MEC 40 NISHA
30 DINGI
90 MINKI
ECE 20 REVITHA
EEE 50 PINKI
CIVIL 60 SAHANA
===============================================================================
Student Guide
=======================
SQL> select *
2 from student;

BRANCHID SNAME GUIDE

---------- --------------- ---------------
10 DINGA PUSPHA
30 DINGI
40 CHINKI NISHA
90 MINKI
=======
1 select *
2* from department
SQL> /

BRANC BRANCHID GUIDE

----- ---------- ---------------
ECE 20 REVITHA
CSE 10 PUSHPA
MEC 40 NISHA
EEE 50 PINKI
CIVIL 60 SAHANA

===
WAQTD only the name of student who got their guide from student table
and even display the name of all the guides

select sname,department.guide
from student right join department
on student.branchid=department.branchid
o/p:--SNAME GUIDE
--------------- -----------
DINGA PUSHPA
CHINKI NISHA
PINKI
SAHANA
REVITHA

WAQTD name of student who does not belong to any branch and display all the guide names.

=======================
CROSS JOIN:---
-->Cross Product:---Matching and unmatching record
-->Relationshi... b/w table are not required.

INNER JOIN:----
-->Matching Records
-->Relationshi... b/w tables are required.

OUTER JOIN:--

-->Relationshi... b/w tables are required.

LOJ :-- left table: Matching and Unmatching record

Right Table:Matching Records

ROJ:-- left table: Matching

Right Table:Matching Record and Unmatching records

FOJ:--- it will return unmatching record from both the table.

=========================
Natural Join:--
--Natural join is similar to inner join,but we wont
write any join condition.
--It automatically combines the common column,
returns inner join output
else(if there is no common column) then it
will return Cross join output.

syntax:--
Ansi:-
select *
from table_name1 natural join table_name2;

Oracle:-- there is not oracle sntax for Natural join.

 ex:--select *
from emp natural join dept;

========================
WAQTD name of employees and department name if the
employees names first character
is 'A' or 'S' and employees should be
earning more than 'SMITH' and also he
has to hired before the last employee and he may
or might be reporting to KING or JONES

=======================
*****inner Join::-
===========
*****Self Join::--
--Joining the same two tables is known as self join
--It is used to display the data present in same table
but different record.
syntax:--

ANSI:--
select */column_Name/ex..
from table_name1 join table_name1.......
on<Join_Condition>

Oracle:--
select */column_Name/ex..
from table_name1,table_name1.......
where<Join_Condition>

--------------------------------------------------------------------------------------
-------
WAQTD the name of employee and their manager name.
--
select e1.ename EMPNAME,e2.ename MGRNAME
from emp e1 join emp e2
on e1.mgr=e2.empno;

------------------------------------------------
WAQTD the manager name of SMITH.
---------------------
WAQTD all the details of employees who are working under manager 'KING'.

select e1.ename EMPNAME

from emp e1 join emp e2
on e1.mgr=e2.empno
where e2.ename='FORD';

-----------------------------------------------------------
WAQTD name of the employees and their managers name if the employees are working as 'CLERK';
---------------------------------------------
WAQTD name of the employees and manager desig.. if manager works in deptno 10 or 20.
--------------------------------------------
WAQTD all the details of employees if employees who are
getting more sal than their respective manager.
---------------------------------------------
WAQTD name of the employees and manger's name if sal of employees and manager both are
earning more than 2300.
-------------------------------------------
WAQTD the employees name,mgr name,managers manager name,managers mangers manager name.
-->
===================================================
Assignment:--
1.WAQTD emp name and manager hiredate if employee hired before 1982
2.WAQD emp name and managers commisiison if employee works
as salesman and manager works in deptno 30
3.WAQTD emp name,hiredate,manager name and hiredate if manager has been hired before
employee
4.WAQTD empname and mgr name if both are working in same job.
5.WAQTD empname and mgrname along with their annual salary if the employees is working in
deptno 10,20 and mgr sal is greater than employee sal.
6.WAQTD emp name and mgr name if mgr is working as actual manager.
====================================================
7.List the emps whose mgr name is jones and also list their manager name.
8. List the emps whose sal is equal to the average of max and minimum
9. List the managers whose sal is more than his employess avg salary.
10. List the emp whose sal<his manager but more than any other manager.
11.List the employee names and his average salary department wise.
12. Find out least 5 earners of the company.
13. List the Enames who are retiring after 31-Dec-89 the max Job period is 20Y.
14. List those Emps whose Salary is odd value.
15. List the emps whose first 2 chars from Hiredate=last 2 characters of Salary.
16. List first 50% of chars of Ename in Lower Case and remaining are upper Case.
17. List the Dname whose No. of Emps is =to number of chars in the Dname.
18. List THE Name of dept where highest no.of emps are working.
19. List the name of the dept where more than average no. of emps are working.
20. List the Managers name who is having max no.of emps working under him.
21. List the emps whose Mgr name is 慗 ones?and also with his Manager name.
22. Find out how may Managers are their in the company.
23. Check whether all the emps numbers are indeed unique.
24. Find out the emps who joined in the company before their Managers.
25. List all the emps by name and number along with their Manager 抯 name and number. Also
List KING who has no 慚 anager?
26. List the employee name,Salary and Deptno for each employee who earns a salary greater
than the average for their department order by Deptno.
27. List the Deptno where there are no emps.
28. List the Name, Job and Salary of the emps who are not belonging to the department 10
but who have the same job and Salary as the emps of dept 10.
29. List the Deptno, Name, Job, Salary and Sal+Comm of the SALESMAN who are earning maximum
salary and commission in descending order.
30. List the Deptno and their average salaries for dept with the average salary less than
the averages for all department
31. List out the Name, Job, Salary of the emps in the department with the highest average
salary.
32. List the empno,ename,annsal,dailysal of all the salesmen in the asc ann sal
33. List the emps whose exp is more than 10 years.
34. List the emp who are clerks who have exp more than 8ys.
35. List the emps along with exp of those working under the mgr whose number is starting
with 7 but should not have a 9 joined before 1983.
36. List the emps who are working for dept 10 or 20 with desgs as clerk or analyst with
a sal is either 3 or 4 digits with an exp>8ys but does not belong to mons of mar,apr,sep
and working for mgrs &no is not ending with 88 and 56.
37. List the emps who are belonging dallas or Chicago with the grade same as adams or exp
more than smith.
38. List the details of most recently hired emp of dept 30.
39. List the highest paid emp working under king.
40. List the emps Whose 10% of Salary is equal to year of joining.

======================================================================================
===========
DDL:--(Data Definition Language)--
-This Query Lanaguage is used to define the properties of table.
-The stmt of DDL are:--
1.create
2.rename
3.alter
4.Truncate
5.drop

-----------------------
1.create :--This stmt is used to create a table in DATABASE.
syntax:--
create table table_name
(Column_name-1 data_type not null/null,
Column_name-2 data_type not null/null,
..
..
..
Column_name-n data_type not null/null,
Constraints constraints_ref_name primary key(Column_name),
Constraints constraints_ref_name foregin key(Column_name),
Constraints constraints_ref_name check(condition),
Constraints constraints_ref_name unique(Column_name),
References parent_table_name(Column_name)
);
------------------------------------------------
create table STUDENT
(
USN varchar(15) not null,
NAME varchar(10) not null,
MARKS number(6,3) not null,
PHONENUM number(10) not null,
EMAIL varchar(20) null,
constraints u_sn primary key(USN),
constraints p_hn check(length(PHONENUM)=10)
);
---------------------
select *
from STUDENT
note:will get no row selected becuase there is no record prsent in the table.
=============================================================================
Query to clone the data and column name of already created table.

syntax:--
create table new_TableName as(select * from table_name);

-------------------------------------------
2.Rename:--
--This Stmt is used to change the name of table.

syntax:--
rename current_table_name to new_table_name;
-------------------------------------------
3.Alter:--
----
a. To add A column in exis..table:--
syntax:-- alter table table_name
add column_name data_type[not null/null];
ex:--
alter table student
add BRANCHNAME varchar(10) not null;
--------------
b.To drop/remove a column from exis..table:--

syntax:--
alter table table_name
drop column column_name;
ex:--
alter table student
drop column BRANCHNAME;
----------------
c.To change the data type:--

syntax:-
alter table table_name
modify column_name new_data;
ex:--
alter table student
modify EMAIL number(10);
-----------------
d.To change the name of column:--

syntax:--
alter table table_name
rename column current_Column_name to new_column_name
ex:--
alter table student
rename column NAME to SNAME;
-----------------
e.To ADD the constraints:--
 syntax:--

alter table table_name

add constraints cont_ref_name primary key(Column_name);
ex:--
alter table student
add constraints j_p unique(PHONENUM);

f.TO drop/enable/disable the constraints:--

alter table table_name
drop/enable/disable constraint const_ref;

----------------
4.Turncate:--
--It is used to delete all the records without affecting the table structure.

syntax: -
truncate table table_name;

ex:--truncate table dinga;

5.Drop:--
--This stmt is used to delete all records and even table structure from database.
synatx:--
drop table table_name;

ex:--drop table dingi;

Note:--The table deleted from database wont be deleted perman...,it will be present in
recycle bin
of Data Base.

a.To recover the table From recycle Bin:--

syntax:--
Flashback table table_name
to before drop;

ex:-- Flashback table dingi

to before drop;

b.To delete the table Parm......

--1st drop the table From DB.

ex:--drop table dingi;

--Then to delete the table from recycle bin make use of command:---
 synatx:-- purge table table_name;

ex:--purge table dingi;

----------------------------------------------------
DML(Data Manipulation Lanaguage):-
1.insert.
2.update.
3.delete.

1.insert:--
--This stmt is used to insert the record in the table.

syntax 1:--

insert into table_name values(v1,v2,v3.....vn);

ex:-- insert into student values('1st','RAM',2.6,1123456789,'a@2123');

--This syntax is used whenever we have to insert miniu.. number of record in table.
(Because remeb.. the colu_name and data types is little difficult)..

=====================
syntax 2:--

insert into table_name (column_name1,column_name2....column_name.n)

values(v1,v2,v3.....vn);

ex:-insert into student (email,usn,phonenum,marks,name) values('jsp@123','1st13ec99',

1234567899,63.36,'DINGA');
===================
syntax 3:--
--This syntax is used to copy the records are another table to current table;

Rules:-
1.Both the table should have same number of column.
2.Both the columns table should have same data type;

syntax:--

insert into table_name

select statement;

ex:--
insert into DUPSTUDENT
select * from student;

-->
insert into DUPSTUDENT
 select * from student
where usn='1st';
=====================================================
2.Update:--

--Used to update the specific data into the table.

sntax:--
update table_name
set colu_name1=v1,colu_name2=v2.......
where <filter-condition>;

ex:--
update student
set PHONENUM=8877599869
where usn='1st13ec123';

=====================================================
3.Delete stmt:--
--This stmt is used to delete the specific record from the table.

syntax:--
delete
from table_name
where<Filter-Condition>

ex:--
delete from student
where name='RAM';
====================================
TCL:--
--TCL commands is used to manage the tran... in the database.
--These are used to manage the changes made to the data in table by DML stmts.

1.Commit.
2.rollback.
3.savepoint.

1.Commit:--
--This stmt is used to save the changeg perman...done by DML commands.
--Commit stmt..will always be enabled in DBMS.

syntax:-commit;
ex:--

SQL> show autocommit;

autocommit IMMEDIATE
SQL> set autocommit off;
SQL> set autocommit on;
-----------------
2.savepoint:--
--This stmt is used to mark the transaction.
syntax:-- savepoint savepoint_name;

3.rollback:--
--this stmt is used to undo the operation which has been saved in database.
syntax:--roolback to savepoint_name;
---------------------------------------------------------------------------------
ex:--SQL> savepoint a;

Savepoint created.

SQL> insert into student values('567','DINGI',36.36,1234567897,'di@123',98);

1 row created.

SQL> select * from student;

USN NAME MARKS PHONENUM EMAIL AGE

---------- ---------- ---------- ---------- -------------------- ----------
1st13ec125 AVINASH 96.36 8298212510 aci@123 28
1st13ec126 AKHILESH 69.36 8877599869 aki@123 30
1234 DINGA 96.36 9066581587 a@123 32
567 DINGI 36.36 1234567897 di@123 98

SQL> rollback to a;

Rollback complete.

SQL> select * from student;

USN NAME MARKS PHONENUM EMAIL AGE

---------- ---------- ---------- ---------- -------------------- ----------
1st13ec125 AVINASH 96.36 8298212510 aci@123 28
1st13ec126 AKHILESH 69.36 8877599869 aki@123 30
1234 DINGA 96.36 9066581587 a@123 32
======================================================================================
=======
DCL:(DATA CONTROL LANGUAGE):--

Grant:--
--It is used to give permission to other user to access the table created by them.
syntax:--
grant sql_stmt(select/insert...)
on table_name
to user_name;
Revoke:--
--It is used to get back the permission from another user.

syntax:--
revoke sql_stmt(select/insert...)
on table_name
to user_name;
===================================================================
ex:--
----------------------
GRANT:--
user:--scott

grant select
on emp
to hr
Grant succeeded.

user:---HR

select *
from scott.emp
----------------------------------
Revoke:--
SQL> show user;
USER is "HR"
SQL> connect scott
Enter password: *****
Connected.
SQL> show user;
USER is "SCOTT"
SQL> revoke select
2 on emp
3 from hr;
----Revoke succeeded.

------------------------------------
SQL> connect hr;
Enter password: *****
Connected.
SQL> show user;
USER is "HR"
SQL> select *
2 from scott.emp;
from scott.emp
*
ERROR at line 2:
ORA-01031: insufficient privileges
--------------------------------------------------------------------------------------
----------------------------------------
Set oprators in SQL:--
--SQl supports few set operators,which is used to get the meaningful result from table.
--4 types of SET operator:--
1.Union.
2.Union All.
3.Intersect
4.Minus.

1.Union:--

-->Union is used to combine the result of two or more select stmt.However it will eleminate
duplicate rows
in result table.

ex:--
A ={1,2,3,4,5};
B ={4,5,6,7};

A U B :--{1,2,3,4,5,6,7};

2.Union All:--
--This operator is same as UNION ,but it also inculeds the duplicate rows.

3.Intersect:--

--Intersect operator is used to combine two select stmt,but it only returns the record
which are common for both select stmt.

note:--MYSQL does not supports the Intersect operator.

4.Minus:--

--The minus opertor cobines result of two select stmt and returns only the non-duplicate
record from 1st select stmt.

note:--->In case of set operators no of columns and data types must be same for both tables.
----------------------------------------------------------------
ex:--
union:--
select * from emp
union
select * from emp2;
Union All:-
select * from emp
union all
select * from emp2;
Intersect:-
 select * from emp
intersect
select * from emp2;
Minus:--
select * from emp2
minus
select * from emp;
======================================================================================
==================
Attributes:--
-->It will define the characte...of DB or attribute refers to database component.
1.Key Attribute/Candidate key
2.Non-Key Attribute.
3.Prime Key Attribute.
4.Non-Prime Key Attribute.
5.Composite key Attribute.
6.Super key Attribute.
7.Foreign key Attribute.
=============================================
1.Key Attribute:--
--It is an Attribute using which we can identify a record Uniquely from the table.
It is also known as Candidate key.

ex:--PhoneNumber,AadharCard,voterId etc..

2.Non-Key Attribute:--
--All the other Attribute except key Attribute is known as Non-Key Attribute.

ex:--Name,DOB,Gender,Religion etc...

3.Prime Key Attribute:-

-- Prime Key Attribute is the attribute among the Key Attributes which is choosen to be
the main attribute to
identify the table.
--In a table we can have only one Prime Key Attribute.

ex:--Phone number

4.Non-Prime Key Attribute:--

--All the attributes except prime key attribute are known as Non-Prime Key Attribute.

5.Composite key Attribute:-

--It is a combination of 2 or more non-key atribute using which we can uniquely identify
the record from table.
ex:-- Name,YOP,Height etc.....

6.Super key Attribute:--

--It is the set of all the key attributes.
--We can use this to set a primary key attribute,we do not need to search which one need
to make primary
key by looking at the table.
ex:--Phone number,Voter id etc..

7.Foreign key Attribute:--

--Foreign key Attribute is an attribute which behaves as an attribute to another entity
to represent the
realtionship.
======================================================================================
===================
Functional Depedency:---

--The functional Depedency is a relationship that exists between two attributes.

--It typically exists between the prime key Attribute and non-key Attribute within a table.

EMPNO(Prime Key Attribute) -----------> ENAME(non-key Attribute)

-The left side FD is known as determinant(it helps user of fetch the unique record)
,right side of arrow mark we can say as Dependent.
---------------------------------------------------------------------
Types of Functional Depedencies are:--
1.Total Functional Depedency
2.Partial Functional Depedency
3.Transitive Functional Depedency.
-----------------
1.Total Functional Depedency:--
--If all the attributes of a relation are determined by single key attribute then it
is known as Total Functional Depedency

ex:--Let us consider ,a relation with the 4 attributes A,B,C,D in which A is key attribute.

R ->{A,B,C,D}
where A is Key attribute.

A -->B
A -->C
A -->D

i.e:::: A -->{B,C,D};

A is the determinant and BCD are the depedencies.

2.Partial Functional Depedency:--

--A relation is knwon as Partial Functional Depedency if
1.It consists of Composite Key attribute.
2.There exists a depedency such that an attribute can be determined
by another attribute which is a
part of Composite key attribute.
 ex:-- let us consider a relation R which consists of 4 attributes (A,B,C,D)
in which A and B are Composite key attribute.

R :--- {(A,B),C,D}

(A,B) ----> D

B ----> C

A ---->(C,D).

Name Marks

Ram 68.36

Ram 89.36

Sohan 32.36
-----------------------------------------------------------
3.Transitive Functional Depedency:--
--There exists a depedency that an attribute is determined by a non-key attributes
which is determined by a key attribute,this type of depedency is known as Transitive
Functional. Depedency

Let us consider the relation which consists of 4 attributes.

A ,B,C and D

R3 = {(A),B,C,D}

A -> B

D -> C

A -> D

--------------------------------------------------------------------------------------
------------
Redundency:--
--Repeatation of unwanted data.

Anamoly:---These are the side effects due to DML operations.

=============================
Normalization:--
--Normalization is the process of reducing the table into smaller table in order to remove
the
Redundency and anamolies by identifying the depedncies.
 --Decomposing a lager table into its normal form is known as Normalization.

Normal Form:--
--A table without redundencies and anamolies is knwon as Normal Form.
--Levels of Normal Forms are:--

1.First Normal Form (1NF)

2.Second Normal Form (2NF)
3.Third Normal Form (3NF)
4.Boyce-Codd Normal form(BCNF)///
..............
..............
8.
note:--A table is said to be normalized table if we have recduced the table to 3NF
-------------------------------------------------------------------------
1.First Normal Form (1NF):--
--A table is said to in first normal form if it satifies the following condition:--

**1.The table should consits of single value data in a single cell(i.e one particular cell
should have 1 value only).
2.A table should not have any duplicate value.

======================================================================================
=======================
2. 2NF

--the table should be in 1NF

--The table should not have partial Functional Depe.....

3. 3NF
--the table should be in 2NF
--there should be any transitive Depe.....

transitive Depe...::--

Attribute ---->Non-Key ----> Key Attibute

===========================================
ex:--
800
1600
1250
2975
2850
2450
3000
5000
1500
1100
950
1300
-
============================================================================