LES01

1-1
Lesson 1
Selecting Rows
1-2
Objectives
• Write a SELECT statement to query the database. • Perform arithmetic calculations. • Handle null values. • Specify alternative column headings using aliases. • Concatenate columns. • Edit SQL commands in the SQL*Plus buffer.
SQL> SELECT 2 FROM last_name, salary * 12, commission_pct s_emp;
LAST_NAME SALARY*12 COMMISSION_PCT ------------ ------------ -------------... Havel 15684 Magee 16800 10 Giljum 17880 12.5 Sedeghi 18180 10 Nguyen 18300 15 Dumas 17400 17.5 Maduro 16800 ...
• When DISTINCT is applied to multiple columns,
the result represents the distinct combination of the columns.
1-22
SQL*Plus: Overview
• Log in to SQL*Plus. • Describe the table structure. • Execute SQL from SQL*Plus. • Edit your SQL statement. • Save SQL statements to files. • Execute saved files. • Load commands from file to buffer to edit. • Format columns.
1-8
Column Label Defaults
•
Label default justification:
– Left: date and character data – Right: numeric data •
Label default display is uppercase.
1-9
Arithmetic Expressions
Literal Character String
•
A literal is a character, expression, or number included in the SELECT list.
•
•
Date and character literal values must be enclosed within single quotation marks.
12 rows selected.
1-7
Selecting Specific Columns
SQL> SELECT 2 FROM dept_id, last_name, manager_id s_emp;
• • •
List the columns in the SELECT clause. Separate columns by using a comma. Specify columns in the order you want them to appear.
• •
•
Is represented by two vertical bars (||).
Links columns or character strings to other columns. Creates a resultant column that is a character expression.
1-11
Operator Precedence
•
Multiplication and division take priority over addition and subtraction.
•
•
Operators of the same priority are evaluated from left to right.
Each character string is output once for each row returned.
1-17
Literal Character String: Example
SQL> SELECT 2 3 FROM first_name ||' '|| last_name ||', '|| title "Employees" s_emp;
Employees ------------------------------------------------CarmenVelasquez LaDorisNgao MidoriNagayama MarkQuick-To-See AudryRopeburn MollyUrguhart ...
1-16
SQL> SELECT 2 FROM DISTINCT name s_dept;
1-21
DISTINCT with Multiple Columns
•
DISTINCT applies to all columns in the SELECT list.
SQL> SELECT 2 FROM DISTINCT dept_id, title s_emp;
• Commands can be on one or many lines. • Tabs and indents can be used for readability. • Abbreviations and splitting of words are not
allowed.
• •
Commands are not case sensitive. Commands are entered into the SQL buffer.
1-20
Duplicate Rows
•
The default display of queries is all rows including duplicate rows.
SQL> SELECT 2 FROM name s_uplicate rows by using DISTINCT in the SELECT clause.
Datatypes must match.
– NVL (start_date, '01-JAN-95') – NVL (title, 'No Title Yet') – NVL (salary, 1000)
SQL> SELECT 2 3 FROM last_name, title, salary*NVL(commission_pct,0)/100 COMM s_emp;
Create expressions on NUMBER and DATE datatypes by using operators.
• • • •
Add Subtract Multiply Divide
+ * /
1-10
Arithmetic Expressions
Display the annual salary for all employees.
and alias
Double quotation marks are required if an alias contains spaces, special characters, or is casesensitive.
1-14
Concatenation Operator
The concatenation operator
Parentheses can be used to force prioritized evaluation and to clarify statements.
1-12
Operator Precedence
Parentheses change the order in which a statement is evaluated.
Employees -------------------------------------------Carmen Velasquez, President LaDoris Ngao, VP, Operations Midori Nagayama, VP, Sales Mark Quick-To-See, VP, Finance Audry Ropeburn, VP, Administration Molly Urguhart, Warehouse Manager ...
1-13
Column Aliases
A column alias renames a column heading.
• •
•
Especially useful with calculations
Immediately follows column name
– Optional AS keyword between column name
1-5
Selecting All Columns, All Rows
Simplest SELECT statement contains the following two clauses:
•
•
SELECT clause
– Asterisk (*)
FROM clause