MSBTE K-Scheme
Unit III – Interactive SQL and Performance Tuning
Prepared as detailed study material for students.
3.1 SQL (Structured Query Language)
SQL (Structured Query Language) is the standard language to interact with relational database
management systems (RDBMS). It enables defining database structures, manipulating data,
controlling access, and ensuring data consistency.
SQL Data Types
Different categories of SQL data types are given below:
Category Examples / Description
Numeric INT, SMALLINT, DECIMAL(p,s), FLOAT
Character CHAR(n), VARCHAR(n), TEXT
Date & Time DATE, TIME, DATETIME, TIMESTAMP
Boolean TRUE / FALSE
SQL Sub-Languages
SQL is divided into four categories: DDL, DML, DCL, and TCL.
1. DDL – Data Definition Language
Command Purpose
CREATE Create objects (tables, views, indexes)
ALTER Modify structure of tables
DROP Remove objects permanently
TRUNCATE Remove all records from table
RENAME Rename objects
2. DML – Data Manipulation Language
Command Purpose
INSERT Add new records
UPDATE Modify existing records
DELETE Remove records
SELECT Retrieve records
3.2 Clauses & Joins
SQL clauses are used to filter, group, and order records in queries. Joins are used to combine data
from multiple tables based on relationships.
Clause Purpose / Example
WHERE Filter rows. Example: SELECT * FROM Student WHERE Marks > 60;
GROUP BY Group rows. Example: SELECT Dept, COUNT(*) FROM Employee GROUP BY Dept;
HAVING Filter groups. Example: SELECT Dept, AVG(Salary) FROM Employee GROUP BY Dept HAV
ORDER BY Sort rows. Example: SELECT * FROM Employee ORDER BY Salary DESC;
Types of Joins:
Join Type Description
INNER JOIN Only matching rows from both tables.
LEFT JOIN All rows from left + matching from right.
RIGHT JOIN All rows from right + matching from left.
FULL OUTER JOIN All rows from both tables.
CROSS JOIN Cartesian product of both tables.
SELF JOIN Table joined with itself.
3.3 Operators
Type Operators
Relational =, <>, >, <, >=, <=
Arithmetic +, -, *, /, %
Logical AND, OR, NOT
Set UNION, UNION ALL, INTERSECT, MINUS
3.4 Functions
SQL provides numeric, string, date, and aggregate functions for processing data.
Category Examples
Numeric ABS(), ROUND(), CEIL(), FLOOR(), POWER(), MOD()
String CONCAT(), SUBSTRING(), LENGTH(), UPPER(), LOWER(), TRIM(), REPLACE()
Date/Time NOW(), CURDATE(), DATEDIFF(), ADDDATE()
Aggregate COUNT(), SUM(), AVG(), MAX(), MIN()
3.5 Views, Sequences, and Indexes
Views: Virtual tables created from queries. They provide simplicity and security.
Sequences: Database objects that generate unique numbers, used for IDs.
Indexes: Data structures that improve query performance. Types include single-column,
composite, unique, clustered, and non-clustered.
Type Description
Single-column Index on one column
Composite Index on multiple columns
Unique No duplicate values allowed
Clustered Physically sorts table rows
Non-clustered Logical sorting, table remains unchanged
Quick Revision Sheet – Unit III
• SQL = Standard language for RDBMS.
• Sub-languages: DDL, DML, DCL, TCL.
• Clauses: WHERE, GROUP BY, HAVING, ORDER BY.
• Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF.
• Operators: Relational, Arithmetic, Logical, Set.
• Functions: Numeric, String, Date/Time, Aggregate.
• Views: Virtual tables.
• Sequences: Generate unique numbers.
• Indexes: Improve search performance.
