Unit 1: Database Concepts (RDBMS)

Chapter 5: Querying Data - SELECT, WHERE, and ORDER BY

1. The SELECT Command Architecture

The SELECT statement retrieves data rows from one or more database tables according to specified criteria.

Complete Clause Order of SELECT

SELECT [DISTINCT] column_list
FROM table_name
[WHERE search_condition]
[GROUP BY group_columns]
[HAVING group_condition]
[ORDER BY sort_columns [ASC | DESC]];

2. Basic Querying Operations

1. Retrieving All Columns

SELECT * FROM Student;

2. Projecting Specific Columns

SELECT RollNo, Name, Marks FROM Student;

3. Eliminating Duplicate Values (DISTINCT)

-- Shows unique streams without repetition
SELECT DISTINCT Stream FROM Student;

4. Calculated Columns and Column Aliases

-- Using AS for clear display headers
SELECT Name, Marks, Marks + 5 AS Grace_Marks, Marks * 0.10 AS Bonus
FROM Student;

3. Filtering Data with the WHERE Clause

The WHERE clause specifies filtering conditions to return only tuples that evaluate to TRUE.

1. Relational Operators

=, <>, !=, >, <, >=, <=

SELECT Name, Marks FROM Student WHERE Marks >= 90;
SELECT Name, Stream FROM Student WHERE Stream != 'Science';

2. Logical Operators: AND, OR, NOT

-- Both conditions must be met
SELECT Name FROM Student WHERE Stream = 'Science' AND Marks > 85;

-- At least one condition met
SELECT Name FROM Student WHERE Stream = 'Commerce' OR Stream = 'Humanities';

-- Inverting condition
SELECT Name FROM Student WHERE NOT (Stream = 'Science');

3. Range Checking: BETWEEN ... AND ...

Checks if a value falls within an inclusive range (both endpoints included).

SELECT Name, Marks FROM Student WHERE Marks BETWEEN 80 AND 90;
-- Equivalent to: WHERE Marks >= 80 AND Marks <= 90;

4. List Membership: IN and NOT IN

Matches against a discrete set of values.

SELECT Name, Stream FROM Student WHERE Stream IN ('Science', 'Commerce');
SELECT Name, RollNo FROM Student WHERE RollNo NOT IN (101, 105, 110);

5. Pattern Matching: LIKE Operator

Used for wildcard searches in string attributes: - % (Percent): Represents zero, one, or multiple characters. - _ (Underscore): Represents exactly one single character.

-- Names starting with 'A'
SELECT Name FROM Student WHERE Name LIKE 'A%';

-- Names ending with 'a'
SELECT Name FROM Student WHERE Name LIKE '%a';

-- Names having 'an' anywhere in the name
SELECT Name FROM Student WHERE Name LIKE '%an%';

-- Names having exactly 4 letters
SELECT Name FROM Student WHERE Name LIKE '____';

-- Second letter is 'a'
SELECT Name FROM Student WHERE Name LIKE '_a%';

6. Checking for NULL: IS NULL and IS NOT NULL

SELECT Name FROM Student WHERE DOB IS NULL;
SELECT Name FROM Student WHERE Email IS NOT NULL;

4. Sorting Records: The ORDER BY Clause

The ORDER BY clause sorts returned records in ascending (ASC, default) or descending (DESC) order.

-- Ascending order by Name
SELECT RollNo, Name, Marks FROM Student ORDER BY Name ASC;

-- Descending order by Marks
SELECT Name, Marks FROM Student ORDER BY Marks DESC;

-- Multi-column sorting: First by Stream (ASC), then by Marks (DESC)
SELECT Name, Stream, Marks FROM Student ORDER BY Stream ASC, Marks DESC;

5. Board Exam Practice Questions

Consider Table SPORTS: | PCode | PName | Game | Points | Grade | | :--- | :--- | :--- | :--- | :--- | | P01 | Rohan | Cricket | 85 | A | | P02 | Tanya | Badminton | 92 | A | | P03 | Kabir | Tennis | NULL | B | | P04 | Simran | Cricket | 78 | B |

Write SQL queries for: 1. Display details of players whose game is Cricket and points are greater than 80. - SELECT * FROM SPORTS WHERE Game = 'Cricket' AND Points > 80; 2. Display all players whose Grade is 'A' sorted by Points in descending order. - SELECT * FROM SPORTS WHERE Grade = 'A' ORDER BY Points DESC; 3. Display names of players whose Points are not recorded (NULL). - SELECT PName FROM SPORTS WHERE Points IS NULL; 4. Display unique games available in the SPORTS table. - SELECT DISTINCT Game FROM SPORTS;