QUICK SQL REVISION

While revising SQL, I realized that I keep making the same notes again and again. So I decided to document everything in one place for quick reference.

As I was writing, I thought—why not share it as an article? It might help others who are learning SQL or revising for interviews.

This article covers the core SQL concepts and is intended as a quick revision guide as well as an easy starting point for beginners.

SQL

What is SQL?
SQL stands for Structured Query Language and is the standard language used to interact with relational databases. It allows us to:

• Create database structures like tables, views, and indexes.

• Perform CRUD operations (Create, Read, Update, Delete) on the data stored in those tables.

• Define permissions and control access to the data.

• CRUD expands to:

• C – Create

• R – Read

• U – Update

• D – Delete

What is a database?
A database is a structured collection of data stored in a digital system so that it can be efficiently accessed, managed, and updated. It acts as the backbone of most applications because it persists user and system data; without it, most apps would lose their state and real-world usefulness.
Databases are broadly categorized into:

• Relational databases
• Non-relational (NoSQL) databases

Relational databases
In relational databases, data is stored in tables (rows and columns) with a predefined schema. This structure:

• Makes it easier and faster to query data using relationships (joins) between tables.

• Requires designing the schema up front, so a good understanding of the data model is needed before implementation.
  Examples:

• MySQL

• PostgreSQL

• SQL Server

• Oracle

*Relational databases use SQL as their primary query language

Non-relational databases
Non-relational (NoSQL) databases do not rely on fixed tables and schemas like relational databases. Data can be stored as documents, key–value pairs, wide-column stores, or graphs. This:

• Makes it easier and faster to get started because the schema can be more flexible or even schema-less.
• Can become harder to reason about as the data model grows if there is no consistent structure.

• Can scale very well horizontally for certain workloads, though query patterns and consistency guarantee differ from relational systems.
  Examples:

• MongoDB (document-based)

• Redis (key–value)

• Cassandra (wide-column)

Data Types
Before moving forward, it is important to understand the concept of data types in SQL. Like most programming languages, when creating a table, we must specify the type of each column so the database knows what kind of data it will store and how to validate it.
Data types help:

• Enforce that data is stored in the correct format (numbers, text, dates, etc.).
• Optimize storage and performance by choosing appropriate sizes and representations.

Most Common Data Types
SQL offers Different Data Types but here we will discuss about most common data types.

1. CHAR

• CHAR(n) is a fixed-length character type used to store strings with a maximum length of n characters, for example CHAR (21).
• The database reserves space for exactly n characters for each value; if the string is shorter, it may be padded with spaces internally.
• If you try to insert a string longer than n, it will either be truncated or cause an error, depending on the database and settings. Use cases: values with fixed length such as country codes, status codes, or fixed-format identifiers.

2. VARCHAR

• VARCHAR(n) is a variable-length character type used to store strings up to n characters.
• Unlike CHAR, it only uses as much storage as needed for the actual string plus a small overhead, not the full declared length.
• If a value exceeds the specified maximum length, the database will usually throw an error or truncate based on configuration. Use cases: general text fields like names, emails, addresses, titles, etc.

3. INT

• INT is used to store whole numbers (integers).
• Exact range depends on the database system and whether it is signed or unsigned, but it typically supports a large range of positive and negative values suitable for IDs, counts, and similar fields. Use cases: IDs, counts, quantities, ages, and other whole-number values

4. TINYINT

• TINYINT is a smaller integer type with a limited range.
• In MySQL, for example, TINYINT has a range of -128 to 127 when signed, and 0 to 255 when unsigned.
• It is useful when you know values will always remain within a small range, which can save space. Use cases: small flags, status codes, or compact numeric values.

5. FLOAT

• FLOAT is a floating-point data type used to store approximate decimal numbers.
• It is called single-precision because it stores numbers using fewer bits, which means less precision compared to DOUBLE.
• In most databases, FLOAT can accurately represent about 6–7 significant digits.
• Because values are stored in binary form, some decimal numbers cannot be represented exactly, which may result in small rounding errors.
• Due to this approximation, FLOAT should not be used for values where exact precision is required (such as money). Use cases: Measurements, percentages, averages, sensor data, or scientific values where minor rounding differences are acceptable.

6. DOUBLE

• DOUBLE (or DOUBLE PRECISION) is a floating-point data type used to store approximate decimal numbers with higher precision than FLOAT.
• It uses more bits to store each value, allowing for about 15–16 significant digits of precision in most systems.
• DOUBLE supports a wider range and greater accuracy than FLOAT, making it suitable for more complex calculations.
• Like FLOAT, it still stores values approximately, so exact precision is not guaranteed. Use Cases: Scientific computations, complex calculations, analytics, and scenarios where higher precision is needed but exact decimal accuracy is not mandatory.

7. Boolean

• Conceptually, a Boolean represents two logical values: true and false.
• Many SQL dialects have a BOOLEAN or BOOL type; however, the actual storage may be implemented internally as a small integer (for example, TINYINT(1) in MySQL).
• In systems that represent Boolean as integers, the common convention is 0 for false and 1 for true. Use cases: flags like is_active, is_deleted, is_admin, etc.

8. DECIMAL / NUMERIC

• DECIMAL (also called NUMERIC in many SQL dialects) is used to store exact decimal numbers.
• Unlike FLOAT and DOUBLE, DECIMAL stores values in base-10, which means decimal numbers are stored exactly as written, without rounding errors.
• DECIMAL (p, s) defines precision (total digits) and scale (digits after decimal).

Rule of thumb:
Use FLOAT/DOUBLE for measurements and calculations, but DECIMAL for money and financial data.

Use cases: Monetary values, prices, salaries, financial calculations, totals, taxes, and any scenario where precision must be exact.

Signed and unsigned integers
Sometimes you know a column will never store negative values, such as IDs, counts, or quantities.
• Signed integers can store both negative and positive values (for TINYINT in MySQL: -128 to 127).
• Unsigned integers only store non-negative values but shift the range upward (for TINYINT in MySQL: 0 to 255).
Example:

_id TINYINT UNSIGNED

Here, _id can only store values from 0 to 255, which is enough for a small set of teacher IDs and saves space compared to a larger integer type.
You can mention that more will be covered under constraints later when you discuss things like NOT NULL, UNIQUE, PRIMARY KEY, and CHECK.

Types of Commands in SQL
We know that SQL is a language which is used to interact with database, there are various types of commands with each having its own function, now these commands can be classified into 5 types, and these are –

1. DDL (Data Defining Language)
Purpose: Define and manage database structure (tables, schemas).
Examples:

• CREATE - create tables/databases
• ALTER - modify existing tables
• DROP - delete tables/databases
• TRUNCATE - delete all data (keep structure)
• RENAME - rename objects

2. DQL (Data Query Language)
This is used to retrieve data from tables. We can apply conditions too, to get specific data.
Examples:

• SELECT - fetch data with conditions, joins, etc.

3. DML (Data Manipulation Language)
This is used to manipulate the data present/stored inside the table. We can CRUD operations;
Examples:

• INSERT - add new data
• UPDATE - modify existing data
• DELETE - remove data

4. DCL (Data Control Language)
This is used to grant and revoke permissions to users. As the data grows, we may also add sensitive data in it or there may be data which isn’t needed by one user. So, we can limit what the user is allowed to access to maintain security.
Examples:

• GRANT - give permissions
• REVOKE - remove permissions

5. TCL (Transaction Control Language)
One of the most important concepts, although it is used massively in applications. Where it ensures that whole operation is done as per the instructions other wise revert back to the last check point
Examples:

• BEGIN/COMMIT - save changes
• ROLLBACK - undo changes
• SAVEPOINT - partial rollback points

Basic Command (Create table)
As we know that SQL is a language and everything is done using commands. So here is the most basic command. Which will help us to understand other concepts.

List all Databases

SHOW DATABASES;

Use a specific database

USE database_name

List all tables in current DB

SHOW TABLES;

View table structure

DESC table_name

DESC students;

Create table (DDL)

CREATE TABLE table_name(
   column_1 datatype constraint,
   column_2 datatype constraint,
   column_3 datatype constraint, 
);

Ex-

CREATE TABLE student(
   Id INT PRIMARY KEY AUTO_INCREMENT,
   first_name VARCHAR(50) NOT NULL,
   last_name VARCHAR(50) NOT NULL,
   phone VARCHAR(15),
   age INT NOT NULL
);

NOTE-
Here PRIMARY KEY is a key
AUTO_INCREMENT, NOT NULL are constraints
We will learn about these in future.

DELETE DATABASE/TABLE

DROP DATABASE database_name;

DROP TABLE table_name;

KEYS
PRIMARY KEY (always unique)
A column (or set of columns) in a table that uniquely identifies each row (a unique id)
Only one Primary key can be there in a table and it can’t be null.

Ex-

CREATE TABLE students(
   id INT PRIMARY KEY,
   ……
);

FOREIGN KEY
It’s always a good idea to divide data into small tables, rather than keeping everything inside a single table which is a monolith architecture. Now as we divide the tables, we need to establish some sort of relation in between them which allows us to get the data. FOREIGN key in a table refers to the PRIMARY key of another table and helps in establishing that link between tables.
FOREIGN KEY References PRIMARY KEY of another table
A table can have multiple FKs.
Foreign keys may contain null or duplicate values.

• Parent table = table with PRIMARY KEY
• Child table = table with FOREIGN KEY

Example

A teacher table is in relation with department table.

CREATE TABLE dept(
   id INT PRIMARY KEY,
   name VARCHAR(50)
);  

CREATE TABLE teacher(
   id INT PRIMARY KEY,
   name VARCHAR(50),
   dept_id INT,

   FOREIGN KEY (dept_id) REFERENCES dept(id);
);

Here dept is the Parent table while teacher is the Child table (using Foreign Key) and dept_id in teacher table is the FOREIGN KEY, which establishes relation between these two tables.

CASCADE in FOREIGN key
FOREIGN key is used to set up relation between tables. Now there may be a need of change in both tables if anything is changed in parent table, for that we use CASCADING

So, any change(s) in parent table is automatically reflected in children table too.

• ON UPDATE CASCADE → Parent ID changes → Child FK updates automatically
• ON DELETE CASCADE → Parent row deleted → Child rows deleted automatically

CREATE TABLE teachers(
   Id INT PRIMARY KEY AUTO_INCREMENT,
   Name VARCHAR(50),
   dept_id INT,

   FOREIGN KEY (dept_id) REFERENCES dept(id)

   ON UPDATE CASCADE
   ON DELETE CASCADE
);

CONSTRAINTS
Now let’s talk about CONSTRAINTS, while creating a table we can specify some rules for the columns in the table. Like the values can never be null, we can specify the length, allow duplicate values or not. All these rules are applied using CONSTRAINTS.

Some Commonly used CONSTRAINTS
1. NOT NULL – This is used to specify that the column can’t be null. There should be a value in it all the time.
2. UNIQUE – In case we want to have unique values in a column, then we use this.
3. DEFAULT – This is used to set a default value, in case no value is provided, then the set default value is used.
4. CHECK - This is used to check or set the permissible value in the column

Ex.

CREATE TABLE student (
   _id INT PRIMARY KEY,
   first_name VARCHAR (50) NOT NULL,
   last_name VARCHAR (50) NOT NULL,
   phone VARCHAR (15) NOT NULL UNIQUE,
   age INT CHECK (age>=18),
   city DEFAULT 'ABCD'
);

SELECT statement
Till now we learned about databases, their types, data types, types of commands, how to create a table, concept of keys and constraints. Now we will dive into the ocean of commands, how we can actually use the commands and play with data and to do so, first and foremost thing we have is the SELECT statement, it is used to retrieve data from the table and return it.

SELECT col1, col2, col3 FROM table_name;

SELECT * FROM student;

Here the above command will return whole table as the result, * means all.

Targeting specify column in a table.

SELECT id, name, city FROM students;

Above command will return a table with only id, name and city column.

Column aliases (AS):
In case we want to change “name” to “first_name” in the resultant table, then we can use AS command to do so.

SELECT id, name AS first_name, city FROM students;

In the resultant table, now the columns will be id, first_name, city. But this won’t affect the actual table in anyways.

CONDITIONS – WHERE clause
What if we need specific data from the table, which satisfies certain conditions. To do so, we use WHERE clause with the SELECT statement. WHERE clause allows us to set conditions.

SELECT col1, col2, col3, col4…   FROM table_name WHERE condition;

WHERE clause doesn’t work alone, conditions are set with the help of OPERATORS.

OPERATORS
OPERATORS are the backbone of SQL, as they allow us to actually set the conditions as per our requirements.
There are various OPERATORS to choose from, and these are the most commonly and important to know OPERATORS.

1. Arithmetic OPERATORS: *, -, +, /, %

SELECT name, age/2 AS age_divided_by_two FROM students;

SELECT *, age-4 as MINOR FROM students WHERE age-4<18;

2. Comparison OPERATORS: =, !=, >, >=, <, <=
These are used to do comparison
*Note: != means not equal to

SELECT * FROM students WHERE city='New York' AND age>21;

3. BITWISE OPERATORS: & (Bitwise AND), | (Bitwise OR)

4. Logical OPERATORS: AND, OR, NOT, IN, BETWEEN, ALL, LIKE.
These are logical in nature

• AND operator: This checks if both (right and left) conditions are true, then only it satisfies the conditions.

SELECT * from students
WHERE name='John' AND age>26;

• OR operator: Similar to AND operator but this checks if either of the conditions is true.

SELECT * FROM students
WHERE name='Wick' OR age>50;

• BETWEEN operator: Used to set a range for the result.

SELECT * FROM students
WHERE marks BETWEEN 80 AND 92;

• IN operator: It matches any value specified inside it.

SELECT * FROM students
WHERE city IN ('New York', 'Moscow', 'New Delhi')

This will only return the rows, where the city is New York, Moscow, or New Delhi only.

• NOT: To negate the given condition

SELECT * FROM students
WHERE city NOT IN ('New York', 'Moscow', 'New Delhi')

This will return all the rows except the rows where the city is New York, Moscow, or New Delhi

• LIMIT: Used to set the limit of returned rows of the table.

SELECT * FROM students LIMIT 5;

Now, the result will contain only 5 rows at a time, even if it contains 100 rows. It will only be returning 5 rows at a time.

• OFFSET: This one is used to skip certain values and start after them.

LIMIT and OFFSET are used together and these two OPERATORS make pagination in web page possible.

• ORDER BY: This gives us the facility to sort the result as per our choice. Either Descending or Ascending.

SELECT * FROM table_name 
ORDER BY col_name ASC

ASC= Ascending
DESC= Descending

SELECT * FROM students
WHERE city NOT IN ('Rome', 'Tokyo') 
ORDER BY name DESC
LIMIT 2 OFFSET 2;

AGGREGATE FUNCTIONS
These are prebuilt functions/methods which helps us by easing our work, these returns single value as a result.

1. COUNT() - Counts the number of entries
2. MAX() - Maximum value in column
3. MIN() - Minimum value in column
4. SUM() - Total sum of values
5. AVG() - Average of values.

Find the max age-

SELECT MAX(age) as MaxAge FROM students;

     OR

SELECT age FROM students ORDER BY age DESC LIMIT 1;

GROUP BY clause
This is used to group rows that have same values into summary rows.
Generally used with some aggregate function. Both of these goes by hand in hand.

SELECT SPID, SUM(amount) 
FROM sales 
GROUP BY SPID 
ORDER BY SPID ASC;

HAVING clause
Similar to WHERE clause but used when we want to apply condition after grouping.
It was introduced to address the limitations of WHERE clause, which cannot be used with aggregate functions.

SELECT city , COUNT(name)
FROM student
GROUP BY city 
HAVING max(marks)>90;

GENERAL SEQUENCE/ORDER of writing query

SELECT columns(s)
FROM   table_Name
WHERE condition
GROUP BY column(s)
HAVING condition
ORDER BY column(s) ASC/DESC

SELECT city
FROM   students
WHERE grade='A'
GROUP BY city
HAVING MAX(marks) > 90
ORDER BY city ASC/DESC

TABLE Related Queries
1. Create Table

CREATE TABLE table_name(
   column_1  datatype constraint,
   column_2  datatype constraint,
   column_3  datatype constraint, 
);

Ex-

CREATE TABLE student(
   Id INT PRIMARY KEY AUTO_INCREMENT,
   first_name VARCHAR(50) NOT NULL,
   last_name VARCHAR(50) NOT NULL,
   phone VARCHAR(15),
   age INT NOT NULL 
);

2. Insert data

INSERT INTO tablename(col1,col2,col3,….) 
VALUES(val1,val2,val3,….);

INSERT INTO students(id,first_name,last_name,phone,age)
VALUES(1, 'John', 'Wick', '7878787878', 35);  

In case you know the order then you may omit the column names

INSERT INTO students
VALUES(1, 'John', 'Wick', '7878787878', 35);

Notice that we have used AUTO_INCREMENT for id. Which means that the value will increase by its own. To do that, we have to use the columns name and omit the AUTO_INCREMENT column.

INSERT INTO students (first_name, last_name, phone, age) 
VALUES ('John', 'Wick', '7878787878', 35)

Here, id will be populated on its own.

Insert more than one value/row in table

INSERT INTO students(first_name, last_name, phone, age)
VALUES ('John','Wick', '7878787878', 35),
   ('Super', 'Man', '8456985',20),
   ('Bat', 'Man', '2234234',45),
   ('Iron', 'Man', '23584839', 55),
   ('Captain', 'America', '284738473/, 50);

3. Update table

UPDATE table_name 
SET col1=val1 
WHERE condition

UPDATE student 
SET age=44 
WHERE first_name='SUPER';

4. Delete data

DELETE FROM table_name
WHERE condition;

DELETE FROM students 
WHERE first_name= 'Delete';

5. ALTER TABLE

• ADD column

ALTER TABLE table_name
ADD COLUMN col_name datatype constraint;

ALTER TABLE students
ADD COLUMN grades VARCHAR(2) NOT NULL;

• DROP/DELETE Column

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE students
DROP COLUMN grades;

• RENAME table

ALTER TABLE table_name
RENAME TO newtable_name;

ALTER TABLE students
RENAME TO newStudents;

• CHANGE column

ALTER TABLE table_name
CHANGE COLUMN old_name new_name new_datatype new_constraint;

ALTER TABLE students 
CHANGE COLUMN last_name lastName VARCHAR(20) NOT NULL;

• MODIFY column (modify datatype/constraint)

ALTER TABLE table_name
MODIFY col_name new_datatype new_constraint;

ALTER TABLE students
MODIFY phone VARCHAR(15) UNIQUE;

• DELETE TABLE data (only table data, not whole table)

TRUNCATE table_name;

TRUNCATE students;

JOINS
One of the most important topics, these are used to combine rows from two or more tables, based on a related column between them.

Types

1. Inner Join

2. Outer Join

• Left Join
• Right Join
• Full Join

1. INNER JOIN
Returns records that have matching values in both tables (only mutual data is returned)

SELECT column(s) FROM table_A 
INNER JOIN table_b 
ON tableA.col_name = tableB.col_name;

SELECT * FROM students
INNER JOIN course
ON student.student_id = course.student_id;

*This will return only the data which is present in both tables

OUTER JOINS
1. LEFT Join

Returns all records from left table and matched records from the right table.

SELECT column(s)
FROM tableA
LEFT JOIN tableB
ON tableA.col_name = tableb.col_name;

2. RIGHT Join
Same as LEFT join, only side changes. This return all records from right table and matched records from left table.

SELECT column(s) 
FROM tableA
RIGHT JOIN tableB
ON tableA.col_name = tableb.col_name;

3. FULL Join
Returns all records even if there is no relation.
By default, MySQL doesn't support FULL JOIN so we do it through another method and with using UNION.

UNION
Helps in combining data and returns unique values only.

FULL JOIN = LEFT JOIN _UNION _RIGHT JOIN

SELECT * FROM tableA as A
LEFT JOIN tableB as B
ON A.column = B.column

UNION

SELECT * FROM tableA as A
RIGHT JOIN tableB as B
ON a.column = b.column;

??Write SQL command to display the left exclusive join: -

SELECT * FROM tableA as A
LEFT JOIN tableB as B
ON A.col_name = b.col_name
WHERE b.id IS NULL;

*Similar for RIGHT exclusive Join.

SELF JOIN
A regular join but the table is joined with itself

SELECT column(s)
FROM table as A
JOIN table as B
ON A.col_name = B.col_name;

UNION
Used to combine the result-set of two or more SELECT statements.
Gives unique records.
*Columns must have similar data types.

SELECT column(s) FROM tableA
UNION
SELECT column(s) FROM tableB;

*We also have UNION all, this allows duplicates on top of UNION's data

SUBQUERY
Subquery or inner query or a nested query is a query within another SQL query. This allows us to do complex operations which isn't or would be hard to do otherwise.

SELECT column(s)
FROM table_name
WHERE col_name operator/condition(subquery)

SELECT name, marks
FROM student
WHERE marks > (SELECT AVG (marks) FROM student);

MySQL Views
It's a virtual table created from an actual/real table.
Imagine a student table with lots of columns. Now a teacher only needs students roll number, name, marks. Other data isn't needed to the teacher.

So, a view will be created from the student table which will contain only these columns.

Although it's a virtual table but it will act just like a real table, operations, select command etc.

CREATE VIEW

CREATE VIEW view_name AS 
SELECT column(s) 
FROM table_name;

CREATE VIEW view1 AS 
SELECT rollno, name, marks 
FROM student


SELECT * FROM view1;

DROP VIEW

DROP VIEW view_name;

DROP VIEW view1;