Learn General Oracle/SQL Query Writing :
Quick Reference Oracle SQL Query writing. Go through this post and learn Oracle SQL writing within couple of 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
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 -- > * / + -
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;
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 query displays last names and job codes of all employees. The column has the
Heading Employee Details.
Eliminating Duplicate Rows
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
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
SELECT last_name, salary
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
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
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
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’, ’’,
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;
DELATE :
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