Unit III - Views

A view is a table whose rows are not explicitly stored, a view is a virtual
table based on the result-set of an SQL statement. A view can contain
all rows of a table or select rows from a table. A view can be created
from one or many tables which depends on the written SQL query to
create a view.
A view is generated to show the information that the end-user requests
the data according to specified needs rather than complete information
of the table.

Advantages of View over database tables

[Link] Views, we can join multiple tables into a single virtual table.
[Link] hide data complexity.
[Link] the database, views take the less space than tables for storing data
because the database contains only the view definition.
[Link] indicate the subset of that data, which is contained in the tables
of the database.

Creating Views
Database views are created using the CREATE VIEW statement.
Views can be created from a single table, multiple tables or another
view.
To create a view, a user must have the appropriate system privilege
according to the specific implementation.
-Syntax in Mysql
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:
CREATE VIEW Students_CSE AS
SELECT Roll_no,Name
FROM Students
WHERE Branch = 'CSE';

Updating a View
A view can be updated with the CREATE OR REPLACE VIEW
statement.
-Syntax in Mysql
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

The following SQL adds the "Mobile" column to the "Students_CSE"

view:

Example:
CREATE OR REPLACE VIEW Students_CSE AS
SELECT Roll_no,Name,Mobile
FROM Students
WHERE Branch = 'CSE';
CREATE VIEW defines a view on a set of tables or views or both.
REPLACE VIEW redefines an existing view or, if the specified view
does not exist,

Inserting a row in a view

We can insert a row in a View in a same way as we do in a table. We
can use the INSERT INTO statement of SQL to insert a row in a View.

-Syntax in Mysql
INSERT INTO view_name(column1, column2, ...)
VALUES(value1,value2,.....);

Example:
INSERT INTO Students_CSE(Roll_no,Name,Mobile)
VALUES(521,'ram',9988776655);

Deleting a row in a view

Deleting rows from a view is also as simple as deleting rows from a
table. We can use the DELETE statement of SQL to delete rows from
a view.
-Syntax in Mysql
DELETE FROM view_name
WHERE condition;

Example:
DELETE FROM Students_CSE
WHERE Name="ram";

Querying a View
We can query the view as follows

Syntax in Mysql
SELECT * FROM view_name

Example:
SELECT * FROM Students_CSE;

Dropping a View
In order to delete a view in a database, we can use the DROP VIEW
statement.
-Syntax in Mysql
DROP FROM view_name

Example:
DROP FROM Students_CSE;

Data Manipulation Language (DML)

DML is a subset of SQL used to manipulate and retrieve data stored in
database tables.
It allows inserting, updating, deleting, and retrieving records.
Types of DML Commands
INSERT - Add new records into a table
INSERT INTO Student VALUES (101, 'Ravi', 'CSE');
UPDATE - Modify existing records
UPDATE Student SET Dept='IT' WHERE SID=101;
DELETE - Remove records from a table
DELETE FROM Student WHERE SID=101;
SELECT - Retrieve data from one or more tables
SELECT * FROM Student;

SQL Operators

Operators are symbols or keywords that specify an operation to be

performed on data.

a. Arithmetic Operators

Used to perform mathematical calculations.

+,-,*,/,%
Example:
SELECT Name, Salary + 500 AS RevisedSalary FROM Employee;

b. Comparison Operators

Used to compare two values.

= , < , > , <= , >= , <> or !=
Example:
SELECT * FROM Employee WHERE Salary >= 50000;

c. Logical Operators
Combine multiple conditions in WHERE clause.
AND, OR, NOT
Example:
SELECT * FROM Employee WHERE Dept='HR' AND Salary>40000;

d. Special Operators

Operator Description Example

Checks if value lies within a Salary BETWEEN 30000 AND
BETWEEN 60000
range
IN Matches any value in a list Dept IN ('CSE','IT')
LIKE Pattern matching Name LIKE 'A%'
IS NULL Checks for NULL values WHERE Commission IS NULL

3. SELECT Query and Clauses

Syntax:

SELECT [DISTINCT] column_list

FROM table_name
[WHERE condition]
[GROUP BY column]
[HAVING condition]
[ORDER BY column [ASC|DESC]];

Explanation of Clauses:

1. FROM Clause:
Specifies the table(s) from which data is to be retrieved.
Example: FROM Student
2. WHERE Clause:
Filters records based on specified conditions.
Example: WHERE Dept='CSE'
3. GROUP BY Clause:
Groups rows that have the same values in specified columns.
Example:
4. SELECT Dept, COUNT(*) FROM Student GROUP BY Dept;
 5. HAVING Clause:
Applies a condition to groups formed by GROUP BY.
Example:
SELECT Dept, AVG(Salary)
FROM Employee
GROUP BY Dept
HAVING AVG(Salary) > 50000;

6. ORDER BY Clause:
Sorts the result set.
Example:
SELECT Name, Salary FROM Employee ORDER BY Salary DESC;

7. DISTINCT Keyword:
Eliminates duplicate rows from the result set.
Example: SELECT DISTINCT Dept FROM Employee;

Structured Query Language- II: Set operations

In SQL, set operations are used to combine the results of two or more SELECT queries.
They work in a similar way to set operations in mathematics — such as UNION,
INTERSECT, and MINUS (or EXCEPT).

Each SELECT statement involved in a set operation must have:

 The same number of columns.

 The same or compatible data types.
 The columns in the same order.

2. Types of Set Operations

a. UNION

 Combines the results of two queries and removes duplicate rows.

 Returns all unique records from both result sets.

Syntax:

SELECT column_name(s) FROM table1

UNION
SELECT column_name(s) FROM table2;

Example:

SELECT student_id FROM science_students

UNION
SELECT student_id FROM math_students;

🟢 Returns unique student IDs present in either Science or Math.

b. UNION ALL

 Combines results of two queries including duplicates.

 Faster than UNION since it does not check for duplicates.

Syntax:

SELECT column_name(s) FROM table1

UNION ALL
SELECT column_name(s) FROM table2;

Example:

SELECT student_id FROM science_students

UNION ALL
SELECT student_id FROM math_students;

🟢 Returns all student IDs (duplicates included) from both tables.

c. INTERSECT

 Returns only the common rows between two SELECT statements.

Syntax:

SELECT column_name(s) FROM table1

INTERSECT
SELECT column_name(s) FROM table2;

Example:

SELECT student_id FROM science_students

INTERSECT
SELECT student_id FROM math_students;

🔵 Returns student IDs that are in both Science and Math.

d. MINUS (or EXCEPT)

 Returns rows from the first query that are not present in the second.
 Note:
o Oracle uses MINUS.
o SQL Server and PostgreSQL use EXCEPT.

Syntax (Oracle):

SELECT column_name(s) FROM table1

MINUS
SELECT column_name(s) FROM table2;

Syntax (SQL Server/PostgreSQL):

SELECT column_name(s) FROM table1

EXCEPT
SELECT column_name(s) FROM table2;

Example:

SELECT student_id FROM science_students

MINUS
SELECT student_id FROM math_students;

🔴 Returns student IDs that are in Science but not in Math.

3. Rules for Set Operations

1. Each SELECT must return the same number of columns.

2. The data types of corresponding columns must be compatible.
3. The ORDER BY clause can appear only once, at the end of the last SELECT.
4. NULL values are treated as equal for the purpose of duplicate elimination in UNION
and INTERSECT.

Predicate and Joins

Predicate
Predicates are conditions that evaluate to TRUE, FALSE, or UNKNOWN and are used to
filter data, while joins are SQL statements that combine rows from two or more tables based
on a related column. The relationship between them is that a join predicate is a specific type
of predicate used in a JOIN clause's ON condition to define how tables should be combined.
For example, [Link] = [Link] is a join predicate.
Predicates

 Definition: An expression used to filter or specify conditions in a query.

  Examples:
o Comparison: age > 25
o Logical: country = 'USA' AND status = 'active'
o Range: date BETWEEN '2023-01-01' AND '2023-12-31'
o Pattern: name LIKE 'A%' Amey, Akasnkha
o %A% Amar, Arhan %A ISha, ABha
 Usage: Found in WHERE and HAVING clauses, as well as JOIN conditions.

Joins
 Definition: Used to combine records from two or more tables based on a related
column between them.
 Types:
o INNER JOIN: Returns rows when there is a match in both tables.
o OUTER JOIN: Includes all rows from one table and the matched rows from
the other, filling in NULL for non-matches.
 LEFT, RIGHT, and FULL OUTER JOIN are types of outer joins.
o NATURAL JOIN: Joins tables on columns that have the same name and data
type, without an explicit join condition.
o THETA JOIN: Uses any comparison operator (

=,<>,>,<, [Link] comma is less than is greater than comma is greater than
comma is less than comma etc.

=,<>,>,<, etc.

) to join tables.

o EQUI JOIN: A specific type of theta join that uses only the equality operator
(=).

Predicates in Joins

 Join Predicate: A predicate specifically used in the ON clause of a JOIN to define the
relationship between tables.
 Example: In SELECT * FROM Orders INNER JOIN Customers ON
[Link] = [Link], the [Link] =
[Link] part is the join predicate.
 Filtering Predicates: Predicates in the WHERE clause are for filtering results after the
join operation, while predicates in the ON clause define the join itself.
 Example of Filtering: SELECT * FROM Orders INNER JOIN Customers ON
[Link] = [Link] WHERE [Link] =
'USA' filters the final result set, while ON [Link] =
[Link] AND [Link] = 'USA' is part of the join
condition itself.
Set Membership
In a Database Management System (DBMS), set membership refers to the
concept of determining whether a particular element (or value) is part of a given
set of elements. In SQL, this concept is primarily implemented using the IN and
NOT IN operators within subqueries or with explicit lists of values.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Student
Prn_no name address
20233585001 Rushabh Mumbai
20233585002 Shreyas Pune
20233585003 Pruthvi Nashik

Select name from Student where name IN (Mumbai, Pune, Nashik);

Result:
Library
Prn_no name books
20233585001 Rushabh Book1
2023358500234 Shreyas Book2
20233585003565 Pruthvi Book3

Prn_no name address

20233585001 Rushabh Mumbai

SELECT column_name(s)
 FROM table_name
WHERE column_name IN (SELECT column_name FROM
another_table WHERE condition);

Select name from Student where name IN (Select * from );

Subqueries: Set membership often involves subqueries, where the inner
query returns a set of values, and the outer query uses IN or NOT IN to
compare a column's value against that set.
Multiple Attributes: You can also test for set membership with multiple
attributes by comparing a tuple of values against a set of tuples returned
by a subquery.
Alternative to Joins: While set membership can sometimes achieve
similar results to joins (especially EXISTS or NOT EXISTS clauses),
they offer a different way of expressing the query logic, particularly
when dealing with the presence or absence of values in a set.

Student
PRN Dept name
121 IT
122
123

Teacher
Dept name
IT
Select * from student natural join select * from teacher

Tuple Variable
A tuple variable is a variable whose values are tuples (rows) that
conform to a particular relation schema. In tuple relational calculus
(TRC) we write queries that describe properties of tuple variables; the
result of a TRC query is the set of tuples that can be assigned to the
(free) tuple variables so that the describing formula evaluates true.
a tuple variable is a symbolic representation used to refer to an entire
row (or tuple) within a relation (or table).
Tuple: A tuple is a single row in a relational database table. It
represents a single record or instance of an entity, containing a set of
attribute values corresponding to the columns of the table.
Tuple Variable: In TRC, a tuple variable acts as a placeholder that can
range over the tuples of a specific relation. It allows you to express
conditions and retrieve tuples based on those conditions.
Purpose: Tuple variables are fundamental to formulating queries in
TRC. They enable you to express queries by defining a predicate (a
logical condition) that the tuples must satisfy to be included in the
result set.
Syntax: The general form of a TRC query using a tuple variable is:
{t | P(t)}
where:
t is the tuple variable.
P(t) is a predicate (a logical condition) that applies to the tuple
represented by t.
Example:
Consider a table Students with columns StudentID, Name, Age, and
Major.
A TRC query to find all students majoring in "Computer Science"
would look like this:
{t | Students(t) AND [Link] = 'Computer Science'}
t is the tuple variable.
Students(t) indicates that t is a tuple belonging to the Students
relation.
[Link] = 'Computer Science' is the predicate, specifying that the
Major attribute of the tuple t must be 'Computer Science'.

Set Comparison
In the context of Tuple Relational Calculus (TRC), set comparison
refers to expressions where a tuple is compared against a set of
values obtained from another query. TRC allows logical formulas
where conditions involve comparisons between attribute values and
sets produced by subqueries.
These comparisons typically use operators such as:
 ∈ (in)
 ∉ (not in)
 ⊆ (subset)
 = (set equality)
 ≠
 <, >, ≤, ≥ (applied with all or some quantifiers)

1. Why Set Comparison Is Needed

In many database queries, we want to check conditions such as:
 “Find all sailors whose rating is greater than all ratings of
sailors in boat 101.”
 “Find employees whose salary is not in the set of salaries of
managers.”
 “Find suppliers who supply every part in the Parts table.”
Such conditions require comparing a value or tuple against a set of
values produced by a query, not just against a single constant.
TRC supports this naturally using logical quantifiers.

2. Basic Form of Set Comparison

In TRC, a query is typically a set:
{t∣formula involving t}\{ t \mid \text{formula involving } t
\}{t∣formula involving t}
Within the formula, you can include comparisons like:
t.A op (subquery returning a set of values)
Since subqueries return sets, TRC uses quantifiers to interpret
comparison:
 t.A > ALL { values from subquery }
 t.A < SOME { values from subquery }
 t.A ∈ { values from subquery }
Ramakrishnan reduces these to logical formulas involving universal
(∀) and existential (∃) quantifiers.

3. Examples Explained (Ramakrishnan-style)

A. Using Existential Comparison (∃)
“Find sailors whose rating is greater than some sailor who is older
than 30.”
TRC:
{ s∣∃s1∈Sailors([Link]>30∧[Link]>[Link]) }\{\, s \mid \exists s1
\in Sailors ([Link] > 30 \wedge [Link] > [Link])
\,\}{s∣∃s1∈Sailors([Link]>30∧[Link]>[Link])}
This is an example of SOME / ANY comparison, but expressed via
∃.

B. Universal Comparison (∀)

“Find sailors whose rating is greater than all sailors older than
30.”
TRC expresses ALL by negating an existential failure:
{ s∣∀s1∈Sailors([Link]>30→[Link]>[Link]) }\{\, s \mid \forall s1
\in Sailors ([Link] > 30 \rightarrow [Link] > [Link])
\,\}{s∣∀s1∈Sailors([Link]>30→[Link]>[Link])}
This represents set comparison with ALL.

C. IN / NOT IN Using Existential & Negation

IN
To check
A ∈ (set returned by subquery):
Example:
Find sailors who reserved a red boat.
{ s∣∃b∈Boats([Link]=′red′∧∃r∈Reserves([Link]=[Link]∧[Link]=[Link])) }\{
\, s \mid \exists b \in Boats ([Link] = 'red' \wedge \exists r \in
Reserves ([Link] = [Link] \wedge [Link] = [Link]))
\,\}{s∣∃b∈Boats([Link]=′red′∧∃r∈Reserves([Link]=[Link]∧[Link]=[Link]))
}
NOT IN
To express that a value is not in a set:
t.A∉{x∣ϕ(x)}t.A \notin \{ x \mid \phi(x) \}t.A∈/{x∣ϕ(x)}
Ramakrishnan translates this as:
¬∃x(ϕ(x)∧t.A=x)\neg \exists x(\phi(x) \wedge t.A =
x)¬∃x(ϕ(x)∧t.A=x)

4. Set Comparison Using Subset and Equality

These comparisons arise in “division-like” queries, such as:
“Find suppliers who supply every part.”
If
 P={parts}P = \{ parts \}P={parts}
 S(p)={partssuppliedbyasuppliers}S(p) = \{ parts supplied by a
supplier s \}S(p)={partssuppliedbyasuppliers}
We want:
P⊆S(p)P \subseteq S(p)P⊆S(p)
In TRC:
{ s∣∀p(p∈Parts→∃sp([Link]=[Link]∧[Link]=[Link])) }\{\, s \mid \forall p
(p \in Parts \rightarrow \exists sp ([Link] = [Link] \wedge [Link] =
[Link])) \,\}{s∣∀p(p∈Parts→∃sp([Link]=[Link]∧[Link]=[Link]))}
This is a subset comparison between sets.

5. Common Operators in Set Comparison

Expressed in TRC
Operator Meaning
using…
∈ value is in set ∃
 Expressed in TRC
Operator Meaning
using…
∉ value is not in set ¬∃
> ALL greater than every value ∀
< SOME less than at least one ∃
= (set sets contain same
mutual ⊆
equality) elements
⊆ set is subset of another ∀ implication
These operators let TRC express very powerful queries.

Ordering of Tuples
In the relational model, as presented by Ramakrishnan:
A relation is a set of tuples.
And because it is a set:
 Tuples have no inherent order.
 The relational model does not assume a first tuple, last tuple, or
positional ordering.
 Conceptually, reordering tuples does not change a relation.
This is one of the fundamental properties of the relational model.

1. Ordering in the Relational Model

Ramakrishnan emphasizes that:
 Relations are sets, so they are unordered.
 A relational operator (like selection or projection) produces a
relation — still unordered.
  Even if tuples appear in a particular order during query
processing, this order is not meaningful from the model’s
standpoint.
Therefore:
“Ordering of tuples is not part of the logical data model.”

2. Why Relational Model Avoids Ordering

A. Mathematical simplicity
Using sets avoids complications with positional references.
B. Logical independence
The user should not depend on how tuples are stored or retrieved
physically.
C. Optimization freedom
The DBMS can choose any execution strategy without worrying
about preserving order.

3. Physical Ordering vs. Logical Ordering

Though the logical model ignores ordering:
 Physical storage (indexes, heap files, B+ trees) does impose
order internally.
 However, this order is not guaranteed unless explicitly
requested in a query.
“Physical storage order is not visible at the relational level.”

4. Ordering in Relational Algebra

Standard relational algebra does not have an order-by operator.
Why?
  RA is a logical language, not intended to describe presentation
of results.
 RA describes what you want, not how or in what order.

5. Ordering in SQL (Practical Level)

While the relational model rejects ordering, SQL does allow it.
The operator is:
ORDER BY
Example:
SELECT name, age
FROM Students
ORDER BY age DESC;
Ramakrishnan explains:
 ORDER BY is a non-relational construct.
 It is used only for presentation, not for defining relational
semantics.
 Without ORDER BY, SQL may return tuples in any order.
Important note:
If ORDER BY is not used, SQL returns tuples in an unspecified
order, even if physically ordered.

6. When Does Ordering Matter?

In relational theory:
Ordering does not matter for the meaning of a relation.
In SQL programs and applications:
Ordering matters when:
  Displaying sorted output
 Ranking values
 Fetching top-K results (e.g., LIMIT)
 Analytical queries (RANK, ROW_NUMBER)
But these do not change the underlying relation—they change only
the query result presentation.

7. Key Exam-Ready Points

Define Ordering of Tuples:
“Ordering of tuples refers to any arrangement of tuples in a particular
sequence for presentation or processing. In the relational model,
relations are sets and therefore unordered; ordering must be explicitly
requested using ORDER BY in SQL.”
Relational model:
 Unordered
 Set-based
 No inherent tuple sequence
SQL:
 ORDER BY enforces order
 Without ORDER BY, ordering is undefined

Aggregate Functions
Aggregate functions are operations that take a set of values and
return a single summarized value. They are used in SQL and
relational query languages to perform computations over groups of
tuples.
1. What Are Aggregate Functions?
Aggregate functions:
 Take a collection of tuples as input.
 Perform a computation (sum, count, find average, max, etc.).
 Return one value as output.
Example:
SELECT AVG(salary) FROM Employee;
This query returns one number: the average salary of all employees.

2. Common Aggregate Functions (SQL Standard)

Ramakrishnan lists the commonly supported aggregate operators:
Aggregate Meaning
COUNT() Counts the number of tuples or non-null values
SUM() Computes sum of numeric values
AVG() Computes average of numeric values
MAX() Returns the maximum value
MIN() Returns the minimum value
Additional DBMS aggregates (not strictly in relational algebra)
include:
 STDDEV(), VARIANCE() (some SQL systems)
 COUNT(*) (counts rows, including duplicates)

3. Aggregation with GROUP BY

Aggregate functions become more powerful when combined with
GROUP BY.
  GROUP BY partitions a table into smaller groups based on
attribute(s).
 Each group is processed separately.
 Aggregate functions are applied per group, producing one
result per group.
Example
Find the average salary per department:
SELECT dept_id, AVG(salary)
FROM Employee
GROUP BY dept_id;
Each department becomes its own group.

4. HAVING Clause (Condition on Groups)

Ramakrishnan explains:
 WHERE filters individual rows.
 HAVING filters groups after aggregation.
Example:
Find departments with average salary > 50,000:
SELECT dept_id, AVG(salary)
FROM Employee
GROUP BY dept_id
HAVING AVG(salary) > 50000;
HAVING is evaluated after grouping and aggregation.

5. Aggregation in Relational Algebra (Extended Relational

Model)
Traditional relational algebra did not support aggregates.
But modern extensions add the γ (gamma) operator.
General form:
γgroup_list;aggregate_list(Relation)\gamma_{group\_list;
aggregate\_list}(Relation)γgroup_list;aggregate_list(Relation)
Example: average salary per department:
γdept_id;AVG(salary)(Employee)\gamma_{dept\_id;
AVG(salary)}(Employee)γdept_id;AVG(salary)(Employee)
This operator:
 Groups tuples
 Applies aggregate(s)
 Produces a relation containing one tuple per group

6. Multiset (Bag) Semantics

Ramakrishnan emphasizes:
 SQL uses bag semantics, not pure sets.
 Aggregate functions consider all occurrences of a value.
Example:
If salaries are (10, 10, 20), then:
 SUM = 40
 AVG = 13.33
 COUNT = 3
Duplicates matter.

7. NULL and Aggregate Functions

SQL handles NULLs carefully:
Function Treatment of NULL
COUNT(*) Includes NULLs
COUNT(attribute) Excludes NULLs
SUM(), AVG(), MIN(), MAX() Ignore NULL values
If all values are NULL, aggregates (except COUNT) return NULL.

8. Aggregate Functions Without GROUP BY

If no groups are formed:
 The whole table is treated as one group.
 Result contains one row.
Example:
SELECT SUM(price) FROM Orders;

9. Nested and Multiple Aggregations

SQL allows:
 Multiple aggregates in a query
 Nested aggregates (in some DBMSs)
Example:
SELECT MAX(AVG(salary))
FROM Employee
GROUP BY dept_id;
This finds the highest departmental average salary.
Database modification using SQL Insert, Update and Delete
queries
SQL allows modification of database contents using three core DML (Data Manipulation
Language) commands:

 INSERT – add new tuples to a relation

 UPDATE – modify existing tuples
 DELETE – remove tuples

These operations must follow schema constraints (keys, foreign keys, domain constraints).

1. INSERT Statement
Purpose
To add new records (tuples) into a table (relation).

1.1 Syntax (General Form)

INSERT INTO table_name (column_list)
VALUES (value_list);

Student
Prn_no name address
20233585001 Rushabh Mumbai
20233585002 Shreyas Pune
20233585003 Pruthvi Nashik

Insert into Student (Prn_no, name,

address) values (20233585006, Om, Mumbai);

Insert into Student (Prn_no, name,

address) values (20233585010, Sara, Pune);
OR inserting all columns:

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

1.2 Inserting Multiple Tuples

INSERT INTO Student (ID, Name, Dept)
VALUES
(101, 'Amit', 'CSE'),
(102, 'Priya', 'IT');

1.3 Insert Using Subquery

Used to copy data from one table to another.

INSERT INTO Graduates (ID, Name)

SELECT ID, Name
FROM Student
WHERE Year = 2025;

 Here, the result of the SELECT query is treated as a set of tuples to be inserted.

1.4 Imp Points

 Columns not included in INSERT take default value or NULL (if allowed).
 Integrity constraints are checked:
o Primary key cannot be NULL or duplicated
o Foreign keys must refer to valid tuples
o Domain constraints (data types) must be satisfied

2. UPDATE Statement
Purpose
To change existing values in one or more tuples.

2.1 Syntax
UPDATE table_name
SET column1 = value1,
column2 = value2
WHERE condition;

Student
Prn_no name address Marks
20233585001 Rushabh Mumbai 56
20233585002 Shreyas Pune 78
20233585003 Pruthvi Nashik 45

Update Student set name=”Sara” where Prn_no=20233585003;

Prn_no name address Marks

20233585001 Rushabh Mumbai 56
20233585002 Shreyas Pune 78
20233585003 Sara Nashik 45

2.2 Update Without WHERE

Modifies all tuples in the relation (dangerous if used incorrectly).

UPDATE Employee
SET Salary = Salary * 1.10;

Update Student set marks = marks+5;

Prn_no name address Marks

20233585001 Rushabh Mumbai 61
20233585002 Shreyas Pune 83
20233585003 Sara Nashik 50

2.3 Update Using Subquery

UPDATE Student
SET Dept = (
SELECT DeptName
 FROM Dept
WHERE [Link] = [Link]
);

 Subqueries are allowed only if they return a single value per modified tuple (scalar
subquery).

2.4 Important Notes

 Updates must preserve integrity constraints.

 Cascading updates may occur depending on referential integrity settings
(CASCADE / RESTRICT / SET NULL).
 If no WHERE clause → entire relation is updated (Ramakrishnan emphasizes caution
here).

3. DELETE Statement
Purpose
To remove tuples from a table based on a condition.

3.1 Syntax
DELETE FROM table_name
WHERE condition;

Prn_no name address Marks

20233585001 Rushabh Mumbai 61
20233585002 Shreyas Pune 83
20233585003 Sara Nashik 50

Delete from Student where marks=83;

Prn_no name address Marks
20233585001 Rushabh Mumbai 61
20233585003 Sara Nashik 50
3.2 Delete All Tuples
DELETE FROM table_name;

But the table structure remains intact.

3.3 Delete Using Subquery

DELETE FROM Employee
WHERE DeptID IN (
SELECT DeptID
FROM Department
WHERE Location = 'Mumbai'
);

 Deletes all Employees belonging to departments located in Mumbai.

4. Effects of Constraints on Modification

✔ Domain constraints – value must be of proper type

✔ Primary key constraints – cannot insert duplicate keys

✔ Foreign key constraints – referenced tuple must exist

✔ CHECK constraints – custom conditions must hold

If a modification violates constraints → transaction aborts.

5. Examples
INSERT Example
INSERT INTO Student(ID, Name, Dept)
VALUES (111, 'Rohit', 'CSE');

UPDATE Example
UPDATE Student
SET Dept = 'ECE'
WHERE ID = 111;
DELETE Example
DELETE FROM Student
WHERE Dept = 'ECE';

Dynamic SQL
Dynamic SQL refers to SQL statements that are constructed and
executed at runtime (instead of being fixed in the program).
Unlike static SQL (compiled at compile-time), dynamic SQL is:
✔ Built
✔ Modified
✔ Prepared
✔ Executed
during program execution.

Why Dynamic SQL? (Need & Importance)

Dynamic SQL is used when:
1. Query changes based on user input
Example: Searching by name, ID, department, etc.
2. Table names or column names are not known in advance
3. Conditional query generation
If/else logic determines part of the SQL.
4. Metadata-driven operations
E.g., dropping tables, adding columns dynamically.
5. Executing SQL stored in variables or fetched from tables

Dynamic SQL Workflow

1. Construct SQL query as a string
 2. Use a runtime function to prepare the SQL
3. Execute it
4. (Optionally) fetch results

Dynamic SQL Example (PL/SQL)

DECLARE
query_str VARCHAR2(200);
BEGIN
query_str := 'UPDATE Employee SET salary = salary + 5000
WHERE DeptID = 10';
EXECUTE IMMEDIATE query_str;
END;
EXECUTE IMMEDIATE executes the query dynamically.

Advantages
 Flexible, supports dynamic conditions
 Useful in data-driven applications
 Allows operations not possible with static SQL (e.g., dynamic
table names)

Disadvantages
 Performance overhead (compiled at runtime)
 Risk of SQL injection (if user input is not sanitized)
 Harder to debug
 More complex than static SQL
2. Stored Procedures
Definition
A Stored Procedure is a precompiled block of SQL statements
stored in the database server.
It can accept input parameters, process logic, perform queries/updates,
and return results.
It is stored and executed on the database side, not on the client
program.

Why Stored Procedures?

✔ Reduce network traffic
✔ Increase performance (precompiled)
✔ Improve security
✔ Promote reusability
✔ Maintainability—update logic at one place
✔ Allow complex operations stored on the server

Features of Stored Procedures

 Can include SQL + procedural logic
 Support variables, loops, conditions
 Can call other procedures
 Allow error handling
 Can return values or result sets
 Allow user-defined business logic
Syntax (General, SQL/PL)
Creating a Stored Procedure
CREATE PROCEDURE increase_salary(IN dept INT, IN amount
INT)
BEGIN
UPDATE Employee
SET Salary = Salary + amount
WHERE DeptID = dept;
END;

Calling a Stored Procedure

CALL increase_salary(10, 5000);

Dropping a Procedure
DROP PROCEDURE increase_salary;

Types of Stored Procedures

1. Procedures with IN parameters
2. Procedures with OUT parameters
3. Procedures with INOUT parameters
4. Procedures with no parameters

Advantages of Stored Procedures

1. Performance
 Compiled once; stored in server
  Executes faster than dynamic SQL
2. Security
 User can execute procedure without seeing internal SQL
 Reduces SQL injection risks
3. Reduced Network Load
 Only call is sent → not entire query
4. Reusability
 Complex logic written once & reused
5. Centralized Business Logic

Disadvantages of Stored Procedures

 Harder to maintain large procedural code
 Less portable across DBMS
 Requires DBA privileges
 Debugging is difficult
 Too many procedures can complicate system

3. Difference Between Dynamic SQL & Stored Procedures

Feature Dynamic SQL Stored Procedure
Execution At runtime Precompiled
Flexibility Very high Limited to defined logic
Security Lower (SQL injection risk) Higher
Performance Slower Faster
Feature Dynamic SQL Stored Procedure
Compilation Every execution Once
Usage For variable SQL For fixed logic

4. Dynamic SQL inside Stored Procedures

Stored procedures can contain dynamic SQL:
CREATE PROCEDURE delete_by_table(IN tname VARCHAR(50))
BEGIN
SET @q = CONCAT('DELETE FROM ', tname);
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
This allows dynamic table or column names.

 Both concepts are essential for database programming and

server-side application logic.