Tuesday, April 19, 2016

SELECT Statement

SELECT Statement
A  select statement is a combination of two or more clauses.
Syntax:
SELECT *|{[DISTINCT] column [alias],...}
FROM table;
·         SELECT tells the columns to be displayed .
·         FROM clause tells from which table the columns to be selected.
·         DISTINCT - eliminates duplicates
·         alias - A different name to a column
Asterisk (*)
You can display all columns of data in a table by following the SELECT keyword with an
asterisk (*).

Example:  The below example displays all the rows and columns in a table called departements.

SELECT * FROM departments;


Selecting Specific Columns

Specific columns can be selected by using SQL select statement.
Example:
select department_id, department_name from departments;

In the above example , out of all the columns in a table departments only department_id,department_name  are selected.

Columns are show in the order they are selected.


SQL : Rules and Guidelines

By using the following simple rules and guidelines, you can construct valid statements that are
both easy to read and edit:
• SQL statements are not case-sensitive (unless indicated).
• SQL statements can be entered on one or many lines.
• Keywords cannot be split across lines or abbreviated.
• Clauses are usually placed on separate lines for readability and ease of editing.
• Indents should be used to make code more readable.
• Keywords typically are entered in uppercase; all other words, such as table names and
columns names, are entered in lowercase.



Arithmetic Expressions

Add                        :               +
Subtract               :               -
Multiply              :               *
Divide                   :               /

Arithmetic Operators
You can use arithmetic operators in any clause of a SQL statement (except the FROM clause).
Note: With the DATE and TIMESTAMP data types, you can use the addition and subtraction
operators only.
Rules of Precedence
• Multiplication and division occur before addition and subtraction.
• Operators of the same priority are evaluated from left to right.
• Parentheses are used to override the default precedence or to clarify the statement.

Examples :
SELECT last_name, salary, salary + 300 FROM employees;

In the above example , 300 is added to the salary of each employee in employees table.

SELECT last_name, salary, 12*(salary + 300) FROM employees;
In the above example , salary of each employee is added with 300 and finally multiplied by  12 to the annual salary of employee.  Here parentheses () is used to override default precedence.


SQL : Null Value

Defining a Null Value

A null value is undefined , unavailable, unassigned, unknown. It is not equal to zero  or blank space . It is simply nothing.

In sql statement if  any row dosent have data, then it is said to be null.
Any arithmetic expression using NULL values results into NULL.

Any column can have null data, but the constraints NOT NULL and Primary key does not allow null values in its columns.


Column Alias

SELECT last_name AS  "Name" , salary*12 "Annual Salary" FROM employees;

In the above example, Name and Annual salary are alias to the columns last_name , salary*12 respectively.

AS keyword can be used as optional before the alias name.


Concatenation Operator

A concatenation operator:
• Links columns or character strings to other columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a character expression

Below is an example of the concatenation.
SELECT last_name||job_id AS "Employees" FROM employees;



Literal Character Strings

• A literal is a character, a number, or a date that is included
in the SELECT statement.
• Date and character literal values must be enclosed within
single quotation marks.
• Each character string is output once for each row returned.

Example : In the below example 'is a'  literal string used.

SELECT last_name ||' is a '||job_id
AS "Employee Details"
FROM employees;

Alternative Quote (q) Operator
• Specify your own quotation mark delimiter.
• Select any delimiter.
• Increase readability and usability.

You can choose any convenient delimiter, single-byte or multibyte, or any of the following
character pairs: [ ], { }, ( ), or < >.

Example :

SELECT department_name || q'[ Department's Manager Id: ]'
|| manager_id
AS "Department and Manager"
FROM departments;

DISTINCT Keyword

To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT
clause immediately after the SELECT keyword.

You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier
affects all the selected columns, and the result is every distinct combination of the columns.

Example :

SELECT DISTINCT department_id, job_id FROM employees;

DESCRIBE command

Use the DESCRIBE command to display the structure of a
table.

DESC[RIBE] tablename

Example :

DESC employees;

DESCRIBE departments;

Monday, April 18, 2016

SQL : Null Value



Defining a Null Value

A null value is undefined , unavailable, unassigned, unknown. It is not equal to zero  or blank space . It is simply nothing.

In SQL statement if  any row doesn't have data, then it is said to be null.
Any arithmetic expression using NULL values results into NULL.


Any column can have null data, but the constraints NOT NULL and Primary key does't  allow null values in its columns.

Queries using nulls:

Any arithmetic operation with null results in null














NULL comparison with NULL yields null


















Below query gives the employee's whose commission is null





SQL : Arithmetic Expressions

Arithmetic Expressions

Add                      :               +
Subtract               :               -
Multiply                :               *
Divide                   :               /

Arithmetic Operators
You can use arithmetic operators in any clause of a SQL statement (except the FROM clause).
Note: With the DATE and TIMESTAMP data types, you can use the addition and subtraction
operators only.
Rules of Precedence
• Multiplication and division occur before addition and subtraction.
• Operators of the same priority are evaluated from left to right.
• Parentheses are used to override the default precedence or to clarify the statement.

Examples :

SELECT last_name, salary, salary + 300 FROM employees;
 In the above example , 300 is added to the salary of each employee in employees table.

SELECT last_name, salary, 12*(salary + 300) FROM employees;

In the above example , salary of each employee is added with 300 and finally multiplied by  12 to the annual salary of employee.  Here parentheses () is used to override default precedence.

SQL : Rules and Guidelines

SQL : Rules and Guidelines

By using the following simple rules and guidelines, you can construct valid statements that are
both easy to read and edit:
• SQL statements are not case-sensitive (unless indicated).
• SQL statements can be entered on one or many lines.
• Keywords cannot be split across lines or abbreviated.
• Clauses are usually placed on separate lines for readability and ease of editing.
• Indents should be used to make code more readable.
• Keywords typically are entered in uppercase; all other words, such as table names and columns names, are entered in lowercase.

SQL : Selecting Specific Columns

Selecting Specific Columns

Specific columns can be selected by using SQL select statement.

Example:
select department_id, department_name from departments;

In the above example , out of all the columns in a table departments only department_id,department_name  are selected.


Columns are show in the order they are selected. 

An Example below:

SQL : Asterisk (*)

Asterisk (*)

You can display all columns of data in a table by following the SELECT keyword with an
asterisk (*).

Example:  The below example displays all the rows and columns in a table called departements.


SELECT * FROM departments;

SQL: SELECT Statement

SELECT Statement

A  select statement is a combination of two or more clauses.

Syntax:
SELECT *|{[DISTINCT] column [alias],...}
FROM table;

  • ·         SELECT - tells the columns to be displayed .
  • ·         FROM - tells from which table the columns to be selected.
  • ·         DISTINCT - eliminates duplicates
  • ·         alias - A different name to a column