CS8492

Database Management System

Unit I
 Objectives
• To learn the fundamentals of data models and to represent a database
system using ER diagrams.

• To study SQL and relational database design.

• To understand the internal storage structures using different file and

indexing techniques which will help in physical DB design.

• To understand the fundamental concepts of transaction processing-

concurrency control techniques and recovery procedures.

• To have an introductory knowledge about the Storage and Query

processing Techniques
UNIT I - RELATIONAL DATABASES

Purpose of Database System – Views of data –

Data Models – Database System Architecture –
Introduction to relational databases – Relational
Model – Keys – Relational Algebra – SQL
fundamentals – Advanced SQL features –
Embedded SQL– Dynamic SQL
 Database Management Systems

Definition:

“A Collection of interrelated data and a set

of programs to access those data”

 Database Management Systems

Goal:

A way to store and retrieve database

information that is convenient and efficient.

 Some examples of DBMS
• MySQL
• Oracle
• SQL Server
• IBM DB2
• PostgreSQL
• Amazon Simple DB (cloud based)
• MongoDB
 Database Management Systems

Tasks:

 Store data or Visualize data

 Access data

 Querying data

 Update or manipulate data

 Database Management Systems

Applications

 Banking

 Universities

 Reservation Systems

 Telecommunication
 Database Management Systems

Applications

 Manufacturing

 Accounting

 Credit card transactions

 Database Management Systems

Disadvantages of File Processing System

 Data redundancy

 Data inconsistency

 Data isolation
 Database Management Systems

Disadvantages of File Processing System

 Data Integrity

 Atomicity

 Concurrency

 Security
 Database Management Systems

Characteristics

 Systematic Management of information

 Representing the data by multiple views

 It maintains data for some specific purpose

 Database Management Systems

Advantages

 remove the redundancy

 retrieve the desired data

 accessed efficiently

 Data integrity maintained

 Database Management Systems

Disadvantages:

 Complex design

 Hardware and software cost

 Damaged part

 Conversion Cost

 Training
 Views of Data

Abstraction is one of the main features of

database systems.

 Hiding irrelevant details from user and

providing abstract view of data to users, helps

in easy and efficient user-database interaction

 Views of Data

Data Abstraction

 To ease the user interaction with database, the

developers hide internal irrelevant details from

users.
Views of Data - Data Abstraction
 Views of Data - Data Abstraction

Physical level:
 how data is actually stored in database

Logical level:
 what data is stored in database and what relationships exist
among those data

View level:
 the user interaction with database system.
 Schema and Instances

Schema:

 Design of a database

 only a structural view(design) of a database.

 The variable declaration

 The schema for student table

Reg. No Name Marks
Types of Schema:
 Physical Schema:
 how the data stored in blocks of storage

 Logical schema:
 data can be described as certain types of data records gets
stored in data structures.

 View schema or Subschema:

 end user interaction with database systems
Instance:
 The data stored in database may gets
changed while inserting or deleting.
The collection of information at particular
moment is called instance.
 The schema for student table
Reg. No Name Marks
1 AAA 410
2 BBB 446
3 CCC 480
 Database Languages

 used to read, update and store data in a

database.
 Types:
 Data Definition Language (DDL)
 Data Manipulation Language (DML)
 Data Control language (DCL)
 Transaction Control Language(TCL)
Data Definition Language (DDL):
 Specifying the database schema by a set of
definitions

 operations:
 To create the database instance – CREATE
To alter the structure of database – ALTER
To drop database instances – DROP
To delete tables in a database instance –
TRUNCATE

To rename database instances – RENAME

To drop objects from database such as tables –

DROP

To Comment – Comment

Data Manipulation Language (DML)
accessing and manipulating data in a database

 operations:
To read records from table(s) – SELECT

To insert record(s) into the table(s) – INSERT

Update the data in table(s) – UPDATE

Delete all the records from the table – DELETE

 Types
• Procedural DML – Require a user to specify
what data are needed and how to get those
data.
• Declarative DML - Require a user to specify
what data are needed and without specifying
how to get those data.
Data Control language (DCL)
 Granting and revoking user access on a database

 operations:
To grant access to user – GRANT

To revoke access from user – REVOKE

Transaction Control Language(TCL)
The changes in the database that we made using DML
commands are either performed or rollbacked using
TCL

 operations:
To persist the changes made by DML commands in
database – COMMIT
To rollback the changes made to the database –
ROLLBACK
 Data Models

a logical structure of Database

 describes the design of database to reflect
entities, attributes, relationship among data,
constrains etc.
 Types:
 Relational, ER Model, Object based Model and
Semi-structured Model, hierarchical model
 Data Models

 Relational Model:
 the data and relationships are represented by
collection of inter-related tables.

 Table - relation

 Each table is a group of column and rows, where

column represents attribute of an entity and rows
represents records.
 Data Models

 Relational Model – Example:

Reg. No Name Marks

1 AAA 90
2 BBB 100

 Reg. No, Name, Marks – Attributes-(Columns)

 Values in rows - Records

 Data Models

 Relational Model – Advantages:

 Structural Independence

 Conceptual Simplicity

 Query Capability

 Easy design, maintenance and usage

 Data Models

 Relational Model – Disadvantages:

 requires powerful hardware and large data storage
devices

 slower processing time

Hierarchical model
 Data Models

 Entity Relationship Model:

 the notion of real-world entities and relationships
among them.

 based on −
Entities and their attributes.

Relationships among entities.

 Data Models

 Entity Relationship Model:

 Data Models

 Entity Relationship Model:

 Entity:
 An entity in an ER Model is a real-world entity
having properties called attributes.

 Relationship:
 The logical association among entities.
 mapped with entities
 Data Models

 Entity Relationship Model – Advantages:

 Simple

 Easy to understand

 Effective

 Integrated

 Easy conversion
 Data Models

 Entity Relationship Model – Disadvantages:

 Loss of information

 Limited relationship

 No industry standard
 Data Models

Object based Data Model:

 The object oriented languages like C++, Java, C#
are becoming dominant in software development.

 combines object oriented features with relational

model.
 Data Models

Object based Data Model:

 Data Models

Object based Data Model - Advantages:

 Enriched modeling

 Reusability

 Support for schema evolution

 Improved Performance
 Data Models

Object based Data Model - Disadvantages:

 Lack of universal data model

 Lack of experience
 Data Models

 Semi-structured data model

 permits specification of data where individual data
items of same type may have different sets of
attributes.

Ex: Extensible Markup Language

 Data base architecture
• Two tier architecture.
• Three tier architecture
Database System Architecture
 Database System Architecture

 Top Parts:
 Users – Application interfaces

 Application programs – Application programmers

 Sophisticated Users – Query tools

 Database Administrator – Administrator tools

 Database System Architecture

 Query Processor:
 helps the database system to simplify and facilitate
access to data.
 DDL interpreter – a translator which interprets the
DDL statements in data dictionaries.
 DML compiler – translates DML statements query
language into an evaluation plan.
 Database System Architecture

 Query evaluation engine: executed the low level

instructions generated by the DML Compiler.

 An execution plan is a blueprint for evaluating a

query. It is evaluated by query evaluation engine.
 Database System Architecture

 Storage Manager:
 The component of database system that provides
interface between the low level data stored in the
database and the application programs and queries
submitted to the system.

 Responsible for storing, retrieving and updating data

in the database.
 Database System Architecture

 Storage Manager Components:

 Authorization and integrity manager:
 Validates the users who want to access the data and tests for
integrity constraints.

 Transaction Manager:
 Ensures that the database remains in consistent despite
of system failures and concurrent transaction execution
proceeds without conflicting.
 Database System Architecture

File Manager:
 Manages allocation of space on disk storage and
representation of the information on disk.

 Buffer Management:
 Manages the fetching of data from disk storage
into main memory. It also decides what data to
cache in main memory.
 Data Independence

 To modify the schema at one level of the database

system without altering the schema at the next higher
level.
 Physical Independence:
 to change the physical data without impacting the schema
or logical data.
 For example, to replace hard-disks with SSD − it should
not have any impact on the logical data or schemas.
 Data Independence

 Logical Independence:
 it stores information about how data is managed
inside.

 Example: If we do some changes on table format,

it should not change the data residing on the disk.
 Relational DBMS
• Relation / tables • Foreign key
• Tuple / record / row • Stored procedure
• Field / column • Indices
• Domain • Relational operations
• Constraint • Normalization
• Data type
• Primary key
 Relational Databases

 Collection of tables having unique names.

RollNo Name Phone
001 AAA 111
002 BBB 222
003 CCC 333

CourseID CourseName Credits

101 Mech 4
102 CSE 3
103 IT 5
 Relational Databases

 Collection of tables having unique names.

RollNo CourseID
001 101
002 102
003 103
 Relational Databases

 Table or relation:
 a collection of data represented in rows and columns

 cannot have duplicate data or rows.

Roll No Name Marks Phone

001 AAA 88 111
002 BBB 83 222
003 CCC 98 333
004 DDD 67 444
 Relational Databases

 Tuple or record or row

 Each row of a table is known as record

 a set of related data

004 DDD 67 444

 Relational Databases

 Attribute or columns:
 Each record can be broken down into several small parts of
data.

 Ex: RollNo, Sname, Marks, Phone

 Relation Schema and Instance:

 Schema: structure of the relation

 Instance: a specific set of rows

 Relational Databases

 Domain:
 a set of permitted values for an attribute in table.

 Example: a set of all possible marks of the students.

 Domain of marks (88,83,98)

Atomic:
 elements of the domain are considered to be indivisible
units.
 Relational Databases

 NULL attribute:
 a special symbol, independent of data type, either unknown
or inapplicable.

 It does not mean zero or blank.

Emp# Job Name Salary
E10 Sales 12500
E12 Null 25000
Degree:
Total number of columns present in the relational
database
Cardinality:
Total number of rows present in the relational
database
Emp# Job Name Salary
E10 Sales 12500
E12 Null 25000

In the above table, degree is 3 and cardinality is 3.

 Keys

 used for identifying unique rows from table

 keys are used to establishes relationship

among tables.

Types:

Super key, candidate keys, primary keys,

foreign keys, composite keys.
 Why we need Keys?
• In a real world, in many applications huge data
has to store in tables.
• Tables extends to thousands of records stored
in them, unsorted, and unorganized
• To avoid duplication of data and to easily
identify the data's.
Super key:
 a set of attributes within a table that can uniquely
identify each record in a table.
student_id name phone age marks
Student: 1 A 9876723452 17 89

2 Aa 9991165674 19 95

3 B 7898756543 18 55

4 C 8987867898 19 68

5 D 9990080080 17 89

 Super Key: student_id, (student_id, name), phone

 Candidate Key:
A super key without redundancy. (should not allow duplication)

the minimal set of fields which can uniquely identify each record
in a table

 Example: student_id, phone

 A candidate key can be NULL. And its value should be unique.

There can be more than one candidate keys for a table.

A candidate key can be a combination of more than one

columns(attributes).
 Primary Key
 a key that can uniquely identify each record in a table.
Each table must have a primary key.
Primary key should not be NULL.
 Example: student_id

 Foreign key
 it is an attribute which establish relationship between
another table.
to point to the primary key of another table.
It helps in mantaining data integrity for tables in
relationship.
Keys – Foreign Key
Composite key
• Any key with more than one attribute is called
as composite key
• Ex:
(s_id ,phone), (name,phone), (s-id,name)
 Integrity Constraints

 Integrity constraints are a set of rules. It is used to

maintain the quality of information and checks
whether the table is valid or not.
 Types:
 Domain Constraints
 Entity Integrity Constraints
 Referential Integrity Constraints
 Key Constraints or entity
 Integrity Constraints

 Domain Constraints
 a valid set of values for an attribute.

 The data type of domain includes string, character, integer,

time, date, currency, etc. The value of the attribute must be
available in the corresponding domain.
 Integrity Constraints

 Domain Constraints
ID Name Semester Age
1 A I 17
2 B II 16
3 C III A

 A – not allowed, it is an integer attribute

 Integrity Constraints

 Entity integrity constraints

 Primary key value can't be null.
Emp_Id Emp_Name Salary
111 AAA 10000
222 BBB 20000
333 CCC 30000
NULL DDD 40000
 Integrity Constraints

 Referential Integrity Constraints

 A referential integrity constraint is specified between two
tables.

 if a foreign key in Table 1 refers to the Primary Key of

Table 2, then every value of the Foreign Key in Table 1
must be null or be available in Table 2.
 Integrity Constraints

 Referential Integrity Constraints

 Integrity Constraints

 Key Constraints
 Keys are the entity set that is used to identify an entity
within its entity set uniquely.
 Relational Algebra

 A procedural language which is used to access database

to read tables to read data in different ways.
 denoted using operators.
 each relational query describes a step by step procedure
for computing the desired answer, based on the order in
which operators are applied in the query.
 Relational Algebra

 Operations:
 Selection

 projection

 Cartesian product

 Set Operations

Join
 Relational Algebra

 Selection:
 to fetch rows or tuple from the table.

 syntax:

σpredicate(relation)

 predicate – logic using which the data from the relation is

selected.
 Relational Algebra

 Ex: Student - Table

Reg. No Sname Age
1 AAA 15
2 BBB 18
3 CCC 16

σage>15(Student)
Sname
BBB
CCC
 Relational Algebra

 Projection:
 to project only a certain set of attributes of a
relation.

 remove duplicate data from the columns.

 Syntax: ΠA1,A2,…(r)
 Relational Algebra

 Projection: Ex: Student - Table

Reg. No Sname Age
1 AAA 15
2 BBB 18
3 CCC 16

Πage(Student)
 Relational Algebra

 Cartesian product:
 to combine data from two different relations into
one and fetch data from the combined relation

Syntax: A x B
 Relational Algebra
 Cartesian product: Example
Syntax: A1 x B1

Table A1
A B A1 x B1
1 1 A B C D E
2 2 1 1 1 2 7
1 1 2 6 8
Table B1 1 1 5 7 9
C D E 2 2 1 2 7
1 2 7 2 2 2 6 8
2 6 8 2 2 5 7 9
5 7 9
 Relational Algebra

• Set Operations
Union
Intersection
Set difference
 Relational Algebra

 Union:
 to fetch data from two relations or temporary relation.

 the relations specified should have same number of

attributes and same attribute domain.

 duplicated tuples are automatically eliminated from

the result.

 Syntax: A ᴜ B
 Relational Algebra

 Union: Example
Syntax: A1 ᴜ B1
Table A1 A1 ᴜ B1

A B A B

1 1 1 1

1 2 1 2

2 1 2 1
2 3
Table B1
A B
1 2
2 3
Relational Algebra – Additional Operators

 Intersection:
 to fetch data from both tables which is common in
both the tables.

Syntax: A ∩ B
 Relational Algebra

 Intersection: Example
Syntax: A1 ∩ B1
Table A1 A1 ∩ B1

Name id Name

aaa 1 aaa

bbb 2 ccc

ccc 1
Table B1
Name course
aaa C
ccc C++
 Relational Algebra

 Set-Difference:
 the result of set difference is tuples, which are
present in one relation but are not in the second
relation.

Syntax: A - B
 Relational Algebra

 Set-Difference: Example
Syntax: A1 - B1
Table A1 A1 - B1
A B A B

1 1 1 1

1 2 2 1

2 1

Table B1
A B
1 2
2 3
 Relational Algebra

 Rename Operation:
 to rename the output relation for any query
relation which returns the result.

Syntax: ρ (RelationNew, RelationOld)

 Relational Algebra

 Rename: Example

ρ (B1,A1)

Table A1 Table B1
A B A B
1 1 1 1
2 2 2 2
Relational Algebra – Additional Operators

 Join:
 to fetch data from both tables which is common in
both the tables.

Operator: *

https://www.tutorialspoint.com/sql/sql-inner-
joins.htm#:~:text=They%20are%20also%20referred%
20to,which%20satisfy%20the%20join%2Dpredicate.
 Relational Algebra

 Natural Join: Example

Syntax: A1* B1
Table A1 A1 * B1
A B A B C

1 1 1 1 2

1 2 1 2 2

2 1 2 1 3

Table B1
A C
1 2
2 3
Relational Algebra – Additional Operators

 Division:
 to evaluate queries which contain the keyword
ALL.

 denoted by A / B.
Relational Algebra – Additional Operators

Table A1 Table B1 A1 / B1
A B C D C D
1 5 2 7 2 7 A B
1 5 3 7 3 7 1 5
1 6 3 7 2 6
2 6 2 7 3 6
2 6 3 7
3 6 2 7
3 6 3 7
3 5 3 7
 SQL Fundamentals

 Structured Query Language is a database

query language used for storing and managing
data in Relational DBMS.
 SQL Fundamentals

 Data types used in SQL:

 char(n) – fixed length character string, the storage size
of the char value is equal to the maximum size for this
column.

 varchar(n) - a variable-length data type, the storage

size of the varchar value is the actual length of the data
entered, not the maximum size for this column
 SQL Fundamentals

 Data types used in SQL:

 int: store integer values without precision.

 numeric: a fixed point number with user defined

precision.

 Ex: numeric(3,2) – 333.11

 SQL Fundamentals

 DDL Commands:
 to define the database schema.
 deals with descriptions of the database schema and is used to create and
modify the structure of the database objects in database.
 CREATE
 ALTER
 RENAME
 TRUNCATE
 DROP
 SQL Fundamentals

 DDL Commands – create:

 to create the database or its objects such as table, function, views
and so on.
Syntax: CREATE TABLE <TABLE_NAME>
( column_name1 datatype1,
column_name2 datatype2,
column_name3 datatype3,
column_name4 datatype4
);
 SQL Fundamentals

 DDL Commands – create:

 to create the database or its objects such as table, function, views
and so on.
Syntax: CREATE TABLE <TABLE_NAME>
( column_name1 datatype1,
column_name2 datatype2,
column_name3 datatype3,
column_name4 datatype4
);
 SQL Fundamentals

 DDL Commands – create:

 to create the database or its objects such as table, function,
views and so on.

Example: CREATE TABLE Student( student_id INT,

name VARCHAR(100), age INT);

 SQL Fundamentals

 DDL Commands – alter:

 used for altering the table structure, such as,
to add a column to existing table
to rename any existing column
to change datatype of any column or to modify its
size.
to drop a column from the table.
 SQL Fundamentals

 DDL Commands – alter:

 Syntax: ALTER TABLE table_name ADD(

column_name datatype);

 Example: ALTER TABLE student ADD(

address VARCHAR(200) );
 SQL Fundamentals

 DDL Commands – alter:

 Syntax: ALTER TABLE table_name modify (

column_name datatype);

 Example: ALTER TABLE student MODIFY (

address VARCHAR(300) );
 SQL Fundamentals

 DDL Commands – alter:

 Syntax: ALTER TABLE table_name

RENAME old_column_name TO
new_column_name;

 Example: ALTER TABLE student RENAME

address TO location;
 SQL Fundamentals

 DDL Commands – Truncate:

 removes all the records from a table.

 will not destroy the table's structure

 Syntax: TRUNCATE TABLE table_name

 Example: TRUNCATE TABLE student;

 SQL Fundamentals

 DDL Commands – DROP :

 completely removes a table from the database.

 will also destroy the table structure and the data

stored in it.

 Syntax: DROP TABLE table_name

 Example: DROP TABLE student;

 SQL Fundamentals

 DDL Commands – RENAME:

 used to set a new name for any existing table.

 Syntax: RENAME TABLE old_table_name to

new_table_name

 Example: RENAME TABLE student to

students_info;
 SQL Fundamentals

 Data Manipulation Language (DML)

 managing data in database

not auto-committed

 changes made by DML command are not

permanent to database, it can be rolled back.
 SQL Fundamentals

DML Commands – INSERT:

to insert data into a table

 Syntax: INSERT INTO table_name

VALUES(data1, data2, ...)

 INSERT INTO student VALUES(101, 'Adam',

15);
 SQL Fundamentals

DML Commands – UPDATE :

to update any record of data in a table

 Syntax: UPDATE table_name SET column_name

= new_value WHERE some_condition;

 UPDATE student SET age=18 WHERE

student_id=102;
 SQL Fundamentals

DML Commands – DELETE:

used to delete data from a table.

 Syntax: DELETE FROM table_name;

 DELETE FROM student;

DELETE FROM student WHERE s_id=103;

 SQL Fundamentals

SQL Command – WHERE:

to specify/apply any condition while retrieving,
updating or deleting data from a table.
 This clause is used mostly with SELECT, UPDATE
and DELETE query.
 Syntax: DELETE FROM table_name WHERE
[condition];
Example: DELETE FROM student WHERE s_id=103;
 SQL Fundamentals

SQL Command – WHERE:

Example:

 SELECT s_id, name, age, address FROM student

WHERE s_id = 101;
 SQL Fundamentals
string operations

SQL Command – LIKE:

used in the condition in SQL query with the
WHERE clause.

 LIKE clause compares data with an expression

using wildcard operators to match pattern given in
the condition.
 SQL Fundamentals

SQL Command – LIKE:

Wildcard operators;

 Percent sign %: represents zero, one or more than

one character.

Underscore sign _: represents only a single

character.
 SQL Fundamentals

SQL Command – LIKE:

 Example;
 SELECT * FROM Student WHERE s_name
LIKE 'A%';

 will return all records where s_name starts with

character 'A'.
Example;
 SELECT * FROM Student WHERE s_name
LIKE „s%a„;

 will return all records where s_name starts with

character „s„ and end with „a‟.
 SQL Fundamentals

SQL Command – LIKE:

 Example;
 SELECT * FROM Student WHERE s_name
LIKE '_d%';

 will return all records from Student table where

s_name contain 'd' as second character.
 SQL Fundamentals

SQL Command – ORDER BY:

used with SELECT statement for arranging
retrieved data in sorted order.

default sorts the retrieved data in ascending order.

 To sort the data in descending order DESC

keyword is used with Order by clause.
 SQL Fundamentals

SQL Command – ORDER BY:

Syntax: SELECT column-list|* FROM table-name
ORDER BY ASC | DESC;

 Example: SELECT * FROM Emp ORDER BY

salary;
 SQL Fundamentals

SQL Command – Group By:

to group the results of a SELECT query based on
one or more columns

 used with SQL functions to group the result from

one or more tables
 SQL Fundamentals

SQL Command – Group By:

Syntax:
 SELECT column_name, function(column_name)
FROM table_name WHERE condition GROUP
BY column_name
 SELECT name, age FROM Emp GROUP BY
salary
 SQL Fundamentals

SQL Command – Group By:

SELECT name, salary FROM Emp WHERE age >
25 GROUP BY salary
 SQL Fundamentals

SQL Command –HAVING:

to give more precise condition for a statement.

 used to mention condition in Group by based SQL

queries, just like WHERE clause is used with
SELECT query
 SQL Fundamentals

SQL Command –HAVING:

 Syntax:

 SELECT column_name, function(column_name)

FROM table_name WHERE column_name
condition GROUP BY column_name HAVING
function(column_name) condition
 SQL Fundamentals

SQL Command –HAVING:

 Example:

 SELECT * FROM sale GROUP BY customer

HAVING sum(previous_balance) > 3000
 SQL Fundamentals

SQL Command –DISTINCT :

to retrieve unique values from the table.

 Distinct removes all the duplicate records while

retrieving records from any table in the database.

 Synatx: SELECT DISTINCT column-name

FROM table-name;
 SQL Fundamentals

SQL Command –DISTINCT :

Syntax: SELECT DISTINCT column-name
FROM table-name;

 Example: SELECT DISTINCT salary FROM

Emp;
 SQL Fundamentals

SQL Command – AND:

 to set multiple conditions with the WHERE clause,
alongside, SELECT, UPDATE or DELETE SQL queries.

 Example:

 SELECT * FROM Emp WHERE salary < 10000 AND age

> 25
 SQL Fundamentals

SQL Command – OR:

 to combine multiple conditions with WHERE clause.
 Difference:
 When we use AND to combine two or more than two conditions,
records satisfying all the specified conditions will be there in the
result.
 But in case of OR operator, atleast one condition from the
conditions specified must be satisfied by any record to be in the
resultset.
 SQL Fundamentals

SQL Command – OR:

eid name age salary

401 Anu 22 5000

402 Shane 29 8000

403 Rohan 34 12000

404 Scott 44 10000

 SELECT * FROM Emp WHERE salary >

10000 OR age > 25
 SQL Fundamentals

SQL Command – Aggregate Functions:

 return a single value after performing calculations on a
group of values

 AVG() Function:
 returns average value after calculating it from values in a
numeric column.

 SELECT avg(salary) from Emp;

 SQL Fundamentals

SQL Command – Aggregate Functions:

 COUNT() Function:
 returns the number of rows present in the table either based
on some condition or without condition.

 SELECT COUNT(name) FROM Emp WHERE salary =

8000;

 SELECT COUNT(DISTINCT salary) FROM emp;

 SQL Fundamentals

SQL Command – Aggregate Functions:

 MAX() Function:
 returns maximum value from selected column of the table.
 SELECT MAX(salary) FROM emp;

 MIN() Function:
 returns minimum value from a selected column of the table.

 SELECT MIN(salary) FROM emp;

 SQL Fundamentals

SQL Command – Aggregate Functions:

 SUM() Function:
 returns total sum of a selected columns numeric values.

 SELECT SUM(salary) FROM emp;

 SQL Fundamentals

SQL Command – SET Operations:

 UNION Operation:
to combine the results of two or more SELECT statements

 eliminate duplicate rows from its resultset.

 number of columns and datatype must be same in both the

tables, on which UNION operation is being applied.
 SQL Fundamentals

SQL Command – SET Operations:

 UNION Operation – Example:

ID Name ID Name
1 Abi 2 Ashok
2 Ashok 3 Chandran

 SELECT * FROM First UNION SELECT *

FROM Second;
 SQL Fundamentals

SQL Command – SET Operations:

 INTERSECT Operation:
to combine two SELECT statements, but it only returns the
records which are common from both SELECT statements.

 the number of columns and datatype must be same.

 Example: SELECT * FROM First INTERSECT SELECT

* FROM Second;
 SQL Fundamentals

SQL Command – JOIN:

 to fetch data from two or more tables, which is joined to
appear as single set of data.

 used for combining column from two or more tables by

using values common to both tables.

 Types: Inner, outer, Left and Full

 SQL Fundamentals

SQL Command – JOIN – Inner:

 the result is based on matched data as per the equality
condition specified in the SQL query.

 Syntax;

 SELECT column-name-list FROM table-name1 INNER

JOIN table-name2 WHERE table-name1.column-name =
table-name2.column-name;
 SQL Fundamentals

SQL Command – JOIN – Inner:

 Example: Table1: Class, Table2:Class_Info
ID Name ID Address
1 AAA 1 XYZ
2 BBB 2 PQR
3 CCC 3 LMN
4 DDD
SELECT * from class INNER JOIN class_info where class.id =
class_info.id;
 SQL Fundamentals

SQL Command – JOIN – Outer:

based on both matched and unmatched data

 Types:

 Left Outer Join

Right Outer Join

Full Outer Join

 SQL Fundamentals

SQL Command – JOIN – Left Outer:

 returns a resultset table with the matched data from the two
tables and then the remaining rows of the left table and null
from the right table's columns.
 Syntax:
 SELECT column-name-list FROM table-name1 LEFT
OUTER JOIN table-name2 ON table-name1.column-name
= table-name2.column-name;
 SQL Fundamentals

SQL Command – JOIN – Left Outer:

 Example: Table1: Class, Table2:Class_Info

 SELECT * FROM class LEFT OUTER JOIN class_info ON

(class.id = class_info.id);
ID Name ID Address

1 AAA 1 XYZ

2 BBB 2 PQR

3 CCC 3 LMN

4 DDD 7 DEF

5 EEE 8 GHI
 SQL Fundamentals

SQL Command – JOIN – Left Outer:

 Example: Table1: Class, Table2:Class_Info

 SELECT * FROM class LEFT OUTER JOIN class_info ON

(class.id = class_info.id);
ID Name ID Address
 Result Set:
1 AAA 1 XYZ
2 BBB 2 PQR
3 CCC 3 LMN
4 DDD NULL NULL
5 EEE NULL NULL
 SQL Fundamentals

SQL Command – JOIN – RIGHT Outer:

 returns a resultset table with the matched data from the two
tables and then the remaining rows of the right table and
null from the left table's columns.
 Syntax:
 SELECT column-name-list FROM table-name1 RIGHT
OUTER JOIN table-name2 ON table-name1.column-name
= table-name2.column-name;
 SQL Fundamentals

SQL Command – JOIN – Right Outer:

 Example: Table1: Class, Table2:Class_Info

 SELECT * FROM class RIGHT OUTER JOIN class_info ON

(class.id = class_info.id);
ID Name ID Address

1 AAA 1 XYZ

2 BBB 2 PQR

3 CCC 3 LMN

4 DDD 7 DEF

5 EEE 8 GHI
 SQL Fundamentals

SQL Command – JOIN – Left Outer:

 Example: Table1: Class, Table2:Class_Info

 SELECT * FROM class RIGHT OUTER JOIN class_info ON

(class.id = class_info.id);
ID Name ID Address
 Result Set:
1 AAA 1 XYZ
2 BBB 2 PQR
3 CCC 3 LMN
NULL NULL 7 DEF
NULL NULL 8 GHI
 SQL Fundamentals

SQL Command – JOIN – FULL Outer:

 returns a resultset table with the matched data of two table
then remaining rows of both left table and then the right
table.
 Syntax:
 SELECT column-name-list FROM table-name1 FULL
OUTER JOIN table-name2 ON table-name1.column-name
= table-name2.column-name;
 SQL Fundamentals

SQL Command – JOIN – Full Outer:

 Example: Table1: Class, Table2:Class_Info

SELECT * FROM class FULL OUTER JOIN class_info ON

(class.id = class_info.id);
ID Name ID Address

1 AAA 1 XYZ

2 BBB 2 PQR

3 CCC 3 LMN

4 DDD 7 DEF

5 EEE 8 GHI
 SQL Fundamentals

SQL Command – JOIN – FULL Outer:

 Example: Table1: Class, Table2:Class_Info

 SELECT * FROM class FULL OUTER JOIN class_info ON

(class.id = class_info.id); ID Name ID Address
1 AAA 1 XYZ
 Result Set: 2 BBB 2 PQR
3 CCC 3 LMN
4 DDD NULL NULL
5 EEE NULL NULL
NULL NULL 7 DEF
NULL NULL 8 GHI
 SQL Fundamentals

Nested Queries:
 a query is written inside a query

Types:

 Independent Nested Queries

 Co-related Nested Queries

 SQL Fundamentals

Nested Queries - Independent Nested Queries:

query execution starts from innermost query to outermost
queries.
 The execution of inner query is independent of outer query,
but the result of inner query is used in execution of outer
query.
 Various operators like IN, NOT IN, ANY, ALL etc are used
in writing independent nested queries.
 SQL Fundamentals

Nested Queries - Independent Nested Queries:

ID Name Age Address Salary
1 A 25 P 20000
2 B 45 Q 65000
3 C 35 R 37000
4 D 30 S 30000
5 E 31 T 30000
6 F 27 U 20000
7 G 42 V 60000
 SQL Fundamentals

Nested Queries - Independent Nested Queries:

 Operator – IN – Example:

 SELECT * FROM CUSTOMERS WHERE ID IN (SELECT

ID FROM CUSTOMERS WHERE SALARY > 45000) ;
 SQL Fundamentals

Nested Queries - Independent Nested Queries:

 Operator – NOT IN:– Example:

 Select S_ID from STUDENT where S_ID NOT IN

(Select S_ID from STUDENT_COURSE where C_ID IN

(SELECT C_ID from COURSE where C_NAME=‟DSA‟ or

C_NAME=‟DBMS‟));
 SQL Fundamentals

Nested Queries - Co-related Nested Queries:

the output of inner query depends on the row which is being
currently executed in outer query.

 Select S_NAME from STUDENT S where EXISTS

(select * from STUDENT_COURSE SC where

S.S_ID=SC.S_ID and SC.C_ID=‟C1‟);
 Advanced SQL Features

 Embedded SQL :
 The programming module in which the SQL statements are
embedded.
 to embed SQL statements inside the programming language such
as C, C++, JAVA and so on.
 allows the application languages to communicate with DB and
get requested result.
 The high level languages which supports embedding SQLs
within it – Host Language.
 Advanced SQL Features

 Embedded SQL :
 The SQL statements and host language
statements make the source program which is fed to a SQL
precompiler for
processing the SQL statements.
 The host programming languages variables can be referenced
in the embedded SQL statements, which allows values
calculated by the programs
to be used by SQL statements.
 Advanced SQL Features

 Embedded SQL :

 There are some special program variables which are

used to assign null values to database columns. These
program variables support the retrieval of null values
from the database.
 Advanced SQL Features
 Embedded SQL Program Development:
 Advanced SQL Features

 Embedded SQL Program Development:

 the embedded SQL source code is fed to the SQL
precompiler.
 The precompiler scans the program and processes
the embedded SQL statements present in the code.
 After processing the source code, the precompiler
produces 2 files as its output.
 Advanced SQL Features

 Embedded SQL Program Development:

 The first file contains the source program without
embedded SQL statements,

The second file contains all the embedded SQL

statements used in the program
 Advanced SQL Features

 Embedded SQL Program Development:

 The first file produced by precompiler is fed to the
compiler for the host programming language (like
C compiler). The compiler processes the source
code and produces object code as
its output.
 Advanced SQL Features

 Embedded SQL Program Development:

 Now the linker takes the object modules produced
by the compiler and link them with various library
routines and produces an executable
program.
 Advanced SQL Features

 Embedded SQL Program Development:

 The database request modules, produced by the
precompiler (in steps) are submitted to a special BIND
program.
 The BIND program examines
the SQL statements, parse them, validates them,
optimizes them and finally
produces an application plan for each statement.
 Advanced SQL Features

 Embedded SQL Program Development:

 The result is a combined
application plan for the entire program, that represents
a DBMS-executable version of its embedded SQL
statements.
 The BIND program stores the plan in the
database, usually assigning it the name of the
application program that has created it.
 Advanced SQL Features

 An Embedded SQL Example in C:

main(){
exec sql include sqlca;
exec sql declare table teacher (tid char(6) not null, tname char(20),
age number(3), salary number(7))
printf("updating teacher salary who are B.Tech");
exec sql update teacher set salary=1.1*salary where tid=„01';
printf(update done");
exit(); }
 Advanced SQL Features

 Dynamic SQL:
 a programming technique which allows to build the
SQL statements dynamically at runtime.
 are not embedded in the source program but stored as
strings of characters that are manipulated during a
program‟s runtime.
 SQL statements are either entered by a programmer or
automatically generated by the program.
 Advanced SQL Features

 Dynamic SQL:
 facilitates automatic generation and manipulation
of program modules for efficient automated
repeating task preparation and performance.

 facilitates the development of powerful

applications with the ability to create database
objects for manipulation according to user input.
 Advanced SQL Features

 Dynamic SQL:
int main(){
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION
int Reg_No;
char name[10][10];
float marks;
char grade;
 Advanced SQL Features

EXEC SQL END DECLARE SECTION

EXEC SQL WHENEVER SQLERROR STOP

EXEC SQL SELECT Reg_No,name,marks,grade

FROM Student WHERE marks>90

INTO: Reg_No,:name,:marks,:grade;
 Advanced SQL Features

Printf(“Registration Number”);
Printf(“Name”);
Printf(“Marks”);
Printf(“Grade”);
Exit();
EXEC SQL DISCONNECT }