Содержание
- 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. Скачать презентацию