What is SQL?

SQL (Structured Query Language) is used to store, manage, and retrieve data in a
database.
Uses:
• Store data
• Retrieve data
• Update data
• Delete data

What is DBMS?

DBMS (Database Management System) is software used to store and manage

data.

What is RDBMS?
RDBMS stores data in tables with rows and columns and maintains
relationships.
Examples: SQL Server, MySQL, Oracle.

What Can SQL do?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views
 SQL COMMAND TYPES

DDL (Data Definition Language)

• Used to define structure of tables.

Command Meaning
CREATE Create new table/database
ALTER Modify table
DROP Delete table/database
TRUNCATE Delete all rows (cannot rollback)

DML (Data Manipulation Language)

• Used for managing and manipulating data within a database.

Command Meaning
INSERT Insert data
UPDATE Update data
DELETE Delete data

DQL (Data Query Language)

• Specifically dedicated to retrieving and querying data from a database.
• Is typically presented as a result set, which can be viewed or processed by
applications.

Command Meaning
SELECT Retrieve data

DCL (Data Control Language)

 • Used to manage and control access permissions and privileges within a
database system.
Command Meaning
GRANT Give access
REVOKE Remove access
• It primarily deals with the security aspect of the database, determining who
can perform what actions on specific database objects.

TCL (Transaction Control Language)

• These commands are used to manage transactions within a database,
ensuring data integrity and consistency.
• A transaction is a logical unit of work that comprises one or more SQL
statements, and it either completes entirely or is rolled back completely if an
error occurs.

Command Meaning
COMMIT Save changes
ROLLBACK Undo
SAVEPOINT Create a rollback point
CREATE DATABASE
Creates a new container to store tables.

SYNTAX:
CREATE DATABASE database_name;

CREATE TABLE
A table stores data in rows and columns.

SYNTAX:
CREATE TABLE table_name (
column_name datatype,
column_name datatype
);

INSERT DATA
Used to add/insert new rows inside the table.

SYNTAX:
INSERT INTO table_name VALUES (value1, value2, ...);
INSERT INTO table_name VALUES (value1, value2, ...);

SELECT DATA
Used to view/read data from the table.

SYNTAX:
SELECT * FROM table_name; ; -- View all rows
SELECT Name, City FROM Students; -- View specific columns
SELECT * FROM Students WHERE City = 'Chennai'; -- Filter rows

UPDATE DATA
Used to modify/change existing records.

SYNTAX:
UPDATE table_name
SET column = value
WHERE condition;

DELETE DATA
Delete a specific row (based on condition).

SYNTAX:
DELETE FROM table_name
WHERE condition;

TRUNCATE TABLE
Deletes all rows at once (faster than DELETE), cannot be rolled back.

SYNTAX:
TRUNCATE TABLE table_name;

DROP TABLE
Completely removes the table structure from the database.

SYNTAX:
DROP TABLE table_name;

ALTER TABLE
Used to modify table structure.

Add a Column

SYNTAX:
ALTER TABLE table_name
ADD column_name datatype;

Insert Email Values

SYNTAX:
UPDATE Students SET Email = 'rahul@[Link]' WHERE StudentID = 1;
UPDATE Students SET Email = 'priya@[Link]' WHERE StudentID = 2;
UPDATE Students SET Email = 'karthik@[Link]' WHERE StudentID = 3;

Modify a Column

SYNTAX:
ALTER TABLE table_name
ALTER COLUMN column_name new_datatype;

Drop a Column
SYNTAX:

ALTER TABLE table_name

DROP COLUMN column_name;

OPERATOR IN SQL

An operator is a symbol or keyword that tells SQL how to manipulate or compare

values (math, comparison, logical, string operations, etc.).

ARITHMETIC OPERATORS

Used to perform mathematical calculations.

• Operators: + (add), - (subtract), * (multiply), / (divide), % (modulo,
remainder)

SYNTAX:

SELECT Age + 1 AS AgeNextYear FROM table_name;

SELECT StudentID % 2 AS IsEven FROM table_name ; -- modulo
SELECT Age * 2 AS DoubleAge FROM table_name;
SELECT Age / 2.0 AS HalfAge FROM table_name; -- use decimal to avoid
integer division

COMPARISON OPERATORS

Used to compare two values and return boolean (true/false).

SYNTAX:
SELECT * FROM table_name WHERE Age = 20;
SELECT * FROM table_name WHERE Age <> 21;
SELECT * FROM table_name s WHERE Age > 20;
SELECT * FROM table_name WHERE Age >= 21;

LOGICAL OPERATORS
Combine multiple conditions: AND, OR, NOT.
SYNTAX:

SELECT * FROM table_name WHERE City = 'Chennai' AND Age > 20;
SELECT * FROM table_name WHERE City = 'Chennai' OR City = 'Madurai';
SELECT * FROM table_name WHERE NOT City = 'Chennai';

BETWEEN ... AND (Range test)

Checks if a value lies within a range

SYNTAX:

SELECT * FROM table_name WHERE Age BETWEEN 20 AND 22;

IN / NOT IN (List membership)
Tests whether a value equals any value in a list.
SYNTAX:

SELECT * FROM table_name WHERE City IN ('Chennai', 'Madurai');

SELECT * FROM table_name WHERE City NOT IN ('Chennai', 'Madurai');

LIKE / NOT LIKE (Pattern matching)

Match strings using wildcards.

SYNTAX:

SELECT * FROM table_name WHERE Name LIKE 'K%'; -- starts with K

SELECT * FROM table_name WHERE Name LIKE '%a'; -- ends with 'a'
SELECT * FROM table_name WHERE Name LIKE '%ar%'; -- contains 'ar'
AGGREGATE FUNCTIONS
COUNT; number of rows (or non-null values).
Syntax: COUNT (*), COUNT (column), COUNT (DISTINCT column).

SUM; sum of numeric column values.

Syntax: SUM (column)

AVG; arithmetic average of numeric column.

Syntax: AVG (column)

MIN / MAX; smallest / largest value in column.

Syntax: MIN (column), MAX (column)

GROUP BY
GROUP BY collects rows with the same value(s) in grouping column(s);
aggregates compute one value per group.
Syntax:
SELECT group_col, AGG(column) AS Alias
FROM table GROUP BY group_col;

JOINS
A JOIN is used to combine rows from two or more tables based on a related
column.

Why JOIN is needed?

• When data is stored in multiple tables
• To analyze combined information
• To avoid duplicate data
• To maintain a clean database structure (Normalization)

Types of JOINS
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN

INNER JOIN
Returns only matching rows from both tables.
Syntax:
SELECT columns
FROM table1 INNER JOIN table2
ON table1.common_column = table2.common_column;

LEFT JOIN (LEFT OUTER JOIN)

 • All rows from LEFT table
• Matching rows from RIGHT table
• If no match → RIGHT table columns become NULL

Syntax:
SELECT columns
FROM table1 LEFT JOIN table2
ON table1.common_column = table2.common_column;

RIGHT JOIN (RIGHT OUTER JOIN)

• All rows from RIGHT table
• Matching rows from LEFT
• If no match → LEFT table columns become NULL
Syntax:
SELECT columns
FROM table1 RIGHT JOIN table2
ON table1.common_column = table2.common_column;

FULL OUTER JOIN

• All rows from both tables
• Matching rows + non-matching rows
• Non-matching entries filled with NULL
Syntax:
SELECT columns
FROM table1 FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;
 WINDOW FUNCTIONS

A Window Function performs a calculation over a set of rows (window) without

grouping them into a single row.
• Unlike GROUP BY, window functions do NOT collapse rows.
• They add extra calculated columns while keeping all rows intact .

Window Functions are used?

• Ranking
• Row numbering
• Running totals
• Moving averages
• Percentile, cumulative sum
• Partition-wise analysis
• Reports, dashboards, analytics

General Syntax (Important)

function_name (column)
OVER ( PARTITION BY column(s) ORDER BY column(s) )
• PARTITION BY = GROUP BY (but without reducing rows)
• ORDER BY = Sorting inside each partition

WINDOW FUNCTION TYPES

• ROW_NUMBER
• RANK
• DENSE_RANK
• LEAD
• LAG
ROW_NUMBER ()
Gives a unique number (1,2,3...) to each row within a partition.
Syntax:
ROW_NUMBER () OVER (PARTITION BY column ORDER BY column)

RANK ()
Gives rank with gaps when values tie.
Marks: 92, 90, 90,90, 85
Ranks: 1, 2, 2, 2,5
Syntax:
RANK () OVER (PARTITION BY column ORDER BY column DESC)

DENSE_RANK ()
Similar to RANK but no gaps.
Marks: 92, 90, 90, 85
Dense Rank: 1, 2, 2, 3
Syntax:
DENSE_RANK () OVER (PARTITION BY column ORDER BY column DESC)

LEAD ()
Returns the next row’s value.
Useful for: Comparing current vs next student score.
Syntax:
LEAD(column, offset) OVER (ORDER BY column)
CTE (Common Table Expression)
A CTE is a temporary result set you create using the WITH keyword.
It exists only for that single query.
It is mainly used to:
• Simplify complex queries
• Improve readability
• Use result multiple times in one query
• Replace subqueries

Syntax:
WITH DeptAvg AS (
SELECT Department, AVG(Marks) AS AvgMarks
FROM Students4
GROUP BY Department
)
SELECT *
FROM DeptAvg;

STORED PROCEDURE
A Stored Procedure is a saved SQL code that you can run anytime.
Purpose:
• Store logic permanently
• Run multiple times
• Reduce repetitive work
• Improve performance

Syntax:
CREATE PROCEDURE ProcedureName
AS
BEGIN
SQL statements
END;
To execute:
EXEC ProcedureName;

VIEWS
A View is a virtual table created from SQL SELECT queries.
It does not store data, but displays data from tables.
Use cases:
• Hide sensitive columns
• Simplify complex joins
• Provide read-only access
• Reusable SELECT queries

Syntax:
CREATE VIEW ViewName AS
SELECT columns
FROM table_name
WHERE condition;
To see data :
SELECT * FROM ViewName;
 Constraint Meaning Perfect Example Wrong Example
PRIMARY KEY Unique + Not Null Insert ID 1 Insert ID 1 again
NOT NULL Cannot be empty Name='Priya' Name=NULL
CHECK Must satisfy condition Age = 19 Age = 16
UNIQUE No duplicates New Email Email already exists
DEFAULT Auto value City auto = Chennai No wrong example
Function Meaning Simple Example
GETDATE() Current date & time SELECT GETDATE();

CURRENT_TIMESTAMP Same as GETDATE SELECT CURRENT_TIMESTAMP;

DATEPART() Returns number part DATEPART(HOUR, LoginTime)

DATENAME() Returns text part DATENAME(WEEKDAY, LoginTime)

DAY() Day number DAY(LoginTime)

MONTH() Month number MONTH(LoginTime)

YEAR() Year number YEAR(LoginTime)

DATEDIFF() Difference between dates DATEDIFF(HOUR, LoginTime, LogoutTime)

DATEADD() Add time DATEADD(DAY, 5, LoginTime)

CONVERT() Change date format CONVERT(VARCHAR, LoginTime, 105)

CAST() Convert datatype CAST(LoginTime AS DATE)

What is a Transaction?
A Transaction is a group of SQL statements that work together as one unit.
It means:
👉 Either all statements succeed
👉 Or all statements fail

IMPORTANT COMMANDS

BEGIN TRANSACTION
Syntax
BEGIN TRANSACTION;
Meaning
• This command starts the transaction.

COMMIT
Syntax
COMMIT;
Meaning
• Save all changes permanently.

ROLLBACK
Syntax
ROLLBACK;
Meaning
• Cancel all changes done inside transaction.

What is Savepoint?
Savepoint allows partial cancellation. Instead of cancelling everything, we cancel
only after a certain point.
Syntax
SAVE TRANSACTION SavePointName;
Meaning
• Create a checkpoint inside transaction.
• You can rollback only up to this point.