CURSO SQL
http://www.oracle.com/technetwork/developer-tools/sqldeveloper/downloads/sqldev-install-windows-1969674.html
1. Crear tablas
--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;
CREATE TABLE departments (
department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
department_name VARCHAR2(14),
location
VARCHAR2(13)
);
INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;
CREATE TABLE employees (
employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
employee_name VARCHAR2(10),
job
VARCHAR2(9),
manager_id
hiredate
salary
NUMBER(4),
DATE,
NUMBER(7,2),
commission
NUMBER(7,2),
department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES
departments
(department_id)
);
INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-
1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-
1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-
1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-
1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-
1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-
1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-
1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-
87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-
1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-
1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87',
'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-121981','dd-
mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-
1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-
1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
2. SELECT
SELECT *
FROM employees
ORDER BY employee_id;
SELECT e.*, d.*
FROM employees e
JOIN departments d ON d.department_id = e.department_id
ORDER BY e.employee_id;
COLUMNAS
SELECT employee_id, employee_name
FROM employees
ORDER BY employee_id;
ALIAS
SELECT employee_id AS employee_no, employee_name AS "Name"
FROM employees
ORDER BY employee_id;
SELECT e.employee_id,
e.employee_name,
d.department_id,
d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY e.employee_id;
FUNCIONES
SELECT UPPER('lowercase text') AS text
FROM dual;
SELECT 1+2 AS addition
FROM dual;
3. SUBQUERIES
SELECT d.department_id, d.department_name,
(SELECT COUNT(*) FROM employees e WHERE e.department_id =
d.department_id)
AS emp_count
FROM departments d
ORDER BY d.department_id;
SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS
emp_count
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id;
SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON d.department_id = e.department_id
ORDER BY e.employee_name;
INLINE VIEWS
SELECT ed.employee_name, ed.department_name
FROM (SELECT e.employee_name, d.department_name
FROM employees e, departments d
WHERE d.department_id = e.department_id) ed
ORDER BY ed.employee_name;
WITH CLAUSE
WITH emp_dept_join AS (
SELECT e.employee_name, d.department_name
FROM employees e, departments d
WHERE d.department_id = e.department_id
)
SELECT ed.employee_name, ed.department_name
FROM emp_dept_join ed
ORDER BY ed.employee_name;
CREATE OR REPLACE VIEW emp_dept_join_v AS
SELECT e.employee_name, d.department_name
FROM employees e, departments d
WHERE d.department_id = e.department_id;
4. The WHERE Clause
SELECT e.employee_name, e.salary, d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id -- Join Condition
AND
d.department_id = 20
AND
e.salary >= 2000
-- Filter
-- Filter
ORDER BY e.employee_name;
or
SELECT e.employee_name, e.salary, d.department_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id = 20
AND
e.salary >= 2000
-- Filter
-- Filter
ORDER BY e.employee_name;
Equality and Inequality Conditions
Filter conditions can use basic equality and inequality tests.
= : Equal.
<> or != : Not equal.
> : Greater than.
< : Less than.
>= : Great than or equal.
<= : Less than or equal.
SELECT e.employee_name, e.department_id, e.salary
FROM employees e
WHERE e.department_id = 20
AND
e.salary >= 2000
ORDER BY e.employee_name;
IN and NOT IN Conditions
SELECT e.department_id, e.employee_id, e.employee_name
FROM employees e
WHERE e.department_id IN (10, 20)
ORDER BY e.department_id, e.employee_id;
SELECT e.department_id, e.employee_id, e.employee_name
FROM employees e
WHERE e.department_id NOT IN (10, 20)
ORDER BY e.department_id, e.employee_id;
SELECT e.department_id, e.employee_id, e.employee_name
FROM employees e
WHERE e.department_id IN (SELECT d.department_id
FROM departments d
WHERE d.department_id < 30)
EXISTS and NOT EXISTS Conditions
SELECT d.department_id, d.department_name
FROM departments d
WHERE EXISTS (SELECT 1
FROM employees e
WHERE d.department_id = e.department_id)
ORDER BY d.department_id;
SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (SELECT 1
FROM employees e
WHERE d.department_id = e.department_id)
ORDER BY d.department_id;
BETWEEN and NOT BETWEEN Conditions
SELECT d.department_id, d.department_name
FROM departments d
WHERE department_id BETWEEN 20 AND 40
ORDER BY d.department_id;
SELECT d.department_id, d.department_name
FROM departments d
WHERE department_id NOT BETWEEN 20 AND 40
ORDER BY d.department_id;
LIKE and NOT LIKE Conditions
SELECT d.department_id, d.department_name
FROM departments d
WHERE department_name LIKE '%O%'
ORDER BY d.department_id;
SELECT d.department_id, d.department_name
FROM departments d
WHERE department_name NOT LIKE '%O%'
ORDER BY d.department_id;
OR CONDITION
SELECT e.employee_id, e.employee_name, e.department_id, e.salary, e.job
FROM employees e
WHERE e.department_id = 20
AND
OR
e.job = 'MANAGER'
e.job = 'CLERK'
ORDER BY e.employee_id;
SELECT e.employee_id, e.employee_name, e.department_id, e.salary, e.job
FROM employees e
WHERE e.department_id = 20
AND
(e.job = 'MANAGER' OR e.job = 'CLERK')
ORDER BY e.employee_id;
4. The ORDER BY Clause
Identify Columns : Expression, Position and Column Alias
SELECT e.salary, e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY e.salary, e.commission;
SELECT e.salary, e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY e.salary + NVL(e.commission,0);
SELECT e.salary, e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY 1;
SELECT e.salary AS sal, e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY sal;
Ascending (ASC) and Descending (DESC) Order
-- Ascending (ASC) by default.
SELECT e.salary AS sal, e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY e.salary;
-- Explicitly setting ASC.
SELECT e.salary AS sal, e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY e.salary ASC;
SELECT e.salary AS sal, e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY e.salary DESC;
SELECT e.salary, e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY e.salary ASC, e.commission DESC;
Handling NULLs : NULLS FIRST and NULLS LAST
SELECT e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY e.commission ASC;
SELECT e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY e.commission ASC NULLS FIRST;
SELECT e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY e.commission DESC;
SELECT e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY e.commission DESC NULLS LAST;
SELECT e.salary, e.commission, e.employee_name
FROM employees e
WHERE department_id = 30
ORDER BY e.salary ASC NULLS FIRST, e.commission DESC NULLS LAST;
5. JOINS
[INNER] JOIN ... ON
SELECT d.department_name,
e.employee_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id
SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id = e.department_id
AND
d.department_id >= 30
ORDER BY d.department_name;
LEFT [OUTER] JOIN
SELECT d.department_name,
e.employee_name
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id =
e.department_id
WHERE d.department_id >= 30
ORDER BY d.department_name, e.employee_name;
SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id = e.department_id (+)
AND
d.department_id >= 30
ORDER BY d.department_name, e.employee_name;
SELECT d.department_name,
e.employee_name
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id AND
e.salary >= 2000
WHERE d.department_id >= 30
ORDER BY d.department_name, e.employee_name;
RIGHT [OUTER] JOIN
SELECT d.department_name,
e.employee_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.department_id =
d.department_id
WHERE d.department_id >= 30
ORDER BY d.department_name, e.employee_name;
FULL [OUTER] JOIN
INSERT INTO employees VALUES (8888,'JONES','DBA',null,to_date('02-11982','dd-mm-yyyy'),1300,NULL,NULL);
COMMIT;
SELECT d.department_name,
e.employee_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id =
d.department_id
ORDER BY d.department_name, e.employee_name;
SELECT d.department_name,
e.employee_name
FROM employees e, departments d
WHERE e.department_id = d.department_id (+)
UNION ALL
SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id = e.department_id (+)
AND
e.employee_name IS NULL
ORDER BY 1, 2;
CROSS JOIN
SELECT e.employee_name,
d.department_name
FROM employees e
CROSS JOIN departments d
ORDER BY e.employee_name, d.department_name;
SELECT e.employee_name,
d.department_name
FROM employees e, departments d
ORDER BY e.employee_name, d.department_name;
6. Set Operators
UNION
SELECT department_id, department_name
FROM departments
WHERE department_id <= 30
UNION
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY 1;
UNION ALL
SELECT department_id, department_name
FROM departments
WHERE department_id <= 30
UNION ALL
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY 1;
INTERSECT
SELECT department_id, department_name
FROM departments
WHERE department_id <= 30
INTERSECT
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY 1;
MINUS
SELECT department_id, department_name
FROM departments
WHERE department_id <= 30
MINUS
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY 1;
ORDER BY
-- Column name.
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10
UNION ALL
SELECT department_id, department_name
FROM departments
WHERE department_id >= 20
ORDER BY employee_id;
6. The GROUP BY Clause and HAVING Clause
SELECT COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM employees e;
SELECT e.department_id,
COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM employees e
GROUP BY e.department_id
ORDER BY e.department_i
SELECT e.department_id,
e.job,
COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM employees e
GROUP BY e.department_id, e.job
ORDER BY e.department_id, e.job;
Joins
SELECT d.department_name,
COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY d.department_name;
SELECT d.department_name,
COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY d.department_name;
HAVING Clause
SELECT d.department_name, e.job,
COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name, e.job
ORDER BY d.department_name, e.job;
SELECT d.department_name, e.job,
COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name, e.job
HAVING COUNT(e.employee_id) > 1
ORDER BY d.department_name, e.job;
SELECT COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
HAVING COUNT(e.employee_id) > 2
ORDER BY d.department_name, e.job;
