SELECT in SQL

The SELECT statement is used to retrieve records from a database table.

SELECT * FROM Students;

This query displays all columns and all records from the Students table.

The basic syntax is:

SELECT column_name
FROM table_name;

You specify the columns that you want to retrieve followed by the table name.

The asterisk * means all columns.

SELECT * FROM Students;

Suppose the table contains:

The query will return all four columns.

You can retrieve only the columns that you need.

SELECT name, age
FROM Students;

This query returns only the name and age columns.

SELECT name
FROM Students;

This retrieves only the student names.

SELECT id, name, course
FROM Students;

You can specify multiple columns separated by commas.

The WHERE clause is used to filter records.

SELECT *
FROM Students
WHERE age = 20;

Only students whose age is 20 will be returned.

SELECT name, course
FROM Students
WHERE age = 20;

This retrieves the name and course of students whose age is 20.

You can use comparison operators such as >, <, >=, <=, and =.

SELECT *
FROM Students
WHERE age > 20;

This returns students whose age is greater than 20.

Text values are normally enclosed in single quotes.

SELECT *
FROM Students
WHERE course = 'Python';

This returns students enrolled in Python.

The AND operator allows multiple conditions.

SELECT *
FROM Students
WHERE age > 18
AND course = 'Python';

Both conditions must be true.

The OR operator returns records when at least one condition is true.

SELECT *
FROM Students
WHERE course = 'Python'
OR course = 'Java';

The ORDER BY clause sorts the returned records.

SELECT *
FROM Students
ORDER BY name;

By default, sorting is generally ascending.

Use DESC to sort data in descending order.

SELECT *
FROM Students
ORDER BY age DESC;

Use ASC for ascending order.

SELECT *
FROM Students
ORDER BY age ASC;

ASC is the default sorting direction.

In MySQL, LIMIT can restrict the number of returned records.

SELECT *
FROM Students
LIMIT 5;

This returns up to five records.

The DISTINCT keyword removes duplicate values from the result.

SELECT DISTINCT course
FROM Students;

This returns each course only once.

The COUNT() function can count records.

SELECT COUNT(*)
FROM Students;

This returns the total number of rows in the table.

The SUM() function calculates the total of numeric values.

SELECT SUM(fee)
FROM Students;

This can be useful for calculating the total fee amount.

The AVG() function calculates the average value.

SELECT AVG(age)
FROM Students;

The MIN() function returns the smallest value.

SELECT MIN(age)
FROM Students;

The MAX() function returns the largest value.

SELECT MAX(age)
FROM Students;

The LIKE operator is used to search for a pattern.

SELECT *
FROM Students
WHERE name LIKE 'A%';

The % wildcard means zero or more characters. This example finds names starting with A.

The BETWEEN operator checks whether a value is within a specified range.

SELECT *
FROM Students
WHERE age BETWEEN 18 AND 25;

This returns students whose age is between 18 and 25, including the boundary values.

The IN operator allows you to specify multiple possible values.

SELECT *
FROM Students
WHERE course IN ('Python', 'Java', 'PHP');

This returns students whose course matches one of the listed values.

To find NULL values, use IS NULL.

SELECT *
FROM Students
WHERE mobile IS NULL;

To find values that are not NULL:

SELECT *
FROM Students
WHERE mobile IS NOT NULL;

SELECT can also retrieve data from multiple tables using techniques such as JOIN.

SELECT Students.name, Courses.course_name
FROM Students
INNER JOIN Courses
ON Students.course_id = Courses.course_id;

JOINs will be covered in detail in later SQL lessons.

The AS keyword can provide a temporary name for a column in the result.

SELECT name AS Student_Name
FROM Students;

The result will display the column using the alias Student_Name.

Display all students:

SELECT * FROM Students;

Display only names:

SELECT name FROM Students;

Display Python students:

SELECT *
FROM Students
WHERE course = 'Python';

Display students older than 20:

SELECT *
FROM Students
WHERE age > 20;

SELECT name, course, fee
FROM Students
WHERE fee > 5000
AND course = 'Python'
ORDER BY fee DESC
LIMIT 5;

This query:

• Selects the name, course, and fee columns.
• Filters records where fee is greater than 5000.
• Filters records where the course is Python.
• Sorts the result by fee from highest to lowest.
• Returns up to five records.