SELECT,
with its ORDER BY
capabilities and many other uses, is a powerful core SQL statement. In this module, you learned more about the SELECT
statement and how you can put it to use to get the information you need in the format you need it. Using the ability to limit the columns returned and to filter the results means that you can get very specific, small results sets. This means that the information you need is immediately usable.
SELECT
statement in SQL is used to retrieve data from one or more tables in a relational database. It is the primary mechanism for querying and extracting specific data based on user-defined criteria. Below is a comprehensive description of how the SELECT statement is used, including its core components, syntax, and key features:
SELECT [columns] FROM [table_name] [WHERE condition] [GROUP BY columns] [HAVING condition] [ORDER BY columns [ASC | DESC]];
SELECT
clause specifies the columns to retrieve from the database.*
to select all columns (e.g., SELECT * FROM employees
).SELECT first_name, last_name FROM employees
).SELECT first_name AS fname FROM employees
).FROM employees
).SELECT e.first_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id
).SELECT * FROM employees WHERE salary > 50000
).=
, >
, <
, LIKE
, IN
, BETWEEN
, and logical operators (AND
, OR
, NOT
).SELECT department_id, COUNT(*) FROM employees GROUP BY department_id
).COUNT
, SUM
, AVG
, MAX
, or MIN
.SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 10
).GROUP BY
, unlike WHERE
, which filters individual rows.SELECT first_name, salary FROM employees ORDER BY salary DESC
).ASC
for ascending (default) or DESC
for descending order.ORDER BY department_id ASC, salary DESC
).INNER JOIN
, LEFT JOIN
, RIGHT JOIN
, or FULL JOIN
.SELECT e.first_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id
.SELECT
statement within another to retrieve dynamic values (e.g., SELECT first_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)
).COUNT
, SUM
, AVG
, MAX
, and MIN
(e.g., SELECT department_id, AVG(salary) FROM employees GROUP BY department_id
).SELECT DISTINCT department_id FROM employees
).SELECT * FROM employees LIMIT 10 OFFSET 20
).TOP
in SQL Server, FETCH FIRST n ROWS ONLY
in some databases).SELECT
statements (e.g., SELECT first_name FROM employees UNION SELECT first_name FROM contractors
).UNION
removes duplicates; UNION ALL
retains them for better performance.SELECT first_name, last_name, salary FROM employees WHERE salary > 50000 ORDER BY salary DESC;
SELECT
syntax is consistent across relational database management systems (RDBMS) like MySQL, PostgreSQL, SQL Server, and Oracle, though some features vary.SELECT column_name,column_name FROM table_name;
SELECT * FROM table_name;
SELECT column_name,column_name FROM table_name WHERE column_name operator value;
Statement | Description |
---|---|
Data Manipulation | |
SELECT | Retrieves data from the database |
INSERT | Adds new rows of data to the database |
UPDATE | Modifies existing database data |
MERGE | Conditionally inserts/updates/deletes new and existing rows |
DELETE | Removes rows of data from the database |
Data Definition | |
CREATE TABLE | Adds a new table to the database |
DROP TABLE | Removes a table from the database |
ALTER TABLE | Changes the structure of an existing table |
CREATE VIEW | Adds a new view to the database |
DROP VIEW | Removes a view from the database |
CREATE INDEX | Builds an index for a column |
DROP INDEX | Removes the index for a column |
CREATE SCHEMA | Adds a new schema to the database |
DROP SCHEMA | Removes a schema from the database |
CREATE DOMAIN | Adds a new data value domain |
ALTER DOMAIN | Changes a domain definition |
DROP DOMAIN | Removes a domain from the database |
Access Control | |
GRANT | Grants user access privileges |
REVOKE | Removes user access privileges |
CREATE ROLE | Adds a new role to the database |
GRANT ROLE | Grants role containing user access privileges |
DROP ROLE | Removes a role from the database |
Transaction Control | |
COMMIT | Ends the current transaction |
ROLLBACK | Aborts the current transaction |
SET TRANSACTION | Defines data access characteristics of the current transaction |
START TRANSACTION | Explicitly starts a new transaction |
SAVEPOINT | Establishes a recovery point for a transaction |