What are you looking for ?
Home » » Lesson (1) In Oracle & SQl Server 2008

Lesson (1) In Oracle & SQl Server 2008


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.
¡  Clauses are usually placed on separate lines.
¡  Indents are used to enhance readability.

Arithmetic Expressions


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
¡  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;

Share this article :

+ comments + 1 comments

27 May 2013 at 11:01

بارك الله فيك....مجهود رائع

Post a Comment

Flag Counter

Social Profile

Copyright x 2011. By Wael Medhat - All Rights Reserved