About Oracle SQL

SELECT Statement

•Syntax :

  SELECT  * | { [DISTINCT]  column | expression [alias],…}

  FROM table;

•SELECT identifies what columns

•FROM identifies which table

In simple form, it must include

•  A SELECT clause

•       A FROM clause

In the syntax :

  SELECT  is a list of one or more columns

  *  selects all columns

  DISTINCT  suppresses duplicates

  column/expression    selects the named column or the expression

  alias  gives selected columns different headings

  FROM table  specifies the table containing the columns

•Capabilities of SQL SELECT Statements

  A SELECT Statement retrieves information from the database. Using a SELECT Statement we can do :

•  Projection :  You can use the projection capability in SQL to choose the columns in a table that you want returned by your   query.You can choose as few or as many columns of the table as you require

•     Selection :  You can use the selection capability in SQL to choose the rows in a table that you want returned by a query.   You can use various criteria to restrict the rows that you see.

•     Joining :  You can use the join capability in SQL to bring together data that is stored in different tables by creating a link   between them. You learn more about joins in a later lesson.

Selecting Columns

•All Columns

  SELECT  *

  FROM departments;

•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 Operations

Operator	Description
+	Add
-	Substract
*	Multiply
/	Divide

 

Operator Precedence :   *    /     +     -

•Multiplication and division take priority over addition and substraction

•Operators of the same priority are evaluated from left to right

•Parentheses are used to force prioritized evaluation and to clarify statements

Arithmetic Operators :

  SELECT last_name, salary, salary +300

  FROM employees;

•Operator Precedence :

  SELECT last_name,salary, 12*salary+100

  FROM employees;

•Using Parentheses :

  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.

•Example :

  SELECT last_name,  job_id, salary, commission_pct

  FROM employees;

•Arithmetic expressions containing a null value evaluate to null

•Example :

       SELECT last_name, job_id, 12*salary*commission_pct

       FROM employees;

Examples : the following expressions give the result as NULL

1.    select 1/NULL  from dual;
2.    select NULL/NULL  from dual;
3.    select 1+NULL  from dual;

Defining a column alias

A column alias :

•Renames a column heading

•Is useful with calculations

•Immediately follows the column name

•An optional  AS keyword can be used

•Requires double quotations if it contains spaces or special characters or is case sensitive

•Example :

      SELECT last_name  name, 12*salary AS  “Annual Salary”

      FROM employees;

Concatenation Operator:

•Concatenates columns or character strings to other columns

•Operator is two vertical bars ( || )

•Creates a resultant column that is a character expression

•Example :

  SELECT last_name || job_id AS  "Employees"

  FROM     employees;

Result : 
        Employees
         KingAD_PRES
KochharAD_VP
De HaanAD_VP
HunoldIT_PROG
ErnstIT_PROG
AustinIT_PROG

Literal Character Strings :  

•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

•Literal strings included in the query are treated as a column in the SELECT list.

•Example :

SELECT last_name || ' is a ' || job_id  AS
                    "Employee Details"
        FROM    employees;        Result :
         EmployeeDetails
         King is a AD_PRES
Kochhar is a AD_VP
De Haan is a AD_VP
Hunold is a IT_PROG
Ernst is a IT_PROG
 
Eliminating DISTINCT Rows: 

•The default display is all rows, including duplicate rows

•To eliminate duplicate rows,  use the DISTINCT keyword

•Example :

  SELECT  DISTINCT  department_id

  FROM employees;

 
Functions:   

SQL FUNCTIONS :

•  Perform calculations on data

•  Modify individual data items

•  Manipulate output for groups of rows

•  Format dates and numbers for display

•  Convert column data type

•  They some times take arguments and always return a value.

Query: select first_name, lower(first_name), upper(first_name), initcap(first_name) from employees;
Result :

Functions are two types : 

 1.Single Row Functions
          2.Multiple Row Functions

•Character functions :   Accept character input and can return both characters and number values

• Number functions :  Accept numeric input and return numeric values

• Date functions  :  Operate on values of the DATE data type

• Conversion functions : Convert a value from one data type to another

• General functions : 

  -  NVL

  -  NVL2

  -  NULLIF

  -  COALSECE

  -  CASE

  -  DECODE

Example for : NVL, NVL2
Query : SELECT NVL(null,100), NVL(NULL,NULL), NVL(10,50), NVL2(10,20,30), NVL2(NULL,40,50) FROM dual;
Result :

Single Row Functions

•   Manipulate data items

•   Accept arguments and return one value

•   Act on each row returned

•   Return one result per row

•   May modify the data type

•   Can be nested

•   Accept arguments which can be a column or an   expression function_name [ (arg1, arg2, …) ]

•   Can be used in SELECT, WHERE, and ORDER BY   clauses

The argument can be :

•  User supplied constant

•   Variable value

•   Column name

•   Expression

 

Character Functions : 

  function  purpose

  LOWER(col/expr)  Converts alpha character values to lower case

  UPPER (col/expr)   Converts alpha character values to upper case

  INITCAP (col/expr)   Converts alpha character values to upper case for the first letter of each word, all other letters in   lowercase

  CONCAT(col1 , col2)  Concatenates the first character value to the second character value; equivalent to concatenation   operator ( || )

  SUBSTR(col, m [,n] )  Returns specified characters from character value started at character position m, n characters long

  If m is negative, the count starts from end of the character value.

  If n is omitted, all characters to the end of the string are returned

  LENGTH(col)  returns the number of characters in the expression

  INSTR(col,’string’ [,m], [n])    returns the numeric position of a named string. Optionally, we can provide a position m to start   searching, and the occurrence n of the string. M and N default to 1.

  LPAD(col, n, ‘string’)  Pads the character value right justified to a total width of n character positions

  RPAD(col, n, ‘string’)  Pads the character value left justified to a total width of n character positions

  TRIM(leading/trailing/both,  Enables you to trim heading or trailing characters (or both) from a character string.

           trim_character FROM

  trim_source)

  REPLACE(text, search_string,  Searches a text expression for a character string and, if found, replaces it with specified

  replacement-string)  string.

Arithmetic Operators :  
Arithmetic Operators :