SQL Unlocked — Learn SELECT From Zero

01 The Story 3 min read

Imagine you have a filing cabinet.

Imagine your college keeps a record of every student — their names, cities, and scores. All of this is stored in a database. Think of a database like a giant, super-organized filing cabinet. Each drawer is a table, and each paper inside a drawer is a row of data.

Your professor says: "Give me the list of all students from Delhi." You can't just open the cabinet and look — it has thousands of papers. You need a way to ask for exactly what you want.

That's where SQL comes in. SQL is the language you use to talk to a database. And the very first word you'll ever use in SQL? SELECT.

02 The Why 2 min read

Why SELECT? Is there any other way?

Short answer: no. If you want to read data from a database, you always start with SELECT. It is the one and only way. There's no alternative, no shortcut, no other keyword that does the same job.

Key Concept

SELECT never modifies data. It is purely a read operation. No matter how many times you run a SELECT query, the underlying data stays completely unchanged — making it safe to run anywhere, even in production.

SELECT is read-only

SELECT never changes your data. It just looks at it. Nothing gets added, deleted, or updated. You can run SELECT a thousand times and the database stays exactly the same.

SQL has other keywords too — but you start here

SQL has INSERT (add data), UPDATE (change data), and DELETE (remove data). But reading data — which is 80% of what you'll do in a real job — always starts with SELECT.

When do you use SELECT? Every single time you want to see data. Checking how many users signed up today? SELECT. Finding top-selling products? SELECT. Building a dashboard? Every chart is a SELECT query. It's literally everywhere.

03 Anatomy 4 min read

Breaking down a SELECT query.

Every SELECT query has the same three parts. Let's dissect one:

The anatomy of SELECT

SELECT

Command

reads data

·

name, city

Columns

or * for all

·

FROM

Source

from where

·

students

Table name

where data lives

;

1

SELECT

The command. Always the first word. Tells SQL: "I want to read data."

2

Column list

What columns do you want? Use * for all columns, or list specific ones separated by commas.

3

FROM table

Which table to read from. The table name goes right after FROM.

Key Concept

This order is mandatory — SELECT first, then FROM. SQL will not accept them in any other order. Every read query you ever write follows this exact pattern.

Syntax at a Glance

SELECT col1, col2 … or *

FROM table_name ;

Coming up in this guide

WHEREfilter rows

ORDER BYsort results

TOP ncap row count

ASrename output

Execution Order — how SQL actually runs your query

1 · FROM

Load the table

→

2 · WHERE

Filter rows

→

3 · SELECT

Pick columns

You write SELECT first, but SQL runs FROM first to find the table. Knowing this order matters for WHERE — covered in the next chapter.

Interactive — toggle columns to see the query update

SELECT * FROM students;

Select all 4 → uses *. Select fewer → lists only those columns.

What does * actually mean?

students table

id

name

city

score

SELECT *

your result

idnamecityscore

1ArjunDelhi88

2PriyaMumbai72

3RajPune95

…and more rows

* is shorthand for "all columns" — instead of writing id, name, city, score you just write * and get everything.

04 Live Examples 4 min read

SELECT in action — 4 real examples.

We'll use this students table throughout. Here's the data we're working with:

students table (our data)

id	name	city	score
1	Arjun	Delhi	88
2	Priya	Mumbai	72
3	Raj	Pune	95
4	Simran	Delhi	61
5	Karan	Chennai	80

Ex 1 Get everything from the table

Use * to pull all rows and all columns.

SQL

SELECT * FROM students;

Output

id	name	city	score
1	Arjun	Delhi	88
2	Priya	Mumbai	72
3	Raj	Pune	95
4	Simran	Delhi	61
5	Karan	Chennai	80

Ex 2 Get only specific columns

Instead of all 4 columns, ask for just name and city. You list the column names separated by commas.

SQL

SELECT name, city FROM students;

Output — only 2 columns returned

name	city
Arjun	Delhi
Priya	Mumbai
Raj	Pune
Simran	Delhi
Karan	Chennai

Ex 3 Rename a column in the output with AS

AS lets you give a column a custom label in the output. The table itself doesn't change — only how the column header appears in your result.

SQL

SELECT name AS student_name, score AS marks FROM students;

Output — custom column headers

student_name	marks
Arjun	88
Priya	72
Raj	95
Simran	61
Karan	80

Ex 4 Calculate a new value on the fly

You can do math directly in SELECT. Here we double everyone's score. The original data stays untouched.

SQL

SELECT name, score * 2 AS double_score FROM students;

Output — calculated column, original data unchanged

name	double_score
Arjun	176
Priya	144
Raj	190
Simran	122
Karan	160

Ex 5 Remove duplicates with DISTINCT

Two students live in Delhi. Without DISTINCT you get Delhi twice. Adding DISTINCT right after SELECT collapses the result to unique values only.

SQL — without DISTINCT (duplicates appear)

SELECT city FROM students;

Output — Delhi appears twice

city
Delhi
Mumbai
Pune
Delhi
Chennai

SQL — with DISTINCT

SELECT DISTINCT city FROM students;

Output — each city once

city
Delhi
Mumbai
Pune
Chennai

Key Rule

DISTINCT on multiple columns removes rows where all listed columns match — not just one.

SELECT DISTINCT city, name

a Delhi+Arjun row and a Delhi+Simran row are not duplicates because name differs — both survive.

SELECT ALL is the default keyword (never written explicitly) that means "return every row including duplicates" — the opposite of DISTINCT. You will never need to write it, but interviewers mention it.

Ex 6 Limit rows with TOP (SQL Server)

TOP n caps how many rows are returned. In SQL Server, TOP goes immediately after SELECT, before the column list — unlike MySQL/SQLite which use LIMIT at the end.

SQL — TOP 3 rows

SELECT TOP 3 * FROM students;

Output — first 3 rows only

id	name	city	score
1	Arjun	Delhi	88
2	Priya	Mumbai	72
3	Raj	Pune	95

TOP n WITH ORDER BY — meaningful

SELECT TOP 3 * FROM students ORDER BY score DESC;Returns the 3 highest scorers. Always combine TOP with ORDER BY to get the rows you actually intend.

TOP without ORDER BY — random

SELECT TOP 3 * FROM students;Gives any 3 rows — SQL Server makes no promise about which. Different runs can return different rows.

Variants

TOP n PERCENT — returns a percentage of rows:

SELECT TOP 10 PERCENT * FROM students ORDER BY score DESC;

top 10% of rows by score.

TOP n WITH TIES — if the n-th row ties with the next row, all tied rows are included:

SELECT TOP 3 WITH TIES * FROM students ORDER BY score DESC;

if 3rd and 4th scores are equal, both are returned.

Ex 7 Conditional column with CASE WHEN

CASE WHEN is SQL's if/else. It lets you add a derived column whose value depends on a condition. Every row is evaluated independently — first matching WHEN wins.

SQL

SELECT name, score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 75 THEN 'B'
    WHEN score >= 60 THEN 'C'
    ELSE 'F'
  END AS grade
FROM students;

Output — grade column computed per row

name	score	grade
Arjun	88	B
Priya	72	C
Raj	95	A
Simran	61	C
Karan	80	B

Rules

Every CASE must close with END. WHEN branches evaluate top-to-bottom — first match wins, the rest are skipped.
ELSE is optional but recommended — without it, unmatched rows return NULL.

IIF shorthand (SQL Server only) — for simple two-branch conditions:

SELECT name, IIF(score >= 75, 'Pass', 'Fail') AS result FROM students;

04b Functions in SELECT 5 min read

Built-in functions you can use in SELECT.

Any function can appear in the column list — SQL evaluates it once per row. The original data is never changed. These are grouped by category for SQL Server (T-SQL).

Math Functions

ROUND — decimal places

SELECT name, ROUND(score / 3.0, 2) AS avg FROM students;

ROUND(expr, n) rounds to n decimal places. Use 3.0 not 3 — integer ÷ integer truncates (88/3 = 29, not 29.33).

ABS — absolute value

SELECT ABS(-88) -- 88 SELECT ABS(score - 70) AS diff FROM students;

ABS() removes the sign. Useful for calculating unsigned distance from a target value.

CEILING / FLOOR

SELECT CEILING(4.1) -- 5 SELECT FLOOR(4.9) -- 4 SELECT CEILING(score/3.0) AS up FROM students;

CEILING always rounds up to the next integer. FLOOR always rounds down.

POWER / SQRT

SELECT POWER(2, 10) -- 1024 SELECT SQRT(144) -- 12.0 SELECT POWER(score, 2) AS sq FROM students;

POWER(base, exp) raises to a power. SQRT() returns square root. Both return float.

String Functions (T-SQL / SQL Server)

LEN — character count

SELECT name, LEN(name) AS len FROM students; -- Arjun → 5, Raj → 3

LEN() does not count trailing spaces. MySQL/SQLite use LENGTH() instead.

LEFT / RIGHT — first/last N chars

SELECT LEFT(name, 3) AS first3, RIGHT(name, 2) AS last2 FROM students; -- Arjun → 'Arj', 'un'

LEFT(col, n) takes n chars from the start. RIGHT(col, n) takes from the end.

SUBSTRING — extract from middle

-- SUBSTRING(col, start, length) SELECT SUBSTRING(name, 2, 3) AS mid FROM students; -- Arjun → 'rju' (start=2, len=3)

Start position is 1-based. Goes left→right. If length exceeds remaining chars, returns what's there.

LTRIM / RTRIM — strip spaces

SELECT LTRIM(RTRIM(name)) AS clean FROM students; -- ' Arjun ' → 'Arjun'

LTRIM removes leading spaces. RTRIM removes trailing spaces. Nest both for a full trim. SQL Server 2017+ has TRIM().

REPLACE — find and replace

SELECT REPLACE(city, 'Delhi', 'NCR') AS city_clean FROM students; -- 'Delhi' → 'NCR'

REPLACE(col, find, replacement) replaces all occurrences. Case-insensitive in SQL Server CI collation.

CHARINDEX — find position

-- CHARINDEX(find, in_string) SELECT CHARINDEX('r', name) AS pos FROM students; -- Arjun → 2 (r is at position 2)

Returns 0 if not found. Useful to check if a value contains a substring before extracting it.

Date & Time Functions (T-SQL)

GETDATE — current date/time

SELECT GETDATE() AS now; -- 2026-06-11 14:32:05.123 SELECT GETDATE() AS created_at, name FROM students;

Returns current server datetime. MySQL: NOW(). PostgreSQL: CURRENT_TIMESTAMP. SQLite: datetime('now').

YEAR / MONTH / DAY — extract parts

SELECT name, YEAR(enrolled_on) AS yr, MONTH(enrolled_on) AS mo, DAY(enrolled_on) AS dy FROM students;

Each function extracts one part of a date column as an integer. Works on DATE, DATETIME, and DATETIME2 columns.

DATEDIFF — difference between dates

-- DATEDIFF(unit, start, end) SELECT name, DATEDIFF(DAY, enrolled_on, GETDATE()) AS days_enrolled FROM students;

Units: YEAR, MONTH, DAY, HOUR, MINUTE. Returns an integer. Negative if end is before start.

FORMAT — custom date display

SELECT FORMAT(GETDATE(), 'dd-MMM-yyyy') AS display_date; -- 11-Jun-2026 SELECT FORMAT(enrolled_on, 'MMMM yyyy') AS month_year FROM students;

FORMAT uses .NET date format strings. Returns NVARCHAR. Avoid in WHERE — it prevents index use. Use for display only.

Type Conversion (CAST / CONVERT)

CAST — ANSI standard

-- CAST(expr AS target_type) SELECT CAST(score AS FLOAT) / 3 AS avg FROM students; SELECT CAST(enrolled_on AS VARCHAR(20)) AS date_text FROM students;

CAST is ANSI SQL — works in all databases. Use when you need to change a value's data type: INT→FLOAT for division, DATE→VARCHAR for display.

CONVERT — SQL Server style option

-- CONVERT(type, expr [, style]) SELECT CONVERT(VARCHAR, GETDATE(), 103) -- 11/06/2026 (style 103 = DD/MM/YYYY) SELECT CONVERT(INT, '42') AS n; -- 42

CONVERT is SQL Server-specific. The optional third argument is a style code for date formatting. CAST is preferred for portability.

Integer division trap — use CAST

-- WRONG — integer ÷ integer truncates SELECT 88 / 3; -- 29 (not 29.33) -- RIGHT — cast one side to float SELECT CAST(88 AS FLOAT) / 3; -- 29.33 SELECT 88 / 3.0; -- 29.33

When both operands are INT, SQL Server performs integer division and drops the remainder. Always cast one side or use a decimal literal.

TRY_CAST / TRY_CONVERT — safe conversion

-- Returns NULL on failure instead of error SELECT TRY_CAST('abc' AS INT); -- NULL SELECT TRY_CAST('42' AS INT); -- 42 SELECT TRY_CONVERT(INT, score_text) FROM students;

TRY_CAST and TRY_CONVERT return NULL if the conversion fails, instead of throwing an error. Use on untrusted/dirty data.

NULL in Arithmetic

NULL + anything = NULL. If any operand in an expression is NULL, the entire result is NULL.

SELECT 100 + NULL;

NULL

SELECT name, score + 10 AS bonus FROM students;

if score is NULL, bonus is NULL too.

Fix: Use ISNULL(score, 0) or COALESCE(score, 0) to substitute 0 before the arithmetic.

05 Common Mistakes 3 min read

Things beginners get wrong — and you won't.

Typo in the keyword

SELCT * FROM students; — SQL won't guess what you meant. One wrong letter = query fails. Always spell SELECT correctly.

Missing FROM

SELECT name students; — SQL doesn't know which table to look in. FROM is not optional.

Missing comma between columns

SELECT name city FROM students; — SQL thinks city is an alias for name. Use a comma: SELECT name, city.

Wrong column name

SELECT naam FROM students; — The column is name, not naam. SQL is case-insensitive for keywords but column names must exist.

Keywords are case-insensitive

select * from students; works the same as SELECT * FROM students;. Convention is uppercase for readability, but it's your call.

The semicolon ends the query

The ; at the end tells SQL "this query is done." Most tools work without it, but it's good habit, especially when running multiple queries.

Integer division drops the decimal

SELECT 88 / 3; returns 29, not 29.33. When both sides are integers, SQL truncates. Fix: use a decimal literal (88 / 3.0) or CAST(88 AS FLOAT) / 3.

NULL + anything = NULL

If any column in your expression is NULL, the whole result is NULL. score + 10 returns NULL if score is NULL. Use ISNULL(score, 0) + 10 to substitute a default first.

TOP without ORDER BY = unpredictable rows

SELECT TOP 3 * FROM students; alone can return any 3 rows — SQL Server makes no guarantee. Always pair TOP with ORDER BY to get the rows you actually intend.

06 Key Takeaways 2 min read

What you just learned.

Quick Reference SELECT — what you need to know

SELECT is the only way to read data in SQL — no shortcut, no alternative.
The basic pattern is always: SELECT columns FROM table;
Use * to get all columns. List specific column names separated by commas for a subset.
Use AS to rename a column in the output — the actual table doesn't change.
You can do math directly in SELECT — computed per row, original data untouched.SELECT price * 2 AS double_price FROM products;
Keywords are case-insensitive — select = SELECT. Uppercase is just convention.
SELECT never changes data — it only reads. Safe to run anywhere, any number of times.

07 Case Sensitivity 3 min read

Three rules that trip everyone up.

SQL doesn't treat every character the same way. Keywords follow one rule, data values follow another — and mixing them up is one of the most common beginner mistakes.

Key Concept

SQL has two completely separate rules: keywords like SELECT and FROM are entirely case-insensitive. String values you work with — like 'Delhi' or 'hello' — are case-sensitive in most databases.

Rule 1

Keywords are case-insensitive

SELECT, select, and Select are all identical. SQL simply doesn't care.

Rule 2 — Gotcha

String values are case-sensitive

'Delhi' and 'delhi' are different values. The case must match exactly what's stored.

Rule 3 — Convention

Uppercase keywords is the standard

It's not required, but SELECT in uppercase makes keywords pop visually against column and table names.

All three queries below do exactly the same thing:

SQL — keyword casing doesn't matter

SELECT name FROM students;   -- standard (recommended)
select name from students;   -- all lowercase — also works
Select name From students;   -- mixed — also works

String values keep their exact casing — handle with care:

Finds the row

WHERE city = 'Delhi'

Exact match — stored as 'Delhi'

Returns nothing

WHERE city = 'delhi'

No match — lowercase ≠ title case

Fix

Use LOWER() or UPPER() to normalize before comparing: LOWER(city) = 'delhi' matches any casing — 'Delhi', 'DELHI', or 'delhi'. You'll use this heavily in the WHERE chapter.

08 SQL Command Types 6 min read

SQL has four command types — here's the full map.

Every SQL command belongs to one of four categories. DDL builds the structure, DML works with the data inside, DCL controls who can do what, and TCL manages saving and undoing changes. As a data analyst, you'll live in DML — but knowing all four makes you a smarter, safer SQL writer.

Before you write a single query — understand where your data lives

In SQL Server, every table has a full address — just like your home address has a country, city, street, and house number. Here's what each part means:

1 · Server

LAPTOP\SQLEXPRESS

The computer where SQL Server is installed. Could be your own laptop or a company server.

.

2 · Database

StudentDB

A collection of related tables — like a folder. One server can hold many databases.

.

3 · Schema

dbo

A namespace inside the database. The default is always dbo (database owner). Beginners almost never change this.

.

4 · Table

Students

The actual table where rows of data are stored.

Full address: LAPTOP\SQLEXPRESS.StudentDB.dbo.Students
Short form (most common): dbo.Students — SQL Server assumes the current server & database.
Shortest: Students — SQL assumes dbo schema automatically. Fine for beginners.

Complete Setup Script — copy this into SSMS and run it step by step

-- ================================================
--  MarutiNovaTech | Your First SQL Script
--  Copy this → paste into SSMS → press F5 to run
-- ================================================

-- STEP 1: Create your practice database
--         Think of this as creating a new folder for your work
CREATE DATABASE StudentDB;
GO

-- STEP 2: Tell SQL Server to USE this database
--         Like opening that folder so everything you do goes inside it
USE StudentDB;
GO

-- STEP 3: Create the Students table (DDL)
--
--  Full name  →  YourServerName.StudentDB.dbo.Students
--  Short name →  dbo.Students   (dbo = default schema, always there)
--  Shortest   →  Students       (SQL assumes dbo automatically)
--
--  Each column needs: Name  DataType  [Optional rules]
CREATE TABLE dbo.Students (
    StudentID   INT            PRIMARY KEY,      -- Unique number, no two students share it
    FirstName   VARCHAR(50)   NOT NULL,         -- Text up to 50 letters, can't be empty
    LastName    VARCHAR(50)   NOT NULL,         -- Same
    City        VARCHAR(50),                     -- Optional — can be left blank
    Score       INT,                              -- Whole number (no decimals)
    EnrolledOn  DATE           DEFAULT GETDATE() -- Auto-fills today's date if not given
);
GO

-- STEP 4: Add 5 students (DML — INSERT)
--         List column names first, then their values in the same order
INSERT INTO dbo.Students (StudentID, FirstName, LastName, City, Score) VALUES
(1, 'Arjun',  'Kumar',  'Delhi',   88),
(2, 'Priya',  'Mehta',  'Mumbai',  72),
(3, 'Raj',    'Verma',  'Pune',    95),
(4, 'Simran', 'Kaur',   'Delhi',   61),
(5, 'Karan',  'Singh',  'Chennai', 80);
GO

-- STEP 5: See all your data (DML — SELECT)
--         * means "all columns"
SELECT * FROM dbo.Students;

-- STEP 6: Try the full address format
--         Replace LAPTOP\SQLEXPRESS with YOUR server name from SSMS login screen
SELECT * FROM [LAPTOP\SQLEXPRESS].StudentDB.dbo.Students;
GO

-- ================================================
--  What you just did:
--  ✓ Created a DATABASE  (like a folder)
--  ✓ Created a TABLE     (like a spreadsheet inside that folder)
--  ✓ Added 5 rows        (like filling rows in that spreadsheet)
--  ✓ Read the data back  (SELECT = reading, never changes anything)
-- ================================================

DDL

Chapter 1 · Data Definition Language

Day 1 — the database is empty. Someone has to build it.

Ravi is the database architect at MarutiNovaTech. It's his first morning. The server is running, but the database is a blank canvas — no tables, no columns, nothing. Before a single student record can be saved, someone has to define the structure.

He opens SSMS and types his first DDL command. CREATE TABLE — the architect's first act. It doesn't add any data. It just says: "here is a table called Students, and here are its columns."

Think of DDL like constructing a building. You're not moving furniture in yet — you're deciding how many rooms to build, where the doors go, and what each room is for.

Two weeks later, the team realises they forgot to add a score column. Ravi uses ALTER TABLE to add it — no data is lost. A month later, a test table gets accidentally destroyed when a junior developer runs DROP TABLE without realising it deletes everything permanently. Lesson learned the hard way.

DDL — building the structure

-- Day 1: build the table
CREATE TABLE Students (
    id    INT PRIMARY KEY,
    name  VARCHAR(50),
    city  VARCHAR(50)
);

-- Two weeks later: forgot a column — ALTER adds it
ALTER TABLE Students ADD score INT;

-- Danger: DROP deletes the table permanently — no undo
DROP TABLE test_backup; -- gone forever

Remember

DDL changes structure — not data. Once you DROP a table, it is gone. No recycle bin. No undo. Always double-check before running DDL on a real database.

DML

Chapter 2 · Data Manipulation Language

Week 3 — the structure exists. Now the real work begins.

The Students table is live. 200 students have enrolled. Every single action the team takes from this point — adding a student, checking scores, fixing a typo, removing a duplicate — is a DML command.

The admissions team uses INSERT to add new students as they enrol. The academic team uses SELECT constantly — to pull class lists, check attendance, build reports. When a student's score gets entered incorrectly, the teacher uses UPDATE to fix it. When a student accidentally gets added twice, DELETE removes the extra row.

DML is the daily heartbeat of any database. Every dashboard, every report, every analytics query — it all starts with SELECT. UPDATE and DELETE are used carefully, always with a WHERE clause.

DML — working with the data

-- Add a new student
INSERT INTO Students VALUES (6, 'Rohan', 'Pune', 88);

-- Read: show all Delhi students
SELECT * FROM Students WHERE city = 'Delhi';

-- Fix a wrong score — always use WHERE
UPDATE Students SET score = 91 WHERE id = 6;

-- Remove a duplicate entry
DELETE FROM Students WHERE id = 99;

In real jobs

As a data analyst, 90% of what you write is SELECT. INSERT, UPDATE, and DELETE are used by developers and admins. But you need to understand all four — because reading someone else's DML query is part of the job.

DCL

Chapter 3 · Data Control Language

Month 6 — the team grows. Not everyone should have the same access.

MarutiNovaTech hires Sneha as a data analyst. Her job is to build reports. She needs to read the Students table — but she should never be able to change or delete records. The IT admin uses GRANT to give her exactly the access she needs, and nothing more.

Three months later, a third-party contractor finishes his project. The admin immediately uses REVOKE to remove his access. The data stays safe — he can no longer query the database at all.

DCL is about trust. In real companies, a junior analyst almost never has UPDATE or DELETE access. They are GRANTed SELECT only. The DBA decides who gets which keys — and can REVOKE them at any time.

DCL — controlling access

-- Give Sneha read-only access to Students
GRANT SELECT ON Students TO sneha_analyst;

-- Contractor project done — remove all access
REVOKE SELECT ON Students FROM contractor_user;

-- Give a teacher insert access (but not delete)
GRANT INSERT ON Students TO teacher_role;

Real world

When you join a company as a data analyst, someone will GRANT you access to specific tables. You won't run GRANT yourself — but you'll hear "I need SELECT access on the orders table" constantly. Now you know exactly what that means.

TCL

Chapter 4 · Transaction Control Language

A Thursday afternoon — and the day everything almost went wrong.

It's 4pm. A developer needs to mark all active students as "enrolled" in the Students table. He types an UPDATE query. He's in a hurry. He forgets the WHERE clause.

He hits Run.

Every single student in the table — all 1,200 of them — just got updated. Not just the active ones. All of them. The database has changed. Panic sets in.

Then someone asks: "Were you running inside a transaction?" He was. He quickly types ROLLBACK — and in one second, every change is undone. The database is exactly as it was before. Crisis over.

From that day, every risky DML operation at MarutiNovaTech is wrapped in a transaction. Run the change. Check the result. Then COMMIT if it looks right — or ROLLBACK if something went wrong. This is TCL: your safety net for everything that could go wrong.

TCL — the safety net

-- Wrap a risky UPDATE in a transaction
BEGIN TRANSACTION;

UPDATE Students
SET status = 'Enrolled'
WHERE IsActive = 1; -- only active students

-- Check: does the result look right?
SELECT * FROM Students WHERE status = 'Enrolled';

-- Looks good → make it permanent
COMMIT;

-- Something wrong? Undo everything
-- ROLLBACK;

Professional habit

Senior developers always wrap UPDATE and DELETE in a transaction. Run it. SELECT to verify. COMMIT if correct. ROLLBACK if not. This habit has saved careers.

DDL — Data Definition Language

Defines the structure

Changes how the database is built — tables, columns, data types. Think of it as the architect's work. Usually done once, rarely touched again.

CREATE

Build a new table from scratch

CREATE TABLE Students (id INT, name VARCHAR(50), city VARCHAR(50));

ALTER

Modify an existing table — add or drop a column

ALTER TABLE Students ADD COLUMN score INT;

DROP Danger

Permanently delete the entire table + all its data

DROP TABLE Students; -- gone forever, no undo

TRUNCATE Careful

Wipe all rows instantly — keeps the table structure

TRUNCATE TABLE Students; -- table stays, all rows gone

DML — Data Manipulation Language

Works with the data inside

Reads and changes the rows in tables. This is the daily work of every analyst, developer, and data engineer. SELECT is the most common DML command by far.

SELECT Safe

Read data — never changes anything

SELECT * FROM Students WHERE city = 'Delhi';

INSERT

Add one or more new rows to a table

INSERT INTO Students VALUES (6, 'Rohan', 'Pune');

UPDATE Careful

Change values in existing rows — always use WHERE

UPDATE Students SET city = 'Mumbai' WHERE id = 6;

DELETE Careful

Remove specific rows — always use WHERE

DELETE FROM Students WHERE id = 6;

Comparison

DDL

DML

What it affects

Table structure & schema

Rows of data inside tables

Commands

CREATE, ALTER, DROP, TRUNCATE

SELECT, INSERT, UPDATE, DELETE

How often used

Rarely — setup & migrations

Every day — this is the real work

Risk level

High — structural changes affect everything

Medium — SELECT is always safe; others need WHERE

In real companies

Needs DBA / senior approval

Everyday analyst & developer work

The Golden Rule

Never run UPDATE or DELETE without a WHERE clause. Without WHERE, every single row in the table gets changed or deleted — instantly, with no undo. Always run a SELECT with the same WHERE first to preview exactly which rows you're about to affect.

Two more you'll encounter

DCL — Data Control Language

Controls who can do what

Manages permissions — which users can read, write, or delete data. In real companies, analysts usually only get SELECT. Only a DBA can grant more.

GRANT

Give a user permission to perform an action on a table

GRANT SELECT ON Employees TO analyst_user;

REVOKE

Take away a previously granted permission

REVOKE INSERT ON Employees FROM analyst_user;

TCL — Transaction Control Language

Save or undo your work

A transaction groups multiple changes into one unit. You can confirm them all at once or undo everything if something goes wrong — a safety net for risky operations.

COMMIT

Make all changes in the current transaction permanent

COMMIT; -- changes are now saved forever

ROLLBACK

Undo all changes since the last COMMIT — like Ctrl+Z for SQL

ROLLBACK; -- everything since last COMMIT is undone

SAVEPOINT

Create a named checkpoint to partially roll back to

SAVEPOINT before_update;
ROLLBACK TO before_update;

The complete picture — all four types

DDL

Data Definition Language

CREATE · ALTER
DROP · TRUNCATE

Builds & modifies
table structure

DML

Data Manipulation Language

SELECT · INSERT
UPDATE · DELETE

Reads & changes
data rows — daily work

DCL

Data Control Language

GRANT
REVOKE

Controls user
permissions & access

TCL

Transaction Control Language

COMMIT · ROLLBACK
SAVEPOINT

Saves or undoes
groups of changes

08b Reference

SELECT quick reference — patterns and performance

Keep these patterns handy. The cheat sheet covers every SELECT pattern taught in this chapter.

SELECT Cheat Sheet

All columns

SELECT * FROM students;

* means every column. Use for exploration; avoid in production code.

Specific columns

SELECT name, city FROM students;

List only the columns you need. Faster, clearer intent.

Alias with AS

SELECT name AS student_name, score AS total FROM students;

AS renames the output column. Does not change the table.

Alias with spaces (SQL Server)

SELECT name AS [Full Name], score AS [Total Score] FROM students;

Square brackets allow spaces in aliases — SQL Server syntax.

DISTINCT — remove duplicates

SELECT DISTINCT city FROM students;

Returns each unique value once. On two columns, removes rows where BOTH match.

Arithmetic expression

SELECT name, salary * 12 AS annual FROM employees;

Any arithmetic is valid per row. Use 3.0 not 3 for decimal division.

String functions (SQL Server)

SELECT UPPER(name), LOWER(city), LEN(name) FROM students;

LEN() counts characters. UPPER/LOWER convert case. Original data unchanged.

Concatenation (SQL Server)

SELECT name + ' — ' + city AS info FROM students;

SQL Server uses + for string concat. SQLite/PostgreSQL use ||.

CASE expression

SELECT name, CASE WHEN score >= 80 THEN 'Pass' ELSE 'Fail' END AS result FROM students;

CASE WHEN is like IF/ELSE inside a column. Multiple WHEN branches allowed.

NULL handling (SQL Server)

SELECT name, ISNULL(score, 0) AS score, COALESCE(city, 'Unknown') AS city FROM students;

ISNULL(col, default) — SQL Server. COALESCE works on multiple values.

TOP n (SQL Server)

SELECT TOP 5 * FROM students ORDER BY score DESC;

TOP goes right after SELECT. Combine with ORDER BY for meaningful top-N.

Literal value column

SELECT name, 'India' AS country FROM students;

A string/number literal repeats the same value for every row.

TOP n PERCENT

SELECT TOP 20 PERCENT * FROM students ORDER BY score DESC;

Returns the top 20% of rows by count. If the table has 100 rows, returns 20. Always combine with ORDER BY.

TOP n WITH TIES

SELECT TOP 3 WITH TIES * FROM students ORDER BY score DESC;

If the 3rd and 4th rows share the same ORDER BY value, both are included. Prevents arbitrary tie-breaking.

IIF — two-branch shorthand

SELECT name, IIF(score >= 75, 'Pass', 'Fail') AS result FROM students;

IIF(condition, true_value, false_value) is SQL Server shorthand for a two-branch CASE. More concise but less portable.

SELECT without FROM

SELECT 1 + 1; -- 2 SELECT GETDATE(); -- now SELECT UPPER('hello'); -- HELLO SELECT LEN('SQL Server'); -- 10

SQL Server allows SELECT without FROM for scalar expressions. Useful for quick calculations or testing a function without a table.

CAST — type conversion

-- INT to FLOAT for real division SELECT CAST(score AS FLOAT) / 3 FROM students; -- Date to text SELECT CAST(GETDATE() AS VARCHAR(20));

CAST(expr AS type) is ANSI standard — works across all databases. Use when you need to change a value's data type.

CONVERT — SQL Server with style

-- Style 103 = DD/MM/YYYY SELECT CONVERT(VARCHAR, GETDATE(), 103); -- Style 112 = YYYYMMDD SELECT CONVERT(VARCHAR, GETDATE(), 112);

CONVERT is SQL Server-specific and adds a style parameter for date formatting. Use CAST for portability; CONVERT when you need a date format code.

LEFT / RIGHT / SUBSTRING

SELECT LEFT(name, 3) AS first3, RIGHT(name, 2) AS last2, SUBSTRING(name, 2, 3) AS mid FROM students; -- Arjun → 'Arj', 'un', 'rju'

SUBSTRING(col, start, length) — start is 1-based. LEFT/RIGHT are shortcuts for the start/end.

REPLACE / LTRIM / RTRIM

SELECT REPLACE(city, 'Delhi', 'NCR'), LTRIM(RTRIM(name)) AS clean FROM students;

REPLACE swaps substrings. LTRIM/RTRIM strip leading/trailing spaces. SQL Server 2017+ adds TRIM() for both at once.

Performance Tips

SELECT *

Avoid in prod

Fetches every column including ones you don't need. Increases network transfer, breaks views when schema changes.

Use during exploration only.

Specific columns

Best practice

Fetching only needed columns reduces I/O. With a covering index, SQL Server can satisfy the query entirely from the index.

Always prefer in production queries, views, and stored procedures.

SELECT TOP n

Fast

SQL Server stops scanning as soon as it has the required rows — with a sorted index, TOP 1 is near-instant.

Always combine TOP with ORDER BY so you get the intended rows.

Functions on columns

Can block indexes

Functions in SELECT list are fine. Functions wrapped around columns in WHERE prevent index use on large tables.

Functions in SELECT: free. Functions in WHERE: expensive on large tables.

DISTINCT

Sort cost

DISTINCT requires sorting or hashing all rows. Costly on millions of rows.

Use when duplicates genuinely exist. If they shouldn't, fix the query upstream.

Computed columns

Free in SELECT

Arithmetic in SELECT (salary * 12, ROUND(score, 2)) runs per row with no extra overhead.

Move expensive functions to persisted computed columns if used in WHERE repeatedly.

SQL Dialect Comparison — SELECT

Same concept, different syntax across databases.

Feature	SQL Server	MySQL / MariaDB	PostgreSQL	SQLite
Limit rows	✓ `SELECT TOP n ...`	⚡ `LIMIT n` at end	⚡ `LIMIT n` at end	⚡ `LIMIT n` at end
String concat	✓ `col1 + col2`	⚡ `CONCAT(c1, c2)`	⚡ `col1 \|\| col2`	⚡ `col1 \|\| col2`
String length	✓ `LEN(col)`	⚡ `LENGTH(col)`	⚡ `LENGTH(col)`	⚡ `LENGTH(col)`
NULL fallback	✓ `ISNULL(col, v)`	⚡ `IFNULL(col, v)`	⚡ `COALESCE(col, v)`	⚡ `IFNULL / COALESCE`
Alias spaces	✓ `[Full Name]`	⚡ backtick or quotes	⚡ `"Full Name"`	⚡ `"Full Name"`
DISTINCT	✓ Standard	✓ Standard	✓ Standard	✓ Standard
SELECT INTO	✓ `SELECT * INTO new_tbl FROM src`	✗ Use CREATE TABLE ... SELECT	✗ Use CREATE TABLE AS SELECT	✗ Not natively supported
CASE expression	✓ Standard	✓ Standard	✓ Standard	✓ Standard
Current datetime	✓ `GETDATE()`	⚡ `NOW()`	⚡ `CURRENT_TIMESTAMP`	⚡ `datetime('now')`
Date parts	✓ `YEAR/MONTH/DAY(col)`	⚡ `YEAR/MONTH/DAY(col)`	⚡ `EXTRACT(part FROM col)`	⚡ `strftime('%Y', col)`
Type conversion	✓ `CAST` & `CONVERT`	⚡ `CAST` only	⚡ `CAST` / `::type`	⚡ `CAST` only
Safe conversion	✓ `TRY_CAST / TRY_CONVERT`	✗ No native equivalent	⚡ errors by default; use `NULLIF`	✗ Not supported
TOP / LIMIT	✓ `SELECT TOP n ...`	⚡ `LIMIT n` at end	⚡ `LIMIT n` at end	⚡ `LIMIT n` at end
Two-branch IF	✓ `IIF(cond, t, f)`	⚡ `IF(cond, t, f)`	✗ Use CASE WHEN	✗ Use CASE WHEN

✓ fully supported · ⚡ alternative syntax · ✗ not natively supported

Showing SQL Server only · MySQL · PostgreSQL · SQLite visible on wider screens

09 Practice 30+ min

60 SELECT Questions

MCQ to check concepts · Fix-It to spot bugs · Write to build muscle memory. Do them in order — they build on each other.

SQL Server focus — All concepts and syntax on this page are SQL Server (T-SQL). The interactive playground runs SQLite in the browser (no server needed). Where syntax differs — e.g. TOP n vs LIMIT n — each question notes both versions.

0 / 42 completed Start with Q1 →

60 questions

No questions match your filter.

10 Interview Prep 5 min read

11 questions interviewers ask about SELECT.

Quick reference first — then click any card below for the full answer.

Quick Reference SELECT — 10 patterns interviewers test

Pattern	SQL Server Syntax	Why interviewers ask it
All columns	SELECT * FROM t	"Why avoid this in production?"
Specific columns	SELECT a, b FROM t	Always preferred over *
Column alias	SELECT salary AS pay	Renaming output without changing data
Computed column	SELECT price * 1.18 AS with_tax	Math in SELECT — table is never changed
Conditional column	SELECT CASE WHEN score>=75 THEN 'Pass' ELSE 'Fail' END AS result	Logic inside SELECT — most asked pattern
Remove duplicates	SELECT DISTINCT city FROM t	De-duplication in output
Limit rows	SELECT TOP 10 * FROM t ORDER BY id	TOP goes after SELECT, not at end (SQL Server)
Handle NULL	SELECT COALESCE(phone, 'N/A')	NULL safety — first non-NULL wins
String operation	SELECT UPPER(name), LEN(name)	Data transformation in SELECT
Type conversion	SELECT CAST(score AS VARCHAR(5))	Explicit type handling

Click any card to reveal the full answer. These surface the why behind the syntax.

Concept Trap Pattern Advanced

Trap Why is SELECT * bad practice in production?

Three reasons: it fetches columns you don't need (wastes bandwidth), it breaks views and apps when a column is added or removed, and it hides intent — a reader can't tell what data the query actually uses.

-- Bad in production: SELECT * FROM Employees; -- Good — explicit, stable, intent is clear: SELECT EmployeeID, FirstName, Department, Salary FROM Employees;

Schema change trap: If the table gains a new column, SELECT * silently returns it. If your app maps columns by position, it will map the wrong data. Named columns are always positionally stable.

Exception: SELECT * is fine in ad-hoc exploration and development. The rule is about production queries, views, and stored procedures.

Concept What is the difference between SELECT DISTINCT col and SELECT DISTINCT col1, col2?

DISTINCT applies to the combination of all selected columns. One column returns unique values of that column. Two columns returns unique pairs — both must match to be removed.

-- Unique cities only SELECT DISTINCT city FROM students; -- Unique city+dept pairs (more rows — each pair counts separately) SELECT DISTINCT city, dept FROM employees;

Common mistake: Thinking SELECT DISTINCT name, city returns unique names. It returns unique name+city combinations — Arjun in Delhi and Arjun in Mumbai both appear.

Performance: DISTINCT triggers a sort or hash on all rows. On millions of rows, this is expensive.

Trap What does SELECT name, NULL AS score FROM students return?

A column called score where every row has NULL — the literal null value, aliased. The actual score column in the table is completely ignored.

-- Returns name + NULL for every row SELECT name, NULL AS score FROM students; -- Use ISNULL to show actual score with fallback SELECT name, ISNULL(score, 0) AS score FROM students;

When this matters: In UNION queries, NULL AS col_name is a common pattern to align column counts when one query doesn't have data the other does.

Pattern How do you add a conditional column using CASE WHEN in SQL Server?

Use a CASE WHEN ... THEN ... ELSE ... END expression inside the SELECT list. Each row is evaluated independently and gets the matching label.

SELECT name, score, CASE WHEN score >= 90 THEN 'A' WHEN score >= 75 THEN 'B' WHEN score >= 60 THEN 'C' ELSE 'F' END AS grade FROM students;

Common mistake: Forgetting the END keyword. Every CASE must close with END. Multiple WHEN branches evaluate top-to-bottom; first match wins.

SQL Server tip: IIF(condition, true_val, false_val) is a shorthand for simple two-branch CASE expressions.

Concept What is the execution order of a SELECT query — and why does it matter?

You write SELECT first, but SQL runs FROM first. Logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

-- Execution order: -- 1. FROM students ← load table -- 2. WHERE score > 70 ← filter rows -- 3. SELECT name, score ← pick columns -- 4. ORDER BY score DESC ← sort result

Why this matters: Aliases defined in SELECT don't exist at WHERE stage. WHERE annual_salary > 500000 fails if annual_salary is a SELECT alias — repeat the expression or use a CTE.

SQL Server exception: ORDER BY can reference SELECT aliases because it runs after SELECT.

Advanced What is SELECT INTO in SQL Server and when do you use it?

SELECT INTO creates a new table and populates it from the result set in one statement. The destination table must not already exist.

-- Create a backup table SELECT * INTO Employees_Backup FROM Employees WHERE HireDate >= '2020-01-01'; -- Create empty copy (structure only) SELECT * INTO Employees_Empty FROM Employees WHERE 1 = 0;

Does not copy constraints/indexes: SELECT INTO copies column names and data types, but NOT primary keys, foreign keys, or indexes. Add these manually after.

MySQL/PostgreSQL alternative: CREATE TABLE new AS SELECT * FROM old;

Pattern How do you handle NULL values in a SELECT result?

Use ISNULL(col, default) for a two-value fallback in SQL Server, or COALESCE(col1, col2, fallback) for multi-value fallback. Both return the first non-NULL value.

-- ISNULL: SQL Server-specific, two args only SELECT name, ISNULL(score, 0) AS score FROM students; -- COALESCE: ANSI standard, works across all databases SELECT name, COALESCE(score, 0) AS score FROM students; -- Multi-value fallback SELECT COALESCE(phone, mobile, 'N/A') AS contact FROM contacts;

Type difference: ISNULL uses data type of the first argument; COALESCE uses the highest-precedence type — can cause implicit conversion differences on edge cases.

Advanced Why can't you use a SELECT alias in a WHERE clause?

WHERE runs before SELECT in the logical execution order. The alias doesn't exist yet when WHERE is evaluated.

-- FAILS — alias 'annual' doesn't exist at WHERE stage SELECT name, salary * 12 AS annual FROM employees WHERE annual > 600000; -- Error! -- Fix 1: repeat the expression in WHERE WHERE salary * 12 > 600000; -- Fix 2: use a CTE WITH base AS ( SELECT name, salary * 12 AS annual FROM employees ) SELECT * FROM base WHERE annual > 600000;

ORDER BY exception: ORDER BY runs after SELECT, so it CAN reference aliases. Only WHERE, GROUP BY, and HAVING cannot.

Concept What is the difference between SELECT TOP n, TOP n PERCENT, and TOP n WITH TIES?

TOP n returns exactly n rows. TOP n PERCENT returns that percentage of total rows (rounds up). TOP n WITH TIES returns n rows plus any additional rows that tie on the last ORDER BY value.

-- Exactly 3 rows SELECT TOP 3 * FROM students ORDER BY score DESC; -- Top 20% of rows (1 row if table has 5 rows) SELECT TOP 20 PERCENT * FROM students ORDER BY score DESC; -- 3 rows, but include ties on the 3rd rank SELECT TOP 3 WITH TIES * FROM students ORDER BY score DESC;

Always use ORDER BY with TOP. Without ORDER BY, SQL Server returns any n rows — the result is non-deterministic and can change between runs.

MySQL/PostgreSQL equivalent: LIMIT n at the end of the query. WITH TIES equivalent in PostgreSQL is FETCH FIRST n ROWS WITH TIES.

Concept What is SELECT ALL and when would you ever write it?

SELECT ALL is the default behavior — return every row including duplicates. It is the explicit opposite of SELECT DISTINCT. In practice, no one writes it because it is already the default; you would only ever see it in documentation or obscure codebases to explicitly communicate intent.

-- These two are identical: SELECT ALL city FROM students; SELECT city FROM students; -- DISTINCT is the notable variant: SELECT DISTINCT city FROM students; -- unique values only

Why it exists: The SQL standard defines the syntax as SELECT [ ALL | DISTINCT ] columns FROM table. ALL is simply the default value — omitting it means ALL is implied.

Trap What is the difference between CAST and CONVERT in SQL Server?

Both convert a value from one data type to another. CAST is ANSI standard and works across all databases. CONVERT is SQL Server-specific and adds an optional style parameter for date formatting. Prefer CAST for portability; use CONVERT when you need a date format code.

-- CAST: ANSI standard, portable SELECT CAST(score AS FLOAT) / 3 AS avg FROM students; SELECT CAST(GETDATE() AS VARCHAR(20)) AS date_txt; -- CONVERT: SQL Server only, adds style codes for dates SELECT CONVERT(VARCHAR, GETDATE(), 103); -- 11/06/2026 (DD/MM/YYYY) SELECT CONVERT(VARCHAR, GETDATE(), 112); -- 20260611 (YYYYMMDD) -- TRY_CAST: returns NULL on failure instead of error SELECT TRY_CAST('abc' AS INT); -- NULL, not an error

Integer division trap: SELECT score / 3 truncates. Fix with CAST(score AS FLOAT) / 3 or score / 3.0.

Up Next

Chapter 2 — WHERE

Learn to filter rows so you only get the data you need. Uses everything you learned here.

Start WHERE Chapter →

11 Reverse Challenge 10 min

Reverse engineer the query.

You see the result. You write the query. 10 levels — one new concept each time. Ctrl + Enter runs your query.

Loading SQL engine…

Learn to SELECT
your data.

Can you reverse engineer a SQL query?

Imagine you have a filing cabinet.

Why SELECT? Is there any other way?

Breaking down a SELECT query.

SELECT in action — 4 real examples.

Built-in functions you can use in SELECT.

Things beginners get wrong — and you won't.

What you just learned.

Three rules that trip everyone up.

SQL has four command types — here's the full map.

SELECT quick reference — patterns and performance

60 SELECT Questions

11 questions interviewers ask about SELECT.

Reverse engineer the query.

Learn to SELECTyour data.

Can you reverse engineer a SQL query?

Imagine you have a filing cabinet.

Why SELECT? Is there any other way?

Breaking down a SELECT query.

SELECT in action — 4 real examples.

Built-in functions you can use in SELECT.

Things beginners get wrong — and you won't.

What you just learned.

Three rules that trip everyone up.

SQL has four command types — here's the full map.

SELECT quick reference — patterns and performance

60 SELECT Questions

11 questions interviewers ask about SELECT.

Reverse engineer the query.

Learn to SELECT
your data.