Final Course Review
Objectives
This lesson covers the following objectives:
- Review the key points about case and character manipulation
- Review number, date, conversion, and general functions
- Review conditional expressions
- Review Cartesian product and join operations
- Review non-equijoins, outer joins, self joins, cross joins, natural joins, and join clauses
- Review group functions, group by syntax, and having clauses
- Review single-row and multiple row subqueries
- Review pair-wise and non-pair-wise subqueries
- Review correlated subqueries
- Review DML statements insert, update, delete, merge, and multi-table inserts
- Review DDL statements CREATE, ALTER, RENAME, TRUNCATE, FLASHBACK TABLE, DROP, and FLASHBACK QUERY
- Review DCL statements CREATE and REVOKE object privileges
Purpose
- Review is the best preparation for assessment.
- Assessment helps you realize how much you’ve learned and highlights areas in which you may wish to improve.
- Reviewing the topics learned to this point will help you be your best during the final exam.
Syntax Review
- This is a review of the syntax.
- Ensure that you also review the rules concerning the syntax.
- These are covered throughout the course.
Case and Character Manipulation
Case:
LOWER(column name|expression)
UPPER(column name|expression)
INITCAP(column name|expression)
Character:
CONCAT(column name|expression, column name|expression)
SUBSTR(column name|expression,n,m)
LENGTH(column name|expression)
LOWER(column name|expression)
UPPER(column name|expression)
INITCAP(column name|expression)
INSTR(column name|expression, string literal)
LPAD (column name|expression, n, character literal)
RPAD(column name|expression, n, character literal)
TRIM ( [leading | trailing | both] char1 FROM char2)
REPLACE (column name|expression, string to be replaced, replacement
string)
Number Functions
ROUND(column|expression,n)
TRUNC(column|expression,n)
MOD(column|expression, column|expression)
Date Functions
ROUND(column|expression,string)
TRUNC(column|expression,string)
MONTHS_BETWEEN(column|expression, column|expression)
ADD_MONTHS(column|expression,n)
NEXT_DAY(column|expression,'day')
LAST_DAY(column|expression)
Conversion Functions
TO_CHAR(number, 'format model')
TO_CHAR(date, 'format model')
TO_NUMBER(character string, 'format model')
TO_DATE(character string, 'format model')

NULL Functions
NVL(column|expression, value)
NVL2(column|expression, column|expression,
column|expression)
NULLIF(column|expression, column|expression)
COALESCE(column|expression, column|expression,
column|expression…. column|expression)
Conditional Expressions
Oracle-specific:
DECODE(columnl|expression, search1, result1
[, search2, result2,...,]
[, default])
ANSI:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
ANSI SQL Standard Syntax:
- Cross Join:
SELECT last_name, department_name
FROM employees CROSS JOIN departments;
- Natural Join:
SELECT employee_id, last_name, department_name
FROM employees NATURAL JOIN departments;
- Join .. On:
SELECT e.employee_id, e.last_name, e.salary, j.grade_level
FROM employees e JOIN job_grades j
ON (e.salary BETWEEN j.lowest_sal AND j.highest_sal);
- Joins .. Using:
SELECT employee_id, last_name, department_name
FROM employees JOIN departments
USING (department_id);
- Join .. On:
SELECT e.employee_id, e.last_name, d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
Outer Joins
- Right Outer Join
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
- Left Outer Join
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
- Full Outer Join (No comparable Oracle specific Join)
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
Group Functions, Group By Syntax and Having Clauses
AVG (column |expression)
COUNT (column |expression)
MIN (column |expression)
MAX (column |expression)
SUM (column |expression)
VARIANCE (column |expression)
STDDEV (column |expression)
SELECT column1, AVG (column |expression)
FROM table 1
GROUP BY (ROLLUP | CUBE) (column1 | GROUPING SETS)
HAVING AVG (column |expression)
Single-row and Multiple-row Subqueries
SELECT column1..
FROM table 1
WHERE column2 = (SELECT column2
FROM table 1
WHERE column 3 = expression)
- Single row operators: =,>,<,>=,<=,<>
- Multiple row operators: IN, ANY, ALL
Pairwise and Non-Pairwise Subqueries
- Pairwise
SELECT column1..
FROM table 1
WHERE (column2, column3) = (SELECT column2, column3
FROM table 1
WHERE column 4 = expression);
- Non-pairwise
SELECT column1..
FROM table 1
WHERE column2 = (SELECT column2
FROM table 1
WHERE column 4 = expression)
AND column3 = (SELECT column3
FROM table 2
WHERE column 4 = expression);
Correlated Subqueries
SELECT o.column1..
FROM table_1 o
WHERE o.column2 = (SELECT i.column2
FROM table_2 i
WHERE i.column1 = o.column1)
Inserting, Updating, and Deleting Data
- Explicit Insert
INSERT INTO table (column1, column2…)
VALUES (value1, value2…) ;
- Implicit Insert
INSERT INTO table
VALUES (value1, value2, value3, value4);
UPDATE table1
SET column1 = value1,
column2 = value2…
WHERE column1 = value;
DELETE FROM table1
WHERE column1 = value;
UPDATE table1
SET column1 = value1,
column2 = value2…
WHERE column1 = value;
DELETE FROM table1
WHERE column1 = value;
conditional_insert_clause
[ ALL | FIRST ]
WHEN condition THEN
insert_into_clause [ values_clause ]
WHEN condition THEN
insert_into_clause [ values_clause ]
ELSE insert_into_clause [ values_clause ]
Default Values
CREATE TABLE table1 (
column1 DATE DEFAULT SYSDATE,…)
INSERT INTO table1
(column1,….)
VALUES
(DEFAULT,…);
The Merge Statement
- Multi-table Insert
MERGE INTO destination-table USING source-table
ON matching-condition
WHEN MATCHED THEN UPDATE
SET ……
WHEN NOT MATCHED THEN INSERT
VALUES (……);
Creating Tables
CREATE TABLE table
(column data type [DEFAULT expression],
column data type [DEFAULT expression],
……[ ] );
CREATE TABLE tablename
[(column, column, …)]
AS subquery;
Specifying Data Types
NUMBER(p,s)
CHAR
VARCHAR2(n)
DATE
TIMESTAMP
TIMESTAMP WITH TIMEZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
CLOB
BLOB
RAW
Modifying a Table
ALTER TABLE tablename
ADD (column_name data type [DEFAULT expression]…);
ALTER TABLE tablename MODIFY (column_name VARCHAR2(30));
ALTER TABLE tablename DROP COLUMN column name;
ALTER TABLE tablename SET UNUSED (column name);
ALTER TABLE tablename DROP UNUSED COLUMNS;
- Multi-table Insert
DROP TABLE tablename;
FLASHBACK TABLE tablename TO BEFORE DROP;
SELECT * FROM user_recyclebin;
SELECT versions_starttime "START_DATE",
versions_endtime "END_DATE",
column, column......
FROM table
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE column = value;
Column Level Constraints
CREATE TABLE table
(col1 data type CONSTRAINT tab_col1_pk PRIMARY KEY,
col2 data type CONSTRAINT tab_col2_nn NOT NULL,
col3 data type CONSTRAINT tab_col3_uk UNIQUE,
col4 data type CONSTRAINT tab_col4_ck CHECK (col4 > value),
col5 data type CONSTRAINT tab_col5 REFERENCES table2 (col1));
Table Level Constraints
CREATE TABLE table
(col1 data type,
col2 data type,
col3 data type,
col4 data type,
col5 data type,
CONSTRAINT tab_col1_pk PRIMARY(col1),
CONSTRAINT tab_col3_uk UNIQUE(col2),
CONSTRAINT tab_col4_ck CHECK (col4 > value),
CONSTRAINT tab1_col5_fk FOREIGN KEY (col5) REFERENCES table2 (col1));
Creating and Managing Views
CREATE [OR REPLACE] [FORCE| NOFORCE] VIEW view [(alias [, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
DROP VIEW viewname;
Top-n Analysis
SELECT ROWNUM as RANK, col1, col2
FROM (SELECT col1, col2 FROM table1
ORDER BY col1)
WHERE ROWNUM <= n;
Inline Views
SELECT t1.col1, t2.col2…
FROM table 1 t1, (SELECT col1, col2..
FROM table2
WHERE …) t2
WHERE ……;
Creating Sequences
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
DROP SEQUENCE sequence_name;
Creating Indexes, and Synonyms
CREATE INDEX index_name
ON table_name( column...,column);
DROP INDEX index_name;
CREATE [PUBLIC] SYNONYM synonym
FOR object;
DROP [PUBLIC] SYNONYM name_of_synonym
Creating and Revoking Object Privileges
CREATE USER user
IDENTIFIED BY password;
GRANT privilege [, privilege...]
TO user [, user| role, PUBLIC...];
ALTER USER user
IDENTIFIED BY password;
CREATE ROLE role_name;
GRANT object_priv [(column_list)]
ON object_name
TO {user|role|PUBLIC}
[WITH GRANT OPTION];
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
Summary
In this lesson, you should have reviewed:
- Key points about case and character manipulation
- Number, date, conversion, and general functions
- Conditional expressions
- Cartesian product and join operations
- Non-equijoins, outer joins, self joins, cross joins, natural joins, and join clauses
- Group functions, group by syntax, and having clauses
- Single-row and multiple row subqueries
- Pair-wise and non-pair-wise subqueries
- Correlated subqueries
- DML statements, insert, update, delete, merge and multi-table inserts
- DDL statements, FLASHBACK TABLE, DROP and FLASHBACK QUERY
0 Comments