SQL

SQL stands for Structural Query Language

Three Tire App

Front end – Ex: Sign up page and its details

Middleware – Program

Back end – Database

Data

Data is a raw fact which describes the attributes of an entity.

Database

It is a place where data will be stored in a systematic and organized manner.

Basic operations performed on the Database are

1. Create / Insert

2. Read / Retrieve

3. Update / Modify

4. Delete / Drop

Generally known as “CURD” operations.

Database Management System (DBMS)

DBMS is software to Maintain and Manage database

Two important factors are

1. Security

2. Authorization

We use Query language to communicate.

Types of DBMSND

BMS - Network
HDBMS - Hierarchy
ODBMS – Object orient
RDBMS – Relational

Relational Database Management System (RDBMS)

It is a type of DBMS software where data will be stored in form of Tables

Two important factors are

1. Security

2. Authorization

We use Structured Query language (SQL) to communicate.

Relational Model

It is a concept designed by Data scientist “E.F.CODD”.

In the relational model, we store the data in the form of Tables

DBMS software which follows the Relational Model becomes Relational DBMS (RDBMS)

Rules of E.F.CODD

1. The data stored in the cell must be a single value data

2. In RDBMS we store everything in the form of tables Including Metadata

(The details about the data is Mata Data)

3. According to E.F.CODD we can store data in Multiple tables, if needed we can establish the connection between two tables using key attributes

4. We can validate the data entered into the table in two steps

a) By Assigning Datatypes

b) By Assigning Constraints

Note

Here datatypes are mandatory whereas constraints are optional.

Statements in SQL

DDL – Data Definition Language

DML – Data Manipulation Language

TCL – Transaction Control Language

DCL – Data Control Language

DQL – Data Query Language

Data Definition Language (DDL)

1. Create

2. Rename

3. Alter

4. Truncate

5. Drop

Data Manipulation Language (DML)

1. Insert

2. Update

3. Delete

Transaction Control Language (TCL)

1. Savepoint

2. Roll Back

3. Commit

Data Control Language (DCL)

1. Great

2. Revoke

Data Query Language (DQL)

1. Select

2. Projection

3. Selection

4. Joints

Datatypes

Datatypes are used to determine what types or kinds of data will be stored in a particular memory location

Datatypes in SQL

1. CHAR

2. VARCHAR/ VARCHAR2

3. NUMBER

4. DATE

5. LARGE OBJECT

a. Character large object (CLOB)

b. Binary large object (BLOB)

Note

SQL is not case-sensitive.

CHAR

Char datatype can accept characters such as

‘A-Z’

‘a – z’

‘0 – 9’

Special characters (#, *. $, -----)

ASCII

ASCII stands for American Standard Code for Information Interchange

Alphabet	ASCII value
A	65
Z	90
a	97
z	122

Syntax

CHAR(SIZE)

Size

It is used to determine a number of characters that we can store.

Note:

Whenever we mention char datatype we have to mention the size for it
The max size that we can store is 2000
It is a type of “Fixed Length Memory Allocation”.

Char(10)

Used memory unused memory

Char(2000)

Drawback

Memory wastage, in char unused memory will be wasted

VARCHAR

Varchar datatypes can accept characters such as

‘A-Z’

‘a – z’

‘0 – 9’

Special characters (#, *. $, -----)

Syntax

VARCHAR(SIZE)

Size

It is used to determine number of characters that we can store.

Note

Whenever we mention varchar datatype we have to mention the size for it.
The max size that we can store is 2000
It is a type of “Variable Length Memory Allocation”
There is No Wastage of memory is varchar..

Ex:

1. Varchar(10)

Used memory Free memory

2) Varchar(2000)

In varchar, only used memory for the given size is used other free memory is sent to the database.

VARCHAR2

Varchar2 is the updated version of Varchar

Size is updated from 2000 to 4000

Syntax

VARCHAR2(SIZE)

The max size we can store is 4000

Difference between CHAR and VARCHAR

CHAR	VARCHAR
Char is a datatype used to store characters such as A-Z, a-z, 0-9, special characters	Varchar is a datatype used to store characters such as A-Z, a-z, 0-9, special characters
Fixed Length Memory Allocation	Variable Length Memory Allocation
Syntax: CHAR(SIZE)	Syntax: VARCHAR(SIZE)
Size – 2000	Size – 2000

Difference between Varchar and Varchar2

Varchar	Varchar2
It is a datatype used to store characters	It is an updated version of Varchar
Size – 2000	Size – 4000
Syntax: VARCHAR(SIZE)	Syntax: VARCHAR2(SIZE)

Varchar is automatically updated to Varchar2

Number

This data type is used to store Numerical values.

It can accept two Arguments

a) Precision

b) Scale

Syntax

NUMBER(PRECISION,[SCAKE])

Example

P>S Number(5) ±99999

Number(3,0) ±999

Number(5,2) ±999.99

Number(8,3) ±99999.999

P=S Number(4,4) ±0.9999

P<S Number(2,5) ±0.00099

Number(3,9) ±0.000000999

Number(38,127) ±0.0000

Precision is a Numerical value

Scale is a Decimal value

Precision

It is used to determine the digits we are going to store in a Numerical place.

Scale

It is used to determine the number of digits we are going to store in a Decimal place within the precision. The Max Precision we can store is 38

The Max Scale we can store is 127

Question and Answers

Number(2,8) ±0.00000099

Number(7,3) ±9999.999

Number(8,2) ±999999.99

Number(5,5) ±0.55555

Number(4,8) ±0.00009999

Number(5,8) ±0.00099999

Number(8,4) ±9999.9999

Date

Syntax

DATE

The two oracle-specified date formats are

‘DD – MOM – YY’
‘DD – MOM – YYYY’

Date should always be enclosed within single quotes

Example

1) ‘DD-MOM-YY’
’26-OCT-21’

2) ‘DD-MOM-YYYY’

’15-AUG-1947’

Large Object

CLOB (Character Large Object)

This is used to store the characters up to 4GB of size

Syntax: CLOB

BLOB (Binary Large Object)

This is used to store Binary numbers of images, videos, files etc., up to 4GB in size

Syntax: BLOB

Photo	EID	EName	Ph.no	Sal	HIREDATE
BLOB	NUMBER(3)	VARCHAR(25)	NUMBER(7,4)	NUMBER(7,2)	DATE
	1	Adeep	9245277891	40,000.00	01-Jan-21
	2	Swathi	7657267582	55000.00	01-Dec-20
	3	Manish	77935	_______	01-Feb-20
	3	UMA	__________	-60000.00	01-Sep-21

Constraints

Constraints are the conditions that are assigned to a particular column to validate the data.

Types of constraints

1. Unique

2. Not Null

3. Check

4. Primary key

5. Foreign key

Unique

Unique is a constraint which is assigned to a particular column which cannot accept Repeated or Duplicate values.

Not Null

Not Null is a constraint which is assigned to a particular column which cannot be Null or which are mandatory

Check

Check is a constraint which is assigned to a particular column for extra validation.

Check constraint is assigned with a condition if the condition is true. The value gets accepted, else rejected.

Ex:

Check(length(phno =10))

Check(sal > 0)

Note:

Null – It is a keyword which represents an empty cell. Any operations performed with Null, the result will be null.

Primary key

Primary key is a constraint which is used to assign to a column to identify a record uniquely from the table.

Characteristics of Primary key

1. We can have only one primary key in a table

2. It cannot accept repeated or duplicate values

3. It cannot accept null

4. It is a combination of unique and not null

5. Primary key is not mandatory but recommended to have one in the table.

Foreign key

It is a constraint which is used to establish the connection between two tables.

Characteristics of Foreign Key

1. We can have ‘n’ number of foreign key in a table

2. It can accept repeated or Duplicate values

3. It can accept Null

4. It is not a combination of Unique and Not Null

5. It is present in the child table but actually belongs to the Parent table

6. It is also referred as “Referential Integrity Constraint”.

Note

Only primary key can travel to another table, when it travels it becomes Foreign key.

Data Query Language (DQL)

This statement is used to retrieve the data from the database.

Types of DQL statements

1. Select

2. Projection

3. Selection

4. Joints

Select

This statement is used to retrieve the data from the database and display it

Projection

This statement is used to retrieve the data from the database by selecting only the column

All the values in the column will be selected by default

Selection

This statement is used to retrieve the data from the database by selecting both columns as well as records

Joins

This statement is used to retrieve the data from multiple tables simultaneously.

Projection

Note

1. From clause starts the execution.

2. For From clause we can pass the table name as an argument

3. The job of from clause is to go to the database and search for the table and put the table under execution.

4. Select clause executes after the execution of From clause.

5. For selection clause we can pass Asterisk (*), column name and expression as an argument.

6. The job of select clause is to go to the table which is under execution and select the data and

display it.

7. Select clause is responsible for the result table.

Example table

Student O/P of From O/P of Select

SID	SName	Branch	Per	SName
1	Sai	Mech	65	Sai
2	Jerry	CSE	80	Jerry
3	Tom	EEE	80	Tom
4	Tim	ECE	70	Tim
5	Varun	Mech	55	Varun

1) WAQTD Name and Branch of all the students

Select SName, Branch

From Student;

Syntax:

Select */[Distinct] column-Name/ Expression [Alias]

Form Table-Name;

Order of Execution

1- From

2- Select

Expression

A statement which gives us result is known as Expression.
Expression consists of two types

1) Operand
2) Operators (+, - ,* ,/)

Operand consists of two types

1) Column Name
2) Literals (Direct Values)

Literals are of three types

1)      Number Literals
2)      Character Literals
3)      Date Literals

Character literal and date literal should be enclosed with in single quotes.

Alias

·         Alias is an alternative name given to a column or an expression in the result table.
·         Alias name can be used with or without using “AS” Keyword
·         Alias name should be a single word or a string enclosed with double quotes.
·         Alias is not mandatory but recommended to provide.

Examples

1. WAQTD Sal as salary, hire date as date of joining?

Select sal as SALARY, hiredate “Date of Joining”

From emp;

2. WAQTD Name, sal of the employee along with their Annual salary?

Select ename, sal, sal*12 ANNUALSAL

From emp;

3. WAQTD ename and job for all the employees with their half term salary?

Select ename, job, sal*6 “Half Term Salary”

From emp;

Distinct

To remove repeated values or Duplicate values in the result table we use distinct clause.

·         For Distinct clause we can pass column name or an expression as an argument.
·         Distinct clause should be used as the first argument in the select clause
·         We can pass multiple columns for distinct clause.
·         It removes the combination of duplicates form all the columns.

Ex:

Student table

SID

SNAME

BRANCH

PER

1

A

EC

75

2

B

CS

60

3

C

TC

80

4

D

CS

60

5

A

EC

75

6

E

ME

80

Questions

1. WAQTD percentage of all the students?
                Select Per
                From student;
2. WAQTD different percentage from student table?
                Select distinct per
From student;

Selection

Where clause

This is used to filter the records.

Note

·         For where clause we can pass filter condition as an argument.
·         Where clause executes Row-by-Row
·         Where clause after the execution of from clause.
·         We can pass multiple condition for where clause using logical operators.

Syntax

Select * / [Distinct] Column _Name/ Expression [ALIAD]
From Table _Name
Where <filter – condition>

Order of Execution

1) From
2) Where

3) Select

Examples

1.WAQTD ename who are working in dept 20?

Select ename

From emp

Where dno = 20;

2. WAQTD the annual salary of the employee whose name is smith?

Select sal*12 ANNUALSAL

From emp

Where ename = ‘SMITH’;

3. WAQTD the name of the employee working as a clerk?

Select ename

From emp

Where job = ‘CLERK’;

Operators

1. Arithmetic operators (+, -, *, /)

2.       Comparison operators (=, !=)
3.       Relational operators (<, >, <=, >=)
4.       Logical operators (AND, OR, NOT)
5.       Concatenation operators ( || )
6.       Special operators (IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE, IS, IS NOT)
7.       Sub-Query operators (ALL, ANY, EXISTS, NOT EXISTS)

Concatenation operators

This operator is used to join the given two strings.

Questions

WAQTD the output in the following format

a. ‘MR.ABC your salary is Rs.XYZ’
Select ‘Mr.’ || ename || ‘ your salary is Rs. ‘ || sal

From emp;

b. ‘Mr. SMITH your salary is Rs.XYZ’
Select ‘Mr.’ || ename || ‘ your salary is Rs. ‘ || sal
From emp
Where ename = ‘SMITH’;
c. ‘Mr. KING your designation as President’
Select ‘Mr.’ || ename || ‘ your salary is Rs. ‘ || job
From emp
Where job = ‘PRESIDENT’;
(or) Where ename =’KING’ and job = ‘PRESIDENT’;

Logical Operator

And operator

Binary multiplication

And operator returns true if both the conditions are true

And operator should always be used between conditions.

I/P		O/P
C1	C2	R
F	F	F
F	T	F
T	F	F
T	T	T

OR operator

Binary Addition

OR operator returns true if any one of the conditions is satisfied (True).

OR operator should always be used between conditions.

I/P		O/P
C1	C2	R
F	F	F
F	T	T
T	F	T
T	T	T

Not operator

It is used negation.

I/P	O/P
F	T
T	F

Logical operators Questions

WAQTD ename if the emp is working in dept 20 and earning sal more than 2000

Select ename

From emp

Where deptno = 20 AND sal > 2000;

1. WAQTD details of the employees working as clerk and earning less than 1500
Select *
From emp
Where job = ‘CLERK’ AND sal < 1500;
2. WAQTD name and hiredate of the employees working as Manager in dept 30
Select ename, hiredate
From emp
Where job = ‘MANAGER’ AND deptno = 30;

Special operator

IN operator

IN operator is a multi-valued operator in which we can pass multiple values at RHS.
i.e. IN operator can accept multiple values at RHS.
IN operator returns true, if any one of the condition is satisfied.
IN operator allows the value present at LHS to be compared with all the values present at RHS.

Syntax

Column_name/expression IN (v1, v2, v3, ------vn);

Ex: 10 IN (10,20,30)

Example for IN operator

1.       WAQTD details of emps working in dept 10,20,30,40,50,60
Select *
From emp
Where deptno in (10,20,30,40,50,60);
2.       WAQTD ename and job of emp’s who are working as Manager or Salesman
Sales ename, job
From emp
Where job in (‘MANAGER’, ‘SALESMAN’)
3.       WAQTD details of emp working as clerk or manager in dept 10.
Select *
From emp
Where job in (‘CLERK’, ‘MANAGER’) AND deptno = 10;

Not IN operator

NOT IN operator is similar to IN operator but it rejects the value instead of selecting it.

Syntax

column_name/expression NOT IN (v1, v2, v3, ------vn );

Example for NOT IN operator

1. WAQTD emp’s name excluding the emp’s working in dept 10 or 20.
Select ename
From emp
Where deptno NOT IN (10, 20);
i.e., 10 not in (10, 20)

Between operator

·         Between operator is used whenever we have ranges.
·         Between operator works including the ranges.
·         The range cannot be interchanged.

Syntax

Column_name/expression BETWEEN lower_range AND higher_range;

Not Between

Not Between is similar to between operator but it rejects the value instead of selecting it.
Not between operator does not include ranges.

Syntax

Column_name/expression NOT BETWEEN lower_range AND higher_range;

IS operator

IS operator is used only to compare with NULL

Syntax

Column_name/expression IS NULL;

Example for IS operator

1. WAQTD ename if they are not earing an commission
Select ename
From emp
Where comm is NULL;
2. WAQTD ename who doesn’t get any salary
Select ename
From emp
Where sal is NULL;

IS NOT operator

IS NOT operator is similar to IS operator but it rejects the values instead of selecting it.

Syntax

Column_name/expression IS Not NULL;

Example for IS NOT operator

1. WAQTD ename and comm of emp’s if they are earning comm?
Select ename, comm
From emp
Where comm IS Not NULL;

Like operator

Like operator is used whenever we can need to “Match the Pattern”.
Syntax
Column_name/expression LIKE ‘pattern_to_match’;
To achieve the pattern making we use character such as

1. Percentile (%): It can accept any character, any number of times or no character.
2. Underscore (_): It can accept any character but only once.

NOT Like operator

NOT Like operator is similar to Like operator but it rejects the values instead of selecting it.
Syntax
Column_name/expression NOT LIKE ‘pattern_to_match’;

Escape operator

This character is used to remove the special behaviour of special character which is present next to it.

Syntax

Column_name/expression LIKE ‘pattern_to_match’ ESCAPE ‘char’;

Example for Escape operator

1. WAQTD ename only if the emp’s having character ‘%’ in their name?
Select ename
From emp
Where ename Like ‘%!%%’ ESCAPE ‘!’;
2. WAQTD ename only if the emp’s having character ‘%’ in the last place pf their name?

Select ename
From emp
Where ename Like ‘%!%’ ESCAPE ‘!’;

3. WAQTD ename only if the emp’s having character ‘_’ in the first place pf their name?
Select ename
From emp
Where ename Like ‘!_%’ ESCAPE ‘!’;

Functions

It is the list of instructions that are used to perform a specific task.

Types of functions

1. User-defined function
2. In-built function

a. Single Row function

b. Multi-Row function / Group function / Aggregate function

Single Row Function

·         Single row function executes Row-By-Row
·         It takes one input executes and generates one output then goes to the next input
·         If we pass ‘n’ number of inputs to single row function, it returns ‘n’ number of outputs.

Multi-Row Function

·         Multi-Row function is also known as Group function or Aggregate function.
·         It executes group by group.
·         It takes all the inputs at once aggregates it (combine) and generates one output.
·         If we pass ‘n’ number of inputs to multi row functions, it returns a single output.

List of Multi-Row function

1.       MAX( )
2.       MIN( )
3.       SUM( )
4.       AVG( )
5.       COUNT()

Note

1.       Multi-Row function can accept only a single argument that is a column name or an expression.
2.       MAX() and MIN() functions can be used for all the following datatypes problems.
i.e., CHAR, VARCHR, NUMBER & DATE
3.       SUM() and AVG() functions can only take number column as an argument.
4.       Multi-Row function will ignore the null value.
5.       We cannot use multi-row function in where clause.
6.       We cannot use any column name with multi row functions in select clause.
7.       COUNT() is the only multi row function to which we can pass ‘*’ as an argument.

Question for Multi-Row function

1.       WAQTD max sal from emp table
Select MAX(sal)
From emp;
2.       WAQTD min sal from emp table
Select min(sal)
From emp;
3.       WAQTD total sal from emp table
Select sum(sal)

From emp;

Group By clause

·         We use group by clause to group the records.
·         It executes row by row.
·         For Group by clause we can pass column name or expression as an argument
·         We can write Group_ By_ Expression along with multi-row function is select clause.
·         Group_ By_ Expression: Any column name or expression which is written in Group

by----clause is known as Group_ By_ Expression.
· After the execution of Group By clause it case group and if any clause executes after group by clause it executes Group_ By_ Group ---

Syntax

Select                   group-by-expression/ group-function
From                     table-name
Where                  <filter_condition>
Group by             column_ name/ expression

Question for Group By clause

WQATD number of emp’s working in each dept?

select count(*), deptno

from emp

group by deptno;

1.       WQATD number of emp’s working in each department except president?
select count(*), deptno
from emp
where job not in (‘PRESIDENT’)
group by deptno;
2.       WQATD total salary needed to pay all the employees in each job?
select sum(sal),job
from emp
group by job;
3.       WQATD number of employees working as manager in each department?
select COUNT(*),deptno
from emp
where job in 'MANAGER'
group by deptno;

Having clause

·         We use having clause to filter the groups.
·         We can pass Multi-Row function condition in having clause.
·         It executes Group-By-Group.
·         If you are using Having clause it should be used after Group by clause.
·         If cannot be used without Group by clause.

Syntax

Select                   group-by-expression/ group-function
From                     table-name
Where                  <filter_condition>
Group by             column_ name/ expression
Having                  <group_filter_condition>
Order of execution

1.       From
2.       Where (if used) [Row-By-Row]
3.       Group by [Row-By-Row]
4.       Having                                  [Group-By-Group]
5.       Select                                    [Group-By-Group]

Order By

It is used to sort the records in Ascending or descending order.
·         Order by clause must be written as last clause in the statement.
·         Order by clause executes after the select clause.
·         By default order by clause sort the records in ascending order.
·         We can pass column name or expression as an argument in order by clause.
·         We can pass Alias name in order by clause.

Syntax

Select                    group_ by_ expression/ group_ function
From                     table_ name
[where                 <filter _condition>]
[group by            column_ name/ expression]
[Having                 <group_ filter _ condition>]
Order by              column_ name [ASC]/ DESC;

Order by Execution

1.       From
2.       Where                  (if used) [Row- By- Row]
3.       Group by             (if used) [Row- By- Row]
4.       Having                  (if used) [Group-By- Group]
5.       Select                                    [Group-By- Group]
6.       Order by

Problems

1.       WAQTD ename in Asc.order
Select ename
From emp
Order by ename;
2.       WAQTD sal in asc.order
Select sal
From emp
Order by sal;
3.       WAQTD salary in descending order
Select sal
From emp
Order by sal DESC;
4.       WAQTD deptno and sal in ASC order
Select deptno, sal
From emp
Order by deptno, sal;                     or Order by 1,2;

Sub- Query

A query which is written inside another query is known as Sub-Query.

Working Procedure

Here we will be having of 2 queries

1. Outer Query
2. Inner Query

· Inner query will executes first and generate the output.

· The output generated by the inner query will be given as input to the outer query.

· The outer query will execute and generate the output.

· The output will be the result.

· By this we can say the outer query is dependent on inner query.

Why Sub-query

Case 1: whenever we have unknown.

Case 2: Whenever the data to be selected and the condition to be executed are present in different tables we use sub-query.

Sub-Query Questions

1)      WAQTD name of the employees earning more than ADAMS.
Select ename
From emp
Where sal> (select sal
                         From emp
                          Where ename in ‘ADAMS’);
2)      WAQTD name and sal of the employee earning less than king.
Select ename, sal
From emp
Where sal< (select sal
                         From emp
                          Where ename in ‘KING’);
3)      WAQTD name and deptno of the employee if they are working in the same department as JONES
Select ename, deptno
From emp
Where deptno = (select deptno
                         From emp
                          Where ename in ‘JONES’);
4)      WAQTD name and job of all the employee working in the same designation as JAMES
Select ename, job
From emp
Where job = (select job
                         From emp
                          Where ename in ‘JAMES’);
5)      WAQTD empno and ename along with annual salary of all the employees if there annual sal is greater than WARD’s Annual sal
Select empno, ename, sal*12 ANNUALSAL
From emp
Where sal*12> (select sal*12
                                From emp
                                Where ename in ‘WARD’);

Nested Sub-Query

· A sub-query written inside another sub-query is known as Nested sub-query.
· We can nest about 255 sub-query.

· To find n^thmax or min salary, we will write (n-1) sub-queries.

Types of Sub-Queries

There are two types of sub-quires

1. Single Row Sub-queries

2. Multi Row Sub-queries

Single Row Sub-queries

A sub-query which returns exactly one output is known as Single Row Sub-queries

We can use operators such as IN, NOT IN, ALL, ANY

Example

WAQTD ename earning more than scott?
Select ename
From emp
Where sal> (select sal
From emp
Where ename in ‘SCOTT’);

Multi Row Sub-queries

A sub-query which returns more than one output is known as Multi Row Sub-queries

We must use operators such as IN, NOT IN, ALL, ANY

ALL operators

ALL operator is a special operator which can accept multiple values at RHS. It will return true only if all the condition at the RHS is satisfied.

Syntax

column_name/ expression --- Relational op. ALL (V1,V2,-----Vn);

ANY operators

ANY operator is a special operator which can accept multiple values in RHS. It will return true if any one of the condition at the RHS is satisfied.

Syntax

column_name/ expression --- Relational op. ANY (V1, V2,----- Vn);

Q) Display employee name who are earning more than salesman.

Method-1 Select ename

From emp

Where sal> ALL(select sal

From emp

Where job = ‘Salesman’);

Method -2 Select ename

From emp

Where sal > (select max(sal)

From emp

Where job in ‘Salesman’);

1.       WAQTD name of the employees earning salary more than all the salesman
Select ename
From emp
Where sal > all (select sal
                                From emp
                                Where ename in ‘SALESMAN’);

Employee – Manager relation

Emp

EID	ENAME	MGR
1	Vicky	2
2	Micky	4
3	Lucky	4
4	Tara	5
5	MIA

Questions

Q) WAQTD manager name of lucky.

Select ename

Form emp

Where eid in ( select MGR

From emp

Where ename in ‘Lucky’);

Q) WAQTD smith reporting manager’s name

Select ename

From emp

Where empno in (select MGR

Form emp

Where ename = ‘SMITH’;

Q)WAQTD adams manager’s manager name

Select ename

Form emp

Where empno in (select MGR

From emp

Where empno in (select MGR

Form emp

Where ename = ‘ADAM’));

Q) WAQTD dname of jones manager

Select dname

From dept

Where deptno in (select deptno

From emp

Where empno in (select MGR

From emp

Where ename = ‘JONEA’));

Q) WAQTD loc of smith’s manager’s manager.

Select loc

From dept

Where deptno in (select deptno

From emp

Where empno in (select MGR

From emp

Where empno in (select MGR

From emp

Where ename = ‘SMITH’)));

Joins

This statement is used to retrieve the data from multiple tables simultaneously.

Types of Joins

1.       Cartesian Join (or) Cross join
2.       Inner join
3.       Outer join
a.       Left outer join
b.      Right outer join
c.       Full outer join
4.       Self Join
5.       Natural join

Cartesian join (or) Cross join

In Cartesian join a record from table1 will be merged with all the records of table2.

Syntax

1. ANSI : Select column_ Name
From Table_Name1 cross join Table_Name2;

2. Oracle : Select Column_ Name
From Table_ Name1, Table_Name2;

·         Number of columns in result table will be summation of columns present in table1 and table2.
·         Number of records in result table will be product of records present in table1 and table2.
·         In this join we will be getting error records.

Q) WAQTD Bname and Gname?

Select Bname, Gname

From Boys, Girls;

Boys

BID	Bname	GID
1	VIRAT	22
2	RANVEER	33
3	ABHI	11

Girls

GID	Gname
11	AISH
22	ANUSHKA
33	DEEPIKA

BID	Bname	GID	GID	Gname
1	VIRAT	22	11	AISH
1	VIRAT	22	22	ANUSHKA
1	VIRAT	22	33	DEEPIKA
2	RANVEER	33	11	AISH
2	RANVEER	33	22	ANUSHKA
2	RANVEER	33	33	DEEPIKA
3	ABHI	11	11	AISH
3	ABHI	11	22	ANUSHKA
3	ABHI	11	33	DEEPIKA

Inner join

· We use inner join to obtain only the matched records or the records which as pair.
· We use join condition to obtain the matched records.

Syntax

1.       ANSI      :               Select column_ Name
From Table_Name1 Inner join Table_Name2
ON <Join_ Condition>;
                Ex           :               Select *
                                                From emp Inner join dept
                                                ON emp.deptno = dept.deptno;

2.       Oracle   :               Select Column_ Name
From Table_ Name1, Table_Name2
Where <Join_ Condition>;

Join condition

It is a condition on which we merge two tables to get only the matched records.

Syntax for join condition

Table_ Name1.col_ Name = Table_ Name2.col_ Name

Eg: emp.dno = dept.dno

Q) WAQTD Bname and Gname?

Select Bname, Gname

From boys, girls

Where boys.GID = girls.GID;

1.       22= 11 F
22= 22 T
22= 33 F
2.       33 = 11 F
33 = 22 F
33 = 33 T
3.       11 = 11 T
11 = 22 F
11 = 33 F

(III) O/p of where

BID	Bname	GID	GID	Gname
1	VIRAT	22	22	ANUSHKA
2	RANVEER	33	33	DEEPIKA
3	ABHI	11	11	AISH

(IV) O/P of select

Bname	Gname
VIRAT	ANUSHKA
RANVEER	DEEPIKA
ABHI	AISH

Questions

Q) WAQTD ename, sal and dname of all the emp’s

Select ename, sal, dname

From emp, dept

Where emp.deptno = dept.deptno;

Q) WAQTD ename, deptno, dname

Select ename, emp.deptno, dname

From emp, dept

Where emp.deptno = dept.deptno;

Q) WAQTD ename, dname if employees are working in dept 20.

Select ename, dname

From emp, dept

Where emp.deptno = dept.deptno and deptno =20;

Outer joins

In outer join we get the unmatched records along with the matched records.

Left outer join

In left outer join we get unmatched records of left table along with matched record.

Syntax

1. ANSI : Select column_ Name

From Table_Name1 Left [Outer] join Table_Name2

ON <Join_ Condition>;

Ex : Select *

From emp E Left outer join dept D

ON E.deptno = D.deptno;

2. Oracle : Select Column_ Name

From Table_ Name1, Table_Name2

Where Table_ Name1.col_ Name = Table_ Name2.col_ Name(+);

Ex : Select *

From emp E, dept D

Where E.deptno = D.deptno(+);

Questions

Q1) WAQTD all the details from emp and dept table along with unmatched records from emp table?

Select *

From emp E, dept D

Where E.deptno = D.deptno(+);

Q) WAQTD ename who are not working in any dept using joins

Select ename

From emp E, dept D

Where E.deptno =D.deptno(+) and E.deptno is NULL;

Right outer join

In right outer join we get unmatched records of right table along with matched records.

Syntax

1. ANSI : Select column_ Name

From Table_Name1 Right [Outer] join Table_Name2

ON <Join_ Condition>;

Ex : Select *

From emp E Right outer join dept D

ON E.deptno = D.deptno;

2. Oracle : Select Column_ Name

From Table_ Name1, Table_Name2

Where Table_ Name1.col_ Name(+) = Table_ Name2.col_ Name;

Ex : Select *

From emp E, dept D

Where E.deptno(+) = D.deptno;

Questions

Q) WAQTD all the details from emp and dept table along with unmatched records from dept table.

Select *

From emp E, dept D

Where E.deptno(+) = D.deptno;

Q) WAQTD dname in which there are no employees working using joins.

Select dname

From emp E, dept D

Where E.deptno(+) = D.deptno and empno is null;

Full outer join

To obtain unmatched records of both the tables along with matched records.

Syntax

1. ANSI : Select column_ name

Form Table_Name1 FULL [OUTER] JOIN Table_ Name2

ON <Join_ Condition>;

Eg : Select *

From emp E Full outer join dept D

ON E.deptno = D.deptno;

2. No syntax in oracle for full outer join.

Questions

Q) WAQTD all the details from emp and dept table along with unmatched records of both tables?

Select *

From emp E full join dept D

Where E.deptno = D.deptno;

Self-join

Self-join is used to join the same two tables or the table itself.

Why we use self-join?

It the data to be selected and condition to be executed is present in the same table but in different record we use self-join.

Syntax

1. ANSI : Select column_ Name

From Table_ NameT1 Join Table_NameT2

ON <Join_ condition>;

Eg : Select *

From emp E1 join emp E2

ON E1.MGR = E2.Empno;

2. Oracel : Select column_ Name

From Table_ NameT1, Table_NameT2

Where <Join_ condition>;

Eg : Select *

From emp E1, emp E2

Where E1.MGR = E2.empno;

Q) WAQTD ename and MGR’s name?

Select E1.ename, E2.ename

From emp E1, emp E2

Where E1.MGR = E2.eid;

1. WAQTD name of the employees and his manager’s name if employee is working as clerk.
Select e1.ename, e2.ename MGR_NAME
From emp e1, emp e2
Where e1.MGR = e2.empno and e1.job = ‘CLERK’;

Note:

· If we want to join ‘n’ number of tables we will be writing (n-1) join conditions.

· We can join up to 256 tables using inner join.

1. WAQTD ename, manager’s name and manager’s manager name along with

their dname’s emp earns more than 1000 and manager earns more than

ALLEN and manager’s manager working in New York or Chicago?

Select e1.ename, e2.ename, e3.ename, d1.dname, d2. dname, d3. dname

From emp e1, emp e2, emp e3, dept d1, dept d2, dept d3

Where e1.MGR = e2.empno and

e2.MGR = e3.empno and

e1.deptno = d1.deptno and

e2.deptno = d2.deptno and

e3.deptno = d3.deptno and

e1.sal > 1000 and e2.sal > (Select sal from emp where ename = ‘ALLEN’) and

e3.loc in (‘New York’, ‘Chicago’);

ANSI

Select e1.ename, e2.ename, e3.ename, d1.dname, d2. dname, d3. dname

From emp e1 join emp e2

On e1.MGR = e2.empno join emp e3

On e2.MGR = e3.empno join dept d1

On e1.deptno = d1.deptno join dept d2

On e2.deptno = d2.deptno join dept d3

On e3.deptno = d3.deptno and

e1.sal > 1000 and e2.sal > (Select sal from emp where ename = ‘ALLEN’)

and

e3.loc in (‘New York’, ‘Chicago’);

2. WAQTD ename, manager’s name and manager’s manager name along with their loc

if the emp hired before Matin and manager working in Accounting or Sales dept and manager’s manager earning sal more than Smith?

Select e1.ename, e2.ename, e3.ename, d1.loc, d2.loc, d3.loc

From emp e1, emp e2, emp e3, dept d1, dept d2, dept d3

Where e1.MGR = e2.empno and

e1.deptno = d1.deptno and

e2.deptno = d2.deptno and

e3.deptno = d3.deptno and

e1.hiredate < (Select hiredate from emp where ename =’MARTIN’) and d2.dname in (‘ACCOUNTING’, ‘SALES’) and

e3.sal > (select sal from emp where ename = ‘SMITH’);

ANSI

Select e1.ename, e2.ename, e3.ename, d1.loc, d2.loc, d3.loc

From emp e1 join emp e2

On e1.MGR = e2.empno join emp e3

On e2.MGR = e3.empno join dept d1

On e1.deptno = d1.deptno join dept d2

On e2.deptno = d2.deptno join dept d3

On e3.deptno = d3.deptno and

e1.hiredate < (Select hiredate from emp where ename =’MARTIN’) and d2.dname in (‘ACCOUNTING’, ‘SALES’) and e3.sal > (select sal from emp where ename = ‘SMITH’);

Natural Join

·         In natural join we won’t be writing any join condition.
·         If the table contains similar columns we get the output of inner join.
·         If the table is not having similar columns we will get the output of Cartesian join.

Why or when we use Natural join?
Whenever there is no table structure we use Natural join columns that are present- table structure.
Syntax
ANSI : Select column_ Name
From Table_ Name1 Natural Join Table_ Name2;

Ex: Select *
From emp Natural Join dept;
(Or)
Select *
From emp Natural Join Salgrade;

Outer joins

In outer join we get the unmatched records along with the matched records.

Left outer join

In left outer join we get unmatched records of left table along with matched record.

Syntax

1. ANSI : Select column_ Name

From Table_Name1 Left [Outer] join Table_Name2

ON <Join_ Condition>;

Ex : Select *

From emp E Left outer join dept D

ON E.deptno = D.deptno;

2. Oracle : Select Column_ Name

From Table_ Name1, Table_Name2

Where Table_ Name1.col_ Name = Table_ Name2.col_ Name(+);

Ex : Select *

From emp E, dept D

Where E.deptno = D.deptno(+);

Questions

Q1) WAQTD all the details from emp and dept table along with unmatched records from emp table?

Select *

From emp E, dept D

Where E.deptno = D.deptno(+);

Q) WAQTD ename who are not working in any dept using joins

Select ename

From emp E, dept D

Where E.deptno =D.deptno(+) and E.deptno is NULL;

Right outer join

In right outer join we get unmatched records of right table along with matched records.

Syntax

1. ANSI : Select column_ Name

From Table_Name1 Right [Outer] join Table_Name2

ON <Join_ Condition>;

Ex : Select *

From emp E Right outer join dept D

ON E.deptno = D.deptno;

2. Oracle : Select Column_ Name

From Table_ Name1, Table_Name2

Where Table_ Name1.col_ Name(+) = Table_ Name2.col_ Name;

Ex : Select *

From emp E, dept D

Where E.deptno(+) = D.deptno;

Full outer join

To obtain unmatched records of both the tables along with matched records.

Syntax

1. ANSI : Select column_ name
Form Table_Name1 FULL [OUTER] JOIN Table_ Name2
ON <Join_ Condition>;

Eg : Select *

From emp E Full outer join dept D

ON E.deptno = D.deptno;

2. No syntax in oracle for full outer join.

Questions

Q) WAQTD all the details from emp and dept table along with unmatched records of both tables?

Select *

From emp E full join dept D

Where E.deptno = D.deptno;

Self-join

Self-join is used to join the same two tables or the table itself.

Why we use self-join?

It the data to be selected and condition to be executed is present in the

same table but in different record we use self-join.

Syntax

ANSI : Select column_ Name

From Table_ NameT1 Join Table_NameT2

ON <Join_ condition>;

Eg : Select *

From emp E1 join emp E2

ON E1.MGR = E2.Empno;

Oracel : Select column_ Name

From Table_ NameT1, Table_NameT2

Where <Join_ condition>;

Eg : Select *

From emp E1, emp E2

Where E1.MGR = E2.empno;

Q) WAQTD ename and MGR’s name?

Select E1.ename, E2.ename

From emp E1, emp E2

Where E1.MGR = E2.eid;

Natural Join

In natural join we won’t be writing any join condition.
If the table contains similar columns we get the output of inner join.
If the table is not having similar columns we will get the output of Cartesian join.

Why or when we use Natural join?

Whenever there is no table structure we use Natural join columns that are present- table structure.

Syntax

ANSI : Select column_ Name

From Table_ Name1 Natural Join Table_ Name2;

Ex: Select *

From emp Natural Join dept;

(Or)

Select *

From emp Natural Join Salgrade;

Single Row Functions

Dual

Dual is dummy table to print the result of any Mathematical operators done.

UPPER()

This function is used to convert the given string into upper case.

Syntax

UPPER(‘STRING’)

Example

Select upper(‘SAI’)

From dual;

LOWER()

This function is used to convert the given string into lower case.

Syntax

LOWER(‘STRING’)

Example

Select lower(‘SAI’)

From dual;

INITCAP()

This function is used to convert the given string into upper case.

Syntax

INITCAP(‘STRING’)

Example

Select upper(‘THE EARTH’)

From dual;

LENGTH()

This function is used to convert the number of characters that are present in the string.

Syntax

LENGTH(‘STRING’)

Q1) WAQTD number of characters present in ename’s for all the emp’s?

Select length(ename)

From emp;

Q2) WAQTD ename who are having only 5 characters in their name using single row function(SRF)?

Select ename

From emp

Where length(ename) = 5;

Q3) WAQTD ename and sal of emp’s who are getting 3 digit sal using SRF?

Select ename, sal

From emp

Where length(sal) = 3;

Q4) WAQTD ename and comm of emp’s who are getting 3 digits comm?

Select ename, comm

From emp

Where length(comm) = 3;

REVERSE()

This function is used to reverse the given string.

Syntax

REVERSE(‘STRING’)

Example

Select reverse(‘MALAYALAM’)

From dual;

O/P: MALAYALAM

Select reverse(‘SAI’)

From dual;

O/P: IAS

SUBSTR()

This function is used to extract the part of the string form he given original string.

Syntax

SUBSTR(‘ORIGINAL_ STRING’, POSITION, [LENGTH])

Example

-9 -8 -7 -6 -5 -4 -3 -2 -1

BANGALORE

1 2 345678 9

SUBSTR(‘BANGALORE’,1,1) 🡪 B

SUBSTR(‘BANGALORE’,1,3) 🡪 BAN

SUBSTR(‘BANGALORE’,5,3) 🡪 ALO

SUBSTR(‘BANGALORE’,7,3) 🡪 ORE

SUBSTR(‘BANGALORE’,8,8) 🡪 RE

SUBSTR(‘BANGALORE’,7,5) 🡪 ORE

SUBSTR(‘BANGALORE’,-1,1) 🡪 E

SUBSTR(‘BANGALORE’,-3,3) 🡪 ORE

SUBSTR(‘BANGALORE’,-8,3) 🡪 ANG

Q1) WAQTD the first character of all the emp’s from emp table?

Select substr(ename, 1,1)

From emp;

Q2) WAQTD details of the emp’s if their name starts with ‘A’ using SRF?

Select *

From emp

Where substr(ename, 1,1) = ‘A’;

Q3) WAQTD the first three characters of all the emp’s?

Select substr(ename, 1,3)

From emp;

Q4) WAQTD details of the emp’s if their name starts with ‘A’ or ‘S’ using SRF?

Select *

From emp

Where substr(ename, 1,1) in (‘A’, ‘S’);

Q5) WAQTD ename whose name starts with vowels using SRF?

Select ename

From emp

Where substr(ename, 1,1) in (‘A’, ‘E’, ‘I’,’O’, ‘U’);

7. INSTR()

This function is used to obtain index value of the substring which is

present in the original string.

Index value – Position of character.

Syntax

INSTR(‘ORIGINAL_ STRING’, ‘SUB_ STR’, POSITION, [Nth OCCURANCE])

Nth OCCURANCE – number of times it is present.

Example

BANANA

1 23 456

INSTR(‘BANANA’, ‘A’,1,1) 🡪 2

INSTR(‘BANANA’, ‘A’,1,3) 🡪 6

INSTR(‘BANANA’, ‘A’,2,1) 🡪 2

INSTR(‘BANANA’, ‘A’,3,1) 🡪 4

INSTR(‘BANANA’, ‘A’,2,3) 🡪 6

INSTR(‘BANANA’, ‘N’,2,2) 🡪 5

INSTR(‘BANANA’, ‘AN’,1,2) 🡪 4

INSTR(‘BANANA’, ‘A’,1,4) 🡪 0 if character is not present in string, = 0

INSTR(‘BANANA’, ‘Z’,1,1) 🡪 0 if character is present in string, > 0

Q) WAQTD details of emp’s if their name having character ‘A’ using SRF?

Select *

From emp

Where instr(ename,’A’,1,1) > 0;

MOD()

This function is used to obtain modulus of the given number (reminder).

Syntax

MOD(m,n)

Example

MOD(7,2) 🡪 1
MOD(6,2) 🡪 0

Q1) WAQTD details of emp’s whose empno is an odd number?

Select *

From emp

Where mod(empno,2) = 1;

Q2) WAQTD details of even records?

Select *

From emp

Where mod(empno,2)=0;

TO_CHAR()

This function is used to convert the given date to string format.

Syntax:

TO_CHAR(DATE, ‘FORMAT_MODELS’)

Format_ Models

YEAR
YYYY
YY
MONTH
MON
MM
DAY
DY
DD
D
HH24
HH12
MI
SS

Example

WAQTD ename’s of the emp’s who were hired in the month Feb using SRF?

Select ename

From emp

Where to_char( hiredate, ‘MON’) = ‘FEB’;

WAQTD ename’s of the emp’s who were hired in the month oct, nov, dec?

Select ename

From emp

Where to_char( hiredate, ‘MON’) in (‘OCT’,’NOV’,’DEC’);

WAQTD details of the emp’s hired on WEDNESDAY or MONDAY?

Select *

From emp

Where to_char( hiredate, ‘DY’) in (‘WED’, ‘MON’);

WAQTD details of the emp’s hired in the year 81, 83, 87?

Select *

From emp

Where to_char( hiredate, ‘YY’) in (81, 83, 87);

WAQTD details of the emp’s hired on date 17 & 19 & 22?

Select *

From emp

Where to_char( hiredate, ‘DD’) in (17, 19, 22);

SYSDATE / CURRENT_ DATE

This command is used to obtain the current date form database.

SYSTEMSTAMP

This command is used to obtain the date and time along with time zone.

TO_DATE()

This function is used to convert the date sting to date format.

Syntax

TO_DATE(‘DATE_ STR’)

Example

Select to_ char(to_ date(’15- AUG- 1947’), ‘DAY’)

From DUAL;

10. NVL()

NVL means Null Value Logic

Syntax

NVL(ARG1, ARG2)

It can accept two arguments

In arg1 we must write a column name or expression that can be NULL.

In args2 we must write a value that can be substituted in place of NULL.

If arg1 is not NULL, NVL returns same value present in arg1.

Example

Q) WAQTD total salary of each employee?

Select sal+NVL(comm,0)

From emp;

11. REPLACE()

This function is used to replace the substring with new string in given original string.

Syntax

REPALCE(‘ORGINAL_ STRING’,’SUB_ STR’,[‘NEW_ STR’])

Example

Select replace(‘Jspiders’, ‘J’, ‘Q’)

From dual;

PSEUDO columns

PSEUDO columns are the false columns that are present in each and every table

and must be called Explicitly.

PSEUDO columns cannot be seen without calling them.

Types of PESUDO columns

ROWID
ROWNUM

ROWID

ROWID is an 18 digit address in which the record is present or

ROWID is an 18 digit address in which the record is present or the record is stored in the memory.

Select ROWID, emp.*

From Emp:

Note

>ROWID is one of the ways to access or delete the record

> ROWID is unique.

> ROWID is present for each and every record.

> ROWID is generated at the time of insertion of records.

> ROWID cannot be inserted, updated or deleted.

> Empty table will not be having ROWID

> ROWID is static in nature. (Constant)

> ROWID can be used to identify a record uniquely from the table when there is no

key attribute or primary key.

ROWNUM

ROWNUM acts as serial number or the result table.

Syntax

Select ROWNUM, emp.*

From emp;

Note

ROWNUM is used as record number that is assigned to the result table.
ROWNUM is dynamic in nature (keeps in changing)
ROWNUM is generated at the time of execution.
ROWNUM always starts with 1.
ROWNUM cannot be displayed.
ROWNUM gets incremented after it is assigned.

Q) WAQTD first three records?

Select *

From emp

Where rownum<4;

Q) WAQTD 3rd record from emp table?

Select *

From emp

Where rownum=3;

To make ROWNUM as static

Take a table and assign rownum to a given table.
Change the rownum to any other name by using alias (Slno).
Use this as a sub query in from clause of outer query.
In the outer query use the alias name in the condition.

Example

Q) WAQTD the 7th record from each table.

Select *

From (select rownum slno, emp.*

From emp)

Where slno =7;

Q1) WAQTD 5th record from the emp table

Select *

From (select rownum slno, emp.*

From emp)

Where slno =5;

To find nth max and nth min salary using Rownum concept

Syntax for nth max

Select sal

From (select rownum slno, sal

From (select distinct sal

Form emp

Order by sal desc))

Where slno = n;

Syntax for nth min

Select sal

From (select rownum slno, sal

From (select distinct sal

Form emp

Order by sal asc))

Where slno = n;

Examples

WAQTD 8th min sal?

Select sal

From (select rownum slno, sal

From (select distinct sal

Form emp

Order by sal))

Where slno = 8;

WAQTD 12th max sal?

Select sal

From (select rownum slno, sal

From (select distinct sal

Form emp

Order by sal desc))

Where slno = 12;

WAQTD top 5 max sal?

Select sal

From (select rownum slno, sal

From (select distinct sal

Form emp

Order by sal desc))

Where slno <6;

DML (Data Manipulation Language)

This statement is used to insert, update or delete the records from the table.

There are three statements

Insert
Update
Delete

Insert

This statement is used to insert the records into the table.

Syntax

INSERT INTO table_ name VALUES(V1, V2,V3, -----, Vn);
INSERT INTO table_ name(COL 1,COL 2,COL 3,---,COL n) VALUES(V1, V2,V3, -----, Vn);
INSERT INTO table_ name(COL 1,COL 2,COL 3,---,COL n) VALUES(&COL 1,&COL 2,&COL 3,---,&COL n);

Example

Insert into bonus values (‘TONY’,’CEO’,10000, 3000);

Insert into bonus (ename, sal, job, comm) values(‘ROCKY’, 9000, ‘ACTER’, 1000);

Commit;

Insert into bonus (ename, job, sal, comm) values(&ename, &job, &sal, &comm);

Enter value for ename: ‘JONES’

Enter value for job: ‘TAMER’

Enter value for sal: 1000

Enter value for comm: 500

Commit;

Update

This statement is used to update the records in the table.

Syntax

Update table_ name

Set Col1 = V1, Col2 = V2, Col3 = V3, -------- Col n = V n;

[where < filter- condition>];

Example

Update bonus

Set sal = 150000

Where ename = ‘ROCKY’;

Commit;

Delete

This statement is used to delete a particular record from the table.

Syntax

Delete

From table- name

[where <filter- condition>];

Example

Delete from bonus

Where ename = ‘TARUN’;

Commit;

Commit

Commit statement is used to save the record in database.

Transaction Control Language (TCL)

There are three statements

COMMIT
SAVEPOINT
ROLLBACK

COMMIT

This statement is used to save the transaction on database.

Syntax

COMMIT;

SAVEPOINT

This statement is used to mark the position of database.

Syntax

SAVEPOINT savepoint_name;

ROLLBACK

Rollback to SAVEPOINT

This statement is used to go back or undo to the previous savepoint.

Syntax

ROLLBACK TO savepoint_name;

Example

Insert into bonus values( );

Savepoint r1;

Insert into bonus values( );

Savepoint r2;

Insert into bonus values( );

Savepoint r3; deleted

Insert into bonus values( );

Rollback to r2;

upcoming topic TCL ..............

Thursday 28 April 2022

SQL

SQL

Three Tire App

Data

Database

Database Management System (DBMS)

Types of DBMSND

Relational Database Management System (RDBMS)

Relational Model

Rules of E.F.CODD

Note

Statements in SQL

Data Definition Language (DDL)

Data Manipulation Language (DML)

Transaction Control Language (TCL)

Data Control Language (DCL)

Data Query Language (DQL)

Datatypes

Datatypes in SQL

Note

CHAR

ASCII

Syntax

Size

Note:

Drawback

VARCHAR

Syntax

Size

Note

Ex:

1. Varchar(10)

VARCHAR2

Syntax

Difference between CHAR and VARCHAR

Difference between Varchar and Varchar2

Number

Syntax

Example

Precision

Scale

Question and Answers

Date

Syntax

Example

Large Object

CLOB (Character Large Object)

BLOB (Binary Large Object)

Constraints

Types of constraints

Unique

Not Null

Check

Primary key

Characteristics of Primary key

Foreign key

Characteristics of Foreign Key

Note

Data Query Language (DQL)

Types of DQL statements

Select

Projection

Selection

Joins

Projection

Note

Order of Execution

Expression

Alias

Examples

Distinct

Student table

Questions

Selection

Where clause

Note

Syntax

Order of Execution

Examples