Semantic portal
SQL Select
The SELECT statement in SQL is used to query and retrieve data from a database. Here's a detailed explanation of its syntax and usage, including the ORDER BY, LIMIT, and DISTINCT clauses:
Basic SELECT Statement Syntax
SELECT column1, column2, ...
FROM table_name;
SELECT: Specifies the columns to retrieve.column1, column2, ...: Lists the columns to be selected.FROM: Specifies the table from which to retrieve the data.table_name: The name of the table.
Example:
SELECT first_name, last_name
FROM employees;
In this example, the query retrieves the first_name and last_name columns from the employees table.
ORDER BY Clause
The ORDER BY clause is used to sort the result set by one or more columns.
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
ORDER BY: Specifies the columns by which to sort the results.column1, column2, ...: Lists the columns to sort by.ASC: Sorts in ascending order (default).DESC: Sorts in descending order.
Example:
SELECT first_name, last_name
FROM employees
ORDER BY last_name ASC, first_name DESC;
In this example, the query retrieves the first_name and last_name columns from the employees table, and sorts the results first by last_name in ascending order and then by first_name in descending order.
LIMIT Clause
The LIMIT clause is used to specify the number of records to return.
SELECT column1, column2, ...
FROM table_name
LIMIT number;
LIMIT number: Specifies the maximum number of records to return.
Example:
SELECT first_name, last_name
FROM employees
ORDER BY last_name ASC
LIMIT 10;
In this example, the query retrieves the first_name and last_name columns from the employees table, sorts the results by last_name in ascending order, and returns only the first 10 records.
DISTINCT Keyword
The DISTINCT keyword is used to return only distinct (different) values.
SELECT DISTINCT column1, column2, ...
FROM table_name;
DISTINCT: Ensures that the selected columns are unique values.
Example:
SELECT DISTINCT department
FROM employees;
In this example, the query retrieves the unique values of the department column from the employees table.
Combining Clauses
You can combine the ORDER BY, LIMIT, and DISTINCT clauses to refine your queries further.
Example:
SELECT DISTINCT first_name, last_name
FROM employees
ORDER BY last_name DESC
LIMIT 5;
In this example:
- The query retrieves distinct
first_nameandlast_namepairs. - The results are sorted by
last_namein descending order. - The query returns the first 5 records from the sorted list.
Full Example
Here’s a full example combining all the elements discussed:
SELECT DISTINCT department, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
In this query:
- Only distinct combinations of
departmentandsalaryare selected. - The results are ordered by
salaryin descending order. - Only the top 3 results are returned.