SQL SELECT Statement Fundamentals

Capabilities of SQL SELECT Statements

• The SQL SELECT statement has three main capabilities: Projection (selecting specific columns), Selection (filtering data), and Join (combining data from multiple tables)

Basic SELECT Statement

• The basic syntax of a SELECT statement is: SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;
• SELECT identifies the columns to be displayed, and FROM identifies the table containing those columns
• The * symbol can be used to select all columns

Selecting Columns

• To select specific columns, list them separately in the SELECT clause, e.g., SELECT department_id, location_id FROM departments;
• To select all columns, use SELECT * FROM table;

Writing SQL Statements

• SQL statements are not case-sensitive
• SQL statements can be entered on one or more lines
• Keywords cannot be abbreviated or split across lines
• Clauses are usually placed on separate lines for readability
• In SQL Developer, SQL statements can be terminated by a semicolon ;

Null Values in Arithmetic Expressions

• Arithmetic expressions containing a null value will evaluate to null
• Example: SELECT last_name, 12*salary*commission_pct FROM employees;

Column Aliases

• A column alias renames a column heading and is useful with calculations
• A column alias immediately follows the column name, and can be separated by the AS keyword
• If the alias contains spaces or special characters, it must be enclosed in double quotation marks

Using Column Aliases

• Example: SELECT last_name AS name, commission_pct comm FROM employees;
• Example: SELECT last_name "Name" , salary*12 "Annual Salary" FROM employees;

Concatenation Operator

• The concatenation operator || links columns or character strings to other columns
• It creates a resultant column that is a character expression
• Example: SELECT last_name||job_id AS "Employees" FROM employees;

Literal Character Strings

• A literal is a character, number, or date that is included in the SELECT statement
• Date and character literal values must be enclosed in single quotation marks
• Each character string is output once for each row returned

Using Literal Character Strings

• Example: SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;

Alternative Quote (q) Operator

• The alternative quote operator allows you to specify your own quotation mark delimiter
• It increases readability and usability
• Example: SELECT department_name || ' Department' || q'['s Manager Id: ]' || manager_id AS "Department and Manager" FROM departments;

Duplicate Rows

• By default, queries display all rows, including duplicates
• The DISTINCT keyword can be used to remove duplicate rows
• Example: SELECT department_id FROM employees; vs SELECT DISTINCT department_id FROM employees;