Introduction to SQL
SQL (Structured Query Language) is the standard language used to interact with
relational database management systems (RDBMS) such as MySQL, PostgreSQL,
Oracle, and SQL Server. It enables users to define, manipulate, and control data
efficiently.
SQL is broadly classified into three main categories:
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
Data Definition Language (DDL)
DDL is used to define and manage the structure of database objects such as tables, schemas, and indexes.
Key Commands:
• CREATE – Creates database objects
• ALTER – Modifies existing objects
• DROP – Deletes objects permanently
• TRUNCATE – Removes all records from a table (faster than DELETE)
CREATE TABLE Student (
id INT,
name VARCHAR(50),
marks INT
);
ALTER TABLE Student
ADD department VARCHAR(50);
DROP TABLE Student;
TRUNCATE TABLE Student;
Data Manipulation Language (DML)
DML is used for managing data within tables.
Key Commands:
• INSERT – Adds new records
• UPDATE – Modifies existing records
• DELETE – Removes records
• SELECT – Retrieves data (sometimes categorized separately as DQL)
INSERT INTO Student (id, name, marks)
VALUES
(2, 'Anita', 90),
(3, 'Kiran', 78);
UPDATE Student
SET name = Anitha
WHERE id = 2;
DELETE FROM Student
WHERE id = 2;
SELECT * FROM Student;
Data Control Language (DCL)
DCL is used to control access to data in the database.
Key Commands:
• GRANT – Gives privileges to users
• REVOKE – Removes privileges
GRANT SELECT ON Student TO user1;
GRANT ALL PRIVILEGES ON Student TO harshitha;
REVOKE INSERT ON Student FROM user1;
REVOKE ALL PRIVILEGES ON Student FROM user1;
Aggregate Functions in SQL
Aggregate functions perform calculations on a set of values and return a single result.
Common Aggregate Functions:
• COUNT() – Number of rows
• SUM() – Total sum
• AVG() – Average value
• MIN() – Minimum value
• MAX() – Maximum value
SELECT COUNT(*) FROM Student;
SELECT COUNT(*)
FROM Student
WHERE marks > 80;
SELECT SUM(marks) FROM Student;
SELECT AVG(marks) FROM Student;
SELECT MIN(marks) FROM Student;
SELECT MAX(marks) FROM Student;
GROUP BY Clause
GROUP BY is used to group rows that have the same values in specified columns into summary rows.
Example:
SELECT department, AVG(marks) AS avg_marks
FROM Student
GROUP BY department;
HAVING Clause
HAVING is used to filter groups after aggregation (unlike WHERE, which filters rows before grouping).
Example:
SELECT department, AVG(marks) AS avg_marks
FROM Student
GROUP BY department
HAVING AVG(marks) > 80;
Joins in SQL
Joins are used to combine data from two or more tables based on a related column.
a) Equi Join
An equi join is a type of inner join where the condition uses equality ( ).
=
Example:
SELECT [Link], d.department_name
FROM Student s
JOIN Department d
ON s.department_id = d.department_id;
Self Join
A self join is when a table is joined with itself.
Example:
SELECT [Link] AS employee, [Link] AS manager
FROM Employee e1
JOIN Employee e2
ON e1.manager_id = e2.employee_id;
Outer Joins
Outer joins include unmatched rows along with matched rows.
Types:
• LEFT OUTER JOIN – All rows from left table + matching rows
• RIGHT OUTER JOIN – All rows from right table + matching rows
• FULL OUTER JOIN – All rows from both tables
Example:
SELECT [Link], d.department_name
FROM Student s
LEFT JOIN Department d
ON s.department_id = d.department_id;
Subqueries (Nested Queries)
A subquery is a query inside another SQL query.
Types:
• Single-row subquery
• Multi-row subquery
• Scalar subquery
Example:
SELECT name
FROM Student
WHERE marks > (SELECT AVG(marks) FROM Student); / > 65;
Correlated Subqueries
A correlated subquery depends on values from the outer query and executes repeatedly.
Example:
SELECT [Link]
FROM Student s1
WHERE marks >
(
SELECT AVG(marks)
FROM Student s2
WHERE [Link] = [Link])
Views
A view is a virtual table based on a SQL query.
Example:
CREATE VIEW HighScorers AS high_score
SELECT name, marks
FROM Student
WHERE marks > 80;
Sequences
A sequence is a database object used to generate unique numbers.
Example (Oracle/PostgreSQL):
CREATE SEQUENCE student_seq
START WITH 1
INCREMENT BY 1;
INSERT INTO Student(student_id, name)
VALUES (student_seq.NEXTVAL, 'Rahul');
Indexes
Indexes improve query performance by enabling faster data retrieval.
Example:
CREATE INDEX idx_student_name
ON Student(name);
Types of Indexes:
• Primary Index
• Secondary Index
• Composite Index
• Unique Index
• A variable in c
• *A ——- > address of variable A
Synonyms
A synonym is an alias for a database object.
Example:
CREATE SYNONYM stu FOR Student;
Nested Queries (Advanced Perspective)
Nested queries can appear in:
• SELECT clause
• FROM clause (inline views)
• WHERE clause
Example (FROM clause):
SELECT dept, avg_marks
FROM (
SELECT department AS dept, AVG(marks) AS avg_marks
FROM Student
GROUP BY department
) AS dept_avg
WHERE avg_marks > 75;
Assertions in SQL
Assertions are database-level constraints that enforce conditions across multiple tables.
Syntax:
CREATE ASSERTION assertion_name
CHECK (condition);
Example:
CREATE ASSERTION max_salary_check
CHECK (
(SELECT MAX(salary) FROM Employee) < 1,00,000
);
Cursors
A cursor is a pointer used to process query results row-by-row.
Steps:
1. Declare cursor
2. Open cursor
3. Fetch rows
4. Close cursor
DECLARE
CURSOR emp_cursor IS SELECT name FROM Employee;
emp_name [Link]%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_name);
END LOOP;
CLOSE emp_cursor;
END;
Triggers
Triggers are automatically executed procedures in response to events.
Types:
• BEFORE / AFTER triggers
• INSERT / UPDATE / DELETE triggers
Example:
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON Employee
FOR EACH ROW
BEGIN
:NEW.last_modi ed := SYSDATE;
END;
fi
Stored Procedures
Stored procedures are precompiled SQL programs stored in the database.
Example:
CREATE PROCEDURE GetHighSalaryEmployees()
BEGIN
SELECT * FROM Employee WHERE salary > 50000;
END;
Embedded SQL
Embedded SQL integrates SQL statements into a host programming language
(like C, Java).
Dynamic SQL
Dynamic SQL allows execution of SQL statements constructed at runtime.
Window Functions
These help you do calculations across rows without grouping them.
Example: Finding rank of students without losing individual data
SELECT name, marks,
RANK() OVER (ORDER BY marks DESC) AS rank
FROM Student;
2. Common Table Expressions (CTE)
CTE is like a temporary table you create inside a query to make it easier to read.
Example:
WITH AvgMarks AS (
SELECT AVG(marks) AS avg_val FROM Student
)
3. Recursive Queries
Used when data is in levels (like manager → employee structure).
Example:
• Company hierarchy
• Folder structure
Helps solve problems where data refers to itself.