SQL SELECT Statement - Retrieving Data from Tables

SQL SELECT Statement

Once your data is in the database, the next step is to extract knowledge from it. The SELECT statement is your go-to tool. Whether you want a list of students in Class 10A or the top scorers in Maths, it all begins here.

Basic Syntax of SELECT

SELECT column1, column2, ...
FROM table_name
WHERE condition;

where

• SELECT: A required keyword that begins the statement. It tells the database you want to retrieve data.
• column1, column2, ...: A comma-separated list of the columns you want to fetch from the table. You can also use * to select all columns.
• FROM: A required keyword that specifies where to look for the data — i.e., which table to select from.
• table_name: The actual name of the table containing the data.
• WHERE (optional): Used to filter the results based on a condition. Only rows that satisfy the condition will be returned.
• condition: The logical expression that decides which rows are included. For example, age > 15.
• ;: Ends the SQL statement. It is required by most SQL engines to separate one statement from another.

Sample Table students

Let’s use a simple students table as our base. We shall use this table in the following examples.

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10),
  age INT,
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A', 15, 'Delhi'),
(2, 'Diya Iyer', '9B', 14, 'Chennai'),
(3, 'Rohit Menon', '10A', 15, 'Kochi'),
(4, 'Sneha Patil', '8C', 13, 'Pune');

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune

1. SELECT All Columns

SELECT * FROM students;

This retrieves everything — every row, every column.

Query Result

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune

2. SELECT Specific Columns

Say, we need only the name and city columns from the students table.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune

SQL query to select only the name and city columns from the students table.

SELECT name, city FROM students;

This query returns only the name and city columns for all the records in the students table.

Query Result

name	city
Aarav Sharma	Delhi
Diya Iyer	Chennai
Rohit Menon	Kochi
Sneha Patil	Pune

3. SELECT with WHERE Clause

Retrieve all the student records who belong to class 10A.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune

SQL Query to select all records from the students table where the value in the class column is equal to '10A'.

SELECT * FROM students
WHERE class = '10A';

The result includes all columns—roll_no, name, class, age, and city—for students who are in class 10A. In this case, it shows the complete information of students Aarav Sharma and Rohit Menon who are both enrolled in class 10A.

Query Result

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
3	Rohit Menon	10A	15	Kochi

4. SELECT with Multiple Conditions

Use logical operators like AND and OR:

Get a list of student names from class 10A who are located in Kochi, perhaps to organize a local class event or distribute location-specific announcements.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune

SQL statement to select the names of all students who are in class 10A and live in the city of Kochi.

SELECT name FROM students
WHERE class = '10A' AND city = 'Kochi';

This query retrieves the name column from the students table, but only for those records where the class is '10A' and the city is 'Kochi'. In other words, it returns the names of students who meet both of these conditions.

resultset

name
Rohit Menon

5. SELECT with ORDER BY

Use Case: A teacher wants to view a list of student names along with their ages from the students table, sorted from the oldest to the youngest. This can help in identifying senior students for leadership roles or assigning age-appropriate responsibilities.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune

Select the name and age columns from the students table, and sort the results in descending order based on the age column. This means the oldest students will appear at the top of the result set.

SELECT name, age FROM students
ORDER BY age DESC;

The query returns the name and age of all students from the students table, sorted by age in descending order. This means students are listed from the oldest to the youngest. If multiple students share the same age, they appear in the order they were found in the table.

Query Result

name	age
Aarav Sharma	15
Rohit Menon	15
Diya Iyer	14
Sneha Patil	13

6. SELECT with DISTINCT

Use Case: A school administrator wants to know all the different classes present in the student records, without listing duplicates, in order to create a class-wise summary or generate a class list.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune

Select only the unique values from the class column in the students table using the DISTINCT keyword. This ensures that duplicate class names are removed from the result.

SELECT DISTINCT class FROM students;

Result Description: The result shows a list of distinct class names found in the students table. Based on the data, the classes returned would be: 10A, 9B, and 8C.

Query Result

class
10A
9B
8C

7. SELECT with Aliases

Use Case: A reporting system needs to display a list of student names and their hometowns with custom column headers for better readability or alignment with a specific format.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune

SQL Description: Select the name and city columns from the students table, and rename them in the output as student_name and hometown using the AS keyword.

SELECT name AS student_name, city AS hometown
FROM students;

Result Description: The output will show two columns: one labeled student_name containing the names of the students, and another labeled hometown containing their respective cities. This makes the data more descriptive and easier to interpret in external reports or user interfaces.

Query Result

student_name	hometown
Aarav Sharma	Delhi
Diya Iyer	Chennai
Rohit Menon	Kochi
Sneha Patil	Pune

8. SELECT with Calculations

Use Case: A school administrator wants to prepare for next year's academic planning by viewing each student's age in the upcoming year. This helps in organizing age-specific activities or promotions.

students

roll_no	name	class	age	city
1	Aarav Sharma	10A	15	Delhi
2	Diya Iyer	9B	14	Chennai
3	Rohit Menon	10A	15	Kochi
4	Sneha Patil	8C	13	Pune

SQL Description: Select the name and age columns from the students table, and also calculate a new column named next_year_age by adding 1 to the current age of each student. This column is given an alias using AS.

SELECT name, age, age + 1 AS next_year_age
FROM students;

Result Description: The result includes the student's name, their current age, and a computed column next_year_age which shows what their age will be next year. This is calculated by adding 1 to each student's current age.

Query Result

name	age	next_year_age
Aarav Sharma	15	16
Diya Iyer	14	15
Rohit Menon	15	16
Sneha Patil	13	14

Best Practices

• Use SELECT * cautiously — avoid it in production queries.
• Filter your queries using WHERE to reduce load and noise.
• Use aliases for clarity in reports or views.

Summary

The SELECT statement is your querying powerhouse — whether you're scanning the full student list, targeting a specific class, or organizing by age. Mastering SELECT unlocks the full potential of SQL as a query language.