Unit - III Interactive SQL and

Performance Tuning
Subject : DMS.
 SQL Basics
•SQL (Structured Query Language) is the standard language used to
communicate with relational databases.
•Purpose: To create, read, update, and delete data efficiently.
Four Main Categories of SQL Commands:
[Link] – Data Definition Language
•Used to define or modify database structure.
•Examples: CREATE, ALTER, DROP, TRUNCATE
[Link] – Data Manipulation Language
•Used to work with the data inside tables.
•Examples: SELECT, INSERT, UPDATE, DELETE
[Link] – Data Control Language
•Used to control access and permissions for users.
•Examples: GRANT, REVOKE
[Link] – Transaction Control Language
•Used to manage transactions and maintain data consistency.
•Examples: COMMIT, ROLLBACK, SAVEPOINT
Data Types
•SQL Data Types define the type of data that can be stored in a table column.
•Choosing the right data type ensures efficient storage and accurate data operations.
Main Categories:
[Link] Data Types – Store numbers
•INT – Whole numbers (e.g., 10, 250)
•FLOAT / REAL – Decimal numbers (e.g., 3.14)
•DECIMAL / NUMERIC – Precise fixed-point numbers
[Link] Data Types – Store text
•CHAR(n) – Fixed-length text (e.g., CHAR(5) stores exactly 5 characters)
•VARCHAR(n) – Variable-length text (e.g., VARCHAR(50) stores up to 50
characters)
[Link] & Time Data Types – Store dates and times
•DATE – Only date (YYYY-MM-DD)
•TIME – Only time (HH:MM:SS)
•DATETIME / TIMESTAMP – Date and time together
[Link] Data Types – Special types
•BOOLEAN – True or False values
•BLOB – Binary large objects (images, files)
•CLOB – Character large objects (large text)
 SQL Command Categories

•SQL commands are divided into four main categories, each serving a specific
purpose:
[Link] – Data Definition Language
•Defines or modifies the structure of databases and tables.
•Examples:
•CREATE TABLE – Create a new table
•ALTER TABLE – Modify table structure
•DROP TABLE – Delete a table
•TRUNCATE TABLE – Remove all data but keep the table
2. DML – Data Manipulation Language
•Used to insert, update, delete, or retrieve data from tables.
•Examples:
•SELECT – Retrieve data
•INSERT – Add new records
•UPDATE – Modify existing records
•DELETE – Remove records

3. DCL – Data Control Language

•Manages permissions and access control for database users.
•Examples:
•GRANT – Give privileges to users
•REVOKE – Remove privileges
4. TCL – Transaction Control Language
•Ensures data consistency and manages transactions.
•Examples:
•COMMIT – Save changes permanently
•ROLLBACK – Undo changes
•SAVEPOINT – Set a point to rollback to
 SQL Clauses
1. WHERE Clause
•Filters rows based on specified conditions.
•Can be used with SELECT, UPDATE, DELETE.
•Supports comparison operators (=, <>, >, <, >=, <=) and logical operators (AND, OR,
NOT).
•Example:
SELECT * FROM Employees WHERE Department = 'Sales';
Retrieves only employees in the Sales department.

2. GROUP BY Clause
•Groups rows that have the same values in specified columns.
•Often used with aggregate functions like COUNT(), SUM(), AVG().
•Example:
SELECT Department, COUNT(*) AS EmployeeCount FROM Employees GROUP BY
Department;
Counts employees in each department.
3. ORDER BY Clause
•Sorts the result set ascending (ASC) or descending (DESC).
•Can sort by one or more columns.
•Example:
SELECT * FROM Employees ORDER BY Salary DESC;
Shows employees sorted by salary from highest to lowest.

4. HAVING Clause
•Filters groups of records after aggregation.
•Similar to WHERE but applied on grouped data.
•Example:
SELECT Department, AVG(Salary) AS AvgSalary FROM Employees GROUP BY
Department HAVING AVG(Salary) > 50000;
Shows departments where the average salary is greater than 50,000.
 SQL Joins
Joins are used to combine rows from two or more tables based on a related
column.
[Link] Join
•Returns only the common records that exist in both tables.
•Example:
SELECT [Link], [Link] FROM Employees INNER
JOIN Departments ON [Link] = [Link];
Shows only employees who belong to a department.
[Link] Join (Left Outer Join)
•Returns all records from the left table and the matching records from the
right table.
•If there is no match, NULL is returned for right table columns.
•Example:
SELECT [Link], [Link] FROM Employees LEFT
JOIN Departments ON [Link] = [Link];
Shows all employees, even if they don’t belong to any department.
3. Right Join (Right Outer Join)
•Returns all records from the right table and the matching records from the left
table.
•If there is no match, NULL is returned for left table columns.
•Example:
SELECT [Link], [Link] FROM Employees RIGHT
JOIN Departments ON [Link] = [Link];
Shows all departments, even if no employee is assigned.

4. Full Outer Join

•Returns all records from both tables, with NULLs where there is no match.
•Example:
SELECT [Link], [Link] FROM Employees FULL
OUTER JOIN Departments ON [Link] = [Link];
Shows all employees and all departments, matching where possible.
 Nested Queries (Subqueries)
•Nested Query (Subquery): A query inside another SQL query.
•Used to compute intermediate results that are then used by the main query.
•Can return:
•Single value – used in expressions or comparisons
•Multiple rows or a table – used with IN, EXISTS, or joins
Types of Nested Queries:
[Link] Subquery
•Returns a single value.
•Example:
SELECT Name, Salary FROM Employees WHERE Salary > (SELECT
AVG(Salary) FROM Employees);
Finds employees earning more than the average salary.
[Link]-row Subquery
•Returns multiple rows, often used with IN or ANY.
•Example:
SELECT Name FROM Employees WHERE DeptID IN (SELECT DeptID FROM
Departments WHERE Location = 'Mumbai');
Finds employees working in departments located in Mumbai.
3. Correlated Subquery
•The subquery depends on the outer query for its values.
•Executed once per row of the outer query.
•Example:
SELECT Name, Salary FROM Employees E1 WHERE Salary > (SELECT
AVG(Salary) FROM Employees E2 WHERE [Link] = [Link]);
Finds employees earning more than the average salary in their own
department.
SQL Operators
SQL operators are used to perform operations on data in queries. They can be
classified into four main types:
[Link] Operators
•Used to compare values.
•Examples:
•= : Equal to
•<> or != : Not equal to
•> : Greater than
•< : Less than
•>= : Greater than or equal to
•<= : Less than or equal to
•Example:
SELECT * FROM Employees WHERE Salary > 50000;
Retrieves employees with salary greater than 50,000.
2. Arithmetic Operators
•Used to perform mathematical operations.
•Examples: + (Addition), - (Subtraction), * (Multiplication), / (Division), % (Modulo)
•Example:
SELECT Salary, Salary * 0.1 AS Bonus FROM Employees;
Calculates 10% bonus for each employee.

3. Logical Operators
•Combine multiple conditions in a query.
•Examples:
•AND : Both conditions must be true
•OR : At least one condition must be true
•NOT : Negates a condition
•Example:
SELECT * FROM Employees WHERE Department = 'Sales' AND Salary > 50000;
4. Set Operators
•Combine results from multiple queries.
•Examples:
•UNION : Combines results and removes duplicates
•INTERSECT : Returns common rows between queries
•MINUS (or EXCEPT) : Returns rows in first query but not in second
•Example:
SELECT Name FROM Employees_2023 UNION SELECT Name FROM
Employees_2024;
Lists unique employee names from both years.
SQL Functions
SQL functions are used to perform operations on data and return a value.
They are grouped into several types:
[Link] Functions
•Perform operations on numbers.
•Examples:
•ROUND(number, decimals) – Rounds a number to specified
decimals
•CEIL(number) – Rounds a number up to the nearest integer
•FLOOR(number) – Rounds a number down to the nearest integer
•Example:
SELECT ROUND(123.456, 2) AS RoundedValue;
Result: 123.46
2. Date & Time Functions
•Work with dates and times.
•Examples:
•NOW() – Current date and time
•SYSDATE() – System date
•DATEADD(interval, value, date) – Adds a specific interval to a date
•Example:
SELECT NOW() AS CurrentDateTime;

3. String Functions
•Manipulate text data.
•Examples:
•CONCAT(str1, str2) – Joins two strings
•LENGTH(str) – Returns length of string
•SUBSTR(str, start, length) – Extracts substring
•UPPER(str) – Converts text to uppercase
•Example:
SELECT CONCAT('John', ' Doe') AS FullName;
Result: John Doe
4. Aggregate Functions
•Perform calculations on multiple rows and return a single value.
•Examples:
•COUNT(column) – Counts rows
•SUM(column) – Total of values
•AVG(column) – Average of values
•MAX(column) – Maximum value
•MIN(column) – Minimum value
•Example:
SELECT AVG(Salary) AS AverageSalary FROM Employees;
Views in SQL
•Concept:
•A view is a virtual table based on the result of a SELECT query.
•It does not store data physically but displays data from one or more
tables.
•Helps in simplifying complex queries, restricting access, and
improving readability.
•Create a View:
CREATE VIEW EmployeeView AS SELECT Name, Department,
Salary FROM Employees WHERE Salary > 50000;
Creates a view showing employees with salary above 50,000.
•Update a View:
ALTER VIEW EmployeeView AS SELECT Name, Department, Salary,
JoiningDate FROM Employees WHERE Salary > 50000;
Modifies the view to include the joining date column.

•Drop a View:
DROP VIEW EmployeeView;
Deletes the view from the database.
Sequences in SQL

•Concept:
•A sequence is a database object that generates unique, auto-incremented
numeric values.
•Commonly used to auto-generate primary key values for tables.
•Create a Sequence:
CREATE SEQUENCE seq_employee START WITH 1 INCREMENT BY 1;
Starts from 1 and increments by 1 each time.
•Alter a Sequence:
ALTER SEQUENCE seq_employee INCREMENT BY 5;
Changes the increment value to 5.
•Drop a Sequence:
DROP SEQUENCE seq_employee;
Deletes the sequence from the database.
•Use a Sequence in a Table:
INSERT INTO Employees(EmployeeID, Name) VALUES
(seq_employee.NEXTVAL, 'John Doe');
Inserts a new row using the next value from the sequence as EmployeeID.
Indexes in SQL
•Concept:
•An index is a database object that improves the speed of data
retrieval.
•Works like an index in a book: instead of scanning the entire table, the
database uses the index to quickly locate rows.
•Types of Indexes with Examples:
[Link]-column Index
CREATE INDEX idx_employee_name ON Employees(Name);
Creates an index on the Name column to speed up searches by Name.

[Link] Index (Multi-column)

CREATE INDEX idx_employee_dept ON Employees(Department, Salary);
Creates an index on both Department and Salary columns for faster queries
using both.
[Link] Index
CREATE UNIQUE INDEX idx_unique_email ON Employees(Email);
Ensures all Email values are unique and improves search performance.

[Link]-text Index (for text searching in large text columns)

CREATE FULLTEXT INDEX idx_employee_resume ON Employees(ResumeTe
Optimizes search for keywords in the ResumeText column.

•Drop an Index:
DROP INDEX idx_employee_name;
Deletes the index from the database.