One of the most important operations in SQL is selecting data from a table. The SELECT statement is used to query the database and retrieve the desired data. In this article, we will discuss the SELECT statement and its variations, including DISTINCT and TOP.
SQL SELECT Statement
The SELECT statement is used to retrieve data from one or more tables in a database. It has the following syntax:
SELECT column1, column2, ...
FROM table_name;Here, column1, column2, etc. are the names of the columns in the table that we want to retrieve data from. table_name is the name of the table from which we want to retrieve data.
We can also use the * operator to retrieve data from all columns in a table:
SELECT *
FROM table_name;Example
Consider the following employees table:
| id | name | department | salary |
|---|---|---|---|
| 1 | Alice | Sales | 50000 |
| 2 | Bob | Marketing | 60000 |
| 3 | Charlie | Sales | 55000 |
| 4 | Dave | Marketing | 70000 |
To retrieve the names of all employees in the employees table, we can use the following SELECT statement:
SELECT name
FROM employees;This will return the following result:
| name |
|---|
| Alice |
| Bob |
| Charlie |
| Dave |
SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to retrieve unique values from a table. It has the following syntax:
SELECT DISTINCT column1, column2, ...
FROM table_name;Here, column1, column2, etc. are the names of the columns in the table that we want to retrieve unique values from.
Example
Consider the following employees table:
| id | name | department | salary |
|---|---|---|---|
| 1 | Alice | Sales | 50000 |
| 2 | Bob | Marketing | 60000 |
| 3 | Charlie | Sales | 55000 |
| 4 | Dave | Marketing | 70000 |
| 5 | Alice | Sales | 55000 |
To retrieve the unique departments from the employees table, we can use the following SELECT DISTINCT statement:
SELECT DISTINCT department
FROM employees;This will return the following result:
| department |
|---|
| Sales |
| Marketing |
Note that the duplicate value of “Sales” in the department column has been removed.
SQL SELECT TOP Statement
The SELECT TOP statement is used to retrieve a specified number of rows from the top of a table. It has the following syntax:
SELECT TOP number column1, column2, ...
FROM table_name;Here, number is the number of rows that we want to retrieve from the top of the table.
Example
Suppose we have a table called Employees with the following data:
| ID | Name | Age | Salary |
|---|---|---|---|
| 1 | John Smith | 25 | 50000 |
| 2 | Jane Doe | 30 | 60000 |
| 3 | Bob Johnson | 35 | 70000 |
| 4 | Lisa Wong | 40 | 80000 |
| 5 | Mark Lee | 45 | 90000 |
To retrieve the top 3 employees based on their salary, we can use the following SQL statement:
SELECT TOP 3 Name, Salary FROM Employees ORDER BY Salary DESC;The output of the above SQL statement would be:
| Name | Salary |
|---|---|
| Mark Lee | 90000 |
| Lisa Wong | 80000 |
| Bob Johnson | 70000 |
Note that the ORDER BY clause is used to sort the data in descending order based on the Salary column before selecting the top 3 rows.
Practice Tasks
Beginner
Consider a “products” table with the following columns: “id” (unique identifier), “name” (product name), “category” (product category), “price” (product price), and “supplier” (supplier name). Here’s an example of some data in this table:
| id | name | category | price | supplier |
|---|---|---|---|---|
| 1 | Apple iPhone | Electronics | 1000 | Apple Inc. |
| 2 | Samsung Galaxy | Electronics | 900 | Samsung |
| 3 | Sony PlayStation | Gaming | 500 | Sony |
| 4 | Bose QuietComfort | Audio | 350 | Bose |
| 5 | Nike Air Max | Fashion | 120 | Nike |
Write a SQL query to retrieve all unique values in the “category” column of the “products” table.
Intermediate
Using the same “products” table as above, write a SQL query to retrieve the top 5 most expensive products in the table, including their names, prices, and the name of the supplier that provides each product.
Advanced
Using the same “products” table as above, write a SQL query to retrieve the top 3 best-selling products in each category, including the category name, product name, and the total number of units sold for each product. Here’s an example of a “sales” table that includes data on which products were sold and how many units were sold:
| id | product_id | quantity | sale_date |
|---|---|---|---|
| 1 | 1 | 10 | 2022-04-01 |
| 2 | 1 | 15 | 2022-04-02 |
| 3 | 2 | 5 | 2022-04-03 |
| 4 | 2 | 10 | 2022-04-04 |
| 5 | 3 | 20 | 2022-04-05 |
| 6 | 3 | 15 | 2022-04-06 |
| 7 | 4 | 5 | 2022-04-07 |
| 8 | 4 | 10 | 2022-04-08 |
| 9 | 5 | 30 | 2022-04-09 |
| 10 | 5 | 25 | 2022-04-10 |
Note that “product_id” in the “sales” table corresponds to “id” in the “products” table. Use this data to write a SQL query that retrieves the top 3 best-selling products in each category.
Summary
In this article, we discussed the SELECT statement in SQL and its different variations such as SELECT TOP and SELECT DISTINCT. We covered their syntax, examples, and how they can be used to retrieve specific data from a database table. By using these statements effectively, you can easily retrieve the desired information from your database tables.
To learn more about SQL, check out our articles on SQL WHERE with AND, OR, NOT Operators and SQL Order By (ASC and DESC).
