Relational Model in DBMS

E.F. Codd proposed the relational Model to model data in the form of relations or
tables.
The relational model uses a collection of tables to represent both data and the
relationships among those data. Each table has multiple columns, and each column has
a unique name. Tables are also known as relations.
The relational model is an example of a record-based model. Record-based models are
so named because the database is structured in fixed-format records of several types.
Each table contains records of a particular type. Each record type defines a fixed
number of fields, or attributes. The columns of the table correspond to the attributes of
the record type. The relational data model is the most widely used data model, and a
vast majority of current database systems are based on the relational model.
What is the Relational Model?
The relational model represents how data is stored in Relational Databases. A relational
database consists of a collection of tables, each of which is assigned a unique name.
Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS,
PHONE, and AGE shown in the table.

Important Terminologies
 Attribute: Attributes are the properties that define an entity. e.g.; ROLL_NO,
NAME, ADDRESS
 Relation Schema: A relation schema defines the structure of the relation and
represents the name of the relation with its attributes. e.g.; STUDENT
(ROLL_NO, NAME, ADDRESS, PHONE, and AGE) is the relation schema for
STUDENT. If a schema has more than 1 relation, it is called Relational Schema.
 Tuple: Each row in the relation is known as a tuple. The above relation contains
4 tuples, one of which is shown as:

 Relation Instance: The set of tuples of a relation at a particular instance of time

is called a relation instance. Table 1 shows the relation instance of STUDENT
at a particular time. It can change whenever there is an insertion, deletion, or
update in the database.
 Degree: The number of attributes in the relation is known as the degree of the
relation. The STUDENT relation defined above has degree 5.
 Cardinality: The number of tuples in a relation is known as cardinality. The
STUDENT relation defined above has cardinality 4.
 Column: The column represents the set of values for a particular attribute. The
column ROLL_NO is extracted from the relation STUDENT.
  NULL Values: The value which is not known or unavailable is called a NULL
value. It is represented by blank space. e.g.; PHONE of STUDENT having
ROLL_NO 4 is NULL.
 Relation Key: These are basically the keys that are used to identify the rows
uniquely or also help in identifying tables. These are of the following types.
 Primary Key
 Candidate Key
 Super Key
 Foreign Key
 Alternate Key
 Composite Key
Advantages of the Relational Model
 Simple model: Relational Model is simple and easy to use in comparison to
other languages.
 Flexible: Relational Model is more flexible than any other relational model
present.
 Secure: The relational Model is more secure than any other relational model.
 Data Accuracy: Data is more accurate in the relational data model.
 Data Integrity: The integrity of the data is maintained in the relational model.
 Operations can be Applied Easily: It is better to perform operations in the
relational model.
Disadvantages of the Relational Model
 The Relational Database Model is not very good for large databases.
 Sometimes, it becomes difficult to find the relation between tables.
 Because of the complex structure, the response time for queries is high.
Characteristics of the Relational Model
 Data is represented in rows and columns called relations.
 Data is stored in tables having relationships between them called the Relational
model.
 The relational model supports the operations like Data definition, Data
manipulation, and Transaction management.
 Each column has a distinct name and they represent attributes.
 Each row represents a single entity.
DBMS Integrity Constraints
Integrity constraints are the set of predefined rules that are used to maintain the quality
of information. Integrity constraints ensure that the data insertion, data updating, data
deleting and other processes have to be performed in such a way that the data integrity
is not affected. They act as guidelines ensuring that data in the database remain accurate
and consistent. So, integrity constraints are used to protect databases. The various types
of integrity constraints are
Types of Integrity Constraints:
 Domain Constraints
 Entity integrity Constraints
 Key Constraints
  Referential integrity constraints

1. Domain constraints
Domain constraints can be defined as the definition of a valid set of values for an
attribute.
The data type of domain includes string, character, integer, time, date, currency, etc.
The value of the attribute must be available in the corresponding domain.
Example:

2. Entity integrity constraints

o The entity integrity constraint states that primary key value can't be null.
o This is because the primary key value is used to identify individual rows in
relation and if the primary key has a null value, then we can't identify those
rows.
o A table can contain a null value other than the primary key field.

Example:

3. Referential Integrity Constraints

o A referential integrity constraint is specified between two tables.
o In the Referential integrity constraints, if a foreign key in Table 1 refers to the
Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be
null or be available in Table 2.
Example:

4. Key constraints
o Keys are the entity set that is used to identify an entity within its entity set
uniquely.
o An entity set can have multiple keys, but out of which one key will be the
primary key. A primary key can contain a unique and null value in the relational
table.

Example:

Relational Algebra

Relational Algebra is a procedural query language. Relational algebra mainly provides

a theoretical foundation for relational databases and SQL. The main purpose of using
Relational Algebra is to define operators that transform one or more input relations into
an output relation. Given that these operators accept relations as input and produce
relations as output, they can be combined and used to express potentially complex
queries that transform potentially many input relations (whose data are stored in the
database) into a single output relation (the query results). As it is pure mathematics,
there is no use of English Keywords in Relational Algebra and operators are
represented using symbols.
Fundamental Operators
These are the basic/fundamental operators used in Relational Algebra.
1. Selection(σ)
2. Projection(π)
3. Union(U)
4. Set Difference(-)
5. Set Intersection(∩)
6. Rename(ρ)
7. Cartesian Product(X)
1. Selection(σ): It is used to select required tuples of the relations.
Example:

For the above relation, σ(c>3)R will select the tuples which have c more than 3.

Note: The selection operator only selects the required tuples but does not display them.
For display, the data projection operator is used.
2. Projection(π): It is used to project required column data from a relation.
Example: Consider Table 1. Suppose we want columns B and C from Relation R.
π(B,C)R will show following columns.

Note: By Default, projection removes duplicate data.

3. Union(U): Union operation in relational algebra is the same as union operation in set
theory.
Example:
FRENCH

GERMAN

Consider the following table of Students having different optional subjects in their
course.
π(Student_Name)FRENCH U π(Student_Name)GERMAN

Note: The only constraint in the union of two relations is that both relations must have
the same set of Attributes.
4. Set Difference(-): Set Difference in relational algebra is the same set difference
operation as in set theory.
Example: From the above table of FRENCH and GERMAN, Set Difference is used as
follows
π(Student_Name)FRENCH - π(Student_Name)GERMAN
Note: The only constraint in the Set Difference between two relations is that both
relations must have the same set of Attributes.
5. Set Intersection(∩): Set Intersection in relational algebra is the same set intersection
operation in set theory.
Example: From the above table of FRENCH and GERMAN, the Set Intersection is
used as follows
π(Student_Name)FRENCH ∩ π(Student_Name)GERMAN

Note: The only constraint in the Set Difference between two relations is that both
relations must have the same set of Attributes.
6. Rename(ρ): Rename is a unary operation used for renaming attributes of a relation.
ρ(a/b)R will rename the attribute 'b' of the relation by 'a'.
7. Cross Product(X): Cross-product between two relations. Let’s say A and B, so the
cross product between A X B will result in all the attributes of A followed by each
attribute of B. Each record of A will pair with every record of B.
Example:
A

AXB
Note: If A has ‘n’ tuples and B has ‘m’ tuples then A X B will have ‘ n*m ‘ tuples.

Derived Operators
These are some of the derived operators, which are derived from the fundamental
operators.
Natural Join(⋈)
Conditional Join
1. Natural Join(⋈): Natural join is a binary operator. Natural join between two or more
relations will result in a set of all combinations of tuples where they have an equal
common attribute.
Example:
EMP

DEPT

Natural join between EMP and DEPT with condition :

EMP ⋈ DEPT
EMP.Dept_Name = DEPT.Dept_Name
2. Conditional Join: Conditional join works similarly to natural join. In natural join, by
default condition is equal between common attributes while in conditional join we can
specify any condition such as greater than, less than, or not equal.
Example:
R

Join between R and S with condition R.marks >= S.marks

Instructor Relation
Projection Operation
The Set-Difference Operation

The Cartesian-Product Operation

 Relational Calculus
There is an alternate way of formulating queries known as Relational Calculus.
Relational calculus is a non-procedural query language. In the non-procedural query
language, the user is concerned with the details of how to obtain the end results. The
relational calculus tells what to do but never explains how to do. Most commercial
relational languages are based on aspects of relational calculus including SQL-QBE and
QUEL.
Why it is called Relational Calculus?
It is based on Predicate calculus, a name derived from branch of symbolic language. A
predicate is a truth-valued function with arguments. On substituting values for the
arguments, the function result in an expression called a proposition. It can be either true
or false. It is a tailored version of a subset of the Predicate Calculus to communicate

o Universal Quantifiers: The universal quantifier denoted by ∀ is read as for all

with the relational database.

which means that in a given set of tuples exactly all tuples satisfy a given

o Existential Quantifiers: The existential quantifier denoted by ∃ is read as for

condition.

all which means that in a given set of tuples there is at least one occurrences
whose value satisfy a given condition.
Before using the concept of quantifiers in formulas, we need to know the concept of
Free and Bound Variables.
A tuple variable t is bound if it is quantified which means that if it appears in any
occurrences a variable that is not bound is said to be free.
Free and bound variables may be compared with global and local variable of
programming languages.
Types of Relational calculus:

1. Tuple Relational Calculus (TRC)

It is a non-procedural query language which is based on finding a number of tuple
variables also known as range variable for which predicate holds true. It describes the
desired information without giving a specific procedure for obtaining that information.
The tuple relational calculus is specified to select the tuples in a relation. In TRC,
filtering variable uses the tuples of a relation. The result of the relation can have one or
more tuples.
Notation:
A Query in the tuple relational calculus is expressed as following notation
1. {T | P (T)} or {T | Condition (T)}
Where
T is the resulting tuples
P(T) is the condition used to fetch T.
For example:
1. { T.name | Author(T) AND T.article = 'database' }
Output: This query selects the tuples from the AUTHOR relation. It returns a tuple
with 'name' from Author who has written an article on 'database'.
 TRC (tuple relation calculus) can be quantified. In TRC, we can use Existential (∃) and
Universal Quantifiers (∀).

1. { R| ∃T ∈ Authors(T.article='database' AND R.name=T.name)}

For example:

Output: This query will yield the same result as the previous one.
2. Domain Relational Calculus (DRC)
The second form of relation is known as Domain relational calculus. In domain

calculus uses the same operators as tuple calculus. It uses logical connectives ∧ (and),
relational calculus, filtering variable uses the domain of attributes. Domain relational

∨ (or) and ┓ (not). It uses Existential (∃) and Universal Quantifiers (∀) to bind the
variable. The QBE or Query by example is a query language related to domain
relational calculus.
Notation:
1. { a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}
Where
a1, a2 are attributes
P stands for formula built by inner attributes

1. {< article, page, subject > | ∈ javatpoint ∧ subject = 'database'}

For example:

Output: This query will yield the article, page, and subject from the relational
javatpoint, where the subject is a database.
Relational calculus is a non-procedural query language, and instead of algebra, it uses
mathematical predicate calculus. The relational calculus is not the same as that of
differential and integral calculus in mathematics but takes its name from a branch of
symbolic logic termed as predicate calculus. When applied to databases, it is found in
two forms. These are
 Tuple relational calculus which was originally proposed by Codd in the year
1972 and
 Domain relational calculus which was proposed by Lacroix and Pirotte in the
year 1977
In first-order logic or predicate calculus, a predicate is a truth-valued function with
arguments. When we replace with values for the arguments, the function yields an
expression, called a proposition, which will be either true or false.
For example, steps involved in listing all the employees who attend the 'Networking'
Course would be:
SELECT the tuples from COURSE relation with COURSENAME = 'NETWORKING'
PROJECT the COURSE_ID from above result
SELECT the tuples from EMP relation with COURSE_ID resulted above.
In this chapter, you will learn about the relational calculus and its concept about the
database management system. A certain arrangement is explicitly stated in relational
algebra expression, and a plan for assessing the query is implied. In the relational
calculus, there is no description and depiction of how to assess a query; Instead, a
relational calculus query focuses on what is to retrieve rather than how to retrieve it.
What is Relational Calculus?
Relational calculus is a non-procedural query language, and instead of algebra, it uses
mathematical predicate calculus. The relational calculus is not the same as that of
differential and integral calculus in mathematics but takes its name from a branch of
symbolic logic termed as predicate calculus. When applied to databases, it is found in
two forms. These are
 Tuple relational calculus which was originally proposed by Codd in the year
1972 and
 Domain relational calculus which was proposed by Lacroix and Pirotte in the
year 1977
In first-order logic or predicate calculus, a predicate is a truth-valued function with
arguments. When we replace with values for the arguments, the function yields an
expression, called a proposition, which will be either true or false.
Example:
For example, steps involved in listing all the employees who attend the 'Networking'
Course would be:
SELECT the tuples from COURSE relation with COURSENAME = 'NETWORKING'
PROJECT the COURSE_ID from above result
SELECT the tuples from EMP relation with COURSE_ID resulted above.

Tuple Relational Calculus

In the tuple relational calculus, you will have to find tuples for which a predicate is
true. The calculus is dependent on the use of tuple variables. A tuple variable is a
variable that 'ranges over' a named relation: i.e., a variable whose only permitted values
are tuples of the relation.
Example:
For example, to specify the range of a tuple variable S as the Staff relation, we write:
Staff(S)
To express the query 'Find the set of all tuples S such that F(S) is true,' we can write:
{S | F(S)}
Here, F is called a formula (well-formed formula, or wff in mathematical logic). For
example, to express the query 'Find the staffNo, fName, lName, position, sex, DOB,

{S | Staff(S) ∧ S.salary > 10000}

salary, and branchNo of all staff earning more than £10,000', we can write:

{t | TEACHER (t) and t.SALARY>20000}

- It implies that it selects the tuples from the TEACHER in such a way that the resulting
teacher tuples will have a salary higher than 20000. This is an example of selecting a
range of values.
{t | TEACHER (t) AND t.DEPT_ID = 6}
- T select all the tuples of teachers' names who work under Department 8. Any tuple
variable with 'For All' (?) or 'there exists' (?) condition is termed as a bound variable.
In the last example, for any range of values of SALARY greater than 20000, the
meaning of the condition does not alter. Bound variables are those ranges of tuple
variables whose meaning will not alter if another tuple variable replaces the tuple
variable.
In the second example, you have used DEPT_ID= 8, which means only for DEPT_ID =
8 display the teacher details. Such a variable is called a free variable. Any tuple variable
without any 'For All' or 'there exists' condition is called Free Variable.
Domain Relational Calculus
In the tuple relational calculus, you have use variables that have a series of tuples in a
relation. In the domain relational calculus, you will also use variables, but in this case,
the variables take their values from domains of attributes rather than tuples of relations.
A domain relational calculus expression has the following general format:
{d1, d2, . . . , dn | F(d1, d2, . . . , dm)} m ≥ n
where d1, d2, . . . , dn, . . . , dm stand for domain variables and F(d1, d2, . . . , dm)
stands for a formula composed of atoms.
Example:
select TCHR_ID and TCHR_NAME of teachers who work for department 8, (where
suppose - dept. 8 is Computer Application Department)
{<tchr_id, tchr_name=""> | <tchr_id, tchr_name=""> ? TEACHER Λ DEPT_ID = 10}
Get the name of the department name where Karlos works:
{DEPT_NAME |< DEPT_NAME > ? DEPT Λ ? DEPT_ID ( ? TEACHER Λ
TCHR_NAME = Karlos)}
It is to be noted that these queries are safe. The use domain relational calculus is
restricted to safe expressions; moreover, it is equivalent to the tuple relational calculus,
which in turn is similar to the relational algebra.

Introduction to SQL
 o
SQL stands for Structured Query Language. It is used for storing and managing
data in relational database management system (RDMS). In RDBMS data stored
in the form of the tables.
o It is a standard language for Relational Database System. It enables a user to
create, read, update and delete relational databases and tables.
o All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server
use SQL as their standard database language.
o SQL allows users to query the database in a number of ways, using English-like
statements.
o SQL is mostly used by engineers in software development for data storage.
Nowadays, it is also used by data analyst for following reason:
SQL Statement Rules:
SQL follows the following rules:
o Structure query language is not case sensitive. Generally, keywords of SQL are
written in uppercase.
o Every SQL statements should ends with a semicolon.
o Statements of SQL are dependent on text lines. We can use a single SQL
statement on one or multiple text line.
o Using the SQL statements, you can perform most of the actions in a database.
o SQL depends on tuple relational calculus and relational algebra.
How does SQL process works:
o When an SQL command is executing for any RDBMS, then the system figure
out the best way to carry out the request and the SQL engine determines that
how to interpret the task.
o In the process, various components are included. These components can be
optimization Engine, Query engine, Query dispatcher, classic, etc.
o All the non-SQL queries are handled by the classic query engine, but SQL
query engine won't handle logical files.

SQL Query Execution Order

In the above diagrammatic representation following steps are performed:

o Parsing: In this process, Query statement is tokenized.
o Optimizing: In this process, SQL statement optimizes the best algorithm for
byte code.
 o From: In SQL statement, from keyword is used to specify the tables from
which data fetched.
o Where: Where keyword works like conditional statement in SQL.
o Join: A Join statement is used to combine data from more than one tables based
on a common field among them.
o Group by: It is used to group the fields by different records from table(s).
o Having: Having clause is also works like conditional statement in SQL. It is
mostly used with group by clause to filter the records.
o Order by: This clause is used to sort the data in particular order by using
"ASC" for ascending and "DESC" for descending order.
o Select: This "Data Manipulation Language" statement is used to get the data
from the database.
o Limit: It is used to specify the how many rows returned by the SQL select
statement.

Characteristics of SQL

o SQL is easy to learn.

o SQL is used to access data from relational database management systems.
o SQL can execute queries against the database.
o SQL is used to describe the data.
o SQL is used to define the data in the database and manipulate it when needed.
o SQL is used to create and drop the database and table.
o SQL is used to create a view, stored procedure, function in a database.
o SQL allows users to set permissions on tables, procedures, and views.
Advantages of SQL
There are the following advantages of SQL:
High speed
Using the SQL queries, the user can quickly and efficiently retrieve a large amount of
records from a database.
No coding needed
In the standard SQL, it is very easy to manage the database system. It doesn't require a
substantial amount of code to manage the database system.
Well defined standards
Long established are used by the SQL databases that are being used by ISO and ANSI.
Portability
SQL can be used in laptop, PCs, server and even some mobile phones.
Interactive language
SQL is a domain language used to communicate with the database. It is also used to
receive answers to the complex questions in seconds.
Multiple data view
Using the SQL language, the users can make different views of the database structure.
SQL Datatype
o SQL Datatype is used to define the values that a column can contain.
o Every column is required to have a name and data type in the database table.
 Datatype of SQL:

1. Binary Datatypes
There are Three types of binary Datatypes which are given below:
Data Type Description

binary It has a maximum length of 8000 bytes. It contains fixed-length binary data.

varbinary It has a maximum length of 8000 bytes. It contains variable-length binary data.

image It has a maximum length of 2,147,483,647 bytes. It contains variable-length binary data.
2. Approximate Numeric Datatype :
The subtypes are given below:
Data type From To Description

It is used to specify a floating-point value

float -1.79E + 308 1.79E + 308
e.g. 6.2, 2.9 etc.

real -3.40e + 38 3.40E + 38 It specifies a single precision floating point number

3. Exact Numeric Datatype

The subtypes are given below:

Data type Description

int It is used to specify an integer value.

smallint It is used to specify small integer value.

bit It has the number of bits to store.

decimal It specifies a numeric value that can have a decimal number.

numeric It is used to specify a numeric value.

4. Character String Datatype

The subtypes are given below:
 Data type Description

It has a maximum length of 8000 characters. It contains Fixed-length non-unicode

char
characters.

It has a maximum length of 8000 characters. It contains variable-length non-unicode

varchar
characters.

It has a maximum length of 2,147,483,647 characters. It contains variable-length

text
non-unicode characters.
5. Date and time Datatypes
The subtypes are given below:
Datatype Description

date It is used to store the year, month, and days value.

time It is used to store the hour, minute, and second values.

timestamp It stores the year, month, day, hour, minute, and the second value.

SQL Commands
o SQL commands are instructions. It is used to communicate with the database. It
is also used to perform specific tasks, functions, and queries of data.
o SQL can perform various tasks like create a table, add data to tables, drop the
table, modify the table, set permission for users.
Types of SQL Commands
There are four types of SQL commands: DDL, DML, DCL, TCL.

1. Data Definition Language (DDL)

o DDL changes the structure of the table like creating a table, deleting a table,
altering a table, etc.
o All the command of DDL are auto-committed that means it permanently save
all the changes in the database.
Following are the some commands that come under DDL:
 a. CREATE It is used to create a new table in the database.
Syntax:
1. CREATE TABLE TABLE_NAME (COLUMN_NAMES DATATYPES [ ...]);
In above statement, TABLE_NAME is the name of the table, COLUMN_NAMES is
the name of the columns and DATATYPES is used to define the type of data.
Example:
1. CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), D
OB DATE);
b. DROP: It is used to delete both the structure and record stored in the table.
Syntax: To DROP a table permanently from memory
1. DROP TABLE table_name [cascade constraint];
The cascade constraint is an optional parameter which is used for tables which have
foreign keys that reference the table being dropped. If cascade constraint is not
specified and used attempt to drop a table that has records in a child table, then an error
will occur. So by using cascade constraints, all child table foreign keys are dropped.
Example
1. DROP TABLE EMPLOYEE;
c. ALTER: It is used to alter the structure of the database. This change could be either
to modify the characteristics of an existing attribute or probably to add a new attribute.

Following are the list of modifications that can be done using ALTER command.
o With the use of ALTER commands we can add or drop one or more columns
form existing tables.
o Increase or decrease the existing column width by changing the data type
o Make an existing mandatory column to optional.
o Enable or disable the integrity constraints in a table. We can also add, modify or
delete the integrity constraints from a table.
o We can also specify a default value for existing column in a table.
Adding new columns in Table:
With the use of ALTER table command we can add new columns existing table.
Syntax: To add a new column in the table
1. ALTER TABLE table_name ADD column_name column-definition;
In the above syntax, where table_name corresponds to the name of the table, column-
definition corresponds to the valid specifications for a column name and data type.
EXAMPLE:
1. ALTER TABLE STU_DETAILS ADD (ADHAR_NUM VARCHAR2 (15));
Syntax: To ADD a multiple column from a table.
ALTER TABLE table_name ADD column_name1, column_name2;
Example:
1. ALTER TABLE STU_DETAILS ADD ADHAR_NUM, NAME;
Adding constraints in a Table:
 You can also add constraints to an existing table. For example: If you forget to add a
primary key constraint to a table during creation, you can add it using the ALTER
TABLE statement.
Syntax: To ADD a constraint from a table.
1. ALTER TABLE table_name ADD (column_name column-definition CONSTRAINT c
onstraint_name);
Example:
1. ALTER TABLE STU_DETAILS ADD (CONSTRAINT PK_STU_DETAILS PRIMA
RY KEY (STU_ID);
Following points should be kept in mind while adding new columns/relationships
to existing tables.
o No need for parentheses if you add only one column or constraints.
o You can add a column at any time if NULL is not specified. You can add a new
column with NOT NULL if the table is empty.

Modifying Column using ALTER:

With the use of ALTER table we can modify column and constraint in the existing
table. These statements can increase or decrease the column widths and changing a
column from mandatory to optional.
Syntax:
1. ALTER TABLE table_name MODIFY (column definitions....);
Example:
1. ALTER TABLE STU_DETAILS MODIFY (ADHAR_NUM VARCHAR2 (20));
SQL does not allow column widths to be reduced even if all column values are of valid
length. So the values should be set to NULL to reduce the width of the columns. It is
also not possible to reduce the width of the ADHAR_NUM column from 18 to 12 even
if all values in the ADHAR_NUM column are less than 12 characters, unless all al
values in the name column are null. You can modify the column form NULL to
NOTNULL constraints if there is no record in that column in the table.
Example:
1. ALTER TABLE STU_DETAILS MODIFY (ADHAR_NUM VARCHAR2 (20) NOT
NULL);
Drop column and constraints using ALTER
You cannot only modify columns but you can also drop them entirely if it is no longer
required in a table. Using drop statement in alter command we can also remove the
constraints form the table.
Syntax: To drop a column from a table.
1. ALTER TABLE table_name DROP COLUMN column_name;
Example:
1. ALTER TABLE STU_DETAILS DROP COLUMN ADHAR_NUM;
Syntax: To drop a multiple column from a table.
1. ALTER TABLE table_name DROP COLUMN column_name1, column_name2;
Example:
1. ALTER TABLE STU_DETAILS DROP COLUMN ADHAR_NUM, NAME;
Syntax: To drop a constraint from a table.
1. ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Example:
1. ALTER TABLE STU_DETAILS DROP CONSTRAINT FK_STU_DETAILS;
Following points should be kept in mind while deleting columns/associations:
o You cannot drop columns in a table. If you want to drop a column from a table,
the deletion is permanent so you cannot undo the column if you accidentally
drop the wrong column.
o You cannot drop a column whose username is SYS.
o If you want to drop a primary key column unless you drop the foreign keys that
belong to it then use cascade keyword for this.
 Example:
1. ALTER TABLE STU_DETAILS DROP PRIMARY KEY CASCADE;
o You can also enable or disable the key constraint in a table. It can be done in
various situations such as: when loading large amount of data into table,
performing batch operations, migrating the organizations legacy data.
Example: To disable constraint
1. ALTER TABLE STU_DETAILS DISABLE CONSTRAINT FK_STU_DETAILS;
Example: To Enable constraint
1. ALTER TABLE STU_DETAILS ENABLE CONSTRAINT FK_STU_DETAILS;
o Instead of dropping a column in a table, we can also make the column unused
and drop it later on. It makes the response time faster. After a column has been
marked as unused, the column and all its contents are no longer available and
cannot be recovered in the future. The unused columns will not be retrieved
using Select statement
Example:
1. ALTER TABLE STU_DETAILS SET UNUSED COLUMN ADHAR_NUM;
RENAMING TABLE
SQL provides the facility to change the name of the table by using a ALTER TABLE
statement.
Syntax:
1. ALTER TABLE <OLD_TABLENAME> Rename to <NEW_TABLENAME>;
Example:
1. ALTER TABLE STU_NAME Rename to STUDENT_NAME;
d. TRUNCATE: It is used to delete all the rows from the table and free the space
containing the table.
Syntax:
1. TRUNCATE TABLE table_name;
Example:
1. TRUNCATE TABLE EMPLOYEE;
e. Rename: It is used to rename the table.

Syntax:
1. Rename <OLD_TABLENAME> to <NEW_TABLENAME>;
In the above syntax, Rename is a command, <OLD_TABLENAME> is the name of the
table and <NEW_TABLENAME> is the name that you have changed.
Example:
1. Rename STU_NAME to STUDENT_NAME;
2. Data Manipulation Language
o DML commands are used to modify the database. It is responsible for all form
of changes in the database.
o The command of DML is not auto-committed that means it can't permanently
save all the changes in the database. They can be rollback.
Following are the some commands that come under DML:
 a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the
row of a table. To insert a new row into a table you must be your on schema or
INSERT privilege on the table.
Following are the list of points should be considered while inserting the data into
tables.
o SQL uses all the columns by default if you do not specify the column name
while inserting a row.
o The number of columns in the list of column name must match the number of
values that appear in parenthesis after the word "values".
o The data type for a column and its corresponding value must match.
Syntax: To add row in a table
1. INSERT INTO TABLE_NAME
2. (col1, col2, col3,.... col N)
3. VALUES (value1, value2, value3, .... valueN);
Or
1. INSERT INTO TABLE_NAME
2. VALUES (value1, value2, value3, .... valueN);
In the above syntax, TABLE_NAME is the name of the table in which the data will be
inserted. The (col1, col2, col3, col N) are optional and name of the columns in which
values will be inserted. The value1 corresponds to the value of be inserted in col1 and
similarly value2 corresponds to the value of be inserted in col2 and so on.
For example:
1. INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");
Syntax: To add multiple rows in a table
1. INSERT INTO TABLE_NAME
2. (col1, col2, col3,.... col N)
3. VALUES (value1, value2, value3, .... valueN), (value1, value2, value3, .... valueN);
For example:
1. INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS"), ("Raman",
"DBMS"), ("Priya", "DBMS");
b. UPDATE: This command is used to update or modify the value of a column in the
table.
Syntax: To update record in a table
1. UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WH
ERE CONDITION]
In the above syntax, table_name is the name of the table, the column_name is the name
of column in the table to be modified, and value1 corresponds to the valid SQL values.
The "WHERE" is a condition that restricts the rows updated for which the specified
condition is true. If condition is not specified is not defined then SQL updates all the
rows in the table. It contains comparison and logical operators etc.
The following the list of points should be remembered while executing the
UPDATE statement.
o It references only one table.
o In the SET clause atleast one column must be assigned an expression for the
update statement,
o In the where clause you could also give multiple conditions for update
statement.
For example:
1. UPDATE students
2. SET User_Name = 'Sonoo'
3. WHERE Student_Id = '3'
c. DELETE: It is used to remove one or more row from a table. To delete rows from
the table, it must be in your schema or you must have delete privilege.
Syntax: To Delete a record from table
1. DELETE FROM table_name [WHERE condition];
 In the above syntax, condition is used in the where clause to filter the records that are
actually being removed. You can remove zero or more rows from a table. If you do not
use where condition then DELETE statement will remove all the rows from the table.
You can also use one or multiple conditions in WHERE clause.

For example:
1. DELETE FROM javatpoint
2. WHERE Author="Sonoo";
d. SELECT: This is the same as the projection operation of relational algebra. It is
used to select the attribute based on the condition described by WHERE clause.
Syntax: It is used for retrieve the records from table
1. SELECT expressions
2. FROM TABLES
3. WHERE conditions;
For example:
1. SELECT emp_name
2. FROM employee
3. WHERE age > 20;
3. Data Control Language
DCL commands are used to grant and take back authority from any database user.
Following are the some commands that come under DCL:

Syntax:
1. GRANT <obj_priv> ON <obj_name> To <username>;
In the above syntax, obj_priv> is the DML statement like Insert, Delete , update and
Select and <obj_name> is a table, view etc. and username is the name of the authorized
user.
Example
1. GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER
;
b. Revoke: It is used to take back permissions from the user.
Syntax:
1. REVOKE <obj_priv> ON <obj_name> FROM <username>;
In the above syntax, obj_priv> is the DML statement like Insert, Delete , update and
Select and <obj_name> is a table, view etc. and username is the name of the user from
whom the permission is revoked.
Example
1. REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
4. Transaction Control Language
Transactions are atomic i.e. either every statement succeeds or none of statement
succeeds. There are number of Transaction Control statements available that allow us to
control this behavior. These statements ensure data consistency. TCL commands can
only use with DML commands like INSERT, DELETE and UPDATE only.
These operations are automatically committed in the database that's why they cannot be
used while creating tables or dropping them.
 Following are the some commands that come under TCL:

a. Commit: Commit command is used to save all the transactions to the database. It
makes your changes permanent and ends the transaction.
Syntax: To permanently save the changes
1. COMMIT;
Example:
1. DELETE FROM CUSTOMERS
2. WHERE AGE = 25;
3. COMMIT;
b. Rollback: Rollback command is used to undo transactions that have not already
been saved to the database. Rollback also serves to end the current transaction and
begin a new one.
Consider a Situation where you have completed a series of INSERT, UPDATE or
DELETE statements but have not yet explicitly committed them and yiu encounter a
problem such as computer failure, then SQL will automatically rollback any
uncommitted work.
Syntax: To remove the changes
1. ROLLBACK;
Example:
1. DELETE FROM CUSTOMERS
2. WHERE AGE = 25;
3. ROLLBACK;
c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling
back the entire transaction.
Syntax:
1. SAVEPOINT SAVEPOINT_NAME;
In the above syntax, SAVEPOINT_NAME is the name given to savepoint.
To selectively ROLLBACK a group of statements within a large transaction use the
following command is used.
1. Rollback TO <save_point_name>
Example:
1. DELETE FROM CUSTOMERS WHERE AGE = 15;
2. SAVEPOINT A;
3. DELETE FROM CUSTOMERS WHERE AGE = 35;
4. ROLLBCAK TO A;
Multiple Choice Questions on SQL commands:
1. Which command is used to display the records in a table?
1. Create table tablename;
2. Delete from tablename;
3. Select * from tablename;
4. Rename table tablename;
Show Answer Workspace
2. What does the following statement perform in a table?
Update student set name = "meet" where roll_no =101;
 1. It will not update the name in student table.
2. It will update all the values of name column.
3. It will update the value of name column with "meet".
4. It will update the value of name column with "meet" where roll_no =101.
Show Answer Workspace
3. Which of the following is not a Transaction Control Language in a SQL?
1. Delete
2. Save point
3. Commit
4. Rollback
Show Answer Workspace
4. Which command is used to add a column in existing table?
1. Alter table table_name ADD column_name data-type;
2. Alter table table_name column_name data-type;
3. Alter table table_name ADD;
4. Alter table ADD column_name data-type;

Show Answer Workspace

5. What does the following statement perform in a table?
Insert into student (name,roll_no) values( "meet",101);
1. It will insert the value of rollno in a table.
2. It will insert the values of name, roll_no column in table.
3. It will insert the value of name in a table.
4. All of the above.
Show Answer Workspace
6. Which command is used to maintain the structure of table after deleting data in
a table?
1. Alter
2. Delete
3. Drop
4. Truncate
Show Answer Workspace
7. Which command is used to rename a column in a table?
1. Alter table old_table_name Rename to new_table_name ;
2. Rename old_table_name to new_table_name ;
3. Both of the above
4. None of the above
SQL Operator
There are various types of SQL operator:
SQL Arithmetic Operators
Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.
Operator Description Example

It adds the value of both

+ a+b will give 30
operands.

It is used to subtract the right-

- hand operand from the left-hand a-b will give 10
operand.

It is used to multiply the value

* a*b will give 200
of both operands.

It is used to divide the left-hand

/ operand by the right-hand a/b will give 2
operand.

It is used to divide the left-hand

% operand by the right-hand a%b will give 0
operand and returns reminder.
SQL Comparison Operators:
Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.
Operator Description Example

It checks if two operands values are equal or not, if

= (a=b) is not true
the values are queal then condition becomes true.

It checks if two operands values are equal or not, if

!= (a!=b) is true
values are not equal, then condition becomes true.

It checks if two operands values are equal or not, if

<> (a<>b) is true
values are not equal then condition becomes true.

It checks if the left operand value is greater than

> right operand value, if yes then condition becomes (a>b) is not true
true.

It checks if the left operand value is less than right

< (a<b) is true
operand value, if yes then condition becomes true.

It checks if the left operand value is greater than or

>= equal to the right operand value, if yes then (a>=b) is not true
condition becomes true.

It checks if the left operand value is less than or

<= equal to the right operand value, if yes then (a<=b) is true
condition becomes true.

It checks if the left operand value is not less than

!< the right operand value, if yes then condition (a!=b) is not true
becomes true.

!> It checks if the left operand value is not greater (a!>b) is true
than the right operand value, if yes then condition
 becomes true.
SQL Logical Operators
There is the list of logical operator used in SQL:
Operator Description

ALL It compares a value to all values in another value set.

It allows the existence of multiple conditions in an SQL

AND
statement.

ANY It compares the values in the list according to the condition.

BETWEEN It is used to search for values that are within a set of values.

IN It compares a value to that specified list value.

NOT It reverses the meaning of any logical operator.

OR It combines multiple conditions in SQL statements.

EXISTS It is used to search for the presence of a row in a specified table.

LIKE It compares a value to similar values using wildcard operator.

SQL Table
o SQL Table is a collection of data which is organized in terms of rows and
columns. In DBMS, the table is known as relation and row as a tuple.
 o Table is a simple form of data storage. A table is also considered as a
convenient representation of relations.

Let's see an example of the EMPLOYEE table:

EMP_ID EMP_NAME CITY PHONE_NO

1 Kristen Washington 7289201223

2 Anna Franklin 9378282882

3 Jackson Bristol 9264783838

4 Kellan California 7254728346

5 Ashley Hawaii 9638482678

In the above table, "EMPLOYEE" is the table name, "EMP_ID", "EMP_NAME",
"CITY", "PHONE_NO" are the column names. The combination of data of multiple
columns forms a row, e.g., 1, "Kristen", "Washington" and 7289201223 are the data of
one row.
Operation on Table
1. Create table
2. Drop table
3. Delete table
4. Rename table
SQL Create Table
SQL create table is used to create a table in the database. To define the table, you
should define the name of the table and also define its columns and column's data type.
Syntax
1. ("column1" "data type",
2. "column2" "data type",
3. "column3" "data type",
4. ...
5. "columnN" "data type");
Example
1. SQL> CREATE TABLE EMPLOYEE (
2. EMP_ID INT NOT NULL,
3. EMP_NAME VARCHAR (25) NOT NULL,
4. PHONE_NO INT NOT NULL,
5. ADDRESS CHAR (30),
6. PRIMARY KEY (ID)
7. );
If you create the table successfully, you can verify the table by looking at the message
by the SQL server. Else you can use DESC command as follows:
SQL> DESC EMPLOYEE;
Field Type Null Key Default Extra

EMP_ID int(11) NO PRI NULL

EMP_NAME varchar(25) NO NULL

PHONE_NO NO int(11) NULL

ADDRESS YES NULL char(30)

 o 4 rows in set (0.35 sec)
Now you have an EMPLOYEE table in the database, and you can use the stored
information related to the employees.

Drop table
A SQL drop table is used to delete a table definition and all the data from a table. When
this command is executed, all the information available in the table is lost forever, so
you have to very careful while using this command.
Syntax
1. DROP TABLE "table_name";
Firstly, you need to verify the EMPLOYEE table using the following command:
1. SQL> DESC EMPLOYEE;

Field Type Null Key Default Extra

EMP_ID int(11) NO PRI NULL

EMP_NAME varchar(25) NO NULL

PHONE_NO NO int(11) NULL

ADDRESS YES NULL char(30)

o 4 rows in set (0.35 sec)

This table shows that EMPLOYEE table is available in the database, so we can drop it
as follows:
1. SQL>DROP TABLE EMPLOYEE;
Now, we can check whether the table exists or not using the following command:
1. Query OK, 0 rows affected (0.01 sec)
As this shows that the table is dropped, so it doesn't display it.
SQL DELETE table
In SQL, DELETE statement is used to delete rows from a table. We can use WHERE
condition to delete a specific row from a table. If you want to delete all the records
from the table, then you don't need to use the WHERE clause.
Syntax
1. DELETE FROM table_name WHERE condition;
Example
Suppose, the EMPLOYEE table having the following records:
EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Denzel Boston 7353662627 100000

4 Angelina Denver 9232673822 600000

5 Robert Washington 9367238263 350000

 6 Christian Los angels 7253847382 260000
The following query will DELETE an employee whose ID is 2.
1. SQL> DELETE FROM EMPLOYEE
2. WHERE EMP_ID = 3;
Now, the EMPLOYEE table would have the following records.
EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

4 Angelina Denver 9232673822 600000

5 Robert Washington 9367238263 350000

6 Christian Los angels 7253847382 260000

If you don't specify the WHERE condition, it will remove all the rows from the table.
1. DELETE FROM EMPLOYEE;
Now, the EMPLOYEE table would not have any records.
SQL SELECT Statement
In SQL, the SELECT statement is used to query or retrieve data from a table in the
database. The returns data is stored in a table, and the result table is known as result-set.
Syntax
1. SELECT column1, column2, ...
2. FROM table_name;
Here, the expression is the field name of the table that you want to select data from.
Use the following syntax to select all the fields available in the table:
1. SELECT * FROM table_name;
Example:

EMPLOYEE
EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Angelina Denver 9232673822 600000

4 Robert Washington 9367238263 350000

5 Christian Los angels 7253847382 260000

To fetch the EMP_ID of all the employees, use the following query:
1. SELECT EMP_ID FROM EMPLOYEE;

Output
EMP_ID
 1

5
To fetch the EMP_NAME and SALARY, use the following query:

1. SELECT EMP_NAME, SALARY FROM EMPLOYEE;

EMP_NAME SALARY

Kristen 150000

Russell 200000

Angelina 600000

Robert 350000

Christian 260000
To fetch all the fields from the EMPLOYEE table, use the following query:
1. SELECT * FROM EMPLOYEE
Output
EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Angelina Denver 9232673822 600000

4 Robert Washington 9367238263 350000

5 Christian Los angels 7253847382 260000

SQL INSERT Statement

The SQL INSERT statement is used to insert a single or multiple data in a table. In
SQL, You can insert the data in two ways:
1. Without specifying column name
2. By specifying column name

Sample Table
EMPLOYEE
EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

 3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

1. Without specifying column name
If you want to specify all column values, you can specify or ignore the column values.
Syntax
1. INSERT INTO TABLE_NAME
2. VALUES (value1, value2, value 3, .... Value N);
Query
1. INSERT INTO EMPLOYEE VALUES (6, 'Marry', 'Canada', 600000, 48);
Output: After executing this query, the EMPLOYEE table will look like:
EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

2. By specifying column name
To insert partial column values, you must have to specify the column names.
Syntax
1. INSERT INTO TABLE_NAME
2. [(col1, col2, col3,.... col N)]
3. VALUES (value1, value2, value 3, .... Value N);

Query
1. INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, AGE) VALUES (7, 'Jack', 40);
Output: After executing this query, the table will look like:
EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

7 Jack null null 40

 Note: In SQL INSERT query, if you add values for all columns then there is no need to
specify the column name. But, you must be sure that you are entering the values in the
same order as the column exists.
SQL Update Statement
The SQL UPDATE statement is used to modify the data that is already in the database.
The condition in the WHERE clause decides that which row is to be updated.
Syntax
1. UPDATE table_name
2. SET column1 = value1, column2 = value2, ...
3. WHERE condition;
Sample Table
EMPLOYEE
EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Updating single record
Update the column EMP_NAME and set the value to 'Emma' in the row where
SALARY is 500000.
Views in SQL
o Views in SQL are considered as a virtual table. A view also contains rows and
columns.
o To create the view, we can select the fields from one or more tables present in
the database.
o A view can either have specific rows based on certain condition or all the rows
of a table.
Advantages of View:
1. Complexity: Views help to reduce the complexity. Different views can be
created on the same base table for different users.
2. Security: It increases the security by excluding the sensitive information from
the view.
3. Query Simplicity: It helps to simplify commands from the user. A view can
draw data from several different tables and present it as a single table.
4. Consistency: A view can present a consistent, unchanged image of the structure
of the database. Views can be used to rename the columns without affecting the
base table.
5. Data Integrity: If data is accessed and entered through a view, the DBMS can
automatically check the data to ensure that it meets the specified integrity
constraints.
6. Storage Capacity: Views take very little space to store the data.
7. Logical Data Independence: View can make the application and database
tables to a certain extent independent.
Disadvantages of View:
The DML statements which can be performed on a view created using single base table
have certain restrictions are:
 1. You cannot INSERT if the base table has any not null column that do not appear
in view.
2. You cannot INSERT or UPDATE if any of the column referenced in the
INSERT or UPDATE contains group functions or columns defined by
expression.
3. You can't execute INSERT, UPDATE, DELETE statements on a view if with
read only option is enabled.
4. You can't be created view on temporary tables.
5. You cannot INSERT, UPDATE, DELETE if the view contains group functions
GROUP BY, DISTINCT or a reference to a psuedocolumn rownum.
6. You can't pass parameters to the SQL server views.
7. You can't associate rules and defaults with views.
Student_Detail
STU_ID NAME ADDRESS

1 Stephan Delhi

2 Kathrin Noida

3 David Ghaziabad

4 Alina Gurugram
Student_Marks
STU_ID NAME MARKS AGE

1 Stephan 97 19

2 Kathrin 86 21

3 David 74 18

4 Alina 90 20

5 John 96 18
1. Creating view
A view can be created using the CREATE VIEW statement. We can create a view
from a single table or multiple tables.
Syntax:
1. CREATE VIEW view_name AS
2. SELECT column1, column2.....
3. FROM table_name
4. WHERE condition;
2. Creating View from a single table
In this example, we create a View named DetailsView from the table Student_Detail.
Query:
1. CREATE VIEW DetailsView AS
2. SELECT NAME, ADDRESS
3. FROM Student_Details
4. WHERE STU_ID < 4;
Just like table query, we can query the view to view the data.
1. SELECT * FROM DetailsView;
Output:
NAME ADDRESS
 Stephan Delhi

Kathrin Noida

David Ghaziabad
3. Creating View from multiple tables
View from multiple tables can be created by simply include multiple tables in the
SELECT statement.
In the given example, a view is created named MarksView from two tables
Student_Detail and Student_Marks.
Query:
1. CREATE VIEW MarksView AS
2. SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS

3. FROM Student_Detail, Student_Mark

4. WHERE Student_Detail.NAME = Student_Marks.NAME;
To display data of View MarksView:
1. SELECT * FROM MarksView;
NAME ADDRESS MARKS

Stephan Delhi 97

Kathrin Noida 86

David Ghaziabad 74

Alina Gurugram 90
4. Deleting View
A view can be deleted using the Drop View statement.
Syntax
1. DROP VIEW view_name;

Example:
If we want to delete the View MarksView, we can do this as:
1. DROP VIEW MarksView;
Significance of Views:
Views are highly significant, as they can provide advantages over tasks. Views can
represent a subset of data contained in a table. Consequently they can limit the degree
of exposure of the underlying base table to the outer world. They are used for security
purpose in database and act as an intermediate between real table schemas and
programmability. They act as aggregate tables.
Types of Views:
There are two types of views.

1. Join View: A join view is a view that has more than one table or view in its
from clause and it does not use any Group by Clause, Rownum, Distinct and set
operation.
 2. Inline View: An inline view is a view which is created by replacing a subquery
in the from clause which defines the data source that can be referenced in the
main query. The sub query must be given an alias for efficient working.
SQL Index
o Indexes are special lookup tables. It is used to retrieve data from the database
very fast.
o An Index is used to speed up select queries and where clauses. But it shows
down the data input with insert and update statements. Indexes can be created or
dropped without affecting the data.
o An index in a database is just like an index in the back of a book.
o For example: When you reference all pages in a book that discusses a certain
topic, you first have to refer to the index, which alphabetically lists all the topics
and then referred to one or more specific page numbers.
1. Create Index statement
It is used to create an index on a table. It allows duplicate value.
Syntax
1. CREATE INDEX index_name
2. ON table_name (column1, column2, ...);
Example
1. CREATE INDEX idx_name
2. ON Persons (LastName, FirstName);
2. Unique Index statement
It is used to create a unique index on a table. It does not allow duplicate value.
Syntax
1. CREATE UNIQUE INDEX index_name
2. ON table_name (column1, column2, ...);
Example
1. CREATE UNIQUE INDEX websites_idx
2. ON websites (site_name);
3. Drop Index Statement
It is used to delete an index in a table.
Syntax
1. DROP INDEX index_name;
Example
1. DROP INDEX websites_idx;
SQL Sub Query
A Subquery is a query within another SQL query and embedded within the WHERE
clause.
Important Rule:
o A subquery can be placed in a number of SQL clauses like WHERE clause,
FROM clause, HAVING clause.
o You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements
along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
o A subquery is a query within another query. The outer query is known as the
main query, and the inner query is known as a subquery.
o Subqueries are on the right side of the comparison operator.
o A subquery is enclosed in parentheses.
o In the Subquery, ORDER BY command cannot be used. But GROUP BY
command can be used to perform the same function as ORDER BY command.
1. Subqueries with the Select Statement
SQL subqueries are most frequently used with the Select statement.
Syntax
1. SELECT column_name
2. FROM table_name
3. WHERE column_name expression operator
4. ( SELECT column_name from table_name WHERE ... );
 Example
Consider the EMPLOYEE table have the following records:
ID NAME AGE ADDRESS SALARY

1 John 20 US 2000.00

2 Stephan 26 Dubai 1500.00

3 David 27 Bangkok 2000.00

4 Alina 29 UK 6500.00

5 Kathrin 34 Bangalore 8500.00

6 Harry 42 China 4500.00

7 Jackson 25 Mizoram 10000.00

The subquery with a SELECT statement will be:
1. SELECT *
2. FROM EMPLOYEE
3. WHERE ID IN (SELECT ID
4. FROM EMPLOYEE
5. WHERE SALARY > 4500);
This would produce the following result:
ID NAME AGE ADDRESS SALARY

4 Alina 29 UK 6500.00

5 Kathrin 34 Bangalore 8500.00

7 Jackson 25 Mizoram 10000.00

2. Subqueries with the INSERT Statement
o SQL subquery can also be used with the Insert statement. In the insert
statement, data returned from the subquery is used to insert into another table.
o In the subquery, the selected data can be modified with any of the character,
date functions.
Syntax:
1. INSERT INTO table_name (column1, column2, column3....)
2. SELECT *
3. FROM table_name
4. WHERE VALUE OPERATOR
Example
Consider a table EMPLOYEE_BKP with similar as EMPLOYEE.
Now use the following syntax to copy the complete EMPLOYEE table into the
EMPLOYEE_BKP table.
1. INSERT INTO EMPLOYEE_BKP
2. SELECT * FROM EMPLOYEE
3. WHERE ID IN (SELECT ID
4. FROM EMPLOYEE);
3. Subqueries with the UPDATE Statement
The subquery of SQL can be used in conjunction with the Update statement. When a
subquery is used with the Update statement, then either single or multiple columns in a
table can be updated.
Syntax
1. UPDATE table
2. SET column_name = new_value
3. WHERE VALUE OPERATOR
4. (SELECT COLUMN_NAME
5. FROM TABLE_NAME
6. WHERE condition);
Example
Let's assume we have an EMPLOYEE_BKP table available which is backup of
EMPLOYEE table. The given example updates the SALARY by .25 times in the
EMPLOYEE table for all employee whose AGE is greater than or equal to 29.
1. UPDATE EMPLOYEE
2. SET SALARY = SALARY * 0.25
3. WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
4. WHERE AGE >= 29);
This would impact three rows, and finally, the EMPLOYEE table would have the
following records.

ID NAME AGE ADDRESS

1 John 20 US

2 Stephan 26 Dubai

3 David 27 Bangkok

4 Alina 29 UK

5 Kathrin 34 Bangalore

6 Harry 42 China

7 Jackson 25 Mizoram
4. Subqueries with the DELETE Statement
The subquery of SQL can be used in conjunction with the Delete statement just like any
other statements mentioned above.
Syntax
1. DELETE FROM TABLE_NAME
2. WHERE VALUE OPERATOR
3. (SELECT COLUMN_NAME
4. FROM TABLE_NAME
5. WHERE condition);
Example
Let's assume we have an EMPLOYEE_BKP table available which is backup of
EMPLOYEE table. The given example deletes the records from the EMPLOYEE table
for all EMPLOYEE whose AGE is greater than or equal to 29.
1. DELETE FROM EMPLOYEE
2. WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP
3. WHERE AGE >= 29 );
This would impact three rows, and finally, the EMPLOYEE table would have the
following records.
ID NAME AGE ADDRESS SALARY

1 John 20 US 2000.00

2 Stephan 26 Dubai 1500.00

 3 David 27 Bangkok 2000.00

7 Jackson 25 Mizoram 10000.00

SQL Clauses
The following are the various SQL clauses:

1. GROUP BY
o SQL GROUP BY statement is used to arrange identical data into groups. The
GROUP BY statement is used with the SQL SELECT statement.
o The GROUP BY statement follows the WHERE clause in a SELECT statement
and precedes the ORDER BY clause.
o The GROUP BY statement is used with aggregation function.
Syntax
1. SELECT column
2. FROM table_name
3. WHERE conditions
4. GROUP BY column
5. ORDER BY column
Sample table:

PRODUCT_MAST
PRODUCT COMPANY QTY RATE COST

Item1 Com1 2 10 20

Item2 Com2 3 25 75

Item3 Com1 2 30 60

Item4 Com3 5 10 50

Item5 Com2 2 20 40

Item6 Cpm1 3 25 75
 Item7 Com1 5 30 150

Item8 Com1 3 10 30

Item9 Com2 2 25 50

Item10 Com3 4 30 120

Example:
1. SELECT COMPANY, COUNT(*)
2. FROM PRODUCT_MAST
3. GROUP BY COMPANY;
Output:

Com1 5
Com2 3
Com3 2

2. HAVING
o HAVING clause is used to specify a search condition for a group or an
aggregate.
o Having is used in a GROUP BY clause. If you are not using GROUP BY clause
then you can use HAVING function like a WHERE clause.
Syntax:
1. SELECT column1, column2
2. FROM table_name
3. WHERE conditions
4. GROUP BY column1, column2
5. HAVING conditions
6. ORDER BY column1, column2;
Example:
1. SELECT COMPANY, COUNT(*)
2. FROM PRODUCT_MAST
3. GROUP BY COMPANY
4. HAVING COUNT(*)>2;
Output:

Com1 5
Com2 3

3. ORDER BY
o The ORDER BY clause sorts the result-set in ascending or descending order.
o It sorts the records in ascending order by default. DESC keyword is used to sort
the records in descending order.
Syntax:
1. SELECT column1, column2
2. FROM table_name
3. WHERE condition
4. ORDER BY column1, column2... ASC|DESC;
Where
ASC: It is used to sort the result set in ascending order by expression.
DESC: It sorts the result set in descending order by expression.
Example: Sorting Results in Ascending Order
Table:
 CUSTOMER
CUSTOMER_ID NAME ADDRESS

12 Kathrin US

23 David Bangkok

34 Alina Dubai

45 John UK

56 Harry US
Enter the following SQL statement:
1. SELECT *
2. FROM CUSTOMER
3. ORDER BY NAME;
Output:
CUSTOMER_ID NAME ADDRESS

34 Alina Dubai

23 David Bangkok

56 Harry US

45 John UK

12 Kathrin US
Example: Sorting Results in Descending Order
Using the above CUSTOMER table
1. SELECT *
2. FROM CUSTOMER
3. ORDER BY NAME DESC;
Output:
CUSTOMER_ID NAME ADDRESS

12 Kathrin US

45 John UK

56 Harry US

23 David Bangkok

34 Alina Dubai
SQL Aggregate Functions
o SQL aggregation function is used to perform the calculations on multiple rows
of a single column of a table. It returns a single value.
o It is also used to summarize the data.
 Types of SQL Aggregation Function

1. COUNT FUNCTION
o COUNT function is used to Count the number of rows in a database table. It can
work on both numeric and non-numeric data types.
o COUNT function uses the COUNT(*) that returns the count of all the rows in a
specified table. COUNT(*) considers duplicate and Null.
Syntax
1. COUNT(*)
2. or
3. COUNT( [ALL|DISTINCT] expression )

Sample table:

PRODUCT_MAST
PRODUCT COMPANY QTY RATE COST

Item1 Com1 2 10 20

Item2 Com2 3 25 75

Item3 Com1 2 30 60

Item4 Com3 5 10 50

Item5 Com2 2 20 40

Item6 Cpm1 3 25 75

Item7 Com1 5 30 150

Item8 Com1 3 10 30

Item9 Com2 2 25 50

Item10 Com3 4 30 120

 Example: COUNT()
1. SELECT COUNT(*)
2. FROM PRODUCT_MAST;
Output:

10

Example: COUNT with WHERE

1. SELECT COUNT(*)
2. FROM PRODUCT_MAST;
3. WHERE RATE>=20;
Output:

Example: COUNT() with DISTINCT

1. SELECT COUNT(DISTINCT COMPANY)
2. FROM PRODUCT_MAST;

Output:

Example: COUNT() with GROUP BY

1. SELECT COMPANY, COUNT(*)
2. FROM PRODUCT_MAST
3. GROUP BY COMPANY;
Output:

Com1 5
Com2 3
Com3 2

Example: COUNT() with HAVING

1. SELECT COMPANY, COUNT(*)
2. FROM PRODUCT_MAST
3. GROUP BY COMPANY
4. HAVING COUNT(*)>2;
Output:

Com1 5
Com2 3

2. SUM Function
Sum function is used to calculate the sum of all selected columns. It works on numeric
fields only.
Syntax
1. SUM()
2. or
3. SUM( [ALL|DISTINCT] expression )
Example: SUM()
1. SELECT SUM(COST)
2. FROM PRODUCT_MAST;
Output:

670

Example: SUM() with WHERE

1. SELECT SUM(COST)
2. FROM PRODUCT_MAST
3. WHERE QTY>3;
Output:

320

Example: SUM() with GROUP BY

1. SELECT SUM(COST)
2. FROM PRODUCT_MAST
3. WHERE QTY>3
4. GROUP BY COMPANY;
Output:

Com1 150
Com2 170

Example: SUM() with HAVING

1. SELECT COMPANY, SUM(COST)
2. FROM PRODUCT_MAST
3. GROUP BY COMPANY
4. HAVING SUM(COST)>=170;
Output:

Com1 335
Com3 170

3. AVG function
The AVG function is used to calculate the average value of the numeric type. AVG
function returns the average of all non-Null values.
Syntax
1. AVG()
2. or
3. AVG( [ALL|DISTINCT] expression )
Example:
1. SELECT AVG(COST)
2. FROM PRODUCT_MAST;
Output:

67.00

4. MAX Function
MAX function is used to find the maximum value of a certain column. This function
determines the largest value of all selected values of a column.
Syntax
1. MAX()
2. or
3. MAX( [ALL|DISTINCT] expression )
Example:
1. SELECT MAX(RATE)
2. FROM PRODUCT_MAST;

30

5. MIN Function
MIN function is used to find the minimum value of a certain column. This function
determines the smallest value of all selected values of a column.
Syntax
1. MIN()
2. or
3. MIN( [ALL|DISTINCT] expression )
Example:
1. SELECT MIN(RATE)
2. FROM PRODUCT_MAST;
Output:

10

SQL JOIN
As the name shows, JOIN means to combine something. In case of SQL, JOIN means
"to combine two or more tables".
In SQL, JOIN clause is used to combine the records from two or more tables in a
database.
Types of SQL JOIN
1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN
Sample Table
EMPLOYEE
EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

PROJECT_NO EMP_ID DEPARTMENT

101 1 Testing
 102 2 Development

103 3 Designing

104 4 Development
1. INNER JOIN
In SQL, INNER JOIN selects records that have matching values in both tables as long
as the condition is satisfied. It returns the combination of all rows from both the tables
where the condition satisfies.
Syntax
1. SELECT table1.column1, table1.column2, table2.column1,....
2. FROM table1
3. INNER JOIN table2
4. ON table1.matching_column = table2.matching_column;
Query
1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
2. FROM EMPLOYEE
3. INNER JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
Output
EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development
2. LEFT JOIN
The SQL left join returns all the values from left table and the matching values from the
right table. If there is no matching join value, it will return NULL.

Syntax
1. SELECT table1.column1, table1.column2, table2.column1,....
2. FROM table1
3. LEFT JOIN table2
4. ON table1.matching_column = table2.matching_column;
Query
1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
2. FROM EMPLOYEE
3. LEFT JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
Output
EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development
 Russell NULL

Marry NULL
3. RIGHT JOIN
In SQL, RIGHT JOIN returns all the values from the values from the rows of right table
and the matched values from the left table. If there is no matching in both tables, it will
return NULL.
Syntax
1. SELECT table1.column1, table1.column2, table2.column1,....
2. FROM table1
3. RIGHT JOIN table2
4. ON table1.matching_column = table2.matching_column;
Query
1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
2. FROM EMPLOYEE
3. RIGHT JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
Output
EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development
4. FULL JOIN
In SQL, FULL JOIN is the result of a combination of both left and right outer join. Join
tables have all the records from both tables. It puts NULL on the place of matches not
found.
Syntax
1. SELECT table1.column1, table1.column2, table2.column1,....
2. FROM table1
3. FULL JOIN table2
4. ON table1.matching_column = table2.matching_column;
Query
1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
2. FROM EMPLOYEE
3. FULL JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
Output
EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development

Russell NULL
 Marry NULL
SQL Set Operation
The SQL Set operation is used to combine the two or more SQL SELECT statements.
Types of Set Operation
1. Union
2. UnionAll
3. Intersect
4. Minus

1. Union
o The SQL Union operation is used to combine the result of two or more SQL
SELECT queries.
o In the union operation, all the number of datatype and columns must be same in
both the tables on which UNION operation is being applied.
o The union operation eliminates the duplicate rows from its resultset.
Syntax
1. SELECT column_name FROM table1
2. UNION
3. SELECT column_name FROM table2;
Example:
The First table
ID NAME

1 Jack

2 Harry

3 Jackson

The Second table

ID NAME

3 Jackson

4 Stephan

5 David
Union SQL query will be:
1. SELECT * FROM First
2. UNION
3. SELECT * FROM Second;
The resultset table will look like:
ID NAME

1 Jack

2 Harry

3 Jackson

4 Stephan

5 David
2. Union All
Union All operation is equal to the Union operation. It returns the set without removing
duplication and sorting the data.
Syntax:
1. SELECT column_name FROM table1
2. UNION ALL
3. SELECT column_name FROM table2;
Example: Using the above First and Second table.
Union All query will be like:
1. SELECT * FROM First
2. UNION ALL
3. SELECT * FROM Second;
The resultset table will look like:
ID NAME

1 Jack

2 Harry

3 Jackson

3 Jackson

4 Stephan

5 David
3. Intersect
o It is used to combine two SELECT statements. The Intersect operation returns
the common rows from both the SELECT statements.
o In the Intersect operation, the number of datatype and columns must be the
same.
o It has no duplicates and it arranges the data in ascending order by default.
Syntax
1. SELECT column_name FROM table1
2. INTERSECT
3. SELECT column_name FROM table2;
Example:
Using the above First and Second table.
Intersect query will be:
1. SELECT * FROM First
2. INTERSECT
3. SELECT * FROM Second;
The resultset table will look like:

ID NAME

3 Jackson
4. Minus
o It combines the result of two SELECT statements. Minus operator is used to
display the rows which are present in the first query but absent in the second
query.
o It has no duplicates and data arranged in ascending order by default.
Syntax:
1. SELECT column_name FROM table1
2. MINUS
3. SELECT column_name FROM table2;
Example
Using the above First and Second table.
Minus query will be:
1. SELECT * FROM First
2. MINUS
3. SELECT * FROM Second;
The resultset table will look like:
ID NAME

1 Jack

2 Harry
What is Cursor in SQL ?
Cursor is a Temporary Memory or Temporary Work Station. It is Allocated
by Database Server at the Time of Performing DML(Data Manipulation Language)
operations on the Table by the User. Cursors are used to store Database Tables.
There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors. These are
explained as following below.
1. Implicit Cursors: Implicit Cursors are also known as Default Cursors of SQL
SERVER. These Cursors are allocated by SQL SERVER when the user performs
DML operations.
2. Explicit Cursors: Explicit Cursors are Created by Users whenever the user
requires them. Explicit Cursors are used for Fetching data from Table in Row-By-
Row Manner.

How To Create Explicit Cursor?

1. Declare Cursor Object
Syntax:
DECLARE cursor_name CURSOR FOR SELECT * FROM table_name

Query:
DECLARE s1 CURSOR FOR SELECT * FROM studDetails
2. Open Cursor Connection
Syntax:
OPEN cursor_connection
Query:
OPEN s1
Fetch Data from the Cursor There is a total of 6 methods to access data from the
cursor. They are as follows:
1. FIRST is used to fetch only the first row from the cursor table.
2. LAST is used to fetch only the last row from the cursor table.
3. NEXT is used to fetch data in a forward direction from the cursor table.
4. PRIOR is used to fetch data in a backward direction from the cursor table.
5. ABSOLUTE n is used to fetch the exact n th row from the cursor table.
6. RELATIVE n is used to fetch the data in an incremental way as well as a
decremental way.
Syntax:
FETCH NEXT/FIRST/LAST/PRIOR/ABSOLUTE n/RELATIVE n FROM
cursor_name
Query:
FETCH FIRST FROM s1
FETCH LAST FROM s1
FETCH NEXT FROM s1
FETCH PRIOR FROM s1
FETCH ABSOLUTE 7 FROM s1
FETCH RELATIVE -2 FROM s1
 Close cursor connection
Syntax:
CLOSE cursor_name
Query:
CLOSE s1
 Deallocate cursor memory
Syntax:
DEALLOCATE cursor_name
Query:
DEALLOCATE s1
How To Create an Implicit Cursor?
An implicit cursor is a cursor that is automatically created by PL/SQL when you
execute a SQL statement. You don’t need to declare or open an implicit cursor
explicitly. Instead, PL/SQL manages the cursor for you behind the scenes.
To create an implicit cursor in PL/SQL, you simply need to execute a SQL statement.
For example, to retrieve all rows from the EMP table, you can use the following code:
Query:
BEGIN
FOR emp_rec IN SELECT * FROM emp LOOP
DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_rec.ename);
END LOOP;
END;
In PL/SQL, when we perform INSERT, UPDATE or DELETE operations, an implicit
cursor is automatically created. This cursor holds the data to be inserted or identifies
the rows to be updated or deleted. You can refer to this cursor as the SQL cursor in
your code. Thi SQL cursor has several useful attributes.
1. %FOUND is true if the most recent SQL operation affected at least one row.
2. %NOTFOUND is true if it didn’t affect any rows.
3. %ROWCOUNT is returns the number of rows affected.
4. %ISOPEN checks if the cursor is open.
In addition to these attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS
are specific to the FORALL statement, which is used to perform multiple DML
operations at once. %BULK_ROWCOUNT returns the number of rows affected by
each DML operation, while %BULK_EXCEPTION returns any exception that
occurred during the operations.
Query:
CREATE TABLE Emp(
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Country VARCHAR(50),
Age int(2),
Salary int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Emp (EmpID, Name,Country, Age, Salary)
VALUES (1, 'Shubham', 'India','23','30000'),
(2, 'Aman ', 'Australia','21','45000'),
(3, 'Naveen', 'Sri lanka','24','40000'),
(4, 'Aditya', 'Austria','21','35000'),
(5, 'Nishant', 'Spain','22','25000');
Select * from Emp;
Output:

This program updates a table by increasing the salary of each employee by 1500.
After the update, the SQL%ROWCOUNT attribute is used to find out how many rows
were affected by the operation.
Query:
DECLARE
total_rows number;
BEGIN
UPDATE Emp
SET Salary = Salary + 1500;

total_rows := SQL%ROWCOUNT;

dbms_output.put_line(total_rows || ' rows updated.');

END;
Output:
5 Emp selected
PL/SQL procedure successfully completed.
SQL Cursor Exceptions
Whenever we execute an SQL query then there is the possibility of an error that is
unexpected. Cursor goes through each set of rows to return in an SQL query.
There are some very popular exceptions:
1. Duplicate Value: This type of error occur when the cursor tries to insert a record
or tuple which already exists in the database. these types of errors can be avoided
by handling proper error conf
2. Invalid Cursor State: Whenever the cursor is in an invalid state this type of error
will show as an error.
3. Lock Timeout: This occurs when the cursor tries to obtain a lock on a row or
table but the lock is already held by another transaction.
Need of Cursor in an SQL server
1. Cursors allow us to process data row-by-row, which can be useful when we need
to perform complex calculations or transformations on the data.
2. Cursors allow us to iterate over a result set multiple times, which can be useful
when we need to perform multiple operations on the same data.
3. Cursors can be useful when we need to join multiple tables with complex
relationships, such as when processing hierarchical data structures or when
performing recursive queries.
4. Cursors allow us to perform operations such as updating, deleting, or inserting
records based on some condition or criteria.
5. Cursors are especially useful when processing data from multiple tables where the
relationships are not straightforward.
SQL Server Cursor Limitations
As a cursor has some limitations, it should only be used when there is no other
choice. These restrictions include:
1. When processing data, it imposes locks on a subset or the entire table.
2. The cursor updates table records one row at a time, which slows down its
performance.
3. While loops are slower than cursors, they do have more overhead.
4. Another factor that influences cursor speed is the quantity of rows and columns
brought into the cursor.
SQL Trigger | Student Database
A trigger is a stored procedure in a database that automatically invokes whenever a
special event in the database occurs. For example, a trigger can be invoked when a
row is inserted into a specified table or when specific table columns are updated. In
simple words, a trigger is a collection of SQL statements with particular names that
are stored in system memory. It belongs to a specific class of stored procedures that
are automatically invoked in response to database server events. Every trigger has a
table attached to it.
Because a trigger cannot be called directly, unlike a stored procedure, it is referred to
as a special procedure. A trigger is automatically called whenever a data modification
event against a table takes place, which is the main distinction between a trigger and a
procedure. On the other hand, a stored procedure must be called directly.
The following are the key differences between triggers and stored procedures:
1. Triggers cannot be manually invoked or executed.
2. There is no chance that triggers will receive parameters.
3. A transaction cannot be committed or rolled back inside a trigger.
Syntax:
create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
[for each row]
[trigger_body]
Explanation of Syntax
1. Create trigger [trigger_name]: Creates or replaces an existing trigger with the
trigger_name.
2. [before | after]: This specifies when the trigger will be executed.
3. {insert | update | delete}: This specifies the DML operation.
4. On [table_name]: This specifies the name of the table associated with the trigger.
5. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed
for each affected row.
6. [trigger_body]: This provides the operation to be performed as the trigger is fired
Why Do We Employ Triggers?
When we need to carry out some actions automatically in certain desirable scenarios,
triggers will be useful. For instance, we need to be aware of the frequency and timing
of changes to a table that is constantly changing. In such cases, we could create a
trigger to insert the required data into a different table if the primary table underwent
any changes.
Different Trigger Types in SQL Server
Two categories of triggers exist:
1. DDL Trigger
2. DML Trigger
3. Logon Triggers
DDL Triggers
The Data Definition Language (DDL) command events such as Create_table,
Create_view, drop_table, Drop_view, and Alter_table cause the DDL triggers to be
activated.
SQL Server
create trigger safety
on database
for
create_table,alter_table,drop_table
as
print 'you can not create,drop and alter tab
Output:

DML Triggers
The Data uses manipulation Language (DML) command events that begin with Insert,
Update, and Delete set off the DML triggers. corresponding to insert_table,
update_view, and delete_table.
SQL Server
create trigger deep
on emp
for
insert,update ,delete
as
print 'you can not insert,update and delete this table i'
rollback;
Output:

Logon Triggers
logon triggers are fires in response to a LOGON event. When a user session is created
with a SQL Server instance after the authentication process of logging is finished but
before establishing a user session, the LOGON event takes place. As a result, the
PRINT statement messages and any errors generated by the trigger will all be visible
in the SQL Server error log. Authentication errors prevent logon triggers from being
used. These triggers can be used to track login activity or set a limit on the number of
sessions that a given login can have in order to audit and manage server sessions.
How does SQL Server Show Trigger?
The show or list trigger is useful when we have many databases with many tables.
This query is very useful when the table names are the same across multiple
databases. We can view a list of every trigger available in the SQL Server by using
the command below:
Syntax:
FROM sys.triggers, SELECT name, is_instead_of_trigger
IF type = ‘TR’;
The SQL Server Management Studio makes it very simple to display or list all
triggers that are available for any given table. The following steps will help us
accomplish this:
Go to the Databases menu, select the desired database, and then expand it.
 Select the Tables menu and expand it.
 Select any specific table and expand it.
We will get various options here. When we choose the Triggers option, it displays all
the triggers available in this table.
BEFORE and AFTER Trigger
BEFORE triggers run the trigger action before the triggering statement is run. AFTER
triggers run the trigger action after the triggering statement is run.
Example
Given Student Report Database, in which student marks assessment is recorded. In
such a schema, create a trigger so that the total and percentage of specified marks are
automatically inserted whenever a record is inserted.
Here, a trigger will invoke before the record is inserted so BEFORE Tag can be used.
Suppose the Database Schema
Query
mysql>>desc Student;

SQL Trigger to the problem statement.

Above SQL statement will create a trigger in the student database in which whenever
subjects marks are entered, before inserting this data into the database, the trigger will
compute those two values and insert them with the entered values. i.e.
Output

In this way, triggers can be created and executed in the databases.

Advantage of Triggers
The benefits of using triggers in SQL Server include the following:
1. Database object rules are established by triggers, which cause changes to be
undone if they are not met.
2. The trigger will examine the data and, if necessary, make changes.
3. We can enforce data integrity thanks to triggers.
4. Data is validated using triggers before being inserted or updated.
5. Triggers assist us in maintaining a records log.
6. Due to the fact that they do not need to be compiled each time they are run,
triggers improve the performance of SQL queries.
7. The client-side code is reduced by triggers, saving time and labor.
8. Trigger maintenance is simple.
Disadvantage of Triggers
The drawbacks of using triggers in SQL Server include the following:
1. Only triggers permit the use of extended validations.
2. Automatic triggers are used, and the user is unaware of when they are being
executed. Consequently, it is difficult to troubleshoot issues that arise in the
database layer.
3. The database server’s overhead may increase as a result of triggers.
4. In a single CREATE TRIGGER statement, we can specify the same trigger action
for multiple user actions, such as INSERT and UPDATE.
5. Only the current database is available for creating triggers, but they can still make
references to objects outside the database.
Procedures in PL/SQL
PL/SQL procedures are reusable code blocks that perform specific actions or logic
within a database environment. They consist of two main components such as
the procedure header which defines the procedure name and optional parameters and
the procedure body which contains the executable statements implementing the
desired business logic.
In this article, We will learn about PL/SQL Procedures in detail by understanding
various examples and so on.
Table of Content
 Procedures in PL/SQL
o Procedure Header
o Procedure Body
 Create Procedures in PL/SQL
o Example
 Parameters in Procedures
o 1. IN parameters
o 2. OUT parameters
o 3. IN OUT parameters
 Modify Procedures in PL/SQL
o Example
 Drop Procedure in PL/SQL
 o PL/SQL DROP PROCEDURE Example
 Advantages of Procedures
 Disadvantages of Procedures
 Important Points About Procedures in SQL
 Conclusion
Procedures in PL/SQL
A PL/SQL procedure is a reusable block of code that contains a specific set of actions
or logic.
The procedure contains two parts:
1. Procedure Header
 The procedure header includes the procedure name and optional parameter list.
 It is the first part of the procedure and specifies the name and parameters
2. Procedure Body
 The procedure body contains the executable statements that implement the
specific business logic.
 It can include declarative statements, executable statements, and exception-
handling statements
Create Procedures in PL/SQL
To create a procedure in PL/SQL, use the CREATE PROCEDURE command:
Syntax
CREATE PROCEDURE syntax is:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE procedure_name
@Parameter1 INT,
@Parameter2 VARCHAR(50) = NULL,
@ReturnValue INT OUTPUT
AS
BEGIN
END
GO
Note: Procedures in PL/SQL without parameters are written without parentheses after
the procedure name
Example
In this example, we will create a procedure in PL/SQL
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetStudentDetails

@StudentID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Students WHERE StudentID=@StudentID
END
GO
Parameters in Procedures
In PL/SQL, parameters are used to pass values into procedures. There are three types
of parameters used in procedures:
1. IN parameters
 Used to pass values into the procedure
 Read-only inside the procedure
 Can be a variable, literal value, or expression in the calling statement.
2. OUT parameters
 Used to return values from the procedure to the calling program
 Read-write inside the procedure
 Must be a variable in the calling statement to hold the returned value
3. IN OUT parameters
 Used for both passing values into and returning values from the procedure
 Read-write inside the procedure
 Must be a variable in the calling statement
Modify Procedures in PL/SQL
To modify an existing procedures in PL/SQL use the ALTER PROCEDURE
command:
Syntax

ALTER PROCEDURE Syntax is:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE procedure_name

@Parameter1 INT,

@Parameter2 VARCHAR(50) = NULL,

@ReturnValue INT OUTPUT

AS

BEGIN
— QueryEND
GO
Example
In this example, we will modify a procedure in PL/SQL
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE GetStudentDetails

@StudentID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, City
FROM Students WHERE StudentID=@StudentID
END
GO
Drop Procedure in PL/SQL
To drop a procedure in PL/SQL use the DROP PROCEDURE command
Syntax
DROP PROCEDURE syntax is:
DROP PROCEDURE procedure_name
PL/SQL DROP PROCEDURE Example
In this example, we will delete a procedure in PL/SQL
DROP PROCEDURE GetStudentDetails
Advantages of Procedures
 They result in performance improvement of the application. If a procedure is
being called frequently in an application in a single connection, then the compiled
version of the procedure is delivered.
 They reduce the traffic between the database and the application since the lengthy
statements are already fed into the database and need not be sent again and again
via the application.
 They add to code reusability, similar to how functions and methods work in other
languages such as C/C++ and Java.
Disadvantages of Procedures
 Stored procedures can cause a lot of memory usage. The database administrator
should decide an upper bound as to how many stored procedures are feasible for a
particular application.
 MySQL does not provide the functionality of debugging the stored procedures.
Important Points About Procedures in SQL
 A procedure in PL/SQL is a subprogram that can take parameters and be called to
perform a specific action.
 Procedures are executed just like SQL statements.
 Procedures have two parts the specification (spec) and the body. The spec begins
with the PROCEDURE keyword and ends with the procedure name and optional
parameter list. The body begins with IS (or AS) and ends with END followed by
an optional procedure name
 They enhance performance by reducing network traffic between the application
and database.
Conclusion
PL/SQL procedures are essential for efficient database management, providing a way
to encapsulate and execute business logic. They help in enhancing performance by
minimizing network traffic and improving execution efficiency. Despite their
advantages, procedures can increase memory usage and lack debugging functionality
in some databases.