Into Statement  «Prev  Next»

Lesson 10

SQL Select Statement Conclusion

The SELECT statement is by far the most common statement used in SQL. 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.
The 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:
Basic Syntax
SELECT [columns] 
FROM [table_name]
[WHERE condition]
[GROUP BY columns]
[HAVING condition]
[ORDER BY columns [ASC | DESC]];

Key Components and Usage of the Select Statement

  1. Selecting Columns
    • The SELECT clause specifies the columns to retrieve from the database.
    • Use * to select all columns (e.g., SELECT * FROM employees).
    • Specify individual columns for targeted data (e.g., SELECT first_name, last_name FROM employees).
    • Columns can be renamed using aliases for clarity (e.g., SELECT first_name AS fname FROM employees).
  2. FROM Clause
    • Identifies the table(s) from which to retrieve data (e.g., FROM employees).
    • Multiple tables can be queried using JOIN operations (e.g., SELECT e.first_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id).
  3. WHERE Clause
    • Filters rows based on specified conditions (e.g., SELECT * FROM employees WHERE salary > 50000).
    • Supports operators like =, >, <, LIKE, IN, BETWEEN, and logical operators (AND, OR, NOT).
  4. GROUP BY Clause
    • Groups rows with identical values in specified columns into summary rows (e.g., SELECT department_id, COUNT(*) FROM employees GROUP BY department_id).
    • Often used with aggregate functions like COUNT, SUM, AVG, MAX, or MIN.
  5. HAVING Clause
    • Filters grouped data based on conditions (e.g., SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 10).
    • Applied after GROUP BY, unlike WHERE, which filters individual rows.
  6. ORDER BY Clause
    • Sorts the result set based on one or more columns (e.g., SELECT first_name, salary FROM employees ORDER BY salary DESC).
    • Use ASC for ascending (default) or DESC for descending order.
    • Can sort by multiple columns (e.g., ORDER BY department_id ASC, salary DESC).

Advanced Features of the SELECT Statement

  • Joins:
    • Combine data from multiple tables using INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN.
    • Example: SELECT e.first_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id.
  • Subqueries:
    • Nest a SELECT statement within another to retrieve dynamic values (e.g., SELECT first_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)).
  • Aggregate Functions:
    • Perform calculations on data, such as COUNT, SUM, AVG, MAX, and MIN (e.g., SELECT department_id, AVG(salary) FROM employees GROUP BY department_id).
  • DISTINCT Keyword:
    • Eliminates duplicate rows (e.g., SELECT DISTINCT department_id FROM employees).
  • LIMIT / OFFSET:
    • Restricts the number of rows returned, useful for pagination (e.g., SELECT * FROM employees LIMIT 10 OFFSET 20).
    • Syntax may vary (e.g., TOP in SQL Server, FETCH FIRST n ROWS ONLY in some databases).
  • UNION / UNION ALL:
    • Combines results from multiple 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.

Example of SQL Statement

Suppose you have a table `employees` with columns `employee_id`, `first_name`, `last_name`, `salary`, and `department_id`. To retrieve the names and salaries of employees earning more than $50,000, sorted by salary in descending order:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

Use Cases
  • Data Retrieval: Extract specific data for reports or analysis.
  • Data Analysis: Aggregate and summarize data for insights (e.g., average salary per department).
  • Data Integration: Combine data from multiple tables for a unified view.
  • Filtering and Sorting: Present data in a user-friendly format.

Key Characteristics
  • Declarative: You specify what data to retrieve, not how to retrieve it (the database engine optimizes the query).
  • Flexible: Supports complex queries with joins, subqueries, and aggregations.
  • Standardized: Core SELECT syntax is consistent across relational database management systems (RDBMS) like MySQL, PostgreSQL, SQL Server, and Oracle, though some features vary.

The `SELECT` statement’s versatility, combined with its filtering, sorting, and aggregation capabilities, makes it the cornerstone of data manipulation and analysis in relational databases.

SQL SELECT Syntax

SELECT column_name,column_name
 FROM table_name;

and
SELECT * FROM table_name;

  1. The WHERE clause is used to filter records.
  2. The SQL WHERE Clause
  3. The WHERE clause is used to extract only those records that fulfill a specified criterion.
  4. SQL WHERE Syntax

SELECT column_name,column_name
 FROM table_name
 WHERE column_name operator value;

SQL Relational Theory
Statement Description
Data Manipulation
SELECTRetrieves data from the database
INSERTAdds new rows of data to the database
UPDATEModifies existing database data
MERGEConditionally inserts/updates/deletes new and existing rows
DELETERemoves rows of data from the database
Data Definition
CREATE TABLEAdds a new table to the database
DROP TABLERemoves a table from the database
ALTER TABLEChanges the structure of an existing table
CREATE VIEWAdds a new view to the database
DROP VIEWRemoves a view from the database
CREATE INDEXBuilds an index for a column
DROP INDEXRemoves the index for a column
CREATE SCHEMAAdds a new schema to the database
DROP SCHEMARemoves a schema from the database
CREATE DOMAINAdds a new data value domain
ALTER DOMAINChanges a domain definition
DROP DOMAINRemoves a domain from the database
Access Control
GRANTGrants user access privileges
REVOKERemoves user access privileges
CREATE ROLEAdds a new role to the database
GRANT ROLEGrants role containing user access privileges
DROP ROLERemoves a role from the database
Transaction Control
COMMITEnds the current transaction
ROLLBACKAborts the current transaction
SET TRANSACTIONDefines data access characteristics of the current transaction
START TRANSACTIONExplicitly starts a new transaction
SAVEPOINTEstablishes a recovery point for a transaction
Figure 6-10: Major SQL Statements

SEMrush Software 10 SEMrush Banner 10