SQL

�Introduction to SQL

• What is SQL?
• SQL stands for Structured Query Language and it is pronounced as S-Q-L or sometimes as See-Quell.
• It is used to perform operations on the records stored in database, such as updating records,inserting records,deleting,creating and modifying database tables ,views etc.
• SQL is not a database system ,but it is a query language.
• SQL is like a language to ask questions to your data.
• It is used in apps, websites, companies—almost everywhere data is stored.
• It's simple, powerful, and very useful to learn.

​

​

​

​

​

​

​

• SQL is a language that communicates with databases.
• This database language is mainly designed for maintaining the data in relational database management systems.
• It is a special tool used by data professionals for handling structured data (data which is stored in the form of tables). It is also designed for stream processing in RDSMS.

​

​

​

How SQL Works?

​

• SQL works by allowing users to communicate with a database to perform tasks like retrieving, inserting, updating, and deleting data.
• The process involves parsing and compilation of SQL commands to understand their structure and syntax.

Syntax: SELECT * FROM students WHERE grade = ‘A’;

• SQL queries are optimized by the database’s query optimizer to execute them efficiently.
• The database management system (DBMS) then executes the query, accesses the database’s storage, and returns results to the user or application.����

​

​

History of SQL

​

🔹 1970 – The Birth of the Relational Model�🔹 1973 – SQL is Developed at IBM�🔹 1976 – SEQUEL becomes SQL�🔹 1980s – SQL Becomes a Standard�🔹 1980s–1990s – SQL Grows in Popularity�🔹 2000s–Present – Continuous Improvement��

• Uses of SQL in real world�Managing Databases�Data Retrieval�Web Development�Mobile Apps�Data Analysis�Business Intelligence (BI)�Finance & Banking�Healthcare Systems�E-commerce Platforms�Education Systems�

🔹 1.Managing Databases: SQL is used to create, modify, and manage databases. You can define tables, insert data, update records, and delete unwanted data.�🔹 2. Data Retrieval: You can query large datasets and retrieve specific data using SQL. � Example: Get a list of all customers who made a purchase this month.�🔹 3. Web Development: Websites use SQL to store and fetch user data.�Example: Login systems, product search, comment sections.�🔹 4. Mobile Apps: Apps like Instagram, Facebook, and Uber use SQL in the background to manage user profiles, messages, transactions, etc.�🔹 5. Data Analysis: Data analysts use SQL to analyze large datasets, generate reports, and find insights. Tools like Power BI, Tableau often integrate with SQL databases.�🔹 6. Business Intelligence (BI): SQL helps businesses make data-driven decisions by accessing and visualizing key business metrics.��

��🔹 7. Finance & Banking: Used for handling transactions, customer accounts, ATM records, fraud detection, etc.�🔹 8. Healthcare Systems: Hospitals use SQL to store patient records, appointment data, billing information.�🔹 9. E-commerce Platforms: Stores use SQL to manage product inventories, orders, customer details, and reviews.�🔹 10. Education Systems: Schools and universities store student data, attendance, results, course details using SQL databases.�

• Importance of SQL in real world�🔹 1. Standard Language for Databases: SQL is the universal language used to interact with most relational databases like MySQL, PostgreSQL, SQL Server, Oracle, etc. It is used globally in almost every industry.�🔹 2. Easy to Learn and Use: SQL has simple, English-like syntax. Even beginners can learn it quickly to work with data.�🔹 3. Data is Everywhere: Every app, website, and business uses data. SQL helps store, retrieve, and manage that data efficiently.�🔹 4. Crucial for Data Analysis: SQL is a must-have skill for data analysts, data scientists, and business analysts. It allows them to analyze large datasets, create reports, and get insights.�🔹 5. Widely Used in Industries: From banking, healthcare, and retail to education and e-commerce, SQL is used everywhere. It helps in handling transactions, customer information, inventory, and much more.

🔹 6. Integrates with Other Tools: SQL works well with data tools like Power BI, Tableau, Excel, Python, and R. This makes it perfect for reporting and advanced analysis.�🔹 7. Supports Decision Making: Businesses use SQL to track performance, customer trends, sales, etc. This helps in making data-driven decisions.�🔹 8. Automation and Security: SQL allows automation of repetitive tasks. It also includes permissions and user controls to secure data access.�🔹 9. Career Growth: SQL is in high demand. Knowing SQL opens opportunities in roles like: � Data Analyst� Database Administrator� Software Developer� Business Analyst

DBMS VS RDBMS

Popular RDBMS tools: MySQL, PostgreSQL, SQL Server, Oracle

1. MySQL

• Type: Open-source (also has commercial versions)
• Owned by: Oracle Corporation
• Highlights:
• Lightweight, fast, and easy to use
• Great for web development (especially with PHP)
• Uses the InnoDB engine for full ACID compliance
• Use Cases: Blogs, e-commerce websites, web apps (e.g., WordPress, Magento)

​

2. PostgreSQL

• Type: Open-source
• Maintained by: PostgreSQL Global Development Group (community-led)
• Highlights:
• Known for strict compliance with SQL standards
• Highly extensible and supports complex queries
• Advanced features (e.g., custom data types, full-text search)
• Use Cases: Scientific research, analytics, geospatial data (with PostGIS), financial applications

​

3. SQL Server

• Type: Commercial (with free editions like Express)
• Developed by: Microsoft
• Highlights:
• Seamless integration with Microsoft technologies (.NET, Azure)
• Powerful management tools (like SSMS)
• Supports high availability and advanced security features
• Use Cases: Enterprise business applications, internal company tools, reporting and BI

​

4. Oracle Database

• Type: Commercial (free Express Edition available)
• Developed by: Oracle Corporation
• Highlights:
• Scalable and robust for large systems
• Advanced security, performance tuning, and high-availability features
• Used in mission-critical and high-transaction environments
• Use Cases: Banking, telecommunications, enterprise-level ERP/CRM systems

​

Summary Table

BASICS OF SQL

Understanding tables, rows, columns

Table:

• A table is a collection of data arranged in rows and columns. It represents a specific type of data like customers, products, or orders.

Example: A "Students" table.

Row (Record):

• Each row in a table represents a single record or entry.

Example: One student's details in a row — like Name, Age, Grade.

Column (Field):

• Each column represents a specific attribute of the data.

Example: "Name", "Age", "Grade" are columns in the "Students" table.

​

​

​

Creating a Database

Syntax: CREATE DATABASE database_name;

Example: CREATE DATABASE SchoolDB;

Using a Database

Syntax: USE database_name;

Example: USE SchoolDB;

Creating a table

Syntax:

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype,

…………

);

​

​

Example:

CREATE TABLE Students (

StudentID INT,

Name VARCHAR(50),

Age INT,

Grade VARCHAR(5)

);

Inserting Data into tables

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

VALUES (value1, value2, ...);

Example: INSERT INTO Students (StudentID, Name, Age, Grade)

VALUES (1, 'Ravi', 15, 'A');

​

Basic SELECT Statement

• The SELECT statement is used to retrieve data from a table in sql.

Syntax: SELECT column1, column2 FROM table_name;

SELECT * FROM table_name;

Example: SELECT * FROM Students;

SELECT specific columns

Syntax: SELECT column1, column2 FROM table_name;

Example: SELECT Name, Age FROM Students;

SELECT with DISTINCT

The DISTINCT keyword is used to remove duplicates values and return only unique values in query

Syntax: SELECT DISTINCT column1, column2 FROM table_name;

​

​

Example: SELECT DISTINCT Grade FROM Students;�

DATA TYPES IN SQL

Common data types: INT, VARCHAR, TEXT, DATE, FLOAT, BOOLEAN��������Example:�CREATE TABLE Students (� StudentID INT,� Name VARCHAR(50),� Bio TEXT,� BirthDate DATE,� Marks FLOAT,� IsPassed BOOLEAN�);�����

Choosing correct data types

• Choosing the right data type is very important in database design. It helps ensure that data is:
• Stored efficiently
• Easy to query
• Accurate and consistent
• NULL vs NOT NULL in SQL
• NULL: It represents missing or unknown data. It is not the same as zero or an empty string.
• Usage: If you allow NULL the column can have no value or empty value in some rows.
• Example:
• If a student's middle name is unknown, you might store NULL

​

CREATE TABLE Students (� StudentID INT,� Name VARCHAR(50),� MiddleName VARCHAR(50) NULL�);�

NOT NULL: It means that a column must have a value. You cannot leave it empty.

Usage: If you define a column as NOT NULL every row in the table must have a value for that column.

Example:

• A student’s ID must be provided, so the StudentID column is defined as NOT NULL

CREATE TABLE Students (

StudentID INT NOT NULL,

Name VARCHAR(50) NOT NULL,

MiddleName VARCHAR(50) NULL

);

​

CONSTRAINTS IN SQL

PRIMARY KEY

• A Primary Key is a unique identifier for each record in a table. It ensures that:
• No two rows have the same value in that column (uniqueness)
• The value is never NULL (it must always be present)
• Why is a Primary Key Important?
• ✅ Uniquely identifies each row
• ❌ Prevents duplicate records
• 🚫 Disallows NULL values
• 🔗 Helps create relationships between tables (via foreign keys)

​

​

​

Example:�CREATE TABLE Students (� StudentID INT PRIMARY KEY,� Name VARCHAR(50),� Age INT�);� Here StudentID is the primary key;� Each student must have a unique and non-null ID.�

Composite Primary Key�

• A primary key made from multiple columns.
• A Composite Primary Key is a primary key made of two or more columns. It uniquely identifies each row using the combination of values in those columns.

CREATE TABLE Enrollments (

StudentID INT,

CourseID INT,

EnrollmentDate DATE,

PRIMARY KEY (StudentID, CourseID)

);

​

�

• A student can register for multiple courses.
• But the same student cannot register twice for the same course.
• Together, StudentID + CourseID form a composite primary key.

​

FOREIGN KEY

• Foreign Key is a column (or set of columns) in one table that links to the Primary Key of another table.
• It is used to create relationships between two tables and to maintain data integrity.

​

​

Example :

Students Table

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(50)

);

Enrollment Table

CREATE TABLE Enrollments (

EnrollmentID INT PRIMARY KEY,

StudentID INT,

CourseName VARCHAR(50),

FOREIGN KEY (StudentID) REFERENCES Students(StudentID)

);

​

​

�UNQUIE KEY

• A Unique Key is a type of constraint in SQL used to ensure that the values in a particular column (or group of columns) are all different from each other — meaning, no duplicates are allowed.
• It is primarily used to maintain data integrity and prevent duplicate entries for fields that should be unique — such as email, phone number, username, etc.
• A Unique Key ensures that:
• Each value is distinct in that column.
• You cannot insert two rows with the same value in a unique column.
• You can have one NULL value in a Unique Key column (depends on DBMS).

​

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Email VARCHAR(100) UNIQUE,

PhoneNumber VARCHAR(15) UNIQUE,

Name VARCHAR(50)

);

Duplicate Values�INSERT INTO Employees (EmpID, Email, PhoneNumber, Name)

VALUES (1, 'john@example.com', '1234567890', 'John’);

​

INSERT INTO Employees (EmpID, Email, PhoneNumber, Name)

VALUES (2, 'john@example.com', '9876543210', 'Mike');

​

​

NOT NULL

• The NOT NULL constraint in SQL is used to make sure that a column cannot have empty (NULL) values.
• When a column is defined as NOT NULL it must always have a value — it cannot be left blank when inserting or updating a record.
• Key Points:
• Ensures mandatory data is entered.
• Prevents NULL (empty) values in that column.
• Helps maintain data completeness and reliability.

​

​

CREATE TABLE Students (� StudentID INT PRIMARY KEY,� Name VARCHAR(100) NOT NULL,� Age INT NOT NULL�);�

​

Invalid Insert Example:

-- This will cause an error because Name is NOT NULL

INSERT INTO Students (StudentID, Name, Age)

VALUES (1, NULL, 20);

​

DEFAULT

• The Default constraint in SQL is used to set a default value for a column when no value is provided during data insertion.
• Key Points:
• Automatically fills in a value if none is given.
• Helps prevent NULL values.
• Useful for setting standard or commonly used values.

​

CREATE TABLE Employees (� EmpID INT PRIMARY KEY,� Name VARCHAR(100) NOT NULL,� Country VARCHAR(50) DEFAULT 'India'�);�

​

INSERT INTO Employees (EmpID, Name)

VALUES (1, 'Ravi');

​

CHECK

• The Check constraint is used in SQL to limit the values that can be placed in a column. It makes sure the data entered meets a specific condition.
• Key Features:
• Ensures valid data only is stored.
• Rejects data that doesn’t meet the condition.
• Can be used on one or multiple columns.

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(100),

Age INT CHECK (Age >= 18)

);

​

​

AUTO_INCREMENT(MySQL)

• Auto Increment is used in SQL to automatically generate a unique number for a column when a new row is inserted.
• It’s commonly used for primary key columns, like IDs.

Key Features:

• Automatically increases the number with each new row.
• Ensures each record gets a unique ID.
• Saves time – no need to manually enter ID values.

​

​

RETRIEVING DATA

SELECT with WHERE Clause

• The WHERE clause in SQL is used to filter records. It tells the database to return only rows that meet a specific condition.

SELECT column1, column2, ...

FROM table_name

WHERE condition;

​

SELECT * FROM Employees

WHERE Department = 'HR’;

​

​

​

​

​

SELECT Name, Salary

FROM Employees

WHERE Salary > 30000;

​

Comparison operators�Comparison operators are used in SQL to compare values in the WHERE clause.

​

​

​

​

​

​

​

Example: Employees Table

SELECT * FROM Employees WHERE Salary > 40000;

4. < (Less than): checks if the left value is less than right value

SELECT * FROM Employees WHERE Salary < 30000;

5. >= (Greater Than or Equal To): checks if the left value is greater than or equal to right value.

SELECT * FROM Employees WHERE Salary >= 50000;

6. <= (Less Than or Equal To): checks if the left value is less than or equal to right value.

SELECT * FROM Employees WHERE Salary <= 25000;

1. = (Equal To):check if two values are equal.

SELECT * FROM Employees WHERE Department = 'IT’;

2. != or < >(Not Equal To):check if two values not equal.

SELECT * FROM Employees WHERE Department != 'HR’;

3.>(Greater than): checks if the left value is greater than right value

Logical Operators in SQL (AND, OR, NOT) :Logical operators are used in SQL to combine multiple conditions in the WHERE clause.

1. AND: Returns rows only when both conditions are true.

SELECT * FROM Employees

WHERE Department = 'IT' AND Salary > 40000;

2. OR: Returns rows when either condition is true (or both).

SELECT * FROM Employees

WHERE Department = 'HR' OR Salary < 30000;

3. NOT: Reverses the result of a condition — returns rows where the condition is false.

SELECT * FROM Employees

WHERE NOT Department = 'IT';

​

​

BETWEEN, IN, NOT IN:��

1. BETWEEN

• Used to filter values within a range (inclusive).
• Works with numbers, dates, and text.

Syntax: SELECT * FROM Employees WHERE Salary BETWEEN 30000 AND 50000;

​

�

2. IN

• Used to match multiple values in a column.
• Acts like multiple OR conditions.

Syntax: SELECT * FROM Employees WHERE Department IN ('HR', 'IT');

3. NOT IN

• Returns rows that do NOT match the listed values.

Syntax: SELECT * FROM Employees WHERE Department NOT IN ('HR', 'Sales');

​

LIKE, NOT LIKE

1. LIKE:
1. used to search for a pattern in a column especially text(string).
2. Often used with wild cards
• % → any number of characters
• _ → exactly one character

Syntax: SELECT * FROM Employees WHERE Name LIKE 'S%’;

​

​

2. NOT LIKE� Used to exclude results that match a pattern.�

Syntax: SELECT * FROM Employees WHERE Name NOT LIKE '%a';

​

1. IS NULL�Used to find rows where a column has no value (i.e., it is NULL)�Null means unknown or missing data, not zero or blank.�

Syntax: SELECT * FROM Employees WHERE Department IS NULL;

2. IS NOT NULL

• Used to find rows where a column has a value (i.e., it is not NULL)

Syntax: SELECT * FROM Employees WHERE Department IS NOT NULL;

​

Sorting and Limiting Results

ORDER BY: It is used to sort the result of a query based on one or more columns.���

Syntax: SELECT * FROM table_name

ORDER BY column_name ASC; -- or DESC

• ASC – Ascending Order (Default)
• Sorts values from smallest to largest, or A to Z.

Syntax: SELECT * FROM Employees ORDER BY Salary ASC;

• DESC – Descending Order
• Sorts values from largest to smallest, or Z to A.

Syntax: SELECT * FROM Employees ORDER BY Name DESC;

​

​

​

LIMIT and OFFSET�

LIMIT/TOP:

• Used to restrict the number of rows returned by a query.
• Helps to fetch only a portion of the results.

Syntax: SELECT * FROM Employees LIMIT 5;

O/P: Ravi Sneha Akash Priya Arun

2.OFFSET �

• Used to skip a number of rows before starting to return results.
• Often used with LIMIT for pagination (like showing page 2, 3...).

Syntax: SELECT * FROM table_name

LIMIT number_of_rows OFFSET number_of_rows_to_skip;

Example: SELECT * FROM Employees LIMIT 3 OFFSET 2;

​

FETCH FIRST (PostgreSQL/SQL Server)

FETCH FIRST & FETCH NEXT: Used to get a specific number of rows from a query result.

Syntax: SELECT * FROM table_name

ORDER BY column_name

OFFSET n ROWS FETCH NEXT m ROWS ONLY;

​

Example:�SELECT * FROM Employees�ORDER BY ID�OFFSET 4 ROWS FETCH NEXT 5 ROWS ONLY;�

• FETCH NEXT:

Syntax: SELECT * FROM Employees

ORDER BY ID

OFFSET 5 ROWS FETCH NEXT 3 ROWS ONLY;

​

OUTPUT

FETCH FIRST

Syntax: SELECT * FROM Employees

ORDER BY ID

FETCH FIRST 4 ROWS ONLY;

​

SQL Functions

Aggregate Functions COUNT(), SUM(), AVG(), MIN(), MAX():

• COUNT(): Returns the number of rows.

Syntax: SELECT COUNT(*) FROM Employees;

• SUM(): Returns the total sum of a numeric column.

Syntax: SELECT SUM(Marks) FROM Employees;

• AVG()-Average: Calculates the average (mean) of numeric values.

Syntax: SELECT AVG(Marks) FROM Employees;

• MIN(): Returns the smallest value in a column.

Syntax: SELECT MIN(Marks) FROM employees;

• MAX(): Returns the Largest value in a column.

Syntax: SELECT MAX(Marks) FROM Employees;

​

​

​

​

String Functions UPPER(), LOWER(), LENGTH(), CONCAT(), SUBSTRING()

• UPPER(): Converts the string into uppercase.

Syntax: SELECT UPPER('hello world’);

Output: HELLO WORLD

• LOWER(): Converts the string into lowercase.

Syntax: SELECT LOWER('HELLO WORLD’);

Output: hello world

• LENGTH OR LEN(): Returns the number of characters in a string.

Syntax: SELECT LENGTH('SQL Functions’);

Output: 13

​

​

​

​

• CONCAT(): Combines two or more strings.

Syntax: SELECT CONCAT('Hello', ' ', 'World’);

Output: Hello World

• SUBSTRING OR SUBSTR(): Extracts part of a string starting from a specific position.

Syntax: SELECT SUBSTR('Database', 1, 4);

Output: Data

​

​

Date & Time Functions NOW(), CURDATE(), DATE_ADD(), DATEDIFF()

• NOW(): Returns the current date and time.

Syntax: SELECT NOW();

• CURDATE(): Returns the current date only without time.

Syntax: SELECT CURRDATE();

• DATE_ADD(): Adds a specific amount of time(like days, months)to Date.

Syntax: SELECT DATE_ADD('2025-05-07', INTERVAL 5 DAY);

• DATE_DIFF(): Returns no.of days between two dates.

Syntax: SELECT DATEDIFF('2025-05-10', '2025-05-01');

​

​

Conversion Functions

• Conversion functions are used to change the data type of a value.�They are useful when you want to convert between types like:
• String ↔ Integer
• Date ↔ String
• Float ↔ Integer, etc.
• These conversions are helpful when:
• You want to format output.
• You need to do calculations or comparisons.
• You're joining tables with different data types.

​

​

• CAST(): Used to convert one data type to another in a standard SQL (ANSI compliant) way.

Syntax: CAST(expression AS target_data_type)

• CONVERT(): Also converts one data type to another, but it’s more flexible in SQL Server (supports formatting styles for dates).

Syntax: CONVERT(target_data_type, expression, [style])

​

​

GROUP BY and HAVING

GROUP BY for aggregation:

• This clause in SQL is used to group rows that have the same values in specified columns into summary rows.
• It’s commonly used with aggregate functions like:
• COUNT(): counts number of rows
• SUM():adds up values
• AVG():calculates average
• MAX():finds maximum value
• MIN():finds minimum value

Syntax: SELECT column1, AGGREGATE_FUNCTION(column2)

FROM table_name

GROUP BY column1;

​

Example:�

HAVING CLAUSE

• The HAVING clause is used with GROUP BY to filter the grouped rows based on an aggregate condition.
• WHERE filters individual rows before grouping, HAVING filters groups after aggregation.

Syntax: SELECT column1, AGG_FUNC(column2)

FROM table

GROUP BY column1

HAVING condition_on_aggregate;

​

Filtering groups using HAVING:

• The HAVING clause in SQL is used to filter groups that are formed by the GROUP BY clause.
• WHERE filters individual rows before grouping.
• HAVING filters grouped results after aggregation.

Syntax: SELECT column1, AGG_FUNC(column2)

FROM table_name

GROUP BY column1

HAVING AGG_FUNC(column2) condition;

​

​

GROUP BY with multiple columns:

• The GROUPBY clause can group data by more than one column. This is useful when you want to organize data based on combinations of multiple fields.

Syntax: SELECT column1, column2, AGG_FUNC(column3)

FROM table_name

GROUP BY column1, column2;

​

​

JOINS

JOIN:A JOIN clause is used to combine rows from two or more tables based on a related column between them, usually a foreign key.��EMPLOYEE TABLE�

1. INNER JOIN: It returns only the rows that have matching values in both tables.�Syntax: SELECT columns� FROM table1� INNER JOIN table2� ON table1.common_column = table2.common_column;�

2. LEFT JOIN (LEFT OUTER JOIN): It returns all rows from the left table and matching rows from the right table. If there’s no match, NULL is returned for right table columns.

Syntax: SELECT columns

FROM table1

LEFT JOIN table2

ON table1.common_column = table2.common_column;

​

3. RIGHT JOIN (RIGHT OUTER JOIN): It returns all rows from the right table and matching rows from the left table. If there’s no match, NULL is returned for left table columns.�Syntax: SELECT columns� FROM table1� RIGHT JOIN table2� ON table1.common_column = table2.common_column;�

4. FULL OUTER JOIN: It returns all rows from both tables. Rows without a match in one of the tables will have NULL values.

Syntax: SELECT columns

FROM table1

FULL OUTER JOIN table2

ON table1.common_column = table2.common_column;

​

5. CROSS JOIN: It returns all combinations of rows from both tables (Cartesian Product).�Syntax: SELECT *� FROM table1� CROSS JOIN table2;�

6. SELF JOIN: A SELF JOIN s a join of a table with itself. You need to use aliases.

Syntax: SELECT A.column, B.column

FROM table A

JOIN table B

ON A.column = B.related_column;

​

SUB QUERIES

SUB QUERIES WITH SELECT

• A subquery is a query inside another query.�When used in a SELECT clause, it is usually used to fetch a single value (scalar subquery).
• SYNTAX:

SELECT

column1,

(SELECT expression FROM table2 WHERE condition) AS alias_name

FROM table1;

​

​

SUB QUERIES IN WHERE CLAUSE

• A subquery in the WHERE clause is used to filter the records based on the result of another query.
• Compare values from another table

Types of WHERE subqueries:

• Single-row subquery (returns one value)�Use with =,<,> etc
• Multi-row subquery (returns multiple values)�Use with IN,ANY,ALL

• Compare values from another table

​

CORRELATED SUB QUERIES

• A correlated subquery is a subquery that uses a value from the outer query.
• It is executed for every row of the outer query.
• It depends on the current row of the outer query to produce its result.

SYNTAX:

SELECT column1, column2

FROM table1 t1

WHERE columnX = (

SELECT columnY FROM table2 t2

WHERE t2.related_column = t1.related_column

);

​

EXISTS:� It is used to check whether a subquery returns any rows.�

• If the subquery returns at least one row, the condition is TRUE.
• Often used with correlated subqueries.

SYNTAX:

SELECT column1, column2

FROM table1 t1

WHERE EXISTS (

SELECT 1 FROM table2 t2

WHERE t1.id = t2.id

);

​

NOT EXISTS

• The outer query returns rows only if the subquery returns no rows.

SYNTAX:

SELECT Name, DeptID

FROM Employee e

WHERE NOT EXISTS (

SELECT 1 FROM Department d

WHERE d.DeptID = e.DeptID

);

​

SUB QUERIES IN FROM CLAUSE

• A subquery in the FROM clause is called an inline view or derived table.
• It is treated like a temporary table created during query execution.
• You must give it an alias.

SYNTAX:

SELECT columns

FROM (

SELECT ... FROM table WHERE ...

) AS alias_name

WHERE condition_on_derived_table;

​

SET OPERATIONS

UNION:

• It is used to combine the result sets of two or more SELECT statements into a single result set.
• It removes duplicate rows.

Syntax:

SELECT column1, column2, ...

FROM table1

UNION

SELECT column1, column2, ...

FROM table2;

​

UNION ALL:

• It is used to combine the results of two or more SELECT Queries.
• It does not remove duplicate rows.
• It returns all rows, including duplicates.

SYNTAX:

SELECT column1, column2 FROM table1

UNION ALL

SELECT column1, column2 FROM table2;

​

EXCEPT/MINUS:

• It returns only the rows from the first SELECT query that are not present in the second SELECT query.
• It removes duplicates automatically.

SYNTAX:

SELECT column1, column2 FROM table1

EXCEPT

SELECT column1, column2 FROM table2;

​

​

• It removes duplicates automatically.

​

INTERSECT:

• It is a set operation that returns only the common rows between two SELECT Queries.
• It automatically removes duplicates.

SYNTAX:

SELECT column1, column2 FROM table1

INTERSECT

SELECT column1, column2 FROM table2;

​

MODIFYING DATA

UPDATE Statement

• All rows in the table will be updated!

SYNTAX:

UPDATE table_name

SET column1 = value1, column2 = value2;

• UPDATE with WHERE

SYNTAX:

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

​

​

​

DELETE

• Statement is used to remove rows from a table.

SYNTAX:

DELETE FROM table_name

WHERE condition;

DELETE Statement with CONDITION:

• Only the rows that match the condition will be deleted.

SYNTAX:

DELETE FROM table_name

WHERE condition;

​

​

​

TRUNCATE VS DELETE

• It is used to delete all rows from a table quickly and permanently.
• It cannot delete specific rows — it deletes everything.

DELETE�Statement is used to remove rows from a table.

​

REPLACE AND UPSERT

• It is used to insert a new row or replace an existing row if there's a conflict (usually on primary key or unique key).
• Update + Insert�If the row exists, it updates it.
• If the row does not exists, it inserts it.

INDEXES

What is an Index?

• An index in SQL is a database object that improves the speed of data retrieval (SELECT queries) from a table, just like an index in a book helps you find information faster.
• -- Non-clustered Index
• CREATE INDEX idx_emp_name
• ON Employees(Name);
• -- Unique Index
• CREATE UNIQUE INDEX idx_emp_email
• ON Employees(Email);

Creating an index:

Syntax: CREATE [UNIQUE] INDEX index_name

ON table_name (column1, column2, ...);

​

​

​

VIEWS

VIEW: �A view is a virtual table based on the result of a SQL query. It does not store data physically, but it acts like a table and can be used in SELECT, JOIN, etc.

​

• SYNTAX:

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

​

TRANSACTIONS

Definition:� A transaction is a set of one or more SQL statements that are executed as a single unit of work.�All the operations in a transaction must complete successfully,�or else none of them should take effect.�This ensures data accuracy, consistency, and integrity.�

COMMIT :

• It is a transaction control command in SQL.
• It is used to save all the changes made during current transaction permanently to the database.
• Once COMMIT is executed ,the changes cannot be undone.

SYNTAX: COMMIT;

​

• It is used to save all the changes made during the current transaction permanently to the database.

​

ROLLBACK: �It is a transaction control command used to undo all the changes made in the current transaction.�It is used when something goes wrong (like an error or failed condition).

SYNTAX:

BEGIN TRANSACTION;

-- SQL statements here...

• ROLLBACK;

​

SAVE POINT:� It is used to set a temporary marker within a transaction.�You can ROLLBACK to that savepoint instead of rolling back the entire transaction.�It gives more control when handling large transactions.�

SYNTAX:

BEGIN TRANSACTION;

-- SQL statements

SAVE TRANSACTION savepoint_name;

-- more SQL statements

ROLLBACK TRANSACTION savepoint_name;

-- finally

COMMIT;

​

ACID Properties

​

• Atomocity: A transaction must execute completely or not at all. If one part fails, the entire transaction is rolled back.
• Consistency -Valid State: A transaction must bring the database from one valid state to another.
• Isolation: Multiple transactions happening at the same time should not affect each other.
• Durability – Permanent Change: Once a transaction is committed, its changes are permanent, even if system crashes immediately after.

​

​

​

ISOLATION LEVELS

• Isolation is one of the ACID properties.
• It ensures that concurrent transactions do not interfere with each other.
• SQL supports different levels of isolation that control how and
• when changes made by one transaction are visible to others.

Read UnCommitted:

• Transactions can see uncommitted changes of their transactions.

SYNTAX:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Read Committed:

A transaction can only read committed data.

SYNTAX:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

​

​

Repeatable Read:

• Ensures that same row read twice will return the same value.

SYNTAX:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Serializable:

Highest level of isolation.

SYNTAX:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

​

STORED PROCEDURES AND FUNCTIONS

• A stored procedure is a precompiled collection of one or more SQL statements that are stored in the database and can be executed whenever required.

Creating stored procedures

SYNTAX:

CREATE PROCEDURE ProcedureName

@param1 datatype,

@param2 datatype = default_value -- optional

AS

BEGIN

-- SQL statements

END;

​

IN,OUT,INOUT PARAMAETERS

• IN PARAMETER:
• Used to pass a value into the procedure.
• It is read-only within the procedure.
• OUT PARAMETER:
• Used to return a value from the procedure.
• The value is assigned inside the procedure and returned back.
• INOUT PARAMETER:
• Used to pass a value, and return a modified value out.
• Acts as both input and output.

SQL User Defined Functions

Definition:

• A User Defined Function (UDF) in SQL is a custom function that you define to perform a specific task and return a result.
• It is similar to functions in programming languages — it accepts input, processes it, and returns a value.

1. Scalar Function

• Returns a single value (int, varchar, etc.)

2. Inline Table-Valued Function

• Returns a table based on a single SELECT statement.

3. Multi-statement Table-Valued Function

• Returns a table using multiple SQL statements.

​

​

​

​

TRIGGERS

Defintion:�A trigger is a stored program in a database that automatically executes (fires) when a specified event occurs in a table or view.It is used to automate processes, enforce rules, maintain logs, or ensure data integrity.

SYNTAX:

CREATE TRIGGER trigger_name

[BEFORE | AFTER | INSTEAD OF]

[INSERT | UPDATE | DELETE]

ON table_name

FOR EACH ROW

BEGIN

-- SQL statements

END;

​

• BEFORE INSERT AND AFTER INSERT

CREATE TRIGGER trg_AfterInsert

ON Employeess11

AFTER INSERT

AS

BEGIN

PRINT

END;

BEFORE UPDATE AND AFTER UPDATE

SYNTAX:

CREATE TRIGGER trg_AfterUpdate

ON Employeess11

AFTER UPDATE

AS

BEGIN

PRINT

END;

​

• BEFORE DELETE AND AFTER DELETE

SYNTAX

CREATE TRIGGER trg_AfterDelete

ON Employeess11

AFTER DELETE

AS

BEGIN

PRINT

END;

​

Users, Roles, and Permissions

• Creating user

GRANT AND REVOKE :

GRANT:

• It is used to give permissions to a user or role to perform certain actions on a database object (like a table, view, procedure, etc.).

Syntax:

GRANT SELECT, INSERT

ON Employeess11

TO RaviUser;

​

REVOKE:� It is used to take back permissions that were previously given using GRANT

SYNTAX:

REVOKE INSERT

ON Employeess11

FROM RaviUser;

​

Normalization

• Normalization is a process in relational database design used to:
• Remove data redundancy (repeating data)
• Eliminate update anomalies
• Organize data efficiently
• 1NF – First Normal Form

Rule:

• Each column should contain atomic (indivisible) values.
• No repeating groups or arrays.

​

• 2NF – Second Normal Form

Rule:

• Must be in 1NF
• No partial dependencies on a composite primary key

Partial Dependency = A non-key attribute depends only on part of the composite key.

• 3NF – Third Normal Form

Rule:

• Must be in 2NF
• No transitive dependency
• Transitive Dependency:
• When A → B → C, and A is the primary key.�C is transitively dependent on A via B.

​

• BCNF – Boyce-Codd Normal Form
• Rule:
• Must be in 3NF
• Every determinant must be a candidate key
• Determinant: An attribute (or set) that uniquely determines another.

​

Denormalization:

Denormalization is the process of intentionally adding redundancy to a normalized

database to improve read performance and reduce complex joins.

In simple terms:

• Normalization = divide data into multiple related tables (to avoid duplication)
• Denormalization = combine tables back together (to make queries faster)

​