Lesson 10

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

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

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.

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;