Unit-4

Introduction to SQL

Presented by
Chaithra C S
Assistant Professor
Dept. of Computer Applications
JSSSTU, Mysuru
Syllabus
CO4: Apply SQL to handle all the basic and advanced
operations on DBMS.

Introduction to SQL
Overview of the SQL Query Overview of the SQL Query Language, SQL Data Definition, Basic
Structure of SQL Queries, Additional Basic Operations, Set Operations, Null Values, Aggregate
Functions, Nested Sub queries, Modification of the Database. Intermediate SQL: Join Expressions, Views,
Transactions, Integrity Constraints, SQL Data Types and Schemas, Authorization. Embedded and
Dynamic SQL, Data Integrity and constraints, Further data definition commands and SQL Queries with
examples.
Self-Learning and Teamwork
• Additional Basic Operations of SQL
• Further data definition commands and SQL Queries with examples.
Introduction
Overview of the SQL Query Language
◦ Structured Query Language (SQL) is the most widely used language for managing and manipulating
relational databases. Although it is often referred to as a query language, SQL provides much more than just
query capabilities. It encompasses commands for data definition, data manipulation, integrity constraints,
view management, transaction control, and access authorization.
◦ SQL (Structured Query Language):-
◦ It is non-procedural language. It is international standard language for creating or manipulating relational
database. SQL is used to perform operations on the records stored in the database such as updating
records, deleting records, creating and modifying tables.
◦ IBM developed the original version of SQL, originally called Structured English Query Language (SEQUEL),
as part of the System R project in the early 1970s. The Sequel language has evolved since then, and its
name has changed to SQL (Structured Query Language). SQL was developed at IBM by Donald D.
Chamberlin and Raymond F. Boyce in the 1970s for use in system R. Structured Query
Language) is a domain- specific language used in programming and designed for managing data held in a
relational database management system (RDBMS), SQL is often pronounced SEQUEL.
Characteristics of SQL
◦ Oriented towards relational databases: - SQL is oriented specifically around relational databases.
◦ Can operate on Multiple Groups:-SQL commands can operate on several groups of tables as
single objects.
◦ Uses temporary tables: - SQL allows use of temporary tables. Temporary tables are used for
storing intermediate result.
◦ Suitable for Client/Server Environment:- SQL is well-suited to a client server environment,
where the DBMS resides on a server and services the client requests.
◦ Transaction Management: - SQL provides a flexible transaction management. A transaction is a
group of SQL statements that succeed or fail as a group. If any one of the SQL statements fails, the
transaction is aborted.
◦ Constraint Specification: - SQL allows one to specify constraints. Constraints are the rules for
restricting the values that can be placed in a table column.
◦ Privileges Granting:- Privileges can be granted or denied using SQL commands.
Advantages of SQL
There are the following advantages of SQL:
◦ High speed: - Using the SQL queries, the user can quickly and efficiently retrieve a large amount of
records from a database.
◦ Easy to learn and understand: - SQL mainly consists of English Statements and it is very easy to
learn and understand a SQL query.
◦ Portability:-SQL can be used in laptop, PCs, server and even some mobile phones.
◦ Interactive language: - SQL is a domain language used to communicate with the database. It is also
used to receive answers to the complex questions in seconds.
◦ Multiple data view: - Using the SQL language, the users can make different views of the database
structure.
◦ Supports Object Based Programming:-SQL supports the latest object based programming and
is highly flexible.
Disadvantages of SQL
◦ Partial Control: The programmers who use SQL don’t have a full control over the database
because of the hidden business rules.
◦ Implementation: Some of the databases go to the proprietary extensions to standard SQL for
ensuringthe vendor lock-in.
◦ Cost: The operating cost of some SQL versions makes it difficult for some programmers to access it.
Historical Background:
◦ SQL was originally developed by IBM in the early 1970s as part of the System R project under
the name SEQUEL (Structured English Query Language). Later, the language was renamed SQL
(Structured Query Language). Since then, SQL has become the de facto standard for relational
database systems, supported by almost all commercial and open-source DBMSs such as Oracle,
MySQL, PostgreSQL, SQL Server, and DB2.
◦ To ensure standardization, the American National Standards Institute (ANSI) and the
International Organization for Standardization (ISO) published official standards:
◦ SQL-86 (1986) – the first official standard.
◦ SQL-89 (1989) – an extended version.
◦ SQL-92 (1992) – a major revision.
◦ SQL:1999, SQL:2003, SQL:2006, and SQL:2008 – which successively added new features such as
object-oriented capabilities, triggers, recursive queries, and data analytics extensions.
◦ Despite the existence of these standards, commercial implementations of SQL may differ slightly,
supporting certain nonstandard features or omitting others.
Components of SQL
The SQL language is broadly divided into several functional components:
◦ Data Definition Language (DDL): The DDL provides commands for defining database structures
such as relations (tables), specifying their schemas, and modifying or deleting them.
◦ Data Manipulation Language (DML): The DML allows the user to query the database, as well as
insert, update, and delete data.
◦ Integrity Constraints: SQL allows specification of integrity rules that ensure data correctness
and consistency. Updates violating these rules are automatically disallowed.
◦ View Definition: SQL enables the creation of views, which are virtual tables derived from one or
more base tables.
◦ Transaction Control: SQL supports the definition and management of transactions using
commands such as BEGIN, COMMIT, and ROLLBACK.
◦ Embedded and Dynamic SQL: These features specify how SQL commands can be embedded
within general-purpose programming languages like C, C++, or Java.
◦ Authorization: SQL provides commands for defining user access rights and privileges to ensure
database security.
 SQL process:
◦ When an SQL command is executing for any
RDBMS, then the system figure out the best
way to carry out the request and the SQL
engine determines that how to interpret the
task.
◦ In the process, various components are
included. These components can be
optimization Engine, Query engine, Query
dispatcher, classic, etc.
◦ All the non-SQL queries are handled by the
classic query engine, but SQL query engine
won't handle logical files.
Rules for Writing SQL Queries
There are certain rules for SQL which would ensure consistency and
functionality across databases. By following these rules, queries will be well
formed and well executed in any database.
◦ End with Semicolon (;): Each SQL statement must end with a semicolon
to execute properly.
◦ Case Insensitivity: SQL keywords (e.g., SELECT, INSERT) are not case-
sensitive. However, table or column names may be case-sensitive depending
on the DBMS.
◦ Whitespace Allowed: Queries can span multiple lines, but use spaces
between keywords and names.
◦ Reserved Words: Avoid using SQL keywords as names. If needed, wrap
them in quotes (" ") or backticks (`).
Rules for Writing SQL Queries
Comments
◦ Single-line: -- comment
◦ Multi-line: /* comment */
Data Constraints: Use NOT NULL, UNIQUE, PRIMARY KEY, etc., to
ensure data accuracy.
String Values: Enclose strings in single quotes ('text').
Naming Rules:
◦ We Start with a letter
◦ We can only use Max 30 characters
◦ We only use letters, numbers and underscores (_)
 SQL Datatype
o SQL Datatype is used to define the values that a column can contain.
o Every column is required to have a name and data type in the database table.
Data Type
1. Binary Datatypes
◦ There are Three types of binary Datatypes which are given below:
Data Type
2. Approximate Numeric Datatype :
◦ The subtypes are given below:
Data Type
3. Exact Numeric Datatype
◦ The subtypes are given below:
 Data Type
4. Character String Datatype
◦ The subtypes are given below:
 Data Type
5. Date and time Datatypes
◦ The subtypes are given below:
Basic Data Types
SQL supports a rich collection of built-in data types. Some of the commonly used ones are:

Type Description
char(n) Fixed-length character string of length n.
varchar(n) Variable-length string with a maximum length n.
int or integer Integer values (machine-dependent size).
smallint Smaller range integer type.
Fixed-point number with p total digits, d of which are after the decimal
numeric(p, d) point. Thus, numeric(3,1) allows 44.5 to be stored exactly, but
neither 444.5 or 0.32 can be stored exactly in a field of this type.

real, double precision Floating-point types with machine-dependent precision.

float(n) Floating-point number with precision of at least n digits.
Basic Data Types
◦ Additionally, null values are supported to represent missing or unknown information.
However, some attributes may explicitly prohibit null values using the NOT NULL
constraint.
◦ Example – char vs. varchar:
◦ If an attribute A is declared as char(10) and we store 'Avi', the system pads it with 7 blank
spaces. In contrast, if B is declared as varchar(10) and we store 'Avi', only three characters
are stored. Thus, varchar is usually preferred for efficiency and accuracy in string
comparison.
Basic Schema Definition
The structure of each table is defined using the CREATE TABLE command.
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2),
primary key (dept_name));
◦ This command creates a table named department with three attributes:
◦ dept_name: character string of up to 20 characters,
◦ building: character string of up to 15 characters, and
◦ budget: a numeric value with 12 digits, 2 of which are after the decimal point.
◦ The PRIMARY KEY constraint ensures that each department name is unique and non-null.
General Form of Create Table
create table r
( A1 D1,
A2 D2,
...,
An Dn,
<integrity-constraint 1>,
...,
<integrity-constraint k> );
◦ Here:
◦ r – name of the relation (table).
◦ Aᵢ – attribute names.
◦ Dᵢ – domains or data types of attributes.
◦ Integrity constraints – rules ensuring data consistency.
Common Integrity Constraints
PRIMARY KEY (A1, A2, …, Am):
◦ Ensures that the specified attributes form a unique identifier for each tuple (row). Primary
key values cannot be null.
◦ FOREIGN KEY (A1, A2, …, An) REFERENCES S:
◦ Enforces referential integrity. The foreign key attributes must match the primary key of some
tuple in relation S.
◦ NOT NULL:
◦ Specifies that the attribute must have a value (no nulls allowed).
A Few Commands in SQL

◦ SELECT - extracts data from a database

◦ UPDATE - updates data in a database
◦ DELETE - deletes data from a database
◦ INSERT INTO - inserts new data into a database
◦ CREATE DATABASE - creates a new database
◦ ALTER DATABASE - modifies a database
◦ CREATE TABLE - creates a new table
◦ ALTER TABLE - modifies a table
◦ DROP TABLE - deletes a table
Example Queries
Integrity Constraints and Their Enforcement
SQL enforces database consistency by ensuring that all updates follow the integrity constraints defined
during schema creation. These constraints include primary keys, foreign keys, unique constraints, and
NOT NULL conditions.
Enforcement of Constraints
When a user attempts to perform an operation that violates a constraint, SQL automatically rejects
the modification and generates an error. This prevents the database from entering an inconsistent state.

Examples of Violations
Primary-Key Violation:
◦ A tuple containing a null value in a primary-key attribute, or a duplicate key value, is not allowed.
◦ If dept_name is the primary key of the department table, inserting another tuple with the same dept_name is
rejected.
Foreign-Key Violation:
◦ A foreign key must reference an existing tuple in the parent (referenced) table.
◦ If a tuple is inserted into course with a dept_name not present in department, SQL rejects the insertion.
Integrity Constraints and Their Enforcement
SQL enforces database consistency by ensuring that all updates follow the integrity constraints defined
during schema creation. These constraints include primary keys, foreign keys, unique constraints, and
NOT NULL conditions.
Enforcement of Constraints
When a user attempts to perform an operation that violates a constraint, SQL automatically rejects
the modification and generates an error. This prevents the database from entering an inconsistent state.

Examples of Violations
Primary-Key Violation:
◦ A tuple containing a null value in a primary-key attribute, or a duplicate key value, is not allowed.
◦ If dept_name is the primary key of the department table, inserting another tuple with the same dept_name is
rejected.
Foreign-Key Violation:
◦ A foreign key must reference an existing tuple in the parent (referenced) table.
◦ If a tuple is inserted into course with a dept_name not present in department, SQL rejects the insertion.
SQL Commands
SQL commands are instructions. It is used to communicate with the database. It is also used
to perform specific tasks, functions, and queries of [Link] can perform various tasks like
create a table, add data to tables, drop the table, modify the table, set permission for users.
There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
SQL Data Definition
Before data can be stored or manipulated, the structure of the database must be
defined. This structure is specified through the Data Definition Language (DDL).
◦ The SQL DDL allows users to define:
◦ The schema (structure) of each relation.
◦ The types of values associated with each attribute.
◦ Integrity constraints to enforce data validity.
◦ Indexes for efficient data retrieval.
◦ Authorization information controlling access rights.
◦ Physical storage details for optimization (optional in most cases).
SQL Data Definition
1. Data Definition Language (DDL)
◦ o DDL changes the structure of the table like creating a table, deleting a table, altering a
table, etc.
◦ o All the command of DDL are auto-committed that means it permanently save all the
changes in the database.
Here are some commands that come under DDL:
◦ CREATE
◦ ALTER
◦ DROP
◦ TRUNCATE
 SQL Data Definition
a. CREATE It is used to create a new table in the database.
Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
Example:
CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);
b. DROP: It is used to delete both the structure and record stored in the table.
Syntax
DROP TABLENAME ;
Example
DROP TABLE EMPLOYEE;
c. ALTER: It is used to alter the structure of the database. This change could be either to modify
the characteristics of an existing attribute or probably to add a new attribute.
 SQL Data Definition
Syntax:
To add a new column in the table
ALTER TABLE table_name ADD column_name COLUMN-definition;
To modify existing column in the table:
ALTER TABLE MODIFY(COLUMN DEFINITION....);
EXAMPLE
1. ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));
2. ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));
[Link]: It is used to delete all the rows from the table and free the space containing the table.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE EMPLOYEE;
SQL Data Definition
 SQL Data Definition
◦ The CREATE TABLE statement in SQL is used to define a new table in a database. It specifies the table name,
column names, and their data types. This command lays the foundation for storing and organizing data.
CREATE table table_name
(
Column1 datatype (size),
column2 datatype (size),
.
.
columnN datatype(size)
);
◦ table_name: The name you assign to the new table.
◦ column1, column2, ... : The names of the columns in the table.
◦ datatype(size): Defines the data type and size of each column.
Explanation:
• CustomerID is an integer and serves as the PRIMARY KEY, ensuring each record is unique.
• FirstName, LastName, and Country are VARCHAR fields to store variable-length text.
• Age has a CHECK constraint, ensuring it’s within the range of 0 to 99.
• Phone is an integer field, although in real scenarios, a VARCHAR would often be used for storing phone
numbers to allow for leading zeros and formatting.
 SQL Data Definition
Create Table from Existing Table
We can also create a new table based on the structure (and optionally the data) of an existing table.
The CREATE TABLE AS SELECT command allows us to duplicate an entire table or select specific columns to
form a new one. The following query creates a new table called SubTable that contains CustomerID and
CustomerName from the existing Customer table.
Syntax:
CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table_name
WHERE ...;
In this example, we create a new table SubTable that contains just the CustomerID and CustomerName columns
from the Customer table. This method is useful for creating backups or performing quick data migrations.
Query:
CREATE TABLE SubTable AS
SELECT CustomerID, CustomerName
FROM customer;
 SQL Data Definition
Tips for Using CREATE TABLE in SQL
To ensure the smooth creation and management of your tables, keep these points in mind:
1. The CREATE TABLE statement can also define constraints like NOT NULL, UNIQUE, and DEFAULT.
2. If you attempt to create a table that already exists, SQL will throw an error. To avoid this, you can use
the IF NOT EXISTS clause.
CREATE TABLE IF NOT EXISTS Customer (...);
3. Always define appropriate data types for each column (e.g., VARCHAR(50) for names and INT for IDs)
to optimize performance and storage.
4. After creating a table, use the following command to view the structure of your table:
DESC table_name;
5. If you need to change the table’s structure after creation (e.g., renaming a column, adding a new column),
use the ALTER TABLE statement.
◦
 SQL Data Definition
SQL DROP Command
The SQL DROP command is used to permanently remove an object from a database, such as a table,
database, index or view. When a table is dropped, both its data and structure are permanently removed,
and the action cannot be undone without a backup.
Permanent Removal: Deletes object and data permanently.
Deletes Dependencies: Removes related constraints and indexes.
Schema Deleted: Removes table structure and schema.
No Integrity Check: Skips constraints and triggers.
Syntax:
DROP object object_name ;
object: The type of object you want to drop (e.g., TABLE, DATABASE).
object_name: The name of the object to be deleted.
 SQL Data Definition
Examples of Using the DROP Command
To understand how the DROP command works in SQL, let us explore some practical examples
demonstrating its various applications.
1. DROP Table
To delete an entire table including its data and structure:
Syntax:
DROP TABLE table_name;
2. DROP database
To delete an entire database and all of its associated tables:
Syntax:
DROP DATABASE database_name;
 SQL Data Definition

SQL TRUNCATE Command

◦ The TRUNCATE command is a Data Definition Language (DDL) action that removes all rows from a
table but preserves the structure of the table for future use.
◦ Although TRUNCATE is similar to the DELETE command (without the WHERE clause), it is much faster
because it bypasses certain integrity constraints and locks. It was officially introduced in the SQL:2008
standard.
◦ Syntax:
TRUNCATE TABLE table_name;
• table_name: Name of the table to be truncated.
• DATABASE name: table_name
 SQL Data Definition
DROP Database Example
In this example, we will drop a database called student_data:
DROP DATABASE student_data;
Running this query will completely delete the student_data database,
including all tables and data.
DROP Table Example
In this example, we will drop the student_details table;
DROP TABLE student_details;
This query will permanently remove the student_details table, along with all
its data, from the database.
TRUNCATE Table Example
In this example, we will truncate all data from a table (e.g., student_details)
while keeping the table structure as it is:
TRUNCATE TABLE Student_details;
After running the above query Student_details table will be truncated, i.e, the
data will be deleted but the structure will remain in the memory for further
operations.
 SQL Data Definition
SQL ALTER TABLE statement modify the structure of an existing table in a database. Whether adding new columns,
modifying existing ones, deleting columns or renaming them. ALTER TABLE statement enables you to make changes
without losing data stored in the table.
Syntax:
ALTER TABLE table_name [ADD | DROP | MODIFY] column_name datatype;
Common Use Cases for SQL ALTER TABLE
1. ADD
The ADD clause is used to add a new column to an existing table. You must specify the name of the new column and
its data type.
Parameters:
Syntax: table_name: name of the table you want to modify.
ALTER TABLE table_name ADD: used to add a new column.
ADD column_name datatype; DROP: used to remove an existing column.
Example: MODIFY: used to change datatype or definition of an existing
column.
ALTER TABLE Students
ADD Email varchar(255);
Here, we are adding a column named Email to Student table
 SQL Data Definition
2. MODIFY
The MODIFY (or ALTER COLUMN in some databases like SQL Server) clause is used to modify the definition of an existing column,
such as changing its data type or size.
Syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Example:
ALTER TABLE Students
MODIFY COLUMN Address VARCHAR(100);
Here, we are modifying the column named Address datatype that is VARCHAR(100).
3. DROP
The DROP clause allows you to remove a column from a table. Be cautious when using this command as it will permanently remove
the column and its data.
Query:
ALTER TABLE table_name
DROP COLUMN column_name;
Example:
ALTER TABLE Students
DROP COLUMN Grade;
 SQL Data Definition
4. RENAME COLUMN
We can rename an existing column using RENAME COLUMN clause. This allows you to change the name of a column while
preserving its data type and content.
Query:
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;
Example:
ALTER TABLE Customer
RENAME COLUMN CustomerName TO FirstName;
This renames the column CustomerName to FirstName in the Customer table.
5. RENAME TO
We can rename an entire table using the RENAME TO clause. This changes the name of the table while preserving its structure and
data.
Query:
ALTER TABLE table_name
RENAME TO new_table_name;
Example:
ALTER TABLE Customer
RENAME TO Clients;
This renames the table from Customer to Clients.
 SQL Data Definition
1. Adding Columns (AGE and COURSE)
To add new columns AGE and COURSE to the Student table, use ALTER TABLE statement with
the ADD clause.
Query:
ALTER TABLE Student ADD
(AGE number (3), COURSE VARCHAR (40));
Explanation: This adds an AGE column (numeric) and a COURSE column (VARCHAR(40)). Initially, these
columns will be empty for existing rows.
2. Modify Column COURSE to Reduce its Size
To reduce the size of the COURSE column from VARCHAR(40) to VARCHAR(20), use the MODIFY clause.
Query:
ALTER TABLE Student
MODIFY COURSE varchar(20);
Explanation: COURSE column will now allow a maximum of 20 characters instead of 40.
3. Drop COURSE Column from Student Table
To remove the COURSE column from the Student table, use the DROP COLUMN clause.
Query:
ALTER TABLE Student
DROP COLUMN COURSE;
Explanation: This permanently deletes the COURSE column from the table.
 SQL Data Definition
◦ SQL RENAME TABLE
In SQL, the RENAME TABLE statement is used to change the name of an existing table. This operation is
typically performed when a table's name no longer reflects the data it contains, or to make the table name
more descriptive or organized. Renaming a table does not affect the data or structure of the table; it only
updates the table's identifier in the database schema.
The syntax for renaming a table varies slightly depending on the database system we are using. Below is the
general syntax for the RENAME TABLE statement.
Syntax
RENAME TABLE old_table_name TO new_table_name;
Key terms
◦ old_table_name: The current name of the table that you want to rename.
◦ new_table_name: The new name you want to assign to the table.

Example: RENAME TABLE Customers TO Clients;

 SQL Data Manipulation
◦ DML stands for Data Manipulation Language. The purpose of DML is to select, insert, update, and
delete data in a database. The DML commands in SQL modify or change the data present in the
database.
◦ These commands are not auto-committed. This means they can’t permanently save all the changes
in the database. However, they can roll back using the DML commands themselves.
Following are the four main commands in DML:
◦ INSERT
◦ UPDATE
◦ DELETE
 SQL Data Manipulation
1. INSERT Command : It is a command to insert data into the table’s columns. The user must check
the table to see whether it has any integrity constraints, such as PRIMARY KEY, UNIQUE, NOT
NULL, etc., and enter records accordingly.
Things to keep in mind while inserting data into the table using the INSERT command:
◦ Enclose the string within a single quote, ‘ ‘.
◦ Write the date as YYYY/MM/DD and enclose it within a single quote.
◦ Mention the data type along with the column name.
◦ Follow all INTEGRITY constraints. They are the rules for data being stored in the table.
Syntax:
INSERT INTO table_name (column_1 data type, column_2 data
type,...,column_N data type) VALUES (value 1, value 2, value 3,...,
value N);
Example 1 – Consider an empty table called “Employee” that will contain details of all employees.
The structure of the Employee table is as follows:
| Emp_ID | Emp_Name | Emp_Age | Emp_City | Salary |
 SQL Data Manipulation
Let us add a record of an employee to this table using the DML command INSERT.
◦ INSERT INTO Employee (Emp_ID int, ‘Emp_Name’ varchar(20), Emp_Age int,
‘Emp_City’ varchar(20), Salary int) VALUES (1, ‘Satish’, 25, ‘Pune’,
20000);
◦ INSERT INTO Employee (Emp_ID int, ‘Emp_Name’ varchar(20), Emp_Age int,
‘Emp_City’ varchar(20), Salary int) VALUES (2, ‘Ram’, 26, ‘Pune’, 21000);
To display the record, use the code –
SELECT * FROM Employee;

Emp_ID Emp_Name Emp_Age Emp_City Salary

1 1 Satish 25 Pune 20000

2 2 Ram 26 Pune 21000
 SQL Data Manipulation
Example 2- You can also insert values into the table using another INSERT command format.
◦ No columns need to be specified if you want to insert a record that includes data from every column in the
table. The values can be directly inserted with the INSERT command, which by default takes into account all
columns.
◦ INSERT INTO Employee VALUES (3, ‘Chirag’, 27, ‘Jaipur’, 25000);
To display the result, use the code –
SELECT * FROM Employee;

Emp_ID Emp_Name Emp_Age Emp_City Salary

1 1 Satish 25 Pune 20000

2 2 Ram 26 Pune 21000
3 3 Chirag 27 Jaipur 25000
 SQL Data Manipulation
Example 3 – While inserting the record into the table, you may not have data for all the columns. In this case,
a column that does not contain information will insert a NULL value.
◦ INSERT INTO Employee (Emp_ID int, ‘Emp_Name’ varchar(20), Emp_Age int,
Salary int) VALUES (4, ‘Pankaj’, 24, 15000);
Use the code below to display the result –
◦ SELECT * FROM Employee;

Emp_ID Emp_Name Emp_Age Emp_City Salary

1 1 Satish 25 Pune 20000

2 2 Ram 26 Pune 21000
3 3 Chirag 27 Jaipur 25000
 SQL Data Manipulation
Example 3 – While inserting the record into the table, you may not have data for all the columns. In this case,
a column that does not contain information will insert a NULL value.
INSERT INTO Employee (Emp_ID int, ‘Emp_Name’ varchar(20), Emp_Age
int, Salary int) VALUES (4, ‘Pankaj’, 24, 15000);
Use the code below to display the result –
◦ SELECT * FROM Employee;

Emp_Name Emp_Age Emp_City Salary

Emp_ID
1 1 Satish 25 Pune 20000
2 2 Ram 26 Pune 21000
3 3 Chirag 27 Jaipur 25000
4 4 Pankaj 24 NULL 15000
 SQL Data Manipulation
2. UPDATE Command: This DML command in SQL modifies the record present in the existing table. It updates
the records by using a WHERE clause that specifies the condition with an UPDATE statement. You need to
mention the condition, otherwise, all the rows will be affected.
Syntax –
UPDATE table_name SET column 1 = value 1, column 2 = value 2,..., column N = value N
WHERE CONDITION;

Example: UPDATE Employee SET Address = ‘Chennai’ WHERE Emp_ID = 2;

It will update the employee’s address from Pune to Chennai, where the employee ID is 2.

Emp_ID Emp_Name Emp_Age Emp_City Salary

1 1 Satish 25 Pune 20000
2 2 Ram 26 Chennai 21000
3 3 Chirag 27 Jaipur 25000
4 4 Pankaj 24 NULL 15000
 SQL Data Manipulation
3. DELETE Command: It is used to delete some or all of the records from the existing table. It should contain
the WHERE clause to give the condition. It is necessary to mention the WHERE condition to delete the
selected rows; otherwise, it will delete all the data.
Syntax –
DELETE FROM table_name
WHERE condition;
Example –
DELETE FROM Employee
WHERE Address = ‘Pune’;
◦ It will delete the employee’s record who resides in Pune.

Emp_ID Emp_Name Emp_Age Emp_City Salary

1 2 Ram 26 Chennai 21000
2 3 Chirag 27 Jaipur 25000
3 4 Pankaj 24 NULL 15000
 SQL Data Query Language
SELECT Command
◦ It is used to select and retrieve data from the database table. Note that it does not manipulate data. The SELECT
command is also known as data query language because it is used to query information from a database table.
◦ In the SELECT statement, the table name and column names are specified. It returns data in the form of a result
table. These result tables are called “result sets”.
◦ Various ‘clauses’ that go with the SELECT command are WHERE, GROUP BY, HAVING, ORDER BY, AS, and
DISTINCT. These clauses help filter the results stated in the SELECT clauses.
Let’s discuss each of these clauses one by one.
◦ WHERE Clause: It specifies which row to retrieve from the database.
◦ GROUP BY Clause: It is used to arrange data into groups so that aggregate functions can be applied.
◦ HAVING Clause: It selects among groups defined by the Group By clause by specifying conditions.
◦ ORDER BY Clause: It specifies the order in which to return the rows.
◦ AS Clause: It is used to give a column or table with a temporary name.
◦ DISTINCT Clause: Its use is to remove duplicates from the result set of a SELECT statement (SELECT
DISTINCT).
◦ Syntax – SELECT column_name1, column_name2,...FROM table_name;
 SQL Data Query Language
Example 1: To see all the records in the table Employee, there is no need
to specify every column name in the statement. You can simply use *
asterisk with the SELECT command. This * asterisk mark in SQL recognizes
all the columns and a complete table will appear. Emp_ID Emp_Name
◦ SELECT * FROM Employee; 1 1 Satish

Example 2 – To view only the employees’ IDs and names, you select only 2 2 Ram
Emp_ID and Emp_Name columns in the SELECT command. 3 3 Chirag
◦ SELECT Emp_ID, Emp_Name FROM Employee; 4 4 Pankaj
Example 3 – To retrieve specific records from the table Employee, for
instance, you want to see the information of employees who work in Pune.
◦ SELECT * FROM Employee WHERE Emp_City IN (‘Pune’);

Emp_ID Emp_Name Emp_Age Emp_City Salary

1 1 Satish 25 Pune 20000

2 2 Ram 26 Pune 21000
 SQL Data Control Language
DCL stands for Data Control Language. These commands are used to manage access and permissions in a
database. They control who can see, modify, or manage the data.
GRANT: gives specific privilages to users to perform actions
GRANT permission ON object TO user;

Example: Grants the HR_Manager role permission to view and add data to the Employees table.
GRANT SELECT, INSERT ON Employees TO HR_Manager;

REVOKE: removes specific privilages from users which was previously granted
REVOKE permission ON object FROM user;
Example:
Removes the Sales_Team role's permission to delete data from the Customers table.
REVOKE DELETE ON Customers FROM Sales_Team;
 SQL Transaction Control Language
COMMIT: Saves all the changes you made during a transaction permanently.
BEGIN;
-- SQL statements
COMMIT;
ROLLBACK: Undoes changes you made during a transaction if something went wrong.
BEGIN;
-- SQL statements
ROLLBACK;
SAVEPOINT: Sets a point inside a transaction to which you can roll back if needed.
BEGIN;
-- SQL statements
SAVEPOINT my_savepoint;
-- More SQL statements
ROLLBACK TO my_savepoint;
 SQL Transaction Control Language
`SAVEPOINT` is used within a transaction to create a named point that can be rolled back to if needed.
It is particularly useful for managing complex transactions that may require undoing specific parts
without rolling back the entire transaction.
SAVEPOINT savepoint_name;
◦ In this syntax, `savepoint_name` is a label for the save point created within the transaction.
◦ Example : Creating savepoint
START TRANSACTION;
INSERT INTO orders (product_id, quantity) VALUES (1, 10);
SAVEPOINT order_savepoint;
◦ In this example, a savepoint named `order_savepoint` is created after inserting a row into the `orders`
table. This allows you to roll back to this point if necessary.
 SQL Transaction Control Language

Example: Rolling back to savepoint

INSERT INTO orders (product_id, quantity) VALUES (2, 5);
ROLLBACK TO order_savepoint;
◦ Here, if the second insert operation needs to be undone, the transaction can be rolled back to
`order_savepoint`, effectively undoing the second insert while keeping the first intact.
Example: releasing a Savepoint
RELEASE SAVEPOINT order_savepoint;
◦ Once a savepoint is no longer needed, it can be released to free resources. Note that after releasing,
you cannot roll back to this savepoint.
 Additional basic Operations

The Rename Operation: When writing SQL queries, it is often necessary to rename attributes or
relations. Reasons include:
◦ Attribute Name Conflicts: Two relations may contain attributes with the same name.
◦ Expressions Without Names: Arithmetic expressions in the SELECT clause produce unnamed
attributes.
◦ Convenience: Long relation names can be replaced with shorter aliases.
The rename operation allows assigning a new name to an attribute or relation within a query.
Syntax: old_name AS new_name
Example renaming attributes:
select name as instructor_name, course_id
from instructor, teaches
where [Link] = [Link];
 Additional basic Operations

◦ Example 2: Renaming Relations (Aliases)

◦ select [Link], S.course_id
◦ from instructor as T, teaches as S
◦ where [Link] = [Link];
Rename: It resolves naming conflicts, provides meaningful names to computed attributes, and enables
self-joins. To rename an attribute or relation, SQL uses the AS clause.
Example: for a column
select name as instructor_name
from instructor;
Example : for relation as alias
from instructor as T
 Additional basic Operations
Relation Alias (Correlation Name):
A relation alias is a temporary alternative name for a table within a query.
It shortens long names and enables referencing the same table twice.
Syntax: table_name AS alias
select [Link], S.course_id
from instructor as T, teaches as S
where [Link] = [Link];
Self Join: A join where a table is joined with itself using aliases.
Syntax: from table_name AS A, table_name AS B
Example: Find instructors earning more than at least one Biology instructor:
select distinct [Link]
from instructor as T, instructor as S
where [Link] > [Link]
and S.dept_name = 'Biology';
 Additional basic Operations

◦ A table or a column may be given another name in a SQL query by using an alias. This can be helpful if
there are long or complex table or column names which may be converted to short names using
aliases, in particular in complex queries using several tables. Also, for some reasons, we may want to
change the columns’ names in the output from the names used in the base tables. SQL aliases may then
be used. A column alias in the column list of the SELECT statement may be specified by using the AS
keyword.
◦ Alias used in the FROM clause in SQL
SELECT column_name(s)
FROM table_name AS alias_name;

◦ Alias used in the SELECT command in SQL

SELECT column_name AS alias_name
FROM table_name
 Additional basic Operations

String Constants: A text value enclosed in single quotes. Will store in single quotes
Example : where dept_name = 'Comp. Sci.’
String escaping: Two single quotes inside a string represent one quote.
Syntax: 'It''s correct’
Example: Stores the string: It’s correct
 Simple Queries — Selecting Columns and Rows
Retrieving the Whole Table using *
SELECT * is used to retrieve all columns and all rows from a table.
If no WHERE clause is used → entire table is printed.
Syntax:
SELECT *
FROM table_name;

Example: SELECT *
FROM Student;

StudentID FirstName LastName Gender Country YearOfBirth City

101 Rahul Sharma M India 2002 Delhi

102 Sara Khan F USA 2001 Boston
103 David Lee M UK 2002 London
 Simple Queries — Selecting Columns and Rows
Projection — Selecting Specific Columns
Projection means selecting only some columns from a table (not all).

Syntax:
SELECT column1, column2, ...
FROM table_name;

Example:
Find StudentID, FirstName, LastName:
StudentID FirstName LastName
SELECT StudentID, FirstName, LastName 101 Rahul Sharma
FROM Student;
102 Sara Khan
103 David Lee
Simple Queries — Selecting Columns and Rows

Retrieving All Information About Specific Rows

Use WHERE to retrieve rows matching a condition. WHERE filters rows based on a condition.
Only matching rows are returned.
Example: Find all students from India:

Syntax:
SELECT * SELECT *
FROM Student
FROM table_name
WHERE Country = 'India';
WHERE condition;
StudentID FirstName LastName Country YearOfBirth City
101 Rahul Sharma India 2002 Delhi
110 Priya Singh India 2001 Mumbai
Simple Queries — Selecting Columns and Rows

Multiple Conditions (AND / OR)

AND → all conditions must be true Indian students born after 2001:
OR → any one condition must be true
SELECT *
FROM Stud ent
Syntax: WHERE Country = 'India'
SELECT * AND YearOfBirth > 2001;
FROM table_name
WHERE condition1 AND condition2;

StudentID FirstName LastName Country YearOfBirth City

115 Aman Gupta India 2003 Pune
Simple Queries — Selecting Columns and Rows

Retrieving Certain Columns of Certain Rows

Combines both selection (WHERE) and projection (SELECT columns).

Syntax:
SELECT column1, column2
FROM table_name
WHERE condition; Find StudentID and Grade of students who got ‘A’:

SELECT StudentID, CourseID, Grade

FROM Enrollment
StudentID CourseID Grade
WHERE Grade = 'A';

102 C101 A
106 C103 A
Simple Queries — Selecting Columns and Rows

Using IN to Match Multiple Values:

IN checks whether a column value matches any value in a given list.

Syntax:
WHERE column IN (value1, value2, ...)

Students from India or USA:

StudentID FirstName Country
SELECT *
101 Rahul India
FROM Student
102 Sara USA WHERE Country IN ('India', 'USA');
110 Priya India
Simple Queries — Selecting Columns and Rows

Using DISTINCT (Removing Duplicate Values)

DISTINCT removes duplicate values from the result.

Syntax:
SELECT DISTINCT column_name
FROM table_name;

Country
List all the Unique Countries:
India
USA SELECT DISTINCT Country
FROM Student;
UK
Canada
Simple Queries — Selecting Columns and Rows

DISTINCT with Multiple Columns

Removes duplicates based on a combination of columns.

Syntax:

SELECT DISTINCT column1, column2

FROM table_name; Unique pairs of Country and City:
Country City
SELECT DISTINCT Country, City
India Delhi FROM Student;
India Mumbai
USA Boston
Simple Queries — Selecting Columns and Rows

Matching a Specific Number / ID

Filters rows using numeric conditions.

Syntax:

WHERE numeric_column = value;

Students born in 2002:

StudentID FirstName LastName SELECT StudentID, FirstName, LastName

FROM Student
101 Rahul Sharma WHERE YearOfBirth = 2002;
103 David Lee
ORDER BY

Sorting the Results (ORDER BY): ORDER BY sorts results in ascending

(ASC) or descending (DESC) order.

Syntax:
SELECT column1, column2
FROM table_name
ORDER BY column1 ASC|DESC;
Sort students alphabetically by FirstName:
StudentID FirstName LastName
SELECT StudentID, FirstName, LastName
103 David Lee FROM Student
ORDER BY FirstName ASC;
101 Rahul Sharma

102 Sara Khan

STRING MATCHING IN SQL (LIKE OPERATOR)

String Matching Using LIKE: Wildcard Meaning

LIKE is used for pattern matching on text values. % any sequence of characters
_ exactly one character
Syntax:
WHERE column LIKE 'pattern';

FirstName LastName
Find students whose last name starts with “Sh”:
Rahul Sharma

Neha Sharma SELECT FirstName, LastName

FROM Student
WHERE LastName LIKE 'Sh%';
STRING MATCHING IN SQL (LIKE OPERATOR)

LIKE uses two wildcards to match flexible character patterns: the percent
(%) wildcard and the underscore (_) wildcard. The percent symbol (%)
represents any sequence of zero or more characters, while the underscore (_)
represents exactly one single character.
This makes LIKE extremely useful when the exact value is not known but
the pattern is known. For example, the pattern 'Del%' matches any string
that begins with “Del”, such as “Delhi” or “Delight”, because % allows a
variable number of trailing characters.
STRING MATCHING IN SQL (LIKE OPERATOR)

Similarly, the pattern 'Gup_' matches any four-character string that begins
with “Gup”, since _ matches exactly one trailing character such as “Gupa”
or “Gupt”.
LIKE is fundamentally different from the equality (=) operator because SQL
does not pad strings when matching patterns; equality compares strings by
padding the shorter one with spaces if needed. LIKE simply tests whether
the actual string satisfies the given wildcard pattern.
Syntax: RollNo FName LName City
column_name LIKE 'pattern'; 101 Ravi Singhal Delhi
column_name NOT LIKE 'pattern'; 102 Megha Singh Mumbai
103 Rohan Kumar Mysore
104 Anita Sinha Delhi
105 Mohan Singar Bengaluru
Simple Queries — Selecting Columns and Rows
Example 3: Last name with exactly one character after “Sing” FName LName
SELECT * FROM STUDENT
WHERE LName LIKE 'Sing_’; Megha Singh

Only “Singh” matches, because _ requires exactly one trailing character.

Example 4: Names ending with “ar” FName LName

SELECT * FROM STUDENT
WHERE LName LIKE '%ar'; Mohan Singar
 SQL STRING FUNCTIONS

SQL provides several built-in functions for processing strings. These

functions help convert case, extract substrings, and compute length.
They are commonly used in data cleaning, display formatting, and
pattern matching.
FName City_UPPER
UPPER(string): Converts all characters in a string to
uppercase. Ravi DELHI
Megha MUMBAI
Syntax:
Rohan MYSORE
UPPER(string)
Example: Anita DELHI
SELECT FName, UPPER(City) AS City_UPPER
Mohan BENGALURU
FROM STUDENT;
 SQL STRING FUNCTIONS LName lname_lower
Singhal singhal
LOWER(string): Converts the string into lowercase. Singh singh
Syntax: Kumar kumar
LOWER(string)
Sinha sinha
Example: SELECT LOWER(LName) AS lname_lower
FROM STUDENT; Singar singar

Proper
INITCAP(string): NITCAP converts the first letter of every word in the LName
Name
input string into uppercase and converts the remaining letters into
Singhal Singhal
lowercase. It is commonly used to format names or text into proper “title
case”. Singh Singh
Syntax: INITCAP(string) Kumar Kumar
Example SELECT LName, INITCAP(LName) AS ProperName
Sinha Sinha
FROM STUDENT;
Singar Singar
 SQL STRING FUNCTIONS

Example: SELECT INITCAP('computer SCIENCE department') AS Converted;

Converted
Computer Science Department
 SQL STRING FUNCTIONS

LENGTH(string): The LENGTH function returns the number of characters in the string, including
letters, digits, and special symbols. Spaces are also counted.
Syntax:
LENGTH(string)
LName NameLength
Length of each student’s last name Singhal 7
Singh 5
SELECT LName, LENGTH(LName) AS NameLength
FROM STUDENT; Kumar 5
Sinha 5
Singar 6
 SQL STRING FUNCTIONS

SUBSTR(string, start_position, length):

SUBSTR extracts a substring from the given string. The starting position is counted from 1, and
the second argument defines how many characters to extract.
Syntax:
SUBSTR(string, start_position, length) LName First3Letters
Singhal Sin
Example: First 3 characters of each Lname
Singh Sin
SELECT LName, SUBSTR(LName, 1, 3) AS First3Letters Kumar Kum
FROM STUDENT; Sinha Sin
Singar Sin
 SQL STRING FUNCTIONS
LName Middle2
Singhal ng
Example: Middle 2 characters (starting from position 3) Singh ng
Kumar ma
SELECT LName, SUBSTR(LName, 3, 2) AS Middle2
Sinha nh
FROM STUDENT;
________________________________________________________ Singar ng
Example: Last 3 characters of each LName

To extract last 3 characters, we use LENGTH(): LName Last3Letters

Singhal hal
SELECT LName, Singh ngh
SUBSTR(LName, LENGTH(LName)-2, 3) AS Last3Letters Kumar mar
FROM STUDENT;
Sinha nha

SUBSTR(‘Singhal’, 5, 3) Characters 5,6,7 hal Singar gar

 FORMS OF THE WHERE CLAUSE
The WHERE clause allows SQL queries to filter rows RollNo FName LName City
based on conditions. SQL supports a wide range of 101 Ravi Singhal Delhi
comparison operators—>, <, >=, <=, =, <>—as well as 102 Megha Singh Mumbai
logical operators such as AND, OR, and NOT. These 103 Rohan Kumar Mysore
conditions can also include pattern matching (LIKE), list
104 Anita Sinha Delhi
checking (IN), range checking (BETWEEN), NULL tests,
and subqueries. 105 Mohan Singar Bengaluru

WHERE C1 AND C2: Returns rows that satisfy both

conditions C1 and C2.
Syntax:
WHERE condition1 AND condition2

Students from Delhi whose last name starts with ‘Sin’

FName LName City
SELECT * FROM STUDENT Ravi Singhal Delhi
WHERE City='Delhi' AND LName LIKE 'Sin%';
 FORMS OF THE WHERE CLAUSE
WHERE C1 OR C2: Returns rows where at least one RollNo FName LName City
condition is true. 101 Ravi Singhal Delhi
AND is executed before OR unless parentheses are used.
104 Anita Sinha Delhi
Select students either from Delhi OR whose last name ends
Bengalur
with “ar”. 105 Mohan Singar
u
Syntax: WHERE condition1 OR condition2
SELECT * FROM STUDENT
WHERE City='Delhi' OR LName LIKE '%ar’;

WHERE NOT C1: Selects rows that do not satisfy the

specified condition.
Syntax: WHERE NOT condition
RollNo FName LName City
Select students who are not from Delhi. 102 Megha Singh Mumbai
Example: Students not from Delhi
103 Rohan Kumar Mysore
SELECT * FROM STUDENT
WHERE NOT City='Delhi'; 105 Mohan Singar Bengaluru
 FORMS OF THE WHERE CLAUSE
WHERE IN (list): The IN operator allows a value to be matched against a list of values. A row is
selected if the column value matches any value from the list. The IN operator checks whether a value
exists in a list of values or in the result returned by a subquery.
✔ A IN (x, y, z) means “A is equal to any of these values”.
✔ Used to test set membership.

Syntax: WHERE column IN (value1, value2, ...)

-- OR
WHERE column IN (SELECT column FROM table) RollNo FName LName City

Tests if a value belongs to a list of values. 101 Ravi Singhal Delhi

NOT IN selects values not in the list.
102 Megha Singh Mumbai

Select students whose city is Delhi or Mumbai. 104 Anita Sinha Delhi
Ex: SELECT * FROM STUDENT
WHERE City IN ('Delhi','Mumbai’);
 FORMS OF THE WHERE CLAUSE
WHERE NOT IN : The NOT IN operator selects rows whose values are not present in the given
list.
Syntax: WHERE column NOT IN (value1, value2, ...)
SELECT * FROM STUDENT
WHERE City NOT IN ('Delhi','Mumbai’); RollNo FName LName City

103 Rohan Kumar Mysore

Where Between:
The BETWEEN operator is used to select rows whose values lie 105
within a Mohan Singar bothBengaluru
range, including
boundary values.

WHERE column BETWEEN value1 AND value2

Select roll numbers from 102 to 104 inclusive. RollNo FName LName City
102 Megha Singh Mumbai
SELECT * FROM STUDENT
WHERE RollNo BETWEEN 102 AND 104; 103 Rohan Kumar Mysore
104 Anita Sinha Delhi
 FORMS OF THE WHERE CLAUSE
Where LIKE:
The LIKE operator is used to match strings using wildcards:
% → any sequence of characters RollNo FName LName City
_ → exactly one character
102 Megha Singh Mumbai

Syntax: 105 Mohan Singar Bengaluru

WHERE column LIKE 'pattern’

SELECT * FROM STUDENT

WHERE LName LIKE 'Sing%';
 FORMS OF THE WHERE CLAUSE
WHERE IS NULL:

The IS NULL operator is used to test whether a column contains no value (NULL). Equality (=)
cannot be used to check for NULL.

Syntax: WHERE column IS NULL

RollNo FName LName City

106 Jyoti Mehta NULL

SELECT * FROM STUDENT

WHERE City IS NULL;
 FORMS OF THE WHERE CLAUSE
WHERE EXISTS: EXISTS checks whether the subquery returns at least one row.
If the subquery has a result, EXISTS is TRUE.

Syntax:
SELECT columns RollNo Marks
FROM table
101 80
WHERE EXISTS (
SELECT 1 102 70
FROM another_table
WHERE condition
); FName LName
SELECT [Link], [Link]
Ravi Singhal
FROM STUDENT S
WHERE EXISTS ( Megha Singh
SELECT *
FROM MARKS M
WHERE [Link] = [Link]
);
 FORMS OF THE WHERE CLAUSE
WHERE NOT EXISTS:

NOT EXISTS is true only when the subquery returns no rows. Used to find rows that have no
matching record in another table. SELECT [Link], [Link]
FROM STUDENT S
Syntax: WHERE NOT EXISTS (
SELECT *
SELECT columns
FROM MARKS M
FROM table
WHERE [Link] = [Link]
WHERE NOT EXISTS ( );
SELECT 1
FROM another_table
WHERE condition
FName LName
);
Rohan Kumar
Anita Sinha
Mohan Singar
 QUERIES INVOLVING MORE THAN ONE TABLE
A subquery (also called an inner query or nested query) is a SELECT statement written inside
another SQL statement. The result of the subquery is used by the outer query to filter rows or
compute values. Subqueries are typically written inside the WHERE, FROM, or SELECT clause.

✔ Subqueries return a value, a row, a column, or a table.

✔ Common forms: IN (subquery), = (subquery), EXISTS (subquery).

SELECT columns
FROM table [Link] executes the inner subquery first.
WHERE expression operator ( [Link] subquery returns a value or set of values.
SELECT columns [Link] outer query uses that result to filter its own rows.
FROM table
WHERE condition
);
 QUERIES INVOLVING MORE THAN ONE TABLE

Find students enrolled in the CS department:

SELECT Name
FROM Student
WHERE DeptID IN (
SELECT DeptID
FROM Department
WHERE DeptName='Computer Science’
);
Execution:
Subquery finds DeptID = 10
Outer query selects students whose DeptID = 10
 QUERIES INVOLVING MORE THAN ONE TABLE
Nested Subquery: A nested subquery is a subquery inside another subquery.
SQL allows multiple levels of nesting.

Syntax:
How it Works
SELECT ... [Link] innermost subquery executes first.
FROM table [Link] result is used by the next level.
WHERE column IN ( [Link], the outermost query executes.
SELECT column
FROM table
WHERE column IN (
SELECT column
FROM table
WHERE condition
)
);
 QUERIES INVOLVING MORE THAN ONE TABLE
Example:
Find courses taken by “Arjun Kumar”:

SELECT *
FROM Course Execution:
WHERE CourseID IN ( Inner → find SID of Arjun
SELECT CourseID Middle → find all CourseIDs of Arjun
FROM Enrollment Outer → fetch details of those courses
WHERE SID IN (
SELECT SID
FROM Student
WHERE FName='Arjun' AND LName='Kumar'
)
);
 QUERIES INVOLVING MORE THAN ONE TABLE
JOIN: A join combines rows from two or more tables based on related columns.

Syntax:
SELECT columns
FROM Table1 t1
JOIN Table2 t2
ON t1.common_column = t2.common_column;

How it Works
SQL finds rows where the join condition is TRUE.
It merges columns from both tables into a single result row.
Only matching rows (INNER JOIN) are returned.
 QUERIES INVOLVING MORE THAN ONE TABLE
Example: List students with their course names:

SELECT [Link], [Link]

FROM Student S
JOIN Enrollment E ON [Link] = [Link]
JOIN Course C ON [Link] = [Link];

Execution:
Join Student -- Enrollment using SID
Join Enrollment -- Course using CourseID
Return merged rows
 QUERIES INVOLVING MORE THAN ONE TABLE
Self Join:
A self join is when a table is joined with itself using two different aliases.

Syntax:
SELECT [Link], [Link]
FROM table a
JOIN table b
ON a.common_column = b.common_column
WHERE additional_condition;

How it Works
SQL treats the same table as two separate tables (aliases).
It matches rows in one alias to rows in the other alias.
 QUERIES INVOLVING MORE THAN ONE TABLE
Example: Find students who scored above 80 in DBMS and OS:

SELECT [Link]
FROM Enrollment e1
JOIN Enrollment e2 ON [Link] = [Link]
WHERE [Link]=501 AND [Link]=502
AND [Link]>80 AND [Link]>80;

Execution:
e1 = DBMS marks
e2 = OS marks
Same SID must satisfy both conditions
 QUERIES INVOLVING MORE THAN ONE TABLE
Example: Find students who scored above 80 in DBMS and OS:

SELECT [Link]
FROM Enrollment e1
JOIN Enrollment e2 ON [Link] = [Link]
WHERE [Link]=501 AND [Link]=502
AND [Link]>80 AND [Link]>80;

Execution:
e1 = DBMS marks
e2 = OS marks
Same SID must satisfy both conditions
 College Database Example SID FName LName DeptID
1 Arjun Kumar 10
There are four table: 2 Riya Sharma 20
1. Student 3. Enrolment 3 Vivek Gupta 10
2. Department 4. Course
4 Sneha Reddy 30
CourseID CourseName DeptID 5 Mohan Das 20
501 DBMS 10
502 Operating Systems 10 SID CourseID Marks
601 Digital Circuits 20 1 501 87
701 Thermodynamics 30 2 501 90

DeptID DeptName 3 501 45

1 502 92
Computer
10
Science 4 701 86
20 Electronics 3 601 72

30 Mechanical
 QUERIES INVOLVING MORE THAN ONE TABLE
(JOINS + SUBQUERIES USING COLLEGE DATABASE)
Q1 — Find all CourseIDs in which “Riya Sharma” is enrolled.
Use a subquery to find the SID of student Riya Sharma, then use this to find all CourseIDs from
ENROLLMENT.
SELECT CourseID
FROM ENROLLMENT
WHERE SID IN (
SELECT SID
FROM STUDENT
WHERE FName='Riya' AND LName='Sharma'
);
Subquery returns SID = 2 for Riya Sharma.
Outer query retrieves all CourseIDs from ENROLLMENT where SID = 2. CourseID
Riya is enrolled only in CourseID 501. 501
 QUERIES INVOLVING MORE THAN ONE TABLE
Student Table Department Table
RollNo Name Dept Age Dept DeptName HOD
101 Ananya Rao CSE 20 CSE Computer Science Dr. Vikram Nair
102 Rohan Mehta ECE 19 ECE Electronics & Comm. Dr. Meena Joshi
103 Sneha Patil CSE 21 ME Mechanical Engg. Dr. Prakash Rao
104 Aravind KS ME 22 CIV Civil Engineering Dr. Lakshmi Iyer

Marks Table
RollNo CourseID Marks
101 CS101 86
101 CS102 78
102 EC201 91
103 CS101 67
Joins
A SQL JOIN is an operation that combines rows from two or more tables based on a related
column, usually a primary key–foreign key relationship. Since data in relational databases is
normalized and spread across multiple tables, joins are necessary to reconstruct complete
information. They create a logical link between tables at query time without physically merging
them. Joins allow users to write powerful queries that answer real-world questions involving
multiple entities.
• In practice, information like student details, department details, and marks are stored in separate
tables for consistency and minimal redundancy.
• To answer queries such as “list each student with their department HOD” or “show departments
with no students”, we must access more than one table at once. SQL provides different join types
to handle only matching rows, unmatched rows, or all combinations.
• Thus, joins form the backbone of multi-table query processing in relational database systems.
 Inner Join
An INNER JOIN returns only those rows where the join condition between two tables is
satisfied. Rows from either table that do not have a matching value in the other table are
excluded from the result. Effectively, it gives the intersection of both tables with respect to the
join condition. This join is used when we are interested only in fully matched, valid
relationships.
Syntax: Name HOD
SELECT column_list Ananya Rao Dr. Vikram Nair
FROM TableA Rohan Mehta Dr. Meena Joshi
INNER JOIN TableB Sneha Patil Dr. Vikram Nair
ON TableA.common_col = TableB.common_col;
Aravind KS Dr. Prakash Rao
Example:
Display each student’s name along with the HOD of their department.
SELECT [Link], [Link]
FROM Student s
INNER JOIN Department d
ON [Link] = [Link];
 Left Outer Join
A LEFT OUTER JOIN returns all rows from the left table and the matching rows from the right
table. If there is no matching row in the right table, the result will still contain the left-table row
but with NULLs in the columns of the right table. It is particularly useful when we want to
retain all master records from the left table and still see where related records are missing in the
right table. This helps in auditing, reporting and integrity checks.
Syntax:
SELECT column_list
FROM TableA
LEFT JOIN TableB
ON TableA.common_col = TableB.common_col;
Example:List all departments and the names Dept DeptName Name
of students enrolled in them CSE Computer Science Ananya Rao
SELECT [Link], [Link], [Link] CSE Computer Science Sneha Patil
FROM Department d
ECE Electronics & Comm. Rohan Mehta
LEFT JOIN Student s
ON [Link] = [Link]; ME Mechanical Engg. Aravind KS
CIV Civil Engineering NULL
 Right Outer Join
A RIGHT OUTER JOIN returns all rows from the right table and only the matching rows from the left
table. If a row exists in the right table with no corresponding match in the left table, the result still
includes the right-table row with NULL values for left-table columns. It is essentially the mirror of the left
join and is helpful when the right table is considered the main reference. It highlights missing or
incomplete data in the left table relative to the right.
Syntax:
SELECT column_list Name Dept DeptName
FROM TableA
RIGHT JOIN TableB Ananya Rao CSE Computer Science
ON TableA.common_col = TableB.common_col; Sneha Patil CSE Computer Science
List all departments and show student names where
Rohan Mehta ECE Electronics & Comm.
they exist, ensuring every department appears.
SELECT [Link], [Link], [Link] Aravind KS ME Mechanical Engg.
FROM Student s NULL CIV Civil Engineering
RIGHT JOIN Department d
ON [Link] = [Link];
 Full Outer Join (available as UNION)
A FULL OUTER JOIN returns all rows from both tables regardless of whether matches exist. When rows
match, they appear combined once; when a row has no match, it appears with NULL for the columns of
the other table. This join is very useful in reconciliation tasks where differences between two datasets
must be identified. It gives a complete picture of matches and mismatches in one query.
Syntax:
SELECT column_list
FROM TableA
FULL OUTER JOIN TableB Name Dept DeptName
ON TableA.common_col = TableB.common_col; Ananya Rao CSE Computer Science
Sneha Patil CSE Computer Science
Show all departments and all students, including any
department without students and any student with invalid Rohan Mehta ECE Electronics & Comm.
department Aravind KS ME Mechanical Engg.
SELECT [Link], [Link], [Link]
NULL CIV Civil Engineering
FROM Student s
FULL OUTER JOIN Department d
ON [Link] = [Link];
 Cross Join
A CROSS JOIN returns the Cartesian product of two tables. Every row from the first table is paired with
every row from the second table, irrespective of matching values. It does not require a join condition and
can produce very large result sets. It is mainly used to generate all combinations, sample data, or when a
later WHERE clause will filter the combinations.
Syntax: Name Dept
SELECT column_list Ananya Rao CSE
FROM TableA
CROSS JOIN TableB; Ananya Rao ECE
Ananya Rao ME
Generate all possible combinations of students and Ananya Rao CIV
departments
Rohan Mehta CSE
SELECT [Link], [Link] … …
FROM Student s
CROSS JOIN Department d;

Total rows = 4 students × 4 departments = 16.

 Self Join
A SELF JOIN is a join where a table is joined with itself to represent hierarchical or recursive
relationships between its rows. It uses aliases to treat different instances of the same table as if they were
separate tables. This is commonly used to show employee–manager relationships, pre-requisite courses,
or parent–child relationships. It allows comparison of rows within the same dataset.
Syntax:
SELECT [Link], [Link]
FROM Table e1
LEFT JOIN Table e2 Employee Manager
ON e1.related_id = [Link]; Harish Kumar Kavya Narayan
Priya Sharma Kavya Narayan
Display each employee along with their manager’s name.
Kavya Narayan NULL
SELECT [Link] AS Employee, [Link] AS Manager
FROM Employee e
LEFT JOIN Employee m
ON [Link] = [Link];
Set operations

Enrollment Table
SID CourseID Semester Year

Course Table S01 C101 ODD 2023

CourseID CourseName S02 C102 ODD 2023

C101 Data Structures S03 C103 ODD 2023

C102 DBMS S04 C104 ODD 2023

C103 Operating Systems S01 C102 EVEN 2024

C104 Computer Networks S02 C102 EVEN 2024

C105 Mathematics S03 C105 EVEN 2024

C106 Python Programming S04 C106 EVEN 2024

S04 C106 EVEN 2024
 Set operations
• UNION combines the results of two Courses taught in ODD 2023
SELECT queries and returns distinct rows SELECT CourseID
from both result sets. FROM Enrollment
• It removes duplicates automatically, WHERE Semester = 'ODD' AND Year = 2023;
following true mathematical set union (A ∪ CourseID
B).
C101
• UNION is used when we want to merge
information fetched from multiple C102
semesters, years, or departments. C103
• Both SELECT subqueries must return the C104
same number of columns and compatible
data types. CourseID
Syntax: C102
SELECT column_list Courses taught in EVEN 2024
SELECT CourseID C105
FROM table1
FROM Enrollment C106
UNION WHERE Semester = 'EVEN' AND Year = 2024;
SELECT column_list C106
FROM table2;
 Set operations-
CourseID
C101
UNION Query – Removes Duplicate
C102
(SELECT CourseID FROM Enrollment WHERE Semester='ODD' AND Year=2023)
UNION C103
(SELECT CourseID FROM Enrollment WHERE Semester='EVEN' AND Year=2024); C104
C105
CourseID C106
C101
UNION ALL Query –with Duplicate
C102 (SELECT CourseID FROM Enrollment WHERE Semester='ODD' AND Year=2023)
C103 UNION ALL
(SELECT CourseID FROM Enrollment WHERE Semester='EVEN' AND Year=2024);
C104
C102
C105
C106
C106
 Set operations
INTERSECT Operation
• INTERSECT returns all rows that appear in both result sets.
• It mirrors the mathematical intersection (A ∩ B) and removes duplicates automatically.
• This operation is ideal for finding common subjects across semesters or years.
• Only values present at least once in both queries appear in the output.

Syntax:
SELECT column_list
FROM table1
INTERSECT
CourseID
SELECT column_list
FROM table2; C102

Query:
(SELECT CourseID FROM Enrollment WHERE Semester='ODD' AND Year=2023)
INTERSECT
(SELECT CourseID FROM Enrollment WHERE Semester='EVEN' AND Year=2024);
 Set operations
EXCEPT Operation
• EXCEPT returns rows from the first SELECT that do not appear in the second SELECT.
• It removes duplicates before performing the difference and outputs only distinct results.
• This is used to find discontinued courses, subjects not taught in next semester, or changes in curriculum.
• It follows mathematical set difference (A − B).

Syntax;
SELECT column_list
FROM table1
EXCEPT
SELECT column_list
FROM table2;
Query: The courses offered in ODD 2023 but not in EVEN 2024. CourseID
C101
(SELECT CourseID FROM Enrollment WHERE Semester='ODD' AND Year=2023) C103
EXCEPT
(SELECT CourseID FROM Enrollment WHERE Semester='EVEN' AND Year=2024); C104
 Working with NULL Values
• A NULL value in SQL represents missing, unknown, or inapplicable data.
• Any arithmetic or comparison involving NULL results in NULL or UNKNOWN, not TRUE or FALSE.
• SQL uses three-valued logic: TRUE, FALSE, UNKNOWN, making NULL handling different from normal
values.
• To check NULL values, SQL provides special predicates IS NULL and IS NOT NULL.
• NULL is not equal to zero, an empty string, or any other value.
SID Name Email
Syntax; S01 Arjun arjun@[Link]
column IS NULL
S02 Meera NULL
column IS NOT NULL
S03 Rahul rahul@[Link]
Query: Find all students whose email is NOT provided
SELECT SID, Name
FROM Student
WHERE Email IS NULL; SID Name
S02 Meera
 AGGREGATE FUNCTIONS
• Aggregate functions operate on groups of rows and return a single computed value.
• They are used in statistical reports such as average marks, total enrollments, minimum fees, or maximum
attendance.
• Common SQL aggregate functions include COUNT, SUM, AVG, MIN, and MAX.
SID Marks
• These functions ignore NULL values (except COUNT(*)).
• They are widely used in analytics, dashboards, and performance summaries. S01 85
Syntax: S02 90
SELECT AGG_FUNC(column)
S03 NULL
FROM table
WHERE condition; S04 70

Query: Find the average marks of all students

SELECT AVG(Marks) AS AvgMarks AvgMarks
FROM Marks; 81.66

Null is ignored
 AGGREGATE FUNCTIONS SID Name Marks
S01 Arjun 85
COUNT() – Counting Rows
S02 Meera 90
• COUNT() is used to count the number of rows in a table.
• COUNT(column) counts only non-NULL values, whereas COUNT() counts all rows S03 Rohan 75
including NULLs. S04 Sneha NULL
• It is used widely for calculating number of students, courses, registrations, or entries.
S05 Kavya 95
• COUNT ignores NULL values unless COUNT() is used.
Syntax: S06 Varun 60
SELECT COUNT(column_name) FROM table;
SELECT COUNT(*) FROM table;

Query: Count how many students have marks recorded StudentsWithMarks

SELECT COUNT(Marks) AS StudentsWithMarks 5
FROM Marks;

Count total number of students (including NULL marks)

SELECT COUNT(*) AS TotalStudents
FROM Marks; TotalStudents
6
 AGGREGATE FUNCTIONS
SUM() – Total of Values
• SUM() returns the total of all numeric values in a column.
• It automatically ignores NULL values and only adds valid numbers.
• It is used in fee reports, marks analysis, total credits calculation, etc.
• SUM works only on numeric data types.
Syntax:
SELECT SUM(column_name) FROM table;

Query: Find total marks scored by all students

SELECT SUM(Marks) AS TotalMarks
FROM Marks;
Calculation: 85 + 90 + 75 + 95 + 60 = 405
TotalMarks
405
 AGGREGATE FUNCTIONS
AVG() – Average of Values
• The AVG() function calculates the arithmetic mean of numeric values.
• It ignores NULL values, ensuring only valid entries contribute to the average.
• AVG is useful for calculating class averages, performance analysis, and grading metrics.
• It returns decimal values unless rounded explicitly.

Syntax:
SELECT AVG(column_name) FROM table;

Query: Find the average marks of students AverageMarks

SELECT AVG(Marks) AS AverageMarks
FROM Marks; 81.00

(405 ÷ 5 = 81)
 AGGREGATE FUNCTIONS
MIN() – Minimum Value
• MIN() returns the smallest value in a numeric or text column.
• It ignores NULL values and considers only valid entries.
• Used for identifying the lowest score, minimum fee, earliest date, etc.
• It works on numbers, strings (alphabetical order), and dates.

Syntax:
SELECT MIN(column_name) FROM table;
LowestMarks
Query: Find the lowest marks in the class 60

SELECT MIN(Marks) AS LowestMarks

FROM Marks;
 AGGREGATE FUNCTIONS
MAX() – Maximum Value
• MAX() returns the highest value in the selected column.
• It ignores NULL values and considers only actual data.
• Commonly used for finding highest marks, maximum fees, latest dates, etc.
• Like MIN, it works on numeric, textual, and date columns.

Syntax:
SELECT MAX(column_name) FROM table;
HighestMarks
Query: Find the highest marks in the class 95

SELECT MAX(Marks) AS HighestMarks

FROM Marks;
 AGGREGATE FUNCTIONS
COUNT(DISTINCT) – Count Unique Values
• COUNT(DISTINCT column) counts the number of unique non-NULL values.
• It removes duplicates before counting, making it useful for identifying unique students, courses, or grades.
• This helps avoid inflated counts when duplicates exist.
• It is often used in enrollment, attendance, and registration systems.

Syntax:
SELECT COUNT(DISTINCT column_name) FROM table; UniqueMarks

Query: Count how many different marks students scored 5

SELECT COUNT(DISTINCT Marks) AS UniqueMarks

FROM Marks;
(Marks: 85, 90, 75, 95, 60 — all unique)
 GROUP BY
• GROUP BY divides rows into smaller groups based on the values of one or more columns.
• Aggregate functions are then applied separately to each group.
• This is useful to find statistics department-wise, course-wise, semester-wise, etc.
• Only attributes listed in GROUP BY can appear without aggregation in the SELECT clause, otherwise an
error occurs.
Syntax:
SELECT column, AGG_FUNC(column)
FROM table
GROUP BY column;

Query: Count number of enrollments per semester

SELECT Semester, COUNT(*) AS TotalEnrollments
FROM Enrollment
GROUP BY Semester; Semester TotalEnrollments
ODD 4
EVEN 5
 GROUP BY SID
S01
Name
Arjun
Dept
CS
SID
S01
Marks
85
S02 Meera CS S02 90
QUERY: Find average marks of each department S03 Rohan EC S03 75
S04 Sneha EC S04 88
SELECT Dept, AVG(Marks) AS AvgMarks
S05 Kavya ME S05 60
FROM Student
JOIN Marks ON [Link] = [Link]
GROUP BY Dept;
Dept AvgMarks
Find total number of students in each department CS 87.50
EC 81.50
SELECT Dept, COUNT(*) AS StudentCount
ME 60.00
FROM Student
GROUP BY Dept; Dept StudentCount
CS 2
EC 2
ME 1
 HAVING Clause
• The HAVING clause is used to filter groups after GROUP BY has been applied.
• WHERE filters rows before grouping, while HAVING filters groups after aggregation.
• It allows conditions based on aggregate values, such as groups with totals above a threshold.
• HAVING is essential when summarizing large datasets and performing conditional grouping.

Syntax:
SELECT column, AGG_FUNC(column)
FROM table
GROUP BY column
HAVING condition_on_aggregate;

Query: Display semesters where total enrollments exceed 4

SELECT Semester, COUNT(*) AS TotalEnrollments
FROM Enrollment
GROUP BY Semester Semester TotalEnrollments
HAVING COUNT(*) > 4; EVEN 5
 HAVING Clause
QUERY: Show departments where average marks > 80 Dept AvgMarks
CS 87.50
SELECT Dept, AVG(Marks) AS AvgMarks
FROM Student EC 81.50
JOIN Marks ON [Link] = [Link]
GROUP BY Dept
HAVING AVG(Marks) > 80;

(ME is removed because its avg = 60)

Show departments where count of students > 1

SELECT Dept, COUNT(*) AS StudentCount Dept StudentCount

FROM Student CS 2
GROUP BY Dept
EC 2
HAVING COUNT(*) > 1;
 View
A VIEW in SQL is a virtual table created from a SELECT query on one or more base tables.
Unlike normal tables that are stored physically on disk, a view does not store data. Each time the view is used,
the DBMS runs the query defined inside the view.
Views are very useful for hiding complexity, restricting access, and providing a simplified or secure version of
the database.
They also provide logical data independence, meaning users can query views without knowing how data is
stored in base tables.
If base tables are updated, the view automatically shows updated results because it is computed dynamically.
Stude
Marks
nt
SID CourseID Marks
SID Name Dept Country
S01 C101 85
S01 Arjun CS India
S02 C101 90
S02 Meera CS India
S03 C102 75
S03 Rohan EC USA
S04 C102 88
S04 Sneha EC India
S05 C103 60
S05 Kavya ME UK
 Marks
DEFINING VIEWS (CREATE VIEW) SID CourseID Marks
Syntax: S01 C101 85
CREATE VIEW view_name AS
S02 C101 90
SELECT column1, column2, ...
FROM base_table S03 C102 75
WHERE condition; S04 C102 88
S05 C103 60
Create a View of High Scoring Students

CREATE VIEW HighScorers AS

SELECT [Link], [Link], [Link] Student
FROM Student SID Name Dept Country
JOIN Marks ON [Link] = [Link]
S01 Arjun CS India
WHERE [Link] >= 80;
S02 Meera CS India
SID Name Marks
S03 Rohan EC USA
S01 Arjun 85
S04 Sneha EC India
S02 Meera 90
S05 Kavya ME UK
S04 Sneha 88
 SYNTAX FOR QUERYING A VIEW
Syntax:
SELECT column_list
FROM view_name
WHERE condition;

List students who scored above 85 Name Marks

SELECT Name, Marks Meera 90

FROM HighScorers Sneha 88
WHERE Marks > 85;
 HOW THE VIEW QUERY IS TRANSFORMED INTERNALLY

Original Query

SELECT Name, Marks

FROM HighScorers
WHERE Marks > 85;

Internal trasformation

SELECT [Link], [Link]

FROM Student
JOIN Marks ON [Link] = [Link]
WHERE [Link] >= 80 -- view condition
AND [Link] > 85; -- query condition
 VIEW WITH AGGREGATE FUNCTIONS
Syntax:
SELECT Country
CREATE VIEW view_name AS FROM StudentCountByCountry
SELECT column, AGG_FUNC(column) WHERE TotalStudents = (
FROM table SELECT MAX(TotalStudents) FROM
GROUP BY column; StudentCountByCountry
);
Count students in each country

CREATE VIEW StudentCountByCountry AS Country

SELECT Country, COUNT(*) AS TotalStudents
India
FROM Student
GROUP BY Country;

Country TotalStudents
India 3
USA 1
UK 1
 UPDATING A VIEW
Syntax:

UPDATE view_name
SET column = value
WHERE condition;

CREATE VIEW CSEStudents AS SID Name

SELECT SID, Name
S01 Arjun
FROM Student
WHERE Dept = 'CS’; S02 Meera

Update through view

SID Name
UPDATE CSEStudents
SET Name = 'Arjun Kumar' S01 Arjun Kumar
WHERE SID = 'S01’; S02 Meera

After updation s01 is Arjun Kumar

 Embedded SQL

• Embedded SQL is the technique of writing SQL statements inside a host programming language
such as C, C++, Java, or COBOL using special syntax like EXEC SQL.
• The SQL commands are processed by a precompiler, converted into function calls of the host
language, and then executed like normal program code.
• Embedded SQL allows programmers to use procedural constructs (loops, IF-ELSE, functions)
along with SQL queries.
• It is mainly used when the program logic is fixed and SQL queries are known at compile time.
 Embedded SQL

Characteristics of Embedded SQL

• SQL code is written inside a host language using EXEC SQL prefix.
• SQL statements are statically embedded, meaning they are known before program execution.
• Requires a precompiler (preprocessor) to convert SQL to host-language function calls.
• Uses host variables to exchange data between SQL and program, prefixed by colon (:).
• Uses cursors to fetch rows one-by-one due to impedance mismatch.
• Easier error handling using SQLCA, SQLCODE, SQLSTATE.
 Embedded SQL

Advantages of Embedded SQL

• Allows combining SQL with powerful procedural constructs like loops and conditions.
• Faster execution because queries are precompiled and optimized.
• Errors in SQL syntax are detected early during compilation.
• Improves security and structure in database-driven applications.
• Good for applications where logic and queries rarely change.

Disadvantages of Embedded SQL

• Queries cannot change at runtime—not flexible.
• Requires precompiler setup, making development slower.
• Hard to maintain if SQL logic changes frequently.
• Does not handle dynamic scenarios like user-selected table names or columns.
• Requires more code for handling multiple rows (cursor usage).
 Embedded SQL This is a program that uses Embedded SQL inside a C language
program.
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION; Fetch all students from the CSE department using a cursor.
char name[20];
char dept[10];
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO studentdb USER 'admin' USING 'admin123';
EXEC SQL DECLARE C1 CURSOR FOR
SELECT Name, Dept
FROM Student
WHERE Dept = 'CSE';

EXEC SQL OPEN C1;

while (1) {
EXEC SQL FETCH C1 INTO :name, :dept;
if (SQLCODE != 0) break;
printf("Name: %s Dept: %s\n", name, dept);
}
EXEC SQL CLOSE C1;
EXEC SQL COMMIT;
 Embedded SQL SQLCA = SQL Communication Area
It contains important information like SQL errors, SQLSTATE, SQLCODE.
EXEC SQL INCLUDE SQLCA; Required for checking whether fetch succeeded or not.
EXEC SQL BEGIN DECLARE SECTION;
char name[20]; These are normal C variables.
char dept[10]; They will store values fetched from the database. Connects the C program to the database named studentdb.
EXEC SQL END DECLARE SECTION; Uses username 'admin' and password 'admin123'.
EXEC SQL CONNECT TO studentdb USER 'admin' USING 'admin123';
EXEC SQL DECLARE C1 CURSOR FOR A cursor is like a pointer that can move row-by-row through query results.
SELECT Name, Dept Required because C cannot handle multiple rows at once (SQL returns sets).
FROM Student
WHERE Dept = 'CSE';

EXEC SQL OPEN C1; Executes the SELECT query.

while (1) { Cursor now points to before the first row.
EXEC SQL FETCH C1 INTO :name, :dept; FETCH moves the cursor to the next row.
if (SQLCODE != 0) break; The values go into name and dept.
printf("Name: %s Dept: %s\n", name, dept);
}
EXEC SQL CLOSE C1;
EXEC SQL COMMIT;
Dynamic SQL

• Dynamic SQL is SQL that is constructed and executed at runtime, usually using strings in
languages like Java, Python, or C.
• It allows greater flexibility because the exact SQL statement does not need to be known during
compilation.
• Dynamic SQL is used when the query structure depends on user input, changing table names, or
conditions determined at runtime

Characteristics of Dynamic SQL

• SQL query is built as a string at runtime.
• Allows table names, column names, WHERE and ORDER BY clauses to change dynamically.
• Uses PREPARE, EXECUTE, or host language functions.
• Can adapt automatically to changing data structures.
• More flexible than static/embedded SQL.
• Mostly used in web apps, reporting tools, and dashboards.
Dynamic SQL

Advantages of Dynamic SQL

• Very flexible—query can change based on user input or runtime conditions.
• Ideal for reporting applications where queries vary.
• Allows building applications that access tables with dynamic names.
• No need to recompile program when SQL structure changes.
• Useful in data warehousing, dashboards, ad-hoc reporting.

Disadvantages of Dynamic SQL

• Slower performance because query must be parsed at runtime.
• Higher risk of SQL injection attacks if not handled safely.
• Harder to debug because SQL is built during execution.
• Does not allow compile-time checking of SQL errors.
• Requires careful handling of strings and parameters.
 Dynamic SQL

User enters department name at runtime, and query is prepared dynamically.

String dept = userInput(); // e.g., "CSE"

String query = "SELECT Name, Dept FROM Student WHERE Dept = '" + dept + "'";

Statement stmt = [Link]();

ResultSet rs = [Link](query);

while ([Link]()) {
[Link]([Link]("Name") + " " + [Link]("Dept"));
}
Difference Between Embedded SQL and Dynamic SQL

Embedded SQL Dynamic SQL

SQL is written inside the program and known at

SQL is generated at runtime as a string.
compile time.

Uses EXEC SQL statements processed by a

Uses PREPARE, EXECUTE, or language functions.
precompiler.

Slower because SQL must be parsed during

Faster because SQL is precompiled.
execution.

Good for stable applications where queries do not Good for flexible applications where queries change
change. often.

Cannot easily build SQL with dynamic table names or Fully supports dynamic table names, columns, and
conditions. WHERE clauses.

Safer and detects errors early. Higher chance of SQL injection if not coded carefully.

Requires cursors to handle multiple rows. Fetching data depends on host language library.
Used in enterprise software (banking apps, ERP). Used in websites, dashboards, reporting tools.