SELECT Statement in SQL

The SELECT statement is used to retrieve and display data from one or more tables in a database. It allows us to view all the data in a table or select only specific columns and records. We can also use conditions to retrieve only the records that match our requirements.

For example, suppose we have a Students table containing the following data:

We can use the SELECT statement to retrieve this data from the table.

Syntax

The basic syntax of the SELECT statement is:

SELECT column1, column2, ... FROM table_name;

Here, we specify the columns that we want to retrieve after the SELECT keyword. The FROM keyword specifies the table from which the data should be retrieved.

How to Select All Columns in SQL

We can use the asterisk (*) with the SELECT statement to retrieve all columns from a table. It is useful when we want to display all the columns from a table without writing each column name separately.

Example:

SELECT * FROM Students;

Output:

In this example, * tells SQL to return all columns from the Students table. This is useful when we want to see the complete data stored in the table.

How to Select Specific Columns in SQL

Sometimes, we need to display only certain columns instead of retrieving all the data from a table. In such cases, we can specify the column names that we want to retrieve using the SELECT statement.

Example:

SELECT Name, Course FROM Students;

Output:

In this example, SQL returns only the Name and Course columns. The other columns are not included in the result.

How to Select Data with a WHERE Clause

The WHERE clause is used to filter records based on a specific condition. It returns only those rows that match the given condition and helps us retrieve the required data from a table.

Example:

SELECT * FROM Students WHERE Age > 20;

Output:

Explanation:

In this example, the WHERE Age > 20 condition selects only those students whose age is greater than 20.

How to Select Data Using Multiple Conditions

We can use operators such as AND and OR with the WHERE clause to apply multiple conditions in a SQL query. These operators help us filter records based on specific requirements and return only the data that matches the given conditions.

Using AND

The AND operator returns records only when both conditions are true.

Example:

SELECT * FROM Students WHERE Age > 19 AND Course = 'BCA';

Output:

Here, SQL selects students who are older than 19 and are enrolled in the BCA course.

Using OR

The OR operator returns records when at least one of the conditions is true.

Example:

SELECT * FROM Students WHERE Course = 'BCA' OR Course = 'MCA';

Output:

Here, the query returns students who are enrolled in either BCA or MCA.

How to Sort Data Using SELECT

The ORDER BY clause is used to sort the result of a SELECT query. By default, sorting is done in ascending order. We can use DESC to sort the data in descending order.

Sort Data in Ascending Order

SELECT * FROM Students ORDER BY Age ASC;

Output:

The ASC keyword sorts the students from the lowest age to the highest age.

Sort Data in Descending Order

SELECT * FROM Students ORDER BY Age DESC;

Output:

Here, DESC sorts the records from the highest age to the lowest age.

How to Select Unique Values Using DISTINCT

The DISTINCT keyword is used to remove duplicate values from the result. It returns only unique values for the selected column or combination of columns, which is useful when the same data appears multiple times in a table.

For example, the Course column contains some repeated course names. We can use DISTINCT to display each course only once.

SELECT DISTINCT Course FROM Students;

Output:

Here, each course appears only once in the result.

How to Limit the Number of Records

In MySQL, the LIMIT clause is used with the SELECT statement to restrict the number of rows returned in the result. It is useful when we want to display only a specific number of records from a table.

Example:

SELECT * FROM Students LIMIT 2;

Output:

This query returns only the first two records from the result.

Using SELECT with GROUP BY

The GROUP BY clause is used to group rows that have the same value in a column. It is commonly used with aggregate functions such as COUNT(), SUM(), and AVG(). The SELECT statement supports GROUP BY for creating grouped results.

For example, we can count how many students are enrolled in each course.

SELECT Course, COUNT(*) AS TotalStudents FROM Students GROUP BY Course;

Output:

Here, GROUP BY Course groups students according to their course, while COUNT(*) counts the number of students in each group.

Using SELECT with HAVING

The HAVING clause is used to filter grouped results in SQL. It is used with GROUP BY when we want to apply a condition to a group of records based on an aggregate value, such as COUNT(), SUM(), or AVG().

Example:

SELECT Course, COUNT(*) AS TotalStudents FROM Students GROUP BY Course HAVING COUNT(*) > 1;

Output:

In this example, the query first groups students by course and then displays only those courses that have more than one student.

SELECT Statement with Multiple Clauses

We can combine different clauses to create more useful SQL queries.

For example:

SELECT Name, Age, Course FROM Students WHERE Age >= 20 ORDER BY Age DESC;

Output:

Explanation:

In this query, SELECT specifies the columns to display, FROM specifies the table, WHERE filters students whose age is 20 or more, and ORDER BY Age DESC sorts the result from the highest age to the lowest age.

Difference Between SELECT * and SELECT Specific Columns

Several differences between the select * and select specific columns are:

Important Points About SELECT in SQL

• SELECT is used to retrieve data from a table or tables.
• FROM specifies the table from which the data is retrieved.
• * is used to select all columns.
• We can specify individual column names to retrieve selected columns.
• WHERE filters rows based on a condition.
• DISTINCT removes duplicate values from the result.
• ORDER BY sorts the result in ascending or descending order.
• GROUP BY groups rows with similar values.
• HAVING filters grouped results.
• In MySQL, LIMIT can be used to restrict the number of rows returned.

Conclusion

The SELECT statement is an important part of SQL because it allows us to retrieve and view data stored in database tables. We can use a simple SELECT query to display all records or combine it with clauses such as WHERE, ORDER BY, GROUP BY, HAVING, and LIMIT to get more specific results.