AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (9 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

Learn how to use SQL SELECT with examples

Published on Oct 21,2019 62 Views
12 / 37 Blog from Introduction to SQL

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

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 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.

StudentIDStudentNameAgeCity Country
1Rohan23MumbaiIndia
2Sameera22MumbaiIndia
3Anna21LondonUnited Kingdom
4John19New YorkUSA
5Alice22BerlinGermany

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:

StudentIDStudentNameAge
1Rohan23
2Sameera22
3Anna21
4John19
5Alice22

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;

Output:

StudentIDStudentNameAgeCity Country
1Rohan23MumbaiIndia
2Sameera22MumbaiIndia
3Anna21LondonUnited Kingdom
4John19New YorkUSA
5Alice22BerlinGermany

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:

Age
23
22
21
19

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:

StudentIDStudentNameAgeCity Country
5Alice22BerlinGermany
3Ana21LondonUnited Kingdom
1Rohan23MumbaiIndia
2Sameera22MumbaiIndia
4John19New YorkUSA

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;
StudentIDStudentNameAgeCity Country
4John19New YorkUSA
1Rohan23MumbaiIndia
2Sameera22MumbaiIndia
3Ana21LondonUnited Kingdom
5Alice22BerlinGermany

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:

COUNT(StudentID)City
2Mumbai
1London
1New York
1Berlin

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:

Count(StudentID)City
2Mumbai

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.

StudentIDStudentNameAgeCity Country
1Rohan23MumbaiIndia
2Sameera22MumbaiIndia
3Anna21LondonUnited Kingdom
4John19New YorkUSA
5Alice22BerlinGermany

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.

StudentNameAge
Rohan23
Sameera22
Anna21
John19
Alice22

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';
StudentIDStudentNameAgeCity Country
1Rohan23MumbaiIndia
2Sameera22MumbaiIndia

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.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.