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.