Learn how to use SQL SELECT with examples

Databases store large volumes of data in various formats. But have you ever thought how to select data from database? Well, the SQL SELECT statement is used to retrieve the data from databases. In this article  on SQL SELECT, I will discuss how to use the SELECT statement in SQL with various other commands.

The following topics will be covered in this article:

• What is SELECT?
• Syntax
• Examples:
  • SELECT Column Example
  • SELECT * Example
  • Use SELECT with DISTINCT
  • SELECT with ORDER BY
  • Use SELECT with GROUP BY
  • SELECT Having Clause
  • Use SELECT INTO

What is SQL SELECT?

The SELECT statement is used to select a specific set of data from the database. The data returned by the SELECT statement is stored in a result table called as result set.

SQL SELECT Syntax:

--To select few columns
SELECT ColumnName1, ColumnName2, ColumnName(N) FROM TableName; 

-- To select complete data from the table 
SELECT * FROM TableName; 

--To select the top N records from the table 
SELECT TOP N * FROM TableName;

Moving on in this article on SQL SELECT, let us understand how to use the SELECT statement in various ways.

Examples:

For your better understanding, I will be considering the following table.

Let us look into each one of them one by one.

SQL SELECT Column Example

Here you mention the column names for which you wish to retrieve data.

Example: Write a query to retrieve the StudentID, StudentName and Age from the Students table.

SELECT StudentID, StudentName, Age FROM Students;

Output:

SQL SELECT * Example

The Asterisk(*) is used to select all the data from the database/ table/ column.

Example: Write a query to retrieve all the details from the Students table.

SELECT * FROM Students;

You can even check out the details of relational databases, functions, queries, variables, etc with the SQL Course.

Output:

That was the simple way to use the SELECT statement. Let us move forward in this article on SQL SELECT and understand how to use the SELECT statement with the other commands in SQL.

Use SELECT with DISTINCT

You can use the SELECT statement with the DISTINCT statement to retrieve only distinct values.

Syntax

SELECT DISTINCT ColumnName1, ColumnName2,ColumnName(N) FROM TableName;

Example

SELECT DISTINCT Age FROM Students;

Output:

Moving on in this article, let us understand how to use SQL SELECT with the ORDER BY clause.

Use SELECT with ORDER BY

As we all know that the ORDER BY statement is used to sort the results either in ascending or descending order. We can use the ORDER BY statement with the SELECT statement to retrieve specific data in ascending or descending order.

Syntax

SELECT ColumnName1, ColumnName2, ColumnName(N) 
FROM TableName 
ORDER BY ColumnName1, ColumnName2, ... ASC|DESC;

Example to use only ORDER BY

Write a query to select all the fields from the students table ordered by city. 

SELECT * FROM Students ORDER BY City;

Output:

Example to use ORDER BY in descending order

Write a query to select all the fields from the students table ordered by city in the descending order.

SELECT * FROM Students ORDER BY City DESC;

Next in this article, let us understand how to use SQL SELECT with the GROUP BY statement.

Use SELECT with GROUP BY

The GROUP BY statement is used with the SELECT statement to group the result-set by one or more columns.

Syntax

SELECT ColumnName1, ColumnName2,..., ColumnName(N) 
FROM TableName 
WHERE Condition
GROUP BY ColumnName(N) 
ORDER BY ColumnName(N);

Example:

Write a query to list the number of students of each age.

SELECT COUNT(StudentID), City FROM Students GROUP BY City;

Output:

Next in this article, let us understand how to use SQL SELECT with the GROUP BY statement.

Use SELECT with HAVING clause

The HAVING clause can be used with the SELECT statement to retrieve data based on some conditions.

Syntax

SELECT ColumnName1, ColumnName2, ColumnName(N) 
FROM TableName 
WHERE Condition 
GROUP BY ColumnName(N) 
HAVING Condition 
ORDER BY ColumnName(N);

Example

Write a query to retrieve the number of students in each city where the number of students is > 1, and are sorted in a descending order.

SELECT COUNT(StudentID), City 
FROM Students 
GROUP BY City 
HAVING COUNT(StudentID) > 1 
ORDER BY COUNT(StudentID) DESC;

Output:

Use SELECT with INTO clause

This statement is used when you want to copy data from one table to the other table.

Syntax

SELECT * INTO NewTableName [IN DatabaseName] 
FROM OldTableName 
WHERE Condition;

Example

Write a query to create a backup of the Students database.

SELECT * INTO StudentBackup FROM Students;

Output:

You will see that the StudentBackup table will have all the fields from Students table.

Example: Write a query to create a backup by selecting few columns of the Students table. 

SELECT StudentName, Age INTO StudentBackup FROM Students;

Output:

You will see that the StudentBackup table will have the following fields from Students table.

Example: Write a query to create a backup by inserting all details of all those students who study in City ‘Mumbai’. 

SELECT * INTO StudentsBackup FROM Students WHERE City = 'Mumbai';

These were few ways to use the SELECT command. To get further knowledge go ahead and practice writing queries in SQL commands. With this we come to an end to this article on SQL SELECT. 

If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject. 

Got a question for us? Please mention it in the comments section of this article on SQL SELECT and I will get back to you.