All you need to know about SQL Queries

In this article, we will cover how to write SQL commands, along with a few tips and things to keep in mind while writing queries, and how to write optimized queries.

SQL commands are categorized into different sublanguages such as DDL, DQL, and DML.

Let’s understand.

DDL (Data Definition Language)

Whenever we are talking about the structure of data, like creating, modifying, or deleting tables, we are dealing with DDL.

It is not about actual data inside the table, but about how the data is structured. Things like defining tables, altering columns, or deleting tables come under DDL.

Commands in DDL include:

• CREATE

• ALTER

• DROP

• TRUNCATE

So whenever you are defining or changing the structure of your database, you are using DDL.

Building you first table

Let’s create a table:

CREATE TABLE matrimony (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100),
  email VARCHAR(322) UNIQUE NOT NULL,
  phone_number VARCHAR(10) UNIQUE,
  country_code VARCHAR(4),
  gender VARCHAR(20) CHECK (gender IN ('male','female','prefer not to say')),
  age INT CHECK(age > 21),
  no_of_rejections INT DEFAULT 0 CHECK (no_of_rejections >= 0),
  enrollment_date DATE DEFAULT CURRENT_DATE
);

Understanding the Keywords

• SERIAL → auto-increment integer (handled internally using a sequence)

• PRIMARY KEY → uniquely identifies each row + creates index

• VARCHAR(n) → variable-length string (max n)

• UNIQUE → no duplicate values allowed

• NOT NULL → field must have a value

• DEFAULT → assigns value automatically if not provided

• CHECK → constraint to validate data

Important Insight (this is gold for interviews)

Always think in terms of bytes and disk I/O, not just data types.

• Every column → consumes storage

• More bytes → more disk reads → slower queries

• Fixed types (INT) are faster for computation

• Variable types (VARCHAR) are flexible .

So choice is:

Correctness first, then optimization

How id SERIAL auto-increments internally

When you use:

id SERIAL PRIMARY KEY

PostgreSQL actually does:

1. Creates a hidden sequence matrimony_id_seq

2. Sets default: DEFAULT nextval('matrimony_id_seq')

3. On every insert: Database calls nextval() gets next number and assigns it to id

So internally:

It’s not magic it’s a sequence generator.

DDL Operations

ALTER (modify structure)

Used when you want to change schema without deleting table.

Examples:

ALTER TABLE matrimony ADD COLUMN city VARCHAR(50); // add

ALTER TABLE matrimony RENAME COLUMN first_name TO fname; // rename

TRUNCATE vs DROP (very important difference)

TRUNCATE

TRUNCATE TABLE matrimony; 

• Removes all data

• Keeps table structure

• Very fast (no row-by-row delete)

• Minimal logging

In Disk level it deallocates data pages quickly and doesn’t scan each row .

DROP

DROP TABLE matrimony; 

• Deletes entire table + structure

• Everything is gone

In Disk level it removes metadata + data files and frees storage completely

When to use what

• Use TRUNCATE → when you want empty table but keep structure

• Use DROP → when you don’t need table at all

• Use ALTER → when structure needs change Final Insight (this connects everything)

All these operations ultimately affect disk layout:

CREATE → allocates structure on disk ALTER → modifies metadata + sometimes rewrites data TRUNCATE → clears data pages efficiently DROP → removes everything

So always think:

SQL is just syntax, but real work is happening on disk.

DML (Data Manipulation Language)

Whenever we are talking about the actual data inside the table, like inserting, updating, or deleting records, we are dealing with DML.

It is not about the structure, but about the data itself. So whenever you add new data, modify existing data, or remove data from a table, that comes under DML.

Commands in DML include:

• INSERT

• UPDATE

• DELETE

INSERT INTO matrimony (first_name, email, age)
VALUES ('Saurav', 'saurav@gmail.com', 25);

Here we are inserting actual data into the table.

UPDATE matrimony
SET age = 26
WHERE email = 'saurav@gmail.com';

Here we are modifying existing data.

DELETE FROM matrimony
WHERE email = 'saurav@gmail.com';

Here we are removing data from the table.

Important insight

DML operations are expensive compared to reads because they directly affect disk.

• INSERT → writes new data

• UPDATE → reads + writes (can rewrite rows)

• DELETE → marks data as deleted (later cleaned by database)

So always be careful with:

• missing WHERE clause

• large updates/deletes

DQL (Data Query Language)

Whenever we are talking about reading or fetching data, we are dealing with DQL.

This is the most commonly used part of SQL because most of the time applications are reading data.

Command in DQL:

• SELECT

Think of this in your matrimony system

Your table = users looking for matches
Each row = one person profile

Now SQL is how you query these profiles.

Filtering (WHERE) — find specific profiles

SELECT * FROM matrimony
WHERE age > 21 AND gender = 'female';

Meaning:

You are saying:

“Give me profiles where age is greater than 21 and gender is female”

Without WHERE, database will scan all profiles → more disk reads.

Pagination (LIMIT & OFFSET)

SELECT * FROM matrimony
LIMIT 10 OFFSET 20;

What it means:

• LIMIT 10 → give me 10 profiles

• OFFSET 20 → skip first 20 profiles

Imagine users are scrolling profiles:

• Page 1 → first 10 profiles

  LIMIT 10 OFFSET 0
  

• Page 2 → next 10 profiles

  LIMIT 10 OFFSET 10
  

OFFSET = how many profiles to skip

Aggregation (GROUP BY)

SELECT gender, COUNT(*)
FROM matrimony
GROUP BY gender;

Meaning:

“How many male, female, and others are there?”

Database groups profiles and then counts.

More examples

SELECT AVG(age) FROM matrimony;

→ average age of users

SELECT MAX(age) FROM matrimony;

→ oldest user

HAVING — filter after grouping

SELECT gender, COUNT(*)
FROM matrimony
GROUP BY gender
HAVING COUNT(*) > 10;

Meaning:

“Show only those gender groups where count is more than 10”

Simple difference

• WHERE → filters rows

• HAVING → filters grouped results

Pattern Matching (LIKE)

SELECT * FROM matrimony
WHERE first_name LIKE 'Sa%';

Meaning:

“Find profiles where name starts with ‘Sa’”

Examples

• 'S%' → starts with S

• '%v' → ends with v

• '%au%' → contains "au"

• '__u%' -> third letter "u"

Tips and Tricks (Important for Writing Better SQL)

1. Using Alias (creating columns on the fly)

SELECT first_name, age, (age + 5) AS future_age
FROM matrimony;

• (age + 5) is computed on the fly

• AS future_age creates a temporary column in the result

2. Alias for Table

SELECT m.first_name, m.age
FROM matrimony m;

• Makes queries shorter and cleaner

• Very useful in joins

3. Single Quote vs Double Quote

• ' ' → string/value

• " " → column/table name (identifier, especially in PostgreSQL)

SELECT * FROM matrimony
WHERE first_name = 'Saurav';

SELECT "first_name" FROM matrimony;

4. Avoid SELECT *

SELECT * FROM matrimony;

Better:

SELECT first_name, age FROM matrimony;

• Reduces disk reads

• Improves performance

5. Always Use WHERE Carefully

UPDATE matrimony SET age = 30;

This updates the entire table ❌

UPDATE matrimony
SET age = 30
WHERE id = 1;

• Missing WHERE = dangerous

6. Indexing

CREATE INDEX idx_email ON matrimony(email);

• Without index → full table scan

• With index → faster lookup

7. Use LIMIT While Testing

SELECT * FROM matrimony LIMIT 10;

• Prevents loading large data

8. Filter Early

SELECT * FROM matrimony WHERE age > 21;

• Reduces unnecessary data scan

9. Be Careful with LIKE

SELECT * FROM matrimony
WHERE first_name LIKE '%au%';

• % at the start → cannot use index → slow

Better:

SELECT * FROM matrimony
WHERE first_name LIKE 'Sa%';

10. COUNT Can Be Expensive

SELECT COUNT(*) FROM matrimony;

• On large tables, scans data → can be slow

Final Thoughts

• SQL is not just about writing queries, it’s about understanding how data flows and how efficiently you interact with it.

• The better you think in terms of data and disk, the better your queries and systems will perform.