UNIT – I

Relational Databases
 Contents
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.
 Introduction to Database Management
Database Management System (DBMS)
A Database Management System (DBMS) is a software system designed to manage a
collection of interrelated data and the programs that allow users to interact with this data
efficiently.
• Primary Goal:
The main objective of a DBMS is to enable users to store, retrieve, and manage data in a
convenient and efficient manner, ensuring data integrity and accessibility.
Database Applications:
 Banking: transactions
 Airlines: reservations, schedules
 Universities: registration, grades
 Sales: customers, products, purchases
 Online retailers: order tracking, customized recommendations
 Manufacturing: production, inventory, orders, supply chain
 Human resources: employee records, salaries, tax deductions
 Purpose of Database System
Earlier database systems are created in response to manage the commercial data. These data is
typically stored in files. To allow users to manipulate these files various programs are written for

1) Addition of new data

2) Updating the data
3) Deleting the data.

Disadvantages of Traditional File Processing System

1. Data Redundancy
2. Data Inconsistency
3. Difficulty in Accessing Data
4. Data Isolation
5. Integrity Problems
6. Atomicity Problems
7. Concurrent Access Anomalies
8. Security Problems
 Data Abstraction
Data Abstraction
Data abstraction refers to retrieving only the required information while hiding
the underlying details and complexities of the system.
Levels of Abstraction
 Physical level: describes how a record (e.g., customer) is stored.
 Logical level: describes data stored in database, and the relationships among the data.
type instructor = record
ID : string;
name : string;
dept_name : string;
salary : integer;
end;
 View level: application programs hide details of data types. Views can also hide
information (such as an employee’s salary) for security purposes.
 Views of Data
An architecture for a database system
 Instances and Schemas
Schema
 The overall design or structure of the database, specifying how data is organized,
stored, and how the relationships among the data are defined.
 Physical schema: database design at the physical level
 Logical schema: database design at the logical level
 For example - In a program we do variable declaration and assignment of values to the
variable. The variable declaration is called schema and the value assigned to the
variable is called instance. The schema for the student record can be
RollNo Name Marks

Instances
 When information is inserted or deleted from the database then the database gets
changed. The collection of information at particular moment is called instances.
 For example - following is an instance of student database RollN Nam Mark
o e s
10 AAA 43
20 BBB 67
 Data Languages
There are two types of languages supported by database systems. These are –
1. Data Definition Language (DDL)
• Data Definition Language (DDL) is a specialized language used to specify a database schema by a
set of definitions.
• It is a language which is used for creating and modifying the structures of tables, views, indexes
and so on.
Functions:
• CREATE: Builds new tables, views, etc.
• ALTER: Modifies existing structures (e.g., add/remove columns).
• DROP: Deletes tables, views, or other objects
2. Data Manipulation Language (DML)
• This language enables users to access or manipulate data as organized by appropriate data
model.
Types of Operations:
• Retrieval: Extracting information from the database.
• Insertion: Adding new data.
• Deletion: Removing data.
• Modification: Updating existing data.
 Data Models
Definition : It is a collection of conceptual tools for describing data, relationships among
data, semantics (meaning) of data and constraints.

 Relational model
 Entity-Relationship data model (mainly for database design)
 Object-based data models (Object-oriented and Object-relational)
 Semi-structured data model (XML)
 Other older models:
 Network model
 Hierarchical model
 Relational Model
Definition: The relational model consists of a collection of tables that store data and
represent relationships among the data.
Table (Relation):
• A table is also known as a relation in the relational model.
• It consists of one or more columns, and each column has a unique name.
Records and Attributes:
Each table contains records of a particular type, and each record type has a fixed number of
fields (attributes).
Example:
Consider the relationship between the Student and Result databases. For
instance, Student Ram lives in Chennai and his marks are 78.The relationship between
these two databases is maintained through a common attribute, such as SeatNo.

SeatNo Name City SeatNo Marks

101 Ram Chennai 101 78

102 Shyam Pune 102 95
 Entity Relationship Model

Definition: The Entity-Relationship (ER) model uses a collection of basic objects called
entities and relationships to represent data and their associations.
Entity:
An entity is a thing or object in the real world that is distinguishable from other
objects. It can be a person, place, object, or event.
Usage:
The ER model is widely used in database design to visually represent the structure of a
database.
Example:
Consider the Employee and Department entities, where the relationship works_for
connects the two entities. In this example, the Employee works for a Department.
 Object Based Data Model

 The object oriented languages like C++, Java, C# are becoming the dominant in
software development.
 This led to object based data model.
 The object based data model combines object oriented features with relational data
model.

Semi – structured Data Model

 The semi-structured data model permits the specification of data where individual data
items of same type may have different sets of attributes.
 The Extensible Markup Language (XML) is widely used to represent semi- structured
data model.
Database System Architecture
 Storage Manager
Storage manager is a program module that provides the interface between the low-level
data stored in the database and the application programs and queries submitted to the
system.
The storage manager is responsible to the following tasks:
 Interaction with the file manager
 Efficient storing, retrieving and updating of data
Issues:
 Storage access
 File organization
 Indexing and hashing

Query Processing
1. Parsing and translation
2. Optimization
3. Evaluation
 Transaction Management
 A transaction is a collection of operations that performs a single logical function in a
database application
 Transaction-management component ensures that the database remains in a consistent
(correct) state despite system failures (e.g., power failures and operating system crashes) and
transaction failures.
 Concurrency-control manager controls the interaction among the concurrent transactions,
to ensure the consistency of the database.

Database Architecture
The architecture of a database systems is greatly influenced by the underlying computer system
on which the database is running:
 Centralized
 Client-server
 Parallel (multi-processor)
 Distributed
 Introduction to relational databases
 Relation database is a collection of tables having unique names.
 For example – Consider the example of Student table in which the information about
the student is stored. RollNo Name Phone
001 AAA 1111111111
002 BBB 2222222222

003 CCC 3333333333

 The above table consists of three column headers RollNo, Name and Phone. Each row
of the table indicates the information of each student by means of his Roll Number,
Name and Phone number. Similarly consider another table named Course as follows –
CourseID CourseName Credits
101 Mechanical 4
102 Computer Science 6
103 Electrical 5
104 Civil 3

 Clearly, in above table the columns are CourseID, CourseName and Credits.
 The CourseID 101 is associated with the course named Mechanical and associated with
the course of mechanical there are 4 credit points. Thus the relation is represented by
the table in the relation model. Similarly we can establish the relationship among the
two tables by defining the third table.
RollNo CourseID
 For example – Consider the table Admission as 001 102
002 104
003 101

 From this third table we can easily find out that the course to which the RollNo 001 is
admitted is computer Science.

Relational Model
1.Table or Relation: A collection of data items arranged in rows and columns.
Example:
Roll No Name Marks Phone
001 AAA 88 1111111111
002 BBB 83 2222222222
003 CCC 98 3333333333
004 DDD 67 4444444444
2.Tuple or Record or Row: A single entry in the table representing a set of related data.
001 AAA 88 1111111111

3. Attribute or columns : Parts of a table that contain specific data types. Example:
RollNo, Name, Marks, Phone
4. Relation Schema: Describes the structure of a table, including the table name, attributes,
and their types.
5. Relation Instance: A specific set of rows in a table at a particular moment.
Example (rows with marks above 80):
RollNo Name Marks Phone
001 AAA 88 1111111111
002 BBB 83 2222222222
003 CCC 98 3333333333

6.Domain: The set of permitted values for an attribute. Example: The domain of Marks is
{88, 83, 98}.
7.Atomic: A domain is atomic if its elements are indivisible units. Example: Phone
attribute in the student table is non-atomic.
8.NULL Attribute: A special symbol indicating unknown or inapplicable values, not to be
confused with zero or blank.
Example: Emp# Job Name Salary Commission
E10 Sales 12500 32090
E11 Null 25000 8000
E12 Sales 44000 0
E13 Sales 44000 Null

9.Degree: Total number of columns in a table.

Example: The degree of the student table is 4.
Roll No Name Marks Phone
001 AAA 88 1111111111
002 BBB 83 2222222222
003 CCC 98 3333333333

10.Cardinality: Total number of rows (tuples) in a table.

Example: The cardinality of the student table is 3.
 Keys
1.Super Key (SK): A set of one or more attributes within a table that can uniquely identify
each record.

2.Candidate Key (CK): A minimal subset of superkeys that can uniquely identify each
record.
3.Primary Key (PK): A candidate key chosen by the database designer to uniquely identify
tuples in a relation.

4.Alternate Key: A candidate key that is not chosen as the primary key.
5.Foreign Key: A set of attributes in one table that refers to the primary key of another table.
Links two tables to maintain referential integrity.
Example:

From above example, we can see that two tables are linked. For instance we
could easily find out that the ‘Student CCC has opted for ComputerSci course’

Relationship Among Keys:

Candidate Key ⊆ Super Key
Primary Key = Candidate Key - Alternate Keys
 Relational Algebra
There are two formal query languages associated with relational model and those are
relational algebra and relational calculus.
Definition : Relational algebra is a procedural query language which is used to access
database tables to read data in different ways.
The queries present in the relational algebra are denoted using operators.
Every operator in relational algebra accepts relational instances (tables) as input and returns
relational instance as output.
For example :
Relational Operations
Various types of relational operations are as follows –
1.Selection (σ):
This operation is used to fetch the rows or tuples from the table(relation).
Syntax: σ(condition)(relation) sid sname age gender
1 Ram 21 Male
For example - Consider the relation student as follows 2 Shyam 18 Male
3 Seeta 16 Female
4 Geeta 23 Female
Query : Fetch students with age more than 18 .
Relational Algebra: σage >18(Student)
The output will be – sname
Ram
Geeta

2.Projection (π):
Retrieves specific columns (attributes) from a table and removes duplicates.
Syntax: π(attribute1, attribute2, ...)(relation)
Query: Display sname and age from the Student table.
sname age
Relational Algebra: π(sname, age)(Student) Ram 21
Shyam 18
Seeta 16
Geeta 23
3. Cartesian Product (×):
Combines data from two tables to produce all possible combinations of tuples.
Syntax: A × B
Example: Suppose there are two tables named Student and Reserve as follows
sid sname age sid isbn day
1 005 07-07-18
1 Ram 21

2 Shyam 18 2 005 03-03-18

Query: Combine the two tables.

Relational Algebra: Student × Reserve

sid sname age sid isbn day

Output:
1 Ram 21 1 005 07-07-18
1 Ram 21 2 005 03-03-18
2 Shyam 18 1 005 07-07-18
2 Shyam 18 2 005 03-03-18
4.Set Operations:
 Union (∪): Combines tuples from two relations, removing duplicates.
sname bname
Syntax: A ∪ B Ram DBMS

Example: Two tables - Student name and Book name Shyam Ram

name
Query: Combine names from both tables. Ram
Shyam
Relational Algebra: π(sname)(Student) ∪ π(bname)(Book) DBMS

 Intersection (∩): Retrieves tuples common to both relations.

name name
Syntax: A ∩ B Ram Ram
Shyam Seeta
Example: Find names common to the Student and Worker tables.

name
Query: Find names common in both tables.
Ram
Relational Algebra: π(name)(Student) ∩ π(name)(Worker)
 Set Difference (−): Retrieves tuples present in one relation but not in another.
Syntax: A − B
EmpName EmpName
Example: Two tables - Full-Time Employee and Pat-Time Employee Alice
Bob
Bob
Query: Find employees working full-time but not part-time.
Relational Algebra: π(EmpName)(Full_Time_Employee) − π(EmpName)(Part_Time_Employee)
EmpName
Alice
5.Join (⋈):
Definition: Combines rows from two tables based on a condition.
Types:
 Conditional Join: Uses a specific condition.
Example: Two tables- Student Table and Reserve Table sid sname age sid isbn day
1 Ram 21
1 005 07-07-18
Query: Find names of students with isbn = 005. 2 Shyam 18
2 005 03-03-17

Relational Algebra:
Student ⋈ (Student.sid = Reserve.sid) ∧ (Reserve.isbn = 005)
sid sname age isbn day

1 Ram 21 005 07-07-18

2 Shyam 18 005 03-03-18
 Equi Join :
An Equi Join is a type of join that combines tables based on a condition that involves
equality (=) between specified columns from each table.
sid sname age sid cname
Example: Two tables – Student table and Course table 1 Ram 21 1 Math
2 Shyam 18 2 Science
3 Seeta 22 4 English

Query: Join the tables where Student.sid = Course.sid. sid sname age cname

Relational Algebra: Student ⋈ (Student.sid = Course.sid) Course

1 Ram 21 Math
2 Shyam 18 Science
 Natural Join :
A Natural Join is a type of join that combines tables based on all columns with the same
name and datatype in both tables. It eliminates duplicate columns from the result.

Relations r, s:

Natural Join
r⋈ s
 SQL Fundamentals
SQL (Structured Query Language) is a query language used to store, retrieve, and
manage data in a Relational Database Management System (RDBMS).
Components of SQL
 Defines and modifies database schema.
CREATE: Creates database objects like tables, views, etc.
DROP: Deletes objects from the database.
ALTER: Modifies database structure.
TRUNCATE: Removes all records from a table.
 Data Manipulation Language (DML): Manipulates data within tables.
SELECT: Retrieves data from a table.
INSERT: Adds new data into a table.
UPDATE: Modifies existing data.
DELETE: Removes records.
 Data Control Language (DCL): Manages access and permissions.
GRANT: Provides access rights.
REVOKE: Withdraws permissions.
Basic Data Types in SQL
 char(n): Fixed-length character strings (e.g., names).
 varchar(n): Variable-length character strings.
 int: Integer values.
 numeric ( p, s): Fixed-point numbers with precision p and scale s.
 real/float(n): Floating-point numbers
SQL Query Structure
Basic Commands:
 SELECT: Retrieves specific attributes from tables.
 FROM: Specifies the table(s) involved.
 WHERE: Filters data based on conditions.
 DISTINCT: Eliminates duplicate results.
Syntax:
SELECT DISTINCT column_name
FROM table_name
WHERE condition;
Example:
SELECT DISTINCT name
FROM student
WHERE age > 18;

Key Features
 Integrity Constraints: Ensure data accuracy and reliability.
 Views: Simplify complex queries by creating virtual tables.
 Transaction Control: Manages the execution of transactions (COMMIT, ROLLBACK).
 Embedded SQL: Integrates SQL with programming languages like C, C++, and Java.
 SQL Joins: Queries on Multiple Relations
Joins are used to combine data from two or more tables based on a related column, allowing
queries across multiple relations.
Types of Joins
 1. Inner Join
Combines rows from two tables where there is a match in the common column.
Only matching rows are included in the result. Table1 Table2
sid cid sname cid cname
Syntax: 1 101 Ram
101 Pune
2 101 Shyam
SELECT columns 3 102 Seeta
102 Mumbai

FROM Table1 4 NULL Geeta 103 Chennai

INNER JOIN Table2

ON Table1.common_column = Table2.common_column;
Example:
SELECT Student.sid, Student.sname, City.cname sid
Output
cid sname cname
FROM Student 1 101 Ram Pune
2 101 Shyam Pune
INNER JOIN City 3 102 Seeta Mumbai

ON Student.cid = City.cid;
 2. Left Join (Left Outer Join)
Returns all rows from the left table, with matching rows from the right table. If no match is
found, NULL is returned for columns from the right table.
Syntax:
SELECT columns
FROM Table1
LEFT JOIN Table2
ON Table1.common_column = Table2.common_column;
Example:
SELECT Student.sid, Student.sname, City.cname
FROM Student Output
LEFT JOIN City sid cid sname cname
1 101 Ram Pune
ON Student.cid = City.cid; 2 101 Shyam Pune
3 102 Seeta Mumbai
4 NULL Geeta NULL
 3. Right Join (Right Outer Join)
Returns all rows from the right table, with matching rows from the left table. If no match is
found, NULL is returned for columns from the left table.
Syntax:
SELECT columns
FROM Table1
RIGHT JOIN Table2
ON Table1.common_column = Table2.common_column;
Example:
SELECT Student.sid, Student.sname, City.cname Output
FROM Student sid cid sname cname

RIGHT JOIN City 1 101 Ram Pune

2 101 Shyam Pune
ON Student.cid = City.cid; 3 102 Seeta Mumbai
NULL 103 NULL Chennai
 4. Full Join (Full Outer Join)
Combines the results of both Left Join and Right Join. Includes all rows from both tables, with
NULLs for unmatched rows on either side.
Syntax:
SELECT columns
FROM Table1
FULL JOIN Table2
ON Table1.common_column = Table2.common_column;

Example:
SELECT Student.sid, Student.sname, City.cname Output
sid cid sname cname
FROM Student
1 101 Ram Pune
FULL JOIN City 2 101 Shyam Pune
3 102 Seeta Mumbai
ON Student.cid = City.cid;
4 NULL Geeta NULL
NULL 103 NULL Chennai
 Additional Basic SQL Operations

 1. The Rename Operation

SQL allows renaming table columns or entire tables using the AS keyword for temporary
naming. Student Table Reserve Table
sid sname marks city sid isbn
Syntax:
1 AAA 60 Pune 1 1234567890
SELECT alias_name.column_name 2 BBB 70 Mumbai
2 9876543210
FROM table_name AS alias_name; 3 CCC 90 Pune
3 1122334455
4 DDD 55 Mumbai
Example:
Query: "Find the names of students and the ISBN of books they reserved."
SELECT S.sname, R.isbn
FROM Student AS S, Reserve AS R
WHERE S.sid = R.sid; Output
sname isbn

AAA 1234567890
BBB 9876543210
CCC 1122334455
 2.Attribute Specification in SELECT Clause
Use * to select all columns in a table.
Syntax: Output
sid sname marks city
SELECT * FROM table_name;
1 AAA 60 Pune
Example: 2 BBB 70 Mumbai
3 CCC 90 Pune
SELECT * FROM Student;
4 DDD 55 Mumbai

 3.Ordering the Display of Tuples

Use ORDER BY to sort records in ascending (default) or descending order.
Syntax:
SELECT columns
FROM table_name snameOutputmarks
CCC
ORDER BY column_name [ASC | DESC]; Output90
BBB 70
Example: AAA 60
DDD 55
SELECT sname, marks
FROM Student
ORDER BY marks DESC;
 4. WHERE Clause Predicates: Used to filter records based on conditions.
Operators:
• Comparison Operators: =, !=, <, >, <=, >=
• Logical Operators: AND, OR, NOT
• Special Operators: BETWEEN, LIKE, IN, IS NULL
(i) Using BETWEEN Operator
Simplifies filtering for values within a range.
Example: "Find students whose marks are between 80 and 90.“ Output
sname marks
SELECT sname, marks
CCC 90
FROM Student
WHERE marks BETWEEN 80 AND 90;
(ii) Using AND, OR, and NOT Operators
Syntax:
SELECT columns
FROM table_name
WHERE condition1 [AND | OR | NOT] condition2;
Example:
1. AND Operator:
Query: "Find the student with name 'AAA' and city 'Pune’.”
SELECT * Output
sid sname marks cname
FROM Student
1 AAA 60 Pune
WHERE sname = 'AAA' AND city = 'Pune’;

2. OR Operator:
Query: "Find the student with name 'AAA' OR city 'Pune’.” Output
sid sname marks cname
SELECT * 1 AAA 60 Pune
FROM Student 3 CCC 90 Pune

WHERE sname = 'AAA' OR city = 'Pune’;

3. NOT Operator:
Query: "Find students who do not live in 'Pune’.” Output
sid sname marks cname
SELECT * 2 BBB 70 Mumbai
4 DDD 55 Mumbai
FROM Student
WHERE city != 'Pune';
String Operations
 LIKE 'S%': Matches any string starting with "S" followed by zero or more characters.
 LIKE 'S%a': Matches any string starting with "S", followed by any number of characters,
and ending with "a".
 LIKE 'S____': Matches any string starting with "S" followed by exactly four characters
(underscore _ matches a single character).
1. Find all employees with EmpName starting with "S"
SQL Statement:
SELECT EmpID, EmpName, Department, Date_of_Join Employee Database
FROM Employee EmpID EmpName Department Date_of_Join
WHERE EmpName LIKE 'S%’; 1 Sunil Marketing 1-Jan
Output 2 Mohsin Manager 2-Jan
EmpID EmpName Department Date_of_join 3 Supriya Manager 3-Jan
1 Sunil Marketing 1-Jan
4 Sonia Accounts 4-Jan
2 Supriya Manager 3-Jan
3 Sunia Accounts 4-Jan 5 Suraj Sales 5-Jan
4 Suraj Sales 5-Jan 6. Archana Purchase 6-Jan
2. Find employees whose names begin with "S" and end with "a"
SQL Statement:
SELECT EmpName EmpName
Supriya
FROM Employee
Sonia
WHERE EmpName LIKE 'S%a’;

3. Find employees whose names begin with "S" and are followed by exactly four
characters
SQL Statement:
SELECT EmpName EmpName
Sunil
FROM Employee
Sonia
WHERE EmpName LIKE 'S____'; Suraj
Aggregate Functions
Aggregate functions in SQL perform a calculation on a set of values to return a
single scalar value. The five most commonly used aggregate functions are:
 AVG (Average): Calculates the average of a set of values.
 MIN (Minimum): Returns the minimum value in a set.
 MAX (Maximum): Returns the maximum value in a set.
 SUM (Total): Adds up all the values in a set.
 COUNT: Returns the number of values in a set.
1. Basic Aggregation
 DISTINCT: Used to modify the expression and consider only distinct (non-duplicate)
values.
 ALL: Includes all values, including duplicates.
Syntax of Aggregate Functions: Student Table
sid sname marks city
SELECT AGGREGATE_FUNCTION(column_name)
1 AAA 60 Pune
FROM table_name; 2 BBB 70 Mumbai
3 CCC 90 Pune
4 DDD 55 Mumbai
1. AVG (Average): To compute the average marks of students
Example:
AVG(marks)
SELECT AVG(marks)
67.5
FROM Student;

2. COUNT (Count): To count the total number of students

Example:
COUNT(*)
SELECT COUNT(*)
FROM Student; 4

3. MIN (Minimum): To find the minimum value from a table

Example:
MIN(marks)
SELECT MIN(marks)
55
FROM Student;
4. MAX (Maximum): To find the maximum value from a table
Example: MAX(marks
)
SELECT MAX(marks) 90

FROM Student;
5. SUM (Total): To calculate the total sum of marks:
Example: SUM(marks)

SELECT SUM(marks) 270

FROM Student;
2. Group By and Having Clause
(i) GROUP BY: The GROUP BY clause groups rows based on common values, and it is
often used with aggregate functions to perform calculations on each group.
Syntax:
SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;
Example Query: Find the total marks of students in each city
SUM(marks) city
SELECT SUM(marks), city
FROM Student 150 Pune
125 Mumbai
GROUP BY city;
(ii) HAVING: The HAVING clause is used to filter records after the GROUP BY operation,
applying conditions to aggregated results. It only works with grouped records.
Syntax:
SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;
Example Query: Find the total marks of students only in cities 'Pune' and 'Mumbai'
SELECT SUM(marks), city
SUM(marks) city
FROM Student
GROUP BY city 150 Pune
125 Mumbai
HAVING city IN ('Pune', 'Mumbai');
 Advanced SQL

 Accessing SQL From a Programming Language

 Dynamic SQL
 JDBC and ODBC
 Embedded SQL
 SQL Data Types and Schemas
 Functions and Procedural Constructs
 Triggers
 Advanced Aggregation Features
 OLAP
 Embedded SQL

 The SQL standard defines embeddings of SQL in a variety of programming languages such as
C, Java, and Cobol.
 A language to which SQL queries are embedded is referred to as a host language, and the SQL
structures permitted in the host language comprise embedded SQL.
 The basic form of these languages follows that of the System R embedding of SQL into PL/I.
 EXEC SQL statement is used to identify embedded SQL request to the preprocessor
 EXEC SQL <embedded SQL statement > END_EXEC
 Note: this varies by language (for example, the Java embedding uses # SQL { …. }; )
 From within a host language, find the ID and name of students who have completed more than
the number of credits stored in variable credit_amount.
 Specify the query in SQL and declare a cursor for it
EXEC SQL
declare c cursor for
select ID, name
from student
where tot_cred > :credit_amount
 The open statement causes the query to be evaluated
EXEC SQL open c END_EXEC
 The fetch statement causes the values of one tuple in the query result to be placed on
host language variables.
EXEC SQL fetch c into :si, :sn END_EXEC
Repeated calls to fetch get successive tuples in the query result
 A variable called SQLSTATE in the SQL communication area (SQLCA) gets set to
‘02000’ to indicate no more data is available
 The close statement causes the database system to delete the temporary relation that
holds the result of the query.
EXEC SQL close c END_EXEC
 Note: above details vary with language. For example, the Java embedding defines Java
iterators to step through result tuples.
Dynamic SQL
Example:
int main() {
 Dynamic SQL is a programming
technique which allows to build the EXEC SQL INCLUDE SQLCA;
SQL statements dynamically at EXEC SQL BEGIN DECLARE SECTION
runtime. int Reg_No;
 Dynamic SQL statements are not
char name[10][10];
embedded in the source program but float marks;
stored as strings of characters that are
char grade;
manipulated during a program's
EXEC SQL END DECLARE SECTION
runtime.
EXEC SQL WHENEVER SQLERROR STOP
 These SQL statements are either
EXEC SQL SELECT Reg_No,name,marks,grade
entered by a programmer or
FROM Student
automatically generated by the
program. WHERE marks>90
 The simplest way to execute a dynamic INTO :Reg_No,:name,:marks,:grade;
SQL statement is with an EXECUTE printf ("Registration number: %d\n", Reg_No);
IMMEDIATE statement. This printf ("Name: %s\n", name);
statement passes the SQL statement to printf ("Marks: %f\n", marks); printf ("Grade:%c\n", grade);
the DBMS for compilation and exit();
execution. EXEC SQL DISCONNECT
}