Oracle SQL Query writing Helper

Quick Reference Oracle SQL Query writing. Go through this post and learn Oracle SQL writing within 2 hours. Quick reference Oracle DML,DDL,DCL Operations.

SELECT Retrieves data from the database.

INSERT ,UPDATE,DELETE,MERGE

Enters new rows, changes existing rows, and removes unwanted rows from tables in the database, respectively. Collectively known as data manipulation language (DML).

CREATE, ALTER ,DROP

RENAME

TRUNCATE

Sets up, changes, and removes data structures from tables. Collectively known as data

Definition language (DDL).

COMMIT

ROLLBACK

SAVEPOINT

Manages the changes made by DML statements. Changes to the data can be grouped together into logical transactions.

GRANT

REVOKE

Gives or removes access rights to both the Oracle database and the structures within it. Collectively known as data control language(DCL).

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

FROM table;

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

SELECT * FROM Table Name; àSelecting All Columns of All Rows

SELECT Col1, col2. . Col9

        From Table Name;  à Selecting Specific Columns of All Rows

üOperator Precedence -- >    *  /  +  -

ü  Defining a Column Alias

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;

SELECT last_name||job_id AS "Employees"

üFROM employees;

LAST_NAME and JOB_ID are concatenated, and they are given the alias

Employees.

SELECT last_name ||’ is a ’||job_id

AS "Employee Details"

FROM employees;

The slide displays last names and job codes of all employees. The column has the

Heading Employee Details.

üEliminating Duplicate Rows

SELECT DISTINCT department_id FROM employees;

SELECT DISTINCT department_id, job_id FROM employees;

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

immediately after the SELECT keyword. In the example on the slide, the EMPLOYEES table actually Contains 20 rows but there are only seven unique department numbers in the table.

                                                                                                 

ü  Displaying Table Structure à  DESC[RIBE] tablename

Null? -----> indicates whether a column must contain data; NOT NULL indicates that a

                    Column must contain data

Type ---à    displays the data type for a column

                  

ü  Limiting the Rows Selected à

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

   FROM table [WHERE condition(s)];

Ø  SELECT employee_id, last_name, job_id, department_id

         FROM employees

ü  Comparison Conditions

Operator      Meaning

=                      Equal to

>                      Greater than

>=                    Greater than or equal to

<                      Less than

<=                    Less than or equal to

            <>                    Not equal to

Ø  WHERE department_id = 90; SELECT last_name, salary

   FROM employees

   WHERE salary <= 3000;

ü  Other Comparison Conditions

            Operator                                Meaning

            BETWEEN...AND...   Between two values (inclusive),

            IN(set)                         Match any of a list of values

            LIKE                                        Match a character pattern

            IS NULL                                 Is a null value

  

Ø  SELECT last_name, salary

      FROM employees

        WHERE salary BETWEEN 2500 AND 3500;

Ø  SELECT employee_id, last_name, salary, manager_id

   FROM employees 

   WHERE manager_id IN (100, 101, 201);

Ø  SELECT first_name

   FROM employees

    WHERE first_name LIKE ’S%’; à % denotes zero or many characters.

Ø  SELECT last_name, manager_id

   FROM employees

   WHERE manager_id IS NULL; àTest for nulls with the IS NULL operator.

ü  ORDER BY Clause

          Sort rows with the ORDER BY clause

       – ASC: ascending order, default

       – DESC: descending order

• The ORDER BY clause comes last in the SELECT statement .

Ø  SELECT last_name, job_id, department_id, hire_date

   FROM employees ORDER BY hire_date ;

Ø  SELECT last_name, job_id, department_id, hire_date

   FROM employees ORDER BY hire_date DESC ;

• The order of ORDER BY list is the order of sort.

Ø  SELECT last_name, department_id, salary

   FROM employees

   ORDER BY department_id, salary DESC;

             

Operation                             Result                         Description

Date + number                       Date                Adds a number of days to a date

Date – number                      Date                Subtracts a number of days from a date

Date - date                  Number of days         ubtracts one date from another

            ate + number/24                    Date                adds a number of hours

ü  Nesting Functions

Ø  SELECT last_name,

     NVL(TO_CHAR(manager_id), ’No Manager’)

     FROM employees

                    WHERE manager_id IS NULL;

NVL Function

Converts a null to an actual value.

• Data types that can be used are date, character, and number.

• Data types must match:

   – NVL (commission_pct,0)

   – NVL (hire_date,’01-JAN-97’)

   – NVL (job_id,’No Job Yet’)

ü JOIN

        § Write SELECT statements to access data from more than one table using equality and nonequality joins.

• View data that generally does not meet a join condition by using outer joins.

• Join a table to itself by using a self join.

• A Cartesian product is formed when:

            – A join condition is omitted

            – A join condition is invalid

– All rows in the first table are joined to all rows in the second table

• To avoid a Cartesian product, always include a valid join condition in a WHERE clause.

Types of Joins

Oracle Proprietary Joins (8i and prior):

• Equijoin

• Non-equijoin

• Outer join

• Self join

Use a join to query data from more than one table.

Ø  SELECT table1.column, table2.column

   FROM table1, table2

   WHERE table1.column1 = table2.column2;

a.Equijoin-> Equijoins are also called simple joins or inner joins.

To determine an employee’s department name, you compare the value in the DEPARTMENT_ID

column in the EMPLOYEES table with the DEPARTMENT_ID values in the DEPARTMENTS table.

The relationship between the EMPLOYEES and DEPARTMENTS tables is an equijoin—that is, values

in the DEPARTMENT_ID column on both tables must be equal. Frequently, this type of join involves

primary and foreign key complements.

Retrieving Records with Equijoins

Ø  SELECT employees.employee_id, employees.last_name,

employees.department_id, departments.department_id,

departments.location_id

FROM employees, departments

      WHERE employees.department_id = departments.department_id;

Using Table Aliases

Ø  SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

FROM employees e , departments d

      WHERE e.department_id = d.department_id;

b.Non-Equijoins

A non-equijoin is a join condition containing something other than an equality operator.

The relationship between the EMPLOYEES table and the JOB_GRADES table has an

example of a non-equijoin. A relationship between the two tables is that the SALARY

column in the EMPLOYEES table must be between the values in the LOWEST_SALARY

and HIGHEST_SALARY columns of the JOB_GRADES table. The relationship is

obtained using an operator other than equals (=).

Ø  SELECT e.last_name, e.salary, j.grade_level

FROM employees e, job_grades j

WHERE e.salary

      BETWEEN j.lowest_sal AND j.highest_sal;

C.Outer Joins

If a row does not satisfy a join condition, the row will not appear in the query result. For example, in

the equijoin condition of EMPLOYEES and DEPARTMENTS tables, employee Grant does not appear

because there is no department ID recorded for her in the EMPLOYEES table. Instead of seeing 20

employees in the result set, you see 19 records.

                     • You use an outer join to also see rows that do not meet the join condition.

                   • The Outer join operator is the plus sign (+).

Ø  SELECT e.last_name, e.department_id, d.department_name

FROM employees e, departments d

      WHERE e.department_id = d.department_id;

Ø  SELECT table1.column, table2.column

FROM table1, table2   

      WHERE table1.column(+) = table2.column;

Ø  SELECT table1.column, table2.column

FROM table1, table2

      WHERE table1.column = table2.column(+);

Ø  SELECT e.last_name, e.department_id, d.department_name

FROM employees e, departments d

      WHERE e.department_id(+) = d.department_id ;

LEFT OUTER JOIN

This query retrieves all rows in the EMPLOYEES table, which is the left table even if there is no

match in the DEPARTMENTS table.

This query was completed in earlier releases as follows:

Ø  SELECT e.last_name, e.department_id, d.department_name

FROM employees e, departments d

      WHERE d.department_id (+) = e.department_id;

RIGHT OUTER JOIN

This query retrieves all rows in the DEPARTMENTS table, which is the right table even if there is no

match in the EMPLOYEES table.

This query was completed in earlier releases as follows:

Ø  SELECT e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE d.department_id = e.department_id (+);

FULL OUTER JOIN

This query retrieves all rows in the EMPLOYEES table, even if there is no match in the

DEPARTMENTS table. It also retrieves all rows in the DEPARTMENTS table, even if there is no match

in the EMPLOYEES table.

Ø  SELECT e.last_name, e.department_id, d.department_name

FROM employees e

FULL OUTER JOIN departments d

      ON (e.department_id = d.department_id) ;

D.Self Joins

Sometimes you need to join a table to itself. To find the name of each employee’s manager, you need

to join the EMPLOYEES table to itself, or perform a self join. For example, to find the name of

Whalen’s manager, you need to:

               • Find Whalen in the EMPLOYEES table by looking at the LAST_NAME column.

                • Find the manager number for Whalen by looking at the MANAGER_ID column. Whalen’s

                 manager number is 101.

Ø  SELECT worker.last_name || ’ works for ’

|| manager.last_name

FROM employees worker, employees manager

WHERE worker.manager_id = manager.employee_id ;

ü  Group Functions

Unlike single-row functions, group functions operate on sets of rows to give one result per group.

These sets may be the whole table or the table split into groups.

Ø  SELECT [column,] group_function(column), ...

FROM table

[WHERE condition]

[GROUP BY column]

      ORDER BY column];

Ø  SELECT AVG(salary), MAX(salary),

MIN(salary), SUM(salary)

FROM employees

      HERE job_id LIKE ’%REP%’;

ü   Manipulating Data

• A DML statement is executed when you:

– Add new rows to a table

– Modify existing rows in a table

– Remove existing rows from a table

• A transaction consists of a collection of DML statements that form a logical unit of work.

INSERT

Only one row is inserted at a time with this syntax.

INSERT INTO table [(column [, column...])]

VALUES (value [, value...]);

Ø  INSERT INTO departments(department_id, department_name,

manager_id, location_id)

      ALUES (70, ’Public Relations’, 100, 1700);

The SYSDATE function records the current dateand time.

Ø  INSERT INTO employees (employee_id,first_name, last_name,

email, phone_number,hire_date, job_id, salary,commission_pct, manager_id,department_id)

VALUES (113,’Louis’, ’Popp’,’LPOPP’, ’515.124.4567’,

SYSDATE, ’AC_ACCOUNT’, 6900,ULL, 205, 100);

UPDATE

The UPDATE statement modifies specific rows if the WHERE clause is specified. The slide example

transfers employee 113 (Popp) to department 70.

If you omit the WHERE clause, all the rows in the table are modified.

Modify existing rows with the UPDATE statement.

Update more than one row at a time, if required.

UPDATE table SET column = value [, column = value, ...]

[WHERE condition];

• Specific row or rows are modified if you specify the WHERE clause.

Ø  UPDATE employees

SET department_id = 70

      HERE employee_id = 113;

• All rows in the table are modified if you omit the WHERE clause.

Ø  UPDATE copy_emp

SET department_id = 110;

DELETE

You can remove existing rows by using the DELETE statement.

DELETE [FROM] table [WHERE condition];

• Specific rows are deleted if you specify the WHERE clause.

Ø  DELETE FROM departments

WHERE department_name = ’Finance’;

• All rows in the table are deleted if you omit the WHERE clause.

Ø  DELETE FROM copy_emp;