M

O
H
A
M
M
ED
M
AT
H
EE
N
L
R
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Contents

LICENSE 3

CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) 4

CHAPTER NOTES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
9.2 Structured Query Language (SQL) . . . . . . . . . . . . . . . . . . . . . . . . . 4

R
9.2.1 Installing MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
9.3 Data Types and Constraints in MySQL . . . . . . . . . . . . . . . . . . . . . . . 6

L
9.4 SQL for Data Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

N
9.4.4 — ALTER TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
9.4.5 — DROP Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

EE
9.5 — SQL for Data Manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
9.6 — SQL for Data Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
9.6.2 — Querying Using Database OFFICE . . . . . . .
H . . . . . . . . . . . . . . . . . 19
AT
9.7 — Data Updation and Deletion . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
9.8: Functions in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
9.9 — GROUP BY Clause in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
M

9.10 – Operations on Relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

ED
M
M
A
H
O
M

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
2
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

LICENSE

This work is licensed under the Creative Commons Attribution-NonCommercial-NoDerivs 4.0 Interna-
tional License. To view a copy of this license, visit [Link] or
send a letter to Creative Commons, PO Box 1866, Mountain View, CA 94042, USA.

“Karnataka Second PUC Computer Science Study Material / Student Notes” by L R Mohammed Matheen
is licensed under CC BY-NC-ND 4.0.

R
L
N
Figure 1: Licence

EE
This work is licensed under the Creative Commons Attribution-NonCommercial-NoDerivs 4.0 Interna-
tional License.

H
Portions of this work may include material under separate copyright. These materials are not covered by
AT
this Creative Commons license and are used by permission or under applicable copyright exceptions.

This book is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 4.0 Interna-

M

tional License.
ED
M
M
A
H
O
M

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
3
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL)

CHAPTER NOTES

Introduction

Overview of RDBMS and SQL

R
• In the previous chapter, you learned about Relational Database Management Systems
(RDBMS) — systems that organize data in tables called relations.

L
• Examples of RDBMS:

N
– MySQL
– Microsoft SQL Server

EE
– PostgreSQL
– Oracle

H
AT
Purpose of RDBMS

• Allows us to:
M

– Create a database using relations (tables).

– Store, retrieve, and manipulate data using queries.
ED

• The tool used to perform all these actions is called SQL (Structured Query Language).
M

9.2 Structured Query Language (SQL)

M

What is SQL?
A

• SQL (Structured Query Language) is a special-purpose language used to access and manipulate
data in a database.
H

• It is used instead of writing application programs, especially in the context of Database Man-
O

agement Systems (DBMS).

M

• SQL works with popular RDBMS like:

– MySQL
– ORACLE
– SQL Server

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
4
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Key Features of SQL

Feature Description

Easy to Learn SQL uses descriptive English words; it’s readable and intuitive.
Case-Insensitive Commands like SELECT and select are treated the same.
Declarative You tell SQL what you want, not how to do it.

R
Powerful SQL does more than just queries. It includes commands to define data,
insert data, update, and manage constraints.

L
N
Capabilities of SQL

EE
• Define data structures (tables, attributes, constraints)
• Manipulate data (insert, delete, update)
• Query data (retrieve specific or filtered data)
H
AT
• Declare constraints (rules on what kind of data can go into tables)
M

SQL with MySQL Example

• The textbook uses the StudentAttendance database (introduced in Chapter 8) to explain SQL
ED

queries.
• Students will create, populate, and query this database using SQL.
M

9.2.1 Installing MySQL

M

What is MySQL?
A

• MySQL is a popular, open-source Relational Database Management System (RDBMS).

H

• It supports SQL and is widely used to create and manage databases.

O

How to Install MySQL?

M

• Download from the official site:

[Link]

• Install the software on your computer.

• Start MySQL service.

– Once started, the terminal (or command-line interface) will show the prompt:

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
5
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

mysql>

– This indicates that MySQL is ready to accept SQL commands.

Key Points to Remember While Using SQL in MySQL

Point Description

R
Case Insensitivity SQL keywords, table names, and column names are not case-sensitive.
End Statements with ;

L
Always terminate SQL commands with a semicolon.
Multiline Statements If an SQL statement is long: → Write it over multiple lines → Don’t

N
put ; until the last line → mysql> prompt changes to -> for

EE
continuation

H
Activity 9.1 Q: Find and list other types of databases besides RDBMS.
AT
Example Answers:

• NoSQL databases (e.g., MongoDB, CouchDB)

M

• Hierarchical databases
• Object-oriented databases
ED

• Network databases
M

9.3 Data Types and Constraints in MySQL

M

What are Data Types and Constraints?

• Every table (relation) is made up of attributes (columns).

A

• Each attribute:
H

– Has a data type — defines the kind of values it can store.

O

– May have constraints — rules that restrict the values entered into that column.
M

9.3.1 Data Type of Attribute

Definition: The data type of an attribute defines:

• What kind of values can be stored.

• What kind of operations can be performed on them.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
6
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Data Type Description

CHAR(n) Fixed-length character string. - Length n can be

from 0 to 255. - e.g., CHAR(10) stores exactly
10 characters; unused space is padded with
spaces.
VARCHAR(n) Variable-length character string. - Length n up

R
to 65535. - Only actual input length occupies
space.

L
INT Integer values. - Occupies 4 bytes. - Use

N
BIGINT (8 bytes) for larger numbers.

EE
FLOAT Decimal (floating-point) numbers. - Occupies 4
bytes.
DATE
H
Stores dates in 'YYYY-MM-DD' format. - Range:
'1000-01-01' to '9999-12-31'.
AT
M

Activity 9.2: What other data types are supported in MySQL? Are there other variants of integer and
float?
ED

Examples:

• BIGINT, DECIMAL, DOUBLE

M

• TEXT, TINYTEXT, BLOB

• DATETIME, TIMESTAMP, TIME, YEAR
M
A

9.3.2 Constraints
H

Definition: Constraints are rules or restrictions placed on data in a table to ensure accuracy and
O

reliability.
M

Constraint Description

NOT NULL Value must be provided; cannot be left empty

(NULL).
UNIQUE All values in the column must be different.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
7
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Constraint Description

DEFAULT Assigns a default value if no value is

entered.
PRIMARY KEY Uniquely identifies each row in a table. Must
be unique and not null.
FOREIGN KEY Refers to a primary key in another table.

R
Used to create relationships.

L
N
Concept Check: Q: Which two constraints, when applied together, produce a Primary Key? NOT
NULL + UNIQUE

EE
9.4 SQL for Data Definition

H
AT
Purpose This section explains how to:

• Create a database
M

• Create tables inside a database

• Define attributes, data types, and constraints
ED

• View or modify table structure

1. STUDENT Table Attributes & Description

M
M

Attribute Data Type Constraint Description

A

RollNumber INT PRIMARY KEY Unique roll number of student (1–100)

SName VARCHAR(20) NOT NULL Student name
H

SDateofBirth DATE NOT NULL Date of birth

O

GUID CHAR(12) FOREIGN KEY Guardian’s Aadhaar (linked to

M

GUARDIAN)

SQL Command

CREATE TABLE STUDENT (

RollNumber INT,
SName VARCHAR(20),

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
8
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

SDateofBirth DATE,
GUID CHAR(12),
PRIMARY KEY (RollNumber)
);

DESCRIBE Output

mysql> DESCRIBE STUDENT;

R
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |

L
+--------------+-------------+------+-----+---------+-------+
| RollNumber | int | NO | PRI | NULL | |

N
| SName | varchar(20) | YES | | NULL | |

EE
| SDateofBirth | date | YES | | NULL | |
| GUID | char(12) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
H
AT
2. GUARDIAN Table Attributes & Description
M

Attribute Data Type Constraint Description

ED

GUID CHAR(12) PRIMARY KEY Unique Aadhaar number of guardian

GName VARCHAR(20) NOT NULL Guardian name
M

GPhone CHAR(10) NULL, UNIQUE Phone number (optional but unique)

GAddress VARCHAR(30) NOT NULL Address of guardian
M
A

3. ATTENDANCE Table Attributes & Description

H
O

Attribute Data Type Constraint Description

M

AttendanceDate DATE Composite Primary Key Date of attendance

RollNumber INT Composite PK, FOREIGN Student’s roll number
KEY
AttendanceStatus CHAR(1) NOT NULL ‘P’ for present, ‘A’ for absent

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
9
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

SHOW TABLES Command in SQL Purpose:

The SHOW TABLES; command is used to list all tables present in the currently selected database.

Syntax:

SHOW TABLES;

Example:

Assume you’ve created and selected the database named StudentAttendance.

R
USE StudentAttendance;

L
SHOW TABLES;

N
Output

EE
+------------------------------+
| Tables_in_studentattendance |
+------------------------------+
H
AT
| STUDENT |
| GUARDIAN |
| ATTENDANCE |
M

+------------------------------+
3 rows in set (0.00 sec)
ED

This shows that the database contains three tables: STUDENT, GUARDIAN, and ATTENDANCE.
M

9.4.4 — ALTER TABLE

M

Purpose of ALTER TABLE Once a table is created, you might need to:
A
H

• Add or remove attributes (columns)

• Modify data types or constraints
O

• Add primary/foreign/unique keys

M

All these structural changes are done using the ALTER TABLE command.

General Syntax:
ALTER TABLE table_name
<alteration_action>;

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
10
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

(A) Add Primary Key to a Table

Example:
ALTER TABLE GUARDIAN
ADD PRIMARY KEY (GUID);

ALTER TABLE ATTENDANCE

ADD PRIMARY KEY (AttendanceDate, RollNumber);

R
Composite Primary Key: Combines multiple columns to uniquely identify a record.

L
(B) Add Foreign Key to a Table

N
Ensure:

EE
• The referenced table exists.
• The referenced column is a primary key.
• Data types must match.
H
AT
Syntax:
M

ALTER TABLE table_name

ADD FOREIGN KEY (column_name)
ED

REFERENCES referenced_table (referenced_column);

Example:
M

ALTER TABLE STUDENT

M

ADD FOREIGN KEY (GUID) REFERENCES GUARDIAN(GUID);

A

(C) Add UNIQUE Constraint Ensures all values in a column are distinct.
H
O

Example:
M

ALTER TABLE GUARDIAN

ADD UNIQUE (GPhone);

(D) Add New Attribute

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
11
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Syntax:
ALTER TABLE table_name
ADD column_name datatype;

Example:
ALTER TABLE GUARDIAN
ADD Income INT;

R
(E) Modify Data Type of Attribute

L
N
Syntax:

EE
ALTER TABLE table_name
MODIFY column_name NEW_DATATYPE;

Example: H
AT
ALTER TABLE GUARDIAN
MODIFY GAddress VARCHAR(40);
M

(F) Modify Constraint (e.g., Add NOT NULL)

ED

Example:
M

ALTER TABLE STUDENT

MODIFY SName VARCHAR(20) NOT NULL;
M

You must re-specify the datatype when changing constraints.

A
H

(G) Add DEFAULT Value

O

Syntax:
M

ALTER TABLE table_name

MODIFY column_name datatype DEFAULT default_value;

Example:
ALTER TABLE STUDENT
MODIFY SDateofBirth DATE DEFAULT '2000-05-15';

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
12
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

(H) Remove an Attribute

Syntax:
ALTER TABLE table_name
DROP column_name;

Example:

R
ALTER TABLE GUARDIAN

L
DROP Income;

N
(I) Remove Primary Key

EE
Syntax:
ALTER TABLE table_name
H
AT
DROP PRIMARY KEY;

Example:
M

ALTER TABLE GUARDIAN

DROP PRIMARY KEY;
ED

Note: Each table should have a primary key for data integrity. You can re-add it using ADD PRIMARY
KEY.
M
M

ALTER TABLE — Summary Table

A

Operation Purpose Syntax / Example

H

Add Primary To define a column(s) as a ALTER TABLE table_name ADD PRIMARY KEY
O

Key primary key (column); ALTER TABLE GUARDIAN ADD

M

PRIMARY KEY (GUID);

Add Use multiple columns as a ALTER TABLE ATTENDANCE ADD PRIMARY KEY
Composite single primary key (AttendanceDate, RollNumber);
Primary Key
Add Foreign To create a relationship to ALTER TABLE STUDENT ADD FOREIGN KEY
Key another table’s primary key (GUID) REFERENCES GUARDIAN(GUID);

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
13
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Operation Purpose Syntax / Example

Add UNIQUE To ensure all values in a ALTER TABLE GUARDIAN ADD UNIQUE
Constraint column are unique (GPhone);
Add New To add a new column to an ALTER TABLE GUARDIAN ADD Income INT;
Attribute existing table
Modify Data To change data type of an ALTER TABLE GUARDIAN MODIFY GAddress

R
Type existing column VARCHAR(40);

L
Modify To enforce NOT NULL or ALTER TABLE STUDENT MODIFY SName
Constraint other constraints VARCHAR(20) NOT NULL;

N
Add To provide a default value ALTER TABLE STUDENT MODIFY SDateofBirth

EE
DEFAULT for a column DATE DEFAULT '2000-05-15';
Value
Remove
Attribute
To delete a column from a
table H
ALTER TABLE GUARDIAN DROP Income;
AT
Remove To drop an existing primary ALTER TABLE GUARDIAN DROP PRIMARY KEY;
Primary Key key
M
ED

9.4.5 — DROP Statement

Purpose of the DROP Statement The DROP statement in SQL is used to permanently delete a:
M

• Table, or
M

• Entire database
A

Once dropped:
H

• The data cannot be recovered (use with caution).

O

• All associated data, structure, and constraints are lost permanently.

M

A. DROP a Table

Syntax:
DROP TABLE table_name;

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
14
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Example:
DROP TABLE STUDENT;

This command will delete the STUDENT table, including all its data and structure from the database.

B. DROP a Database

R
L
Syntax:

N
DROP DATABASE database_name;

EE
Example:
DROP DATABASE StudentAttendance;

H
This will delete the entire database and all tables (STUDENT, GUARDIAN, ATTENDANCE, etc.) within it.
AT
Important Notes:
M

• There is no undo for DROP. Once executed, the data is gone permanently.
• Always double-check before dropping tables or databases.
ED

• If you’re testing, it’s good practice to back up data or use temporary test tables.

9.5 — SQL for Data Manipulation

M
M

What is Data Manipulation?

• After defining tables (DDL), we use Data Manipulation Language (DML) commands to:
A

– Insert
H

– Update
O

– Delete
M

9.5.1 INSERTION of Records Syntax 1: Insert into all columns

INSERT INTO table_name

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

Use this when inserting values for all attributes in the same order as the table.

Syntax 2: Insert into selected columns

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
15
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

Use when skipping some columns (e.g., default or NULL values).

Example 1: Insert a Guardian Record

INSERT INTO GUARDIAN

VALUES (444444444444, 'Amit Ahuja', 5711492685, 'G-35, Ashok Vihar, Delhi');

R
Result:

L
Query OK, 1 row affected (0.01 sec)

N
View the Inserted Record:

EE
SELECT * FROM GUARDIAN;

H
+--------------+--------------+-----------+----------------------------+
AT
| GUID | GName | GPhone | GAddress |
+--------------+--------------+-----------+----------------------------+
M

| 444444444444 | Amit Ahuja | 5711492685| G-35, Ashok Vihar, Delhi |

+--------------+--------------+-----------+----------------------------+
ED

Example 2: Insert Guardian with NULL Phone

INSERT INTO GUARDIAN (GUID, GName, GAddress)

M

VALUES (333333333333, 'Danny Dsouza', 'S -13, Ashok Village, Daman');

M

Explanation: Skips GPhone. It will be stored as NULL.

A

Query OK, 1 row affected (0.03 sec)

H
O

Example 3: Insert into STUDENT with All Fields

M

INSERT INTO STUDENT

VALUES (1, 'Atharv Ahuja', '2003-05-15', 444444444444);

Or

INSERT INTO STUDENT (RollNumber, SName, SDateofBirth, GUID)

VALUES (1, 'Atharv Ahuja', '2003-05-15', 444444444444);

View Student Record:

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
16
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

SELECT * FROM STUDENT;

+------------+--------------+--------------+--------------+
| RollNumber | SName | SDateofBirth | GUID |
+------------+--------------+--------------+--------------+
| 1 | Atharv Ahuja | 2003-05-15 | 444444444444 |
+------------+--------------+--------------+--------------+

R
Example 4: Insert Student with NULL GUID (Allowed)

L
INSERT INTO STUDENT
VALUES (3, 'Taleem Shah', '2002-02-28', NULL);

N
EE
Or using selected columns:

INSERT INTO STUDENT (RollNumber, SName, SDateofBirth)

VALUES (3, 'Taleem Shah', '2002-02-28');
H
AT
GUID is a foreign key, so it can be NULL, but must match if present.
M

View Student Table:

SELECT * FROM STUDENT;

ED

+------------+--------------+--------------+--------------+
| RollNumber | SName | SDateofBirth | GUID |
M

+------------+--------------+--------------+--------------+
M

| 1 | Atharv Ahuja | 2003-05-15 | 444444444444 |

| 3 | Taleem Shah | 2002-02-28 | NULL |
A

+------------+--------------+--------------+--------------+
H

Tip:
O

If the order of attributes is not known, always use Syntax 2 (with column names).
M

Q: Can we insert two records with the same RollNumber? No, because RollNumber is a
PRIMARY KEY, it must be unique.

Great! Let’s now explore the next major section.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
17
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

9.6 — SQL for Data Query

What is Data Querying? Once data is inserted into tables, we use SQL to retrieve specific infor-
mation. This process is called querying.

SQL provides the SELECT command to:

• Display entire tables

• Fetch specific columns

R
• Apply conditions to filter rows

L
N
EE
9.6.1 SELECT Statement Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE condition;
H
AT
Example 1: Select All Columns
M

SELECT * FROM STUDENT;

Explanation:
ED

* selects all attributes from the table.

Output:
M

+------------+--------------+--------------+--------------+
M

| RollNumber | SName | SDateofBirth | GUID |

+------------+--------------+--------------+--------------+
A

| 1 | Atharv Ahuja | 2003-05-15 | 444444444444 |

H

| 3 | Taleem Shah | 2002-02-28 | NULL |

+------------+--------------+--------------+--------------+
O
M

Example 2: Select Specific Columns

SELECT RollNumber, SName FROM STUDENT;

Output:

+------------+--------------+
| RollNumber | SName |

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
18
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

+------------+--------------+
| 1 | Atharv Ahuja |
| 3 | Taleem Shah |
+------------+--------------+

Example 3: Apply a WHERE Clause

SELECT * FROM STUDENT

R
WHERE GUID IS NOT NULL;

L
Output:

N
+------------+--------------+--------------+--------------+

EE
| RollNumber | SName | SDateofBirth | GUID |
+------------+--------------+--------------+--------------+
| 1 | Atharv Ahuja | 2003-05-15 | 444444444444 |
+------------+--------------+--------------+--------------+
H
AT
9.6.2 — Querying Using Database OFFICE
M

Context Many organizations manage data using databases like OFFICE, which include related ta-
bles such as EMPLOYEE and DEPARTMENT. Each employee belongs to a department, and the DeptId in
ED

EMPLOYEE is a foreign key referencing DEPARTMENT.

M

EMPLOYEE Table (Sample Data — Table 9.8)

M

EmpNo Ename Salary Bonus DeptId

A

101 Aaliya 10000 234 D02

H

102 Kritika 60000 123 D01

O

103 Shabbir 45000 566 D01

M

104 Gurpreet 19000 565 D04

105 Joseph 34000 875 D03
106 Sanya 48000 695 D02
107 Vergese 15000 NULL D01
108 Nachaobi 29000 NULL D05

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
19
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

EmpNo Ename Salary Bonus DeptId

109 Daribha 42000 NULL D04

110 Tanya 50000 467 D05

(A) Retrieve Selected Columns Query 1:

R
SELECT EmpNo FROM EMPLOYEE;

L
Output:

N
+-------+

EE
| EmpNo |
+-------+
|
|
101 |
102 | H
AT
| 103 |
| 104 |
M

| 105 |
| 106 |
ED

| 107 |
| 108 |
| 109 |
M

| 110 |
+-------+
M
A

Query 2:
H

SELECT EmpNo, Ename FROM EMPLOYEE;

O

Output:
M

+-------+----------+
| EmpNo | Ename |
+-------+----------+
| 101 | Aaliya |
| 102 | Kritika |
| 103 | Shabbir |

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
20
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

| 104 | Gurpreet |
| 105 | Joseph |
| 106 | Sanya |
| 107 | Vergese |
| 108 | Nachaobi |
| 109 | Daribha |
| 110 | Tanya |
+-------+----------+

R
L
(B) Renaming Columns (Using Alias) Query:

N
SELECT Ename AS Name FROM EMPLOYEE;

EE
Output:

+----------+
| Name | H
AT
+----------+
| Aaliya |
M

| Kritika |
| Shabbir |
ED

| Gurpreet |
| Joseph |
| Sanya |
M

| Vergese |
| Nachaobi |
M

| Daribha |
A

| Tanya |
+----------+
H
O

(C) Calculated Columns (Annual Income) Query:

M

SELECT Ename AS Name, Salary*12 FROM EMPLOYEE;

Output:

+----------+-----------+
| Name | Salary*12 |
+----------+-----------+

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
21
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

| Aaliya | 120000 |
| Kritika | 720000 |
| Shabbir | 540000 |
| Gurpreet | 228000 |
| Joseph | 408000 |
| Sanya | 576000 |
| Vergese | 180000 |
| Nachaobi | 348000 |

R
| Daribha | 504000 |

L
| Tanya | 600000 |
+----------+-----------+

N
To rename the calculated column:

EE
Query (Using Alias with Space):

H
SELECT Ename AS Name, Salary*12 AS 'Annual Income' FROM EMPLOYEE;
AT
Output:

+----------+---------------+
M

| Name | Annual Income |

+----------+---------------+
ED

| Aaliya | 120000 |
| Kritika | 720000 |
| Shabbir | 540000 |
M

| Gurpreet | 228000 |
M

| Joseph | 408000 |
| Sanya | 576000 |
A

| Vergese | 180000 |
| Nachaobi | 348000 |
H

| Daribha | 504000 |
O

| Tanya | 600000 |
M

+----------+---------------+

Note: Aliased column names with spaces should be enclosed in single quotes ('Annual Income').

(D) Using DISTINCT — To Avoid Duplicates Purpose: The DISTINCT clause is used to retrieve
unique values only, avoiding repetitions.
Query: Display Unique Department IDs

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
22
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

SELECT DISTINCT DeptId FROM EMPLOYEE;

Output:

+--------+
| DeptId |
+--------+
| D01 |

R
| D02 |
| D04 |

L
| D03 |

N
| D05 |
+--------+

EE
Without DISTINCT, repeated DeptIds would be shown for each employee.

H
AT
(E) Using WHERE Clause — To Filter Rows Purpose: The WHERE clause helps select only those
rows that satisfy a specific condition.
M

Query 1: Employees from Department D01

SELECT * FROM EMPLOYEE

ED

WHERE DeptId = 'D01';

Output:
M

+-------+----------+--------+-------+--------+
M

| EmpNo | Ename | Salary | Bonus | DeptId |

A

+-------+----------+--------+-------+--------+
| 102 | Kritika | 60000 | 123 | D01 |
H

| 103 | Shabbir | 45000 | 566 | D01 |

O

| 107 | Vergese | 15000 | NULL | D01 |

+-------+----------+--------+-------+--------+
M

Query 2: Employees from Department D02

SELECT * FROM EMPLOYEE

WHERE DeptId = 'D02';

Output:

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
23
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

+-------+--------+--------+-------+--------+
| EmpNo | Ename | Salary | Bonus | DeptId |
+-------+--------+--------+-------+--------+
| 101 | Aaliya | 10000 | 234 | D02 |
| 106 | Sanya | 48000 | 695 | D02 |
+-------+--------+--------+-------+--------+

R
Query 3: Employees with Salary > 25000

SELECT EmpNo, Ename, Salary

L
FROM EMPLOYEE
WHERE Salary > 25000;

N
Output:

EE
+-------+----------+--------+
| EmpNo | Ename | Salary |
H
AT
+-------+----------+--------+
| 102 | Kritika | 60000 |
| 103 | Shabbir | 45000 |
M

| 105 | Joseph | 34000 |

| 106 | Sanya | 48000 |
ED

| 108 | Nachaobi | 29000 |

| 109 | Daribha | 42000 |
M

| 110 | Tanya | 50000 |

+-------+----------+--------+
M

Query 4 Query: Display all details of employees from department D04 who earn more than 5000.
A

SELECT * FROM EMPLOYEE

H

WHERE Salary > 5000 AND DeptId = 'D04';

O

Output:
M

+-------+----------+--------+-------+--------+
| EmpNo | Ename | Salary | Bonus | DeptId |
+-------+----------+--------+-------+--------+
| 104 | Gurpreet | 19000 | 565 | D04 |
| 109 | Daribha | 42000 | NULL | D04 |
+-------+----------+--------+-------+--------+

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
24
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Query 5
Query: Display all employee records except Aaliya.

SELECT * FROM EMPLOYEE

WHERE NOT Ename = 'Aaliya';

Output:

+-------+----------+--------+-------+--------+

R
| EmpNo | Ename | Salary | Bonus | DeptId |

L
+-------+----------+--------+-------+--------+
| 102 | Kritika | 60000 | 123 | D01 |

N
| 103 | Shabbir | 45000 | 566 | D01 |
| 104 | Gurpreet | 19000 | 565 | D04 |

EE
| 105 | Joseph | 34000 | 875 | D03 |
| 106 | Sanya | 48000 | 695 | D02 |
| 107 | Vergese | 15000 | NULL | D01 |
H
AT
| 108 | Nachaobi | 29000 | NULL | D05 |
| 109 | Daribha | 42000 | NULL | D04 |
| 110 | Tanya | 50000 | 467 | D05 |
M

+-------+----------+--------+-------+--------+
ED

Query 6 Query: Employees earning salary between 20000 and 50000.

SELECT Ename, DeptId

M

FROM EMPLOYEE
WHERE Salary BETWEEN 20000 AND 50000;
M

Output:
A
H

+----------+--------+
| Ename | DeptId |
O

+----------+--------+
M

| Shabbir | D01 |
| Joseph | D03 |
| Sanya | D02 |
| Nachaobi | D05 |
| Daribha | D04 |
| Tanya | D05 |
+----------+--------+

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
25
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

You can also write:

WHERE Salary >= 20000 AND Salary <= 50000;

Query 7 Query: Employees from departments D01, D02, or D04 using OR:

SELECT * FROM EMPLOYEE

WHERE DeptId = 'D01' OR DeptId = 'D02' OR DeptId = 'D04';

Output:

R
L
+-------+----------+--------+-------+--------+
| EmpNo | Ename | Salary | Bonus | DeptId |

N
+-------+----------+--------+-------+--------+
| 101 | Aaliya | 10000 | 234 | D02 |

EE
| 102 | Kritika | 60000 | 123 | D01 |
| 103 | Shabbir | 45000 | 566 | D01 |
| 104 | Gurpreet | 19000 | 565 | D04 |
H
AT
| 106 | Sanya | 48000 | 695 | D02 |
| 107 | Vergese | 15000 | NULL | D01 |
| 109 | Daribha | 42000 | NULL | D04 |
M

+-------+----------+--------+-------+--------+
ED

(F) Using IN — Multiple Matching Values Purpose: The IN operator allows checking if a value
matches any value in a list.
M

Query: Employees from departments D01, D02, D04

M

SELECT * FROM EMPLOYEE

WHERE DeptId IN ('D01', 'D02', 'D04');
A

Output:
H
O

+-------+----------+--------+-------+--------+
M

| EmpNo | Ename | Salary | Bonus | DeptId |

+-------+----------+--------+-------+--------+
| 101 | Aaliya | 10000 | 234 | D02 |
| 102 | Kritika | 60000 | 123 | D01 |
| 103 | Shabbir | 45000 | 566 | D01 |
| 104 | Gurpreet | 19000 | 565 | D04 |
| 106 | Sanya | 48000 | 695 | D02 |

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
26
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

| 107 | Vergese | 15000 | NULL | D01 |

| 109 | Daribha | 42000 | NULL | D04 |
+-------+----------+--------+-------+--------+

IN is more concise than using multiple OR conditions.

Query: Select all employees except those working in departments D01 or D02.

SELECT * FROM EMPLOYEE

R
WHERE DeptId NOT IN ('D01', 'D02');

L
Output:

N
+-------+----------+--------+-------+--------+

EE
| EmpNo | Ename | Salary | Bonus | DeptId |
+-------+----------+--------+-------+--------+
| 104 | Gurpreet | 19000 | 565 | D04 |
| 105 | Joseph | 34000 | 875 | D03 |
H
AT
| 108 | Nachaobi | 29000 | NULL | D05 |
| 109 | Daribha | 42000 | NULL | D04 |
M

| 110 | Tanya | 50000 | 467 | D05 |

+-------+----------+--------+-------+--------+
ED

(G) Using IS NULL — Check for Missing Data Purpose: To check whether a column contains a
NULL (empty) value.
M

Query: Employees with no bonus

M

SELECT EmpNo, Ename, Bonus

FROM EMPLOYEE
A

WHERE Bonus IS NULL;

H

Output:
O

+-------+----------+-------+
M

| EmpNo | Ename | Bonus |

+-------+----------+-------+
| 107 | Vergese | NULL |
| 108 | Nachaobi | NULL |
| 109 | Daribha | NULL |
+-------+----------+-------+

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
27
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

You cannot use = NULL. Always use IS NULL or IS NOT NULL.

Query: Select names of employees with bonus and working in department D01.

SELECT EName FROM EMPLOYEE

WHERE Bonus IS NOT NULL
AND DeptId = 'D01';

Output:

R
L
+----------+
| EName |

N
+----------+

EE
| Kritika |
| Shabbir |
+----------+

H
AT
(H) Using LIKE — Pattern Matching Purpose: The LIKE operator is used to match text patterns
using wildcards:
M

• % → zero or more characters

• _ → exactly one character
ED

Query: Names that start with ‘S’

M

SELECT Ename FROM EMPLOYEE

WHERE Ename LIKE 'S%';
M

Output:
A

+----------+
H

| Ename |
O

+----------+
| Shabbir |
M

| Sanya |
+----------+

Query: Names that end with ‘a’

SELECT Ename FROM EMPLOYEE

WHERE Ename LIKE '%a';

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
28
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Output:

+----------+
| Ename |
+----------+
| Aaliya |
| Kritika |
| Sanya |

R
| Daribha |

L
| Tanya |
+----------+

N
EE
Query: Names containing ‘ya’

SELECT Ename FROM EMPLOYEE

WHERE Ename LIKE '%ya%';
H
AT
Output:

+--------+
M

| Ename |
+--------+
ED

| Sanya |
| Tanya |
M

+--------+
M

Query: Select employees whose name starts with ‘K’.

A

SELECT * FROM EMPLOYEE

WHERE Ename LIKE 'K%';
H

Output:
O
M

+-------+---------+--------+-------+--------+
| EmpNo | Ename | Salary | Bonus | DeptId |
+-------+---------+--------+-------+--------+
| 102 | Kritika | 60000 | 123 | D01 |
+-------+---------+--------+-------+--------+

Query: Select employees whose name ends with ‘a’ and earn salary > 45000.

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
29
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

SELECT * FROM EMPLOYEE

WHERE Ename LIKE '%a'
AND Salary > 45000;

Output:

+-------+--------+--------+-------+--------+
| EmpNo | Ename | Salary | Bonus | DeptId |

R
+-------+--------+--------+-------+--------+
| 102 | Kritika| 60000 | 123 | D01 |

L
| 106 | Sanya | 48000 | 695 | D02 |
| 110 | Tanya | 50000 | 467 | D05 |

N
+-------+--------+--------+-------+--------+

EE
Query: Select employees whose names are exactly 5 letters and have ‘ANYA’ starting from second
character.
H
AT
SELECT * FROM EMPLOYEE
WHERE Ename LIKE '_ANYA';
M

Output:
ED

+-------+-------+--------+-------+--------+
| EmpNo | Ename | Salary | Bonus | DeptId |
+-------+-------+--------+-------+--------+
M

| 106 | Sanya | 48000 | 695 | D02 |

| 110 | Tanya | 50000 | 467 | D05 |
M

+-------+-------+--------+-------+--------+
A
H

Purpose of ORDER BY The ORDER BY clause in SQL is used to sort the result set of a query by:
O

• One or more columns

• In ascending (ASC) or descending (DESC) order
M

If no order is specified, it defaults to ascending.

Basic Syntax:

SELECT column1, column2, ...

FROM table_name
ORDER BY column_name [ASC|DESC];

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
30
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Query – Sort by Salary (Ascending)

SELECT Ename, Salary

FROM EMPLOYEE
ORDER BY Salary;

Output:

+----------+--------+

R
| Ename | Salary |
+----------+--------+

L
| Aaliya | 10000 |

N
| Vergese | 15000 |
| Gurpreet | 19000 |

EE
| Joseph | 34000 |
| Nachaobi | 29000 |
| Shabbir | 45000 |
H
AT
| Daribha | 42000 |
| Sanya | 48000 |
| Tanya | 50000 |
M

| Kritika | 60000 |
+----------+--------+
ED

ORDER BY Salary sorts rows by salary increasingly.

M

Query – Sort by Salary (Descending)

M

SELECT Ename, Salary

FROM EMPLOYEE
A

ORDER BY Salary DESC;

H

Output:
O

+----------+--------+
M

| Ename | Salary |
+----------+--------+
| Kritika | 60000 |
| Tanya | 50000 |
| Sanya | 48000 |
| Shabbir | 45000 |
| Daribha | 42000 |

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
31
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

| Joseph | 34000 |
| Nachaobi | 29000 |
| Gurpreet | 19000 |
| Vergese | 15000 |
| Aaliya | 10000 |
+----------+--------+

DESC keyword sorts values from highest to lowest.

R
L
9.7 — Data Updation and Deletion

N
9.7.1 Data Updation Syntax:

EE
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

H
AT
Caution: Missing the WHERE clause will update all rows.

Query — Update GUID for Roll Number 3

M

The student with roll number 3 is a sibling of student 5, so assign same GUID.
ED

mysql> UPDATE STUDENT

-> SET GUID = 101010101010
-> WHERE RollNumber = 3;
M

Output:
M

Query OK, 1 row affected (0.06 sec)

A

Rows matched: 1 Changed: 1 Warnings: 0

H

You can verify with:

O

SELECT * FROM STUDENT;

M

Query — Update Multiple Columns in GUARDIAN Table

Change address and phone of guardian with GUID = 466444444666.

mysql> UPDATE GUARDIAN

-> SET GAddress = 'WZ - 68, Azad Avenue, Bijnour, MP',
-> GPhone = 9010810547
-> WHERE GUID = 466444444666;

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
32
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Output:

Query OK, 1 row affected (0.06 sec)

Rows matched: 1 Changed: 1 Warnings: 0

After Update:

mysql> SELECT * FROM GUARDIAN;

R
+--------------+---------------+------------+--------------------------------------+

L
| GUID | GName | GPhone | GAddress |

N
+--------------+---------------+------------+--------------------------------------+
| 444444444444 | Amit Ahuja | 5711492685 | G-35, Ashok Vihar, Delhi |

EE
| 111111111111 | Baichung B. | 7110047139 | Flat 5, Darjeeling Appt., Shimla |
| 101010101010 | Himanshu Shah | 9818184855 | 26/77, West Patel Nagar, Ahmedabad |
| 333333333333 | Danny Dsouza | NULL
| 466444444666 | Sujata P. H
| S -13, Ashok Village, Daman
| 9010810547 | WZ - 68, Azad Avenue, Bijnour, MP
|
|
AT
+--------------+---------------+------------+--------------------------------------+
M

9.7.2 Data Deletion Syntax:

ED

DELETE FROM table_name

WHERE condition;
M

Caution: Omitting WHERE will delete all records from the table.
M

Query — Delete Student with Roll Number 2

A

mysql> DELETE FROM STUDENT

-> WHERE RollNumber = 2;
H

Output:
O
M

Query OK, 1 row affected (0.06 sec)

After Deletion:

SELECT * FROM STUDENT;

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
33
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

+------------+--------------+--------------+--------------+
| RollNumber | SName | SDateofBirth | GUID |
+------------+--------------+--------------+--------------+
| 1 | Atharv Ahuja | 2003-05-15 | 444444444444 |
| 3 | Taleem Shah | 2002-02-28 | 101010101010 |
| 4 | John Dsouza | 2003-08-18 | 333333333333 |
| 5 | Ali Shah | 2003-07-05 | 101010101010 |
| 6 | Manika P. | 2002-03-10 | 466444444666 |

R
+------------+--------------+--------------+--------------+

L
9.8: Functions in SQL

N
EE
Introduction Functions in SQL are predefined commands that perform operations on data. They are
used for:

• Performing calculations
H
AT
• Modifying strings
• Extracting date components
• Summarizing data
M

SQL functions are categorized into:

ED

1. Single Row Functions (Scalar Functions): Operate on individual row values (return one result
per row)
2. Multiple Row Functions (Aggregate Functions): Work on groups of rows (return one result per
M

group)
M

Differences Between Single Row and Multiple Row Functions

A
H

Multiple Row Functions

Feature Single Row Functions (Aggregate)
O

Operate On Individual rows Groups of rows

M

Return One result per row One result per group

Used In SELECT, WHERE, ORDER BY SELECT, HAVING
Examples UCASE(), POWER(), ROUND(), COUNT(), SUM(), AVG(), MAX(),
DAY() MIN()

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
34
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Database Used: CARSHOWROOM Tables:

• CUSTOMER
• EMPLOYEE
• INVENTORY
• SALE

R
L
1. Single Row (Scalar) Functions

N
A. Math Functions

EE
Function Description Example Output

POWER(x,y) x raised to the power y

H
SELECT POWER(2,3); 8
AT
ROUND(n,d) Rounds n to d decimals SELECT ROUND(2912.564,1); 2912.6
MOD(a,b) Remainder of a ÷ b SELECT MOD(21, 2); 1
M

Examples with INVENTORY table:

ED

1. GST Calculation:
M

SELECT ROUND(12/100 * Price, 1) AS GST FROM INVENTORY;

M

2. EMI and Remaining Amount:

SELECT CarId, FinalPrice,

A

ROUND(FinalPrice - MOD(FinalPrice,1000)/10, 0) AS EMI,

H

MOD(FinalPrice, 10000) AS "Remaining Amount"

FROM INVENTORY;
O

3. Rounded Commission:
M

SELECT InvoiceNo, SalePrice, ROUND(Commission, 0)

FROM SALE;

B. String Functions

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
35
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Function Description Example Output

LCASE() Converts to lowercase SELECT LCASE(‘SQL’); sql

UCASE() Converts to uppercase SELECT UCASE(‘sql’); SQL
MID(str,p,n) Extract substring from position p, SELECT MID(‘Informatics’, 3, form
length n 4);
LENGTH(str)Returns string length SELECT LENGTH(‘SQL’); 3

R
LEFT(str,n) Leftmost n characters SELECT LEFT(‘Email’, 4); Emai

L
INSTR(s,sub)Position of substring in string SELECT 6

N
INSTR(‘Informatics’,‘ma’);

EE
TRIM() Remove leading/trailing/specific SELECT TRIM(‘.com’ FROM abc@gmail
chars ‘abc@[Link]’);

Examples with CUSTOMER table: H

AT
1. Convert name and email:
M

SELECT LOWER(CustName), UPPER(Email) FROM CUSTOMER;

2. Extract name from email:

ED

SELECT LENGTH(Email), LEFT(Email, INSTR(Email,"@") - 1) FROM CUSTOMER;

M

3. MID() to extract area code:

M

SELECT MID(Phone, 3, 4) FROM CUSTOMER WHERE CustAdd LIKE '%Rohini%';

4. TRIM domain suffix:

A

SELECT TRIM('.com' FROM Email) FROM CUSTOMER;

H
O

C. Date Functions
M

Function Description Example Output

NOW() Returns system date and time SELECT NOW(); 2019-07-11

DATE() Extracts date from datetime SELECT DATE(NOW()); 2019-07-11
DAY(date) Returns day of month SELECT DAY(‘2003-03-24’); 24

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
36
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Function Description Example Output

MONTH(date) Returns numeric month SELECT MONTH(‘2003-11-28’); 11

MONTHNAME()
Returns name of the month SELECT November
MONTHNAME(‘2003-11-28’);
YEAR(date) Returns year SELECT YEAR(‘2003-11-28’); 2003
DAYNAME(date)
Returns weekday name SELECT Thursday

R
DAYNAME(‘2019-07-11’);

L
N
Examples with EMPLOYEE table:

EE
1. DOJ components:

SELECT DAY(DOJ), MONTH(DOJ), YEAR(DOJ) FROM EMPLOYEE;

2. Formatted joining date: H

AT
SELECT DAYNAME(DOJ), DAY(DOJ), MONTHNAME(DOJ), YEAR(DOJ)
FROM EMPLOYEE WHERE DAYNAME(DOJ) != 'Sunday';
M

3. Day of birth:
ED

SELECT EmpName, DAYNAME(DOB) FROM EMPLOYEE WHERE Salary > 25000;

2. Multiple Row (Aggregate) Functions

M
M

Function Description Example Output

A

COUNT(*) Total row count SELECT COUNT(*) FROM 4

MANAGER;
H

COUNT(col) Count of non-NULL values SELECT COUNT(MEMNAME) 3

O

FROM MANAGER;
M

COUNT(DISTINCT)
Unique values SELECT COUNT(DISTINCT Model) 6
FROM INVENTORY;
SUM() Total of numeric column SELECT SUM(Price) FROM 4608733.00
INVENTORY;
AVG() Average of numeric column SELECT AVG(Price) FROM 576091.625
INVENTORY;

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
37
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Function Description Example Output

MAX() Highest value in column SELECT MAX(Price) FROM 673112.00

INVENTORY;
MIN() Lowest value in column SELECT MIN(Price) FROM 355205.00
INVENTORY;

R
Examples with INVENTORY/SALE tables:

L
1. Count cars with model VXI:

N
SELECT COUNT(*) FROM INVENTORY WHERE Model = 'VXI';

EE
2. Distinct models:

SELECT COUNT(DISTINCT Model) FROM INVENTORY;

3. Average price for LXI: H

AT
SELECT AVG(Price) FROM INVENTORY WHERE Model = 'LXI';
M

9.9 — GROUP BY Clause in SQL

ED

Purpose of GROUP BY The GROUP BY clause is used to:

• Aggregate rows with the same values in specified columns

M

• Perform operations like COUNT, SUM, AVG, etc. on groups

M

Basic Syntax:
A

SELECT column_name, AGGREGATE_FUNCTION(column_name)

H

FROM table_name
O

GROUP BY column_name;
M

Each column in the SELECT clause must either:

• Appear in the GROUP BY clause, or

• Be used inside an aggregate function

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
38
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

9.9.1 Examples Using GROUP BY Count Cars Sold Per Customer

SELECT CustID, COUNT(*) AS "Number of Cars"

FROM SALE
GROUP BY CustID;

Output:

+--------+------------------+

R
| CustID | Number of Cars |
+--------+------------------+

L
| C0001 | 2 |

N
| C0002 | 2 |
| C0003 | 1 |

EE
| C0004 | 1 |
+--------+------------------+

H
AT
Displays how many cars each customer bought

Count Distinct Employees Handling Sales Per Model

M

SELECT Model, COUNT(DISTINCT EmpID)

FROM SALE, INVENTORY
ED

WHERE [Link] = [Link]

GROUP BY Model;
M

Output:
M

+----------+---------------------------+
| Model | COUNT(DISTINCT EmpID) |
A

+----------+---------------------------+
H

| LXI | 2 |
| VXI | 1 |
O

+----------+---------------------------+
M

Combines two tables using JOIN condition (implicit syntax), then groups

Count Customers by Payment Mode

SELECT PaymentMode, COUNT(*)

FROM SALE
GROUP BY PaymentMode;

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
39
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Output:

+--------------+----------+
| PaymentMode | COUNT(*) |
+--------------+----------+
| Cheque | 1 |
| Credit Card | 1 |
| Online | 1 |

R
+--------------+----------+

L
Finds frequency of each payment type

N
Display Customers Who Bought More Than 1 Car

EE
SELECT CustID, COUNT(*)
FROM SALE
GROUP BY CustID
HAVING COUNT(*) > 1; H
AT
Output:
M

+--------+----------+
| CustID | COUNT(*) |
ED

+--------+----------+
| C0001 | 2 |
M

| C0002 | 2 |
+--------+----------+
M

Displays customers and number of cars bought, but only if more than 1 car was purchased.
A

Payment Modes Used More Than Once

H

SELECT PaymentMode, COUNT(PaymentMode)

O

FROM SALE
M

GROUP BY PaymentMode
HAVING COUNT(*) > 1
ORDER BY PaymentMode;

Output:

+--------------+--------------------+
| PaymentMode | Count(PaymentMode) |

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
40
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

+--------------+--------------------+
| Bank Finance | 2 |
| Credit Card | 2 |
+--------------+--------------------+

Filters only those payment modes used more than once, after grouping all SALE records by Payment-
Mode.

R
9.10 – Operations on Relations

L
Introduction SQL allows operations that involve multiple tables (relations). These are called set

N
operations and work when:

EE
• Both tables have the same number of columns
• Corresponding columns have the same data types (domain compatibility)

Types of Relational Operations H

AT
Operation Description
M

UNION ( � ) Combines rows from two tables, removing duplicates

ED

INTERSECT ( ∩ ) Returns rows common to both tables

MINUS ( - ) Returns rows in the first table that are not in the second
M

CARTESIAN All combinations of rows from two tables

PRODUCT ( × )
M
A

Tables Used for Examples Table: DANCE

H

SNo Name Class

O
M

1 Aastha 7A
2 Mahira 6A
3 Mohit 7B
4 Sanjay 7A

Table: MUSIC

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
41
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

SNo Name Class

1 Mehak 8A
2 Mahira 6A
3 Lavanya 7A
4 Sanjay 7A

R
5 Abhay 8A

L
9.10.1 – UNION ( ∪ ) Query:

N
SELECT * FROM DANCE

EE
UNION
SELECT * FROM MUSIC;

Output (DANCE ∪ MUSIC):

H
AT
SNo Name Class
M

1 Aastha 7A
2 Mahira 6A
ED

3 Mohit 7B
4 Sanjay 7A
M

1 Mehak 8A
M

3 Lavanya 7A
A

5 Abhay 8A
H
O

Combines students from both events without repeating common rows

M

9.10.2 – INTERSECT ( ∩ ) Query:

SELECT * FROM DANCE

INTERSECT
SELECT * FROM MUSIC;

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
42
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

Output (DANCE ∩ MUSIC):

SNo Name Class

2 Mahira 6A
4 Sanjay 7A

R
Finds students common to both DANCE and MUSIC

L
N
EE
9.10.3 – MINUS ( - ) Query:

SELECT * FROM MUSIC

MINUS
SELECT * FROM DANCE; H
AT
Output (MUSIC - DANCE):
M

SNo Name Class

ED

1 Mehak 8A
3 Lavanya 7A
M

5 Abhay 8A
M

Shows students participating only in MUSIC

A
H

9.10.4 – CARTESIAN PRODUCT ( × ) Query:

O

SELECT * FROM DANCE, MUSIC;

M

Output (DANCE × MUSIC):

• Degree (columns): 6
• Cardinality (rows): 4 × 5 = 20 rows

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
43
STUDENT NOTES || CHAPTER 9 STRUCTURED QUERY LANGUAGE (SQL) April 23, 2025

SNo Name Class SNo Name Class

1 Aastha 7A 1 Mehak 8A
1 Aastha 7A 2 Mahira 6A
… … … … … …
4 Sanjay 7A 5 Abhay 8A

R
Generates all possible combinations of students from both tables

L
N
For more information Visit:

EE
[Link]

H
AT
M
ED
M
M
A
H
O
M

L R MOHAMMED MATHEEN M.C.A., M.A., [Link]., UGC NET.,

LECTURER, PRIMUS PU COLLEGE, BANGALORE - 560 035
44