Tuesday, February 28, 2012

Oracle SQL

index

SQL: Structured Query Language
DML: Data Manipulation Language
DDL:  Data Definition Language

/* multiline comment */
-- one line comment

Query:  an operation that retrieves data from one or more tables or views. A query nested within another SQL statement is called a subquery.

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema.

Tables are the basic unit of data storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name (such as employees) and set of columns. You give each column a column name (such as employee_id, last_name, and job_id), a datatype (such as VARCHAR2, DATE, or NUMBER), and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width.
A datatype is either scalar or nonscalar. A scalar type contains an atomic value, whereas a nonscalar (sometimes called a "collection") contains a set of values.
A large object (LOB) is a special form of scalar datatype representing a large scalar value of binary or character data.
--------------------
datatype
CHAR: fixed-length character string
NCHAR: Fixed-length Unicode-only
NVARCHAR2: Variable-length Unicode-only
Do not use the VARCHAR datatype.

NUMBER: number from 1.0 x 10^-130 to but not including 1.0 x 10^126.
NUMBER(p,s)
p: is the precision, or the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit.
s: is the scale,or the number of digits from the decimal point to the least significant digit.
---------------------
CREATE TABLE test (col1 NUMBER(5,2), col2 FLOAT(5));
INSERT INTO test VALUES (123.45, 123.45);
----------------
Floating-point numbers can have a decimal point anywhere from the first to the last digit or can have no decimal point at all.
----------------
TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype.
----------
DUAL: Dummy table

SELECT TO_DATE('2005', 'YYYY') FROM DUAL;
SELECT 1+1 FROM DUAL;
SELECT SYSDATE  FROM DUAL;
SELECT USER FROM DUAL;
-------------
literal: fixed data value
text literals: 'Hello'
-----------
INSERT INTO my_table VALUES (1, SYSDATE);
INSERT INTO my_table VALUES (2, TRUNC(SYSDATE));
SELECT * FROM my_table
WHERE datecol = TO_DATE('03-OCT-02','DD-MON-YY');
SELECT * FROM my_table
WHERE datecol > TO_DATE('02-OCT-02', 'DD-MON-YY');
-----------
SYSDATE:  returns the current date and time set for the operating system on which the database resides.
-----------
TRUNC(date,fmt): returns date with the time portion of the day truncated to the unit specified by the format model fmt.
SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')  "New Year"  FROM DUAL;
--------------------
Datetime Literals

DATE '1998-12-25'
TO_DATE('98-DEC-25 17:30','YY-MON-DD HH24:MI')
---------------
SELECT * FROM my_table WHERE datecol = DATE '2002-10-03';
SELECT * FROM my_table WHERE TRUNC(datecol) = DATE '2002-10-03';
INSERT INTO my_table VALUES
   (3, TO_DATE('3-OCT-2002','DD-MON-YYYY'));
INSERT INTO my_table VALUES (4, '03-OCT-02');
INSERT INTO my_table VALUES (5, TRUNC(SYSDATE));
SELECT TIMESTAMP '1999-10-29 01:30:00' AT TIME ZONE 'US/Pacific' FROM DUAL;
--------------
IS NULL
IS NOT NULL
SELECT last_name FROM employees
WHERE commission_pct IS NULL ORDER BY last_name;
---------------
sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns:
CURRVAL: Returns the current value of a sequence
NEXTVAL: Increments the sequence and returns the next value
You must qualify CURRVAL and NEXTVAL with the name of the sequence:
sequence.CURRVAL
sequence.NEXTVAL
To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:
schema.sequence.CURRVAL
schema.sequence.NEXTVAL
To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:
schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink
-----------
SELECT employees_seq.nextval FROM DUAL;
INSERT INTO employees
VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe', '555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null,30);
INSERT INTO orders (order_id, order_date, customer_id) VALUES (orders_seq.nextval, TO_DATE(SYSDATE), 106);
INSERT INTO order_items (order_id, line_item_id, product_id) VALUES (orders_seq.currval, 1, 2359);
------------
ROWNUM: a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
You can use ROWNUM to limit the number of rows returned by a query, as in this example:
SELECT * FROM employees WHERE ROWNUM < 11;
SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;
---------------
SELECT * FROM order_items WHERE quantity = -1
 ORDER BY order_id, line_item_id, product_id;
SELECT * FROM employees WHERE -salary < 0
  ORDER BY employee_id;
--------------
SELECT hire_date  FROM employees
WHERE SYSDATE - hire_date  > 365
ORDER BY hire_date;
-------------------
SELECT 'Name is ' || last_name
FROM employees ORDER BY last_name;
------------
NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

SELECT last_name,
NVL(TO_CHAR(commission_pct), 'Not Applicable')
"COMMISSION"  FROM employees
WHERE last_name LIKE 'B%' ORDER BY last_name;
-------------------
TO_CHAR function converts a DATETIME, number, or NTEXT expression to a TEXT expression in a specified format. This function is typically used to format output data.
SHOW TO_CHAR(SYSDATE, 'Month DD, YYYY HH24:MI:SS')
November  30, 2000 10:01:29

--calculates the average of the maximum salaries of all the departments
SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;
SELECT COUNT(*) "Total" FROM employees;
SELECT COUNT(*) "Allstars" FROM employees
   WHERE commission_pct > 0;
SELECT COUNT(commission_pct) "Count" FROM employees;
SELECT COUNT(DISTINCT manager_id) "Managers" FROM employees;
-------------
partition: smaller and more manageable pieces of very large tables and indexes
---------------
INSERT INTO employee (empno, ename, job)
VALUES (emp_sequence.NEXTVAL, 'SMITH', 'CLERK');
--------------
INSERT INTO personal_info
values('bart','simpson',12345,$45000)
SELECT * FROM personal_info
SELECT last_name FROM personal_info
SELECT * FROM personal_info WHERE salary > $50000
UPDATE personal_info SET salary = salary * 1.03
UPDATE personal_info SET salary = salary + $5000
WHERE employee_id = 12345
DELETE FROM personal_info WHERE employee_id = 12345
--------------
SELECT last_name, salary + NVL(commission_pct, 0),
   job_id, e.department_id
  FROM employees e, departments d
  WHERE e.department_id = d.department_id
    AND salary + NVL(commission_pct,0) >
   (SELECT salary + NVL(commission_pct,0)
      FROM employees
      WHERE last_name = 'Pataballa');



SELECT last_name,
salary + NVL(commission_pct, 0),
job_id, e.department_id
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND salary + NVL(commission_pct, 0) >
  (SELECT salary + NVL(commission_pct,0)
  FROM employees  WHERE last_name = 'Pataballa');
-------------
SELECT last_name  FROM employees WHERE last_name
LIKE '%A\_B%' ESCAPE '\'
ORDER BY last_name;

x [NOT] LIKE y [ESCAPE 'z']
%:  zero or more characters except null.
_:  single character

SELECT salary FROM employees
WHERE last_name LIKE 'R%' ORDER BY salary;

SELECT salary FROM employees
WHERE 'SM%' LIKE last_name ORDER BY salary;
----------------
ESCAPE Clause Example The following example searches for employees with the pattern A_B in their name:

SELECT last_name  FROM employees
WHERE last_name LIKE '%A\_B%' ESCAPE '\'
ORDER BY last_name;
The ESCAPE clause identifies the backslash (\) as the escape character. In the pattern, the escape character precedes the underscore (_). This causes Oracle to interpret the underscore literally, rather than as a special pattern matching character.
---------------------
SELECT * FROM employees
WHERE salary BETWEEN 2000 AND 3000
ORDER BY employee_id;
-------------
EXISTS: TRUE if a subquery returns at least one row.

SELECT department_id FROM departments d
WHERE EXISTS
  (SELECT * FROM employees e
    WHERE d.department_id
    = e.department_id)
   ORDER BY department_id;
---------------
SELECT * FROM employees
WHERE job_id IN ('PU_CLERK','SH_CLERK')
ORDER BY employee_id;

SELECT * FROM employees WHERE salary IN
  (SELECT salary FROM employees
   WHERE department_id =30)
ORDER BY employee_id;

SELECT * FROM employees WHERE salary NOT IN
  (SELECT salary  FROM employees
   WHERE department_id = 30)
ORDER BY employee_id;

SELECT * FROM employees WHERE job_id NOT IN
  ('PU_CLERK', 'SH_CLERK')
ORDER BY employee_id;
----------------
SELECT * FROM persons p
WHERE VALUE(p) IS OF TYPE (employee_t);

SELECT * FROM persons p
WHERE VALUE(p) IS OF (ONLY part_time_emp_t);
-------------------
integrity constraints: rules for each column.

NOT NULL: prohibits a database value from being null.
unique: prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
primary key: combines a NOT NULL constraint and a unique constraint.
foreign key: requires values in one table to match values in another table.
check constraint: requires a value in the database to comply with a specified condition.
--------------------------
you must match datatype (using the TO_CHAR function) when columns do not exist in one or the other table:

SELECT location_id, department_name "Department",
TO_CHAR(NULL) "Warehouse"  FROM departments

UNION

SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name FROM warehouses;
--------------------------
--UNION returns only distinct rows
SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories
ORDER BY product_id;

--UNION ALL does not eliminate duplicate rows
SELECT location_id  FROM locations
UNION ALL
SELECT location_id  FROM departments
ORDER BY location_id;
--------------------
SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items
ORDER BY product_id;
---------------
SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items
ORDER BY product_id;
-------------------
Join is a query that combines rows from two or more tables, views, or materialized views. It is done whenever multiple tables appear in the FROM clause of the query.
-----------------------
SELECT last_name, job_id, departments.department_id, department_name
FROM employees, departments
WHERE
 employees.department_id =
 departments.department_id
ORDER BY last_name, job_id;
-----------------
SELECT last_name, job_id, departments.department_id, department_name
FROM employees, departments
WHERE
employees.department_id =
     departments.department_id
  AND job_id = 'SA_MAN'
ORDER BY last_name;
-------------------
SELECT last_name, department_id FROM employees
WHERE department_id =
     (SELECT department_id FROM employees
      WHERE last_name = 'Lorentz')
ORDER BY last_name, department_id;
--------------------
UPDATE employees SET salary = salary * 1.1
WHERE employee_id IN
  (SELECT employee_id FROM job_history);
-------------------------
CREATE TABLE new_departments (department_id, department_name, location_id) AS
   SELECT department_id, department_name,
   location_id  FROM departments;
----------------------
SELECT
e1.last_name || ' works for ' || e2.last_name
   "Employees and Their Managers"
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id
      AND e1.last_name LIKE 'R%'
ORDER BY e1.last_name;
---------------------




The left outer join returns all departments, including those without any employees.

recommended syntax:

SELECT d.department_id, e.last_name
FROM departments d LEFT OUTER JOIN employees e
   ON d.department_id = e.department_id
ORDER BY d.department_id, e.last_name;

traditional syntax:

SELECT d.department_id, e.last_name
FROM departments d, employees e
WHERE d.department_id = e.department_id(+)
ORDER BY d.department_id, e.last_name;
--------------------
UPDATE employees SET commission_pct = NULL
WHERE job_id = 'SH_CLERK';

UPDATE employees SET  job_id = 'SA_MAN',
  salary = salary + 1000, department_id = 120
WHERE first_name||' '||last_name = 'Douglas Grant';

UPDATE employees@remote SET salary = salary * 1.1
WHERE last_name = 'Baer';
------------------------
UPDATE employees a SET

 department_id =
 (SELECT department_id FROM departments
 WHERE location_id = '2100'),

 (salary, commission_pct) =
 (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
 FROM employees b
 WHERE a.department_id = b.department_id)

WHERE department_id IN
  (SELECT department_id FROM departments
  WHERE location_id = 2900 OR location_id = 2700);
---------------------

No comments:

Post a Comment