✅ DISTINCT is used to:

Remove duplicate rows from the result set.

• It ensures that the output has only unique rows — no duplicates.
• It compares all selected columns — if all columns in a row match, it’s considered a duplicate.

---

When Do You Use DISTINCT?

• When you want to get only unique values.
• When you want to avoid duplicates in your results.
• When you’re doing reporting or analytics and you need clean, non-repeating data.

Example

Imagine a Products table:

product_id	category
1	Electronics
2	Books
3	Electronics
4	Books
5	Furniture

---

Without DISTINCT

SELECT category FROM Products;

Result:

category
Electronics
Books
Electronics
Books
Furniture

(You get duplicates.)

---

With DISTINCT

SELECT DISTINCT category FROM Products;

Result:

category
Electronics
Books
Furniture

✅ Only unique categories are shown — duplicates are removed.

How DISTINCT Works

• SQL compares all selected columns together.
• If two rows are exactly the same in all selected columns, only one is kept.

Example with two columns

SELECT DISTINCT product_id, category FROM Products;

Now, it looks at the combination of product_id and category — if they’re unique together, the row stays.

Key Points

Point	Meaning
Removes duplicates	Returns only unique rows.
Works on all selected columns	Duplicates are based on all selected columns.
Often used with COUNT(DISTINCT column)	To count unique values.

Important Tip

• DISTINCT is expensive for large datasets because SQL must sort or hash to find duplicates.
• If you don’t need uniqueness, avoid it for better performance.

In Short

DISTINCT is used to remove duplicate rows and make your result set contain only unique rows.

What is the Default Sort Order of ORDER BY?

✅ By default, ORDER BY sorts in:

Ascending (ASC) order — from smallest to largest, A → Z, 0 → 9.

---

If you don’t specify anything, this:

SELECT * FROM Employees
ORDER BY salary;

is the same as:

SELECT * FROM Employees
ORDER BY salary ASC;

✅ Default is ASC — Ascending:

• Numbers: lowest to highest (1, 2, 3, 4...).
• Text: alphabetical (A, B, C...).
• Dates: earliest to latest (2023-01-01, 2023-01-02…).

---

What if you want descending order?

You explicitly specify:

ORDER BY column_name DESC

Numbers: highest to lowest.

Text: Z → A.

Dates: latest to earliest.

Quick Example

name	salary
Alice	5000
Bob	7000
Carol	6000

Default ORDER BY salary:

SELECT * FROM Employees ORDER BY salary;

Result:

name	salary
Alice	5000
Carol	6000
Bob	7000

✅ Sorted ascending — lowest to highest salary.

---

Explicit ORDER BY salary DESC:

SELECT * FROM Employees ORDER BY salary DESC;

Result:

name	salary
Bob	7000
Carol	6000
Alice	5000

✅ Sorted descending — highest to lowest salary.

In Short

ORDER BY without ASC/DESC sorts in ASC (ascending) order by default.

It depends a little on the SQL dialect (MySQL, PostgreSQL, SQL Server, etc.), but the general idea is:

---

1. Using LIMIT (MySQL, PostgreSQL, SQLite)

SELECT * FROM table_name
ORDER BY column_name
LIMIT N;

✅ Example — get top 5 employees with highest salary:

SELECT * FROM Employees
ORDER BY salary DESC
LIMIT 5;

ORDER BY salary DESC — sort by salary, highest first.

LIMIT 5 — return only the first 5 rows.

2. Using TOP (SQL Server)

SELECT TOP N * FROM table_name
ORDER BY column_name DESC;

✅ Example — top 3 customers by total purchases:

SELECT TOP 3 * FROM Customers
ORDER BY total_purchases DESC;

3. Using FETCH FIRST N ROWS ONLY (Standard SQL, Oracle, DB2, PostgreSQL 13+)

SELECT * FROM table_name
ORDER BY column_name
FETCH FIRST N ROWS ONLY;

✅ Example — top 10 students by score:

SELECT * FROM Students
ORDER BY score DESC
FETCH FIRST 10 ROWS ONLY;

Very standard SQL way.

Supported by Oracle (12c+), DB2, PostgreSQL 13+.

Why Use ORDER BY with LIMIT / TOP?

Because:

• LIMIT N without ORDER BY just returns random N rows depending on how the DB scans the table.
• ORDER BY makes it meaningful — like top salaries, earliest dates, highest scores.

Bonus: Skip First N Rows (Pagination)

You can also skip rows — useful for pagination:

✅ In MySQL/PostgreSQL:

SELECT * FROM Employees
ORDER BY salary DESC
LIMIT 5 OFFSET 10;   -- Skip first 10, fetch next 5

✅ In SQL Server (2012+):

SELECT * FROM Employees
ORDER BY salary DESC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

In Short

Database	Top N Syntax
MySQL, PostgreSQL, SQLite	LIMIT N
SQL Server	TOP N
Oracle, DB2, PostgreSQL 13+	FETCH FIRST N ROWS ONLY

✅ Always use ORDER BY to get meaningful Top N results.

---

Example

📝 Top 5 best-selling products:

SELECT product_name, SUM(quantity) AS total_sold
FROM Sales
GROUP BY product_name
ORDER BY total_sold DESC
LIMIT 5;

DELETE vs TRUNCATE vs DROP

Command	What it Does	Speed	Can Rollback?	Affects Structure?
DELETE	Deletes specific rows based on WHERE condition (or all if no condition).	Slow for big tables	✅ Yes (if in a transaction)	❌ No — table stays.
TRUNCATE	Deletes all rows from the table — no WHERE clause. Faster than DELETE.	⚡ Very fast	🚫 No (auto-commit in many DBs)	❌ No — table stays.
DROP	Deletes the entire table (structure + data) permanently.	⚡ Very fast	🚫 No (permanent)	✅ Yes — removes the table itself.

1. DELETE

• Used to remove specific rows:

DELETE FROM Users WHERE age > 50;

You can delete all rows:

DELETE FROM Users;

Slow for big tables because it deletes row by row, firing triggers, checking constraints, writing to logs.

Can be rolled back if inside a transaction:

BEGIN;
DELETE FROM Users WHERE age > 50;
ROLLBACK; -- Undo

• Table remains — just with fewer rows.

---

2. TRUNCATE

• Removes all rows from the table — no WHERE allowed.

TRUNCATE TABLE Users;

• Much faster than DELETE — it doesn’t scan rows individually.
• Cannot specify conditions — it’s all-or-nothing.
• Auto-commits in most databases — cannot rollback in most systems (depends on the DB).
• Resets auto-increment counters in many databases.
• Keeps the table structure — just deletes all the data.

✅ TRUNCATE is like a quick emptying of the table.

3. DROP

• Removes the entire table, both structure and data:

DROP TABLE Users;

Cannot rollback — it’s a hard delete.

Table is gone — you can’t query it anymore.

If there are foreign key constraints, you may have to DROP those first or disable them.

✅ DROP is like deleting the whole file from disk.

Summary

Feature	DELETE	TRUNCATE	DROP
Deletes specific rows?	✅ Yes (WHERE)	🚫 No — all rows only	🚫 No — deletes whole table
Deletes all rows?	✅ Yes (without WHERE)	✅ Yes (always)	🚫 No — deletes table entirely
Table structure remains?	✅ Yes	✅ Yes	❌ No — table gone
Rollback possible?	✅ Yes (with transaction)	🚫 No (auto-commit)	🚫 No
Speed	🐢 Slow (row-by-row)	⚡ Fast (bulk delete)	⚡ Fast (object drop)
Resets auto-increment?	🚫 No	✅ Yes	❌ (Table is deleted)

---

Real-Life Analogy

Action	Analogy
DELETE rows	Throwing specific books out of a shelf.
TRUNCATE table	Emptying the whole shelf but keeping the shelf.
DROP table	Throwing the entire shelf away — no books, no shelf.

---

⚠️ Caution

• TRUNCATE and DROP are usually DANGEROUS in production — because they can’t be easily undone.
• DELETE is safer (you can control what to remove and rollback).

1. Uniqueness: ✅
   • No two rows can have the same value (or same combination, for composite keys).
2. NOT NULL: ✅
   • No NULLs allowed in any column that is part of the Primary Key.
   • Every row must have a real, non-missing value for the Primary Key.
3. Automatic Index: ✅
   • A unique index is automatically created behind the scenes.
   • This makes finding, updating, and deleting rows much faster.
4. Data Integrity: ✅
   • Ensures your data has a reliable way to uniquely identify each row.
5. Foreign Key Relationships: ✅
   • Other tables can create foreign keys pointing to this Primary Key.
   • This is how you build relationships between tables (like Users → Orders).

📝 In Short

Feature	Primary Key Ensures
Uniqueness	No duplicate key values.
Not Null	No NULL allowed in the key.
Fast Lookup	Creates a unique index for fast access.
Data Integrity	Each row is reliably identified.
Relationships	Other tables can refer to it (foreign keys).

---

Example

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    name VARCHAR(100)
);

✅ What happens now:

• user_id must be unique.
• user_id cannot be NULL.
• A unique index on user_id is created automatically.
• You can easily do:

user_id must be unique.

user_id cannot be NULL.

A unique index on user_id is created automatically.

You can easily do:

SELECT * FROM Users WHERE user_id = 123;

and it will be fast.

So — to your question:

When I define a primary key, it means I cannot insert a record with the same primary key, and that is it?

✅ YES — plus:

• No NULLs allowed in that column.
• Fast searching thanks to the index.
• Safe relationships via foreign keys.

It’s not only about uniqueness — it’s about uniqueness + not null + fast access + relational integrity.

---

Visual Analogy

Primary Key is like a passport number:

• Unique — no two people can have the same passport number.
• Mandatory — you must have one (can’t be NULL).
• Index — the government database can quickly find you using it.
• Link — other systems (airlines, visa offices) can reference it.

What is a Composite Key?

A Composite Key is a Primary Key (or sometimes a Unique Key) that is made up of two or more columns.

It’s called composite because it’s combined from multiple columns to uniquely identify a row.

---

Why Use a Composite Key?

• Sometimes, no single column can uniquely identify a row.
• But a combination of columns together can.

✅ Composite Key ensures that no two rows have the same combination of values in these columns.

---

Example

Let’s say you have a table of course enrollments:

student_id	course_id
1	101
1	102
2	101

• student_id alone is not unique — a student can enroll in many courses.
• course_id alone is not unique — many students can enroll in the same course.
• But (student_id, course_id) together are unique — a student can’t enroll twice in the same course.

CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)   -- Composite Primary Key
);

This means:

• No two rows can have the same student_id and course_id together.
• Combination must be unique.

---

Composite Key vs Single Column Key

Single Column Key	Composite Key
One column uniquely identifies the row.	Combination of multiple columns uniquely identifies the row.
Simple PRIMARY KEY (id)	Composite PRIMARY KEY (col1, col2, ...)
Example: user_id	Example: student_id + course_id

---

Real-Life Analogy

Think of:

• First Name = not unique.
• Last Name = not unique.
• First Name + Last Name = more unique, but still not perfect.
• First Name + Last Name + Date of Birth = very likely unique — a composite key!

---

Important Rules About Composite Keys

• All columns together must be NOT NULL.
• All columns are treated as one unit for uniqueness.
• You can still have other UNIQUE constraints on other columns.

---

In Short

A Composite Key is a Primary Key or Unique Constraint made up of multiple columns that together uniquely identify a row.

Absolutely YES! ✅

You can GROUP BY many columns — and it’s very common in real-world SQL queries!

---

GROUP BY Many Columns — How It Works

When you group by multiple columns:

• SQL will create a separate group for each unique combination of those columns.

---

Example

Imagine a table Sales:

sale_id	region	year	amount
1	North	2024	100
2	North	2025	200
3	South	2024	150
4	North	2024	50
5	South	2025	300

---

Query: Group by region and year

SELECT region, year, SUM(amount) AS total_sales
FROM Sales
GROUP BY region, year;

➡️ Result:

region	year	total_sales
North	2024	150
North	2025	200
South	2024	150
South	2025	300

✅ SQL groups rows by both region and year together:

• All rows where region = 'North' and year = 2024 → one group.
• All rows where region = 'South' and year = 2025 → another group.

Key Points When Grouping by Many Columns

Point	Explanation
Group by combinations	Each unique combination of the columns becomes one group.
SELECT must match GROUP BY	Columns you SELECT must either be: grouped columns or aggregates (SUM(), AVG(), etc.).
Order of columns matters sometimes	SQL Engine may organize results based on grouping order.

---

More Complex Example

SELECT region, year, AVG(amount) AS avg_sales
FROM Sales
GROUP BY region, year
ORDER BY region, year;

✅ Calculates average sales for each region-year combination, ordered nicely.

---

In Short

Yes, you can GROUP BY multiple columns — it groups by their combinations.

---

Real-Life Analogy

Imagine school grades:

• You group students by class and year.
• Find the average grade per class and year.

Just like:

GROUP BY class, year

The GROUP BY clause is used to:

Group rows that have the same values in specified columns into summary rows — like totals, counts, averages, etc.

✅ It’s most commonly used with aggregate functions like:

• SUM()
• COUNT()
• AVG()
• MAX()
• MIN()

---

Why Use GROUP BY?

Without GROUP BY, aggregate functions summarize the entire table.

With GROUP BY, you can:

• Summarize data by categories.
• Get subtotals for each group.
• Analyze data in a more meaningful way.

---

Simple Example

Table: Sales

sale_id	customer_id	amount
1	101	100
2	101	150
3	102	200
4	103	250

---

Without GROUP BY

Total sales:

SELECT SUM(amount) FROM Sales;

➡️ 100 + 150 + 200 + 250 = 700

With GROUP BY

Total sales per customer:

SELECT customer_id, SUM(amount)
FROM Sales
GROUP BY customer_id;

Result:

customer_id	SUM(amount)
101	250
102	200
103	250

✅ Here:

• It groups rows by customer_id.
• For each group, it calculates the sum of amount.

Key Points

Feature	Description
Groups data	Rows with the same value in the group-by columns are combined.
Works with aggregates	Calculates SUM(), COUNT(), etc., per group.
Reduces number of rows	Returns one row per group, not per original row.
Columns rule	All selected columns must either be grouped or aggregated.

---

What Happens Internally?

Step-by-step:

1. Scan all the rows.
2. Group rows with the same customer_id.
3. Apply the SUM(amount) function to each group.
4. Return one row per group.

---

⚡ Golden Rule

When you use GROUP BY, every column in the SELECT must be:

• In the GROUP BY list OR
• Be an aggregate function.

❌ This is wrong:

SELECT customer_id, amount FROM Sales GROUP BY customer_id;

Because amount is not aggregated.

✅ Correct:

SELECT customer_id, SUM(amount) FROM Sales GROUP BY customer_id;

Real-Life Analogy

Imagine you have a list of purchases:

• GROUP BY is like grouping all purchases by customer before totaling them.
• It’s like summarizing all receipts per customer.

---

In Short

GROUP BY = Group similar rows together and apply aggregation on each group.

What is a VIEW in SQL?

A VIEW is a virtual table in SQL.

• It’s a saved SQL query that you can treat like a table when writing queries.
• A VIEW does not store actual data — it just stores the query definition.
• When you query a VIEW, the database runs the underlying SQL and gives you the result.

In short:

VIEW = Named, saved SQL query.

---

How to Create a View

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Example:

CREATE VIEW ActiveCustomers AS
SELECT customer_id, name
FROM Customers
WHERE status = 'Active';

Now you can:

SELECT * FROM ActiveCustomers;

Just like a table!

---

Key Properties of a VIEW

Feature	VIEW
Virtual table	Does not store data; runs the underlying query each time.
Based on tables	Built from one or more existing tables or other views.
Read-only (by default)	Most views cannot be updated (some can under restrictions).
Simplifies queries	Hides complexity (e.g., joins, filters) behind a name.
Secures data	Can show only certain columns to users, hiding others.

---

How is a VIEW Different from a TABLE?

Aspect	TABLE	VIEW
Stores data	Yes — actual stored rows.	No — just a saved query.
Physical storage	Takes space on disk (rows, indexes).	Minimal storage — just query definition.
Query performance	Very fast (data is ready).	Can be slower (query is executed each time).
Updatable	Yes — fully read/write.	Sometimes read-only (depends on the view).
Independent	Exists on its own.	Depends on underlying tables.

---

Why Use Views?

✅ Simplify Complex Queries

• Instead of writing a huge join or filter every time, wrap it in a view.

✅ Security

• Limit access to sensitive columns — users query the view instead of the base table.

✅ Logical Layer

• Present a different logical structure without changing the physical database.

✅ Reuse

• Create standard business rules — like “Active Customers” — and reuse them.

---

Example Scenario

Without view:

SELECT customer_id, name
FROM Customers
WHERE status = 'Active' AND region = 'North';

You write this every time.

With view:

CREATE VIEW NorthActiveCustomers AS
SELECT customer_id, name
FROM Customers
WHERE status = 'Active' AND region = 'North';

-- Later:
SELECT * FROM NorthActiveCustomers;

✅ Much simpler.

But Wait: What About Materialized Views?

Some databases (PostgreSQL, Oracle) support Materialized Views:

• They store the query result on disk.
• Faster to query (precomputed).
• Must be refreshed manually or on schedule.

📝 VIEW = runs fresh query every time.
📝 MATERIALIZED VIEW = stores and refreshes data periodically.

In Short

View = virtual table (saved query) — no data stored.
Table = real table (stores rows of data).

Composite Primary Key

A Primary Key doesn’t have to be just one column — it can be a combination of multiple columns.

That’s called a composite primary key.

✅ Example:

CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)  -- Composite Primary Key
);

Here:

• (student_id, course_id) together uniquely identify each row.
• A student can enroll in multiple courses.
• A course can have multiple students.
• But the same student cannot enroll in the same course twice.

👉 Both columns together form the Primary Key.

---

And What About Many UNIQUE Constraints?

Even if you have a composite Primary Key, you can still add UNIQUE constraints on other columns (or other combinations of columns).

✅ Example:

CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    semester VARCHAR(20),
    email VARCHAR(100),
    PRIMARY KEY (student_id, course_id),   -- Composite PK
    UNIQUE (student_id, semester),         -- Unique Constraint #1
    UNIQUE (email)                         -- Unique Constraint #2
);

• (student_id, course_id) → uniquely identifies a record (composite Primary Key).
• (student_id, semester) → no student can be enrolled twice in the same semester (UNIQUE).
• email → email must be unique (UNIQUE).

✅ Many UNIQUE constraints are still allowed, even with a composite Primary Key.

Important Rules

Primary Key	Unique Constraint
Only one Primary Key per table	You can have many UNIQUE constraints
Can be single-column or multi-column (composite key)	Each UNIQUE can be single or multi-column too
Must be NOT NULL	UNIQUE allows NULL values (in most DBs)
Primary Key creates a unique clustered index (in most DBs)	UNIQUE creates a unique non-clustered index

---

To sum up:

• ✅ Composite Primary Key — multiple columns combined to uniquely identify each row.
• ✅ Many UNIQUE constraints — additional uniqueness rules on other columns or other combinations of columns.

✅ You can absolutely have a composite primary key and many unique constraints in the same table.

---

Real-Life Analogy

Think of a university course enrollment system:

• Primary Key = (StudentID + CourseID): No duplicate enrollment in the same course.
• Unique constraint on (StudentID + Semester): No duplicate enrollment in the same semester.
• Unique Email: No duplicate emails.

Each constraint enforces a different “business rule.”

---

Simple Example

CREATE TABLE Bookings (
    room_id INT,
    date DATE,
    start_time TIME,
    booking_id SERIAL UNIQUE,       -- Unique booking number
    user_email VARCHAR(255),
    PRIMARY KEY (room_id, date, start_time),   -- Composite PK (room + date + time)
    UNIQUE (user_email, date)                 -- User cannot have two bookings on the same day
);

• Composite PK on (room_id, date, start_time).
• Unique constraint on (user_email, date).
• Unique constraint on booking_id.

✅ Perfectly valid!