Learn General Oracle/SQL Query Writing :DK®
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.DK®
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;
DK®
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);
DK®
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;
No comments:
Post a Comment