SQL (Structured Query Language) is a standard language used to communicate with relational databases. It is used to create, retrieve, update and delete data.

SELECT *

FROM Employees;

Common Uses

• Insert Data
• Update Data
• Delete Data
• Retrieve Data
• Create Database Objects

SQL is the language used to query databases. MySQL is a database server that understands SQL.

A Primary Key uniquely identifies each row in a table.

• Unique
• Cannot be NULL
• One Primary Key per table

CREATE TABLE Employees (

  EmployeeId INT PRIMARY KEY,

  Name VARCHAR(100)

);

A Foreign Key creates a relationship between two tables.

CREATE TABLE Orders (

  OrderId INT PRIMARY KEY,

  CustomerId INT,

  FOREIGN KEY(CustomerId)

  REFERENCES Customers(CustomerId)

);

• Maintains Data Integrity
• Prevents Invalid Data
• Creates Relationships

DELETE FROM Employees;

TRUNCATE TABLE Employees;

DROP TABLE Employees;

WHERE filters records based on conditions.

SELECT *

FROM Employees

WHERE Salary > 50000;

• =
• !=
• >
• << /li>
• IN
• LIKE

SELECT DepartmentId,
       COUNT(*)

FROM Employees

GROUP BY DepartmentId

HAVING COUNT(*) > 5;

GROUP BY groups rows that have the same values.

SELECT

  DepartmentId,

  COUNT(*) AS EmployeeCount

FROM Employees

GROUP BY DepartmentId;

• COUNT
• SUM
• AVG
• MIN
• MAX

Aggregate functions perform calculations on multiple rows and return a single value.

SELECT

  AVG(Salary) AS AverageSalary,

  MAX(Salary) AS HighestSalary,

  MIN(Salary) AS LowestSalary

FROM Employees;

INNER JOIN returns only matching records from both tables.

SELECT

  e.EmployeeName,

  d.DepartmentName

FROM Employees e

INNER JOIN Departments d

ON e.DepartmentId = d.DepartmentId;

LEFT JOIN returns all records from the left table and matching records from the right table.

SELECT

  e.EmployeeName,

  d.DepartmentName

FROM Employees e

LEFT JOIN Departments d

ON e.DepartmentId = d.DepartmentId;

Find employees who are not assigned to any department.

A Self Join joins a table with itself.

SELECT

  e.EmployeeName,

  m.EmployeeName AS ManagerName

FROM Employees e

LEFT JOIN Employees m

ON e.ManagerId = m.EmployeeId;

A Subquery is a query nested inside another query.

SELECT *

FROM Employees

WHERE Salary >

(

  SELECT AVG(Salary)

  FROM Employees

);

A View is a virtual table created from a SQL query.

CREATE VIEW ActiveEmployees AS

SELECT *

FROM Employees

WHERE IsActive = 1;

• Reusable Queries
• Security
• Simplified Access

Normalization reduces data redundancy and improves data consistency.

Denormalization intentionally adds redundancy to improve read performance.

Storing customer name directly in Orders table to avoid frequent joins.

A Stored Procedure is a precompiled SQL program stored in the database.

CREATE PROCEDURE GetEmployees

AS

BEGIN

  SELECT *

  FROM Employees

END

• Reusability
• Performance
• Security

Constraints enforce rules on table data.

CREATE TABLE Users (

  UserId INT PRIMARY KEY,

  Email VARCHAR(100) UNIQUE,

  Age INT CHECK (Age >= 18)

);

An Index is a database object that improves query performance by reducing the amount of data scanned.

CREATE INDEX IX_Employee_Email

ON Employees(Email);

Benefits

• Faster Search Operations
• Reduced Table Scans
• Improved Query Performance

A Transaction is a group of operations executed as a single unit.

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 1000
WHERE Id = 1;

UPDATE Accounts
SET Balance = Balance + 1000
WHERE Id = 2;

COMMIT;

Bank money transfer between accounts.

Deadlock occurs when two transactions wait for each other indefinitely.

• Transaction A locks Table 1
• Transaction B locks Table 2
• A waits for Table 2
• B waits for Table 1

Prevention

• Access tables in same order
• Keep transactions short
• Proper indexing

A Cursor processes rows one at a time.

DECLARE EmployeeCursor CURSOR

FOR

SELECT EmployeeId

FROM Employees;

Query Optimization improves performance and reduces execution time.

• Proper Indexing
• Avoid SELECT *
• Optimize Joins
• Reduce Subqueries
• Partition Large Tables

Execution Plan shows how SQL Server executes a query.

• Table Scans
• Index Scans
• Index Seeks
• Missing Indexes
• High Cost Operators

Window Functions perform calculations across rows without collapsing results.

SELECT

  EmployeeName,

  Salary,

  ROW_NUMBER() OVER(

    ORDER BY Salary DESC

  ) AS RankNo

FROM Employees;

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LAG()
• LEAD()

One of the most frequently asked SQL interview questions.

SELECT MAX(Salary)

FROM Employees

WHERE Salary < (

  SELECT MAX(Salary)

  FROM Employees

);

Find duplicate email addresses.

SELECT

  Email,

  COUNT(*) AS Total

FROM Users

GROUP BY Email

HAVING COUNT(*) > 1;

WITH DuplicateUsers AS (

  SELECT

    Id,

    ROW_NUMBER() OVER (

      PARTITION BY Email

      ORDER BY Id

    ) AS RowNum

  FROM Users

)

DELETE

FROM DuplicateUsers

WHERE RowNum > 1;

SELECT

  DepartmentId,

  MAX(Salary) AS HighestSalary

FROM Employees

GROUP BY DepartmentId;

WITH RankedEmployees AS (

  SELECT

    EmployeeName,

    DepartmentId,

    Salary,

    RANK() OVER (

      PARTITION BY DepartmentId

      ORDER BY Salary DESC

    ) AS RankNo

  FROM Employees

)

SELECT *

FROM RankedEmployees

WHERE RankNo = 1;

SELECT TOP 3 *

FROM Employees

ORDER BY Salary DESC;

SELECT *

FROM Employees

ORDER BY Salary DESC

LIMIT 3;

SELECT *

FROM Employees

WHERE JoiningDate >=

DATEADD(DAY, -30, GETDATE());

IDs: 1,2,3,5,6,7 Missing = 4

SELECT

  t1.Id + 1 AS MissingId

FROM Employees t1

LEFT JOIN Employees t2

ON t1.Id + 1 = t2.Id

WHERE t2.Id IS NULL;

SELECT

  DepartmentId,

  COUNT(*) AS EmployeeCount

FROM Employees

GROUP BY DepartmentId;

SELECT *

FROM Employees

WHERE ManagerId IS NULL;

Find CEO or top-level managers.

Frequently asked Window Function problem.

SELECT

  OrderDate,

  Amount,

  SUM(Amount) OVER (

    ORDER BY OrderDate

  ) AS RunningTotal

FROM Orders;

• Check Execution Plan
• Identify Table Scans
• Review Missing Indexes
• Analyze Join Conditions
• Review Statistics
• Check Blocking Sessions

Example

SELECT *

FROM Orders

WHERE CustomerId = 100;

If CustomerId is frequently searched, create an index.

CREATE INDEX IX_Orders_CustomerId

ON Orders(CustomerId);

• Users
• Products
• Categories
• Orders
• OrderItems
• Payments
• Coupons
• Inventory

Relationships

• User → Orders
• Order → OrderItems
• Product → Category
• Order → Payment

• Table Partitioning
• Read Replicas
• Sharding
• Redis Cache
• Archiving
• Optimized Indexes

Common Mistake

Adding more CPU without fixing bad queries.

Sharding distributes data across multiple databases.

Replication copies data from a primary database to secondary databases.

• Read Scaling
• High Availability
• Disaster Recovery

Architecture

• Primary Database → Writes
• Replica Databases → Reads

• Access Tables In Same Order
• Keep Transactions Short
• Use Proper Indexes
• Avoid Long Running Transactions
• Reduce Lock Duration

Payment systems often experience deadlocks during concurrent updates.

AuditLogs

(
  Id,
  UserId,
  Action,
  TableName,
  OldValue,
  NewValue,
  CreatedDate
)

Requirements

• Immutable Records
• Searchable
• Retention Policy
• Security Compliance

• Strict ACID Transactions
• Optimistic/Pessimistic Locking
• Read Replicas
• Audit Logs
• Encryption
• Disaster Recovery

Data consistency is more important than raw performance.

• Users
• Posts
• Comments
• Likes
• Followers
• Messages
• Notifications

Scaling Considerations

• Read Replicas
• Caching
• Partitioning
• Search Engine Integration
• Event Driven Processing