01 ICS 2609 - SQL Part 1

• Projection

• Selection

• Join

• Projection

• Selection

• Join

Capabilities of SQL SELECT Statements

FROM

keyword that identifies which table

SELECT

keyword that identifies what columns

• Not case sensitive

• Can be on one or more lines

• Keywords cannot be abbreviated or split across lines

• Clauses are usually placed on separate lines

• Indents are used to enhance readability

SQL statements are the following:

Arithmetic Operators

Using _________, we can create expressions with number and date data

Multiplication and Division

Takes priority over addition and subtraction

Left to Right

Operators of the same priority are evaluated from __________

Parenthesis

Force prioritized evaluation and to clarify statements

Null

A value that is unavailable, unassigned, unknown, or inapplicable

Zero or Blank Space

A null is not the same as ______ or _______.

Column Alias

• renames a column heading

• Immediately follows the column name

• Immediately follows the column name, either AS keyword or double quotation marks if it contains spaces or special characters or is case-sensitive

Concatenation Operator

• concatenates columns or character strings to other columns

• Is represented by two vertical bars (||)

• creates a resultant column that is a character expression.

Literal

• a character, a number, or a date included in the SELECT list

• Date and character literal values must be enclosed within single quotation marks

• Each character string output once for each row returned

DISTINCT

eliminating duplicate rows

WHERE

Restrict the rows by using _____ clause

FROM

The WHERE clause follows the _____ clause

Single Quotation Marks

Character strings and date values are enclosed in _________.

Case Sensitive, Format Sensitive

Character values are _____, and date values are ______

DD-MON-RR

The default date format is _______

BETWEEN

Using the _________ condition to display row based on a range of values.

IN Membership Condition

Use the _______ to test for values in a list

LIKE

Use the ______ condition to perform wildcard searches of valid search string values.

literal characters or numbers

Search conditions can contain either _________ or ________

%

denotes zero or many characters

__

denotes one character

ESCAPE

you can use the ________ to search for actual % and _ symbols

IS NULL

Tests for nulls with the ______ operator

1. Arithmetic Operators

2. Concatenation Operators

3. Comparison Condition

4. IS [NOT] NULL, LIKE, [NOT] IN

5. [NOT] BETWEEN

6. [NOT] logical condition

7. AND logical condition

8. OR logical condition

Rules of Precedence

ORDER BY

sort rows, it comes last in the SELECT statement