SQL SELECT STATEMENTS

 To extract data from database you need use the structured query language(SQL) : SELECT statement.
Using a select statement you can do the following:-
 Projection: You can use the projection capability in SQL to choose the column in  a table that you want returned by your query.
You can choose few or many columns of the table as you require.
Selection: You can user selection capability in SQL to choose the rows in a table that you want returned by your query. You can use various criteria to restrict the        rows that you see.
Joining: You can user the join capability in SQL to bring together data that is stored in different tables by creating a link between them.
 
Basic SELECT statement
SELECT *|{[DISTINCT] column| expression [alias],}
FROM table;
A SELECT clause, specifies the columns to be displayed
A FROM clause, specifies the tables containing the columns listed in the SELECT clause.
SELECT,FROM - Keywords
SELECT employee_id, last_name,is a clause ( it is a part of SQL statement)
SELECT * FROM employees; - is a SQL statement.
Selecting ALL elements:
SELECT * FROM employees;
Selecting SPECIFIC columns:
SELECT department_id, location_id
FROM departments;
You can user SELECT statement to display specific columns of the table by specifying the column names, separated by commas.
In the SELECT clause, specify the columns that you want, in the order in which you want them to appear in the output.
SQL statements are not case sensitive.
Keywords can not be abbreviated or split across lines.
Clauses are usually placed on separate lines
Keywords typically are entered in uppercase, all other words, such as table name and columns, are entered in lowercase. Column heading defaults
 iSQL*  Plus:- Default heading justification : Centre - Default heading display : uppercase
 
SQL* Plus:
      - Characters and Date column headings are lest-justified
      -  Number column headings are right justified
      -  Default heading display : uppercase
 
 Arithametic expression
 
Operators Description
+ ADD
- Subtract
* Multiply
/ Divide
 
Create expressions with number and date data by using arithmetic operators.
An arithmetic expression can contain column names, constant numeric values, and the arithmetic operators.
You can use arithmetic operators in any clause of sql except in the FROM clause.
SELECT last_name, salary, salary + 300
FROM employees;
This example calculate a salary increase of 300 for all employees and display a new SALARY+300 in the output.
 
Note: The resultant column SALARY+300 is not a new column in the EMPLOYEES table, it is for display only.
The oracle 9i server ignores the spaces before and after the arithmetic operators.
Multiplication and Division take priority over addition and subtraction.
Operators in the same priority are evaluated from left to right.
Parenthesis "()" are used to force prioritized evaluation and to clarify statements.
 
SELECT last_name, salary, 12*salary+100.
FROM employees;
Here the arithmetic expression calculates the annual compensation as 12 multiplied by the monthly salary, plus a onetime bonus of 100. but due to the operator precedence this bonus got added on annual salary.
 
Using parenthesis:
SELECT last_name, salary, 12*(salary+100)
FROM empoyees;
Here the arithmetic expression calculates the annual compensation as monthly salary plus a monthly bonus of 100, multiplied by 12. because of parenthesis            addition takes priority over multiplication.
 
NULL Values
A null value is unknown, unavailable, unassigned, inapplicable.
Its not equal to zero(0) or space( ).
If a row lacks the data value for a particular column, that value is said to be null.
Primary key and Not Null columns can not have null values.
 Null values in arithmetic expression gives null in output.
SELECT last-named, 12*salary*commission_pct
FROM employees;
For some employees 'commission_pct' value may be null, so this SQL output could be null for the column '12*salary*commission_pct' for those employees.
If you try to divide a number with zero (0) the result will be error, but if you divide a number with null, the result is null.
 
Column Alias
it is used to rename a column heading
Immediate following 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 character or is a case sensitive.
SELECT last_name AS name, commission_pct com
FROM empoyees;
SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;
 
Concatenation Operators
Concatenates columns or character strings to other columns.
Is represented by ||
Creates a resultant column that is a character expression.
 -> Columns on either side of this operator combines to make a single column.       
 FROM employees; SELECT last_name||job_id AS "Employees"
         FROM employees;
 -> Here last_name and job_id are concatenated, and they are given the alias.
 
 Literal Character Strings
 -> A litercal is a character, number, or a date included in the select statement.
 -> Date and character literal vlaues must be enlosed within single quotation marks. ' '
 -> Each character string is output once for each row returned.
         SELECT last_name || ' is a '|| job_id AS "Employeed Details"
         From employees;
 
 Duplicate Rows
 -> The default display of wueries is all rows, including duplicate rows.
         SELECT department_id
         FROM employees;
 -> Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause.
 -> Use DISTINCT keyword immediate after SELECT keyword.
         SELECT DISTINCT department_id
         FROM employees;
 -> You can specify multiples collumns after DISTINCT keyword. The DISTINCT          qualifier affects all the selected columns, and the result is every distinct
         combination of the columns..
         SELECT DISTINCT department_id, job _id
         FROM employees;
 
 Displaying TABLE STRUCTURE
 -> DESCRIBE/desc command is used to display structure of table.
 -> DESCRIBE employees