Querying Data (DML - Retrieval)

Introduction

Every other DML command you've met so far changes data. SELECT is different — it's the command for retrieving data, and it's the one you'll write far more often than any other in real use.

Concept

-- Every column, every row
SELECT * FROM STUDENT;

-- Specific columns, filtered by a condition
SELECT Name, Marks FROM STUDENT WHERE Marks > 90;

* means "every column". Naming columns explicitly (Name, Marks) retrieves only those. WHERE filters which rows are included in the result — rows that don't satisfy the condition simply aren't returned.

Explanation

It helps to think of a SELECT query as answering two separate questions at once: which columns do I want to see (right after SELECT), and which rows qualify (the WHERE condition). Getting comfortable separating those two questions makes every more complex query in this chapter easier to build.

Note: SELECT never changes the data in the table — no matter how it's filtered or which columns are chosen, running a SELECT is always completely safe to the underlying data.

Summary

SELECT columns FROM table WHERE condition retrieves data — * selects every column, explicit column names select only those, and WHERE filters which rows are included. Unlike every other DML command in this unit, SELECT never modifies the table.

1. Selecting Columns

2. Removing Duplicates (DISTINCT)

By default, SQL shows duplicate data. Use DISTINCT to show unique values only.

3. Filtering Data (WHERE)

To fetch only specific rows (e.g., "Students who scored > 90"), we use WHERE.

/* ==================================================
   SYSTEM SETUP (Loading Data...)
   ================================================== */
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE STUDENT (AdmNo INT, Name VARCHAR(20), Marks INT);
INSERT INTO STUDENT VALUES (101, 'Ravi', 95);
INSERT INTO STUDENT VALUES (102, 'Amit', 80);
INSERT INTO STUDENT VALUES (103, 'Neha', 98);
INSERT INTO STUDENT VALUES (104, 'Simran', 75);

/* ==================================================
   YOUR TASK STARTS HERE
   Select Name and Marks where Marks > 90
   ================================================== */

-- Write your query here:
-- SELECT ... FROM ... WHERE ...

Introduction

A plain WHERE Marks = 90 only matches an exact value. Real filtering needs more: a range of values, a specific list of options, a text pattern, or the presence/absence of data at all — that's what BETWEEN, IN, LIKE, and IS NULL are each for.

Concept

• BETWEEN a AND b — matches values within a range, inclusive of both ends.
• IN (list) — matches any value in a given list.
• LIKE 'pattern' — matches text against a pattern, using two wildcards: % (any number of characters, including zero) and _ (exactly one character).
• IS NULL — checks whether a column has no value at all.

Worked Example

SELECT * FROM STUDENT WHERE Marks BETWEEN 80 AND 90;

SELECT * FROM STUDENT WHERE City IN ('Delhi', 'Mumbai');

SELECT * FROM STUDENT WHERE Name LIKE 'A%';

SELECT * FROM STUDENT WHERE Marks IS NULL;

The first query returns every student whose marks fall in the 80-90 range, both ends included. The second returns every student living in either Delhi or Mumbai. The third returns every student whose name starts with A — including Amit. The fourth finds rows with no marks recorded at all — Amit's row again, since it was inserted back in Topic 68 without a Marks value, so Marks is NULL for that row.

Explanation

LIKE patterns are worth practicing precisely: 'A%' means "starts with A", '%a' means "ends with a", '_a%' means "second letter is a", and '%a%' means "contains a anywhere". The position of % versus _ relative to the letter is what changes the meaning entirely.

Warning: NULL can never be compared with =. WHERE Marks = NULL is always wrong and matches nothing, even for Amit's row above — the correct form is always WHERE Marks IS NULL (or IS NOT NULL).

Summary

BETWEEN filters a range, IN filters against a list of options, LIKE filters by text pattern using % and _ wildcards, and IS NULL / IS NOT NULL is the only correct way to check for missing data — = NULL never works in SQL.

1. Range & List Checks

2. Pattern Matching (LIKE)

Used to search for a pattern (e.g., "Names starting with R"). We use two Wildcards:

3. The NULL Trap

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE STUDENT (ID INT, Name VARCHAR(20));
INSERT INTO STUDENT VALUES (1, 'Amit');
INSERT INTO STUDENT VALUES (2, 'Amita');
INSERT INTO STUDENT VALUES (3, 'Bob');
INSERT INTO STUDENT VALUES (4, 'Ravi');
INSERT INTO STUDENT VALUES (5, 'Anil');

/* ==================================================
   YOUR TASK STARTS HERE
   Find names starting with 'A' having exactly 4 letters.
   ================================================== */

-- Write your query:
-- SELECT * FROM STUDENT WHERE ...

Introduction

A SELECT query's results come back in no guaranteed order unless you ask for one. ORDER BY is how you control the order rows are displayed in.

Concept

-- Highest marks first
SELECT * FROM STUDENT ORDER BY Marks DESC;

-- Lowest marks first (the default if ASC/DESC is omitted)
SELECT * FROM STUDENT ORDER BY Marks ASC;

ASC (ascending, low to high) is the default if you don't specify either keyword — writing ORDER BY Marks alone behaves exactly like ORDER BY Marks ASC. DESC (descending, high to low) must always be stated explicitly.

Explanation

ORDER BY only changes how results are displayed — it never changes anything about the actual data stored in the table, and it's always the very last clause in a query, after any WHERE filtering has already happened.

Note: If a question doesn't mention ASC or DESC at all, assume ascending order — that's SQL's own default, not something the query needs to state.

Summary

ORDER BY column ASC/DESC sorts query results — ascending is the default if neither keyword is given, descending must be stated explicitly with DESC. It only affects display order, never the underlying stored data.

Syntax & Keywords

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE STUDENT (Name VARCHAR(20), Marks INT);
INSERT INTO STUDENT VALUES ('Ravi', 85);
INSERT INTO STUDENT VALUES ('Amit', 92);
INSERT INTO STUDENT VALUES ('Neha', 78);
INSERT INTO STUDENT VALUES ('Simran', 95);

/* ==================================================
   YOUR TASK STARTS HERE
   Display students sorted by Marks (High to Low)
   ================================================== */

-- Write your query:
-- SELECT * FROM STUDENT ...