SQL Fundamentals

1. Oracle database: A set of files on disk, no limit on the size of the files or database, access to the database is through Oracle database instance
Image

2. Oracle WebLogic Server: a platform for developing, deploying and managing web applications, runs applications that connect users to the database. Three tiers: database tier, client tier (browser) and application tier.

Image

3. Oracle Enterprise Manager: a tool to manage Oracle database, application servers and if desired, the entire computing environment.

Data Normalization: Tables linked through primary and foreign keys

Data Modeling Entity Relationships:
1. 1: N (One to Many)
2. N:1 (Many to One)
3. 1:1 (One to One)
4. M:N (Many to Many)

Entity-Relationship Diagrams

There are 16 primary SQL commands.

Data Manipulation Language (DML) commands:
SELECT
INSERT
UPDATE
DELETE
MERGE

Data Definition Language (DDL) commands:
CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT

Data Control Language (DCL) commands:
GRANT
REVOKE

Transaction Control Language (TCL) commands:
COMMIT
ROLLBACK
SAVEPOINT

SHOW USER;
DESC[RIBE] <SCHEMA>.tablename;

SELECT statement:
SELECT *|{[DISTINCT] column|expresion [alias],…} FROM table;
SELECT * FROM table;
SELECT column_name FROM table;
SELECT DISTINCT column1 AS c1, column2 c2, … FROM table t1;

Examples:
SELECT * FROM regions;
SELECT reqion_name FROM regions;
SELECT ‘literal ‘ || ‘processing using the REGIONS table’ FROM regions;
SELECT ‘Plural”s can be specified using two single quotes’ FROM dual;
SELECT q'<Plural’s can also be specified using alternative quote operators>’ “q<>” FROM dual;
SELECT q’XEven upper case X may delimit possesive plural’sX’ “qX” FROM dual;
SELECT (4 * (22/7) * (3958.759 * 3958.759)) AS “Earth’s Area” FROM dual;

Image

NULL value refers to absence of data. NULL is not equal to 0 or space.

The WHERE clause:
SELECT *|{[DISTINCT] column|expression [alias],. ..} FROM table [WHERE conditions(x)];
Examples:
SELECT last_name, salary, department_id FROM employees WHERE salary = department_id;
SELECT last_name, Salary FROM employees WHERE salary = 10000; and SELECT last_name, salary FROM employees WHERE salary = ‘10000’; are same.
WHERE job_id=SA_REP, WHERE job_id=’Sa_Rep’ and WHERE job_id=’Sa_rep’ are different.
SELECT employee_id FROM job_history WHERE start_date = end_date;
SELECT employee_id FROM job_history WHERE Start_date = ’01-JAN-2001′;
SELECT employee_id FROM job_history WHERE Start_date = ’01-JAN-01′;
SELECT employee_id FROM job_history WHERE Start_date = ’01-JAN-99′;

Image

SELECT last_name FROM employees WHERE last_name < ‘King’;
SELECT last_name, hire_date FROM employees WHERE hire_date < ’01-JAN-2003′;
SELECT last_name, salary FROM employees WHERE salary BETWEEN 3400 AND 4000;
SELECT first_name, hire_date FROM employees WHERE hire date BETWEEN ’24-JUL-1994′ AND ’07-JUN-1996′;
SELECT last_name, salary FROM employees WHERE salary IN (3000,4000,6000);
SELECT last_name, salary FROM employees WHERE salary = 3000 OR salary = 4000 OR salary = 6000;
SELECT first_name FROM employees WHERE first name LIKE ‘A%’ OR last_name LIKE ‘K_ng’;
SELECT * FROM jobs WHERE job_id LIKE ‘SA\_%’ ESCAPE ‘\’;
SELECT job_id FROM jobs WHERE job_id LIKE ‘SA$_%’ ESCAPE ‘$’;
SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NULL;

AND Operator:
Image

OR Operator:
Image

NOT Operator:
Image

Image

The ORDER BY clause:
SELECT *l{[DISTINCT] column/expression [alias],. ..} FROM table [WHERE condition(s)] [ORDER BY {col(s)/expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];
Examples:
SELECT last_name, salary, hire_dace, hire_date-(salary/10] emp_value FROM employees WHERE job_id IN (‘SA_REP’,’MK_MAN’) ORDER BY emp_value;
SELECT last_name, hire_date, salary FROM employees WHERE job_id IN (‘SA_REP’,’MK_MAN’) ORDER BY 2;
SELECT job_id, last_name, salary, hire_date FROM employees WHERE jOb_id IN (‘SA_REP’,’MK_MAN’) ORDER BY job_id DESC, last_name, 3 DESC;

Substitution Variables (& or &&): Left to Right
SELECT employee_id, last_name, phone_number, email FROM employees WHERE last_name = ‘&LASTNAME’ OR employee_id = &EMPNO;
SELECT first_name, job_id, &&col FROM employees WHERE job_id IN (‘MK_MAN’, ‘SA_MAN’) ORDER BY &col;
SELECT &rest_of_statement;
SELECT &SELECT_CLAUSE FROM &FROM_CLAUSE WHERE &WHERE_CLAUSE ORDER BY &ORDER_BY_CLAUSE;

DEFINE and UNDEFINE commands:
UNDEFINE variable;
DEFINE;
DEFINE variable=value;
SET DEFINE ON;
SET DEFINE OFF;
SET VERIFY ON;
SET VERIFY OFF;

Functions available in SQL:
lower(‘SQL’) = sql
upper(‘sql’) = SQL
initcap(‘sql’) = Sql

LENGTH(string)
length(‘A short string’) = 14

CONCAT(string 1, string 2)
concat(‘SQL is’,’ easy to learn.’) = SQL is easy to learn.

SUBSTR(string, start position, number of characters)
substr(‘http://www.domain.com&#8217;,12,6) = domain
SELECT substr(‘1#3#5#7#9#’ , -3 , 2) FROM dual; = #9 (start position is three characters from the end)

INSTR(source string, search item, [start position],[nth occurrence of search item])
instr(‘http://www.domain.com&#8217;,’.’,1,2) = 18

LPAD(string, length after padding, padding string)
lpad( ‘ #PASSWORD#’ , 11 , ‘#’) = ##PASSWORD#

RPAD(string, length after padding, padding string)
rpad( ‘ #PASSWORD#’ , 11 , ‘#’) = #PASSWORD##

TRIM([trailing|leading|both] trimstring from s)
trim( ‘ #’ from ‘#PASSWORD#’) = PASSWORD
SELECT trim(TRAILING ‘e’ FROM 1+2.14||’ is pie’) FROM dual; = 3.14 is pi
SELECT trim(BOTH ‘*’ FROM ‘*******Hidden******* ‘) FROM dual; = Hidden
SELECT trim(1 from sysdate) FROM dual; = 7-DEC-07

REPLACE(string, search item, replacement item)
replace(‘#PASSWORD#’,’WORD’,’PORT’) = #PASSPORT#
SELECT replace(‘1#3#5#7#9#‘,’#’) FROM dual; = 13579

ROUND(number, decimal precision)
round(42.39,1) = 42.4
SELECT round(1601.916718,1) FROM dual; = 1601.9
SELECT round(1601.916718,2) FROM dual; = 1601.92
SELECT round(1601.916718,-3) FROM dual; = 2000
SELECT round(1601.916718) FROM dual; = 1602

TRUNC(number, decimal precision)
trunc(42.39,1) = 42.3
SELECT trunc(1601.916718,1) FROM dual; = 1601.9
SELECT trunc(1601.916718,2) FROM dual; = 1601.91
SELECT trunc(1601.916718,-3) FROM dual; = 1000
SELECT trunc(1601.916718) FROM dual; = 1601

MOD(dividend, divisor)
mod(42, 10) = 2
SELECT mod(5.2,3) FROM dual; = 2.2

MONTHS_BETWEEN(date1, date2)
months_between(’01-FEB-2008′,’01-JAN-2008′) = 1

ADD_MONTHS(date, number of months)
add_months(’01-JAN-2008′,1) = 01-FEB-2008

LAST_DAY(date)
last_day(’01-FEB-2008′) = 29-FEB-2008

NEXT_DAY(date, day of the week)
next_day(’01-FEB-2008′,’Friday’) = 08-FEB-2008

SYSDATE = Current server date and time
sysdate = 17-DEC-2007

ROUND(date, date precision format)
round(sysdate,’month’) = 01-JAN-2008

TRUNC(date, date precision format)
trunc(sysdate,’month’) = 01-DEC-2007

SELECT lower(SYSDATE+2) FROM dual; = 19-dec-2007
SELECT upper(SYSDATE) FROM dual; = 3.14
SELECT initcap(21/7) FROM dual; = quotient 3 as a string
SELECT initcap(SYSDATE) FROM dual; = 17-Dec-2007
SELECT initcap(‘init cap or init_cap or init%cap’) FROM dual; = Init Cap Or Init_Cap Or Init%Cap

Image

Conversion Functions:
TO_NUMBER(char1, [format_mask], [nls_parameters]) = num1
TO_DATE(char1, [format_mask], [nls_parameters]) = date1
TO_CHAR(num1, [format_mask], [nls_parameters]) = char1
TO_CHAR(date1, [format_mask], [nls_parameters]) = char1

Numeric Format Masks:
Image

Date Format Masks:
Image
Image
Image
Image

to_char(sysdate, ‘fmMonth’) = January
to_char(sysdate, ‘fmmonth’) = january
to_char(sysdate, ‘fmMONTH’) = JANUARY
to_char(sysdate, ‘Month’) = January
to_char(sysdate, ‘month’) = january
to_char(sysdate, ‘MONTH’) = JANUARY

to_char(sysdate, ‘DDspth’) = SEVENTEENTH
to_char(sysdate, ‘Ddspth’) = Seventeenth
to_char(sysdate, ‘ddspth’) = seventeenth
to_char(sysdate, ‘fmDDspth’) = SEVENTEENTH
to_char(sysdate, ‘fmDdspth’) = Seventeenth
to_char(sysdate, ‘fmddspth’) = seventeenth
to_char(sysdate, ‘fmDDsp’) = SEVENTEEN
to_char(sysdate, ‘fmDdsp’) = Seventeen
to_char(sysdate, ‘fmddsp’) = seventeen
to_char(sysdate, ‘DDth’) = 17TH
to_char(sysdate, ‘DdTH’) = 17th
to_char(sysdate, ‘ddTh’) = 17th

select to_char(sysdate, ‘yyyysp’) from dual; = two thousand fifteen
select to_char(sysdate, ‘Yyyysp’) from dual; = Two Thousand Fifteen
select to_char(sysdate, ‘YYYYsp’) from dual; = TWO THOUSAND FIFTEEN
select to_char(sysdate, ‘Year’) from dual; = Twenty Fifteen
select to_char(sysdate, ‘year’) from dual; = twenty fifteen
select to_char(sysdate, ‘YEAR’) from dual; = TWENTY FIFTEEN

SELECT TO_CHAR(sysdate, ‘fmDdspth “of” Month, Year’) FROM DUAL; = Nineteenth of December, Twenty Fifteen
NVL(original, ifnull)
SELECT nvl.(1234) FROM dual; = Error
SELECT nvl(null,1234) FROM dual; = 1234
SELECT nvl(substr(‘abc’ ,4) , ‘No substring exists’) FROM dual; = No substring exists

NVL2(original, ifnotnull, ifnull)
SELECT nvl2(1234, 1, ‘a string’) FROM dual; = Error (data type incompatibility between ifnotnull and ifnull)
SELECT nvl2(null, 1234, 5678) FROM dual; = 5678
SELECT nvl2(substr(‘abc’, 2), ‘Not bc’, ‘No substring’) FROM dual; = Not bc

NULLIF(ifunequal, comparison_term)
SELECT nullif(1234, 1234) FROM dual; = null
SELECT nullif(1234, 1233+1) FROM dual; = null
SELECT nullif(’24-JUL-2009′, ’24-JUL-09′) FROM dual; = 24-JUL-2009

COALESCE(expr1, expr2, …, exprn) expr1 is retunred if it is not null, else expr2 if it is not null, and so on.
SELECT coalesce(null, null, null, ‘a string’) FROM dual; = a string
SELECT coalesce(null, null, null) FROM dual; = null
SELECT coalesce(substr(‘abc’, 4), ‘Not bc’, ‘No substring’) FROM dual; = Not bc

DECODE(expr1, comp1, iftru1, [comp2, iftrue22…compN, iftrueN], [iffalse])
Image

SELECT decode(1234, 123, ‘123 is a match’) FROM dual; = null (Since there is no iffalse parameter defined)
SELECT decode(1234, 123, ‘123 is a match’, ‘No match’) FROM dual; = No match
SELECT decode(‘search’, ‘comp1’, ‘true1’, ‘comp2’, ‘true2’, ‘search’, ‘true3’, substr(‘2search’, 2, 6), ‘true4’, ‘false’) FROM dual; = true3

CASE search_expr
WHEN comparison_expr1 THEN iftrue1
WHEN comparison_expr2 THEN iftrue2

WHEN comparison_exprN THEN iftrueN
ELSE iffalse
END
SELECT
CASE substr(1234, 1, 3)
WHEN ‘134’ THEN ‘1234 is a match’
WHEN ‘1235’ THEN ‘1235 is a match’
WHEN concat(‘1′, ’23’) THEN concat(‘1′, ’23’) || ‘ is a match’
ELSE ‘no match’
END
FROM dual;
= 123 is a match

Group Functions:
COUNT(*)
COUNT(DISTINCT expr)
COUNT(ALL expr)
COUNT(expr)

AVG(DISTINCT expr)
AVG(ALL expr)
AVG(expr)

SUM(DISTINCT expr)
SUM(ALL expr)
SUM(expr)

MAX(DISTINCT expr); MIN(DISTINCT expr)
MAX(ALL expr); MIN(ALL expr)
MAX(expr); MIN(expr)

VARIANCE(DISTINCT expr)
VARIANCE(ALL expr)
VARIANCE(expr)

STDDEV(DISTINCT expr)
STDDEV(ALL expr)
STDDEV(expr)

SELECT COUNT(*) FROM employees; = counts the rows in employees table
SELECT COUNT(commission_pct) FROM employees; = counts rows with not null commission_pct
SELECT COUNT(DISTINCT commission_pct) FROM employees; = considers not null value; returns number of unique values
SELECT COUNT(hire_date), COUNT(manager_id) FROM employees; = returns number of not null hire_date and number of not null manager_id

SELECT SUM(2) from employees; = 2 * number of rows
SELECT SUM(salary) from employees; = sum of not null salary
SELECT SUM(DISTINCT salary) from employees; = sum of distinct salary
SELECT SUM(commission_pct) from employees; = sum of not null commission_pct

SELECT AVG(2) from employees; = 2*rows/rows = 2
SELECT AVG(salary) from employees; = sum(salary)/rows
SELECT AVG(DISTINCT salary) from employees; = sum(unique salary values)/(rows with unique salary values)
SELECT AVG(commission_pct) from employees; = sum(commission_pct)/rows

SELECT MIN(commission_pct), MAX(commission_pct) FROM employees; = null values are ignored
SELECT MIN(start_date), MAX(end_date) FROM employees;
SELECT MIN(job_id), MAX(job_id) FROM employees;

The GROUP BY clause:
SELECT max(salary) , count(*) FROM employees GROUP BY department_id ORDER BY department id;
SELECT department_id, sum(commission_pct) FROM employees WHERE commission_pct IS NOT NULL GROUP BY department_id;
SELECT department_id, job_id, sum(commission_pct) FROM employees WHERE commission_pct IS NOT NULL GROUP BY department_id, job_id;
SELECT department_id, count(*) FROM job_history WHERE department_id IN (50, 60, 80, 110) GROUP BY department_id HAVING count(*) > 1 AND department_id > 50;
SELECT department_id, round(avg(salary)) FROM employees HAVING round(avg(salary)) > round(min(salary)) GROUP BY department_id ORDER BY department_id;

JOINS:
SELECT regions.region_name, countries.country_name FROM regions, countries WHERE regions.region id=countries.region id;
SELECT last_name, department_name FROM employees, departments WHERE employees.department_id (+) = departments.department_id; = Right Outer Join
SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id (+); = Left Outer Join
SELECT * FROM regions, countries; = Cartesian or Cross Join

The NATURAL JOIN clause:
SELECT * FROM locations NATURAL JOIN countries; = joins on common column names
SELECT * FROM locations, countries WHERE locations.country_id = countries.country_id;
SELECT * FROM jobs NATURAL JOIN countries; = Cartesian or Cross Join if no common column names
SELECT * FROM jobs, countries; = Cartesian or Cross Join

The JOIN USING clause:
SELECT * FROM locations JOIN countries USING (country_id);
SELECT * FROM locations, countries WHERE locations.country_id = countries.country_id;

The JOIN ON clause:
SELECT * FROM departments d JOIN employees e ON (e.employee_id=d. department_id);
SELECT * FROM employees e, departments d WHERE e . employee_id=d.department_id;

SELECT r.region_name, c.country_name, l.city, d.department_name FROM departments d NATURAL JOIN locations l, countries c, regions r; = 27 * 25 *4 = 2700 rows
SELECT region_id, country_id, c.country_name, l.city, d.department_name
FROM departments d
NATURAL JOIN locations l
NATURAL JOIN countries c
NATURAL JOIN regions r;
= 27 rows
SELECT r.region_name, c.country_name, l.city, d.department_name
FROM departments d
JOIN locations l ON (l.location_id=d.location_id)
JOIN countries c ON (c.country_id=l.country_id)
JOIN regions r ON (r.region_id=c.region_id);
= 27 rows
SELECT r.region_name, c.country_name, l.city, d.department_name
FROM departments d
JOIN locations l USING (location_id)
JOIN countries c USING (country_id)
JOIN regions r USING (region_id);
= 27 rows

SELECT d. department_name FROM departments d JOIN locations l ON (I.LOCATION_ID=d.LOCATION_ID) WHERE d._department_name LIKE ‘P%’ ; = 3 rows
SELECT d_department_name FROM departments d JOIN locations l ON (l.LOCATION_ID=d.LOCATION_ID AND d.department_name like ‘P%’); = 3 rows

SELECT e. employee_id, e.department_id EMP_DEPT_ID, d. department_id DEPT_DEPT_ID, d. department_name FROM departments d
LEFT OUTER JOIN employee e
ON (d.DEPARTMENT_ID=e.DEPARTMENT_ID) WHERE d .department_name like ‘P%’ ; = 7 + 1 rows
SELECT e. employee_id, e.department_id EMP_DEPT_ID, d. department_id DEPT_DEPT_ID, d. department_name FROM departments d
JOIN employee e
ON (d.DEPARTMENT_ID=e.DEPARTMENT_ID) WHERE d .department_name like ‘P%’ ; = 7 rows

SELECT e.Iast_name , d.department_name FROM departments d RIGHT OUTER JOIN employees e ON (e.department._id=d.department_id) WHERE e.Iast_name LIKE ‘G%’;
SELECT e.Iast_name , d.department_name FROM departments d
FULL OUTER JOIN employees e
ON (e.department._id=d.department_id) WHERE e.Iast_name LIKE ‘G%’; = Inner + Left Outer + Right Outer

SELECT * FROM jobs CROSS JOIN job_history; = takes 19 rows, 4 columns from jobs; 10 rows, 5 columns from job_history; generates 190 records with 9 columns
SELECT * FROM jobs j CROSS JOIN job_history jh WHERE j.job_id=’AD_PRES’; = 190 rows constrained by WHERE resulting 10 rows

Subqueries:
SELECT sysdate Today, (SELECT count (*) FROM departments) Dept_count, (SELECT count (*) FROM employees) Emp_count FROM dual;
SELECT last_name FROM employees WHERE employee_id IN (SELECT manager_id FROM employees);
SELECT max(salary), country_id FROM (SELECT salary, country id FROM employees NATURAL JOIN departments NATURAL JOIN locations) GROUP BY country id;
SELECT last_name FROM employees WHERE salary < (SELECT avg (salary) FROM employees);
SELECT count(quantity_sold) FROM sales WHERE prod_id IN (SELECT prod_id FROM products WHERE prod_name=’Comic Book Heroes’)
AND cust_id IN (SELECT cust_id FROM customers WHERE cust_city= ‘Oxford’) AND channel_id IN (SELECT channel_id FROM channels WHERE channel_desc=’Internet’) ;
SELECT avg (salary) , country_id FROM (SELECT salary, country_id FROM employees NATURAL JOIN departments NATURAL JOIN locations) GROUP BY country_id;
SELECT (SELECT max(salary) FROM employees) * (SELECT max(commission_pct) FROM employees)/100 FROM dual;
INSERT INTO sales_hist SELECT * FROM sales WHERE date > sysdate-1;
UPDATE employees SET salary = (SELECT avg(salary) FROM employees) ;
DELETE FROM departments WHERE department_id NOT IN (SELECT department_id FROM employees WHERE department_id is not null);
SELECT last_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id IN (SELECT location_id FROM locations WHERE country_id = (SELECT Country_id FROM countries WHERE country_name=’United Kingdom’));

Image

SELECT last_name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE last_name=’Taylor’) ORDER BY last_name;
SELECT job_title FROM jobs NATURAL JOIN employees GROUP BY job_title HAVING avg(salary) = (SELECT max(avg(salary)) FROM employees GROUP BY job_id);
SELECT last_name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id=80);

Image

Set Operators:
UNION: Returns the combined rows from two queries, sorting them and removing duplicates.
UNION ALL: Returns the combined rows from two queries without sorting or removing duplicates.
INTERSECT: Returns only the rows that occur in both queries’ result set, sorting them and removing duplicates.
MINUS: Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.
ORDER BY can only be used at the end of the compound query

SELECT region_name FROM regions UNION SELECT region_name FROM regions; = SELECT region_name FROM regions;
SELECT region_name FROM regions UNION ALL SELECT region_name FROM regions ; = double the number of rows
SELECT region_name FROM regions INTERSECT SELECT region_name FROM regions; = all rows from regions
SELECT region_name FROM regions MINUS SELECT region_name FROM regions; = no rows returned

SELECT name, FROM cats UNION ALL SELECT name, NULL, wingspan FROM birds;
SELECT name FROM permstaff WHERE location = ‘Germany’ UNION ALL SELECT name FROM consultants WHERE work_area = ‘Western Europe’ MINUS SELECT name FROM blacklist; = UNION ALL is executed before MINUS
SELECT name FROM consultants WHERE work_area = ‘Western Europe’ MINUS SELECT name FROM blacklist UNION ALL SELECT name FROM permstaff WHERE location = ‘Germany’ ; = MINUS is executed before UNION ALL
SELECT name FROM permstaff WHERE location = ‘Germany’ UNION ALL (SELECT name FROM consultants WHERE work_area = ‘Western Europe’ MINUS SELECT name FROM blacklist) ; MINUS is executed before UNION ALL
SELECT depno, trim(dname) name FROM old_dept UNION SELECT dept_id, dname FROM new dept ORDER BY name;

INSERT INTO hr.regions VALUES (10 , ‘Great Britain’);
INSERT INTO hr.regions(region_name, region_id) VALUES (‘Australia’ , 11);
INSERT INTO hr.regions (region_id) VALUES (12) ;
INSERT INTO hr.regions VALUES (13 , null);

INSERT INTO emp_copy (employee_id, last_name, hire_date, email, job_id) VALUES (1000, ‘Watson’, ’03-Nov-13′, ‘jwatson@hr.com’, ‘SA_REP’);
INSERT INTO emp_copy (employee_id, last_name, hire_date, email, job_id) VALUES (1000, upper(‘Watson’), to_date(‘ 03 -Nov-13’, ‘dd-mon-yy’), lower(‘JWatson@hr.com’), upper(‘sa_rep’));

INSERT INTO regions_copy SELECT * FROM hr.regions;
INSERT INTO department salaries (department, staff, salaries) SELECT coalesce (department name, ‘Unassigned’), count (employee_id), sum(coalesce(salary, 0)) FROM hr.employees e
FULL OUTER JOIN hr.departments d ON e.department id = d.department id GROUP BY department_name ORDER BY department_name;

INSERT ALL
WHEN 1=1 THEN
INTO emp_no_name (department_id, job_id, salary, commission_pct, hire_date) VALUES (department_id, job_id, salary, cuntmission_pct, hire_date)
WHEN department_id <> 80 THEN
INTO emp_non_sales (employee_id, department_id, salary, hire_date) VALUES (employee_id, department_id, salary, hire_date)
WHEN department_id = 80 THEN
INTO emp_sales [employee_id, salary, commission_pct  hire_date) VALUES (employee_id, salary, commission_pct, hire_date)
SELECT employee_id, department_id, job_id, salary, commission_pct, hire_date FROM hr.employees WHERE hire_date > sysdate – 30:

UPDATE employees SET salary = (SELECT salary FROM employees WHERE employee_id=206) ; = must return a scalar value
UPDATE employees SET salary = (SELECT salary FROM employees WHERE last.name = ‘Abel’); = must return a scalar value

UPDATE employees SET salary=10000 WHERE department_id IN (SELECT department_id FROM departments WHERE department_name LIKE ‘%IT%’); = all employees in department with ‘IT’

DELETE FROM employees WHERE employee_id = 206;
DELETE FROM employees WHERE last_name LIKE ‘S%’;
DELETE FROM employees WHERE department_id= &which_department;
DELETE FROM employees WHERE department_id IS NULL;

DELETE FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id IN (SELECT location_id FROM locations WHERE country_id IN (SELECT country_id FROM countries WHERE region_id IN (SELECT region_id FROM regions WHERE region_name = ‘Europe’))));

MERGE INTO employees e USING new_employees n ON (e.employee_id = n.employee_id)
WHEN MATCHED THEN UPDATE SET e.salary=n.salary
WHEN NOT MATCHED THEN INSERT (employee_id, last_name, salary, email, job_id) VALUES (n.employee_id, n.last_name, n.salary, n.email, n.job_id);

TRUNCATE TABLE table;

ACID
Atomicity: all parts of transaction must complete or none
Consistency: the result of the query must be consistent with the state of the database at the time the query was started
Isolation: an incomplete (uncommitted) transaction must be invisible to the rest of the world
Durable: once a transaction is complete, it must be impossible for the database to lose it

COMMIT;
ROLLBACK;
ROLLBACK [TO SAVEPOINT savepoint];
SAVEPOINT savepoint;

SET AUTOCOMMIT ON;
SET AUTOCOMMIT OFF;

SELECT object_type, count(object_type) FROM dba_objects GROUP BY object_type ORDER BY object_type;

CREATE TABLE lower (cl date); = Table created LOWER
CREATE TABLE “lower” (coll varchar2(2)); = Table created lower
SELECT table_name FROM user_tables WHERE lower(table_name) = ‘lower’; = lower and LOWER

SELECT object_type , count (*) FROM user_objects GROUP BY object_type; = objects owned by the user/schema
SELECT object_type, count (*) FROM all_objects GROUP BY object_type; = objects to which user/schema has some sort of access
SELECT DISTINCT owner FROM all_objects; = owners of object user/schema has access to

SELECT table_name, cluster_name, iot_type FROM user_tables;
SELECT column_name, data_type, nullable FROM user_tab_columns WHERE table_name= ‘REGIONS’ ;

Data Types for Columns:
VARCHAR2: VARCHAR2(N) = N is the length of the character string
NVARCHAR2
CHAR
RAW
NUMBER: NUMBER(precision, scale) = precision is maximum number of significant decimal digits, scale is the number of digits from the decimal point to the least significant digit, a negative scale is the number of digits to the left of the decimal point
FLOAT
INTEGER
DATE: includes century, year, month, day, hour, minute, and second
TIMESTAMP
TIMESTAMP WITH TIMEZONE
TIMESTAMP WITH LOCAL TIMEZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
CLOB
NCLOB
BLOB
BFILE
LONG
LONG RAW
ROWID

SELECT column_name, data_type, nullable, data_length, data_precision, data_scale FROM user_tab_columns WHERE table_name = ‘EMPLOYEES’ ;

CREATE TABLE [schema.]table [ORGANIZATION HEAP] [column datatype [DEFAULT expression] [,column datatype [DEFAULT expression] . . .);
CREATE TABLE SCOTT.EMP (EMPNO NUMBER(4), ENAME VARCHAR2(10), HIREDATE DATE DEFAULT TRUNC(SYSDATE), SAL NUMBER(7,2), COMM NUMBER(7,2) DEFAULT 0.03);
INSERT INTO scott.emp (empno, ename, sal) VALUES (1000, ‘John’, 1000.789); = 1000, John, 19-NOV-13, 1000.79, .03

CREATE TABLE employees_copy AS SELECT * FROM employees;
CREATE TABLE emp_dept AS SELECT last_name ename, department_name dname, round(sysdate – hire_date) service FROM employees NATURAL JOIN departments ORDER BY dname, ename;
CREATE TABLE no_emps AS SELECT * FROM employees WHERE 1=2; = 1 is never equal to 2 so the table structure will be created but no rows will be inserted

ALTER TABLE emp ADD (job_id number);
ALTER TABLE emp MODIFY (comm number(4,2) DEFAULT 0.05);
ALTER TABLE emp DROP COLUMN Comm;
ALTER TABLE emp SET UNUSED COLUMN job_id;
ALTER TABLE emp RENAME COLUMN hiredate TO recruited;
ALTER TABLE emp READ ONLY;
ALTER TABLE tablename DROP UNUSED COLUMNS;

DROP TABLE [schema.]tablename;

Types of Constraints:
UNIQUE – a column (or combination of columns to form a composite key) must be different for every row, it is possible to enter a NULL value
NOT NULL – column can’t be NULL for any row, recommended to include a DEFAULT clause
PRIMARY KEY – UNIQUE + NOT NULL
FOREIGN KEY – must reference either private or unique key in the parent table
CHECK – enforce rules such as value entered must be within a range, absolute values, rule in the form of an expression

CREATE TABLE dept (deptno NUMBER(2,0) CONSTRAINT dept_deptno_pk PRIMARY KEY CONSTRAINT dept_deptno_check CHECK (deptno BETWEEN 10 AND 90), dname VARCHAR2(20) CONSTRAINT dept_dname_nn NOT NULL);

CREATE TABLE emp (empno NUMBER(4,0) CONSTRAINT emp_empno_pk PRIMARY KEY, ename VARCHAR2(20) CONSTRAINT emp_ename_nn NOT NULL, mgr NUMBER[4,0) CONSTRAINT emp_mgr_fk REFERENCES emp (empno), dob DATE, hiredate DATE, deptno NUMBER(2,0) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) ON DELETE SET NULL, email VARCHAR2(30) CONSTRAINT emp_mail_uk UNIQUE, CONSTRAINT emp_hiredate_ck CHECK(hiredate >= dob + 365*16), CONSTRAINT emp_email_ck CHECK ((instr(email, ‘@’) > 0) AND (instr(email,’ . ‘) = 0)));