Retrieving and manipulating Data in Oracle & Sql server 2008
Writing Basic SQL SELECT Statements
After completing this lesson, you should be able to
Do the following:
¡ List the capabilities of SQL SELECT statements
¡ Execute a basic SELECT statement
Basic SELECT Statement
SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;
Selecting All Columns
¡ SELECT *FROM departments;
Selecting Specific Columns
¡ SELECT department_id, location_id FROM departments;
Writing SQL Statements
¡ SQL statements are not case sensitive.
¡ SQL statements can be on one or more lines.
¡ Keywords cannot be abbreviated or split
across lines.
across lines.
¡ Clauses are usually placed on separate lines.
¡ Indents are used to enhance readability.
Arithmetic Expressions
Operator
|
Description
|
+
|
Add
|
-
|
Subtract
|
*
|
Multiply
|
/
|
Divide
|
Operator Precedence
¡ Multiplication and division take priority over addition and subtraction.
¡ Operators of the same priority are evaluated from left to right.
¡ Parentheses are used to force prioritized evaluation and to clarify statements.
Using Arithmetic Operators
¡ SELECT last_name, salary, salary + 300 FROM employees;
Operator Precedence
¡ SELECT last_name, salary, 12*salary+100 FROM employees;
¡ SELECT last_name, salary, 12*(salary+100) FROM employees;
Defining a Null Value
¡ A null is a value that is unavailable, unassigned, unknown, or inapplicable.
¡ A null is not the same as zero or a blank space.
¡ Arithmetic expressions containing a null value evaluate to null.
Defining a Column Alias
¡ Renames a column heading
¡ Is useful with calculations
¡ Immediately follows the column name - there can also be the optional AS keyword between the column name and alias
¡ Requires double quotation marks if it contains spaces or special characters or is case sensitive
¡ SELECT last_name AS name, commission_pct comm FROM employees;
¡ SELECT last_name "Name", salary*12 "Annual Salary" FROM employees;
Concatenation Operator
¡ Concatenates columns or character strings to other columns
¡ Is represented by two vertical bars (||) àOracle
¡ Is represented by two vertical bars (+) à Sql Server
¡ Creates a resultant column that is a character expression
¡ Oracle
¡ SELECT last_name||first_name AS "Employee Name" FROM employees;
¡ Sql Server
¡ SELECT last_name+first_name AS "Employee Name" FROM employees;
Literal Character Strings
¡ A literal is 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 is output once for each
row returned
row returned
¡ Oracle
¡ SELECT last_name +' '+ first_name AS "Employee Name" FROM employees;
¡ Sql Server
¡ SELECT last_name||' ' ||first_name AS "Employee Name" FROM employees;
Duplicate Rows
¡ The default display of queries is all rows, including duplicate rows.
¡ Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause.
¡ SELECT DISTINCT department_id FROM employees;
+ comments + 1 comments
بارك الله فيك....مجهود رائع
Post a Comment