Database Management Systems

UNIT-2

1
 Content
SQL Queries and Constraints:
• SQL Data Definition

• Types of SQL Commands

• Form of Basic SQL Query

• SQL Operators

• Set Operators

• Nested Queries

• Aggregate Operators

• NULL values

• Integrity Constraints Over Relations

• Joins 2
 Content
• Introduction to Views
• Destroying / Altering Tables and Views
• Cursors
• Triggers and Active Databases.

3
 Structured Query Language(SQL)
• SQL is Structured Query Language, which is a
computer language for storing, manipulating and
retrieving data stored in a relational database.
• SQL is the standard language for Relational
Database System. All the Relational Database
Management Systems (RDMS) like MySQL, MS
Access, Oracle, Sybase, Informix, Postgres and
SQL Server use SQL as their standard database
language.
4
 Why SQL ?
SQL is widely popular because it offers the following advantages
• SQL is easy to learn.
• Allows users to access data in the relational database
management systems.
•Allows users to describe the data.
•Allows users to define the data in a database and manipulate that
data.
•Allows to embed within other languages using SQL modules,
libraries & pre-compilers.
•Allows users to create and drop databases and tables.
•Allows users to create view, stored procedure, functions in a
database.
•Allows users to set permissions on tables, procedures and views.
5
 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.
6
 Domain Types in SQL
• char(n). Fixed length character string, with
user-specified length n.
• varchar(n). Variable length character strings, with
user-specified maximum length n.
• int. Integer (a finite subset of the integers that is
machine-dependent).
• smallint. Small integer (a machine-dependent subset
of the integer domain type).
• numeric(p,d). Fixed point number, with
user-specified precision of p digits, with d digits to
the right of decimal point. (ex., numeric(3,1), allows
44.5 to be stores exactly, but not 444.5 or 0.32)
7
real, double precision. Floating point and
double-precision floating point numbers, with
machine-dependent precision.
float(n). Floating point number, with
user-specified precision of at least n digits.

8
SQL Commands

9
Form of basic sql

10
 Agenda
• Database Basics
• SQL Commands
– SELECT … FROM
– WHERE
– ORDER BY
– GROUP BY
– HAVING

DBMS by Dr. B Sankara Babui, Professor, CSE 11

 Database Basics
In a relational database, data is stored in tables.

Tables

Database
DBMS by Dr. B Sankara Babui, Professor, CSE 12
 Database Basics
Each table consists of columns and rows. Each column is
a field in a record, and there is a column name associated
with each column. Columns
Tables

Database
DBMS by Dr. B Sankara Babui, Professor, CSE 13
 Database Basics
Each row represents one record. When we say how
many records we have, we are referring to the number of
rows.
Columns
Tables

Rows

Database
DBMS by Dr. B Sankara Babui, Professor, CSE 14
 SELECT … FROM
SQL is structured similar to the English language. The basic
command for retrieving data from a database table is to
SELECT data FROM a table. Not surprisingly, the keywords
"SELECT" and "FROM" make up the core of a SQL
statement.

The syntax for “SELECT… FROM” is:

SELECT “COLUMN_NAME”
FROM “TABLE_NAME”

DBMS by Dr. B Sankara Babui, Professor, CSE 15

 SELECT … FROM
Different ways of selecting data:
Select more than 1 column:
SELECT “COLUMN_NAME_1”, “COLUMN_NAME_2”
FROM “TABLE_NAME”

Select all columns: Select unique values:

SELECT * SELECT DISTINCT “Column_Name”
FROM “TABLE_NAME” FROM “TABLE_NAME”

DBMS by Dr. B Sankara Babui, Professor, CSE 16

 WHERE
Sometimes we want to retrieve only a subset of the data.
In those cases, we use the “WHERE” keyword.

The syntax for “WHERE” is:

SELECT “COLUMN_NAME”
FROM “TABLE_NAME”
WHERE “CONDITION”

CONDITION represents how we want the data to be filtered.

DBMS by Dr. B Sankara Babui, Professor, CSE 17

 ORDER BY
When we want to list the results in a particular order (ascending
or descending), we use the ORDER BY keyword at the end of the
SQL statement.

The syntax for “ORDER BY” is:

SELECT “COLUMN_NAME” FROM
“TABLE_NAME” WHERE “CONDITION”
ORDER BY “COLUMN_NAME” [ASC | DESC]

DBMS by Dr. B Sankara Babui, Professor, CSE 18

 MATHEMATICAL FUNCTIONS

SQL has built-in mathematical functions to allow us to

perform mathematical operations on the data.
Common mathematical functions include:
•SUM
•AVG
•COUNT
•MAX
•MIN

DBMS by Dr. B Sankara Babui, Professor, CSE 19

 GROUP BY

To find the highest Sales_Amount across all stores, we use the

MAX( ) function in the following SQL:

SALES_HISTORY
Date Store
Sales_Amount SELECT MAX(Sales_Amount)
FROM SALES_HISTORY;

DBMS by Dr. B Sankara Babui, Professor, CSE 20

 GROUP BY

To find the highest Sales_Amount for each store, we change

the SELECT portion to include “Store”:

SALES_HISTORY
Date Store
Sales_Amount SELECT Store, MAX(Sales_Amount)
FROM SALES_HISTORY;

DBMS by Dr. B Sankara Babui, Professor, CSE 21

 GROUP BY
However, this SELECT statement by itself is not enough. To
allow SQL to correctly calculate what we want, we need to use
the GROUP BY keyword. In the following example, the
Store column after GROUP BY tells SQL to apply the MAX
function for each Store.
SALES_HISTORY
Date Store SELECT Store, MAX(Sales_Amount)
Sales_Amount
FROM SALES_HISTORY
GROUP BY Store;

DBMS by Dr. B Sankara Babui, Professor, CSE 22

 GROUP BY
To summarize, the syntax for GROUP BY is as follows:

SELECT “COLUMN_NAME_1”,
FUNCTION(“COLUMN_NAME_2”)
FROM “TABLE_NAME” WHERE
“CONDITION”
GROUP BY “COLUMN_NAME_1”

DBMS by Dr. B Sankara Babui, Professor, CSE 23

 HAVING
•Previously we had talked about using the WHERE
keyword to filter results.
•We cannot use WHERE to filter based on the result of a
function, because we need to specify the filtering
condition after SQL has calculated the function, and
consequently any filtering condition based on the
function needs to be specified after the GROUP BY
phrase.
•We cannot use the WHERE keyword because it is
always used before GROUP BY.
•HAVING is used to filter based on the result of a
function.
DBMS by Dr. B Sankara Babui, Professor, CSE 24
 HAVING
The syntax for HAVING is as follows:

SELECT “COLUMN_NAME_1”,
FUNCTION(“COLUMN_NAME_2”)
FROM “TABLE_NAME”
GROUP BY “COLUMN_NAME_1”
HAVING (CONDITION based on
FUNCTION)

DBMS by Dr. B Sankara Babui, Professor, CSE 25

 SQL Clauses
A SELECT statement has the following order:
•SELECT … FROM
•WHERE
•GROUP BY
•HAVING
•ORDER BY

DBMS by Dr. B Sankara Babui, Professor, CSE 26

 HAVING
Using the SALES_HISTORY table we had earlier. If we
want to sum the sales amount for each store, but only want to
see results for stores with total sales amount greater than 100,
we use the following SQL:
SALES_HISTORY
Date Store SELECT Store, SUM(Sales_Amount)
Sales_Amount
FROM SALES_HISTORY
GROUP BY Store
HAVING SUM(Sales_Amount) > 100;

DBMS by Dr. B Sankara Babui, Professor, CSE 27

 Multiple Choice Questions
1. If we want to display the results in descending order which sql clause will
be used?
A) SELECT
B) FROM
C) WHERE
D) ORDER BY

2. What is the outcome of the following sql statement

Select Sum(sal)/count( sal) from emp;
A) Display Sum of all salaries
B) Display total number of records
C) Display Average of Salaries
D) Display error message
28
3. What is the meaning of “GROUP BY” clause in sql?
a) Group data by column values
b) Group data by row values
c) Group data by column and row values
d) None of the mentioned

4. What is the meaning of “HAVING” clause in sql?

a) To filter out the row values
b) To filter out the column values
c) To filter out the row and column values
d) None of the mentioned
5. Which of the following belongs to an “aggregate function”?
a) COUNT
b) SUM/AVG
c) MIN/MAX
d) All of the mentioned

29
 SQL Languages
(DDL,DML,DCL,TCL )

30
 SQL
Comm
ands

31
 DDL
• SCHEMA
– A database schema is the skeleton structure that
represents the logical view of the entire database.
It defines how the data is organized and how the
relations among them are associated.
• DATA DEFINATION LANGUAGE
– Are collection of those statements which are
DIRECTLY related to database Schema
• DDL STATEMENTS ARE
– CREATE, ALTER, DROP, REPLACE
TRUNCATE 32
 CREATE
• Used To Create Objects Like
– CREATE TABLE CREATE TABLE
(
STUDENT
– CREATE STDROLLNO number(5) primary
key, NAME varchar2(20),
FUNCTION CAST varchar2(20)
– CREATE VIEW );
– Etc.
STDROLLNO NAME CAST

33
 ALTER
• Use to Alter Objects like
– ALTER TABLE ALTER TABLE
(STUDENT ADD
– ALTER USER ADDRESS
NVARCHAR2(50)
– ALTER );
DATABASE
STDROLLN
STDROLL NAME
NAM CAST C SADDRESS
O
NO E A T

34
 DROP
• Use to Drop Objects CREATE TABLE EXTRA
(
like NUM number(5) primary
key, NAME nvarchar2(20)
– DROP TABLE );
– DROP USER NUM NAME
– DROP FUNCTION
– Etc DROP TABLE
EXTRA;

35
 REPLACE
• Use to Rename table names.
RENAME STUDENT TO STUDENTS;

STDROLLN NAME CAST ADDRESS

O

36
 TRUNCAT
E
• Use to truncate (delete all rows) a
table.

TRUNCATE TABLE STUDENT;

37
 DML
• Data manipulation language (DML)
• Are the statements query and
manipulate data in existing schema
objects.
– INSERT
– UPDATE
– DELETE

38
 INSERT
• Insert into STUDENTS values
(3,’Atif’,’Bhatti’,’UK’);
• Insert into STUDENTS values
(13,’Ahsan’,’Khanzada’,’SK’);
STDROLLN NAME CAST ADDRESS
O
3 Atif Bhatti UK
13 Ahsan Khanzada SK

39
 UPDATE
• USED TO MODIFY DATA IN DB
UPDATE STUDENTS SET
ADDRESS=‘NAWABSHAH’ WHERE
NAME=‘Atif”;
STDROLLN
STDROLL NAME
NAM CAST
CA ADDRESS
ADDRE
O
NO E ST SS
3
3 Ati
Atif Bhatt
Bhatti U
Nawabsha
f i K
h
1 Ahsa Khanza S
13
3 Ahsan
n Khanzada
da SK
K

40
 DELETE
• Use the DELETE statement to delete the
rows from existing tables which are in
your schema or if you have DELETE
privilege on them.
DELETE STUDENTS WHERE
NAME=‘Ahsan’;
STDROLLN NAME CAST ADDRESS
O
3 Atif Bhatti Nawabsha
h
13 Ahsan Khanzada SK
41
 DCL
• Data Control Language (DCL)
Statements
– Data Control Language Statements are
used to grant privileges on tables, views,
procedures to other users or roles.
– The DCL statements are
• GRANK
• REVOKE

42
 GRANT
• Use to grant privileges to other users or
roles.

grant select, update, insert on

STUDENTS to David;

43
 REVOKE
• Use to take back privileges granted to
other users and roles.

revoke select, update, insert on emp from

David;

44
 TCL
Transaction Control Language (TCL)
Statements
•COMMIT

Syntax:

COMMIT [work];

45
• ROLLBACK

Syntax:

ROLLBACK [work] { [TO] [SAVE POINT]

savepoint id];
Or
SAVEPOINT savepoint_id;

46
 Multiple Choice Questions
1. What is the full form of SQL?
A. Structured Query Language
B. Structured Query List
C. Simple Query Language
D. None of these

2. In SQL, which of the following is not a data definition language commands?

A.RENAME
B. TRUNCATE
C. ALTER
D. UPDATE

3. In SQL, which command(s) is(are) used to change a table's storage

characteristics?
A.. ALTER TABLE
B. MODIFY TABLE
C. CHANGE TABLE
D. All of the above
47
4. A command that lets you change one or more fields in a record is
A. Insert
B. Modify
C. Look-up
D. All of the above

5 DCL Stands for

A.Data Communication Language
B.Data Connecting Language
C.Data Control Language
D.Data Committing Language

48
SQL OPERATORS

49
Various types of constraints

PRIMARY KEY
UNIQUE KEY Integrity constraints
NOT NULL
CHECK
FOREIGN KEY Referential Integrity
Constraints

50
Data types used in SQL
• VARCHAR2(size) OR
VARCHAR
• NVARCHAR2(size)
• NUMBER(p,s)
• LONG
• DATE
• RAW(size)
• LONG RAW
• ROWID
• CHAR(size)
• NCHAR(size)
• CLOB
• NCLOB
• BLOB
• BFILE 51
PSEUDO COLUMN
A pseudo column behaves like a table column,
but is not actually stored in the table. You can
select from pseudo columns, but you cannot
insert, update, or delete their values.

Example

* CURRVAL
* NEXTVAL
* SYSDATE
* LEVEL
* ROWID
* ROWNUM

52
Some System tables

USER_USERS - Contains information about all

the users.

TAB - Contains information about all

the objects created by the user.

DUAL - Dummy table containing one

column and one row.

53
SQL OPERATORS
• Arithmetic Operators
• Relational operators
• Logical Operators
• Set operators
• Special Operators

54
Arithmetic Expressions
Create expressions with number and date
data by using arithmetic operators.

Operat Description
or
Add
+
Subtract
-
Multiply
*
Divide
/

55
Relational Operators
=

<
>

!= or <>

>=

<=

56
Logical Operators

AND

OR

NOT

57
SET OPERATORS
UNION

UNION ALL

INTERSECT

MINUS

58
SPECIAL OPERATORS
BETWEEN.. -checks between two values
IN -checks to match with any of a list
of values
LIKE - checks for pattern matching
IS NULL - checks for nulls
ALL - checks for all values in the list
ANY -checks for any of the value in the
list
EXISTS - checks if a subquery returns at
least one row
ROLLUP - calculate multiple levels of
subtotals
59
CUBE - fraction of possible subtotals
EXERCISE-1
1. Select all the information from
the emp table.
2. Display ename, job and hiredate
from employee table
3. Select all employees who have a
salary between 1000 and 2000
4. List department numbers and
names in department name
order.
5. List the details of the employees
in department 10 and 20 in
alphabetical order of names.
60
6 . List names and jobs of all clerks in
department no. 20.
7. Display all employees whose names starts
with letter ‘S’.
8. Display all employees whose names ends
with letter ‘T’.
9. Display all employees names which have
TH or LL in their name
10. List the details of all employees who
have a manager.

61
11. Display all employees who were hired
during 1983.
12. Display name and remuneration of all
employees.
13. Display annual salary and commission of
all sales people whose monthly salary is
greater than their commission. The
output should be ordered by salary,
highest first. If two or more employees
have the same salary sort by employees
name within the highest salary order.
14. List all employees who have name exactly
4 characters in length.
15. List all employees who have no manager.
62
16. Find employees whose salary is not
between a range 1000 and 2000.
17. Find those employees whose job does not
start with M.
18. Find clerks who earn between 2000 and
4000 salary.
19. Write a SQL statement to return all
managers with salaries over 1500, and all
salesmen.
20. Display all different job types.

63
Multiple Choice Questions
1. Which operator performs pattern matching?
A. BETWEEN operator
B. LIKE operator
C. EXISTS operator
D. None of these
2. What operator tests column for the absence of data?
A. EXISTS operator
B. NOT operator
C.. IS NULL operator
D. None of these
3. In SQL, which command is used to SELECT only one copy of each
set of duplicable rows
A. SELECT DISTINCT
B. SELECT UNIQUE
C. SELECT DIFFERENT
D. All of the above 64
4. Which of the following is a valid SQL type?
A. CHARACTER
B. NUMERIC
C. FLOAT
D. All of the above
5. Which one is not a Pseudo column
A. Sysdate
B. Level
C. Rownum
D. View

65
SQL Subqueries

66
Subqueries

67
Syntax for
subqueries:
Subqueries(cont’d)

68
 Subqueries(cont’d)

•The subquery(inner query) is

executed once before the main
query.
•The result of subquery is used by the
main query(outer query).

69
 Subqueries(cont’d)

There are following types

of subqueries:
•Single row
•Multiple row

70
 Subqueries(cont’d)

Guidelines for using subqueries:

•Enclose subquery in parenthesis
•Place subquery on the right side of
comparison condition
•Use single row operators with single
row queries and multiple with multiple
queries

71
 Subqueries(cont’d)

There are two types of subqueries:

•Single row subqueries
•Multiple row subqueries

72
 Subqueries(cont’d)

Single row subqueries:

•Returns only one row
•Use single row comparison operators i.e.
=, < , > , <= ,>=, <>

73
 Subqueries(cont’d)
Single row
subqueries:
•Example: SELECT
* FROM teacher
WHERE dept_id =
(SELECT dept_id
FROM department
WHERE dept_name
= ‘UIIT’);

74
 Subqueries(cont’d)
More than one single row
subqueries:
•Example:
SELECT *
FROM teacher
WHERE dept_id =
(SELECT dept_id
FROM department
WHERE dept_name =
‘UIIT’)
AND salary >
(SELECT AVG(salary)
FROM teacher) ;
75
 Subqueries(cont’d)
HAVING clause single row
subqueries:
•Example:
SELECT dept_id, MIN(salary)
FROM employee
GROUP BY dept_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employee
WHERE dept_name = ‘UIIT’);

76
 Subqueries(cont’d)

Illegal queries are as following:

•You cannot use multiple rows return queries
for single row operators

77
 Subqueries(cont’d)

Empty result subqueries are as

following:
•‘no rows selected’ because
subquery returns nothing

78
 Multiple-Row
Subqueries(cont’d)
• Return more than one row
• Uses multiple row
comparison operators i.e.

79
 Multiple-Row
Subqueries(cont’d)
Use of IN operator in
multiple-row subqueries

80
 Multiple-Row
Subqueries(cont’d)
Use of ANY operator in multiple-row
subqueries:
•‘<ANY’ means less than maximum of
returned values
•‘>ANY’ means more than the minimum
of returned values
•‘=ANY’ is equivalent to ‘IN’ operator

81
 Multiple-Row
Subqueries(cont’d)
Use of ALL operator in multiple-row
subqueries:
•‘<ALL’ means less than maximum of
returned values
•‘>ALL’ means more than the minimum
of returned values

82
 Multiple-Row
Subqueries(cont’d)
NULL values in multiple-row subqueries:
•If one of the returned values by inner query is NULL value then
the entire query returns no
values
•‘NOT IN’ operator is equivalent to ‘<>ALL’
•This issue can be resolved by using an additional WHERE
clause in the inner query i.e WHERE mgr.manager_id IS NOT
NULL

83
 Multiple Choice Questions
When subquery is an operand in a comparison, subquery must appear on
-----------
a) Left hand side
b) Right hand side
c) Middle of the query
d) Non of the above
2. Find the name of cities with all entries whose temperature is in the range of
71 and 89
A. SELECT * FROM weather WHERE temperature NOT IN (71 to 89);
B. SELECT * FROM weather WHERE temperature NOT IN (71 and 89);
C. SELECT * FROM weather WHERE temperature NOT BETWEEN 71 to
89;
D. SELECT * FROM weather WHERE temperature BETWEEN 71 AND
89;
84
3. Find all the tuples having temperature greater than 'Paris’.
a) SELECT * FROM weather WHERE temperature > (SELECT
temperature FROM
weather WHERE city = 'Paris')
b) SELECT * FROM weather WHERE temperature > (SELECT * FROM
weather
WHERE city = 'Paris')
c) SELECT * FROM weather WHERE temperature > (SELECT city FROM
weather
WHERE city = 'Paris')
d) SELECT * FROM weather WHERE temperature > 'Paris' temperature
4. By default, column names refer to table name in Which clause of subquery?
a) SELECT
b) FROM
c) WHERE
d) DISTICT
5. A subquery in an SQL SELECT statement is enclosed in:
a). parenthesis -- (...).
b). brackets -- [...].
c). CAPITAL LETTERS.
d). braces -- {...}.

85
Database Management Systems
UNIT-2
Aggregate Operators & Null
Values

86
Aggregate Operators

Aggregate functions are also referred to as group

functions. They return a value based on the values in a
column.
Group functions can appear in select lists and HAVING
clauses. If you use the GROUP BY clause in a SELECT
statement, Oracle divides the rows of a queried table or
view into groups. Oracle applies the group functions in the
select list to each group of rows and returns a single result
row for each group.
If you omit the GROUP BY clause, Oracle applies group
functions in the select list to all the rows in the queried table
or view. You can also use group functions in a HAVING
clause in the statement to restrict the result rows returned
87
SUM
SYNTAX: SUM([DISTINCT|ALL] n)
PURPOSE: Returns sum of values of n.
EXAMPLE: SELECT SUM(sal) Total FROM emp;
Total
-----
29025

AVG :
SYNTAX: AVG([DISTINCT|ALL] n)
PURPOSE: Returns average value of n.
EXAMPLE: SELECT AVG(sal) "Average" FROM emp ;
Average
----------------
2073.21429

88
COUNT
SYNTAX: COUNT({* | [DISTINCT|ALL] expr})
PURPOSE: Returns the number of rows in the query.
If you specify expr, this function returns rows where expr is
not
If you specify the asterisk (*), this function returns all rows,
including duplicates and nulls.
EXAMPLES1: SELECT COUNT(*) "Total"FROM emp ;
Total
----------
14
EX2: SELECT COUNT(DISTINCT job) "Jobs"FROM emp
;
Jobs
----
5
89
MAX
SYNTAX: MAX([DISTINCT|ALL] expr)
PURPOSE: Returns maximum value of expr.
EXAMPLE: SELECT MAX(sal) "Maximum" FROM emp ;
Maximum
-------
5000
MIN
SYNTAX: MIN([DISTINCT|ALL] expr)
PURPOSE: Returns minimum value of expr.
EXAMPLE: SELECT MIN(hiredate) "Minimum Date" FROM
emp;
Minimum Date
------------
17-DEC-80
Note: The DISTINCT and ALL options have no effect on the
MAX and MIN
functions. 90
STDDEV
SYNTAX: STDDEV([DISTINCT|ALL] x)
PURPOSE: Returns standard deviation of x, a number.
Oracle calculates the standard deviation as the square root
of the variance defined for the VARIANCE group function.
EXAMPLE: SELECT STDDEV(sal) "Deviation"FROM emp ;
Deviation
----------
1182.50322
VARIANCE
SYNTAX: VARIANCE([DISTINCT|ALL]x)
PURPOSE: Returns variance of x, a number
EXAMPLE: SELECT VARIANCE(sal) "Variance" FROM
emp ;
Variance
-----------------
91
1389313.87
These aggregate functions can also be used in various
combinations:

SQL> SELECT COUNT(SAL), AVG(SAL), MIN(SAL), MAX(SAL),

STDDEV(SAL), VARIANCE(SAL), SUM(SAL) FROM EMP;

92
Null Values
NVL :- Converts a null to an actual value.
• Data types that can be used are date, character, and
number.
• Data types must match:
• NVL(commission,0)
• NVL(hiredate,'01-JAN-97')
• NVL(jobid,'No Job Yet')

93
 NULL values
NVL: NVL lets you replace null (returned as a blank) with a string in the
results of a query.

Syntax : NVL(expr1, expr2)

• If expr1 is null, then NVL returns expr2.

• If expr1 is not null, then NVL returns expr1.
• If expr1 is character data, then Oracle Database converts expr2 to the
datatype of expr1
• If expr1 is numeric, then Oracle determines which argument has the
highest numeric
precedence, implicitly converts the other argument to that datatype, and
returns that datatype
• If expr1 is NULL, it returns expr2 otherwise it returns expr1.

SQL> select ename, NVL(comm, 0) from emp;

SQL> select ename, NVL(to_char(comm), ’NOT APPLICABLE’) from
emp;
94
NVL2 : NVL2 lets you determine the value returned by
a query based on whether a specified expression is
null or not null. If expr1 is not null, then NVL2 returns
expr2. If expr1 is null, then NVL2 returns expr3.

Syntax: NVL2(expr1,expr2,expr3)

Note: If expr1 is not null,NVL2 returns expr2,if

expr1 is null ,NVL2 returns expr3.

Ex: SELECT
ename,sal,NVL2(comm.,sal+(sal*comm.),sal)
income FROM emp; 95
Multiple Choice Questions
1. Which of the following is not a built in aggregate function in
SQL?
a) avg
b) max
c) total
d) count
2. We apply the aggregate function to a group of sets of tuples using
the _______ clause.
a) group by
b) group
c) group set
d) group
3. The _____ aggregation operation adds up all the values of the
attribute
a) add
b) avg
c) max
d) sum

96
4. Which keyword is used to rename the resulting attribute after the
application of the aggregation function?
a) rename
b) as
c) replace
d) to
5. What is the outcome of following sql statement
Select sum(salary)/count(salary) from employee;
a) Standard deviation of salaries
b) Variance of salaries
c) Average of Salaries
d) Sum of Salaries

97
Database Management Systems
UNIT-2
Integrity Constraints over
relations

98
Integrity Constraints Over
Relations
TYPES OF CONSTRAINTS

Constraints are classified into two types

• Table Constraints
• Column Constraints
Table Constraint A constraint given at the table level is called as Table
Constraint. It may refer to more than one column of the table.
A typical example is PRIMARY KEY constraint that is used to define
composite primary key.
Column Constraint A constraint given at the column level is called as
Column constraint. It defines a rule for a single column. It cannot refer to
column other than the column, at which it is defined,
A typical example is PRIMARY KEY constraint when a single column is
the primary key of the table. 99
100
PRIMARY KEY : The purpose of Primary key is to ensure that
information in the column is unique and MUST be entered some data .

UNIQUE + NOT NULL = PRIMARY KEY

It is used to uniquely identify the rows in a table. There can be only one
primary key in a table. It may consist of more than one column, if so, it is
called as composite primary key. ( It maintain uniqueness in the data and null
values are not acceptable).
Features of the primary key:
Primary key will not allow duplicate values.
Primary key will not allow null values.
Only one primary key is allowed per table.

SYNTAX – Column level: [CONSTRAINT const-name] PRIMARY KEY

Ex: Create table EMP (EMPNO NUMBER(5) CONSTRAINT PRM_EMP

PRIMARY KEY, ENAME VARCHAR2(20));
101
UNIQUE key : The purpose of a unique key is to ensure that
information in the column is Unique.
Features of the unique key:
It does not allow duplicate values
It allows NULL values.

SYNTAX – Table level: [CONSTRAINT const-name] UNIQUE

(column1, column2, …)
Column level : [CONSTRAINT const-name] UNIQUE

Ex: Create table DEPT ( DEPTNO NUMBER(5), LOC VARCHAR2(10),

CONSTRAINT UN_DEPT UNIQUE(DEPTNO,LOC)); 102
NOT NULL Uniqueness not maintained and null values
are not acceptable.
• To satisfy a NOT NULL constraint ,every row in the
table must contain a value for the column.

Ex: Create table Griet(StudentNo Number(8),Constraint

Studentno_NN NOTNULL, Studentname Varchar2(20)
Constraint Studentname_NN NOTNULL,Branch
Varchar2(20) Constraint Branch_NN NOTNULL);
103
CHECK Defines the condition that should be
satisfied before insertion
and updating is done.

SYNTAX – [CONSTRAINT const-name] CHECK

(condition)
Constraint SAL_CHK CHECK (SALARY <=2500)

104
DESCRIBE USER_CONS_COLUMNS

SQL> SELECT CONSTRAINT_NAME FROM

USER_CONS_COLUMNS WHERE TABLE_NAME =
EMP;

105
REFERENTIAL INTEGRITY CONSTRAINTS
This constraint is useful for maintaining relation with
other table.

Various referential integrity constraints we can use in

Oracle are

•References
•On delete cascade

106
References is mainly useful for creating reference key or a
foreign key.
Reference is always given only to the key fields of other
tables.
A Table can have any references
Reference key accepts NULL and duplicate values.
Features of foreign key:
Records cannot be inserted into a detail table if
corresponding records in the master table do not exist.
Records of the master table cannot be deleted or updated if
corresponding records in the detail table actually exist.

107
On delete cascade is used along with references. It allows your
delete
Child table records, when we delete master table record.

FOREIGN KEY CONSTRAINT:

SYNTAX –
Table level: [CONSTRAINT const-name] FOREIGN KEY (col1,
col2, …) REFERENCES (TABLE (col1), TABLE2(col2), …)

Column level: [CONSTRAINT const-name] REFERENCES

TABLE (column)
Constraint FK_DEPT FOREIGN KEY (DEPTNO)
REFERENCES DEPT(DEPTNO)

108
ON DELECT CASCADE CONSTRAINT

Deletes rows from child table as and when row from

parent table is deleted.
Create table T1 (EMPNO number(4) primary key,
ENAME char (15));
Create table T2 (EMPNO number(4), SALARY
number(7,2), constraint ODC FOREIGN KEY (EMPNO)
REFERENCES T1(EMPNO) ON DELETE CASCADE);
109
Multiple Choice Questions
1. To include integrity constraint in an existing relation use :
a) Create table
b) Modify table
c) Alter table
d) Drop table

2. Which of the following is not an integrity constraint?

a) Not null
b) Positive
c) Unique
d) Check

110
3. Inorder to ensure that the value of budget is non-negative which of the
following should be used?
a) Check(budget>0)
b) Check(budget<0)
c) Alter(budget>0)
d) Alter(budget<0)
4. Foreign key is the one in which the ________ of one relation is
referenced in another relation.
a) Foreign key
b) Primary key
c) References
d) Check constraint
5.Which of the following is used to delete the entries in the referenced table
when the tuple is deleted in course table?
a) Delete
b) Delete cascade
c) Set null
d) All of the mentioned

111
Database Management Systems
JOINS

112
 JOINS

•SQL has ability to define relationships between

multiple tables, all within a single command. This
kind of operation is called a JOIN.
•Rows in a table can be joined with those in another
according to common values existing in
corresponding columns. Depending upon the join
condition the ‘JOINS’ may be classified as follows:

113
Types of joins:

• Equijoin or Inner or Simple join

• Non Equijoin
• Cartesian or Cross join
• Self join
• Outer join
--Left outer join
--Right outer join
--Full outer join

114
What is an Equijoin?
EMPLOY DEPARTME
EES NTS

… …

Foreign Primary
key key 115
EQUI JOIN (OR) INNER JOIN (OR) SIMPLE JOIN :-

The equi join is used to select data from two or more tables that keep common
columns within the table example dept and emp table both have deptno as a
common column.

Ex1: Select Emp.ename, Emp.Deptno, Dept.Loc From Emp,Dept Where

Emp.Deptno=Dept.Deptno;

Ex2: Display all emp details along with corresponding dept details.

1) Select Empno,Ename,Job,Sal,Emp.Deptno,Dname,Loc From

Emp,Dept
Where Emp.Deptno=Dept.Deptno;
(OR)
2)Select Emp.*, Dept.* From Emp,Dept Where
Emp.Deptno=Dept.Deptno;

/*using table alias*/

Select E.Empno,E.Ename,E.Sal,E.Deptno,D.Dname,D.Loc From Emp E , Dept
D
Where E.Deptno=D.Deptno;
116
Non-Equijoins JOB_GRA
EMPLOY DES
EES

Salary in the
EMPLOYEES
table must be
between
… lowest salary and
highest
salary in the
JOB_GRADES
table. 117
NON-EQUI JOINS : Joins that use predicates based on
non-equalities are called Non-Equi Joins. The non equi
join will be based on > and < operators there are no
special
Operators for this type of join.

Select e.ename, e.sal, s.grade from emp e, salgrade s

where e.sal between s.losal and s.hisal;

118
CARTESIAN JOINS (OR) CROSS JOIN

Every row of one table matches with every row of the

other table resulting in
Cartesian Join.
Ex: select * from emp,dept ; (without any condition)
Select ename, loc from dept, emp where job=’CLERK’;
(with condition)

119
Self Joins
EMPLOYEES EMPLOYEES
(WORKER) (MANAGER)

… …

MANAGER_ID in the WORKER table is

equal to EMPLOYEE_ID in the MANAGER
table.
120
SELF JOINS : (To join a table to itself using table aliases.)

The self join is used on those tables that keep a parent child
relation within the table itself.

display employee no , ename , mgr and managers name

(To use self join we must use alias names for tables.)

Sql> Select E.Empno,E.Ename,M.Empno,M.Ename From

Emp E , Emp M
Where M.Empno=E.Mgr;

Find all employees who can earn less than their managers.

Select e.ename, e.sal “emp-sal”, m.ename “mgr-name”, m.sal

“mgr-sal” from emp e, emp m where e.mgr=m.empno and
e.sal <m.sal;

121
Outer Joins
DEPARTME EMPLOY
NTS EES

…
There are no
employees in
department 190.
122
OUTER JOINS:
• The outer join is the same as equi join but it will
display some extra information that is available in
only one of the two tables that we are joining.
•Display all emp details along with corresponding
dept details and also display that dept's details in
which there are no employees.
• ( To get this extra information we have to use the
outer join operator i.e. (+) . This Operator must be
placed in the join condition , it must be placed on
that tables side that is deficient of the value.)

123
Outer Joins Syntax
• You use an outer join to also see
rows that do not meet the join
condition.
• The Outer join operator is the plus
sign (+).
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) =
table2.column;
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column =
table2.column(+); 124
• Select empno,ename,sal,dept.deptno,dname,loc from
emp,dept
Where emp.deptno(+)=dept.deptno;

• Select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc
from emp e , dept d Where e.deptno(+)=d.deptno;

• Ex: In order to list the locations of all departments and

the employees working at those locations and also
include the departments without employees.

Select loc, ename from emp, dept where emp.deptno(+) =

dept.deptno;
125
Multiple Choice Questions
1. Which of the following join is also called as an 'inner-join’?
a) Non-Equijoin
b) Self-Join
c) Equijoin
d) None of these
2. A condition is referred to as __________
a) Join in SQL
b) Join condition
c) Join in SQL & Condition
d) None of the mentioned

126
3. Which is a join condition contains an equality operator:
a) Equijoins
b) Cartesian
c) Both Equijoins and Cartesian
d) None of the mentioned
4. Which join refers to join records from the write table that have no
matching key in the left table are include in the result set:
a) Left outer join
b) Right outer join
c) Full outer join
d) Half outer join
5. Which product is returned in a join query have no join condition:
a) Equijoins
b) Cartesian
c) Both Equijoins and Cartesian
d) None of the mentioned

127
Database Management Systems
UNIT-2
Views & Cursors

128
Introduction to View
Def: A view is a logical / Virtual table that allows you to access data
from other tables and views. A view contains no data itself.

The tables upon which a view is based are called” base

tables.”

Views are used to Provide additional level of table security, by

restricting access to a predetermined set of rows/columns of a base
table.
SYNTAX:
CREATE VIEW View_name AS subquery
[WITH CHECK OPTION];

Ex: CREATE OR REPLACE VIEW NEWEMP AS SELECT *

FROM EMP;

129
 With Check Option
• The WITH CHECK OPTION clause is used to prevent
the insertion of rows in the view where the
condition in the WHERE clause in CREATE VIEW
statement is not satisfied.
• If we have used the WITH CHECK OPTION clause in
the CREATE VIEW statement, and if the UPDATE or
INSERT clause does not satisfy the conditions then
they will return an error.

130
Examples:

- CREATE VIEW dept20

AS SELECT ename, sal FROM EMP
WHERE DEPTNO = 20;

- CREATE VIEW clerk (id number, person, department,

position)
AS SELECT EMPNO, ENAME, DEPTNO, JOB
FROM EMP
WHERE JOB = ‘CLERK’ WITH CHECK OPTION;

131
Cursors

132
 Cursors
ORACLE uses special work areas to execute SQL statements
and store information called ‘private SQL areas’.
PL/SQL allows users to name the private work areas and
access the stored information, these named private work areas
are called cursors.

The PL/SQL cursor is a mechanism by which you can name

that work area and manipulate the information within it. In its
simplest form, you can think of a cursor as a pointer into a
table in the database.

Cursors are of two types in PL/SQL

IMPLICIT CURSOR

EXPLICIT CURSOR

133
Explicit Cursor
Explicit cursors are declared explicitly by the user. They
are for queries only, and allow multiple rows to be processed
from the query.

These are user-defined cursors that are defined in the

declarative part of a PL/SQL block by naming it and
specifying a query.
To control the cursor three commands are used:

OPEN -To initialize the cursor that identifies the

active set.

FETCH - To retrieve the rows individually.

CLOSE - To release the cursor.

134
Handling Explicit Cursor

Explicit cursor is a name used to refer to an area where you

can place multiple rows retrieved by SELECT

STEPS

The following are the required steps to process an explicit

cursor.

•Declare the cursor in declare section

•Open the cursor using OPEN
•Fetch rows from the cursor FETCH
•Close the cursor after the process is overusing CLOSE
135
Declare a Cursor

A cursor is declared in DECLARE section using

CURSOR statement.

Syntax

Cursor <cursorname> is
Select <column(s)> from <tablename> where
<Condition>;

136
Cursor Attributes

Cursor attributes allow to get information regarding

cursor. For example, you can get the number of rows
fetched so far from a cursor using ROWCOUNT

syntax to access cursor attributes

Cursor_name%Attribute

137
The following is the list of available
cursor attributes:

138
Program: Write a program to test the cursor

SET SERVEROUTPUT ON -- SQL*plus Environment command

DECLARE
Cursor emp_cur is
Select empno, ename, job, sal from EMP where empno >= 7521;
Emp_rec emp_cur%rowtype;
BEGIN
/* open the cursor */
Open emp_cur;
/* fetch a record from cursor */
FETCH emp_cur into emp_rec;
DBMS_OUTPUT.PUT_LINE(emp_rec.empno || emp_rec.ename||
emp_rec.sal);
CLOSE emp_cur; -- closing the cursor
END; [This program reads and prints only one
139
record from cursor]
Program2: To read each and every record from the cursor
DECLARE
Cursor emp_cur is
Select empno, ename, job, sal from EMP where empno >= 7521;
Emp_rec emp_cur%rowtype;
BEGIN
/* open the cursor */
Open emp_cur;
/* fetch all the records of the cursor one by one */
LOOP
FETCH emp_cur into emp_rec;
/*
Exit loop if reached end of cursor
NOTFOUND is the cursor attribute
*/
exit when emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (emp_rec.empno || emp_rec.ename||
emp_rec.sal);
END LOOP;
-- closing the cursor
CLOSE emp_cur;
END;
/
140
Ex 1:
declare
total number;
begin
update emp set sal = sal + sal * 0.1
where deptno = 30;

total := SQL%ROWCOUNT;
insert into temp values ( total, sysdate ) ;
dbms_output.put_line( total || ‘rows updated’ );
end;

141
Ex 2:
begin
update emp set comm = sal * 0.1
where deptno = 30
and sal > ( select avg(sal) from emp );

if SQL%FOUND then
commit;
dbms_output.put_line( ‘Transaction
commited’ );
else
rollback;
dbms_output.put_line( ‘No records found’
);
end if;
end;
142
Multiple Choice Questions
1. Which of the following statements is true about implicit
cursors?
A. Implicit cursors are used for SQL statements that are not
named.
B. Developers should use implicit cursors with great care.
C. Implicit cursors are used in cursor for loops to handle data
processing.
D. Implicit cursors are no longer a feature in Oracle.
2. Which of the following is not a feature of a cursor FOR loop?
A. Record type declaration.
B. Opening and parsing of SQL statements.
C. Fetches records from cursor.
143
3. Which three of the following are implicit cursor attributes?
A. %found
B. %too_many_rows
C. %notfound
D. %rowcount
E. %rowtype
4. If left out, which of the following would cause an infinite loop to
occur in a simple loop?
A. LOOP
B. END LOOP
C. IF-THEN
D. EXIT
5. What happens when rows are found using a FETCH statement
A. It causes the cursor to close
B. It causes the cursor to open
C. It loads the current row values into variables
D. It creates the variables to hold the current row values

144
Database Management Systems
UNIT-2
Active Databases

145
 1

ADB
ACTIVE
MS
DATABASES

146
 Conventional Databases 2

Passive database
•Traditionaldatabase management systems (DBMSs)
are passive in the sense that commands are executed
by the database (e.g., query, update, delete) as and
when requested by the user or application program.
•Ithas passive update principle. That is, client
controls DBMS updates.
•However, some situations cannot be effectively
modeled by this pattern.

147
Conventional 3
Databases
Passive
Transaction Model

148
 Problem with Traditional Database
4
Systems: Example
As an example, consider a railway database
where data are stored about trains, timetables,
seats, fares, and so on, which is accessed by
different terminals.
In some circumstances (e.g., public holidays,
cultural
events) it may be beneficial to add additional
coaches to specific trains if the number of
spare seats a month in advance is below a
threshold value.
How to administer this situation in the passive
database? 149
 Resolution of the Situation 5

Two options are possible:

Add the additional monitoring functionality to
all booking programs so that the preceding
situation is checked each time a seat is sold.
However, this approach leads to the
semantics of the monitoring task being
distributed, replicated, and hidden among
different application programs.

150
 Resolution of the Situation 6

The second approach relies on a polling

mechanism that periodically checks the
number of seats available.
The difficulty in this approach stems from
ascertaining the most appropriate polling
frequency.
If too high, there is a cost penalty.
If too low, the reaction may be too late
(e.g., the coach is added, but only after
several customers have been turned
151
away).
Some more examples of real world 7
problem not well suited for passive
update principle:
• Inventory control - reordering items when
quantity in stock falls below threshold.
• Travel waiting list - book ticket as soon as
right kind is available
• Stock market - Buy/sell stocks when price
below/above threshold

152
 Active 8
Databases
Active databases support the preceding application by
moving the
reactive behavior from the application (or polling mechanism)
into the DBMS.
Active databases are thus able to monitor and react to
specific circumstances of relevance to an application.
An active database system must provide a knowledge
model (i.e., a description mechanism) and an execution
model
(i.e., a runtime strategy) for supporting this reactive behavior.

153
 Active Databases 9

General Idea:
Active DBMS provides: Regular DBMS primitives +
definition of application-defined situations + triggering of
application-defined reactions
It means:
‣being able to react automatically to situations in the
database
‣allowing the specification and implementation of reactive
behavior
Active DBMSs
•Embed situation-action rules in database
•Support many functionalities:
154
E.g. Integrity control, derived data, change notification
 Active 1
Databases 0
Recognize predefined situations in
database
•Trigger predefined actions when situations occur
•Actions are usually database updates
Active Rules – rules that are automatically
triggered by events in the
database.

155
 Triggers 1
1
Triggers is a concept that is technique for
specifying certain types of active rules in the
database.
A data base that has a set of associated
triggers is called an active data base.
Trigger is like a procedure that is
automatically invoked by the DBMS in
response to specified changes to data base.
Trigger is like a ‘Daemon that monitors a data
base, and is executed when the data base is
modified in a way that matches the event
specification
156
Trigger
1
Parts 2

Triggers work in ECA model. It has three parts:

Event : A change to data base that activates
the trigger
Condition: A trigger restriction specifies a
Boolean (logical)
expression must be TRUE for the trigger to fire
Action: A procedure that is executed when the
trigger is activated.
Similar to stored procedures, a trigger action can
contain PL/SQL statements

157
 1
Types of Triggers 3

An SQL statement may change several rows.

Apply action once per SQL statement.
Apply action for each row changed by SQL statement.
Row Triggers
•A row trigger is fired each time the table is affected by the
triggering
statement.
•If a triggering statement affects no rows, a row trigger is
not executed at all.
Statement Triggers :
•A statement trigger is fired once on behalf of the triggering
statement, regardless of the number of rows in the table
that the triggering statement affects (even if no rows are 158
 Trigger Timings 1
4

Actions may apply before or after the

triggering event is
executed.
Before Trigger
•Execute the trigger action before the triggering
statement.
•Eliminate unnecessary processing of the
triggering statement.
After Trigger
• AFTER triggers are used when you want the
triggering statement
to complete before executing the trigger action
159
 Availability 1
5
Triggers included in SQL 1999
(SQL 3)
Not in earlier standards.
Included much earlier in most
products:
Oracle, Sybase, DB2
As a consequence syntax may differ from
the standard.

160
 Creating a Trigger: Syntax: 1
6

CREATE [OR REPLACE ] TRIGGER

trigger_name
{BEFORE | AFTER } {INSERT [OR] |
UPDATE [OR] | DELETE}
[OF col_name] ON table_name [FOR
EACH ROW]

DECLARE
Declaration-statements
BEGIN
Executable-statements END
trigger_name; 161
 E-C-A Model 1
7
Most active databases support a knowledge model
that uses rules with three components: an event, a
condition, and an action.
The event part of a rule describes a happening to
which the rule may be able to respond.
The condition part of the rule examines the context
in which the event
has taken place.
The action describes the task to be carried out by
the rule if the relevant event has taken place and
the condition has evaluated to true.
162
 Event 1
8
An event is something that happens at a
point in time.
Possible alternatives:
•structure operation (insert, update, access);
•behavior invocation (the message display is sent to an object
of type widget);
•transaction (abort, commit, begin-transaction);
•exception (an attempt to access some data without appropriate
authorization);
•clock (the first day of every month);
•External (the temperature reading goes above 30 degrees)

163
Conditi
1
on 9

The condition indicates whether rule action

should be executed.
In ECA-rules, the condition is generally
optional.
Once the triggering event has occurred , the
condition may be evaluated. If condition
evaluates to be true, the rule action will be
executed.
If no condition is specified, the action will be
executed once the event occurs.
164
Actio
2
n 0
The range of tasks that can be
performed if the rule condition is
evaluated to be true.
It is usually a sequence of SQL
statements.
•Perform some behavior invocation within the
But actions
external
database ormay:
call,an
•inform
the user or system administrator of
some situation,
•abort a transaction,
•takesome alternative course of action
using do-instead 165
 Example: Protecting
Referential Integrity 3
0
SQL>CREATE OR REPLACE
TRIGGER
cascade_updates
2AFTER UPDATE OF deptno
ON dept
5 EACH
3FOR UPDATE
ROW
4BEGINemp emp.deptno =
6 SET :new.deptno
78 WHEREemp.deptno =
END :old.deptno;
9 /
• Whenever the department number changes, all
employee records for this department will
automatically be changed as well, so that the 166
employees will continue to work for the same
 3
Controlling Triggers using SQL 1

Disable/Re-enable
database trigger
ALTER TRIGGER trigger_nameDISABLE | ENABLE

Disable or Re-enable all

triggers for table
ALTER TABLE table_name DISABLE | ENABLE
ALL TRIGGERS

Removing a trigger
from database
DROP TRIGGER trigger_name

167
 Multiple Choice Questions
1. A __________ is a special kind of a store procedure that executes in response
to certain action on the table like insertion, deletion or updation of data.
a) Procedures
b) Triggers
c) Functions
d) None of the mentioned
2. Triggers are supported in
a) Delete
b) Update
c) Views
d) All of the mentioned
3. What are the after triggers?
a) Triggers generated after a particular operation
b) These triggers run after an insert, update or delete on a
table
c) These triggers run after an insert, views, update or delete on a
table
d) All of the mentioned
168
4. Which of the following is NOT an Oracle-supported trigger?
a) BEFORE
b) DURING
c) AFTER
d) INSTEAD OF
5. Which prefixes are available to Oracle triggers?
a) : new only
b) : old only
c) Both :new and : old
d) Neither :new nor : old

169