Oracle SQL basics, the select statement

Сентябрь 6, 2022

Главная
Алгебра
Oracle SQL basics, the select statement

Содержание

2. Objectives SQL ANSI Standard Oracle Human Resources (HR) Sample Schema The SELECT Statement Joins Set Operations
3. ANSI STANDARDS FOR SQL 2012 © EPAM Systems, RD Dep.
4. ANSI Standards for SQL 2012 © EPAM Systems, RD Dep.
5. ANSI/ISO Standard Structure 2012 © EPAM Systems, RD Dep.
6. Core SQL Language Syntax and Semantic ISO/IEC 9075-1:2008 Part 1: Framework (SQL/Framework) Provides logical concepts. ISO/IEC
7. ORACLE HUMAN RESOURCES SAMPLE SCHEMA OVERVIEW 2012 © EPAM Systems, RD Dep.
8. 2012 © EPAM Systems, RD Dep. Oracle Sample Human Resources (HR) Schema
9. 2012 © EPAM Systems, RD Dep. Retrieving all data from Employees table
10. 2012 © EPAM Systems, RD Dep. Employees Table Properties
11. 2012 © EPAM Systems, RD Dep. Departments Table
12. 2012 © EPAM Systems, RD Dep. Jobs Table
13. 2012 © EPAM Systems, RD Dep. Job_history Table
14. 2012 © EPAM Systems, RD Dep. Locations Table
15. 2012 © EPAM Systems, RD Dep. Countries and Regions Tables
16. 2012 © EPAM Systems, RD Dep. Exploring data: Select Distinct Records
17. 2012 © EPAM Systems, RD Dep. Exploring data: Counting Records
18. 2012 © EPAM Systems, RD Dep. Exploring data: Using COUNT Function
19. 2012 © EPAM Systems, RD Dep. Using COUNT Function
20. THE SELECT STATEMENT 2012 © EPAM Systems, RD Dep.
21. Basic Language Elements Statements Queries Clauses Expressions Predicates Insignificant whitespaces 2012 © EPAM Systems, RD Dep.
22. SELECT emp.job_id, avg(emp.salary) FROM employees emp WHERE emp.salary > 10000 GROUP BY emp.job_id HAVING avg(emp.salary) >
23. SELECT emp.job_id AS "Group by job", avg(emp.salary) "Salary, AVG" FROM employees "EMP" WHERE "EMP".salary > 10000
24. SELECT e.job_id AS "Group by job", avg(e.commission_pct) "Commission, AVG" FROM employees e WHERE "E".salary > 9000
25. Oracle Query Block Structure and WHERE Clause 2012 © EPAM Systems, RD Dep.
26. SELECT Columns List 2012 © EPAM Systems, RD Dep.
27. Tables References (simplified FROM clause) 2012 © EPAM Systems, RD Dep.
28. GROUP BY and HAVING clauses, ORDER BY clause 2012 © EPAM Systems, RD Dep.
29. JOIN TABLES 2012 © EPAM Systems, RD Dep.
30. SQL Joins 2012 © EPAM Systems, RD Dep.
31. SQL Joins Classification Inner join Equi-join Natural join Outer joins Left outer join Right outer join
32. SELECT emp.first_name, emp.last_name, emp.job_id, emp.salary, jb.* FROM employees emp, jobs jb; SELECT emp.first_name, emp.last_name, emp.job_id, emp.salary,
33. SELECT count(*) AS cnt FROM employees emp, jobs jb; SELECT count(*) AS cnt FROM employees emp
34. SELECT emp.first_name, emp.last_name, emp.job_id, emp.salary, jb.* FROM employees emp, jobs jb WHERE emp.job_id = jb.job_id; SELECT
35. Check Your Join (Using foreign keys) 2012 © EPAM Systems, RD Dep.
36. Check Your Join (Nullable fields) DESCRIBE employees Name Null Type -------------- -------- ------------ EMPLOYEE_ID NOT NULL
37. Join Syntax 2012 © EPAM Systems, RD Dep.
38. Inner / Outer / Cross Joins Syntax 2012 © EPAM Systems, RD Dep.
39. SELECT emp.first_name, emp.last_name, emp.salary, jb.* FROM employees emp, jobs jb WHERE emp.job_id = jb.job_id; SELECT emp.first_name,
40. Outer Equi-joins 2012 © EPAM Systems, RD Dep.
41. SELECT emp.first_name, emp.last_name, emp.salary, dept.department_name FROM employees emp, departments dept WHERE emp.department_id = dept.department_id(+) ORDER BY
42. SELECT emp.first_name, emp.last_name, emp.salary, dept.department_name, department_id, manager_id FROM employees emp NATURAL LEFT JOIN departments dept; Typical
43. SELECT dept.department_name, max(emp.salary) FROM employees emp, departments dept WHERE emp.department_id(+) = dept.department_id GROUP BY dept.department_name HAVING
44. SELECT dept.department_name, max(emp.salary) FROM employees emp, departments dept WHERE emp.department_id(+) = ept.department_id(+) GROUP BY dept.department_name HAVING
45. SELECT emp.first_name, emp.last_name, emp.salary, mng.first_name manager_first_name, mng.last_name manager_last_name FROM employees emp LEFT JOIN employees mng ON
46. Complex Join Example 2012 © EPAM Systems, RD Dep. Resulting dataset contains 123 rows: 107 employees
47. SET OPERATIONS 2012 © EPAM Systems, RD Dep.
48. Set Operations UNION 2012 © EPAM Systems, RD Dep. INTERSECT EXCEPT
49. Set Operations Syntax 2012 © EPAM Systems, RD Dep. Always the last section
50. SELECT dept.department_name, max(emp.salary) FROM employees emp FULL OUTER JOIN departments dept USING (department_id) GROUP BY dept.department_name
51. ( SELECT dept.department_name, max(emp.salary) FROM employees emp FULL OUTER JOIN departments dept USING (department_id) GROUP BY
52. ( SELECT dept.department_name, max(emp.salary) FROM employees emp, departments dept WHERE emp.department_id(+) = dept.department_id GROUP BY dept.department_name
53. SELECT dept.department_name FROM employees emp, departments dept WHERE emp.department_id(+) = dept.department_id GROUP BY dept.department_name HAVING count(emp.employee_id)
54. SELECT 'Dept' AS "Dept/Job", dept.department_name "Name", avg(emp.salary) "Avg Salary" FROM employees emp JOIN departments dept USING
55. PSEUDOCOLUMNS 2012 © EPAM Systems, RD Dep.
56. Pseudocolumns Oracle Pseudocolumns Overview Hierarchical Query Pseudocolumns Sequence Pseudocolumns Version Query Pseudocolumns COLUMN_VALUE Pseudocolumn OBJECT_ID Pseudocolumn
57. ROWNUM Pseudocolumn 2012 © EPAM Systems, RD Dep. SELECT ROWNUM, employee_id, first_name, last_name FROM employees; SELECT
58. SELECT ROWNUM, first_name, last_name, salary FROM ( SELECT first_name, last_name, salary FROM employees ORDER BY salary
59. SELECT ROWNUM, first_name, last_name, salary FROM ( SELECT first_name, last_name, salary FROM employees ORDER BY salary
60. ROWID Pseudocolumn For each row in the database, the ROWID pseudocolumn returns the address of the
61. ROWID Pseudocolumn 2012 © EPAM Systems, RD Dep. SELECT first_name, last_name, ROWID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE_NO, DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_NO,
62. Locate Datafile where Table is stored 2012 © EPAM Systems, RD Dep. CONNECT SYSTEM SELECT DISTINCT
63. How many blocks table actually occupies 2012 © EPAM Systems, RD Dep. SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) BLOCKS_NUM
65. Скачать презентацию

Слайд 2

Objectives
SQL ANSI Standard
Oracle Human Resources (HR) Sample Schema
The SELECT Statement
Joins
Set Operations
Pseudocolumns
2012

Слайд 3

ANSI STANDARDS FOR SQL
2012 © EPAM Systems, RD Dep.

Слайд 4

ANSI Standards for SQL
2012 © EPAM Systems, RD Dep.

Слайд 5

ANSI/ISO Standard Structure

2012 © EPAM Systems, RD Dep.

Слайд 6

Core SQL Language Syntax and Semantic
ISO/IEC 9075-1:2008 Part 1: Framework (SQL/Framework)

Provides logical concepts.
ISO/IEC 9075-2:2008 Part 2: Foundation (SQL/Foundation) Contains the most central elements of the language and consists of both mandatory and optional features.
ISO/IEC 9075-11:2008 Part 11: Information and Definition Schemas (SQL/Schemata)
Defines the Information Schema and Definition Schema, providing a common set of tools to make SQL databases and objects self-describing.

2012 © EPAM Systems, RD Dep.

Core SQL:2008

Слайд 7

ORACLE HUMAN RESOURCES SAMPLE SCHEMA OVERVIEW
2012 © EPAM Systems, RD Dep.

Слайд 8

2012 © EPAM Systems, RD Dep.
Oracle Sample Human Resources (HR) Schema

Слайд 9

2012 © EPAM Systems, RD Dep.
Retrieving all data from Employees table

Слайд 10

2012 © EPAM Systems, RD Dep.
Employees Table Properties

Слайд 11

2012 © EPAM Systems, RD Dep.
Departments Table

Слайд 12

2012 © EPAM Systems, RD Dep.
Jobs Table

Слайд 13

2012 © EPAM Systems, RD Dep.
Job_history Table

Слайд 14

2012 © EPAM Systems, RD Dep.
Locations Table

Слайд 15

2012 © EPAM Systems, RD Dep.
Countries and Regions Tables

Слайд 16

2012 © EPAM Systems, RD Dep.
Exploring data: Select Distinct Records

Слайд 17

2012 © EPAM Systems, RD Dep.
Exploring data: Counting Records

Слайд 18

2012 © EPAM Systems, RD Dep.
Exploring data: Using COUNT Function

Слайд 19

2012 © EPAM Systems, RD Dep.
Using COUNT Function

Слайд 20

THE SELECT STATEMENT
2012 © EPAM Systems, RD Dep.

Слайд 21

Basic Language Elements
Statements
Queries
Clauses
Expressions
Predicates
Insignificant whitespaces
2012 © EPAM Systems, RD Dep.
Statement
FROM clause

WHERE clause

GROUP BY clause

HAVING clause

ORDER BY clause

SELECT job_id, avg(salary)
FROM employees
WHERE salary > 10000
GROUP BY job_id
HAVING avg(salary) > 11000
ORDER BY 2 DESC;

Слайд 22

SELECT emp.job_id, avg(emp.salary)
FROM employees emp
WHERE emp.salary > 10000
GROUP BY emp.job_id

HAVING avg(emp.salary) > 11000
ORDER BY avg(emp.salary) DESC;

Tables Aliases

2012 © EPAM Systems, RD Dep.

Table aliases is optional mechanism to make queries easier to read, understand and maintain.
Aliases should be meaningful!
Aliases can be used with asterisk, like SELECT emp.*
Optional AS keyword between table name and its alias throws error in Oracle (non-standard behavior).

Слайд 23

SELECT
emp.job_id AS "Group by job",
avg(emp.salary) "Salary, AVG"
FROM

employees "EMP"
WHERE "EMP".salary > 10000
GROUP BY emp.job_id
HAVING avg(emp.salary) > 11000
ORDER BY -"Salary, AVG";

Field Aliases

2012 © EPAM Systems, RD Dep.

Naming Rules:
Must not exceed 30 characters.
First character must be a letter
The rest can be any combination of letters, numerals, dollar signs ($), pound signs (#), and underscores (_).
Identifier enclosed by double quotation marks (") can contain any combination of legal characters, including spaces but excluding quotation marks.
Identifiers are not case sensitive except within double quotation marks.

Слайд 24

SELECT e.job_id AS "Group by job",
avg(e.commission_pct) "Commission, AVG"
FROM employees e

WHERE "E".salary > 9000
GROUP BY e.job_id
--HAVING min(e.commission_pct) > 0
ORDER BY 2 DESC NULLS LAST;

ORDER BY clause (NULLs Ordering)

ASC | DESC Specify the ordering sequence. ASC is the default.
NULLS FIRST | NULLS LAST Specify whether returned rows containing nulls should appear first or last in the ordering sequence.
NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.

2012 © EPAM Systems, RD Dep.

Слайд 25

Oracle Query Block Structure and WHERE Clause
2012 © EPAM Systems, RD

Dep.

Слайд 26

SELECT Columns List
2012 © EPAM Systems, RD Dep.

Слайд 27

Tables References (simplified FROM clause)
2012 © EPAM Systems, RD Dep.

Слайд 28

GROUP BY and HAVING clauses, ORDER BY clause
2012 © EPAM Systems,

RD Dep.

Слайд 29

JOIN TABLES
2012 © EPAM Systems, RD Dep.

Слайд 30

SQL Joins
2012 © EPAM Systems, RD Dep.

Слайд 31

SQL Joins Classification
Inner join
Equi-join
Natural join
Outer joins
Left outer join
Right outer join
Full outer

join
Cross join
Self-join

2012 © EPAM Systems, RD Dep.

Qualified joins

Слайд 32

SELECT emp.first_name, emp.last_name,
emp.job_id, emp.salary, jb.*
FROM employees emp, jobs jb;

SELECT emp.first_name, emp.last_name,
emp.job_id, emp.salary, jb.*
FROM employees emp CROSS JOIN jobs jb;

Simple Join Example (cross join Employees and Jobs)

2012 © EPAM Systems, RD Dep.

Слайд 33

SELECT count() AS cnt
FROM employees emp, jobs jb;
SELECT count()

AS cnt
FROM employees emp CROSS JOIN jobs jb;
SELECT
(SELECT count(*) FROM employees emp)
* (SELECT count(*) FROM jobs jb) cnt
FROM dual;

Prove Cross Join

2012 © EPAM Systems, RD Dep.

Слайд 34

SELECT emp.first_name, emp.last_name,
emp.job_id, emp.salary, jb.*
FROM employees emp, jobs jb

WHERE emp.job_id = jb.job_id;
SELECT emp.first_name, emp.last_name,
emp.job_id, emp.salary, jb.*
FROM employees emp CROSS JOIN jobs jb
WHERE emp.job_id = jb.job_id;

Reducing Cartesian Product to get meaningful result

2012 © EPAM Systems, RD Dep.

Senseless syntax

Слайд 35

Check Your Join (Using foreign keys)
2012 © EPAM Systems, RD Dep.

Слайд 36

Check Your Join (Nullable fields)
DESCRIBE employees
Name Null Type
-------------- -------- ------------

EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

2012 © EPAM Systems, RD Dep.

DESC jobs
Name Null Type
---------- -------- ------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)

Слайд 37

Join Syntax
2012 © EPAM Systems, RD Dep.

Слайд 38

Inner / Outer / Cross Joins Syntax
2012 © EPAM Systems, RD

Dep.

Слайд 39

SELECT emp.first_name, emp.last_name, emp.salary, jb.*
FROM employees emp, jobs jb
WHERE emp.job_id

= jb.job_id;
SELECT emp.first_name, emp.last_name, emp.salary,
job_id, jb.job_title, jb.min_salary, jb.max_salary
FROM employees emp NATURAL JOIN jobs jb;
SELECT emp.first_name, emp.last_name, emp.salary,
job_id, jb.job_title, jb.min_salary, jb.max_salary
FROM employees emp JOIN jobs jb USING(job_id);
SELECT emp.first_name, emp.last_name, emp.salary, jb.*
FROM employees emp JOIN jobs jb ON emp.job_id=jb.job_id;

Inner Equi-joins

2012 © EPAM Systems, RD Dep.

Слайд 40

Outer Equi-joins
2012 © EPAM Systems, RD Dep.

Слайд 41

SELECT emp.first_name, emp.last_name, emp.salary, dept.department_name
FROM employees emp, departments dept
WHERE emp.department_id

= dept.department_id(+)
ORDER BY dept.department_name NULLS FIRST;
SELECT emp.first_name, emp.last_name, emp.salary, dept.department_name
FROM employees emp NATURAL LEFT OUTER JOIN departments dept
ORDER BY dept.department_name NULLS FIRST;
SELECT emp.first_name, emp.last_name, emp.salary, dept.department_name
FROM employees emp LEFT OUTER JOIN departments dept USING (department_id)
ORDER BY dept.department_name NULLS FIRST;
SELECT emp.first_name, emp.last_name, emp.salary, dept.department_name
FROM employees emp LEFT OUTER JOIN departments dept
ON (emp.department_id = dept.department_id)
ORDER BY dept.department_name NULLS FIRST;

Left Outer Equi-joins

2012 © EPAM Systems, RD Dep.

Old Oracle’s syntax

Слайд 42

SELECT emp.first_name, emp.last_name,
emp.salary, dept.department_name,
department_id, manager_id
FROM employees emp NATURAL

LEFT JOIN departments dept;

Typical Mistake with NATURAL JOIN

2012 © EPAM Systems, RD Dep.

SELECT emp.first_name, emp.last_name,
emp.salary, dept.department_name,
department_id, manager_id
FROM employees emp LEFT OUTER JOIN departments dept
USING (department_id, manager_id);

Do you really want this?

Слайд 43

SELECT dept.department_name, max(emp.salary)
FROM employees emp, departments dept
WHERE emp.department_id(+) = dept.department_id
GROUP

BY dept.department_name
HAVING count(emp.employee_id) > 0
ORDER BY dept.department_name NULLS FIRST;
SELECT dept.department_name, max(emp.salary)
FROM employees emp NATURAL RIGHT JOIN departments dept
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
ORDER BY dept.department_name NULLS FIRST;
SELECT dept.department_name, max(emp.salary)
FROM employees emp RIGHT OUTER JOIN departments dept
USING (department_id)
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
ORDER BY dept.department_name NULLS FIRST;
SELECT dept.department_name, max(emp.salary)
FROM employees emp RIGHT OUTER JOIN departments dept
ON (emp.department_id = dept.department_id)
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
ORDER BY dept.department_name NULLS FIRST;

Right Outer Equi-joins

2012 © EPAM Systems, RD Dep.

Do you really
want this?

Old Oracle’s syntax

Слайд 44

SELECT dept.department_name, max(emp.salary)
FROM employees emp, departments dept
WHERE emp.department_id(+) =

ept.department_id(+)
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
ORDER BY dept.department_name NULLS FIRST;
SELECT dept.department_name, max(emp.salary)
FROM employees emp NATURAL FULL JOIN departments dept GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
ORDER BY dept.department_name NULLS FIRST;
SELECT dept.department_name, max(emp.salary)
FROM employees emp FULL OUTER JOIN departments dept
USING (department_id)
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
ORDER BY dept.department_name NULLS FIRST;
SELECT dept.department_name, max(emp.salary)
FROM employees emp FULL OUTER JOIN departments dept
ON (emp.department_id = dept.department_id)
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
ORDER BY dept.department_name NULLS FIRST;

Full Outer Equi-joins

2012 © EPAM Systems, RD Dep.

ORA-01468: a predicate may reference only one outer-joined table

Слайд 45

SELECT emp.first_name, emp.last_name, emp.salary,
mng.first_name manager_first_name, mng.last_name manager_last_name
FROM employees emp

LEFT JOIN employees mng
ON emp.manager_id = mng.employee_id;
SELECT emp.first_name, emp.last_name, emp.salary,
mng.first_name manager_first_name, mng.last_name manager_last_name
FROM employees emp, employees mng
WHERE emp.manager_id = mng.employee_id(+);

Self-join

2012 © EPAM Systems, RD Dep.

Слайд 46

Complex Join Example
2012 © EPAM Systems, RD Dep.
Resulting dataset contains 123

rows:
107 employees
16 empty departments

SELECT dept.department_name "Dept",
dept_mng.first_name || ' ' || dept_mng.last_name "Dept Manager",
emp.first_name || ' ' || emp.last_name "Employee",
emp_mng.first_name || ' ' || emp_mng.last_name "Emp Manager"
FROM departments dept
LEFT OUTER JOIN employees dept_mng
ON (dept.manager_id = dept_mng.employee_id)
FULL OUTER JOIN employees emp
ON (emp.department_id = dept.department_id)
LEFT OUTER JOIN employees emp_mng
ON (emp.manager_id=emp_mng.employee_id)
ORDER BY 1 NULLS FIRST, 2, 3, 4;

Слайд 47

SET OPERATIONS
2012 © EPAM Systems, RD Dep.

Слайд 48

Set Operations
UNION
2012 © EPAM Systems, RD Dep.
INTERSECT
EXCEPT

Слайд 49

Слайд 50

SELECT dept.department_name, max(emp.salary)
FROM employees emp
FULL OUTER JOIN departments

dept
USING (department_id)
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
ORDER BY dept.department_name NULLS FIRST;

SELECT dept.department_name,
max(emp.salary)
FROM employees emp, departments dept
WHERE
emp.department_id(+) = dept.department_id
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
UNION
SELECT dept.department_name, max(emp.salary)
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id(+)
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
ORDER BY 1 NULLS FIRST;
SELECT dept.department_name, max(emp.salary)
FROM employees emp, departments dept
WHERE emp.department_id(+) = dept.department_id
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
UNION
SELECT NULL, max(salary)
FROM employees emp
WHERE department_id IS NULL
ORDER BY 1 NULLS FIRST;

Union Operation

Слайд 51

(
SELECT dept.department_name, max(emp.salary)
FROM employees emp FULL OUTER

JOIN departments dept
USING (department_id)
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
)
MINUS
(
SELECT dept.department_name, max(emp.salary)
FROM employees emp, departments dept
WHERE emp.department_id(+) = dept.department_id
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
UNION
SELECT dept.department_name, max(emp.salary)
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id(+)
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
);

Minus Operation (Check datasets equivalence)

Full Outer Join

Right Outer Join
Union
Left Outer join

Слайд 52

(
SELECT dept.department_name, max(emp.salary)
FROM employees emp, departments dept

WHERE emp.department_id(+) = dept.department_id
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
UNION
SELECT dept.department_name, max(emp.salary)
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id(+)
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
)
MINUS
(
SELECT dept.department_name, max(emp.salary)
FROM employees emp FULL OUTER JOIN departments dept
USING (department_id)
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 0
);

Minus Operation (Check datasets equivalence)

Right Outer Join
Union
Left Outer join

Full Outer Join

Слайд 53

SELECT dept.department_name
FROM employees emp, departments dept
WHERE emp.department_id(+) = dept.department_id
GROUP

BY dept.department_name
HAVING count(emp.employee_id) > 3
INTERSECT
SELECT dept.department_name
FROM employees emp, departments dept
WHERE emp.department_id(+) = dept.department_id
GROUP BY dept.department_name
HAVING MAX(emp.salary) > 9000;
SELECT dept.department_name
FROM employees emp, departments dept
WHERE emp.department_id(+) = dept.department_id
GROUP BY dept.department_name
HAVING count(emp.employee_id) > 3 and max(emp.salary) > 9000;

Intersect Operation

Слайд 54

SELECT 'Dept' AS "Dept/Job",
dept.department_name "Name",
avg(emp.salary) "Avg Salary"

FROM employees emp
JOIN departments dept
USING (department_id)
GROUP BY department_id, dept.department_name
HAVING avg(emp.salary) > 9000
UNION ALL
SELECT 'Job',
jb.job_title,
avg(emp.salary)
FROM employees emp
JOIN jobs jb
USING (job_id)
GROUP BY job_id, jb.job_title
HAVING avg(emp.salary) > 9000
ORDER BY 1, 2, 3;

UNION ALL Operation

Слайд 55

Слайд 56

Pseudocolumns
Oracle Pseudocolumns Overview
Hierarchical Query Pseudocolumns
Sequence Pseudocolumns
Version Query Pseudocolumns
COLUMN_VALUE Pseudocolumn
OBJECT_ID Pseudocolumn
OBJECT_VALUE Pseudocolumn
ORA_ROWSCN

Pseudocolumn
ROWID Pseudocolumn
ROWNUM Pseudocolumn
XMLDATA Pseudocolumn

Слайд 57

last_name
FROM employees;

SELECT ROWNUM, employee_id,
first_name, last_name
FROM employees
ORDER BY first_name, last_name;

Isn’t good idea if we need employee number into the list

Слайд 58

SELECT ROWNUM, first_name,
last_name,
salary
FROM (
SELECT

first_name,
last_name,
salary
FROM employees
ORDER BY salary DESC
);

ROWNUM Pseudocolumn

SELECT ROWNUM,
first_name,
last_name,
salary
FROM employees
ORDER BY salary DESC;

Слайд 59

SELECT ROWNUM, first_name, last_name, salary
FROM (
SELECT first_name, last_name,

salary
FROM employees
ORDER BY salary DESC
)
WHERE ROWNUM <= 5;
SELECT ROWNUM, first_name, last_name, salary
FROM (
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
)
WHERE ROWNUM BETWEEN 3 AND 5;

Limiting result set of SELECT query

Слайд 60

ROWID Pseudocolumn
For each row in the database, the ROWID pseudocolumn returns

the address of the row.
Oracle Database rowid values contain information necessary to locate a row:
The data object number of the object
The data block in the data file in which the row resides
The position of the row in the data block (first row is 0)
The data file in which the row resides (first file is 1). The file number is relative to the tablespace.
Rowid values have several important uses:
They are the fastest way to access a single row.
They can show you how the rows in a table are stored.
They are unique identifiers for rows in a table.

Слайд 61

ROWID,
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE_NO,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_NO,
DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) ROW_NO
FROM employees
ORDER BY 4, 5, 6;

Data file

Block

Row

Слайд 62

SYSTEM
SELECT DISTINCT df.FILE_NAME
FROM hr.employees emp
JOIN dba_data_files df
ON (DBMS_ROWID.ROWID_RELATIVE_FNO(emp.ROWID)=df.RELATIVE_FNO)
ORDER BY 1;
DISCONNECT

Слайд 63

COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) BLOCKS_NUM
FROM employees;

CONNECT SYSTEM/oracle
SELECT df.file_name, ts.tablespace_name, ts.block_size,
COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(emp.ROWID)) BLOCKS_NUM,
ts.block_size
* COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(emp.ROWID)) TBL_SIZE
FROM hr.employees emp
JOIN dba_data_files df
ON (DBMS_ROWID.ROWID_RELATIVE_FNO(emp.ROWID) = df.RELATIVE_FNO)
JOIN dba_tablespaces ts
ON (df.tablespace_name = ts.tablespace_name)
GROUP BY df.file_name, ts.tablespace_name, ts.block_size;
DISCONNECT

Oracle SQL basics, the select statement

Содержание

ObjectivesSQL ANSI StandardOracle Human Resources (HR) Sample SchemaThe SELECT StatementJoinsSet OperationsPseudocolumns2012

ANSI STANDARDS FOR SQL2012 © EPAM Systems, RD Dep.

ANSI Standards for SQL2012 © EPAM Systems, RD Dep.

ANSI/ISO Standard Structure 2012 © EPAM Systems, RD Dep.

Core SQL Language Syntax and SemanticISO/IEC 9075-1:2008 Part 1: Framework (SQL/Framework)

ORACLE HUMAN RESOURCES SAMPLE SCHEMA OVERVIEW2012 © EPAM Systems, RD Dep.

2012 © EPAM Systems, RD Dep.Oracle Sample Human Resources (HR) Schema

2012 © EPAM Systems, RD Dep.Retrieving all data from Employees table

2012 © EPAM Systems, RD Dep.Employees Table Properties

2012 © EPAM Systems, RD Dep.Departments Table

2012 © EPAM Systems, RD Dep.Jobs Table

2012 © EPAM Systems, RD Dep.Job_history Table

2012 © EPAM Systems, RD Dep.Locations Table

2012 © EPAM Systems, RD Dep.Countries and Regions Tables

2012 © EPAM Systems, RD Dep.Exploring data: Select Distinct Records

2012 © EPAM Systems, RD Dep.Exploring data: Counting Records

2012 © EPAM Systems, RD Dep.Exploring data: Using COUNT Function

2012 © EPAM Systems, RD Dep.Using COUNT Function

THE SELECT STATEMENT2012 © EPAM Systems, RD Dep.

Basic Language ElementsStatementsQueriesClauses ExpressionsPredicatesInsignificant whitespaces2012 © EPAM Systems, RD Dep.StatementFROM clause

SELECT emp.job_id, avg(emp.salary)FROM employees emp WHERE emp.salary > 10000GROUP BY emp.job_id

SELECT emp.job_id AS "Group by job", avg(emp.salary) "Salary, AVG" FROM

SELECT e.job_id AS "Group by job", avg(e.commission_pct) "Commission, AVG"FROM employees e

Oracle Query Block Structure and WHERE Clause2012 © EPAM Systems, RD

SELECT Columns List 2012 © EPAM Systems, RD Dep.

Tables References (simplified FROM clause)2012 © EPAM Systems, RD Dep.

GROUP BY and HAVING clauses, ORDER BY clause2012 © EPAM Systems,

JOIN TABLES2012 © EPAM Systems, RD Dep.

SQL Joins2012 © EPAM Systems, RD Dep.

SQL Joins ClassificationInner joinEqui-joinNatural joinOuter joinsLeft outer joinRight outer joinFull outer

SELECT emp.first_name, emp.last_name, emp.job_id, emp.salary, jb.* FROM employees emp, jobs jb;

SELECT count(*) AS cnt FROM employees emp, jobs jb; SELECT count(*)

SELECT emp.first_name, emp.last_name, emp.job_id, emp.salary, jb.* FROM employees emp, jobs jb

Check Your Join (Using foreign keys)2012 © EPAM Systems, RD Dep.

Check Your Join (Nullable fields)DESCRIBE employeesName Null Type -------------- -------- ------------

Join Syntax2012 © EPAM Systems, RD Dep.

Inner / Outer / Cross Joins Syntax2012 © EPAM Systems, RD

SELECT emp.first_name, emp.last_name, emp.salary, jb.*FROM employees emp, jobs jb WHERE emp.job_id

Outer Equi-joins2012 © EPAM Systems, RD Dep.

SELECT emp.first_name, emp.last_name, emp.salary, dept.department_nameFROM employees emp, departments dept WHERE emp.department_id

SELECT emp.first_name, emp.last_name, emp.salary, dept.department_name, department_id, manager_id FROM employees emp NATURAL

SELECT dept.department_name, max(emp.salary)FROM employees emp, departments deptWHERE emp.department_id(+) = dept.department_id GROUP

SELECT dept.department_name, max(emp.salary) FROM employees emp, departments dept WHERE emp.department_id(+) =

SELECT emp.first_name, emp.last_name, emp.salary, mng.first_name manager_first_name, mng.last_name manager_last_name FROM employees emp

Complex Join Example2012 © EPAM Systems, RD Dep.Resulting dataset contains 123

SET OPERATIONS2012 © EPAM Systems, RD Dep.

Set OperationsUNION2012 © EPAM Systems, RD Dep.INTERSECTEXCEPT

Set Operations Syntax2012 © EPAM Systems, RD Dep.Always the last section

SELECT dept.department_name, max(emp.salary) FROM employees emp FULL OUTER JOIN departments

( SELECT dept.department_name, max(emp.salary) FROM employees emp FULL OUTER

( SELECT dept.department_name, max(emp.salary) FROM employees emp, departments dept

SELECT dept.department_name FROM employees emp, departments dept WHERE emp.department_id(+) = dept.department_idGROUP

SELECT 'Dept' AS "Dept/Job", dept.department_name "Name", avg(emp.salary) "Avg Salary"

PSEUDOCOLUMNS2012 © EPAM Systems, RD Dep.

PseudocolumnsOracle Pseudocolumns OverviewHierarchical Query PseudocolumnsSequence PseudocolumnsVersion Query PseudocolumnsCOLUMN_VALUE PseudocolumnOBJECT_ID PseudocolumnOBJECT_VALUE PseudocolumnORA_ROWSCN

ROWNUM Pseudocolumn2012 © EPAM Systems, RD Dep.SELECT ROWNUM, employee_id, first_name,

SELECT ROWNUM, first_name, last_name, salary FROM ( SELECT

SELECT ROWNUM, first_name, last_name, salary FROM ( SELECT first_name, last_name,

ROWID PseudocolumnFor each row in the database, the ROWID pseudocolumn returns

ROWID Pseudocolumn2012 © EPAM Systems, RD Dep.SELECT first_name, last_name,

Locate Datafile where Table is stored2012 © EPAM Systems, RD Dep.CONNECT

How many blocks table actually occupies 2012 © EPAM Systems, RD Dep.SELECT

Похожие презентации

Objectives
SQL ANSI Standard
Oracle Human Resources (HR) Sample Schema
The SELECT Statement
Joins
Set Operations
Pseudocolumns
2012

ANSI STANDARDS FOR SQL
2012 © EPAM Systems, RD Dep.

ANSI Standards for SQL
2012 © EPAM Systems, RD Dep.

ANSI/ISO Standard Structure

2012 © EPAM Systems, RD Dep.

Core SQL Language Syntax and Semantic
ISO/IEC 9075-1:2008 Part 1: Framework (SQL/Framework)

ORACLE HUMAN RESOURCES SAMPLE SCHEMA OVERVIEW
2012 © EPAM Systems, RD Dep.

2012 © EPAM Systems, RD Dep.
Oracle Sample Human Resources (HR) Schema

2012 © EPAM Systems, RD Dep.
Retrieving all data from Employees table

2012 © EPAM Systems, RD Dep.
Employees Table Properties

2012 © EPAM Systems, RD Dep.
Departments Table

2012 © EPAM Systems, RD Dep.
Jobs Table

2012 © EPAM Systems, RD Dep.
Job_history Table

2012 © EPAM Systems, RD Dep.
Locations Table

2012 © EPAM Systems, RD Dep.
Countries and Regions Tables

2012 © EPAM Systems, RD Dep.
Exploring data: Select Distinct Records

2012 © EPAM Systems, RD Dep.
Exploring data: Counting Records

2012 © EPAM Systems, RD Dep.
Exploring data: Using COUNT Function

2012 © EPAM Systems, RD Dep.
Using COUNT Function

THE SELECT STATEMENT
2012 © EPAM Systems, RD Dep.

Basic Language Elements
Statements
Queries
Clauses
Expressions
Predicates
Insignificant whitespaces
2012 © EPAM Systems, RD Dep.
Statement
FROM clause

SELECT emp.job_id, avg(emp.salary)
FROM employees emp
WHERE emp.salary > 10000
GROUP BY emp.job_id

SELECT
emp.job_id AS "Group by job",
avg(emp.salary) "Salary, AVG"
FROM

SELECT e.job_id AS "Group by job",
avg(e.commission_pct) "Commission, AVG"
FROM employees e

Oracle Query Block Structure and WHERE Clause
2012 © EPAM Systems, RD

SELECT Columns List
2012 © EPAM Systems, RD Dep.

Tables References (simplified FROM clause)
2012 © EPAM Systems, RD Dep.

GROUP BY and HAVING clauses, ORDER BY clause
2012 © EPAM Systems,

JOIN TABLES
2012 © EPAM Systems, RD Dep.

SQL Joins
2012 © EPAM Systems, RD Dep.

SQL Joins Classification
Inner join
Equi-join
Natural join
Outer joins
Left outer join
Right outer join
Full outer

SELECT emp.first_name, emp.last_name,
emp.job_id, emp.salary, jb.*
FROM employees emp, jobs jb;

SELECT count() AS cnt
FROM employees emp, jobs jb;
SELECT count()

SELECT emp.first_name, emp.last_name,
emp.job_id, emp.salary, jb.*
FROM employees emp, jobs jb

Check Your Join (Using foreign keys)
2012 © EPAM Systems, RD Dep.

Check Your Join (Nullable fields)
DESCRIBE employees
Name Null Type
-------------- -------- ------------

Join Syntax
2012 © EPAM Systems, RD Dep.

Inner / Outer / Cross Joins Syntax
2012 © EPAM Systems, RD

SELECT emp.first_name, emp.last_name, emp.salary, jb.*
FROM employees emp, jobs jb
WHERE emp.job_id

Outer Equi-joins
2012 © EPAM Systems, RD Dep.

SELECT emp.first_name, emp.last_name, emp.salary, dept.department_name
FROM employees emp, departments dept
WHERE emp.department_id

SELECT emp.first_name, emp.last_name,
emp.salary, dept.department_name,
department_id, manager_id
FROM employees emp NATURAL

SELECT dept.department_name, max(emp.salary)
FROM employees emp, departments dept
WHERE emp.department_id(+) = dept.department_id
GROUP

SELECT dept.department_name, max(emp.salary)
FROM employees emp, departments dept
WHERE emp.department_id(+) =

SELECT emp.first_name, emp.last_name, emp.salary,
mng.first_name manager_first_name, mng.last_name manager_last_name
FROM employees emp

Complex Join Example
2012 © EPAM Systems, RD Dep.
Resulting dataset contains 123

SET OPERATIONS
2012 © EPAM Systems, RD Dep.

Set Operations
UNION
2012 © EPAM Systems, RD Dep.
INTERSECT
EXCEPT

Set Operations Syntax
2012 © EPAM Systems, RD Dep.
Always the last section

SELECT dept.department_name, max(emp.salary)
FROM employees emp
FULL OUTER JOIN departments

(
SELECT dept.department_name, max(emp.salary)
FROM employees emp FULL OUTER

(
SELECT dept.department_name, max(emp.salary)
FROM employees emp, departments dept

SELECT dept.department_name
FROM employees emp, departments dept
WHERE emp.department_id(+) = dept.department_id
GROUP

SELECT 'Dept' AS "Dept/Job",
dept.department_name "Name",
avg(emp.salary) "Avg Salary"

PSEUDOCOLUMNS
2012 © EPAM Systems, RD Dep.

Pseudocolumns
Oracle Pseudocolumns Overview
Hierarchical Query Pseudocolumns
Sequence Pseudocolumns
Version Query Pseudocolumns
COLUMN_VALUE Pseudocolumn
OBJECT_ID Pseudocolumn
OBJECT_VALUE Pseudocolumn
ORA_ROWSCN

ROWNUM Pseudocolumn
2012 © EPAM Systems, RD Dep.
SELECT ROWNUM, employee_id,
first_name,

SELECT ROWNUM, first_name,
last_name,
salary
FROM (
SELECT

SELECT ROWNUM, first_name, last_name, salary
FROM (
SELECT first_name, last_name,

ROWID Pseudocolumn
For each row in the database, the ROWID pseudocolumn returns

ROWID Pseudocolumn
2012 © EPAM Systems, RD Dep.
SELECT first_name,
last_name,

Locate Datafile where Table is stored
2012 © EPAM Systems, RD Dep.
CONNECT

How many blocks table actually occupies
2012 © EPAM Systems, RD Dep.
SELECT