A19PC1CS04 UNIT – III DBMS

SYLLABUS
Introduction to the Relational Model: Structure of RDBMS, Integrity Constraints over Relations,
Querying Relational Data, Relational Algebra and Relational Calculus.
Introduction to SQL: Data Definition commands, Data Manipulation Commands, Basic Structure,
Set operations, Aggregate Operations, Join Operations, Sub queries and correlated queries, views,
Triggers, Cursors, Embedded SQL, Overview of NoSQL database.

Introduction to the Relational Model

Structure of RDBMS:
• A relational database consists of a collection of tables, each of which is assigned unique name.
• The relational model the term relation is used to refer to a table, while the term tuple is used to refer
to a row and the term attribute refers to a column of a table.
• Relation instance to refer to a specific instance of a relation, that is, containing a specific set of rows.
• For each attribute of a relation, there is a set of permitted values, called the domain of that attribute.
• A domain is atomic if elements of the domain are considered to be indivisible units.
• The null value is a special value that signifies that the value is unknown or does not exist.
• When we talk about a database, we must differentiate between the database schema, which is the
logical design of the database, and the database instance, which is a snapshot of the data in the
database at a given instant in time.

Integrity Constraints over Relations

• Integrity constraints ensure that changes made to the database by authorized users do not result in a
loss of data consistency.
• An integrity constraint (IC) is a condition specified on a database schema and restricts the data that
can be stored in an instance of the database.
• A DBMS permits only legal instances to be stored in the database.
• Examples of integrity constraints are:
➢ A student name cannot be null.
➢ No two students can have the same instructor ID.
➢ The budget of a department must be greater than $0.00.
• The create table command may also include integrity-constraint statements.
• There are a number of constraints be included in the create table command.
• The allowed integrity constraints include
• Domain constraints (Not Null, Check)
• Entity Integrity constraints (Unique)
• Referential Integrity constraints
• Key constraints

1
A19PC1CS04 UNIT – III DBMS

Not Null Constraint

• The not null constraint prohibits the insertion of a null value for the attribute, and is an example
of a domain constraint.
• The NOT NULL constraint enforces a column to not accept NULL values, which means that you
cannot insert or update a record without adding a value to this field.
• For example, the student’s name to be not null, or the department budget to be not null.
• Example: name varchar(20) not null
budget numeric(12,2) not null

The Check Clause

• When applied to a relation declaration, the clause check(P) specifies a predicate P that must be
satisfied by every tuple in a relation.
• A common use of the check clause is to ensure that attribute values satisfy specified conditions,
in effect creating a powerful type system.
• For example, a clause check (budget > 0) in the create table command for relation department
would ensure that the value of budget is nonnegative.
• Example: budget numeric (12,2) check (budget > 0)

Unique Constraint
• The unique specification no two tuples in the relation can be equal on all the listed attributes.
• Example: roll int unique
• However, attributes declared as unique are permitted to be null unless they have explicitly been
declared to be not null.
• Example: roll int not null unique
• Recall that a null value does not equal any other value.
Referential integrity constraint:
• Referential Integrity Constraint is used to specify interdependencies between relations.
• This constraint specifies a column or list of columns as a foreign key of the referencing table.
• A foreign key means the values in one table must also appear in another table.
• The foreign key in the child table will generally reference a primary key in the parent table.
• The referencing table is called the child table & referenced table is called the parent table.
• In order to provide Referential Integrity, the conditions must exist
1) The data types of TWO columns must be same
2) The referenced key must be Primary Key.
Key Constraints:
• Keys are the entity set that is used to identify an entity within its entity set uniquely.
• 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 not null value in the relational table.
Assigning Names to Constraints:
• It is possible for us to assign a name to integrity constraints.
• Such names are useful if we want to drop a constraint that was defined previously.
• The constraint is a keyword to create constraint and the name we wish to assign it.
• Example: salary numeric (8,2),
constraint minsalary check (salary > 29000)
• Later, if we decide we no longer want this constraint, we can write:
• Example: alter table instructor drop constraint minsalary;
2
A19PC1CS04 UNIT – III DBMS

Querying Relational Data:

• Query is a question or requesting information.
• Query language is a language which is used to retrieve information from a database.
• Query language is divided into two types as follows −
✓ Procedural language
✓ Non-procedural language
Procedural language
• Information is retrieved from the database by specifying the sequence of operations to be
performed.
• For Example: Relational algebra, Structure Query language (SQL)
Non-Procedural language
• Information is retrieved from the database without specifying the sequence of operation to be
performed.
• Users only specify what information is to be retrieved.
• For Example: Relational Calculus, Query by Example (QBE)

Relational Algebra:
• Relational algebra is a procedural query language, which takes instances of relations as input and
yields instances of relations as output.
• The relational algebra consists of a set of operations that take one or two relations as input a nd
produce a new relation as their result.
• The relational algebra is very important for several reasons.
1. It provides a formal foundation for relational model operations.
2. It is used as a basis for implementing and optimizing queries in the query processing and
optimization modules that are integral parts of relational database management systems
(RDBMSs).
3. Some of its concepts are incorporated into the SQL standard query language for RDBMSs.
• The relational algebra operations can be divided into two groups.
1. Unary Operations (select, project, and rename)
2. Binary Operations (union, Intersection, Cartesian product, and set difference)
Working Tables
emp
empno ename job mgr hiredate sal comm deptno

7839 KING PRESIDENT 1981-11-17 5000 10

7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 30
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7844 TURNER SALESMAN 7698 1981-09-08 1500 30
7900 JAMES CLERK 7698 1981-12-03 950 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7369 SMITH CLERK 7902 1980-12-17 800 20
7788 SCOTT ANALYST 7566 1982-12-09 3000 20
7876 ADAMS CLERK 7788 1983-01-12 1100 20
7934 MILLER CLERK 7782 1982-01-23 1300 10 3
A19PC1CS04 UNIT – III DBMS
dept salgrade
deptno dname location grade losal hisal
10 Accounting New York 1 700 1200
20 Research Dallas 2 1201 1400
30 Sales Chicago 3 1401 2000
40 Operations Boston 4 2001 3000
5 3001 99999

Unary Relational Operations: The unary operations are applied to a single relation (table).

SELECT Operation:
• The SELECT operation is used to retrieve a subset of the tuples from a relation that satisfies a
selection condition.
• The symbol σ (sigma) is used to denote the SELECT operator.
• Syntax: σ < selection condition> ( R )
• Where selection condition is a Boolean expression (condition) specified on the attributes of
relation R.
• For example, to select the EMPLOYEE tuples whose department is 4, SELECT operation as
follows:
σ Dno= 4 ( EMPLOYEE)
• The SELECT operation can also be visualized as a horizontal partition of the relation into two sets
of tuples:
➢ Tuples that satisfy the condition and are selected.
➢ Tuples that do not satisfy the condition and are discarded.
• The <selection condition> is applied independently to each individual tuple t in R.
• The Boolean expression specified in <selection condition> is made up of a number of clauses of
the form.
<attribute name> <comparison op> <constant value>
Or
<attribute name> <comparison op> <attribute name>
• Where <attribute name> is the name of an attribute of R
<comparison op> is normally one of the operators {=, <, ≤, >, ≥, ≠}
For ordered values (numeric or date), the comparison operators are {=, <, ≤, >, ≥, ≠}
For unordered values (names, colors), the comparison operators are {=, ≠}
• The number of tuples in the resulting relation is always less than or equal to the number of tuples
in R. That is, |σc ( R)| ≤ |R| for any condition C.
• The degree of the relation is the number of attributes in a relation.
• Notice that the SELECT operation is commutative; that is,
σ < cond1> ( σ < cond2> ( R )) = σ < cond2> ( σ < cond1> ( R ))
• we can always combine a cascade (or sequence) of SELECT operations into a single SELECT
operation with a conjunctive (AND) condition; that is,
σ < cond1> ( σ < cond2> ( ...( σ < condn> ( R )) ...)) = σ < cond1> AND< cond2> AND...AND < condn> ( R )
• In SQL, the SELECT condition is typically specified in the WHERE clause of a query.
• For example, the following operation:
σ Dno= 4 AND Salary> 25000 ( EMPLOYEE)
• would correspond to the following SQL query:
SELECT * FROM EMPLOYEE WHERE Dno=4 AND Salary>25000;
4
A19PC1CS04 UNIT – III DBMS

PROJECT Operation:

• The PROJECT operation is used to retrieve only certain attributes (columns) in a relation.
• The PROJECT operation is display the attributes in the same order as they appear in the list.
• π (pi) is the symbol used to represent the PROJECT operation.
• Syntax: π < attribute list> ( R )
• Where <attribute list> is the desired sub-list of attributes from the relation R.
• For example, to list each employee’s first and last name and salary, we can use the PROJECT
operation as follows:
π Lname, Fname, Salary( EMPLOYEE)
• The PROJECT operation can be visualized as a vertical partition of the relation into two
relations:
➢ One has the needed columns (attributes) and contains the result of the operation.
➢ The other contains the discarded columns.
• Degree is equal to the number of attributes in <attribute list>.
• If the attribute list includes only non-key attributes of R, duplicate tuples are likely to occur.
• The PROJECT operation removes any duplicate tuples from relation and retrieve a valid set of
distinct tuples is known as duplicate elimination.
• For example, consider the following PROJECT operation:
π Sex, Salary ( EMPLOYEE)

• Notice that the tuple <‘F’, 25000> appears only once in above Figure, even though this
combination of values appears twice in the EMPLOYEE relation.
• It is note that commutativity does not hold on PROJECT.
• In SQL, the PROJECT attribute list is specified in the SELECT clause of a query.
• For example, the following operation:
π Sex, Salary ( EMPLOYEE)
• Would correspond to the following SQL query:
SELECT DISTINCT Sex, Salary FROM EMPLOYEE
• Writing a relational algebra expression in single line known as an in-line expression.

Example: π Fname, Lname, Salary ( σ Dno= 5 ( EMPLOYEE) )

RENAME Operation

• To rename the attributes in a relation, we simply list the new attribute names in parentheses.
• Rename operation is denoted with the symbol ρ (rho)
• If no renaming is applied, the names of the attributes in the resulting relation of a SELECT
operation are the same as those in the original relation and in the same order.
• We can rename either the relation name or the attribute names, or both—as a unary operator.
• The general RENAME operation when applied to a relation R of degree n is denoted by any of the
following three forms:
ρS ( R ) or ρ( B1, B2, ..., Bn)( R ) or ρ S ( B 1, B 2, ..., Bn)( R )
• Where the symbol ρ (rho) is used to denote the RENAME operator, S is the new relation name,
and B1, B2 , ..., Bn are the new attribute names.
5
A19PC1CS04 UNIT – III DBMS

• Suppose we have a relation named Students and we want to change it to FinalYrStudents the
rename operation works as follows:
ρFinalYrStudents(Students)
Suppose we have a relation named Students and we want to change its attributes
StudentID, StudentName to SID and SName, the rename operation works as follows:
ρ(SID,SName)(Students)
we'll change both the relation’s name and attributes of the Students class:
ρFinalYrStudents(SID,SName)(Students)
• The first expression renames the relation only, the second renames the attributes only, and the third
renames both the relation and its attributes.
• Renaming in SQL is accomplished by aliasing using AS, as in the following example:
SELECT E.Fname AS First_name, E.Lname AS Last_name, E.Salary AS Salary
FROM EMPLOYEE AS E WHERE E.Dno=5;
Binary Operations: The binary operations are applied to two relations (table).
UNION, INTERSECTION, and SET DIFFERENCE(MINUS) Operations

• Set theoretic operations are used to merge the elements of two sets in various ways, including
UNION, INTERSECTION, and SET DIFFERENCE (also called MINUS or EXCEPT).
• These are binary operations; that is, each is applied to two relations.
• The two relations on which any of these three operations are applied must follow two rules.
✓ Two relations must have the same number of attributes.
✓ Two relations must have union compatibility or type compatibility (i.e., same type of
tuples).
• Two relations R(A1 , A2 , ..., An ) and S(B1 , B2 , ..., Bn ) are said to be union compatible (or type
compatible) if they have the same degree n and if dom(Ai) = dom(Bi).
• We can define the three operations UNION, INTERSECTION, and SET DIFFERENCE on two
union-compatible relations R and S as follows:

❖ UNION: The result of this operation, denoted by R ∪ S, is a relation that includes all tuples
that are either in R or in S or in both R and S. Duplicate tuples are eliminated.
❖ INTERSECTION: The result of this operation, denoted by R ∩ S, is a relation that includes
all tuples that are in both R and S.
❖ SET DIFFERENCE (or MINUS): The result of this operation, denoted by R – S, is a
relation that includes all tuples that are in R but not in S.

Figure: The set operations UNION, INTERSECTION, and MINUS.

(a) Two union-compatible relations. (b) STUDENT ∪ INSTRUCTOR.
(c) STUDENT ∩ INSTRUCTOR. (d) STUDENT − INSTRUCTOR.
(e) INSTRUCTOR − STUDENT

6
A19PC1CS04 UNIT – III DBMS

• Notice that both UNION and INTERSECTION are commutative operations.

R ∪ S = S ∪ R and R ∩ S = S ∩ R
• The MINUS operation is not commutative.
R−S≠S–R
• Note that INTERSECTION can be expressed in terms of union and set difference as follows:
R ∩ S = ((R ∪ S ) − ( R − S )) − ( S − R )
• There are multiset operations (UNION ALL, INTERSECT ALL, and EXCEPT ALL) that do not
eliminate duplicates.

CARTESIAN PRODUCT (CROSS PRODUCT) Operation:

• CROSS PRODUCT or CROSS JOIN—which is denoted by ×.

• This is also a binary set operation, but the relations on which it is applied do not have to be
union compatible or type compatibility.
• The CARTESIAN PRODUCT creates tuples with the combined attributes of two relations.
• CROSS PRODUCT operation produces a new element by combining every member (tuple) from
one relation (set) with every member (tuple) from the other relation (set).
• The result of R(A1 , A2 , ..., An ) × S(B1 , B2 , ..., Bm ) is a relation Q with degree n + m
attributes Q(A1 , A2 , ..., An , B1 , B2 , ..., Bm ), in that order.

Dept X Salgrade
Deptno Dname Loc Losal Hisal Grade
10 ACCOUNTING NEW YORK 700 1200 1
10 ACCOUNTING NEW YORK 1201 1400 2
Dept 10 ACCOUNTING NEW YORK 1401 2000 3
Deptno Dname Loc 10 ACCOUNTING NEW YORK 2001 3000 4
NEW 10 ACCOUNTING NEW YORK 3001 9999 5
10 ACCOUNTING 20 RESEARCH DALLAS 700 1200 1
YORK
20 RESEARCH DALLAS 20 RESEARCH DALLAS 1201 1400 2
30 SALES CHICAGO 20 RESEARCH DALLAS 1401 2000 3
40 OPERATIONS BOSTON 20 RESEARCH DALLAS 2001 3000 4
7
20 RESEARCH DALLAS 3001 9999 5
30 SALES CHICAGO 700 1200 1
A19PC1CS04 UNIT - II DBMS

The JOIN Operation

• The JOIN operation, denoted by , is used to combine related tuples from two relations into single
“longer” tuples.
• There are several join operations: THETA JOIN, EQUIJOIN, NATURAL JOIN

THETA JOIN:
• A JOIN operation with general join condition is called a THETA JOIN.
• Syntax:
R <join condition>S or R ɵS
• Where R and S are two relations R (A1 , A2 , ..., An ) and S(B1 , B2 , ..., Bm ).
• where each <condition> is of the form A i θ Bj, Ai is an attribute of R, Bj is an attribute of S, Ai
and Bj have the same domain, and θ (theta) is one of the comparison operators {=, <, ≤, >, ≥, ≠}.
• Example: to retrieve the employee names and their grades
π Ename , Grade(Emp emp.sal>=salgrade.losal AND emp.sal>=salgrade.hisal Salgrade Salgrade)
• Tuples whose join attributes are NULL or for which the join condition is FALSE do not appear
in the result.

EQUI JOIN:
• A JOIN, where the only comparison operator used is =, is called an EQUIJOIN.
• EQUIJOIN we always have one or more pairs of attributes that have identical values in
every tuple.
• Example: π Ename , Deptno, Dname(Emp emp.deptno=dept.deptno Dept)

NATURAL JOIN:
• A NATURAL JOIN is the set of tuples of all combinations in R and S that are equal on their
common attribute names.
• NATURAL JOIN denoted by .
• A NATURAL JOIN requires that the two join attributes (or each pair of join attributes)
have the same name in both relations.
• NATURAL JOIN was created to get rid of the second (superfluous) attribute in an EQUIJOIN
condition.
• Example: π Ename , Deptno, Dname(Emp Dept)
• If this is not the case, a renaming operation is applied first.
Example: e1EMP e2EMP mgre2.Empno
π e1.Ename , e2.Ename (e1 e2)
• Join attribute: an attribute is used to join the both relations are called join attribute.
• In JOIN, only combinations of tuples satisfying the join condition appear in the result, whereas
in the CARTESIAN PRODUCT all combinations of tuples are included in the result.
• The expected size of the join result divided by the maximum size n R * nS leads to a ratio called
join selectivity, which is a property of each join condition.

8
A19PC1CS04 UNIT - II DBMS

OUTER JOIN Operations

• Outer joins were developed for the case where the user wants to keep all the tuples in R, or all
those in S, or all those in both relations in the result of the JOIN, regardless of whether or not
they have matching tuples in the other relation.
• This satisfies the need of queries in which tuples from two tables are to be combined by matching
corresponding rows, but without losing any tuples for lack of matching values.
• There are three type of outer joins are there
1. LEFT OUTER JOIN The LEFT OUTER JOIN operation keeps every tuple in the first, or left,
relation R and common with S is denoted by R S.
2. RIGHT OUTER JOIN The RIGHT OUTER JOIN operation keeps every tuple in the second,
or right, relation S and common with R is denoted by R S.
3. FULL OUTER JOIN The FULL OUTER JOIN operation keeps both tuple in the left and
right, relations in R S.
• If no matching tuple is found in relations, then filled or padded with NULL values.
• For example, list of all employee names as well as the name of the departments they manage.
• If employee manage a department, then department name will be result; if they do not manage
placed with NULL value. We can apply an operation LEFT OUTER JOIN

TEMP ← ( EMPLOYEE Ssn= Mgr_ssn DEPARTMENT)

RESULT ← πFname, Minit, Lname, Dname( TEMP)

9
A19PC1CS04 UNIT - II DBMS

The DIVISION Operation

• The DIVISION operation, denoted by ÷
• The DIVISION include the tuples appear in R in combination with every tuple in S.
• Note that in the formulation of the DIVISION operation, the tuples in the denominator relation S
restrict the numerator relation R by selecting those tuples in the result that match all values present
in the denominator.
• The DIVISION operation is applied to two relations R(Z) ÷ S(X), where the attributes of R are a
subset of the attributes of S.
• Example is Retrieve the names of employees who work on all the projects that ‘John Smith’ works
on.

SMITH ← σFname= ‘John’ AND Lname= ‘Smith’( EMPLOYEE)

SMITH_PNOS ← πPno( WORKS_ON Essn= SsnSMITH)
SSN_PNOS ← πEssn, Pno( WORKS_ON)

• Apply the DIVISION operation to the two relations, which gives the desired employees’ Social
Security numbers:

SSNS( Ssn) ← SSN_PNOS ÷ SMITH_PNOS

RESULT ← πFname, Lname( SSNS * EMPLOYEE)

Figure: The DIVISION operation. (a) Dividing SSN_PNOS by SMITH_PNOS. (b) T ← R ÷ S

• The DIVISION operation can be expressed as a sequence of π, ×, and – operations as follows:
T1 ← πY ( R)
T2 ← πY ((S × T1) – R)
T ← T 1 – T2

10
A19PC1CS04 UNIT - II DBMS

Additional Relational Operations

Generalized Projection

• The generalized projection operation extends the projection operation by allowing functions of
attributes to be included in the projection list.
• Syntax:
πF 1, F2, ..., Fn ( R)
• Where F1 , F2 , ..., Fn are functions over the attributes in relation R and may involve arithmet ic
operations and constant values.
• As an example, consider the relation
EMPLOYEE ( Ssn, Salary, Deduction, Years_service)
• A report may be required to show
Net Salary = Salary – Deduction,
Bonus = 2000 * Years_service, and
Tax = 0.25 * Salary.
• Then a generalized projection combined with renaming may be used as follows:

REPORT ← ρ( Ssn, Net_salary, Bonus, Tax) ( π Ssn, Salary – Deduction, 2000 * Years_service, 0.25 * Salary ( EMPLOYEE)).

Aggregate Functions and Grouping

• Aggregate functions are SUM, AVERAGE, MAXIMUM, MINIMUM, and COUNT.

• SUM: function is used to find sum of tuples or values.
• AVERAGE: function is used to find average of tuples or values.
• MAXIMUM: function is used to find maximum value from the tuples or values.
• MINIMUM: function is used to find minimum value from the tuples or values.
• COUNT: function is used for counting tuples or values.
• GROUP: grouping the tuples in a relation by the value of some of their attributes and then
applying an aggregate function independently to each group.
• We can define an AGGREGATE FUNCTION operation, using the symbol ℑ (pronounced script
F)
• Syntax: <grouping attributes> ℑ <function list> ( R)

• Where <grouping attributes> is a list of attributes of the relation specified in R, <function list>
is a list of (<function> <attribute>) pairs.
• Example: List Count of employees in each department and their average salary.
( Dno ℑ COUNT Ssn, AVERAGE Salary ( EMPLOYEE))

• If no renaming is applied, then the attribute names be the concatenation of the function name
with the attribute name in the form <function>_<attribute>.
• If rename is applied then corresponding rename attributes are displayed.
11
A19PC1CS04 UNIT - II DBMS

ρ R ( Dno, No_of_employees, Average_sal) ( Dno ℑ COUNT Ssn, AVERAGE Salary ( EMPLOYEE))

• If no grouping attributes are specified, the functions are applied to all the tuples in the relation,
so the resulting relation has a single tuple only.
• Example:
ℑ COUNT Ssn, AVERAGE Salary ( EMPLOYEE)

• Note: duplicates are not eliminated when an aggregate function is applied.

Examples of Queries in Relational Algebra

Query 1. Retrieve the name and address of all employees who work for the ‘Research’ department.
Ans: RESEARCH_DEPT ← σDname= ‘Research’( DEPARTMENT)
RESEARCH_EMPS ← ( RESEARCH_DEPT Dnumber= Dno EMPLOYEE)
RESULT ← πFname, Lname, Address( RESEARCH_EMPS)
As a single in-line expression, this query becomes:
π Fname, Lname, Address ( σ Dname= ‘Research’( DEPARTMENT Dnumber= Dno ( EMPLOYEE))

Query 2. For every project located in ‘Stafford’, list the project number, the controlling department
number, and the department manager’s last name, address, and birth date.
Ans: STAFFORD_PROJS ← σPlocation= ‘Stafford’( PROJECT)
CONTR_DEPTS ← ( STAFFORD_PROJS Dnum= DnumberDEPARTMENT)

PROJ_DEPT_MGRS ← ( CONTR_DEPTS Mgr_ssn= SsnEMPLOYEE)

RESULT ← πPnumber, Dnum, Lname, Address, Bdate ( PROJ_DEPT_MGRS)

Query 3. Find the names of employees who work on all the projects controlled by department number
5.
Ans: DEPT5_PROJS ← ρ( Pno) ( π Pnumber( σ Dnum= 5 ( PROJECT)))
EMP_PROJ ← ρ( Ssn, Pno) ( π Essn, Pno( WORKS_ON))
RESULT_EMP_SSNS ← EMP_PROJ ÷ DEPT5_PROJS
RESULT ← πLname, Fname( RESULT_EMP_SSNS * EMPLOYEE)

12
A19PC1CS04 UNIT - II DBMS

Query 4. Make a list of project numbers for projects that involve an employee whose last name is
‘Smith’, either as a worker or as a manager of the department that controls the project.
Ans: SMITHS( Essn) ← π Ssn ( σ Lname= ‘Smith’( EMPLOYEE))
SMITH_WORKER_PROJS ← πPno( WORKS_ON * SMITHS)
MGRS ← πLname, Dnumber( EMPLOYEE Ssn= Mgr_ssnDEPARTMENT)

SMITH_MANAGED_DEPTS( Dnum) ← π Dnumber ( σ Lname= ‘Smith’( MGRS))

SMITH_MGR_PROJS( Pno) ← π Pnumber( SMITH_MANAGED_DEPTS * PROJECT)
RESULT ← (SMITH_WORKER_PROJS ∪ SMITH_MGR_PROJS)

π Pno ( WORKS_ON Essn= Ssn( π Ssn ( σ Lname= ‘Smith’( EMPLOYEE))) ∪

π Pno ((π Dnumber ( σ Lname= ‘Smith’( π Lname, Dnumber( EMPLOYEE))) Ssn= mgr_ssn DEPARTMENT))
Dnumber= DnumPROJECT)

Query 5. List the names of all employees with two or more dependents.
Ans: T1(Ssn, No_of_dependents) ← Essn ℑ COUNT Dependent_name( DEPENDENT)
T2 ← σNo_of_dependents> 2 ( T1)
RESULT ← πLname, Fname( T2 * EMPLOYEE)

Query 6. Retrieve the names of employees who have no dependents.

This is an example of the type of query that uses the MINUS (SET DIFFERENCE) operation.
Ans: ALL_EMPS ← πSsn( EMPLOYEE)
EMPS_WITH_DEPS( Ssn) ← πEssn( DEPENDENT)
EMPS_WITHOUT_DEPS ← ( ALL_EMPS – EMPS_WITH_DEPS)
RESULT ← πLname, Fname( EMPS_WITHOUT_DEPS * EMPLOYEE)
As a single in-line expression, this query becomes:
π Lname, Fname((π Ssn( EMPLOYEE) – ρ Ssn( π Essn( DEPENDENT))) * EMPLOYEE)

Query 7. List the names of managers who have at least one dependent.
Ans: MGRS( Ssn) ← πMgr_ssn( DEPARTMENT)
EMPS_WITH_DEPS( Ssn) ← πEssn( DEPENDENT)
MGRS_WITH_DEPS ← ( MGRS ∩ EMPS_WITH_DEPS)
RESULT ← πLname, Fname( MGRS_WITH_DEPS * EMPLOYEE)

13
A19PC1CS04 UNIT - II DBMS

Relational Calculus:
• The relational calculus is a formal language, based on the branch of mathematical logic called
predicate calculus.
• The relational calculus provides a higher-level declarative language for specifying relational queries.
• In a relational calculus expression, there is no order of operations to specify how to retrieve the query
result—only what information the result should contain.
• This is the main distinguishing feature between relational algebra and relational calculus.
• There are two variations of relational calculus-tuple relational calculus and domain relational
calculus.
The Tuple Relational Calculus

• The relational calculus is considered to be a nonprocedural language i.e., no need to write a

sequence of operations to specify a retrieval request in a particular order of applying the
operations.
• A calculus expression specifies what is to be retrieved rather than how to retrieve it.
• Relational calculus is important for two reasons-
➢ First, it has a firm basis in mathematical logic.
➢ Second, the standard query language (SQL) for RDBMSs has some of its foundations in the
tuple relational calculus.
Tuple Variables and Range Relations

• The tuple relational calculus is based on specifying a number of tuple variables.

• Each tuple variable usually ranges over a particular database relation, meaning that the
variable may take as its value any individual tuple from that relation.
• Syntax:
{t | COND( t)}
• Where t is a tuple variable and COND( t) is a conditional (Boolean) expression involving t that
evaluates to either TRUE or FALSE for different assignments of tuples to the variable t.
• For example, to find all employees whose salary is above $50,000, we can write the following
tuple calculus expression:
{t | EMPLOYEE( t) AND t .Salary>50000}

• We can also retrieve only some of the attributes(say the first and last names) as
{t.Fname, t.Lname | EMPLOYEE( t) AND t .Salary>50000}
• we need to specify the following information in a tuple relational calculus expression:
➢ For each tuple variable t, the range relation R of t means R(t). If we do not specify a range
relation, then the variable t will range over all possible tuples “in the universe” as it is not
restricted to any one relation.
➢ A condition to select particular combinations of tuples from range relations, the condition is
evaluated for every possible combination of tuples to identify the selected combinations for
which the condition evaluates to TRUE.
➢ A set of attributes to be retrieved, the requested attributes. The values of these attributes are
retrieved for each selected combination of tuples.

14
A19PC1CS04 UNIT - II DBMS

Expressions and Formulas in Tuple Relational Calculus

• A general expression of the tuple relational calculus is of the form

{t1 . Aj, t2 . Ak , ..., tn .Am | COND( t1 , t2 , ..., tn , tn +1, tn +2, ..., tn+m)}

• where t1 , t2 , ..., tn , tn +1, ..., tn +m are tuple variables, each A i is an attribute of the relation on which
ti ranges, and COND is a condition or formula.
• A formula is made up of predicate calculus atoms, which can be one of the following:
1. An atom of the form R(ti), where R is a relation name and ti is a tuple variable.
2. An atom of the form t i. A op tj. B.
Where op is one of the comparison operators in the set {=, <, ≤, >, ≥, ≠},
ti and tj are tuple variables,
A is an attribute of the relation on which t i ranges, and
B is an attribute of the relation on which t j ranges.
3. An atom of the form ti. A op c or c op tj. B, where c is a constant value.

• A formula (Boolean condition) is made up of one or more atoms connected via the logical
operators AND, OR, and NOT and is defined recursively by Rules 1 and 2 as follows:
➢ Rule 1: Every atom is a formula.
➢ Rule 2: If F1 and F2 are formulas, then so are (F1 AND F 2 ), (F1 OR F 2 ), NOT ( F1 ).
a. ( F1 AND F 2 ) is TRUE if both F1 and F2 are TRUE; otherwise, it is FALSE.
b. ( F1 OR F 2 ) is FALSE if both F1 and F2 are FALSE; otherwise, it is TRUE.
c. NOT ( F1 ) is TRUE if F1 is FALSE; it is FALSE if F1 is TRUE.
d. NOT ( F2 ) is TRUE if F2 is FALSE; it is FALSE if F2 is TRUE.

The Existential and Universal Quantifiers

• Quantifiers are two types, they are the universal quantifier (∀) and the existential quantifier
(∃).
• We need to define the concepts of free and bound tuple variables in a formula.
• Informally, a tuple variable t is bound if it is quantified, meaning that it appears in an (∃t) or (∀t)
clause; otherwise, it is free.
• we define a tuple variable in a formula as free or bound according to the following rules:
➢ An occurrence of a tuple variable in a formula F that is an atom is free in F.
➢ An occurrence of a tuple variable t is free or bound in a formula made up of logical connectives
—(F1 AND F 2 ), (F1 OR F 2 ), NOT( F1 ), and NOT( F2 ) -- depending on whether it is free
or bound in F1 or F2 (if it occurs in either).
• Truth values for formulas with quantifiers are described below-
➢ The formula (∃t)(F) is TRUE if the formula F evaluates to TRUE for some (at least one)
tuple assigned to free occurrences of t in F; otherwise, (∃t)(F) is FALSE.
➢ The formula (∀t)(F) is TRUE if the formula F evaluates to TRUE for every tuple (in the
universe) assigned to free occurrences of t in F; otherwise, (∀t)(F) is FALSE.

15
A19PC1CS04 UNIT - II DBMS

Sample Queries in Tuple Relational Calculus

Query 1. List the name and address of all employees who work for the ‘Research’ department.

Ans: { t.Fname, t.Lname, t.Address | EMPLOYEE( t) AND ( ∃d)(DEPARTMENT( d)

AND d .Dname=‘Research’ AND d .Dnumber= t.Dno)}
Query 2. For every project located in ‘Stafford’, list the project number, the controlling department
number, and the department manager’s last name, birth date, and address.

Ans: { p.Pnumber, p.Dnum, m.Lname, m.Bdate, m.Address | PROJECT( p) AND EMPLOYEE( m)

AND p .Plocation=‘Stafford’ AND ((∃d)(DEPARTMENT( d) AND p .Dnum= d.Dnumber
AND d .Mgr_ssn= m.Ssn))}
Query 3. List the name of each employee who works on some project controlled by department
number 5. This is a variation of Q3 in which all is changed to some. In this case we need two
join conditions and two existential quantifiers.
Ans: { e. Lname, e. Fname | EMPLOYEE( e) AND ((∃x)(∃w)(PROJECT( x) AND
WORKS_ON( w) AND x.Dnum=5 AND w .Essn= e.Ssn AND x.Pnumber= w.Pno))}
Query 4. Make a list of project numbers for projects that involve an employee whose last name is
‘Smith’, either as a worker or as manager of the controlling department for the project.
{ p.Pnumber | PROJECT( p) AND (((∃e)(∃w)(EMPLOYEE( e)
AND WORKS_ON( w) AND w .Pno= p.Pnumber
AND e.Lname=‘Smith’ AND e.Ssn= w.Essn) )
OR
((∃m)(∃d)(EMPLOYEE( m) AND DEPARTMENT( d)
AND p .Dnum= d.Dnumber AND d .Mgr_ssn= m.Ssn
AND m .Lname=‘Smith’)))}
Notation for Query Graphs
• Graphical representation of a query is called a query graph.
• F Relations in the query are represented by relation nodes, which are displayed as single circles.
• Constant values, typically from the query selection conditions, are represented by constant nodes,
which are displayed as double circles or ovals.
• Selection and join conditions are represented by the graph edges (the lines that connect the
nodes), as shown in below figure.
• The attributes to be retrieved from each relation are displayed in square brackets above each relation.
• Example: For every project located in ‘Stafford’, list the project number, the controlling department
number, and the department manager’s last name, birth date, and address.
Ans: { p.Pnumber, p.Dnum, m.Lname, m.Bdate, m.Address | PROJECT( p) AND EMPLOYEE( m)
AND p .Plocation=‘Stafford’ AND ((∃d)(DEPARTMENT( d) AND p .Dnum= d.Dnumber AND
d .Mgr_ssn= m.Ssn))}

16
A19PC1CS04 UNIT - II DBMS

Transforming the Universal and Existential Quantifiers

• It is possible to transform a universal quantifier into an existential quantifier, and vice versa, to get
an equivalent expression.
• One general transformation can be described informally as follows:
• Transform one type of quantifier into the other with negation (preceded by NOT); AND and OR
replace one another; a negated formula becomes unnegated; and an unnegated formula becomes
negated.
• Some special cases of this transformation can be stated as follows, where the ≡ symbol stands for
equivalent to:
(∀x) (P(x)) ≡ NOT ( ∃x) (NOT ( P(x)))
(∃x) (P(x)) ≡ NOT ( ∀x) (NOT ( P(x)))
(∀x) (P(x) AND Q (x)) ≡ NOT ( ∃x) (NOT ( P(x)) OR NOT ( Q(x)))
(∀x) (P(x) OR Q (x)) ≡ NOT ( ∃x) (NOT ( P(x)) AND NOT ( Q(x)))
(∃x) (P(x)) OR Q (x)) ≡ NOT ( ∀x) (NOT ( P(x)) AND NOT ( Q(x)))
(∃x) (P(x) AND Q (x)) ≡ NOT ( ∀x) (NOT ( P(x)) OR NOT ( Q(x)))
Notice also that the following is TRUE, where the ⇒ symbol stands for implies:
(∀x)(P(x)) ⇒ (∃x)(P(x))
NOT ( ∃x)(P(x)) ⇒ NOT ( ∀x)(P(x))

Using the Universal Quantifier in Queries

Query 3. List the names of employees who work on all the projects controlled by department number
5. One way to specify this query is to use the universal quantifier as shown:
Ans: { e. Lname, e. Fname | EMPLOYEE( e) AND ((∀x)(NOT( PROJECT( x)) OR NOT
( x. Dnum=5) OR ((∃w)(WORKS_ON( w) AND w .Essn= e.Ssn AND x .Pnumber= w. Pno))))}
Query 6. List the names of employees who have no dependents.
Ans: { e.Fname, e.Lname | EMPLOYEE( e) AND ( NOT ( ∃d)(DEPENDENT( d) AND e .Ssn=
d.Essn))}
Using the general transformation rule, we can rephrase Q6 as follows:
Ans: { e.Fname, e.Lname | EMPLOYEE( e) AND ((∀d)(NOT( DEPENDENT( d)) OR NOT( e.Ssn=
d.Essn)))}
Query 7. List the names of managers who have at least one dependent.
Ans: { e.Fname, e.Lname | EMPLOYEE( e) AND ((∃d)(∃ρ )(DEPARTMENT( d) AND
DEPENDENT( ρ ) AND e .Ssn= d.Mgr_ssn AND ρ.Essn= e.Ssn))}
This query is handled by interpreting managers who have at least one dependent as managers for
whom there exists some dependent.

17
A19PC1CS04 UNIT - II DBMS

The Domain Relational Calculus

• Domain calculus differs from tuple calculus in the type of variables used in formulas: Rather than
having variables range over tuples, the variables range over single values from domains of
attributes.
• To form a relation of degree n for a query result, we must have n of these domain variables—one
for each attribute.
• An expression of the domain calculus is of the form
{x1 , x2 , ..., xn | COND( x1 , x2 , ..., xn , xn +1, xn+2, ..., xn+m)}
• where x1 , x2 , ..., xn , xn +1, xn+2, ..., xn+m are domain variables that range over domains (of
attributes), and COND is a condition or formula of the domain relational calculus.
• A formula is made up of atoms. The atoms of a formula are slightly different from those for the
tuple calculus and can be one of the following:
1. An atom of the form R(x1 , x2 , ..., xj), where R is the name of a relation of degree j and each x i,
1 ≤ i ≤ j, is a domain variable. We can drop the commas in a list of variables; thus, we can
write:
{ x1 , x2 , ..., xn | R( x1x2x3) AND ...}
Instead of:
{ x1 , x2 , ..., xn | R( x1 , x2 , x3) AND ...}
2. An atom of the form xi op xj , where op is one of the comparison operators in the set {=, <, ≤,
>, ≥, ≠}, and xi and xj are domain variables.
3. An atom of the form xi op c or c op xj , where op is one of the comparison operators in the set
{=, <, ≤, >, ≥, ≠}, x i and xj are domain variables, and c is a constant value.
• As in tuple calculus, atoms evaluate to either TRUE or FALSE for a specific set of values, called
the truth values of the atoms.
• In a similar way to the tuple relational calculus, formulas are made up of atoms, variables, and
quantifiers.
Query 0. List the birth date and address of the employee whose name is ‘John B. Smith’.
ANS: { u, v | (∃q) (∃r) (∃s) (∃t) (∃w) (∃x) (∃y) (∃z)
(EMPLOYEE( qrstuvwxyz) AND q =‘John’ AND r =‘B’ AND s =‘Smith’)}

• An alternative shorthand notation, used in QBE (Query-By-Example), for writing this query is to
assign the constants ‘John’, ‘B’, and ‘Smith’ directly as shown in Q0A. Here, all variables not
appearing to the left of the bar are implicitly existentially quantified:
Q0A: { u , v | EMPLOYEE(‘John’,‘B’,‘Smith’,t,u,v,w,x,y,z) }

Query 1. Retrieve the name and address of all employees who work for the ‘Research’ department.
{ q, s, v | (∃z) (∃l) (∃m) (EMPLOYEE( qrstuvwxyz) AND DEPARTMENT( lmno) AND l
=‘Research’ AND m=z)}
Query 2. For every project located in ‘Stafford’, list the project number, the controlling department
number, and the department manager’s last name, birth date, and address.
{ i, k, s, u, v | (∃j)(∃m)(∃n)(∃t)(PROJECT( hijk) AND EMPLOYEE( qrstuvwxyz) AND
DEPARTMENT( lmno) AND k=m AND n=t AND j =‘Stafford’)}

18
A19PC1CS04 UNIT - II DBMS

Query 6. List the names of employees who have no dependents.

{ q, s | (∃t)(EMPLOYEE( qrstuvwxyz) AND ( NOT( ∃l)(DEPENDENT( lmnop) AND t=l)))}
Q6 can be restated using universal quantifiers instead of the existential quantifiers, as shown in Q6A:
{ q, s | (∃t)(EMPLOYEE( qrstuvwxyz) AND ((∀l)(NOT( DEPENDENT( lmnop)) OR NOT(
t=l))))}

Query 7. List the names of managers who have at least one dependent.
{ s, q | (∃t)(∃j)(∃l)(EMPLOYEE( qrstuvwxyz) AND DEPARTMENT( hijk) AND
DEPENDENT( lmnop) AND t=j AND l=t)}

19
A19PC1CS04 UNIT - II DBMS

Introduction to SQL
• SQL stands for Structured Query Language. It is used for storing and managing data in relational
database management system (RDMS).
• It is a standard language for Relational Database System. It enables a user to create, read, update and
delete relational databases and tables.
• All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard
database language.
• SQL allows users to query the database in a number of ways, using English-like statements.
Database Languages/Commands
• DMBS provides set of languages/commands to manage database.
• The different types of DBMS commands are as follows

CREATE Command:
• It is used to create databases, tables, triggers and other database objects.
• Syntax to Create a Database:
CREATE Database Database_Name;
• Syntax to create a new table:
CREATE TABLE table_name (
column_Name1 data_type ( size of the column ) Constraint ,
column_Name2 data_type ( size of the column) Constraint ,
...
column_NameN data_type ( size of the column ) Constraint ) ;
• Example: create dept table
CREATE TABLE dept (
deptno NUMBER(2,0),
dname VARCHAR2(14),
location VARCHAR2(13) );
• Creating a Table from an Existing Table
• Instead of creating a new table every time, one can also copy an existing table and its contents
including its structure, into a new table.
• This can be done using a combination of the CREATE TABLE statement and the SELECT
statement.
• Since its is copied, the new table will have the same column definitions as the original table.
• Furthermore, the new table would be populated using the existing values from the old table.
• As it is a completely new table, any changes made in it would not be reflected in the original table.

20
A19PC1CS04 UNIT - II DBMS

• Syntax:
CREATE TABLE NEW_TABLE_NAME AS
SELECT [column1, column2,...columnN]
FROM EXISTING_TABLE_NAME
[WHERE CONDITION];
• Example: creating duplicate dept2 with existing columns from dept
CREATE TABLE dept2 AS select deptno,dname from dept;

INSERT Command:
• It is used to insert new record or tuple in a table.
• It is possible to write the INSERT INTO statement in two ways:
1. Specify both the column names and the values to be inserted:
Syntax: INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Example: INSERT INTO dept(deptno,dname,location) VALUES(10, 'ACCOUNTING', 'NEW YORK');
2. Insert values for all the columns of the table
Syntax: INSERT INTO table_name VALUES (value1, value2, value3, ...);
Example: INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
3. Copy values from one table to another
• The INSERT INTO SELECT statement copies data from one table and inserts it into another table.
• The INSERT INTO SELECT statement requires that the data types in source and target tables match.
• Copy all columns from one table to another table:
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
• Copy only some columns from one table into another table:
INSERT INTO table2 (column1, column2, column3, ... columnn)
SELECT column1, column2, column3, ...columnn
FROM table1
WHERE condition;
• Example: insert into dept2 select * from dept;

SELECT Command: It is used to retrieve data from a table.

• The data returned is stored in a result table, called the result-set.
• We can retrieve the data from database in two ways
1. Retrieve all the data from table
Syntax: SELECT * FROM table_name;
Example:List all department details
SQL> select * from dept;
deptno dname location
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston

21
A19PC1CS04 UNIT - II DBMS

2. Retrieve selected attributes

Syntax: SELECT column1, column2,…,column FROM table_name;
Example: List department number and name.
SQL> select deptno,dname from dept;
deptno dname
10 Accounting
20 Research
30 Sales
40 Operations

UPDATE Command:
• UPDATE Query is used to modify the existing records in a table.
• This statement is a part of Data Manipulation Language, as it only modifies the data present in a table
without affecting the table's structure.
• Syntax: UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
• Example: change the location to Hyderabad for deptno 20.
SQL>UPDATE dept set location=”Hyderabad” where deptno=20;
DELETE Command:
• The DELETE statement is used to remove existing records in a table.
• The DELETE statement will not remove table.
• Syntax to delete specific record: DELETE FROM table_name WHERE condition;
• Example: remove the employee record 7521
SQL>DELETE FROM emp WHERE empno=7521;
• Syntax to delete all record: DELETE FROM table_name;
• Example: remove all employee records.
SQL>DELETE FROM emp;
TRUNCATE Command:
• The TRUNCATE TABLE command is used to delete all the records from an existing table by
reinitializing the table's structure.
• This command instructs the database to deallocate the space for all records in a table and change this
table's structure by resetting the table size.
• Syntax: TRUNCATE TABLE table_name;
• Example: remove all employee records.
SQL> TRUNCATE TABLE emp;
DROP Command:
• The DROP TABLE statement is a Data Definition Language (DDL) command that is used to remove a
table's definition, and its data, indexes, triggers, constraints and permission specifications (if any).
• You should be very careful while using this command because once a table is deleted then all the
information available in that table will also be lost forever.
• To drop a table in a database, one must require ALTER permission on the said table and CONTROL
permissions on the table schema.
• Syntax: DROP TABLE table_name;
• Example: remove the table emp.
SQL>DROP TABLE emp;
22
A19PC1CS04 UNIT - II DBMS

Differences between DELETE and TRUNCATE

DELETE TRUNCATE
The DELETE command in SQL removes one or more SQL's TRUNCATE command is used to remove all of
rows from a table based on the conditions specified in a the rows from a table, regardless of whether or not any
WHERE Clause. conditions are met.
It is a DML command. It is a DDL(Data Definition Language) command.
There is a need to make a manual COMMIT after making When you use the TRUNCATE command, the
changes to the DELETE command, for the modifications modifications made to the table are committed
to be committed. automatically.
It deletes rows one at a time and applies some criteria to It removes all of the information in one go.
each deletion.
The WHERE clause serves as the condition in this case. There is no necessity of using a WHERE Clause.
All rows are locked after deletion. TRUNCATE utilizes a table lock, which locks the pages
so they cannot be deleted.
It makes a record of each and every transaction in the log The only activity recorded is the deallocation of the
file. pages on which the data is stored.
It consumes a greater amount of transaction space It takes comparatively less amount of transaction space.
compared to TRUNCATE command.
If there is an identity column, the table identity is not It returns the table identity to a value it was given as a
reset to the value it had when the table was created. seed.
It requires authorization to delete. It requires table alter permission.
When it comes to large databases, it is much slower. It is faster.

ALTER Command:
• The ALTER TABLE command is used to add, delete, or modify the structure of an existing table.
• The ALTER TABLE command is used to add and drop various constraints on an existing table.
• Syntax: ALTER TABLE table_name [alter_option];
• Where, the alter_option depends on the type of operation to be performed on a table.
• Adding a new column
➢ If you need to add a new column to a table you should use the ADD option to ALTER TABLE
statement.
➢ Syntax: ALTER TABLE table_name ADD column_name datatype;
➢ Example: add mgr column to dept.
SQL>ALTER TABLE dept ADD mgr int;
• Dropping a column
➢ You can drop an existing column by using the DROP COLUMN option to ALTER TABLE
statement.
➢ Syntax: ALTER TABLE table_name DROP COLUMN column_name;
➢ Example: remove mgr column from
SQL> dept.ALTER TABLE dept DROP COLUMN mgr;
• Modify size of column or datatype of column
➢ If you want change the column size or data type in table, the use MODIFY option to ALTER
TABLE statement.
➢ The size of column may be increase or decrease.
➢ Syntax: ALTER TABLE table_name MODIFY column_name data_type(new_size);
➢ Example: change location column size from 13 to 50 in dept table.
SQL>ALTER TABLE dept MODIFY location varchar2(50);

23
A19PC1CS04 UNIT - II DBMS

➢ Syntax to change data type of column

➢ Syntax: ALTER TABLE table_name MODIFY column_name new_data_type;
➢ Example: change location datatype from varchar2 to char in dept table.
SQL>ALTER TABLE dept MODIFY location char(30);
➢ Before you modify the data type, you must clear the data in column otherwise it raises an error.
• Rename the column
➢ If you want change the name of the column in table, then use RENAME COLUMN option to
ALTER TABLE statement.
➢ Syntax: ALTER TABLE table_name RENAME COLUMN old_column to new_column;
➢ Example: change column name location to loc in dept.
SQL>ALTER TABLE dept RENAME COLUMN location to loc;
• Rename the Table
➢ If you want change the name of the table, then use RENAME TO option to ALTER TABLE
statement.
➢ Syntax: ALTER TABLE old_table_name RENAME TO new_table_name;
➢ Example: change table name from dept to department.
SQL>ALTER TABLE dept RENAME TO departmrnt;
• Add constraint
➢ If you want to add constraint like null, not null, primary key etc to the column in table, then use
ADD CONSTRAINT option to ALTER TABLE statement.
➢ Syntax: ALTER TABLE table_name ADD CONSTRAINT Constraint_Name Constraint
(column1, column2,…,columnn );
➢ Example: ALTER TABLE dep ADD CONSTRAINT unq UNIQUE( location );

➢ Adding primary key constraint

➢ Syntax: ALTER TABLE table_name ADD CONSTRAINT Constraint_Name PRIMARY
KEY(column_name);
➢ Example: ALTER TABLE dept ADD CONSTRAINT pk_deptno PRIMARY KEY(deptno);

➢ Adding foreign key constraint

➢ Syntax: ALTER TABLE table_name ADD CONSTRAINT Constraint_Name
FOREIGN KEY(column_name);
➢ Example: ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY(deptno)
REFERENCES dept(deptno);
➢ Table with the foreign key is called the child table, and the table with the primary key is called
the referenced or parent table.
• Delete constraint
➢ If you want to add constraint like null, not null, primary key etc to the column in table, then use
DROP CONSTRAINT option to ALTER TABLE statement.
➢ Syntax: ALTER TABLE table_name DROP CONSTRAINT Constraint_Name;
➢ Example: ALTER TABLE dept DROP CONSTRAINT pk_deptno;

24
A19PC1CS04 UNIT - II DBMS

COMMIT Command:
• COMMIT in SQL is a transaction control language that is used to permanently save the changes done
in the transaction in tables/databases.
• The database cannot regain its previous state after its execution of commit.
• Syntax: commit;

ROLLBACK Command:
• The ROLLBACK command is the transactional command used to undo transactions that have not
already been saved to the database. This command can only be used to undo transactions since the last
COMMIT or ROLLBACK command was issued.
• It is used to restore the database to original since the last Commit.
• Syntax: ROLLBACK;

COMMENT Command:
• Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.
• Single Line Comments syntax: --comment
• Multi Line Comments syntax: /* comment */

Basic Structure
• The fundamental structure of SQL queries includes three clauses that are select, from, and where
clause.
• Select clause specifies data to be retrieved from the table.
• From clause specifies list of tables where we can get the data.
• Where clause specifies how the relation must be operated to get the result.
• Syntax: SELECT Attribute list or expression
FROM Table List
WHERE Condition List;
• Example: List the employee names and their locations
SQL>select ename, location
from emp e,dept d
where e.deptno=d.deptno;
• Output: ENAME LOC
---------- -------------
KING NEW YORK
BLAKE CHICAGO
CLARK NEW YORK
JONES DALLAS
SCOTT DALLAS
FORD DALLAS
SMITH DALLAS
ALLEN CHICAGO
WARD CHICAGO
MARTIN CHICAGO

25
A19PC1CS04 UNIT - II DBMS

TURNER CHICAGO
ADAMS DALLAS
JAMES CHICAGO
MILLER NEW YORK
Clauses in SELECT statement:
1. FROM clause
2. WHERE clause
3. ORDER BY clause
4. GROUP BY clause
5. HAVING clause

1. FROM clause

• FROM clause specifies list of tables where we can get the data.
• It can also be used to retrieve records from multiple tables using JOIN condition.
• Syntax: select column1, column2,…,column from table1,…,tablen;
• Example: select ename, deptno from emp;

2. WHERE clause

• WHERE clause is used to filter records while retrieving data from a single table or multiple tables.
• This specifies a condition while fetching the data and if this condition is satisfied, then it returns
filtered data from the table.
• Syntax: SELECT column1, column2,... columnN
FROM table_name
WHERE [condition]
• Example: select ename, location
from emp e,dept d
where e.deptno=d.deptno;

3. ORDER BY clause

• The ORDER BY clause is used to sort the data in either ascending or descending order, based on
one or more columns.
• Sorting by multiple columns can be helpful when you need to sort data hierarchically, such as sorting
by state, city, and then by the person's name.
• ORDER BY is used with the SELECT statement and is usually specified after the WHERE,
HAVING, and GROUP BY clauses, if present in the query.
• Syntax: SELECT column-list
FROM table_name
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
• Example: SELECT * from emp order by ename;
• Example: SELECT * from emp order by ename DESC;
• Example: SELECT * from emp order by deptno, sal;
• Here, ASC means ascending order and DESC means descending order. If option not specify, the
SQL sort the data in ascending order.

26
A19PC1CS04 UNIT - II DBMS

4. GROUP BY clause:
• The GROUP BY clause is used in conjunction with the SELECT statement to arrange identical
data into groups.
• This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the
ORDER BY or HAVING clause (if they exist).
• It is often used with aggregate functions like SUM, COUNT, AVG, MAX, or MIN, which allows
us to perform calculations on the grouped data.
• Syntax: SELECT column_name(s)
FROM table_name
GROUP BY column_name(s);
• Group by single column:
select deptno, sum(sal) as TotalSal from emp group by deptno;
• Group by multiple column:
select deptno,job, sum(sal) as TotalSal from emp group by deptno,job;
5. HAVING clause
• HAVING clause is similar to the WHERE clause but the difference is GROUP BY filter grouped
rows instead of single rows.
• These rows are grouped together by the GROUP BY clause, so, the HAVING clause must always
be followed by the GROUP BY clause.
• It can be used with aggregate functions, whereas the WHERE clause cannot.
• Syntax: SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2
HAVING condition;
• Example: select deptno, sum(sal) as TotalSal from emp group by deptno having sum(sal)>2000;

Note: The order of using clauses is as follow

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

27
A19PC1CS04 UNIT - II DBMS

Set operations
• set operators are used to join the results of two (or more) SELECT statements.
• Set operators can be the part of sub queries.
• Set operators can't be used in SELECT statements containing TABLE collection expressions.
• The SET operators available in SQL are UNION, UNION ALL, INTERSECT, and EXCEPT.
• Rules to perform SET operations in SQL:
1. The number and order of columns must be the same.
2. Data types must be compatible /implicitly convertible.

UNION
• UNION will be used to combine the result of two select statements.
• Duplicate rows will be eliminated from the results obtained after performing the UNION operation.
• Syntax: SELECT column1, column2, …, column FROM table1
UNION
SELECT column1, column2, …, column FROM table2
UNION
SELECT column1, column2, …, column FROM tablen
• Example: combine the department number in emp and dept.
SQL> select deptno from emp UNION select deptno from dept;
Output: DEPTNO
----------
10
30
20
40
UNION ALL
• This operator combines all the records from both the queries.
• Duplicate rows will be not be eliminated from the results obtained after performing the UNION
ALL operation.
• Syntax: SELECT column1,column2,…,column FROM table1
UNION ALL
SELECT column1,column2,…,columnnFROM table2
• Example: select deptno from emp union all select deptno from dept;

DEPTNO 30
---------- 30
10 20
30 30
10 10
20 10
20 20
20 30
20 40
30
30 18 rows selected.

28
A19PC1CS04 UNIT - II DBMS

INTERSECT
• Using INTERSECT operator, Oracle displays the common rows from both the SELECT
statements, with no duplicates
• Syntax: SELECT column1,column2,…,column FROM table1
INTERSECT
SELECT column1,column2,…,columnnFROM table2
• Example: select deptno from emp intersect select deptno from dept;
• Output: DEPTNO
----------
10
30
20
EXCEPT OR MINUS
• It displays the rows which are present in the first query but absent in the second query with no
duplicates.
• Syntax: SELECT column1,column2,…,column FROM table1
EXCEPT
SELECT column1,column2,…,columnnFROM table2
• Example: select deptno from dept minus select deptno from emp;

• Output: DEPTNO
----------
40

29
A19PC1CS04 UNIT - II DBMS

Aggregate Operations/Functions:
• SQL aggregation function is used to perform the calculations on multiple rows of a single column of a
table and returns a single value.
• It is also used to summarize the data.
• Aggregate Functions are COUNT(), SUM(), AVG(), MIN(), and MAX().
1. COUNT ( )
• 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.
• Syntax: COUNT(*) or COUNT( [ALL|DISTINCT] expression )
• Here, Count(*): Returns total number of records in table.
Count(column): Return number of Non Null values over the column.
Count(Distinct column): Return number of distinct Non Null values over the column.
Example1: List the number of records in emp table
SQL> select count(*) from emp;
COUNT(*)
------------
14
Example2: List the number of records in mgr column
SQL> select count(mgr) from emp;
COUNT(MGR)
----------
13
Example3: List the number of records in mgr column without duplicates
SQL> select count(distinct mgr) from emp;
COUNT(DISTINCTMGR)
------------------
6
2. SUM ( )
• Sum function is used to calculate the sum of all selected columns.
• It works on numeric fields only.
• Syntax: SUM( ) or SUM( [ALL|DISTINCT] expression )
• Here, sum(column): Sum all Non Null values of Column.
sum(Distinct column): Sum of all distinct Non-Null values.
• Example1: display the total salary paid to employees.
SQL> select sum(distinct sal) from emp;
SUM(DISTINCTSAL)
----------------
24775
• Example2: display the total salary paid to employees without duplicates.
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025

30
A19PC1CS04 UNIT - II DBMS

3. AVG ( )
• 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: AVG ( ) or AVG( [ALL|DISTINCT] expression )
• Example1: Find the average salary of employee.
SQL> select sum(sal),count(sal),avg(sal) from emp;
SUM(SAL) COUNT(SAL) AVG(SAL)
---------- ---------- ----------
29025 14 2073.21429
• Example2: Find the average salary of employee without duplicates.
SQL> select sum(distinct sal),count(distinct sal),avg(distinct sal) from emp;
SUM(DISTINCTSAL) COUNT(DISTINCTSAL) AVG(DISTINCTSAL)
---------------- ------------------ ----------------
24775 12 2064.58333

4. MAX ( )

• 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: MAX ( ) or MAX( [ALL|DISTINCT] expression )
• Example1: Find the maximum salary of employee.
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
5. MIN ( )
• 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: MAX ( ) or MAX( [ALL|DISTINCT] expression )
• Example1: Find the minimum salary of employee.
SQL> select min(sal) from emp;
MIN(SAL)
----------
800

31
A19PC1CS04 UNIT - II DBMS

Join Operations
• In a relational database, the data stored are all related but scattered throughout the database as multiple
tables.
• So, if there arises a need to retrieve cohesive data from multiple tables, a simple Join clause can be used.
• The Joins clause is used to combine records from two or more tables in a database.
• With a Join clause, fields from two tables can be combined by using values common to each.
• There are various types of Joins provided by SQL
1. CROSS JOIN
2. INNER JOIN
3. SELF JOIN
4. OUTER JOIN
1. CROSS JOIN:
• CROSS JOIN combines all of the possibilities of two or more tables and returns a result that
includes every row from all contributing tables.
• It's also known as CARTESIAN JOIN because it produces the Cartesian product of all linked
tables.
• The Cartesian product represents all rows present in the first table multiplied by all rows present
in the second table.
• The following visual representation of CROSS JOIN

Syntax: SELECT column_lists FROM table1 CROSS JOIN table2;

Example: List the details of dept and salgrade.
SQL> select * from dept cross join salgrade;

DEPTNO DNAME LOC GRADE LOSAL HISAL

---------- -------------- ------------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 1 700 1200
10 ACCOUNTING NEW YORK 2 1201 1400
10 ACCOUNTING NEW YORK 3 1401 2000
10 ACCOUNTING NEW YORK 4 2001 3000
10 ACCOUNTING NEW YORK 5 3001 9999
20 RESEARCH DALLAS 1 700 1200
20 RESEARCH DALLAS 2 1201 1400
20 RESEARCH DALLAS 3 1401 2000
20 RESEARCH DALLAS 4 2001 3000
20 RESEARCH DALLAS 5 3001 9999
30 SALES CHICAGO 1 700 1200
30 SALES CHICAGO 2 1201 1400
30 SALES CHICAGO 3 1401 2000
32
A19PC1CS04 UNIT - II DBMS

30 SALES CHICAGO 4 2001 3000

30 SALES CHICAGO 5 3001 9999
40 OPERATIONS BOSTON 1 700 1200
40 OPERATIONS BOSTON 2 1201 1400
40 OPERATIONS BOSTON 3 1401 2000
40 OPERATIONS BOSTON 4 2001 3000
40 OPERATIONS BOSTON 5 3001 9999

2. INNER JOIN:
• INNER JOIN returns all records from multiple tables that satisfy the specified join condition.
• It compares each row of the first table with each row of the second table.
• If the pairs of these rows satisfy the join-predicate, they are joined together.
• An Inner Join retrieves works as intersection operation.
• This is a default join.
• The following visual representation of INNER JOIN

Syntax: SELECT columns_list

FROM table1 INNER JOIN table2 ON condition1
INNER JOIN table3 ON condition2;
Example: List the employee names and dept names with which they are associated.
SQL> select ename,dname from emp e inner join dept d on e.deptno=d.deptno;
(or)
SQL> select ename,dname from emp e,dept d where e.deptno=d.deptno;
Output: ENAME DNAME
---------- --------------
KING ACCOUNTING
BLAKE SALES
CLARK ACCOUNTING
JONES RESEARCH
SCOTT RESEARCH
FORD RESEARCH
SMITH RESEARCH
ALLEN SALES
WARD SALES
MARTIN SALES
TURNER SALES
ADAMS RESEARCH
JAMES SALES
MILLER ACCOUNTING

33
A19PC1CS04 UNIT - II DBMS

3. SELF JOIN:
• A table is joined to itself is called SELF JOIN.
• It means that each table row is combined with itself and with every other table row.
• We can do this with the help of table name aliases to assign a specific name to each table's
instance.
• The table aliases enable us to use the table's temporary name that we are going to use in the
query.
• It's a useful way to extract hierarchical data and comparing rows inside a single table.
• Syntax: SELECT T1.col_name, T2.col_name...
FROM table1 T1, table1 T2
WHERE join_condition;

Example: List employee names and their manager names.

SQL> select e.ename as ENAME,m.ename as MANAGER from emp e, emp m
where m.mgr=e.empno;

ENAME MANAGER
---------- ----------
KING BLAKE
KING CLARK
KING JONES
JONES SCOTT
JONES FORD
FORD SMITH
BLAKE ALLEN
BLAKE WARD
BLAKE MARTIN
BLAKE TURNER
SCOTT ADAMS
BLAKE JAMES
CLARK MILLER

4. OUTER JOIN:
• An Outer Join retrieves all the records in two tables even if there is no counterpart row of one
table in another table, like Inner Join.
• Outer join is further divided into three subtypes: Left Outer Join, Right Outer Join and Full
Outer Join.
Left Outer Join:
• The LEFT OUTER JOIN retrieves all the records from the left table and matching rows
from the right table.
• It will return NULL when no matching record is found in the right side table.
• Since OUTER is an optional keyword, it is also known as LEFT JOIN.
• Syntax: SELECT column_lists FROM table1
LEFT [OUTER] JOIN table2 ON table1.column = table2.column;
• The below visual representation illustrates the LEFT OUTER JOIN:

34
A19PC1CS04 UNIT - II DBMS

Right Outer Join:

• The RIGHT OUTER JOIN retrieves all the records from the right-hand table and
matched rows from the left-hand table.
• It will return NULL when no matching record is found in the left-hand table.
• Since OUTER is an optional keyword, it is also known as RIGHT JOIN.
• Syntax: SELECT column_lists FROM table1 RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
• The below visual representation illustrates the RIGHT OUTER JOIN:

Full Outer Join:

• The FULL OUTER JOIN in SQL Server returns a result that includes all rows from
both tables.
• The columns of the right-hand table return NULL when no matching records are found in
the left-hand table.
• And if no matching records are found in the right-hand table, the left-hand table column
returns NULL.
• Syntax: SELECT column_lists FROM table1 FULL [OUTER] JOIN table2
ON table1.column = table2.column;
• The below visual representation illustrates the FULL OUTER JOIN:

35
A19PC1CS04 UNIT - II DBMS

Sub queries and correlated queries

Sub Queries:
• A query with in another query is called subquery/inner query/nested query.
• The result of inner query is used in execution of outer query.
• Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with
the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
• In select statement, subquery can be used in where, from, and having clauses.
• 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.
• A subquery is enclosed in parentheses.
• Syntax: SELECT column_name
FROM table_name
WHERE column_name expression operator
( SELECT column_name from table_name WHERE Condition);
• Example: Find all the employees who earn more than the average salary.
SQL> select ename,sal from emp where sal >(select avg(sal) from emp);
ENAME SAL
---------- ----------
KING 5000
BLAKE 2850
CLARK 2450
JONES 2975
SCOTT 3000
FORD 3000

Correlated Subqueries
• Correlated subqueries are used for row-by-row processing. Each subquery is executed once for every
row of the outer query.
• A correlated subquery is evaluated once for each row processed by the parent statement. The parent
statement can be a SELECT, UPDATE, or DELETE statement.
• Syntax:
SELECT column1, column2, ....
FROM table1 outer
WHERE column1 operator (SELECT column1, column2
FROM table2
WHERE expr1 = outer.expr2);

Note:

With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to
be used by the main query. A correlated subquery, executes once for each candidate row considered by the
outer query. In other words, the inner query is driven by the outer query.

36
A19PC1CS04 UNIT - II DBMS

Subqueries with the INSERT Statement

• Subqueries also can be used with INSERT statements.
• The INSERT statement uses the data returned from the subquery to insert into another table.
• The selected data in the subquery can be modified with any of the character, date or number functions.
• Syntax
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2]
FROM table1 [, table2]
[ WHERE VALUE OPERATOR]
• Example
insert into dept2 select * from dept;

Subqueries with the UPDATE Statement

• The subquery can be used in conjunction with the UPDATE statement. Either single or multiple
columns in a table can be updated when using a subquery with the UPDATE statement.
• Syntax
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
• Example
UPDATE EMPLOYEE
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 29);

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
DELETE FROM TABLE_NAME
WHERE VALUE OPERATOR
(SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE condition);
• Example:
DELETE FROM EMPLOYEE
WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP
WHERE AGE >= 29);

37
A19PC1CS04 UNIT - II DBMS

Views:
• Views in SQL are called of virtual tables. A view also has rows and columns as they are in a real table
in the database.
• We can create a view by selecting fields from one or more tables present in the database.
• A View can either have all the rows of a table or specific rows based on certain condition.
• We can perform creating, deleting and updating Views with some conditions.
• Sample table:
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

CREATING VIEWS
• We can create View using CREATE VIEW statement. A View can be created from a single table
or multiple tables.
• Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

• Creating View from a single table: In this example, we create a View named DetailsView from
the table Student_Detail.
Query:
CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM Student_Details
WHERE STU_ID < 4;
• Creating View from multiple tables
In the given example, a view is created named MarksView from two tables Student_Detail and
Student_Marks.

Query:
CREATE VIEW MarksView AS
SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
FROM Student_Detail, Student_Mark
WHERE Student_Detail.NAME = Student_Marks.NAME;

38
A19PC1CS04 UNIT - II DBMS

DELETING VIEWS
• SQL allows us to delete an existing View.
• We can delete or drop a View using the DROP statement.
• Syntax: DROP VIEW view_name;
• For example, if we want to delete the View MarksView, we can do this as:
DROP VIEW MarksView;

UPDATING VIEWS
• There are certain conditions needed to be satisfied to update a view.
• If any one of these conditions is not met, then we will not be allowed to update the view.
1. The SELECT statement which is used to create the view should not include GROUP BY clause
or ORDER BY clause.
2. The SELECT statement should not have the DISTINCT keyword.
3. The View should have all NOT NULL values.
4. The view should not be created using nested queries or complex queries.
5. The view should be created from a single table. If the view is created using multiple tables,
then we will not be allowed to update the view.
• We can use the CREATE OR REPLACE VIEW statement to add or remove fields from a
view. Syntax:

Inserting a row in a view

• We can insert a row in a View in a same way as we do in a table.
• We can use the INSERT INTO statement of SQL to insert a row in a View.
• Syntax:
INSERT INTO view_name(column1, column2 , column3,..) VALUES (value1, value2, value3..);

Deleting a row from a View

• Deleting rows from a view is also as simple as deleting rows from a table.
• We can use the DELETE statement of SQL to delete rows from a view.
• Also deleting a row from a view first delete the row from the actual table and the change is then
reflected in the view.
• Syntax: DELETE FROM view_name WHERE condition;

Uses of a View: A good database should contain views due to the given reasons:
1. Restricting data access – Views provide an additional level of table security by restricting access to a
predetermined set of rows and columns of a table.
2. Hiding data complexity – A view can hide the complexity that exists in multiple tables join.
3. Simplify commands for the user – Views allow the user to select information from multiple tables
without requiring the users to actually know how to perform a join.
4. Store complex queries – Views can be used to store complex queries.
5. Rename Columns – Views can also be used to rename the columns without affecting the base tables
provided the number of columns in view must match the number of columns specified in select
statement. Thus, renaming helps to hide the names of the columns of the base tables.
6. Multiple view facility – Different views can be created on the same table for different users.

39
A19PC1CS04 UNIT - II DBMS

PL/SQL
Pl/SQL stands for "Procedural Language extension of SQL" that is used in Oracle.
PL/SQL is a block structured language that can have multiple blocks in it.
PL/SQL includes procedural language elements like conditions and loops. It allows declaration of
constants and variables, procedures and functions, types and variable of those types and triggers. It can
support Array and handle exceptions (runtime errors).
PL/SQL is not case sensitive so you are free to use lower case letters or upper case letters except within
string and character literals.
structure of a PL/SQL block
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
S.No Sections & Description
Declarations
1 This section starts with the keyword DECLARE. It is an optional section and defines all variables,
cursors, subprograms, and other elements to be used in the program.
Executable Commands
This section is enclosed between the keywords BEGIN and END and it is a mandatory section. It
2
consists of the executable PL/SQL statements of the program. It should have at least one executable line
of code, which may be just a NULL command to indicate that nothing should be executed.
Exception Handling
3 This section starts with the keyword EXCEPTION. This optional section contains exception(s) that
handle errors in the program.

Example: Hello World

DECLARE
message varchar2(20) := 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/

40