SQL Concepts – Quick Revision Guide
What isSQL?
SQL (Structured Query Language) is the standard language for managing and manipulating relational
databases. It is used to:
Create & modify database structures (DDL)
Insert, update, delete (DML)
Retrieve data (DQL)
Control access and permissions (DCL)
Manage transactions (TCL)
Tips: SQL is declarative, meaning you tell the database what you want, not how to do it.
Types of SQL Commands
Categor y Commands Purpose
CREATE, ALTER, DROP, Define/modify database
DDL (Data Definition Language)
TRUNCATE schema
DML (Data Manipulation
INSERT, UPDATE, DELETE Change data
Language)
DQL (Data Query Language) SELECT Retrieve data
TCL (Transaction Control
COMMIT, ROLLBACK, SAVEPOINT Manage transactions
Language)
DCL (Data Control Language) GRANT, REVOKE Control access
SQL Datatypes
Common categories:
Numeric: INT,SMALLINTB,IGINTDE,CIMAL(p,s), FLOAT, REAL
Character: CHAR(n) (fixed), VARCHAR(n) (variable), TEXT
Date/Time: DATE,TIMED,ATETIMETI,MESTAMP, INTERVAL
Boolean: BOOLEAN /BIT
Binary: BLOB,BYTEA (PostgreSQL), VARBINARY
Json: JSON, JSONB
Best Practice: Use the smallest suitable datatype to save space and improve performance.
, Popular SQL Databases
Open Source: MySQL, PostgreSQL, SQLite, MariaDB
Enterprise: Oracle Database, Microsoft SQL Server, IBM Db2
Cloud-Native: Amazon Aurora, Google Cloud Spanner, Snowflake, Azure SQL Database
Core Concepts
Table → Logical collection of data in rows and columns
Row → One complete record
Column → An attribute/field of the data
Constraints
Constraint Purpose Example
PRIMARY KEY Uniquely identifies each row PRIMARY KEY(id)
References another table’s primary FOREIGN KEY(dept_id) REFERENCES
FOREIGN KEY
key department(id)
UNIQUE No duplicate values allowed UNIQUE(email)
NOT NULL Disallow null values name VARCHAR(50) NOT NULL
CHECK Enforce condition CHECK(age >= 18)
DEFAULT Set default value status VARCHAR(10) DEFAULT 'Active'
Clauses
Filtering: WHERE, HAVING
Grouping: GROUP BY
Sorting: ORDER BY
Pagination: LIMIT (MySQL/PostgreSQL), OFFSET, TOP(SQL Server)
Aliasing: AS
Views
Definition: Virtual tables created from queries
Use cases: Security (hide sensitive columns), simplified queries
Materialized Views: Store the result physically for faster reads
Functions
Aggregate Functions: Operate on sets of rows SUM(), AVG(), COUNT(), MAX(), MIN()
What isSQL?
SQL (Structured Query Language) is the standard language for managing and manipulating relational
databases. It is used to:
Create & modify database structures (DDL)
Insert, update, delete (DML)
Retrieve data (DQL)
Control access and permissions (DCL)
Manage transactions (TCL)
Tips: SQL is declarative, meaning you tell the database what you want, not how to do it.
Types of SQL Commands
Categor y Commands Purpose
CREATE, ALTER, DROP, Define/modify database
DDL (Data Definition Language)
TRUNCATE schema
DML (Data Manipulation
INSERT, UPDATE, DELETE Change data
Language)
DQL (Data Query Language) SELECT Retrieve data
TCL (Transaction Control
COMMIT, ROLLBACK, SAVEPOINT Manage transactions
Language)
DCL (Data Control Language) GRANT, REVOKE Control access
SQL Datatypes
Common categories:
Numeric: INT,SMALLINTB,IGINTDE,CIMAL(p,s), FLOAT, REAL
Character: CHAR(n) (fixed), VARCHAR(n) (variable), TEXT
Date/Time: DATE,TIMED,ATETIMETI,MESTAMP, INTERVAL
Boolean: BOOLEAN /BIT
Binary: BLOB,BYTEA (PostgreSQL), VARBINARY
Json: JSON, JSONB
Best Practice: Use the smallest suitable datatype to save space and improve performance.
, Popular SQL Databases
Open Source: MySQL, PostgreSQL, SQLite, MariaDB
Enterprise: Oracle Database, Microsoft SQL Server, IBM Db2
Cloud-Native: Amazon Aurora, Google Cloud Spanner, Snowflake, Azure SQL Database
Core Concepts
Table → Logical collection of data in rows and columns
Row → One complete record
Column → An attribute/field of the data
Constraints
Constraint Purpose Example
PRIMARY KEY Uniquely identifies each row PRIMARY KEY(id)
References another table’s primary FOREIGN KEY(dept_id) REFERENCES
FOREIGN KEY
key department(id)
UNIQUE No duplicate values allowed UNIQUE(email)
NOT NULL Disallow null values name VARCHAR(50) NOT NULL
CHECK Enforce condition CHECK(age >= 18)
DEFAULT Set default value status VARCHAR(10) DEFAULT 'Active'
Clauses
Filtering: WHERE, HAVING
Grouping: GROUP BY
Sorting: ORDER BY
Pagination: LIMIT (MySQL/PostgreSQL), OFFSET, TOP(SQL Server)
Aliasing: AS
Views
Definition: Virtual tables created from queries
Use cases: Security (hide sensitive columns), simplified queries
Materialized Views: Store the result physically for faster reads
Functions
Aggregate Functions: Operate on sets of rows SUM(), AVG(), COUNT(), MAX(), MIN()
