Retrieving Data Using the SQL SELECT Statement

Selecting All Columns :

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

In the example in the slide, the department table contains four columns: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID. 

The table contains seven rows, one for each department. 

SELECT * FROM departments;

You can also display all columns in the table by listing all thecolumns after the SELECT keyword. 

For example, the following SQL statement (like the example in the slide) displays all columns and all rows of the DEPARTMENTS table:

SELECT Department_id, Department_name, Manager_id, Location_id 

FROM departments;

Selecting Specific Columns :

SELECT Department_id, Department_name

FROM departments;

Writing Sql statement :

• SQL statements are not case sensitive.

     SELECT Employee_id, first_NAMe, salary, department_id 

     FROM empLOYEes;

• SQL statements can be on one or more lines.

     SELECT Employee_id, first_name, salary, department_id

     FROM employees;

                           [ OR ]

     SELECT 

     Employee_id    , 

     First_name     , 

     Salary         , 

     department_id

     FROM 

     employees;

• Keywords cannot be abbreviated or split across lines.

     SEL

     ECT 

     Employee_id    , 

     First_name..      

• Clauses are usually placed on separate lines.

             SELECT 

             Employee_id    , 

             First_name     , 

             Salary         , 

             department_id

             FROM 

             employees;

• Indents are used to enhance readability.

             SELECT 

                 Employee_id    , 

                 First_name     , 

                 Salary         , 

                 department_id

             FROM 

                 employees;

• In iSQL*Plus, SQL statements can optionally be terminated by a semicolon (;). Semicolons are required if you execute multiple SQL statements.

    Tool : iSQL Plus

     SELECT 

        Employee_id    , 

        First_name     , 

        Salary         , 

        department_id

     FROM 

        employees;         -- Here ; is a optional one

• In SQL*Plus, you are required to end each SQL statement with a semicolon (;).

    Tool : SQL Plus

     SELECT 

        Employee_id    , 

        First_name     , 

        Salary         , 

        department_id

     FROM 

        employees;

Arithmetic Expression :

    +  ,  -  ,   /  ,  *


SELECT First_name, salary , salary * 12 FROM employees;

sal = 1000

12*sal  =  12000

12*sal+100 = 12100     (Expected result is 13200)

Because * has more priority than +

12*(sal+100) = 13200   [ ( ) has more priority than * ]

Operator Priority :

BODMAS Rule

BO       D       M       A      S
 |           |          |         |        |_________     -
 |           |          |         |
 |           |          |         |______________    +

 |           |          |  
 |           |          |___________________    *
 |           |   

 |           |_________________________    /

 |
 |_______________________________  (  )



Defining Null Value:

•      Null is a value that is unavailable, unassigned, unknown, or inapplicable.

•      Null is not the same as zero or a blank space.

SELECT first_name, commission_pct

FROM employees;

Arithmetic expressions containing a null value evaluate to null.

SELECT first_name, commission_pct , commission_pct + 500

FROM employees;

\

Alias:

Renames a column heading

Is useful with calculation

SELECT first_name,  last_name,  salary, salary*12  FROM employees;

SELECT first_name,  last_name,  salary, salary*12 AS "Ann_salary"  FROM employees;

Immediately follows the column name (there can be a optional AS keyword between the column and Alias Name)

SELECT first_name,  last_name,  salary, salary*12 "Ann_salary"  FROM employees;

Requires double quotation marks if it contains spaces or special characters or if it is case sensitive

SELECT first_name,  last_name,  salary, salary*12 Ann_salary  FROM employees;

SELECT first_name,  last_name,  salary, salary*12 Ann salary  FROM employees;

ORA-00923: FROM keyword not found where expected

00923. 00000 -  "FROM keyword not found where expected"

*Cause:   

*Action:

Error at Line: 71 Column: 55

SELECT first_name,  last_name,  salary, salary*12 "Ann salary"  FROM employees;

Concatenation:

SELECT First_name || salary FROM employees;

SELECT First_name ||' '|| salary FROM employees;

SELECT First_name ||' salary is '|| salary FROM employees;

SELECT First_name ||''s salary is '|| salary FROM employees;

ORA-00923: FROM keyword not found where expected

00923. 00000 -  "FROM keyword not found where expected"

*Cause:   

*Action:

Error at Line: 87 Column: 25

--10g

SELECT First_name ||q'['s salary is ]'|| salary FROM employees;

Default DATE format in oracle:

      21-JAN-13  (If you want to change you can)

     

      DD-MON-YY

     

SELECT * FROM employees;

Distinct:

SELECT DISTINCT Department_id FROM employees;

SELECT department_id, department_name,  ? 

DESC [RIBE]:

DESCRIBE departments;

DESC employees;

Single Line comment:   

--

Multiple Line comment:

/*

   your comment 

*/

Example:

SELECT

   First_name,

   Last_name,

   --salary,

   hire_date,   /*  emp table

                    info  */

   department_id

FROM

   employees;

    Thanks For Reading!!!