SQL SELECT Statement and Data Retrieval and Management Guide

Capabilities of the SQL SELECT Statement

• The SQL SELECT statement possesses three primary capabilities for data retrieval:
  • Projection Capability: This allows for the selection of specific columns in a table that are to be returned by a query.
  • Selection Capability: This allows for the selection of specific rows in a table based on criteria to be returned by a query.
  • Join Capability: This allows for the integration of data stored in different tables by creating a link between them.

Basic SELECT Statement Structure

• A basic SELECT statement consists of two primary clauses:
  • SELECT Clause: Identifies the specific columns that are to be displayed.
  • FROM Clause: Specifies the table that contains the columns listed in the SELECT clause.
• Selecting All Columns: To display every column of data in a table, the SELECT keyword is followed by an asterisk ($*$).
• Selecting Specific Columns: To display particular columns, the column names must be specified and separated by commas.

Rules and Guidelines for Writing SQL Statements

• SQL statements are not case sensitive.
• Statements can span one or more lines to improve layout.
• Keywords cannot be abbreviated or split across multiple lines.
  • Example: "SELECT" cannot be written as "SEL" or split as "SEL-ECT".
• Clauses are typically placed on separate lines to improve readability.
• Indents should be used to enhance the visual clarity and readability of the code.

Arithmetic Expressions and Operators

• Arithmetic expressions can be created using number and date data with the following operators:
  • $+$: Add
  • $-$: Subtract
  • $\times$: Multiply
  • $/$: Divide
• When an arithmetic operator is used (e.g., adding $300$ to a salary), the resulting calculated column is not a permanent new column in the table; it exists for display purposes only within the query result.

Operator Precedence

• The evaluation of arithmetic expressions follows specific priority levels:
  • Multiplication ($\times$) and division ($/$) take priority over addition ($+$) and subtraction ($-$).
  • Operators with the same priority level are evaluated from left to right.
  • Parentheses are used to force a specific evaluation order and to clarify the intent of the statement.

Defining and Handling NULL Values

• A NULL value represents data that is UNAVAILABLE, UNASSIGNED, UNKNOWN, or INAPPLICABLE.
• A NULL value is strictly distinct from the number zero ($0$) or a blank space.
• While columns of any data type can contain nulls, certain constraints such as NOT NULL and PRIMARY KEY prevent nulls from being used in specific columns.
• NULL Values in Arithmetic Expressions: If any column value in an arithmetic expression is null, the entire result of that expression will be null.

Column Aliases

• A column alias is used to rename a column heading in the output.
• Aliases are particularly useful when dealing with calculated columns.
• An alias immediately follows the column name. The optional AS keyword can be placed between the column name and the alias for clarity.
• Double quotation marks are required for an alias if it:
  • Contains spaces.
  • Contains special characters.
  • Requires specific case sensitivity (e.g., "Total Salary").

Concatenation and Literal Character Strings

• Concatenation Operator: Represented by two vertical bars ($||$), this operator links columns or character strings to other columns, creating a resultant column that is a character expression.
• Literal Character Strings: A literal value is a character, number, or date included in the SELECT list that is not a column heading or value from the table.
  • Character and date literals must be enclosed in single quotation marks (' ').
  • Number literals do not require quotation marks.
  • Literal strings are output once for every row returned by the query.

Managing Rows and Table Metadata

• Eliminating Duplicate Rows: Use the DISTINCT keyword in the SELECT clause to remove duplicate entries from the result set.
  • Syntax: SELECT DISTINCT department_id FROM employees;
• Displaying Table Structure: Use the DESCRIBE command to view the structure (columns, data types, and constraints) of a table.
  • Syntax: DESCRIBE employees

Restricting Data with the WHERE Clause

• The WHERE clause is used to limit the rows returned to only those that meet a specified condition.
• The WHERE clause must follow the FROM clause.
• A condition is composed of column names, expressions, constants, and comparison operators.
• Character Strings and Dates in Conditions:
  • Must be enclosed in single quotation marks.
  • Character values are case sensitive (e.g., 'Goyal' is different from 'goyal').
  • Date values are format sensitive.
  • The default date format is DD-MON-RR.

Comparison Conditions and Operators

• Basic comparison operators include:
  • $=$: Equal to
  • $>$: Greater than
  • $>=$: Greater than or equal to
  • $<$: Less than
  • $<=$: Less than or equal to
  • $<>$: Not equal to
• Other Comparison Conditions:
  • BETWEEN … AND …: Matches values within a range (inclusive of the lower and upper limits).
  • IN (set): Matches any value within a specified list.
  • LIKE: Performs wildcard searches using character patterns.
    • $\%$: Denotes zero or many characters.
    • $\_$: Denotes exactly one character.
  • IS NULL: Tests for null values.

Logical Conditions

• Logical operators combine or negate multiple conditions:
  • AND: Returns TRUE if both component conditions are true.
  • OR: Returns TRUE if either component condition is true.
  • NOT: Returns TRUE if the following condition is false.

Comprehensive Rules of Precedence

• SQL evaluates operators in the following descending order of priority:
  1. Arithmetic operators ($+$, $-$, $\\times$, $/$)
  2. Concatenation operator ($||$)
  3. Comparison conditions ($=, >, <$, etc.)
  4. IS [NOT] NULL, LIKE, [NOT] IN
  5. [NOT] BETWEEN
  6. NOT logical condition
  7. AND logical condition
  8. OR logical condition
• These rules of precedence can be overridden by using parentheses.

Sorting Data with the ORDER BY Clause

• The ORDER BY clause is used to sort the rows returned by a query.
• The ORDER BY clause always comes last in the SELECT statement sequence.
• Sorting Options:
  • ASC: Sorts in ascending order (this is the default behavior).
  • DESC: Sorts in descending order.
• Syntax Example: SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date;