SQL - CH 2: Basic SQL SELECT Statements

Important Points about SELECT Statements

- the only clauses required for the SELECT statement are SELECT and FROM. - square brackets indicate optional portions of the statement. - SQL statements can be entered over several lines or on one line. Most SQL statements are entered with each clause on a separate line to improve readability and make editing easier. - an SQL statement ends with a semicolon

Standard Order of Operations

1) moving from left to right in the arithmetic equation, any required multiplication and division operations are solved first. 2) addition and subtraction operations are solved after multiplication and division, again moving from left to right (to override this __, you can parentheses to enclose the portion of the equation that should be calculated first)

Syntax of a column alias for concatenated values

SELECT columnname || ' ' || columnname "Alias Name" FROM tablename;

By default, column headings shown in query results are ...

capitalized

Keywords

capitalized words (SELECT, FROM, WHERE, and so forth)

Projection

choosing specific columns in a SELECT statement

Sometimes a column name is a vague indicator of the data that's displayed. To better describe the data displayed in the output, you can substitute a ...

column alias

SELECT Clause Identifies

column(s)

Concatenation (||) SELECT columnname || columnname FROM tablename; (To make the results more readable, you need to include a blank space between the column names.) SELECT columnname || ' ' || columnname FROM tablename;

combining the contents of two or more columns - can also be used to __ or combine fields, literals, and other data

You must separate a list of fields with ...

commas - if you forget a comma, Oracle 11g interprets the subsequent field name as a column alias, and you don't get the results you intended.

The Optional Keyword of AS

distinguishes between the column name and the column alias

Clause Example: SELECT clause, FROM clause, WHERE clause, and so on

each section that begins with a keyword

SELECT Statements

enables users to retrieve data from tables - a user can view all the fields and records in a table or specify displaying only certain fields and records

NULL

if no value is entered for a column in a row of data, the value is considered __, indicating an absence of data - __ values can lead to undesirable results in operations

The asterisk (*) Symbol SELECT * FROM tablename;

instructs Oracle 11g to include all columns in the table - this symbol can be used only in the SELECT clause of a SELECT statement

String Literal (' ') SELECT columnname || ' ' || columnname FROM tablename; - you can also use __s to include any characters needed to produce output in a certain format SELECT columnname || ' : ' || columnname || ', '|| columnname "Alias Name" FROM tablename

instructs Oracle 11g to interpret the characters you have entered "literally," not to consider them a keyword or command

Arithmetic Operators

multiplication(*) division(/) addition(+) subtraction(-)

Enclose in Double Quotation Marks (" ")

overrides the default setting use - if it contains blank spaces(s) - it if contains special symbol(s) - to retain case (if you don't want it to appear in all uppercase letters)

FROM Clause Identifies

table(s)

Query

the SELECT statement asks the database a question

Syntax

the basic structure, or rules, required to execute the statement

DISTINCT (UNIQUE can also be used) SELECT DISTINCT columnname FROM tablename; or SELECT UNIQUE columnname FROM tablename;

to eliminate duplicate listings - the ___ keyword is applied to all columns listed in the SELECT clause, even though it's stated directly after the SELECT keyword.