SQL SELECT Statement Fundamentals

24 Questions

What is the main function of the SELECT clause in a SQL statement?

To identify the columns to be displayed

What is the purpose of the FROM clause in a SQL statement?

To identify the table containing the columns

What does the asterisk (*) symbol represent in a SELECT statement?

All columns in the table

What is the keyword used to select only distinct values in a SQL statement?

DISTINCT

What is the purpose of using column aliases in a SQL statement?

To give a temporary name to a column in the result

Can SQL statements be entered on multiple lines?

Yes, SQL statements can be on multiple lines

What is the purpose of using a semicolon (;) in SQL statements?

To separate multiple SQL statements

What is the case sensitivity of SQL statements?

SQL statements are not case-sensitive

What is the purpose of the concatenation operator in a SELECT statement?

To link columns or character strings to other columns

What is the symbol used to represent the concatenation operator?

What happens when a literal character string is included in a SELECT statement?

It is output once for each row returned

What is the purpose of the Alternative Quote (q) Operator?

To specify a delimiter for the quotation mark

What is the default display of queries in a SELECT statement?

All rows, including duplicate rows

What is the purpose of the DISTINCT keyword in a SELECT statement?

To filter out duplicate rows in the result set

What is the result of combining two character strings using the concatenation operator?

A character expression

What is the purpose of enclosing date and character literal values in single quotation marks?

To distinguish them from column names

What does a column alias do in a SELECT statement?

It renames a column heading

What happens when a null value is included in an arithmetic expression?

The result is null

What is the purpose of the AS keyword in a column alias?

It is optional and used to separate the column name and alias

What is the result of the SELECT statement: SELECT last_name, 12salarycommission_pct FROM employees?

A column with the last name and null values

What is the purpose of the DESCRIBE command?

It is used to describe the structure of a table

How are column aliases separated from the column name if they contain spaces or special characters?

Using double quotation marks

What is the purpose of a basic SELECT statement?

To retrieve data from a table

What is the result of the SELECT statement: SELECT last_name AS name, commission_pct comm FROM employees?

A column with the last name and the commission percentage with the alias 'name' and 'comm'

Study Notes

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;

Learn the basics of retrieving data using SQL SELECT statements, including its capabilities, arithmetic expressions, and column aliases. Practice executing basic SELECT statements and more.

Make Your Own Quizzes and Flashcards

Convert your notes into interactive study material.