Oracle - Programming with PL/SQL

Introduction to the Oracle9i: SQL Electronic Presentation 40049GC10 Production 1.0 June 2001 D33057Copyright © Oracle Corporation, 2000, 2001. All rights reserved. This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987). The information in this document is subject to change without notice. If you find any problems in the documentation, please report them in writing to Education Products, Oracle Corporation, 500 Oracle Parkway, Box SB-6, Redwood Shores, CA 94065. Oracle Corporation does not warrant that this document is error-free. Oracle and all references to Oracle products are trademarks or registered trademarks of Oracle Corporation. All other products or company names are used for identification purposes only, and may be trademarks of their respective owners. Authors Nancy Greenberg Priya Nathan Technical Contributors and Reviewers Josephine Turner Martin Alvarez Anna Atkinson Don Bates Marco Berbeek Andrew Brannigan Michael Gerlach Sharon Gray Rosita Hanoman Mozhe Jalali Sarah Jones Charbel Khouri Christopher Lawless Diana Lorentz Nina Minchen Cuong Nguyen Daphne Nougier Patrick Odell Laura Pezzini Stacey Procter Maribel Renau Bryan Roberts Helen Robertson Sunshine Salmon Casa Sharif Bernard Soleillant Craig Spoonemore Ruediger Steffan Karla Villasenor Andree Wheeley Lachlan Williams Publisher Sheryl DomingueCopyright © Oracle Corporation, 2001. All rights reserved. Curriculum MapCopyright © Oracle Corporation, 2001. All rights reserved. Languages Curriculum for Oracle9i Introduction to Oracle9i for Experienced SQL Users inClass or Advanced PL/SQL inClass Advanced PL/SQL inClass SQL for End Users inClass SQL for End Users Extended Data inClass Retrieval with SQL Extended Data Retrieval SQL1 with SQL Introduction to Oracle9i: PL/SQL inClassDevelop PL/SQL Program Units Develop PL/SQL Program Units PL/SQL Fundamentals Introduction to Oracle9i: SQL inClassCopyright © Oracle Corporation, 2001. All rights reserved. Languages Curriculum for Oracle9i Introduction to Oracle9i for Experienced SQL Users inClass or Advanced PL/SQL inClass Advanced PL/SQL inClass SQL for End Users inClass SQL for End Users Extended Data inClass Retrieval with SQL Extended Data Retrieval SQL1 with SQL Introduction to Oracle9i: PL/SQL inClassDevelop PL/SQL Program Units Develop PL/SQL Program Units PL/SQL Fundamentals Introduction to Oracle9i: SQL inClassCopyright © Oracle Corporation, 2001. All rights reserved. IntroductionI-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • List the features of Oracle9i • Discuss the theoretical and physical aspects of a relational database • Describe the Oracle implementation of the RDBMS and ORDBMSI-3 Copyright © Oracle Corporation, 2001. All rights reserved. Oracle9i Scalability Reliability Single dev. model Common skill sets One mgmt. interface One vendorI-4 Copyright © Oracle Corporation, 2001. All rights reserved. Oracle9iI-5 Copyright © Oracle Corporation, 2001. All rights reserved. Oracle9i Application Server Business Iintelligence TTrraannssaaccttiioonnaall Aapps Portals APACHE IntegrationI-6 Copyright © Oracle Corporation, 2001. All rights reserved. Oracle9i Database Multimedia Object Relational Data Messages Documents XML I-7 Copyright © Oracle Corporation, 2001. All rights reserved. Oracle9i Database • Performance and availability leader • Richest feature setI-8 Copyright © Oracle Corporation, 2001. All rights reserved. Oracle9i: Object Relational Database Management System • User-defined data types and objects • Fully compatible with relational database • Support of multimedia and large objects • High-quality database server featuresI-9 Copyright © Oracle Corporation, 2001. All rights reserved. Oracle Internet Platform Clients System management Network services Databases Application servers Development tools Internet applications Presentation and business logic Business logic and data Any browser Any FTP client Any mail client JJaavvaa SSQQLL PPLL//SSQQLLI-10 Copyright © Oracle Corporation, 2001. All rights reserved. System Development Life Cycle Strategy and analysis Design Build and document TransitionProductionI-12 Copyright © Oracle Corporation, 2001. All rights reserved. Data Storage on Different Media Electronic spreadsheet Filing cabinet DatabaseI-13 Copyright © Oracle Corporation, 2001. All rights reserved. Relational Database Concept • Dr. E.F. Codd proposed the relational model for database systems in 1970. • It is the basis for the relational database management system. • The relational model consists of the following: – Collection of objects or relations – Set of operators to act on the relations – Data integrity for accuracy and consistencyI-14 Copyright © Oracle Corporation, 2001. All rights reserved. Definition of a Relational Database A relational database is a collection of relations or two-dimensional tables. Oracle server Table Name: EMPLOYEES Table Name: DEPARTMENTSI-15 Copyright © Oracle Corporation, 2001. All rights reserved. Data Models Model of system in client’s mind Entity model of client’s model Table model of entity model Tables on disk Oracle serverI-16 Copyright © Oracle Corporation, 2001. All rights reserved. – – • Create an entity relationship diagram from business specifications or narratives • Scenario – “. . . Assign one or more employees to a department . . .” – “. . . Some departments do not yet have assigned employees . . .” Entity Relationship Model EMPLOYEE #* number * name o job title DEPARTMENT #* number * name o location assigned to composed ofI-17 Copyright © Oracle Corporation, 2001. All rights reserved. Entity Relationship Modeling Conventions Entity Soft box Singular, unique name Uppercase Synonym in parentheses Attribute Singular name Lowercase Mandatory marked with “*” Optional marked with “o” Unique Identifier (UID) Primary marked with “#” Secondary marked with “(#)” EMPLOYEE #* number * name o job title DEPARTMENT #* number * name o location assigned to composed ofI-19 Copyright © Oracle Corporation, 2001. All rights reserved. Relating Multiple Tables • Each row of data in a table is uniquely identified by a primary key (PK). • You can logically relate data from multiple tables using foreign keys (FK). Table Name: EMPLOYEES Table Name: DEPARTMENTS Primary key Foreign key Primary keyI-20 Copyright © Oracle Corporation, 2001. All rights reserved. Relational Database Terminology 1 2 3 4 5 6I-21 Copyright © Oracle Corporation, 2001. All rights reserved. Relational Database Properties A relational database: • Can be accessed and modified by executing structured query language (SQL) statements • Contains a collection of tables with no physical pointers • Uses a set of operatorsI-22 Copyright © Oracle Corporation, 2001. All rights reserved. Communicating with a RDBMS Using SQL SELECT department_name FROM departments; SELECT department_name FROM departments; SQL statement is entered. Oracle server Statement is sent to Oracle Server. Data is displayed.I-23 Copyright © Oracle Corporation, 2001. All rights reserved. Relational Database Management System User tables Data dictionary Oracle serverI-24 Copyright © Oracle Corporation, 2001. All rights reserved. SQL Statements SELECT INSERT UPDATE DELETE MERGE CREATE ALTER DROP RENAME TRUNCATE COMMIT ROLLBACK SAVEPOINT GRANT REVOKE Data retrieval Data manipulation language (DML) Data definition language (DDL) Transaction control Data control language (DCL)I-25 Copyright © Oracle Corporation, 2001. All rights reserved. Tables Used in the Course EMPLOYEESDEPARTMENTS JOB_GRADESI-26 Copyright © Oracle Corporation, 2001. All rights reserved. Summary • The Oracle9i Server is the database for Internet computing. • Oracle9i is based on the object relational database management system. • Relational databases are composed of relations, managed by relational operations, and governed by data integrity constraints. • With the Oracle Server, you can store and manage information by using the SQL language and PL/SQL engine.Copyright © Oracle Corporation, 2001. All rights reserved. Writing Basic SQL SELECT Statements1-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • List the capabilities of SQL SELECT statements • Execute a basic SELECT statement • Differentiate between SQL statements and iSQL*Plus commands1-3 Copyright © Oracle Corporation, 2001. All rights reserved. Capabilities of SQL SELECT Statements Projection Selection Table 1 Table 2 Table 1 Table 1 Join1-4 Copyright © Oracle Corporation, 2001. All rights reserved. Basic SELECT Statement SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; • SELECT identifies what columns • FROM identifies which table1-5 Copyright © Oracle Corporation, 2001. All rights reserved. Selecting All Columns SELECT * FROM departments;1-6 Copyright © Oracle Corporation, 2001. All rights reserved. Selecting Specific Columns SELECT department_id, location_id FROM departments;1-7 Copyright © Oracle Corporation, 2001. All rights reserved. Writing SQL Statements • SQL statements are not case sensitive. • SQL statements can be on one or more lines. • Keywords cannot be abbreviated or split across lines. • Clauses are usually placed on separate lines. • Indents are used to enhance readability.1-8 Copyright © Oracle Corporation, 2001. All rights reserved. Column Heading Defaults • iSQL*Plus: – Default heading justification: Center – Default heading display: Uppercase • SQL*Plus: – Character and Date column headings are leftjustified – Number column headings are right-justified – Default heading display: Uppercase1-9 Copyright © Oracle Corporation, 2001. All rights reserved. Arithmetic Expressions Create expressions with number and date data by using arithmetic operators. Operator +-*/Description Add Subtract Multiply Divide1-10 Copyright © Oracle Corporation, 2001. All rights reserved. Using Arithmetic Operators SELECT last_name, salary, salary + 300 FROM employees;1-11 Copyright © Oracle Corporation, 2001. All rights reserved. Operator Precedence • Multiplication and division take priority over addition and subtraction. • Operators of the same priority are evaluated from left to right. • Parentheses are used to force prioritized evaluation and to clarify statements.1-12 Copyright © Oracle Corporation, 2001. All rights reserved. Operator Precedence ... SELECT last_name, salary, 12*salary+100 FROM employees;1-13 Copyright © Oracle Corporation, 2001. All rights reserved. Using Parentheses SELECT last_name, salary, 12*(salary+100) FROM employees;1-14 Copyright © Oracle Corporation, 2001. All rights reserved. ... Defining a Null Value • A null is a value that is unavailable, unassigned, unknown, or inapplicable. • A null is not the same as zero or a blank space. SELECT last_name, job_id, salary, commission_pct FROM employees;1-15 Copyright © Oracle Corporation, 2001. All rights reserved. Null Values in Arithmetic Expressions Arithmetic expressions containing a null value evaluate to null. SELECT last_name, 12*salary*commission_pct FROM employees;1-16 Copyright © Oracle Corporation, 2001. All rights reserved. Defining a Column Alias A column alias: • Renames a column heading • Is useful with calculations • Immediately follows the column name: there can also be the optional AS keyword between the column name and alias • Requires double quotation marks if it contains spaces or special characters or is case sensitive1-17 Copyright © Oracle Corporation, 2001. All rights reserved. Using Column Aliases SELECT last_name "Name", salary*12 "Annual Salary" FROM employees; SELECT last_name AS name, commission_pct comm FROM employees;1-18 Copyright © Oracle Corporation, 2001. All rights reserved. Concatenation Operator A concatenation operator: • Concatenates columns or character strings to other columns • Is represented by two vertical bars (||) • Creates a resultant column that is a character expression1-19 Copyright © Oracle Corporation, 2001. All rights reserved. Using the Concatenation Operator SELECT last_name||job_id AS "Employees" FROM employees;1-20 Copyright © Oracle Corporation, 2001. All rights reserved. Literal Character Strings • A literal value is a character, a number, or a date included in the SELECT list. • Date and character literal values must be enclosed within single quotation marks. • Each character string is output once for each row returned.1-21 Copyright © Oracle Corporation, 2001. All rights reserved. Using Literal Character Strings SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;1-22 Copyright © Oracle Corporation, 2001. All rights reserved. Duplicate Rows The default display of queries is all rows, including duplicate rows. SELECT department_id FROM employees; SELECT department_id FROM employees;1-23 Copyright © Oracle Corporation, 2001. All rights reserved. Eliminating Duplicate Rows Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause. SELECT DISTINCT department_id FROM employees;1-24 Copyright © Oracle Corporation, 2001. All rights reserved. SQL and iSQL*Plus Interaction SQL statements Oracle server iSQL*Plus Query results commands Client Formatted report Internet Browser iSQL*Plus1-25 Copyright © Oracle Corporation, 2001. All rights reserved. SQL Statements versus iSQL*Plus Commands SQL statements SQL • A language • ANSI standard • Keyword cannot be abbreviated • Statements manipulate data and table definitions in the database iSQL*Plus • An environment • Oracle proprietary • Keywords can be abbreviated • Commands do not allow manipulation of values in the database • Runs on a browser • Centrally loaded, does not have to be implemented on each machine iSQL*Plus commands1-26 Copyright © Oracle Corporation, 2001. All rights reserved. Overview of iSQL*Plus After you log into iSQL*Plus, you can: • Describe the table structure • Edit your SQL statement • Execute SQL from iSQL*Plus • Save SQL statements to files and append SQL statements to files • Execute statements stored in saved files • Load commands from a text file into the iSQL*Plus Edit window1-27 Copyright © Oracle Corporation, 2001. All rights reserved. Logging In to iSQL*Plus From your Windows browser environment: 1-28 Copyright © Oracle Corporation, 2001. All rights reserved. The iSQL*Plus Environment 3 4 5 6 7 1 2 10 8 91-29 Copyright © Oracle Corporation, 2001. All rights reserved. Displaying Table Structure Use the iSQL*Plus DESCRIBE command to display the structure of a table. DDEESSCC[[RRIIBBEE]] ttaabblleennaammee1-30 Copyright © Oracle Corporation, 2001. All rights reserved. Displaying Table Structure DDEESSCCRRIIBBEE eemmppllooyyeeeess1-31 Copyright © Oracle Corporation, 2001. All rights reserved. Interacting with Script Files SELECT last_name, hire_date, salary FROM employees; 1 21-32 Copyright © Oracle Corporation, 2001. All rights reserved. Interacting with Script Files 1 D:\temp\emp_sql.htm 2 SELECT last_name, hire_date, salary FROM employees; 31-33 Copyright © Oracle Corporation, 2001. All rights reserved. Interacting with Script Files DESCRIBE employees SELECT first_name, last_name, job_id FROM employees; 1 3 21-34 Copyright © Oracle Corporation, 2001. All rights reserved. Summary SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; In this lesson, you should have learned how to: • Write a SELECT statement that: – Returns all rows and columns from a table – Returns specified columns from a table – Uses column aliases to give descriptive column headings • Use the iSQL*Plus environment to write, save, and execute SQL statements and iSQL*Plus commands.1-35 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 1 Overview This practice covers the following topics: • Selecting all data from different tables • Describing the structure of tables • Performing arithmetic calculations and specifying column names • Using iSQL*Plus1-40 Copyright © Oracle Corporation, 2001. All rights reserved.Copyright © Oracle Corporation, 2001. All rights reserved. Restricting and Sorting Data2-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Limit the rows retrieved by a query • Sort the rows retrieved by a query2-3 Copyright © Oracle Corporation, 2001. All rights reserved. Limiting Rows Using a Selection “retrieve all employees in department 90” EMPLOYEES2-4 Copyright © Oracle Corporation, 2001. All rights reserved. Limiting the Rows Selected • Restrict the rows returned by using the WHERE clause. • The WHERE clause follows the FROM clause. SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];2-5 Copyright © Oracle Corporation, 2001. All rights reserved. Using the WHERE Clause SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90;2-6 Copyright © Oracle Corporation, 2001. All rights reserved. Character Strings and Dates • Character strings and date values are enclosed in single quotation marks. • Character values are case sensitive, and date values are format sensitive. • The default date format is DD-MON-RR. SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'Goyal';2-7 Copyright © Oracle Corporation, 2001. All rights reserved. Comparison Conditions Operator =>>= <<= <> Meaning Equal to Greater than Greater than or equal to Less than Less than or equal to Not equal to2-8 Copyright © Oracle Corporation, 2001. All rights reserved. SELECT last_name, salary FROM employees WHERE salary <= 3000; Using Comparison Conditions2-9 Copyright © Oracle Corporation, 2001. All rights reserved. Other Comparison Conditions Operator BETWEEN ...AND... IN(set) LIKE IS NULL Meaning Between two values (inclusive) Match any of a list of values Match a character pattern Is a null value 2-10 Copyright © Oracle Corporation, 2001. All rights reserved. Using the BETWEEN Condition Use the BETWEEN condition to display rows based on a range of values. SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; Lower limit Upper limit2-11 Copyright © Oracle Corporation, 2001. All rights reserved. Using the IN Condition Use the IN membership condition to test for values in a list. SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);2-12 Copyright © Oracle Corporation, 2001. All rights reserved. Using the LIKE Condition • Use the LIKE condition to perform wildcard searches of valid search string values. • Search conditions can contain either literal characters or numbers: – % denotes zero or many characters. – _ denotes one character. SELECT first_name FROM employees WHERE first_name LIKE 'S%';2-13 Copyright © Oracle Corporation, 2001. All rights reserved. • You can combine pattern-matching characters. • You can use the ESCAPE identifier to search for the actual % and _ symbols. Using the LIKE Condition SELECT last_name FROM employees WHERE last_name LIKE '_o%';2-14 Copyright © Oracle Corporation, 2001. All rights reserved. Using the NULL Conditions Test for nulls with the IS NULL operator. SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;2-15 Copyright © Oracle Corporation, 2001. All rights reserved. Logical Conditions Operator AND OR NOT Meaning Returns TRUE if both component conditions are true Returns TRUE if either component condition is true Returns TRUE if the following condition is false2-16 Copyright © Oracle Corporation, 2001. All rights reserved. Using the AND Operator AND requires both conditions to be true. SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >=10000 AND job_id LIKE '%MAN%';2-17 Copyright © Oracle Corporation, 2001. All rights reserved. Using the OR Operator OORRrreeqquuiirreess eeiitthheerr ccoonnddiittiioonn ttoo bbee ttrruuee.. SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%';2-18 Copyright © Oracle Corporation, 2001. All rights reserved. Using the NOT Operator SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');2-19 Copyright © Oracle Corporation, 2001. All rights reserved. Rules of Precedence Override rules of precedence by using parentheses. Order Evaluated Operator 1 Arithmetic operators 2 Concatenation operator 3 Comparison conditions 4 IS [NOT] NULL, LIKE, [NOT] IN 5 [NOT] BETWEEN 6 NOT logical condition 7 AND logical condition 8 OR logical condition2-20 Copyright © Oracle Corporation, 2001. All rights reserved. Rules of Precedence SELECT last_name, job_id, salary FROM employees WHERE job_id = 'SA_REP' OR job_id = 'AD_PRES' AND salary > 15000;2-21 Copyright © Oracle Corporation, 2001. All rights reserved. Rules of Precedence UUssee ppaarreenntthheesseess ttoo ffoorrccee pprriioorriittyy.. SELECT last_name, job_id, salary FROM employees WHERE (job_id = 'SA_REP' OR job_id = 'AD_PRES') AND salary > 15000;2-22 Copyright © Oracle Corporation, 2001. All rights reserved. ORDER BY Clause • Sort rows with the ORDER BY clause – ASC: ascending order (the default order) – DESC: descending order • The ORDER BY clause comes last in the SELECT statement. SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date;2-23 Copyright © Oracle Corporation, 2001. All rights reserved. Sorting in Descending Order SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC;2-24 Copyright © Oracle Corporation, 2001. All rights reserved. Sorting by Column Alias SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY annsal;2-25 Copyright © Oracle Corporation, 2001. All rights reserved. • The order of ORDER BY list is the order of sort. • You can sort by a column that is not in the SELECT list. Sorting by Multiple Columns SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;2-26 Copyright © Oracle Corporation, 2001. All rights reserved. Summary SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)] [ORDER BY {column, expr, alias} [ASC|DESC]]; In this lesson, you should have learned how to: • Use the WHERE clause to restrict rows of output – Use the comparison conditions – Use the BETWEEN, IN, LIKE, and NULL conditions – Apply the logical AND, OR, and NOT operators • Use the ORDER BY clause to sort rows of output2-27 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 2 Overview This practice covers the following topics: • Selecting data and changing the order of rows displayed • Restricting rows by using the WHERE clause • Sorting rows by using the ORDER BY clause2-28 Copyright © Oracle Corporation, 2001. All rights reserved.Copyright © Oracle Corporation, 2001. All rights reserved. Single-Row Functions3-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Describe various types of functions available in SQL • Use character, number, and date functions in SELECT statements • Describe the use of conversion functions 3-3 Copyright © Oracle Corporation, 2001. All rights reserved. SQL Functions Function Input arg 1 arg 2arg n Function performs action Output Result value3-4 Copyright © Oracle Corporation, 2001. All rights reserved. Two Types of SQL Functions Functions Single-row functions Multiple-row functions3-5 Copyright © Oracle Corporation, 2001. All rights reserved. Single-Row Functions Single row functions: • Manipulate data items • Accept arguments and return one value • Act on each row returned • Return one result per row • May modify the data type • Can be nested • Accept arguments which can be a column or an expression ffuunnccttiioonn__nnaammee [[((aarrgg11,, aarrgg22,,......))]]3-6 Copyright © Oracle Corporation, 2001. All rights reserved. Single-Row Functions Conversion Character Number Date General Single-row functions3-7 Copyright © Oracle Corporation, 2001. All rights reserved. Character Functions Character functions LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR LPAD | RPAD TRIM REPLACE Case-manipulation functions Character-manipulation functions3-9 Copyright © Oracle Corporation, 2001. All rights reserved. Function Result Case Manipulation Functions These functions convert case for character strings. LOWER('SQL Course') UPPER('SQL Course') INITCAP('SQL Course') sql course SQL COURSE Sql Course3-10 Copyright © Oracle Corporation, 2001. All rights reserved. Using Case Manipulation Functions Display the employee number, name, and department number for employee Higgins: SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'higgins'; no rows selected SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'higgins'; no rows selected SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = 'higgins';3-11 Copyright © Oracle Corporation, 2001. All rights reserved. CONCAT('Hello', 'World') SUBSTR('HelloWorld',1,5) LENGTH('HelloWorld') INSTR('HelloWorld', 'W') LPAD(salary,10,'*') RPAD(salary, 10, '*') TRIM('H' FROM 'HelloWorld') HelloWorld Hello 10 6*****24000 24000***** elloWorld Function Result Character-Manipulation Functions These functions manipulate character strings:3-12 Copyright © Oracle Corporation, 2001. All rights reserved. Using the Character-Manipulation Functions SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?" FROM employees WHERE SUBSTR(job_id, 4) = 'REP';3-13 Copyright © Oracle Corporation, 2001. All rights reserved. Number Functions • ROUND: Rounds value to specified decimal ROUND(45.926, 2) 45.93 • TRUNC: Truncates value to specified decimal TRUNC(45.926, 2) 45.92 • MOD: Returns remainder of division MOD(1600, 300) 1003-14 Copyright © Oracle Corporation, 2001. All rights reserved. Using the ROUND Function DUAL is a dummy table you can use to view results from functions and calculations. SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;3-15 Copyright © Oracle Corporation, 2001. All rights reserved. Using the TRUNC Function SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;3-16 Copyright © Oracle Corporation, 2001. All rights reserved. Using the MOD Function Calculate the remainder of a salary after it is divided by 5000 for all employees whose job title is sales representative. SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = 'SA_REP';3-17 Copyright © Oracle Corporation, 2001. All rights reserved. Working with Dates • Oracle database stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds. • The default date display format is DD-MON-RR. – Allows you to store 21st century dates in the 20th century by specifying only the last two digits of the year. – Allowa you to store 20th century dates in the 21st century in the same way. SELECT last_name, hire_date FROM employees WHERE last_name like 'G%';3-18 Copyright © Oracle Corporation, 2001. All rights reserved. Working with Dates SYSDATE is a function that returns: • Date • Time3-19 Copyright © Oracle Corporation, 2001. All rights reserved. Arithmetic with Dates • Add or subtract a number to or from a date for a resultant date value. • Subtract two dates to find the number of days between those dates. • Add hours to a date by dividing the number of hours by 24.3-20 Copyright © Oracle Corporation, 2001. All rights reserved. Using Arithmetic Operators with Dates SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90;3-21 Copyright © Oracle Corporation, 2001. All rights reserved. Date Functions Number of months between two dates MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY ROUND TRUNC Add calendar months to date Next day of the date specified Last day of the month Round date Truncate date Function Description3-22 Copyright © Oracle Corporation, 2001. All rights reserved. • MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') Using Date Functions • ADD_MONTHS ('11-JAN-94',6) • NEXT_DAY ('01-SEP-95','FRIDAY') • LAST_DAY('01-FEB-95') 19.6774194 '11-JUL-94' '08-SEP-95' '28-FEB-95'3-23 Copyright © Oracle Corporation, 2001. All rights reserved. • ROUND(SYSDATE,'MONTH') 01-AUG-95 • ROUND(SYSDATE ,'YEAR') 01-JAN-96 • TRUNC(SYSDATE ,'MONTH') 01-JUL-95 • TRUNC(SYSDATE ,'YEAR') 01-JAN-95 Using Date Functions Assume SYSDATE = '25-JUL-95':3-24 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 3, Part 1 Overview This practice covers the following topics: • Writing a query that displays the current date • Creating queries that require the use of numeric, character, and date functions • Performing calculations of years and months of service for an employee3-25 Copyright © Oracle Corporation, 2001. All rights reserved. Conversion Functions Implicit data-type conversion Explicit data-type conversion Data-type conversion3-26 Copyright © Oracle Corporation, 2001. All rights reserved. Implicit Data-Type Conversion For assignments, the Oracle server can automatically convert the following: VARCHAR2 or CHAR From To VARCHAR2 or CHAR NUMBER DATE NUMBER DATE VARCHAR2 VARCHAR23-27 Copyright © Oracle Corporation, 2001. All rights reserved. Implicit Data-Type Conversion For expression evaluation, the Oracle Server can automatically convert the following: VARCHAR2 or CHAR From To VARCHAR2 or CHAR NUMBER DATE3-28 Copyright © Oracle Corporation, 2001. All rights reserved. Explicit Data-Type Conversion NUMBER CHARACTER TO_CHAR TO_NUMBER DATE TO_CHAR TO_DATE3-29 Copyright © Oracle Corporation, 2001. All rights reserved. Explicit Data-Type Conversion NUMBER CHARACTER TO_CHAR TO_NUMBER DATE TO_CHAR TO_DATE3-31 Copyright © Oracle Corporation, 2001. All rights reserved. Using the TO_CHAR Function with Dates The format model: • Must be enclosed in single quotation marks and is case sensitive • Can include any valid date format element • Has an fm element to remove padded blanks or suppress leading zeros • Is separated from the date value by a comma TTOO__CCHHAARR((ddaattee,, ''ffoorrmmaatt__mmooddeell''))3-32 Copyright © Oracle Corporation, 2001. All rights reserved. YYYYElements of the Date Format Model YEAR MM MONTH DY DAY Full year in numbers Year spelled out Two-digit value for month Three-letter abbreviation of the day of the week Full name of the day of the week Full name of the month MON Three-letter abbreviation of the month DD Numeric day of the month3-34 Copyright © Oracle Corporation, 2001. All rights reserved. Elements of the Date Format Model • Time elements format the time portion of the date. • Add character strings by enclosing them in double quotation marks. • Number suffixes spell out numbers. HH24:MI:SS AM 15:45:32 PM DD "of" MONTH 12 of OCTOBER ddspth fourteenth3-36 Copyright © Oracle Corporation, 2001. All rights reserved. Using the TO_CHAR Function with Dates SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') HIREDATE FROM employees;3-37 Copyright © Oracle Corporation, 2001. All rights reserved. Using the TO_CHAR Function with Numbers These are some of the format elements you can use with the TO_CHAR function to display a number value as a character: TTOO__CCHHAARR((nnuummbbeerr,, ''ffoorrmmaatt__mmooddeell'')) 90$L., Represents a number Forces a zero to be displayed Places a floating dollar sign Uses the floating local currency symbol Prints a decimal point Prints a thousand indicator3-38 Copyright © Oracle Corporation, 2001. All rights reserved. Using the TO_CHAR Function with Numbers SELECT TO_CHAR(salary, '$99,999.00') SALARY FROM employees WHERE last_name = 'Ernst';3-39 Copyright © Oracle Corporation, 2001. All rights reserved. Using the TO_NUMBER and TO_DATE Functions • Convert a character string to a number format using the TO_NUMBER function: • Convert a character string to a date format using the TO_DATE function: • These functions have an fx modifier. This modifier specifies the exact matching for the character argument and date format model of a TO_DATE function. TTOO__NNUUMMBBEERR((cchhaarr[[,, ''ffoorrmmaatt__mmooddeell'']])) TTOO__DDAATTEE((cchhaarr[[,, ''ffoorrmmaatt__mmooddeell'']]))3-40 Copyright © Oracle Corporation, 2001. All rights reserved. RR Date Format Current Year 1995 1995 2001 2001 Specified Date 27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95 RR Format 1995 2017 2017 1995 YY Format 1995 1917 2017 2095 If two digits of the current year are: 0–49 0–49 50–99 50–99 The return date is in the current century The return date is in the century after the current one The return date is in the century before the current one The return date is in the current century If the specified two-digit year is:3-41 Copyright © Oracle Corporation, 2001. All rights reserved. Example of RR Date Format To find employees hired prior to 1990, use the RR format, which produces the same results whether the command is run in 1999 or now: SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY') FROM employees WHERE hire_date < TO_DATE('01-Jan-90', 'DD-Mon-RR');3-42 Copyright © Oracle Corporation, 2001. All rights reserved. Nesting Functions • Single-row functions can be nested to any level. • Nested functions are evaluated from deepest level to the least deep level. F3(F2(F1(col,arg1),arg2),arg3) Step 1 = Result 1 Step 2 = Result 2 Step 3 = Result 33-43 Copyright © Oracle Corporation, 2001. All rights reserved. Nesting Functions SELECT last_name, NVL(TO_CHAR(manager_id), 'No Manager') FROM employees WHERE manager_id IS NULL;3-44 Copyright © Oracle Corporation, 2001. All rights reserved. General Functions These functions work with any data type and pertain to using null value. • NVL (expr1, expr2) • NVL2 (expr1, expr2, expr3) • NULLIF (expr1, expr2) • COALESCE (expr1, expr2, ..., exprn)3-45 Copyright © Oracle Corporation, 2001. All rights reserved. NVL Function • Converts a null to an actual value • Data types that can be used are date, character, and number. • Data types must match: – NVL(commission_pct,0) – NVL(hire_date,'01-JAN-97') – NVL(job_id,'No Job Yet')3-46 Copyright © Oracle Corporation, 2001. All rights reserved. Using the NVL Function SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL FROM employees;3-47 Copyright © Oracle Corporation, 2001. All rights reserved. SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL') income FROM employees WHERE department_id IN (50, 80); Using the NVL2 Function3-48 Copyright © Oracle Corporation, 2001. All rights reserved. Using the NULLIF Function SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees;3-49 Copyright © Oracle Corporation, 2001. All rights reserved. Using the COALESCE Function • The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values. • If the first expression is not null, it returns that expression; otherwise, it does a COALESCE of the remaining expressions.3-50 Copyright © Oracle Corporation, 2001. All rights reserved. SELECT last_name, COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct; Using the COALESCE Function3-51 Copyright © Oracle Corporation, 2001. All rights reserved. Conditional Expressions • Give you the use of IF-THEN-ELSE logic within a SQL statement • Use two methods: – CASE expression – DECODE function3-52 Copyright © Oracle Corporation, 2001. All rights reserved. The CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END3-53 Copyright © Oracle Corporation, 2001. All rights reserved. Using the CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees;3-54 Copyright © Oracle Corporation, 2001. All rights reserved. The DECODE Function Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement: DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default])3-55 Copyright © Oracle Corporation, 2001. All rights reserved. Using the DECODE Function SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP', 1.20*salary, salary) REVISED_SALARY FROM employees;3-56 Copyright © Oracle Corporation, 2001. All rights reserved. Using the DECODE Function SELECT last_name, salary, DECODE (TRUNC(salary/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATE FROM employees WHERE department_id = 80; Display the applicable tax rate for each employee in department 80.3-57 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned how to: • Perform calculations on data using functions • Modify individual data items using functions • Manipulate output for groups of rows using functions • Alter date formats for display using functions • Convert column data types using functions • Use NVL functions • Use IF-THEN-ELSE logic3-58 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 3, Part 2 Overview This practice covers the following topics: • Creating queries that require the use of numeric, character, and date functions • Using concatenation with functions • Writing case-insensitive queries to test the usefulness of character functions • Performing calculations of years and months of service for an employee • Determining the review date for an employee3-59 Copyright © Oracle Corporation, 2001. All rights reserved.3-61 Copyright © Oracle Corporation, 2001. All rights reserved.3-66 Copyright © Oracle Corporation, 2001. All rights reserved.Copyright © Oracle Corporation, 2001. All rights reserved. Displaying Data from Multiple Tables4-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Write SELECT statements to access data from more than one table using equality and nonequality joins • View data that generally does not meet a join condition by using outer joins • Join a table to itself by using a self join4-3 Copyright © Oracle Corporation, 2001. All rights reserved. Obtaining Data from Multiple Tables EMPLOYEES DEPARTMENTS 4-4 Copyright © Oracle Corporation, 2001. All rights reserved. Cartesian Products • A Cartesian product is formed when: – A join condition is omitted – A join condition is invalid – All rows in the first table are joined to all rows in the second table • To avoid a Cartesian product, always include a valid join condition in a WHERE clause.4-5 Copyright © Oracle Corporation, 2001. All rights reserved. Generating a Cartesian Product Cartesian product: 20x8=160 rows EMPLOYEES (20 rows) DEPARTMENTS (8 rows)4-6 Copyright © Oracle Corporation, 2001. All rights reserved. • Equijoin • Nonequijoin • Outer join • Self join Types of Joins • Cross joins • Natural joins • Using clause • Full or two sided outer joins • Arbitrary join conditions for outer joins SQL: 1999 Compliant Joins: Oracle Proprietary Joins (8i and prior): 4-7 Copyright © Oracle Corporation, 2001. All rights reserved. Joining Tables Using Oracle Syntax Use a join to query data from more than one table. • Write the join condition in the WHERE clause. • Prefix the column name with the table name when the same column name appears in more than one table. SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;4-8 Copyright © Oracle Corporation, 2001. All rights reserved. What Is an Equijoin? EMPLOYEES DEPARTMENTS Foreign key Primary key4-9 Copyright © Oracle Corporation, 2001. All rights reserved. Retrieving Records with Equijoins SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id;4-10 Copyright © Oracle Corporation, 2001. All rights reserved. Additional Search Conditions Using the AND Operator EMPLOYEES DEPARTMENTS4-11 Copyright © Oracle Corporation, 2001. All rights reserved. Qualifying Ambiguous Column Names • Use table prefixes to qualify column names that are in multiple tables. • Improve performance by using table prefixes. • Distinguish columns that have identical names but reside in different tables by using column aliases.4-12 Copyright © Oracle Corporation, 2001. All rights reserved. Using Table Aliases • Simplify queries by using table aliases • Improve performance by using table prefixes SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e, departments d WHERE e.department_id = d.department_id;4-13 Copyright © Oracle Corporation, 2001. All rights reserved. Joining More than Two Tables EMPLOYEES DEPARTMENTS LOCATIONS To join n tables together, you need a minimum of n-1 join conditions. For example, to join three tables, a minimum of two joins is required.4-14 Copyright © Oracle Corporation, 2001. All rights reserved. Nonequijoins EMPLOYEES JOB_GRADES Salary in the EMPLOYEES table must be between lowest salary and highest salary in the JOB_GRADES table.4-15 Copyright © Oracle Corporation, 2001. All rights reserved. Retrieving Records with Nonequijoins SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;4-16 Copyright © Oracle Corporation, 2001. All rights reserved. Outer Joins DEPARTMENTS EMPLOYEES There are no employees in department 190. 4-17 Copyright © Oracle Corporation, 2001. All rights reserved. Outer Joins Syntax • You use an outer join to also see rows that do not meet the join condition. • The outer join operator is the plus sign (+). SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column; SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column; SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column(+); SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column(+);4-18 Copyright © Oracle Corporation, 2001. All rights reserved. Using Outer Joins SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id;4-19 Copyright © Oracle Corporation, 2001. All rights reserved. Self Joins EMPLOYEES (WORKER) EMPLOYEES (MANAGER) MANAGER_ID in the WORKER table is equal to EMPLOYEE_ID in the MANAGER table.4-20 Copyright © Oracle Corporation, 2001. All rights reserved. Joining a Table to Itself SELECT worker.last_name || ' works for ' || manager.last_name FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id;4-21 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 4, Part 1 Overview This practice covers writing queries to join tables together using Oracle syntax.4-22 Copyright © Oracle Corporation, 2001. All rights reserved. Joining Tables Using SQL: 1999 Syntax Use a join to query data from more than one table. SELECT table1.column, table2.column FROM table1 [CROSS JOIN table2] | [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON(table1.column_name = table2.column_name)] | [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]; SELECT table1.column, table2.column FROM table1 [CROSS JOIN table2] | [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON(table1.column_name = table2.column_name)] | [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)];4-23 Copyright © Oracle Corporation, 2001. All rights reserved. Creating Cross Joins • The CROSS JOIN clause produces the crossproduct of two tables. • This is the same as a Cartesian product between the two tables. SELECT last_name, department_name FROM employees CROSS JOIN departments;4-24 Copyright © Oracle Corporation, 2001. All rights reserved. Creating Natural Joins • The NATURAL JOIN clause is based on all columns in the two tables that have the same name. • It selects rows from the two tables that have equal values in all matched columns. • If the columns having the same names have different data types, then an error is returned.4-25 Copyright © Oracle Corporation, 2001. All rights reserved. Retrieving Records with Natural Joins SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations;4-26 Copyright © Oracle Corporation, 2001. All rights reserved. Creating Joins with the USING Clause • If several columns have the same names but the data types do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equijoin. Note: Use the USING clause to match only one column when more than one column matches. • Do not use a table name or alias in the referenced columns. • The NATURAL JOIN and USING clauses are mutually exclusive.4-27 Copyright © Oracle Corporation, 2001. All rights reserved. Retrieving Records with the USING Clause SELECT e.employee_id, e.last_name, d.location_id FROM employees e JOIN departments d USING (department_id);4-28 Copyright © Oracle Corporation, 2001. All rights reserved. Creating Joins with the ON Clause • The join condition for the natural join is basically an equijoin of all columns with the same name. • To specify arbitrary conditions or specify columns to join, the ON clause is used. • Separates the join condition from other search conditions. • The ON clause makes code easy to understand.4-29 Copyright © Oracle Corporation, 2001. All rights reserved. Retrieving Records with the ON Clause SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id);4-30 Copyright © Oracle Corporation, 2001. All rights reserved. Creating Three-Way Joins with the ON Clause SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id = l.location_id;4-31 Copyright © Oracle Corporation, 2001. All rights reserved. INNER versus OUTER Joins • In SQL: 1999, the join of two tables returning only matched rows is an inner join. • A join between two tables that returns the results of the inner join as well as unmatched rows left (or right) tables is a left (or right) outer join. • A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join.4-32 Copyright © Oracle Corporation, 2001. All rights reserved. LEFT OUTER JOIN SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);4-33 Copyright © Oracle Corporation, 2001. All rights reserved. RIGHT OUTER JOIN SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);4-34 Copyright © Oracle Corporation, 2001. All rights reserved. FULL OUTER JOIN SELECT e.last_name, e.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id);4-35 Copyright © Oracle Corporation, 2001. All rights reserved. Additional Conditions SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149;4-36 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned how to use joins to display data from multiple tables in: • Oracle proprietary syntax for versions 8i and earlier • SQL: 1999 compliant syntax for version 9i4-37 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 4, Part 2 Overview This practice covers the following topics: • Joining tables using an equijoin • Performing outer and self joins • Adding conditions4-40 Copyright © Oracle Corporation, 2001. All rights reserved.Copyright © Oracle Corporation, 2001. All rights reserved. Aggregating Data Using Group Functions5-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Identify the available group functions • Describe the use of group functions • Group data using the GROUP BY clause • Include or exclude grouped rows by using the HAVING clause5-3 Copyright © Oracle Corporation, 2001. All rights reserved. What Are Group Functions? Group functions operate on sets of rows to give one result per group. EMPLOYEES The maximum salary in the EMPLOYEES table.5-4 Copyright © Oracle Corporation, 2001. All rights reserved. Types of Group Functions • AVG • COUNT • MAX • MIN • STDDEV • SUM • VARIANCE5-5 Copyright © Oracle Corporation, 2001. All rights reserved. Group Functions Syntax SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];5-6 Copyright © Oracle Corporation, 2001. All rights reserved. Using the AVG and SUM Functions You can use AVG and SUM for numeric data. SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';5-7 Copyright © Oracle Corporation, 2001. All rights reserved. Using the MIN and MAX Functions You can use MIN and MAX for any data type. SELECT MIN(hire_date), MAX(hire_date) FROM employees;5-8 Copyright © Oracle Corporation, 2001. All rights reserved. Using the COUNT Function SELECT COUNT(*) FROM employees WHERE department_id = 50; COUNT(*) returns the number of rows in a table.5-9 Copyright © Oracle Corporation, 2001. All rights reserved. Using the COUNT Function • COUNT(expr) returns the number of rows with non-null values for the expr. • Display the number of department values in the EMPLOYEES table, excluding the null values. SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;5-10 Copyright © Oracle Corporation, 2001. All rights reserved. Using the DISTINCT Keyword • COUNT(DISTINCT expr) returns the number of distinct nonnull values of the expr. • Display the number of distinct department values in the EMPLOYEES table. SELECT COUNT(DISTINCT department_id) FROM employees;5-11 Copyright © Oracle Corporation, 2001. All rights reserved. Group Functions and Null Values Group functions ignore null values in the column. SELECT AVG(commission_pct) FROM employees;5-12 Copyright © Oracle Corporation, 2001. All rights reserved. SELECT AVG(NVL(commission_pct, 0)) FROM employees; Using the NVL Function with Group Functions The NVL function forces group functions to include null values.5-13 Copyright © Oracle Corporation, 2001. All rights reserved. Creating Groups of Data EMPLOYEES The average salary in EMPLOYEES table for each department. 4400 9500 3500 64005-14 Copyright © Oracle Corporation, 2001. All rights reserved. Creating Groups of Data: GROUP BY Clause Syntax SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; Divide rows in a table into smaller groups by using the GROUP BY clause.5-15 Copyright © Oracle Corporation, 2001. All rights reserved. Using the GROUP BY Clause All columns in the SELECT list that are not in group functions must be in the GROUP BY clause. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;5-16 Copyright © Oracle Corporation, 2001. All rights reserved. Using the GROUP BY Clause The GROUP BY column does not have to be in the SELECT list. SELECT AVG(salary) FROM employees GROUP BY department_id;5-17 Copyright © Oracle Corporation, 2001. All rights reserved. Grouping by More Than One Column EMPLOYEES Add up the salaries in the EMPLOYEES table for each job, grouped by department.5-18 Copyright © Oracle Corporation, 2001. All rights reserved. Using the GROUP BY Clause on Multiple Columns SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id;5-19 Copyright © Oracle Corporation, 2001. All rights reserved. Illegal Queries Using Group Functions Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause. SELECT department_id, COUNT(last_name) FROM employees; SELECT department_id, COUNT(last_name) FROM employees; SELECT department_id, COUNT(last_name) * ERROR at line 1: ORA-00937: not a single-group group function SELECT department_id, COUNT(last_name) * ERROR at line 1: ORA-00937: not a single-group group function Column missing in the GROUP BY clause 5-20 Copyright © Oracle Corporation, 2001. All rights reserved. Illegal Queries Using Group Functions • You cannot use the WHERE clause to restrict groups. • You use the HAVING clause to restrict groups. • You cannot use group functions in the WHERE clause. SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; WHERE AVG(salary) > 8000 * ERROR at line 3: ORA-00934: group function is not allowed here WHERE AVG(salary) > 8000 * ERROR at line 3: ORA-00934: group function is not allowed here Cannot use the WHERE clause to restrict groups 5-21 Copyright © Oracle Corporation, 2001. All rights reserved. Excluding Group Results The maximum salary per department when it is greater than $10,000. EMPLOYEES5-22 Copyright © Oracle Corporation, 2001. All rights reserved. Excluding Group Results: The HAVING Clause Use the HAVING clause to restrict groups: 1. Rows are grouped. 2. The group function is applied. 3. Groups matching the HAVING clause are displayed. SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];5-23 Copyright © Oracle Corporation, 2001. All rights reserved. Using the HAVING Clause SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000;5-24 Copyright © Oracle Corporation, 2001. All rights reserved. Using the HAVING Clause SELECT job_id, SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE '%REP%' GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY SUM(salary);5-25 Copyright © Oracle Corporation, 2001. All rights reserved. Nesting Group Functions Display the maximum average salary. SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;5-26 Copyright © Oracle Corporation, 2001. All rights reserved. Summary SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; In this lesson, you should have learned how to: • Use the group functions COUNT, MAX, MIN, AVG • Write queries that use the GROUP BY clause • Write queries that use the HAVING clause 5-27 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 5 Overview This practice covers the following topics: • Writing queries that use the group functions • Grouping by rows to achieve more than one result • Excluding groups by using the HAVING clauseCopyright © Oracle Corporation, 2001. All rights reserved. Subqueries6-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Describe the types of problem that subqueries can solve • Define subqueries • List the types of subqueries • Write single-row and multiple-row subqueries6-3 Copyright © Oracle Corporation, 2001. All rights reserved. Using a Subquery to Solve a Problem Who has a salary greater than Abel’s? Which employees have salaries greater than Abel’s salary? Main Query: ? What is Abel’s salary? ? Subquery:6-4 Copyright © Oracle Corporation, 2001. All rights reserved. Subquery Syntax • The subquery (inner query) executes once before the main query. • The result of the subquery is used by the main query (outer query). SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);6-5 Copyright © Oracle Corporation, 2001. All rights reserved. Using a Subquery 11000 SELECT last_name FROM employees WHERE salary >(SELECT salary FROM employees WHERE last_name = 'Abel');6-6 Copyright © Oracle Corporation, 2001. All rights reserved. Guidelines for Using Subqueries • Enclose subqueries in parentheses. • Place subqueries on the right side of the comparison condition. • The ORDER BY clause in the subquery is not needed unless you are performing top-n analysis. • Use single-row operators with single-row subqueries and use multiple-row operators with multiple-row subqueries.6-7 Copyright © Oracle Corporation, 2001. All rights reserved. Types of Subqueries • Single-row subquery Main query Subquery returns ST_CLERK • Multiple-row subquery ST_CLERK SA_MAN Main query Subquery returns6-8 Copyright © Oracle Corporation, 2001. All rights reserved. Single-Row Subqueries • Return only one row • Use single-row comparison operators Operator =>>= <<= <> Meaning Equal to Greater than Greater than or equal to Less than Less than or equal to Not equal to6-9 Copyright © Oracle Corporation, 2001. All rights reserved. Executing Single-Row Subqueries ST_CLERK 2600 SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE employee_id = 143);6-10 Copyright © Oracle Corporation, 2001. All rights reserved. Using Group Functions in a Subquery 2500 SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);6-11 Copyright © Oracle Corporation, 2001. All rights reserved. The HAVING Clause with Subqueries • The Oracle Server executes subqueries first. • The Oracle Server returns results into the HAVING clause of the main query. 2500 SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);6-12 Copyright © Oracle Corporation, 2001. All rights reserved. What Is Wrong with This Statement? ERROR at line 4: ORA-01427: single-row subquery returns more than one row ERROR at line 4: ORA-01427: single-row subquery returns more than one row SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id); Single -row operator with multiple -row subquery 6-13 Copyright © Oracle Corporation, 2001. All rights reserved. Will This Statement Return Rows? nnoo rroowwss sseelleecctteedd SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas'); Subquery returns no values 6-14 Copyright © Oracle Corporation, 2001. All rights reserved. Multiple-Row Subqueries • Return more than one row • Use multiple-row comparison operators Operator IN ANY ALL Meaning Equal to any member in the list Compare value to each value returned by the subquery Compare value to every value returned by the subquery 6-15 Copyright © Oracle Corporation, 2001. All rights reserved. Using the ANY Operator in Multiple-Row Subqueries 9000, 6000, 4200 SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';6-16 Copyright © Oracle Corporation, 2001. All rights reserved. Using the ALL Operator in Multiple-Row Subqueries 9000, 6000, 4200 SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';6-17 Copyright © Oracle Corporation, 2001. All rights reserved. Null Values in a Subquery SELECT emp.last_name FROM employees emp WHERE emp.employee_id NOT IN(SELECT mgr.manager_id FROM employees mgr); no rows selected6-18 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned how to: • Identify when a subquery can help solve a question • Write subqueries when a query is based on unknown values SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);6-19 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 6 Overview This practice covers the following topics: • Creating subqueries to query values based on unknown criteria • Using subqueries to find out which values exist in one set of data and not in anotherCopyright © Oracle Corporation, 2001. All rights reserved. Producing Readable Output with iSQL*Plus7-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Produce queries that require a substitution variable • Customize the iSQL*Plus environment • Produce more readable output • Create and execute script files7-3 Copyright © Oracle Corporation, 2001. All rights reserved. Substitution Variables I want to query ... salary = ? … different values. … department_id = ? … ... last_name = ? ... User7-4 Copyright © Oracle Corporation, 2001. All rights reserved. Substitution Variables Use iSQL*Plus substitution variables to: • Store values temporarily – Single ampersand (&) – Double ampersand (&&) – DEFINE command • Pass variable values between SQL statements • Dynamically alter headers and footers7-5 Copyright © Oracle Corporation, 2001. All rights reserved. UUssiinngg tthhee && SSuubbssttiittuuttiioonn VVaarriiaabbllee Use a variable prefixed with an ampersand (&) to prompt the user for a value. SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num;7-6 Copyright © Oracle Corporation, 2001. All rights reserved. UUssiinngg tthhee && SSuubbssttiittuuttiioonn VVaarriiaabbllee 101 1 27-7 Copyright © Oracle Corporation, 2001. All rights reserved. Character and Date Values with Substitution Variables Use single quotation marks for date and character values. SELECT last_name, department_id, salary*12 FROM employees WHERE job_id = '&job_title';7-8 Copyright © Oracle Corporation, 2001. All rights reserved. Specifying Column Names, Expressions, and Text Use substitution variables to supplement the following: • WHERE conditions • ORDER BY clauses • Column expressions • Table names • Entire SELECT statements7-9 Copyright © Oracle Corporation, 2001. All rights reserved. Specifying Column Names, Expressions, and Text SELECT employee_id, last_name, job_id, &column_name FROM employees WHERE &condition ORDER BY &order_column;7-10 Copyright © Oracle Corporation, 2001. All rights reserved. Defining Substitution Variables • You can predefine variables using the iSQL*Plus DEFINE command. DEFINE variable = value creates a user variable with the CHAR data type. • If you need to predefine a variable that includes spaces, you must enclose the value within single quotation marks when using the DEFINE command. • A defined variable is available for the session7-11 Copyright © Oracle Corporation, 2001. All rights reserved. DEFINE and UNDEFINE Commands • A variable remains defined until you either: – Use the UNDEFINE command to clear it – Exit iSQL*Plus • You can verify your changes with the DEFINE command. DEFINE job_title = IT_PROG DEFINE job_title DEFINE JOB_TITLE = "IT_PROG" (CHAR) UNDEFINE job_title DEFINE job_title SP2-0135: symbol job_title is UNDEFINED7-12 Copyright © Oracle Corporation, 2001. All rights reserved. Using the DEFINE Command with & Substitution Variable • Create the substitution variable using the DEFINE command. • Use a variable prefixed with an ampersand (&) to substitute the value in the SQL statement. DEFINE employee_num = 200 SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num;7-13 Copyright © Oracle Corporation, 2001. All rights reserved. Using the && Substitution Variable Use the double-ampersand (&&) if you want to reuse the variable value without prompting the user each time. SELECT employee_id, last_name, job_id, &&column_name FROM employees ORDER BY &column_name;7-14 Copyright © Oracle Corporation, 2001. All rights reserved. Using the VERIFY Command Use the VERIFY command to toggle the display of the substitution variable, before and after iSQL*Plus replaces substitution variables with values. old 3: WHERE employee_id = &employee_num new 3: WHERE employee_id = 200 SET VERIFY ON SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num;7-15 Copyright © Oracle Corporation, 2001. All rights reserved. Customizing the iSQL*Plus Environment • Use SET commands to control current session. • Verify what you have set by using the SHOW command. SSEETT EECCHHOO OONN SHOW ECHO echo ON SHOW ECHO echo ON SSEETT ssyysstteemm__vvaarriiaabbllee vvaalluuee7-16 Copyright © Oracle Corporation, 2001. All rights reserved. SET Command Variables • ARRAYSIZE {20 | n} • FEEDBACK {6 | n |OFF | ON} • HEADING {OFF | ON} • LONG {80 | n}| ON | text} SSEETT HHEEAADDIINNGG OOFFFF SHOW HEADING HEADING OFF SHOW HEADING HEADING OFF7-17 Copyright © Oracle Corporation, 2001. All rights reserved. iSQL*Plus Format Commands •COLUMN [column option] •TTITLE [text | OFF | ON] •BTITLE [text | OFF | ON] •BREAK [ON report_element]7-18 Copyright © Oracle Corporation, 2001. All rights reserved. The COLUMN Command Controls display of a column: • CLE[AR]: Clears any column formats • HEA[DING] text: Sets the column heading • FOR[MAT] format: Changes the display of the column using a format model • NOPRINT | PRINT • NULL CCOOLL[[UUMMNN]] [[{{ccoolluummnn||aalliiaass}} [[ooppttiioonn]]]]7-19 Copyright © Oracle Corporation, 2001. All rights reserved. Using the COLUMN Command • Create column headings. COLUMN last_name HEADING 'Employee|Name' COLUMN salary JUSTIFY LEFT FORMAT $99,990.00 COLUMN manager FORMAT 999999999 NULL 'No manager' COLUMN last_name HEADING 'Employee|Name' COLUMN salary JUSTIFY LEFT FORMAT $99,990.00 COLUMN manager FORMAT 999999999 NULL 'No manager' CCOOLLUUMMNN llaasstt__nnaammee CCOOLLUUMMNN llaasstt__nnaammee CCLLEEAARR • Display the current setting for the LAST_NAME column. • Clear settings for the LAST_NAME column.7-20 Copyright © Oracle Corporation, 2001. All rights reserved. COLUMN Format Models Result 1234 001234 $1234 L1234 1234.00 1,234 Example 999999 099999 $9999 L9999 9999.99 9,999 Element 90$L., Description Single zero-suppression digit Enforces leading zero Floating dollar sign Local currency Position of decimal point Thousand separator7-21 Copyright © Oracle Corporation, 2001. All rights reserved. Using the BREAK Command Use the BREAK command to suppress duplicates BBRREEAAKK OONN jjoobb__iidd7-22 Copyright © Oracle Corporation, 2001. All rights reserved. Using the TTITLE and BTITLE Commands • Display headers and footers. • Set the report header. • Set the report footer. TTTTII[[TTLLEE]] [[tteexxtt||OOFFFF||OONN]] TTTTIITTLLEE ''SSaallaarryy||RReeppoorrtt'' BBTTIITTLLEE ''CCoonnffiiddeennttiiaall''7-23 Copyright © Oracle Corporation, 2001. All rights reserved. Creating a Script File to Run a Report 1. Create and test the SQL SELECT statement. 2. Save the SELECT statement into a script file. 3. Load the script file into an editor. 4. Add formatting commands before the SELECT statement. 5. Verify that the termination character follows the SELECT statement.7-24 Copyright © Oracle Corporation, 2001. All rights reserved. Creating a Script File to Run a Report 6. Clear formatting commands after the SELECT statement. 7. Save the script file. 8. Load the script file into the iSQL*Plus text window, and click the Execute button.7-25 Copyright © Oracle Corporation, 2001. All rights reserved. Sample Report7-26 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned how to: • Use iSQL*Plus substitution variables to store values temporarily • Use SET commands to control the current iSQL*Plus environment • Use the COLUMN command to control the display of a column • Use the BREAK command to suppress duplicates and divide rows into sections • Use the TTITLE and BTITLE commands to display headers and footers7-27 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 7 Overview This practice covers the following topics: • Creating a query to display values using substitution variables • Starting a command file containing variablesCopyright © Oracle Corporation, 2001. All rights reserved. Manipulating Data8-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Describe each DML statement • Insert rows into a table • Update rows in a table • Delete rows from a table • Merge rows in a table • Control transactions8-3 Copyright © Oracle Corporation, 2001. All rights reserved. Data Manipulation Language • A DML statement is executed when you: – Add new rows to a table – Modify existing rows in a table – Remove existing rows from a table • A transaction consists of a collection of DML statements that form a logical unit of work.8-4 Copyright © Oracle Corporation, 2001. All rights reserved. Adding a New Row to a Table DEPARTMENTS New row Insert a new row into the DEPARMENTS table.8-5 Copyright © Oracle Corporation, 2001. All rights reserved. The INSERT Statement Syntax • Add new rows to a table by using the INSERT statement. • Only one row is inserted at a time with this syntax. INSERT INTO table [(column [, column...])] VALUES (value [, value...]); INSERT INTO table [(column [, column...])] VALUES (value [, value...]);8-6 Copyright © Oracle Corporation, 2001. All rights reserved. Inserting New Rows • Insert a new row containing values for each column. • List values in the default order of the columns in the table. • Optionally, list the columns in the INSERT clause. • Enclose character and date values within single quotation marks. INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (70, 'Public Relations', 100, 1700); 1 row created.8-7 Copyright © Oracle Corporation, 2001. All rights reserved. Inserting Rows with Null Values • Implicit method: Omit the column from the column list. INSERT INTO departments VALUES (100, 'Finance', NULL, NULL); 1 row created. INSERT INTO departments (department_id, department_name ) VALUES (30, 'Purchasing'); 1 row created. • Explicit method: Specify the NULL keyword in the VALUES clause.8-8 Copyright © Oracle Corporation, 2001. All rights reserved. Inserting Special Values The SYSDATE function records the current date and time. INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (113, 'Louis', 'Popp', 'LPOPP', '515.124.4567', SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205, 100); 1 row created.8-9 Copyright © Oracle Corporation, 2001. All rights reserved. Inserting Specific Date Values • Add a new employee. • Verify your addition. INSERT INTO employees VALUES (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561', TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'AC_ACCOUNT', 11000, NULL, 100, 30); 1 row created.8-10 Copyright © Oracle Corporation, 2001. All rights reserved. Creating a Script • Use & substitution in a SQL statement to prompt for values. • & is a placeholder for the variable value. 40 Human Resources 2500 1 row created. INSERT INTO departments (department_id, department_name, location_id) VALUES (&department_id, '&department_name',&location);8-11 Copyright © Oracle Corporation, 2001. All rights reserved. Copying Rows from Another Table • Write your INSERT statement with a subquery. • Do not use the VALUES clause. • Match the number of columns in the INSERT clause to those in the subquery. INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%'; 4 rows created.8-12 Copyright © Oracle Corporation, 2001. All rights reserved. Changing Data in a Table EMPLOYEES Update rows in the EMPLOYEES table. 30 30 308-13 Copyright © Oracle Corporation, 2001. All rights reserved. The UPDATE Statement Syntax • Modify existing rows with the UPDATE statement. • Update more than one row at a time, if required. UPDATE table SET column = value [, column = value, ...] [WHERE condition]; UPDATE table SET column = value [, column = value, ...] [WHERE condition];8-14 Copyright © Oracle Corporation, 2001. All rights reserved. • Specific row or rows are modified if you specify the WHERE clause. • All rows in the table are modified if you omit the WHERE clause. Updating Rows in a Table UPDATE copy_emp SET department_id = 110; 22 rows updated. UPDATE copy_emp SET department_id = 110; 22 rows updated. UPDATE employees SET department_id = 70 WHERE employee_id = 113; 1 row updated.8-15 Copyright © Oracle Corporation, 2001. All rights reserved. Updating Two Columns with a Subquery Update employee 114’s job and department to match that of employee 205. UPDATE employees SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114; 1 row updated.8-16 Copyright © Oracle Corporation, 2001. All rights reserved. Updating Rows Based on Another Table Use subqueries in UPDATE statements to update rows in a table based on values from another table. UPDATE copy_emp SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100) WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200); 1 row updated.8-17 Copyright © Oracle Corporation, 2001. All rights reserved. UPDATE employees * ERROR at line 1: ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated -parent key not found UPDATE employees * ERROR at line 1: ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated -parent key not found UPDATE employees SET department_id = 55 WHERE department_id = 110; UPDATE employees SET department_id = 55 WHERE department_id = 110; Updating Rows: Integrity Constraint Error Department number 55 does not exist8-18 Copyright © Oracle Corporation, 2001. All rights reserved. Delete a row from the DEPARTMENTS table. Removing a Row from a Table DEPARTMENTS8-19 Copyright © Oracle Corporation, 2001. All rights reserved. The DELETE Statement You can remove existing rows from a table by using the DELETE statement. DELETE [FROM] table [WHERE condition]; DELETE [FROM] table [WHERE condition];8-20 Copyright © Oracle Corporation, 2001. All rights reserved. • Specific rows are deleted if you specify the WHERE clause. • All rows in the table are deleted if you omit the WHERE clause. Deleting Rows from a Table DELETE FROM departments WHERE department_name = 'Finance'; 1 row deleted. DELETE FROM departments WHERE department_name = 'Finance'; 1 row deleted. DELETE FROM copy_emp; 22 rows deleted. DELETE FROM copy_emp; 22 rows deleted.8-21 Copyright © Oracle Corporation, 2001. All rights reserved. Deleting Rows Based on Another Table Use subqueries in DELETE statements to remove rows from a table based on values from another table. DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%'); 1 row deleted.8-22 Copyright © Oracle Corporation, 2001. All rights reserved. Deleting Rows: Integrity Constraint Error DELETE FROM departments WHERE department_id = 60; DELETE FROM departments WHERE department_id = 60; DELETE FROM departments * ERROR at line 1: ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated -child record found DELETE FROM departments * ERROR at line 1: ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated -child record found You cannot delete a row that contains a primary key that is used as a foreign key in another table.8-23 Copyright © Oracle Corporation, 2001. All rights reserved. Using a Subquery in an INSERT Statement INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50) VALUES (99999, 'Taylor', 'DTAYLOR', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000, 50); 1 row created. INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50) VALUES (99999, 'Taylor', 'DTAYLOR', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000, 50); 1 row created.8-24 Copyright © Oracle Corporation, 2001. All rights reserved. Using a Subquery in an INSERT Statement •SELVECeTr ifeym pthloey reees_uidlt,s last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50; SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50;8-25 Copyright © Oracle Corporation, 2001. All rights reserved. Using the WITH CHECK OPTION Keyword on DML Statements • A subquery is used to identify the table and columns of the DML statement. • The WITH CHECK OPTION keyword prohibits you from changing rows that are not in the subquery. INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM employees WHERE department_id = 50 WITH CHECK OPTION) VALUES (99998, 'Smith', 'JSMITH', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000); INSERT INTO * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM employees WHERE department_id = 50 WITH CHECK OPTION) VALUES (99998, 'Smith', 'JSMITH', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000); INSERT INTO * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation8-26 Copyright © Oracle Corporation, 2001. All rights reserved. Overview of the Explict Default Feature • With the explicit default feature, you can use the DEFAULT keyword as a column value where the column default is desired. • The addition of this feature is for compliance with the SQL: 1999 Standard. • This allows the user to control where and when the default value should be applied to data. • Explicit defaults can be used in INSERT and UPDATE statements.8-27 Copyright © Oracle Corporation, 2001. All rights reserved. Using Explicit Default Values INSERT INTO departments (department_id, department_name, manager_id) VALUES (300, 'Engineering', DEFAULT); INSERT INTO departments (department_id, department_name, manager_id) VALUES (300, 'Engineering', DEFAULT); UPDATE departments SET manager_id = DEFAULT WHERE department_id = 10; UPDATE departments SET manager_id = DEFAULT WHERE department_id = 10; • DEFAULT with INSERT: • DEFAULT with UPDATE:8-28 Copyright © Oracle Corporation, 2001. All rights reserved. The MERGE Statement • Provides the ability to conditionally update or insert data into a database table • Performs an UPDATE if the row exists and an INSERT if it is a new row: – Avoids separate updates – Increases performance and ease of use – Is useful in data warehousing applications8-29 Copyright © Oracle Corporation, 2001. All rights reserved. MERGE Statement Syntax You can conditionally insert or update rows in a table by using the MERGE statement. MERGE INTO table_name AS table_alias USING (table|view|sub_query) AS alias ON (join condition) WHEN MATCHED THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values); MERGE INTO table_name AS table_alias USING (table|view|sub_query) AS alias ON (join condition) WHEN MATCHED THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values);8-30 Copyright © Oracle Corporation, 2001. All rights reserved. Merging Rows Insert or update rows in the COPY_EMP table to match the EMPLOYEES table. MERGE INTO copy_emp AS c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.first_name = e.first_name, c.last_name = e.last_name, ... c.department_id = e.department_id WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id);8-31 Copyright © Oracle Corporation, 2001. All rights reserved. Merging Rows MERGE INTO copy_emp c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT VALUES...; MERGE INTO copy_emp c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT VALUES...; SELECT * FROM COPY_EMP; no rows selected SELECT * FROM COPY_EMP; no rows selected SELECT * FROM COPY_EMP; 20 rows selected. SELECT * FROM COPY_EMP; 20 rows selected.8-32 Copyright © Oracle Corporation, 2001. All rights reserved. Database Transactions A database transaction consists of one of the following: • DML statements which constitute one consistent change to the data • One DDL statement • One DCL statement8-33 Copyright © Oracle Corporation, 2001. All rights reserved. Database Transactions • Begin when the first DML SQL statement is executed • End with one of the following events: – A COMMIT or ROLLBACK statement is issued – A DDL or DCL statement executes (automatic commit) – The user exits iSQL*Plus – The system crashes8-34 Copyright © Oracle Corporation, 2001. All rights reserved. Advantages of COMMIT and ROLLBACK Statements With COMMIT and ROLLBACK statements, you can: • Ensure data consistency • Preview data changes before making changes permanent • Group logically related operations8-35 Copyright © Oracle Corporation, 2001. All rights reserved. Controlling Transactions SAVEPOINT B SAVEPOINT A DELETE INSERT UPDATE INSERT Time COMMIT Transaction ROLLBACK to SAVEPOINT B ROLLBACK to SAVEPOINT A ROLLBACK8-36 Copyright © Oracle Corporation, 2001. All rights reserved. Rolling Back Changes to a Marker • Create a marker in a current transaction by using the SAVEPOINT statement. • Roll back to that marker by using the ROLLBACK TO SAVEPOINT statement. UPDATE... SAVEPOINT update_done; Savepoint created. INSERT... ROLLBACK TO update_done; Rollback complete.8-37 Copyright © Oracle Corporation, 2001. All rights reserved. • An automatic commit occurs under the following circumstances: – DDL statement is issued – DCL statement is issued – Normal exit from iSQL*Plus, without explicitly issuing COMMIT or ROLLBACK statements • An automatic rollback occurs under an abnormal termination of iSQL*Plus or a system failure. Implicit Transaction Processing8-38 Copyright © Oracle Corporation, 2001. All rights reserved. State of the Data Before COMMIT or ROLLBACK • The previous state of the data can be recovered. • The current user can review the results of the DML operations by using the SELECT statement. • Other users cannot view the results of the DML statements by the current user. • The affected rows are locked; other users cannot change the data within the affected rows.8-39 Copyright © Oracle Corporation, 2001. All rights reserved. State of the Data After COMMIT • Data changes are made permanent in the database. • The previous state of the data is permanently lost. • All users can view the results. • Locks on the affected rows are released; those rows are available for other users to manipulate. • All savepoints are erased.8-40 Copyright © Oracle Corporation, 2001. All rights reserved. • Make the changes. • Commit the changes. DELETE FROM employees WHERE employee_id = 99999; 1 row deleted. INSERT INTO departments VALUES (290, 'Corporate Tax', NULL, 1700); 1 row inserted. DELETE FROM employees WHERE employee_id = 99999; 1 row deleted. INSERT INTO departments VALUES (290, 'Corporate Tax', NULL, 1700); 1 row inserted. Committing Data COMMIT; Commit complete.8-41 Copyright © Oracle Corporation, 2001. All rights reserved. State of the Data After ROLLBACK Discard all pending changes by using the ROLLBACK statement: • Data changes are undone. • Previous state of the data is restored. • Locks on the affected rows are released. DELETE FROM copy_emp; 22 rows deleted. ROLLBACK; Rollback complete.8-42 Copyright © Oracle Corporation, 2001. All rights reserved. Statement-Level Rollback • If a single DML statement fails during execution, only that statement is rolled back. • The Oracle Server implements an implicit savepoint. • All other changes are retained. • The user should terminate transactions explicitly by executing a COMMIT or ROLLBACK statement.8-43 Copyright © Oracle Corporation, 2001. All rights reserved. Read Consistency • Read consistency guarantees a consistent view of the data at all times. • Changes made by one user do not conflict with changes made by another user. • Read consistency ensures that on the same data: – Readers do not wait for writers – Writers do not wait for readers8-44 Copyright © Oracle Corporation, 2001. All rights reserved. SELECT * FROM userA.employees; Implementation of Read Consistency UPDATE employees SET salary = 7000 WHERE last_name = 'Goyal'; Data blocks Rollback segments changed and unchanged data before change: “old” data User A User B Read consistent image8-45 Copyright © Oracle Corporation, 2001. All rights reserved. Locking In an Oracle database, locks: • Prevent destructive interaction between concurrent transactions • Require no user action • Use the lowest level of restrictiveness • Are held for the duration of the transaction • Are of two types: explicit locking and implicit locking8-46 Copyright © Oracle Corporation, 2001. All rights reserved. Implicit Locking • Two lock modes: – Exclusive: Locks out other users – Share: Allows other users to access the server • High level of data concurrency: – DML: Table share, row exclusive – Queries: No locks required – DDL: Protects object definitions • Locks held until commit or rollback8-47 Copyright © Oracle Corporation, 2001. All rights reserved. Summary Description Adds a new row to the table Modifies existing rows in the table Removes existing rows from the table Conditionally inserts or updates data in a table Makes all pending changes permanent It is used to rollback to the savepoint marker Discards all pending data changes Statement INSERT UPDATE DELETE MERGE COMMIT SAVEPOINT ROLLBACK In this lesson, you should have learned how to use DML statements and control transactions.8-48 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 8 Overview This practice covers the following topics: • Inserting rows into the tables • Updating and deleting rows in the table • Controlling transactionsCopyright © Oracle Corporation, 2001. All rights reserved. Creating and Managing Tables9-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Describe the main database objects • Create tables • Describe the data types that can be used when specifying column definition • Alter table definitions • Drop, rename, and truncate tables9-3 Copyright © Oracle Corporation, 2001. All rights reserved. Database Objects Object Description Table Basic unit of storage; composed of rows and columns View Logically represents subsets of data from one or more tables Sequence Numeric value generator Index Improves the performance of some queries Synonym Gives alternative names to objects9-4 Copyright © Oracle Corporation, 2001. All rights reserved. Naming Rules Table names and column names: • Must begin with a letter • Must be 1 to 30 characters long • Must contain only A–Z, a–z, 0–9, _, $, and # • Must not duplicate the name of another object owned by the same user • Must not be an Oracle Server reserved word9-5 Copyright © Oracle Corporation, 2001. All rights reserved. The CREATE TABLE Statement • You must have: – CREATE TABLE privilege – A storage area • You specify: – Table name – Column name, column data type, and column size CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);9-6 Copyright © Oracle Corporation, 2001. All rights reserved. Referencing Another User’s Tables • Tables belonging to other users are not in the user’s schema. • You should use the owner’s name as a prefix to those tables.9-7 Copyright © Oracle Corporation, 2001. All rights reserved. The DEFAULT Option • Specify a default value for a column during an INSERT operation. • Literal values, expressions, or SQL functions are legal values. • Another column’s name or a pseudocolumn are illegal values. • The default data type must match the column data type. ... hire_date DATE DEFAULT SYSDATE, ...9-8 Copyright © Oracle Corporation, 2001. All rights reserved. • Create the table. • Confirm creation of the table. Creating Tables CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); Table created. DESCRIBE dept9-9 Copyright © Oracle Corporation, 2001. All rights reserved. Tables in the Oracle Database • User tables: – Are a collection of tables created and maintained by the user – Contain user information • Data dictionary: – Is a collection of tables created and maintained by the Oracle Server – Contain database information9-10 Copyright © Oracle Corporation, 2001. All rights reserved. Querying the Data Dictionary ••VViieeww ddiissttiinncctt oobbjjeecctt ttyyppeess oowwnneedd bbyy tthhee uusseerr.. • View tables, views, synonyms, and sequences owned by the user. SELECT table_name FROM user_tables; SELECT DISTINCT object_type FROM user_objects; SELECT * FROM user_catalog; ••SSeeee tthhee nnaammeess ooff ttaabblleess oowwnneedd bbyy tthhee uusseerr..9-11 Copyright © Oracle Corporation, 2001. All rights reserved. Data Types Data Type Description VARCHAR2(size) Variable-length character data CHAR(size) Fixed-length character data NUMBER(p,s) Variable-length numeric data DATE Date and time values LONG Variable-length character data up to 2 gigabytes CLOB Character data up to 4 gigabytes RAW and LONG RAW Raw binary data BLOB Binary data up to 4 gigabytes BFILE Binary data stored in an external file; up to 4 gigabytes ROWID Hexadecimal string representing the unique address of a row in its table9-13 Copyright © Oracle Corporation, 2001. All rights reserved. Datetime Data Types Data Type Description TIMESTAMP Date with fractional seconds INTERVAL YEAR TO MONTH Stored as an interval of years and months INTERVAL DAY TO SECOND Stored as an interval of days to hours minutes and seconds Datetime enhancements with Oracle9i: • New datetime data types have been introduced. • New data type storage is available. • Enhancements have been made to time zones and local time zone.9-14 Copyright © Oracle Corporation, 2001. All rights reserved. Datetime Data Types • The TIMESTAMP data type is an extension of the DATE data type. • It stores the year, month, and day of the DATE data type, plus hour, minute, and second values as well as the fractional second value. • The TIMESTAMP data type is specified as follows: TIMESTAMP[(fractional_seconds_precision)]9-15 Copyright © Oracle Corporation, 2001. All rights reserved. TIMESTAMP WITH TIME ZONE Data Type • TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone displacement in its value. • The time zone displacement is the difference, in hours and minutes, between local time and UTC. TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE9-16 Copyright © Oracle Corporation, 2001. All rights reserved. TIMESTAMP WITH LOCAL TIME Data Type • TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that includes a time zone displacement in its value. • Data stored in the database is normalized to the database time zone • The time zone displacement is not stored as part of the column data; the server returns the data in the users' local session time zone. • TIMESTAMP WITH LOCAL TIME ZONE data type is specified as follows: TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE9-17 Copyright © Oracle Corporation, 2001. All rights reserved. INTERVAL YEAR TO MONTH Data Type • INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields. INTERVAL YEAR [(year_precision)] TO MONTH • Example: INTERVAL '312-2' YEAR(3) TO MONTH Indicates an interval of 312 years and 2 months INTERVAL '312' YEAR(3) Indicates 312 years and 0 months INTERVAL '300' MONTH(3) Indicates an interval of 300 months9-18 Copyright © Oracle Corporation, 2001. All rights reserved. Creating a Table by Using a Subquery Syntax • Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option. • Match the number of specified columns to the number of subquery columns. • Define columns with column names and default values. CREATE TABLE table [(column, column...)] AS subquery;9-19 Copyright © Oracle Corporation, 2001. All rights reserved. Creating a Table by Using a Subquery DESCRIBE dept80 CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80; Table created.9-20 Copyright © Oracle Corporation, 2001. All rights reserved. The ALTER TABLE Statement Use the ALTER TABLE statement to: • Add a new column • Modify an existing column • Define a default value for the new column • Drop a column9-21 Copyright © Oracle Corporation, 2001. All rights reserved. The ALTER TABLE Statement Use the ALTER TABLE statement to add, modify or drop columns. ALTER TABLE table ADD (column datatype [DEFAULT expr] [, column datatype]...); ALTER TABLE table MODIFY (column datatype [DEFAULT expr] [, column datatype]...); ALTER TABLE table DROP (column);9-22 Copyright © Oracle Corporation, 2001. All rights reserved. Adding a Column DEPT80 Add a new column to the DEPT80 table. DEPT80 New column9-23 Copyright © Oracle Corporation, 2001. All rights reserved. Adding a Column • Use the ADD clause to add columns. • The new column becomes the last column. ALTER TABLE dept80 ADD (job_id VARCHAR2(9)); Table altered.9-24 Copyright © Oracle Corporation, 2001. All rights reserved. Modifying a Column • You can change a column’s data type, size, and default value. • A change to the default value affects only subsequent insertions to the table. ALTER TABLE dept80 MODIFY (last_name VARCHAR2(30)); Table altered.9-25 Copyright © Oracle Corporation, 2001. All rights reserved. Dropping a Column Use the DROP COLUMN clause to drop columns you no longer need from the table. ALTER TABLE dept80 DROP COLUMN job_id; Table altered.9-26 Copyright © Oracle Corporation, 2001. All rights reserved. The SET UNUSED Option • You use the SET UNUSED option to mark one or more columns as unused. • You use the DROP UNUSED COLUMNS option to remove the columns that are marked as unused. ALTER TABLE table (column); ALTER TABLE table COLUMN column; ALTER TABLE table (column); ALTER TABLE table COLUMN column; OR ALTER TABLE table DROP UNUSED COLUMNS; ALTER TABLE table DROP UNUSED COLUMNS; SET UNUSED SET UNUSED9-27 Copyright © Oracle Corporation, 2001. All rights reserved. Dropping a Table • All data and structure in the table is deleted. • Any pending transactions are committed. • All indexes are dropped. • You cannot roll back the DROP TABLE statement. DROP TABLE dept80; Table dropped.9-28 Copyright © Oracle Corporation, 2001. All rights reserved. Changing the Name of an Object • To change the name of a table, view, sequence, or synonym, execute the RENAME statement. • You must be the owner of the object. RENAME dept TO detail_dept; Table renamed.9-29 Copyright © Oracle Corporation, 2001. All rights reserved. Truncating a Table • The TRUNCATE TABLE statement: – Removes all rows from a table – Releases the storage space used by that table • You cannot roll back row removal when using TRUNCATE. • Alternatively, you can remove rows by using the DELETE statement. TRUNCATE TABLE detail_dept; Table truncated.9-30 Copyright © Oracle Corporation, 2001. All rights reserved. Adding Comments to a Table • You can add comments to a table or column by using the COMMENT statement. • Comments can be viewed through the data dictionary views: – ALL_COL_COMMENTS – USER_COL_COMMENTS – ALL_TAB_COMMENTS – USER_TAB_COMMENTS COMMENT ON TABLE employees IS 'Employee Information'; Comment created.9-31 Copyright © Oracle Corporation, 2001. All rights reserved. Summary Statement Description CREATE TABLE Creates a table ALTER TABLE Modifies table structures DROP TABLE Removes the rows and table structure RENAME Changes the name of a table, view, sequence, or synonym TRUNCATE Removes all rows from a table and releases the storage space COMMENT Adds comments to a table or view In this lesson, you should have learned how to use DDL statements to create, alter, drop, and rename tables. 9-32 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 9 Overview This practice covers the following topics: • Creating new tables • Creating a new table by using the CREATE TABLE AS syntax • Modifying column definitions • Verifying that the tables exist • Adding comments to tables • Dropping tables • Altering tablesCopyright © Oracle Corporation, 2001. All rights reserved. Including Constraints10-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Describe constraints • Create and maintain constraints10-3 Copyright © Oracle Corporation, 2001. All rights reserved. What Are Constraints? • Constraints enforce rules at the table level. • Constraints prevent the deletion of a table if there are dependencies. • The following constraint types are valid: – NOT NULL – UNIQUE – PRIMARY KEY – FOREIGN KEY – CHECK10-4 Copyright © Oracle Corporation, 2001. All rights reserved. Constraint Guidelines • Name a constraint or the Oracle server generates a name by using the SYS_Cn format. • Create a constraint either: – At the same time as the table is created, or – After the table has been created. • Define a constraint at the column or table level. • View a constraint in the data dictionary.10-5 Copyright © Oracle Corporation, 2001. All rights reserved. Defining Constraints CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint][,...]); CREATE TABLE employees( employee_id NUMBER(6), first_name VARCHAR2(20), ... job_id VARCHAR2(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));10-6 Copyright © Oracle Corporation, 2001. All rights reserved. Defining Constraints • Column constraint level: • Table constraint level: ccoolluummnn [[CCOONNSSTTRRAAIINNTT ccoonnssttrraaiinntt__nnaammee]] ccoonnssttrraaiinntt__ttyyppee,, column,... [CONSTRAINT constraint_name] constraint_type (column, ...), column,... [CONSTRAINT constraint_name] constraint_type (column, ...),10-7 Copyright © Oracle Corporation, 2001. All rights reserved. The NOT NULL Constraint Ensures that null values are not permitted for the column NOT NULL constraint (No row can contain a null value for this column.) Absence of NOT NULL constraint (Any row can contain null for this column.) NOT NULL constraint10-8 Copyright © Oracle Corporation, 2001. All rights reserved. The NOT NULL Constraint Is defined at the column level System named User named CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL, ... 10-9 Copyright © Oracle Corporation, 2001. All rights reserved. The UNIQUE Constraint EMPLOYEES UNIQUE constraint INSERT INTO Not allowed: already exists Allowed10-10 Copyright © Oracle Corporation, 2001. All rights reserved. The UNIQUE Constraint Is defined at either the table level or the column level CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, ... CONSTRAINT emp_email_uk UNIQUE(email));10-11 Copyright © Oracle Corporation, 2001. All rights reserved. The PRIMARY KEY Constraint DEPARTMENTS PRIMARY KEY INSERT INTO Not allowed (null value) Not allowed (50 already exists)10-12 Copyright © Oracle Corporation, 2001. All rights reserved. The PRIMARY KEY Constraint Is defined at either the table level or the column level CREATE TABLE departments( department_id NUMBER(4), department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL, manager_id NUMBER(6), location_id NUMBER(4), CONSTRAINT dept_id_pk PRIMARY KEY(department_id));10-13 Copyright © Oracle Corporation, 2001. All rights reserved. The FOREIGN KEY Constraint DEPARTMENTS EMPLOYEES FOREIGN KEY INSERT INTO Not allowed (9 does not exist) Allowed PRIMARY KEY10-14 Copyright © Oracle Corporation, 2001. All rights reserved. The FOREIGN KEY Constraint Is defined at either the table level or the column level CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, ... department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT emp_email_uk UNIQUE(email));10-15 Copyright © Oracle Corporation, 2001. All rights reserved. FOREIGN KEY Constraint Keywords • FOREIGN KEY: Defines the column in the child table at the table constraint level • REFERENCES: Identifies the table and column in the parent table • ON DELETE CASCADE: Deletes the dependent rows in the child table when a row in the parent table is deleted • ON DELETE SET NULL: Converts dependent foreign key values to null10-16 Copyright © Oracle Corporation, 2001. All rights reserved. The CHECK Constraint • Defines a condition that each row must satisfy • The following expressions are not allowed: – References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns – Calls to SYSDATE, UID, USER, and USERENV functions – Queries that refer to other values in other rows ..., salary NUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0),...10-17 Copyright © Oracle Corporation, 2001. All rights reserved. Adding a Constraint Syntax Use the ALTER TABLE statement to: • Add or drop a constraint, but not modify its structure • Enable or disable constraints • Add a NOT NULL constraint by using the MODIFY clause ALTER TABLE table ADD [CONSTRAINT constraint] type (column); ALTER TABLE table ADD [CONSTRAINT constraint] type (column);10-18 Copyright © Oracle Corporation, 2001. All rights reserved. Adding a Constraint Add a FOREIGN KEY constraint to the EMPLOYEES table to indicate that a manager must already exist as a valid employee in the EMPLOYEES table. ALTER TABLE employees ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id); Table altered.10-19 Copyright © Oracle Corporation, 2001. All rights reserved. Dropping a Constraint • Remove the manager constraint from the EMPLOYEES table. • Remove the PRIMARY KEY constraint on the DEPARTMENTS table and drop the associated FOREIGN KEY constraint on the EMPLOYEES.DEPARTMENT_ID column. ALTER TABLE employees DROP CONSTRAINT emp_manager_fk; Table altered. ALTER TABLE employees DROP CONSTRAINT emp_manager_fk; Table altered. ALTER TABLE departments DROP PRIMARY KEY CASCADE; Table altered. ALTER TABLE departments DROP PRIMARY KEY CASCADE; Table altered.10-20 Copyright © Oracle Corporation, 2001. All rights reserved. Disabling Constraints • Execute the DISABLE clause of the ALTER TABLE statement to deactivate an integrity constraint. • Apply the CASCADE option to disable dependent integrity constraints. ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk CASCADE; Table altered. ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk CASCADE; Table altered.10-21 Copyright © Oracle Corporation, 2001. All rights reserved. Enabling Constraints • Activate an integrity constraint currently disabled in the table definition by using the ENABLE clause. • A UNIQUE or PRIMARY KEY index is automatically created if you enable a UNIQUE key or PRIMARY KEY constraint. ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk; Table altered. ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk; Table altered.10-22 Copyright © Oracle Corporation, 2001. All rights reserved. Cascading Constraints • The CASCADE CONSTRAINTS clause is used along with the DROP COLUMN clause. • The CASCADE CONSTRAINTS clause drops all referential integrity constraints that refer to the primary and unique keys defined on the dropped columns. • The CASCADE CONSTRAINTS clause also drops all multicolumn constraints defined on the dropped columns.10-23 Copyright © Oracle Corporation, 2001. All rights reserved. Cascading Constraints Example ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS; Table altered. ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS; Table altered. ALTER TABLE test1 DROP (pk, fk, col1) CASCADE CONSTRAINTS; Table altered. ALTER TABLE test1 DROP (pk, fk, col1) CASCADE CONSTRAINTS; Table altered.10-24 Copyright © Oracle Corporation, 2001. All rights reserved. Viewing Constraints Query the USER_CONSTRAINTS table to view all constraint definitions and names. SELECT constraint_name, constraint_type, search_condition FROM user_constraints WHERE table_name = 'EMPLOYEES';10-25 Copyright © Oracle Corporation, 2001. All rights reserved. Viewing the Columns Associated with Constraints View the columns associated with the constraint names in the USER_CONS_COLUMNS view. SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name = 'EMPLOYEES';10-26 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned how to create constraints. • There are the following types of constraints: – NOT NULL – UNIQUE – PRIMARY KEY – FOREIGN KEY – CHECK • You can query the USER_CONSTRAINTS table to view all constraint definitions and names.10-27 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 10 Overview This practice covers the following topics: • Adding constraints to existing tables • Adding more columns to a table • Displaying information in data dictionary views10-30 Copyright © Oracle Corporation, 2001. All rights reserved.Copyright © Oracle Corporation, 2001. All rights reserved. CCrreeaattiinngg VViieewss11-2 Copyright © Oracle Corporation, 2001. All rights reserved. OObbjjeeccttiivveess After completing this lesson, you should be able to do the following: • Describe a view • Create, alter the definition of, and drop a view • Retrieve data through a view • Insert, update, and delete data through a view • Create and use an inline view • Perform top-n analysis 11-3 Copyright © Oracle Corporation, 2001. All rights reserved. DDaattaabbaassee OObbjjeeccttss Description Basic unit of storage; composed of rows and columns Logically represents subsets of data from one or more tables Generates primary key values Improves the performance of some queries Alternative name for an object Object Table View Sequence Index Synonym11-4 Copyright © Oracle Corporation, 2001. All rights reserved. Whhaatt IIss aa VViieeww?? EMPLOYEES Table EMPVU80 View11-5 Copyright © Oracle Corporation, 2001. All rights reserved. Whhyy UUssee VViieewwss?? • To restrict data access • To make complex queries easy • To provide data independence • To present different views of the same data 11-6 Copyright © Oracle Corporation, 2001. All rights reserved. Simple Views and Complex Views Feature Simple Views Complex Views Number of tables One One or more Contain functions No Yes Contain groups of data No Yes DML operations through a view Yes Not always11-7 Copyright © Oracle Corporation, 2001. All rights reserved. Creating a View • You embed a subquery within the CREATE VIEW statement. • The subquery can contain complex SELECT syntax. CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];11-8 Copyright © Oracle Corporation, 2001. All rights reserved. Creating a View • Create a view, EMPVU80, that contains details of employees in department 80. • Describe the structure of the view by using the iSQL*Plus DESCRIBE command. DDEESSCCRRIIBBEE eemmppvvuu8800 CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80; View created.11-9 Copyright © Oracle Corporation, 2001. All rights reserved. Creating a View • Create a view by using column aliases in the subquery. • Select the columns from this view by the given alias names. CREATE VIEW salvu50 AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 50; View created.11-10 Copyright © Oracle Corporation, 2001. All rights reserved. RReettrriieevviinngg DDaattaa ffrroom aa VViieeww SELECT * FROM salvu50;11-11 Copyright © Oracle Corporation, 2001. All rights reserved. QQuueerryyiinngg aa VViieeww USER_VIEWS EMPVU80 SELECT employee_id, last_name, salary FROM employees WHERE department_id=80; iSQL*Plus SELECT * FROM empvu80; EMPLOYEES Oracle Server11-12 Copyright © Oracle Corporation, 2001. All rights reserved. Modifying a View • Modify the EMPVU80 view by using CREATE OR REPLACE VIEW clause. Add an alias for each column name. • Column aliases in the CREATE VIEW clause are listed in the same order as the columns in the subquery. CREATE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id) AS SELECT employee_id, first_name || ' ' || last_name, salary, department_id FROM employees WHERE department_id = 80; View created.11-13 Copyright © Oracle Corporation, 2001. All rights reserved. Creating a Complex View Create a complex view that contains group functions to display values from two tables. CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) AS SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name; View created.11-14 Copyright © Oracle Corporation, 2001. All rights reserved. Rules for Performing DML Operations on a View • You can perform DML operations on simple views. • You cannot remove a row if the view contains the following: – Group functions – A GROUP BY clause – The DISTINCT keyword – The pseudocolumn ROWNUM keyword11-15 Copyright © Oracle Corporation, 2001. All rights reserved. Rules for Performing DML Operations on a View • You cannot modify data in a view if it contains: – Group functions – A GROUP BY clause – The DISTINCT keyword – The pseudocolumn ROWNUM keyword – Columns defined by expressions11-16 Copyright © Oracle Corporation, 2001. All rights reserved. Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: • Group functions • A GROUP BY clause • The DISTINCT keyword • The pseudocolumn ROWNUM keyword • Columns defined by expressions • NOT NULL columns in the base tables that are not selected by the view11-17 Copyright © Oracle Corporation, 2001. All rights reserved. Using the WITH CHECK OPTION Clause • You can ensure that DML operations performed on the view stay within the domain of the view by using the WITH CHECK OPTION clause. • Any attempt to change the department number for any row in the view fails because it violates the WITH CHECK OPTION constraint. CREATE OR REPLACE VIEW empvu20 AS SELECT * FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck; View created.11-18 Copyright © Oracle Corporation, 2001. All rights reserved. Denying DML Operations • You can ensure that no DML operations occur by adding the WITH READ ONLY option to your view definition. • Any attempt to perform a DML on any row in the view results in an Oracle server error.11-19 Copyright © Oracle Corporation, 2001. All rights reserved. Denying DML Operations CREATE OR REPLACE VIEW empvu10 (employee_number, employee_name, job_title) AS SELECT employee_id, last_name, job_id FROM employees WHERE department_id = 10 WITH READ ONLY; View created.11-20 Copyright © Oracle Corporation, 2001. All rights reserved. RReemoovviinngg aa VViieeww You can remove a view without losing data because a view is based on underlying tables in the database. DROP VIEW empvu80; View dropped. DDRROOPP VVIIEEWW vviieeww;;11-21 Copyright © Oracle Corporation, 2001. All rights reserved. IInnlliinnee VViieewwss • An inline view is a subquery with an alias (or correlation name) that you can use within a SQL statement. • A named subquery in the FROM clause of the main query is an example of an inline view. • An inline view is not a schema object. 11-22 Copyright © Oracle Corporation, 2001. All rights reserved. TToopp--nnAAnnaallyyssiiss • Top-n queries ask for the n largest or smallest values of a column. For example: – What are the ten best selling products? – What are the ten worst selling products ? • Both largest values and smallest values sets are considered top-n queries. 11-23 Copyright © Oracle Corporation, 2001. All rights reserved. Performing Top-n Analysis The high-level structure of a top-n analysis query is: SELECT [column_list], ROWNUM FROM (SELECT [column_list] FROM table ORDER BY Top-N_column) WHERE ROWNUM <= N; SELECT [column_list], ROWNUM FROM (SELECT [column_list] FROM table ORDER BY Top-N_column) WHERE ROWNUM <= N;11-24 Copyright © Oracle Corporation, 2001. All rights reserved. EExxaamppllee ooff TToopp--nn AAnnaallyyssiiss To display the top three earner names and salaries from the EMPLOYEES table. SELECT ROWNUM as RANK, last_name, salary FROM (SELECT last_name,salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 3;11-25 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson you should have learned that a view is derived from data in other tables or other views and provides the following advantages: • Restricts database access • Simplifies queries • Provides data independence • Provides multiple views of the same data • Can be dropped without removing the underlying data11-26 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 11 Overview This practice covers the following topics: • Creating a simple view • Creating a complex view • Creating a view with a check constraint • Attempting to modify data in the view • Displaying view definitions • Removing viewsCopyright © Oracle Corporation, 2001. All rights reserved. Other Database Objects12-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Create, maintain, and use sequences • Create and maintain indexes • Create private and public synonyms12-3 Copyright © Oracle Corporation, 2001. All rights reserved. DDaattaabbaassee OObbjjeeccttss Description Basic unit of storage; composed of rows and columns Logically represents subsets of data from one or more tables Generates primary key values Improves the performance of some queries Alternative name for an object Object Table View Sequence Index Synonym12-4 Copyright © Oracle Corporation, 2001. All rights reserved. What Is a Sequence? A sequence: • Automatically generates unique numbers • Is a sharable object • Is typically used to create a primary key value • Replaces application code • Speeds up the efficiency of accessing sequence values when cached in memory12-5 Copyright © Oracle Corporation, 2001. All rights reserved. The CREATE SEQUENCE Statement Syntax Define a sequence to generate sequential numbers automatically. CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];12-6 Copyright © Oracle Corporation, 2001. All rights reserved. Creating a Sequence • Create a sequence named DEPT_DEPTID_SEQ to be used for the primary key of the DEPARTMENTS table. • Do not use the CYCLE option. CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE; Sequence created. CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE; Sequence created.12-7 Copyright © Oracle Corporation, 2001. All rights reserved. Confirming Sequences • Verify your sequence values in the USER_SEQUENCES data dictionary table. • The LAST_NUMBER column displays the next available sequence number if NOCACHE is specified. SELECT sequence_name, min_value, max_value, increment_by, last_number FROM user_sequences; SELECT sequence_name, min_value, max_value, increment_by, last_number FROM user_sequences;12-8 Copyright © Oracle Corporation, 2001. All rights reserved. NEXTVAL and CURRVAL Pseudocolumns • NEXTVAL returns the next available sequence value. It returns a unique value every time it is referenced, even for different users. • CURRVAL obtains the current sequence value. • NEXTVAL must be issued for that sequence before CURRVAL contains a value. 12-10 Copyright © Oracle Corporation, 2001. All rights reserved. Using a Sequence • Insert a new department named “Support” in location ID 2500. • View the current value for the DEPT_DEPTID_SEQ sequence. INSERT INTO departments(department_id, department_name, location_id) VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500); 1 row created. INSERT INTO departments(department_id, department_name, location_id) VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500); 1 row created. SELECT dept_deptid_seq.CURRVAL FROM dual; SELECT dept_deptid_seq.CURRVAL FROM dual;12-11 Copyright © Oracle Corporation, 2001. All rights reserved. Using a Sequence • Caching sequence values in memory gives faster access to those values. • Gaps in sequence values can occur when: – A rollback occurs – The system crashes – A sequence is used in another table • If the sequence was created with NOCACHE, view the next available value, by querying the USER_SEQUENCES table.12-12 Copyright © Oracle Corporation, 2001. All rights reserved. Modifying a Sequence Change the increment value, maximum value, minimum value, cycle option, or cache option. ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE; Sequence altered. ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE; Sequence altered.12-13 Copyright © Oracle Corporation, 2001. All rights reserved. Guidelines for Modifying a Sequence • You must be the owner or have the ALTER privilege for the sequence. • Only future sequence numbers are affected. • The sequence must be dropped and re-created to restart the sequence at a different number. • Some validation is performed.12-14 Copyright © Oracle Corporation, 2001. All rights reserved. Removing a Sequence • Remove a sequence from the data dictionary by using the DROP SEQUENCE statement. • Once removed, the sequence can no longer be referenced. DROP SEQUENCE dept_deptid_seq; Sequence dropped. DROP SEQUENCE dept_deptid_seq; Sequence dropped.12-15 Copyright © Oracle Corporation, 2001. All rights reserved. What Is an Index? An index: • Is a schema object • Is used by the Oracle Server to speed up the retrieval of rows by using a pointer • Can reduce disk I/O by using a rapid path access method to locate data quickly • Is independent of the table it indexes • Is used and maintained automatically by the Oracle Server12-16 Copyright © Oracle Corporation, 2001. All rights reserved. How Are Indexes Created? • Automatically: A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition. • Manually: Users can create nonunique indexes on columns to speed up access to the rows.12-17 Copyright © Oracle Corporation, 2001. All rights reserved. Creating an Index • Create an index on one or more columns. • Improve the speed of query access to the LAST_NAME column in the EMPLOYEES table. CREATE INDEX emp_last_name_idx ON employees(last_name); Index created. CREATE INDEX emp_last_name_idx ON employees(last_name); Index created. CREATE INDEX index ON table (column[, column]...); CREATE INDEX index ON table (column[, column]...);12-18 Copyright © Oracle Corporation, 2001. All rights reserved. When to Create an Index You should create an index if: • A column contains a wide range of values • A column contains a large number of null values • One or more columns are frequently used together in a WHERE clause or a join condition • The table is large and most queries are expected to retrieve less than 2 to 4% of the rows12-19 Copyright © Oracle Corporation, 2001. All rights reserved. When Not to Create an Index It is usually not worth creating an index if: • The table is small • The columns are not often used as a condition in the query • Most queries are expected to retrieve more than 2 to 4% of the rows in the table • The table is updated frequently • The indexed columns are referenced as part of an expression12-20 Copyright © Oracle Corporation, 2001. All rights reserved. Confirming Indexes • The USER_INDEXES data dictionary view contains the name of the index and its uniqueness. • The USER_IND_COLUMNS view contains the index name, the table name, and the column name. SELECT ic.index_name, ic.column_name, ic.column_position col_pos,ix.uniqueness FROM user_indexes ix, user_ind_columns ic WHERE ic.index_name = ix.index_name AND ic.table_name = 'EMPLOYEES';12-21 Copyright © Oracle Corporation, 2001. All rights reserved. Function-Based Indexes • A function-based index is an index based on expressions. • The index expression is built from table columns, constants, SQL functions, and user-defined functions. CREATE INDEX upper_dept_name_idx ON departments(UPPER(department_name)); Index created. SELECT * FROM departments WHERE UPPER(department_name) = 'SALES';12-22 Copyright © Oracle Corporation, 2001. All rights reserved. Removing an Index • Remove an index from the data dictionary by using the DROP INDEX command. • Remove the UPPER_LAST_NAME_IDX index from the data dictionary. • To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege. DROP INDEX upper_last_name_idx; Index dropped. DROP INDEX upper_last_name_idx; Index dropped. DDRROOPP IINNDDEEXX iinnddeexx;;12-23 Copyright © Oracle Corporation, 2001. All rights reserved. Synonyms Simplify access to objects by creating a synonym (another name for an object). With synonyms, you can: • Ease referring to a table owned by another user • Shorten lengthy object names CREATE [PUBLIC] SYNONYM synonym FOR object; CREATE [PUBLIC] SYNONYM synonym FOR object;12-24 Copyright © Oracle Corporation, 2001. All rights reserved. Creating and Removing Synonyms • Create a shortened name for the DEPT_SUM_VU view. • Drop a synonym. CREATE SYNONYM d_sum FOR dept_sum_vu; Synonym Created. CREATE SYNONYM d_sum FOR dept_sum_vu; Synonym Created. DROP SYNONYM d_sum; Synonym dropped. DROP SYNONYM d_sum; Synonym dropped.12-25 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned how to: • Generate sequence numbers automatically by using a sequence generator • View sequence information in the USER_SEQUENCES data dictionary table • Create indexes to improve query retrieval speed • View index information in the USER_INDEXES dictionary table • Use synonyms to provide alternative names for objects12-26 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 12 Overview This practice covers the following topics: • Creating sequences • Using sequences • Creating nonunique indexes • Displaying data dictionary information about sequences and indexes • Dropping indexesCopyright © Oracle Corporation, 2001. All rights reserved. Controlling User Access13-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Create users • Create roles to ease setup and maintenance of the security model • Use the GRANT and REVOKE statements to grant and revoke object privileges • Create and access database links13-3 Copyright © Oracle Corporation, 2001. All rights reserved. CCoonnttrroolllliinngg UUsseerr AAcccceessss Database Administrator Users Username and Password Privileges13-4 Copyright © Oracle Corporation, 2001. All rights reserved. Privileges • Database security: – System security – Data security • System privileges: Gaining access to the database • Object privileges: Manipulating the content of the database objects • Schemas: Collections of objects, such as tables, views, and sequences13-5 Copyright © Oracle Corporation, 2001. All rights reserved. System Privileges • More than 100 privileges are available. • The database administrator has high-level system privileges for tasks such as: – Creating new users – Removing users – Removing tables – Backing up tables13-6 Copyright © Oracle Corporation, 2001. All rights reserved. Creating Users The DBA creates users by using the CREATE USER statement. CREATE USER scott IDENTIFIED BY tiger; User created. CREATE USER scott IDENTIFIED BY tiger; User created. CREATE USER user IDENTIFIED BY password;13-7 Copyright © Oracle Corporation, 2001. All rights reserved. User System Privileges • Once a user is created, the DBA can grant specific system privileges to a user. • An application developer, for example, may have the following system privileges: – CREATE SESSION – CREATE TABLE – CREATE SEQUENCE – CREATE VIEW – CREATE PROCEDURE GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...]; GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...];13-8 Copyright © Oracle Corporation, 2001. All rights reserved. Granting System Privileges The DBA can grant a user specific system privileges. GRANT create session, create table, create sequence, create view TO scott; Grant succeeded. GRANT create session, create table, create sequence, create view TO scott; Grant succeeded.13-9 Copyright © Oracle Corporation, 2001. All rights reserved. Whhaatt IIss aa RRoollee?? Allocating privileges without a role Allocating privileges with a role Privileges Users Manager13-10 Copyright © Oracle Corporation, 2001. All rights reserved. CCrreeaattiinngg aanndd GGrraannttiinngg PPrriivviilleeggeess ttoo aa RRoollee CREATE ROLE manager; Role created. CREATE ROLE manager; Role created. GRANT create table, create view TO manager; Grant succeeded. GRANT create table, create view TO manager; Grant succeeded. GRANT manager TO DEHAAN, KOCHHAR; Grant succeeded. GRANT manager TO DEHAAN, KOCHHAR; Grant succeeded. • Create a role • Grant privileges to a role • Grant a role to users13-11 Copyright © Oracle Corporation, 2001. All rights reserved. Changing Your Password • The DBA creates your user account and initializes your password. • You can change your password by using the ALTER USER statement. ALTER USER scott IDENTIFIED BY lion; User altered.13-12 Copyright © Oracle Corporation, 2001. All rights reserved. Object Privilege Table View Sequence Procedure ALTER Ö Ö DELETE Ö Ö EXECUTE Ö INDEX Ö INSERT Ö Ö REFERENCES Ö Ö SELECT Ö Ö Ö UPDATE Ö Ö OObbjjeecctt PPrriivviilleeggeess13-13 Copyright © Oracle Corporation, 2001. All rights reserved. Object Privileges • Object privileges vary from object to object. • An owner has all the privileges on the object. • An owner can give specific privileges on that owner’s object. GRANT object_priv [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION]; GRANT object_priv [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION];13-14 Copyright © Oracle Corporation, 2001. All rights reserved. Granting Object Privileges • Grant query privileges on the EMPLOYEES table. • Grant privileges to update specific columns to users and roles. GRANT select ON employees TO sue, rich; Grant succeeded. GRANT select ON employees TO sue, rich; Grant succeeded. GRANT update (department_name, location_id) ON departments TO scott, manager; Grant succeeded. GRANT update (department_name, location_id) ON departments TO scott, manager; Grant succeeded.13-15 Copyright © Oracle Corporation, 2001. All rights reserved. Using the WITH GRANT OPTION and PUBLIC Keywords • Give a user authority to pass along privileges. • Allow all users on the system to query data from Alice’s DEPARTMENTS table. GRANT select, insert ON departments TO scott WITH GRANT OPTION; Grant succeeded. GRANT select, insert ON departments TO scott WITH GRANT OPTION; Grant succeeded. GRANT select ON alice.departments TO PUBLIC; Grant succeeded. GRANT select ON alice.departments TO PUBLIC; Grant succeeded.13-16 Copyright © Oracle Corporation, 2001. All rights reserved. CCoonnffiirrmiinngg PPrriivviilleeggeess GGrraanntteedd Data Dictionary View Description ROLE_SYS_PRIVS System privileges granted to roles ROLE_TAB_PRIVS Table privileges granted to roles USER_ROLE_PRIVS Roles accessible by the user USER_TAB_PRIVS_MADE Object privileges granted on the user’s objects USER_TAB_PRIVS_RECD Object privileges granted to the user USER_COL_PRIVS_MADE Object privileges granted on the columns of the user’s objects USER_COL_PRIVS_RECD Object privileges granted to the user on specific columns USER_SYS_PRIVS Lists system privileges granted to the user13-17 Copyright © Oracle Corporation, 2001. All rights reserved. How to Revoke Object Privileges • You use the REVOKE statement to revoke privileges granted to other users. • Privileges granted to others through the WITH GRANT OPTION clause are also revoked. REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS]; REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS];13-18 Copyright © Oracle Corporation, 2001. All rights reserved. Revoking Object Privileges As user Alice, revoke the SELECT and INSERT privileges given to user Scott on the DEPARTMENTS table. REVOKE select, insert ON departments FROM scott; Revoke succeeded. REVOKE select, insert ON departments FROM scott; Revoke succeeded.13-19 Copyright © Oracle Corporation, 2001. All rights reserved. Database Links A database link connection allows local users to access data on a remote database. Local Remote SELECT * FROM emp@HQ_ACME.COM; HQ_ACME.COM Database fred.EMP Table13-20 Copyright © Oracle Corporation, 2001. All rights reserved. Database Links • Create the database link. • Write SQL statements that use the database link. CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales'; Database link created. CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales'; Database link created. SELECT * FROM fred.emp@HQ.ACME.COM; SELECT * FROM fred.emp@HQ.ACME.COM;13-21 Copyright © Oracle Corporation, 2001. All rights reserved. SSuummaarryy Statement Action CREATE USER Creates a user (usually performed by a DBA) GRANT Gives other users privileges to access the your objects CREATE ROLE Creates a collection of privileges (usually performed by a DBA) ALTER USER Changes a user’s password REVOKE Removes privileges on an object from users In this lesson you should have learned about DCL statements that control access to the database and database objects.13-22 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 13 Overview This practice covers the following topics: • Granting other users privileges to your table • Modifying another user’s table through the privileges granted to you • Creating a synonym • Querying the data dictionary views related to privilegesCopyright © Oracle Corporation, 2001. All rights reserved. SQL Workshop14-2 Copyright © Oracle Corporation, 2001. All rights reserved. Workshop Overview This workshop covers: • Creating tables and sequences • Modifying data in the tables • Modifying table definitions • Creating views • Writing scripts containing SQL and iSQL*Plus commands • Generating a simple reportCopyright © Oracle Corporation, 2001. All rights reserved. Using SET Operators15-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Describe SET operators • Use a SET operator to combine multiple queries into a single query • Control the order of rows returned 15-3 Copyright © Oracle Corporation, 2001. All rights reserved. The SET Operators A B UNION/UNION ALL A B A B INTERSECT A B MINUS15-4 Copyright © Oracle Corporation, 2001. All rights reserved. Tables Used in This Lesson The tables used in this lesson are: • EMPLOYEES: Provides details regarding all current employees • JOB_HISTORY: When an employee switches jobs, the details of the start date and end date of the former job, the job identification number and department are recorded in this table15-7 Copyright © Oracle Corporation, 2001. All rights reserved. The UNION SET Operator The UNION operator returns results from both queries after eliminating duplications. A B15-8 Copyright © Oracle Corporation, 2001. All rights reserved. Using the UNION Operator Display the current and previous job details of all employees. Display each employee only once. SELECT employee_id, job_id FROM employees UNION SELECT employee_id, job_id FROM job_history;15-10 Copyright © Oracle Corporation, 2001. All rights reserved. The UNION ALL Operator A B The UNION ALL operator returns results from both queries including all duplications. 15-11 Copyright © Oracle Corporation, 2001. All rights reserved. Using the UNION ALL Operator Display the current and previous departments of all employees. SELECT employee_id, job_id, department_id FROM employees UNION ALL SELECT employee_id, job_id, department_id FROM job_history ORDER BY employee_id;15-12 Copyright © Oracle Corporation, 2001. All rights reserved. The INTERSECT Operator A B The INTERSECT operator returns results that are common to both queries. 15-13 Copyright © Oracle Corporation, 2001. All rights reserved. Using the INTERSECT Operator Display the employee IDs and job IDs of employees who are currently in a job title that they have held once before during their tenure with the company SELECT employee_id, job_id FROM employees INTERSECT SELECT employee_id, job_id FROM job_history;15-14 Copyright © Oracle Corporation, 2001. All rights reserved. The MINUS Operator A B The MINUS operator returns rows from the first query that are not present in the second query. 15-15 Copyright © Oracle Corporation, 2001. All rights reserved. The MINUS Operator Display the employee IDs of those employees who have not changed their jobs even once. SELECT employee_id FROM employees MINUS SELECT employee_id FROM job_history;15-16 Copyright © Oracle Corporation, 2001. All rights reserved. SET Operator Guidelines • The expressions in the SELECT lists must match in number and data type. • Parentheses can be used to alter the sequence of execution. • The ORDER BY clause: – Can appear only at the very end of the statement – Will accept the column name, aliases from the first SELECT statement, or the positional notation15-17 Copyright © Oracle Corporation, 2001. All rights reserved. The Oracle Server and SET Operators • Duplicate rows are automatically eliminated except in UNION ALL. • Column names from the first query appear in the result. • The output is sorted in ascending order by default except in UNION ALL. 15-18 Copyright © Oracle Corporation, 2001. All rights reserved. Matching the SELECT Statements Using the UNION operator, display the department ID, location, and hire date for all employees. SELECT department_id, TO_NUMBER(null) location, hire_date FROM employees UNION SELECT department_id, location_id, TO_DATE(null) FROM departments;15-19 Copyright © Oracle Corporation, 2001. All rights reserved. Matching the SELECT Statement Using the UNION operator, display the employee ID, job ID, and salary of all employees. SELECT employee_id, job_id,salary FROM employees UNION SELECT employee_id, job_id,0 FROM job_history;15-20 Copyright © Oracle Corporation, 2001. All rights reserved. Controlling the Order of Rows Produce an English sentence using two UNION operators. COLUMN a_dummy NOPRINT SELECT FROM dual UNION SELECT FROM dual UNION SELECT 'the world to', 2 FROM dual ORDER BY 2; 'sing' AS "My dream", 3 a_dummy 'I''d like to teach', 115-21 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned the following: • UNION returns all distinct rows. • UNION ALL returns all rows, including duplicates. • INTERSECT returns all rows shared by both queries. • MINUS returns all distinct rows selected by the first query but not by the second. • ORDER BY can appear only at the very end of the statement.15-22 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 15 Overview This practice covers the following topics: • Writing queries using the SET operators • Discovering alternative join methodsCopyright © Oracle Corporation, 2001. All rights reserved. Oracle 9i Datetime Functions16-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able use the following datetime functions: • CURRENT_DATE • CURRENT_TIMESTAMP • LOCALTIMESTAMP • DBTIMEZONE • SESSIONTIMEZONE • EXTRACT • FROM_TZ • TO_TIMESTAMP • TO_TIMESTAMP_TZ • TO_YMINTERVAL • TZ_OFFSET16-3 Copyright © Oracle Corporation, 2001. All rights reserved. TIME ZONES 0 0 0 0 GMT16-4 Copyright © Oracle Corporation, 2001. All rights reserved. Oracle 9i Datetime Support • In Oracle9i, you can include the time zone in your date and time data, and provide support for fractional seconds. • Three new data types are added to DATE: – TIMESTAMP – TIMESTAMP WITH TIME ZONE (TSTZ) – TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) • Oracle9i provides daylight savings support for datetime data types in the server.16-6 Copyright © Oracle Corporation, 2001. All rights reserved. CURRENT_DATE ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT SESSIONTIMEZONE, FROM DUAL; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; ALTER SESSION SET TIME_ZONE = '-5:0'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; CURRENT_DATE CURRENT_DATE is sensitive to the session time zone 16-7 Copyright © Oracle Corporation, 2001. All rights reserved. CURRENT_TIMESTAMP ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; ALTER SESSION SET TIME_ZONE = '-5:0'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;16-8 Copyright © Oracle Corporation, 2001. All rights reserved. LOCALTIMESTAMP ALTER SESSION SET TIME_ZONE = '-5:0'; SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;16-9 Copyright © Oracle Corporation, 2001. All rights reserved. DBTIMEZONE and SESSIONTIMEZONE SELECT DBTIMEZONE FROM DUAL; SELECT SESSIONTIMEZONE FROM DUAL;16-10 Copyright © Oracle Corporation, 2001. All rights reserved. EXTRACT SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL; SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE) FROM employees; WHERE manager_id = 100;16-11 Copyright © Oracle Corporation, 2001. All rights reserved. FROM_TZ SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00','3:00') FROM DUAL;16-12 Copyright © Oracle Corporation, 2001. All rights reserved. SELECT FROM DUAL; TO_TIMESTAMP and TO_TIMESTAMP_TZ SELECT TO_TIMESTAMP ('2000-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') FROM DUAL; TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM')16-13 Copyright © Oracle Corporation, 2001. All rights reserved. TO_YMINTERVAL SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') AS HIRE_DATE_YMININTERVAL FROM EMPLOYEES WHERE department_id = 20; 16-14 Copyright © Oracle Corporation, 2001. All rights reserved. TZ_OFFSET SELECT TZ_OFFSET('US/Eastern') FROM DUAL; SELECT TZ_OFFSET('Canada/Yukon') FROM DUAL; SELECT TZ_OFFSET('Europe/London') FROM DUAL; 16-16 Copyright © Oracle Corporation, 2001. All rights reserved. Summary • FROM_TZ • TO_TIMESTAMP • TO_TIMESTAMP_TZ • TO_YMINTERVAL • TZ_OFFSET • CURRENT_DATE • CURRENT_TIMESTAMP • LOCALTIMESTAMP • DBTIMEZONE • SESSIONTIMEZONE • EXTRACT In this lesson, you should have learned how to use the following functions: 16-17 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 16 Overview This practice covers using the Oracle9i datetime functions.Copyright © Oracle Corporation, 2001. All rights reserved. Enhancements to the GROUP BY Clause 17-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Use the ROLLUP operation to produce subtotal values • Use the CUBE operation to produce crosstabulation values • Use the GROUPING function to identify the row values created by ROLLUP or CUBE • Use GROUPING SETS to produce a single result set 17-3 Copyright © Oracle Corporation, 2001. All rights reserved. Review of Group Functions Group functions operate on sets of rows to give one result per group. SELECT [column,] group_function(column). . . FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; SELECT AVG(salary), STDDEV(salary), COUNT(commission_pct),MAX(hire_date) FROM employees WHERE job_id LIKE 'SA%'; EExxaammppllee::17-4 Copyright © Oracle Corporation, 2001. All rights reserved. Review of the GROUP BY Clause SELECT department_id, job_id, SUM(salary), COUNT(employee_id) FROM employees GROUP BY department_id, job_id; SELECT [column,] FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; EExxaammppllee:: SSyynnttaaxx:: group_function(column). . .17-5 Copyright © Oracle Corporation, 2001. All rights reserved. Review of the HAVING Clause • Use the HAVING clause to specify which groups are to be displayed. • You further restrict the groups on the basis of a limiting condition. SELECT [column,] group_function(column). . . FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING having_expression]; [ORDER BY column];17-6 Copyright © Oracle Corporation, 2001. All rights reserved. GROUP BY with ROLLUP and CUBE Operators • Use ROLLUP or CUBE with GROUP BY to produce superaggregate rows by cross-referencing columns. • ROLLUP grouping produces a results set containing the regular grouped rows and the subtotal values. • CUBE grouping produces a results set containing the rows from ROLLUP and cross-tabulation rows.17-7 Copyright © Oracle Corporation, 2001. All rights reserved. ROLLUP Operator • ROLLUP is an extension to the GROUP BY clause. • Use the ROLLUP operation to produce cumulative aggregates such as subtotals. SELECT [column,] group_function(column). . . FROM table [WHERE condition] [GROUP BY [ROLLUP] group_by_expression] [HAVING having_expression]; [ORDER BY column];17-8 Copyright © Oracle Corporation, 2001. All rights reserved. ROLLUP Operator Example SELECT department_id, job_id, FROM employees WHERE department_id < 60 GROUP BY ROLLUP(department_id, job_id); SUM(salary) 1 2317-9 Copyright © Oracle Corporation, 2001. All rights reserved. CUBE Operator • CUBE is an extension to the GROUP BY clause. • You can use the CUBE operator to produce crosstabulation values with a single SELECT statement. SELECT [column,] group_function(column). . . FROM table [WHERE condition] [GROUP BY [CUBE] group_by_expression] [HAVING having_expression]; [ORDER BY column];17-10 Copyright © Oracle Corporation, 2001. All rights reserved. CUBE Operator: Example SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY CUBE (department_id, job_id); 1 2 3 417-11 Copyright © Oracle Corporation, 2001. All rights reserved. GROUPING Function • The GROUPING function can be used with either the CUBE or ROLLUP operator. • Using it, you can find the groups forming the subtotal in a row. • Using it, you can differentiate stored NULL values from NULL values created by ROLLUP or CUBE. • It returns 0 or 1. SELECT [column,] group_function(column) . .,GROUPING(expr) FROM table [WHERE condition] [GROUP BY [ROLLUP][CUBE] group_by_expression] [HAVING having_expression]; [ORDER BY column];17-12 Copyright © Oracle Corporation, 2001. All rights reserved. GROUPING Function: Example SELECT department_id DEPTID, job_id JOB, SUM(salary), FROM employees WHERE department_id < 50 GROUP BY ROLLUP(department_id, job_id); GROUPING(department_id) GRP_DEPT,GROUPING(job_id) GRP_JOB17-13 Copyright © Oracle Corporation, 2001. All rights reserved. GROUPING SETS • GROUPING SETS are a further extension of the GROUP BY clause. • You can use GROUPING SETS to define multiple groupings in the same query. • The Oracle Server computes all groupings specified in the GROUPING SETS clause and combines the results of individual groupings with a UNION ALL operation. • Grouping set efficiency: – Only one pass over the base table is required. – There is no need to write complex UNION statements. – The more elements the GROUPING SETS have, the higher the performance benefit is.17-15 Copyright © Oracle Corporation, 2001. All rights reserved. GROUPING SETS: Example SELECT department_id, job_id, manager_id,avg(salary) FROM employees GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id)); 1217-17 Copyright © Oracle Corporation, 2001. All rights reserved. Composite Columns • A composite column is a collection of columns that are treated as a unit. ROLLUP (a, , d) • To specify composite columns, in the GROUP BY clause you group columns within parentheses so that the Oracle server treats them as a unit while computing ROLLUP or CUBE operations. • When used with ROLLUP or CUBE, composite columns would mean skipping aggregation across certain levels. (b,c)17-19 Copyright © Oracle Corporation, 2001. All rights reserved. Composite Columns: Example SELECT department_id, job_id, manager_id, SUM(salary) FROM employees GROUP BY ROLLUP( department_id,(job_id, manager_id)); 2 1317-21 Copyright © Oracle Corporation, 2001. All rights reserved. Concatenated Groupings • Concatenated groupings offer a concise way to generate useful combinations of groupings. • To specify concatenated grouping sets, you separate multiple grouping sets, ROLLUP, and CUBE operations with commas so that the Oracle Server combines them into a single GROUP BY clause. • The result is a cross-product of groupings from each grouping set. GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d)17-22 Copyright © Oracle Corporation, 2001. All rights reserved. Concatenated Groupings Example SELECT department_id, job_id, manager_id, SUM(salary) FROM employees GROUP BY department_id,ROLLUP(job_id),CUBE(manager_id); 1 23 417-23 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned how to: • Use the ROLLUP operation to produce subtotal values • Use the CUBE operation to produce cross-tabulation values • Use the GROUPING function to identify the row values created by ROLLUP or CUBE • Use the GROUPING SETS syntax to define multiple groupings in the same query. • Use the GROUP BY clause, to combine expressions in various ways: – Composite columns – Concatenated grouping sets17-24 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 17 Overview This practice covers the following topics: • Using the ROLLUP operator • Using the CUBE operator • Using the GROUPING function • Using GROUPING SETSCopyright © Oracle Corporation, 2001. All rights reserved. Advanced Subqueries18-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Write a multiple-column subquery • Describe and explain the behavior of subqueries when null values are retrieved • Write a subquery in a FROM clause • Use scalar subqueries in SQL • Describe the types of problems that can be solved with correlated subqueries • Write correlated subqueries • Update and delete rows using correlated subqueries • Use the EXISTS and NOT EXISTS operators • Use the WITH clause18-3 Copyright © Oracle Corporation, 2001. All rights reserved. What Is a Subquery? A subquery is a SELECT statement embedded in a clause of another SQL statement. SELECT ... FROM ... WHERE ... (SELECT ... FROM ... WHERE ...) Main query Subquery18-4 Copyright © Oracle Corporation, 2001. All rights reserved. Subqueries • The subquery (inner query) executes once before the main query. • The result of the subquery is used by the main query (outer query). SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);18-5 Copyright © Oracle Corporation, 2001. All rights reserved. SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE employee_id = 149); Using a Subquery 1050018-6 Copyright © Oracle Corporation, 2001. All rights reserved. Multiple-Column Subqueries Main query WHERE (MANAGER_ID, DEPARTMENT_ID) IN Subquery 100 90 102 60 124 50 Each row of the main query is compared to values from a multiple-row and multiple-column subquery.18-7 Copyright © Oracle Corporation, 2001. All rights reserved. Column Comparisons Column comparisons in a multiple-column subquery can be: • Pairwise comparisons • Nonpairwise comparisons 18-8 Copyright © Oracle Corporation, 2001. All rights reserved. Pairwise Comparison Subquery Display the details of the employees who are managed by the same manager and work in the same department as the employees with EMPLOYEE_ID 178 or 174. SELECT employee_id, manager_id, department_id FROM employees WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (178,174)) AND employee_id NOT IN (178,174);18-9 Copyright © Oracle Corporation, 2001. All rights reserved. Nonpairwise Comparison Subquery SELECT employee_id, manager_id, department_id FROM employees WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (174,141)) AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174,141)) AND employee_id NOT IN(174,141); Display the details of the employees who are managed by the same manager as the employees with EMPLOYEE_ID 174 or 141 and work in the same department as the employees with EMPLOYEE_ID 174 or 141.18-10 Copyright © Oracle Corporation, 2001. All rights reserved. SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a, (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b WHERE a.department_id = b.department_id AND a.salary > b.salavg; Using a Subquery in the FROM Clause18-11 Copyright © Oracle Corporation, 2001. All rights reserved. Scalar Subquery Expressions • A scalar subquery expression is a subquery that returns exactly one column value from one row. • Scalar subqueries were supported in Oracle8i only in a limited set of cases, For example : – SELECT statement (FROM, WHERE clauses) – VALUES list of an INSERT statement • In Oracle9i, scalar subqueries can be used in: – Condition and expression part of DECODE and CASE – All clauses of SELECT except GROUP BY 18-12 Copyright © Oracle Corporation, 2001. All rights reserved. Scalar Subqueries: Examples Scalar Subqueries in CASE Expressions SELECT employee_id, last_name, (CASE WHEN department_id = THEN 'Canada' ELSE 'USA' END) location FROM employees; (SELECT department_id FROM departments WHERE location_id = 1800) SSccaallaarr SSuubbqquueerriieess iinn OORRDDEERR BBYYCCllaauussee SELECT employee_id, last_name FROM employees e ORDER BY 20 (SELECT department_name FROM departments d WHERE e.department_id = d.department_id);18-14 Copyright © Oracle Corporation, 2001. All rights reserved. Correlated Subqueries Correlated subqueries are used for row-by-row processing. Each subquery is executed once for every row of the outer query. GET candidate row from outer query EXECUTE inner query using candidate row value USE values from inner query to qualify or disqualify candidate row18-15 Copyright © Oracle Corporation, 2001. All rights reserved. Correlated Subqueries SELECT column1, column2, ... FROM table1 WHERE column1 operator (SELECT colum1, column2 FROM table2 WHERE expr1 = .expr2); The subquery references a column from a table in the parent query. outer outer18-16 Copyright © Oracle Corporation, 2001. All rights reserved. SELECT last_name, salary, department_id FROM employees outer WHERE salary > Using Correlated SubqueriesEach time a row from the outer query is processed, the inner query is evaluated. Find all employees who earn more than the average salary in their department. (SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id);18-17 Copyright © Oracle Corporation, 2001. All rights reserved. Using Correlated Subqueries SELECT e.employee_id, last_name,e.job_id FROM employees e WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id); Display details of those employees who have switched jobs at least twice.18-18 Copyright © Oracle Corporation, 2001. All rights reserved. Using the EXISTS Operator • The EXISTS operator tests for existence of rows in the results set of the subquery. • If a subquery row value is found: – The search does not continue in the inner query – The condition is flagged TRUE • If a subquery row value is not found: – The condition is flagged FALSE – The search continues in the inner query18-19 Copyright © Oracle Corporation, 2001. All rights reserved. SELECT employee_id, last_name, job_id, department_id FROM employees outer WHERE EXISTS ( SELECT 'X' FROM employees WHERE manager_id = outer.employee_id); Using the EXISTS Operator Find employees who have at least one person reporting to them.18-20 Copyright © Oracle Corporation, 2001. All rights reserved. SELECT department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT 'X' FROM employees WHERE department_id = d.department_id); Using the NOT EXISTS Operator Find all departments that do not have any employees.18-21 Copyright © Oracle Corporation, 2001. All rights reserved. Correlated UPDATE Use a correlated subquery to update rows in one table based on rows from another table. UPDATE table1 alias1 SET column = (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column);18-22 Copyright © Oracle Corporation, 2001. All rights reserved. Correlated UPDATE • Denormalize the EMPLOYEES table by adding a column to store the department name. • Populate the table by using a correlated update. ALTER TABLE employees ADD(department_name VARCHAR2(14)); UPDATE employees e SET department_name = (SELECT department_name FROM departments d WHERE e.department_id = d.department_id);18-24 Copyright © Oracle Corporation, 2001. All rights reserved. DELETE FROM table1 alias1 WHERE column operator (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column); Correlated DELETE Use a correlated subquery to delete rows in one table based on rows from another table.18-25 Copyright © Oracle Corporation, 2001. All rights reserved. DELETE FROM employees E WHERE employee_id = (SELECT employee_id FROM emp_history WHERE employee_id = E.employee_id); Use a correlated subquery to delete only those rows from the EMPLOYEES table that also exist in the EMP_HISTORY table. Correlated DELETE18-26 Copyright © Oracle Corporation, 2001. All rights reserved. The WITH Clause • Using the WITH clause, you can use the same query block in a SELECT statement when it occurs more than once within a complex query. • The WITH clause retrieves the results of a query block and stores it in the user's temporary tablespace. • The WITH clause improves performance18-27 Copyright © Oracle Corporation, 2001. All rights reserved. WITH Clause: Example Using the WITH clause, write a query to display the department name and total salaries for those departments whose total salary is greater than the average salary across departments.18-28 Copyright © Oracle Corporation, 2001. All rights reserved. WITH Clause: Example dept_costs AS ( SELECT department_name, SUM(salary) AS dept_total FROM employees, departments WHERE employees.department_id = departments.department_id GROUP BY department_name), AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM ) SELECT * FROM WHERE dept_total > (SELECT FROM ) ORDER BY department_name; WITH dept_costs dept_costs avg_costdept_avg18-29 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned the following: • A multiple-column subquery returns more than one column. • Multiple-column comparisons can be pairwise or nonpairwise. • A multiple-column subquery can also be used in the FROM clause of a SELECT statement. • Scalar subqueries have been enhanced in Oracle 9i.18-30 Copyright © Oracle Corporation, 2001. All rights reserved. Summary • Correlated subqueries are useful whenever a subquery must return a different result for each candidate row. • The EXISTS operator is a Boolean operator that tests the presence of a value. • Correlated subqueries can be used with SELECT, UPDATE, and DELETE statements. • You can use the WITH clause to use the same query block in a SELECT statement when it occurs more than once18-31 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 18 Overview This practice covers the following topics: • Creating multiple-column subqueries • Writing correlated subqueries • Using the EXISTS operator • Using scalar subqueries • Using the WITH clauseCopyright © Oracle Corporation, 2001. All rights reserved. Hierarchical Retrieval19-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Interpret the concept of a hierarchical query • Create a tree-structured report • Format hierarchical data • Exclude branches from the tree structure19-3 Copyright © Oracle Corporation, 2001. All rights reserved. Sample Data from the EMPLOYEES Table19-4 Copyright © Oracle Corporation, 2001. All rights reserved. Natural Tree Structure De Hann King Hunold EMPLOYEE_ID = 100 (Parent) MANAGER_ID = 100 (Child) Whalen Kochhar Higgens Mourgos Zlotkey Rajs Davies Matos Gietz Ernst Lorentz Hartstein Goyal Abel Taylor Grant Vargas19-5 Copyright © Oracle Corporation, 2001. All rights reserved. Hierarchical Queries WWHHEERREEccoonnddiittiioonn:: expr comparison_operator expr SELECT [LEVEL], column, expr... FROM table [WHERE condition(s)] [START WITH condition(s)] [CONNECT BY PRIOR condition(s)];19-6 Copyright © Oracle Corporation, 2001. All rights reserved. Walking the Tree Starting Point • Specifies the condition that must be met • Accepts any valid condition • Using the EMPLOYEES table, start with the employee whose last name is Kochhar. ...START WITH last_name = 'Kochhar' START WITH column1 = value19-7 Copyright © Oracle Corporation, 2001. All rights reserved. Walking the Tree Direction Top down Column1 = Parent Key Column2 = Child Key Bottom up Column1 = Child Key Column2 = Parent Key Walk from the top down using the EMPLOYEES table CONNECT BY PRIOR column1 = column2 ... CONNECT BY PRIOR employee_id = manager_id19-8 Copyright © Oracle Corporation, 2001. All rights reserved. Walking the Tree: From the Bottom Up SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR manager_id = employee_id;19-9 Copyright © Oracle Corporation, 2001. All rights reserved. Walking the Tree: From the Top Down SELECT last_name||' reports to '|| PRIOR last_name "Walk Top Down" FROM employees START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id;19-10 Copyright © Oracle Corporation, 2001. All rights reserved. Ranking Rows with the LEVEL Pseudocolumn De Hann King Whalen Hunold Kochhar Higgens Mourgos Zlotkey Rajs Davies Matos Gietz Ernst Lorentz Hartstein Goyal Abel Taylor Grant Vargas Level 1 root/parent Level 2 parent/child Level 3 parent/child /leaf Level 4 leaf19-11 Copyright © Oracle Corporation, 2001. All rights reserved. Formatting Hierarchical Reports Using LEVEL and LPAD Create a report displaying company management levels, beginning with the highest level and indenting each of the following levels. COLUMN org_chart FORMAT A12 SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS org_chart FROM employees START WITH last_name='King' CONNECT BY PRIOR employee_id=manager_id 19-13 Copyright © Oracle Corporation, 2001. All rights reserved. Pruning Branches Use the WHERE clause to eliminate a node. Use the CONNECT BY clause to eliminate a branch. WHERE last_name != 'Higgins' CONNECT BY PRIOR employee_id = manager_id Kochhar AND last_name != 'Higgins' Higgins Gietz Whalen Kochhar Whalen Higgins Gietz19-14 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned the following: • You can use hierarchical queries to view a hierarchical relationship between rows in a table. • You specify the direction and starting point of the query. • You can eliminate nodes or branches by pruning.19-15 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 19 Overview This practice covers the following topics: • Distinguishing hierarchical queries from nonhierarchical queries • Performing tree walks • Producing an indented report by using the LEVEL pseudocolumn • Pruning the tree structure • Sorting the outputCopyright © Oracle Corporation, 2001. All rights reserved. Oracle 9i Extensions to DML and DDL Statements20-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Describe the features of multitable inserts • Use the following types of multitable inserts – Unconditional INSERT – Pivoting INSERT – Conditional ALL INSERT – Conditional FIRST INSERT • Create and use external tables • Name the index at the time of creating a primary key constraint20-3 Copyright © Oracle Corporation, 2001. All rights reserved. Review of the INSERT Statement • Add new rows to a table by using the INSERT statement. • Only one row is inserted at a time with this syntax. INSERT INTO table [(column [, column...])] VALUES (value [, value...]); INSERT INTO table [(column [, column...])] VALUES (value [, value...]); INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (70, 'Public Relations', 100, 1700); 1 row created.20-4 Copyright © Oracle Corporation, 2001. All rights reserved. Review of the UPDATE Statement • Modify existing rows with the UPDATE statement. • Update more than one row at a time, if required. • Specific row or rows are modified if you specify the WHERE clause. UPDATE table SET column = value [, column = value, ...] [WHERE condition]; UPDATE table SET column = value [, column = value, ...] [WHERE condition]; UPDATE employees SET department_id = 70 WHERE employee_id = 142; 1 row updated.20-5 Copyright © Oracle Corporation, 2001. All rights reserved. Overview of Multitable INSERT Statements • The INSERT…SELECT statement can be used to insert rows into multiple tables as part of a single DML statement. • Multitable INSERT statements can be used in data warehousing systems to transfer data from one or more operational sources to a set of target tables. • They provide significant performance improvement over: – Single DML versus multiple INSERT.. SELECT statements – Single DML versus a procedure to do multiple inserts using IF...THEN syntax20-7 Copyright © Oracle Corporation, 2001. All rights reserved. Types of Multitable INSERT Statements Oracle9i introduces the following types of multitable insert statements: • Unconditional INSERT • Conditional ALL INSERT • Conditional FIRST INSERT • Pivoting INSERT20-8 Copyright © Oracle Corporation, 2001. All rights reserved. Multitable INSERT Statements INSERT [ALL] [conditional_insert_clause] [insert_into_clause values_clause] (subquery) [ALL] [FIRST] [WHEN condition THEN] [insert_into_clause values_clause] [ELSE] [insert_into_clause values_clause] conditional_insert_clause Syntax20-10 Copyright © Oracle Corporation, 2001. All rights reserved. Unconditional INSERT ALL • Select the EMPLOYEE_ID, HIRE_DATE, SALARY, and MANAGER_ID values from the EMPLOYEES table for those employees whose EMPLOYEE_ID is greater than 200. • Insert these values into the SAL_HISTORY and MGR_HISTORY tables using a multitable INSERT. SELECT employee_id ,hire_date , salary , manager_id FROM employees WHERE employee_id > 200; INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL) EMPID H I R E D A T E SAL M G R 20-11 Copyright © Oracle Corporation, 2001. All rights reserved. Conditional INSERT ALL • Select the EMPLOYEE_ID, HIRE_DATE, SALARY and MANAGER_ID values from the EMPLOYEES table for those employees whose EMPLOYEE_ID is greater than 200. • If the SALARY is greater than $10,000, insert these values into the SAL_HISTORY table using a conditional multitable INSERT statement. • If the MANAGER_ID is greater than 200, insert these values into the MGR_HISTORY table using a conditional multitable INSERT statement. 20-12 Copyright © Oracle Corporation, 2001. All rights reserved. Conditional INSERT ALL SELECT employee_id ,hire_date , salary , manager_id FROM employees WHERE employee_id > 200; 4 rows created. SAL EMPID H I R E D A T E INSERT ALL WHEN SAL > 10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR > 200 THEN INTO mgr_history VALUES(EMPID,MGR,SAL) MGR 20-13 Copyright © Oracle Corporation, 2001. All rights reserved. Conditional FIRST INSERT • Select the DEPARTMENT_ID , SUM(SALARY) and MAX(HIRE_DATE) from the EMPLOYEES table. • If the SUM(SALARY) is greater than $25,000 then insert these values into the SPECIAL_SAL, using a conditional FIRST multitable INSERT. • If the first WHEN clause evaluates to true, the subsequent WHEN clauses for this row should be skipped. • For the rows that do not satisfy the first WHEN condition, insert into the HIREDATE_HISTORY_00, or HIREDATE_HISTORY_99, or HIREDATE_HISTORY tables, based on the value in the HIRE_DATE column using a conditional multitable INSERT.20-14 Copyright © Oracle Corporation, 2001. All rights reserved. Conditional FIRST INSERT INSERT FIRST WHEN SAL > 25000 THEN INTO special_sal VALUES(DEPTID, SAL) WHEN HIREDATE like ('%00%') THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE) WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID, HIREDATE) ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE) SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE FROM employees GROUP BY department_id;20-15 Copyright © Oracle Corporation, 2001. All rights reserved. Pivoting INSERT • Suppose you receive a set of sales records from a nonrelational database table, SALES_SOURCE_DATA in the following format: EMPLOYEE_ID, WEEK_ID, SALES_MON, SALES_TUE, SALES_WED, SALES_THUR, SALES_FRI • You would want to store these records in the SALES_INFO table in a more typical relational format: EMPLOYEE_ID, WEEK, SALES • Using a pivoting INSERT, convert the set of sales records from the nonrelational database table to relational format.20-16 Copyright © Oracle Corporation, 2001. All rights reserved. Pivoting INSERT INSERT ALL INTO sales_info VALUES (employee_id,week_id,sales_MON) INTO sales_info VALUES (employee_id,week_id,sales_TUE) INTO sales_info VALUES (employee_id,week_id,sales_WED) INTO sales_info VALUES (employee_id,week_id,sales_THUR) INTO sales_info VALUES (employee_id,week_id, sales_FRI) SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI FROM sales_source_data; 20-18 Copyright © Oracle Corporation, 2001. All rights reserved. External Tables • External tables are read-only tables in which the data is stored outside the database in flat files. • The metadata for an external table is created using a CREATE TABLE statement. • With the help of external tables, Oracle data can be stored or unloaded as flat files. • The data can be queried using SQL but you cannot use DML and no indexes can be created.20-19 Copyright © Oracle Corporation, 2001. All rights reserved. Creating an External Table • Use the external_table_clause along with the CREATE TABLE syntax to create an external table. • Specify ORGANIZATION as EXTERNAL to indicate that the table is located outside the database. • The external_table_clause consists of the access driver TYPE, external_data_properties, and the REJECT LIMIT. • The external_data_properties consist of the following: – DEFAULT DIRECTORY – ACCESS PARAMETERS – LOCATION20-20 Copyright © Oracle Corporation, 2001. All rights reserved. Example of Creating an External Table Create a DIRECTORY object that corresponds to the directoryon the file system where the external data source resides. CREATE DIRECTORY emp_dir AS '/flat_files' ;20-21 Copyright © Oracle Corporation, 2001. All rights reserved. Example of Creating an External Table CREATE TABLE oldemp ( empno NUMBER, empname CHAR(20), birthdate DATE) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY emp_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE BADFILE 'bad_emp' LOGFILE 'log_emp' FIELDS TERMINATED BY ',' (empno CHAR, empname CHAR, birthdate CHAR date_format date mask "dd-mon-yyyy")) LOCATION ('emp1.txt')) PARALLEL 5 REJECT LIMIT 200;20-23 Copyright © Oracle Corporation, 2001. All rights reserved. Querying External Tables SELECT * FROM oldemp emp1.txt20-24 Copyright © Oracle Corporation, 2001. All rights reserved. CREATE INDEX with CREATE TABLE Statement CREATE TABLE NEW_EMP (employee_id NUMBER(6) PRIMARY KEY USING INDEX (CREATE INDEX emp_id_idx ON NEW_EMP(employee_id)), first_name VARCHAR2(20), last_name VARCHAR2(25)); Table created. SELECT , FROM USER_INDEXES WHERE TABLE_NAME = 'NEW_EMP'; INDEX_NAME TABLE_NAME --------------------------------------------EMP_ID_IDX NEW_EMP INDEX_NAME TABLE_NAME20-25 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned how to use the following enhancements to DML and DDL statements: • The INSERT…SELECT statement can be used to insert rows into multiple tables as part of a single DML statement. • External tables can be created. • Indexes can be named using the CREATE INDEX statement along with the CREATE TABLE statement.20-26 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 20 Overview This practice covers the following topics: • Writing unconditional INSERT • Writing conditional ALL INSERT • Pivoting INSERT • Creating indexes along with the CREATE TABLE commandCopyright © Oracle Corporation, 2001. All rights reserved. UUssiinngg SSQLL**PPlluussC-2 Copyright © Oracle Corporation, 2001. All rights reserved. OObbjjeeccttiivveess After completing this appendix, you should be able to do the following: • Log in to SQL*Plus • Edit SQL commands • Format output using SQL*Plus commands • Interact with script files C-3 Copyright © Oracle Corporation, 2001. All rights reserved. SSQQLL aanndd SSQQLL**PPlluuss IInntteerraaccttiioonn SQL*Plus Buffer Server SQL statements Query results SQL scriptsC-4 Copyright © Oracle Corporation, 2001. All rights reserved. SQL Statements versus SQL*Plus Commands SQL statements SQL • A language • ANSI standard • Keywords cannot be abbreviated • Statements manipulate data and table definitions in the database SQL*Plus • An environment • Oracle proprietary • Keywords can be abbreviated • Commands do not allow manipulation of values in the database SQL buffer SQL*Plus commands SQL*Plus bufferC-5 Copyright © Oracle Corporation, 2001. All rights reserved. • Log in to SQL*Plus. • Describe the table structure. • Edit your SQL statement. • Execute SQL from SQL*Plus. • Save SQL statements to files and append SQL statements to files. • Execute saved files. • Load commands from file to buffer to edit. OOvveerrvviieeww ooff SSQQLL**PPlluussC-6 Copyright © Oracle Corporation, 2001. All rights reserved. LLooggggiinngg IInn ttoo SSQQLL**PPlluuss • From a Windows environment: • From a command line: sqlplus [username[/password [@database]]]C-7 Copyright © Oracle Corporation, 2001. All rights reserved. DDiissppllaayyiinngg TTaabbllee SSttrruuccttuurree Use the SQL*Plus DESCRIBE command to display the structure of a table. DESC[RIBE] tablenameC-8 Copyright © Oracle Corporation, 2001. All rights reserved. DDiissppllaayyiinngg TTaabbllee SSttrruuccttuurree SQL> departments Name Null? Type -------------------------------------------DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) DESCRIBEC-9 Copyright © Oracle Corporation, 2001. All rights reserved. SSQQLL**PPlluuss EEddiittiinngg CCoommaannddss • A[PPEND] text • C[HANGE] /old /new • C[HANGE] /text /• CL[EAR] BUFF[ER] • DEL • DEL n • DEL m n C-10 Copyright © Oracle Corporation, 2001. All rights reserved. SSQQLL**PPlluuss EEddiittiinngg CCoommaannddss • I[NPUT] • I[NPUT] text • L[IST] • L[IST] n • L[IST] m n • R[UN] • n • n text • 0 text C-11 Copyright © Oracle Corporation, 2001. All rights reserved. UUssiinngg LLIISSTT,, nn,, aanndd AAPPPPEENNDD 1 SELECT last_name 2* FROM employees SQL> LIST SQL> 1 1* SELECT last_name SQL> A , job_id 1* SELECT last_name, job_id SQL> L 1 SELECT last_name, job_id 2* FROM employeesC-12 Copyright © Oracle Corporation, 2001. All rights reserved. UUssiinngg tthhee CCHHAANNGGEECCoommaanndd SQL> L 1* SELECT * from employees SQL> L 1* SELECT * from departments SQL> c/employees/departments 1* SELECT * from departmentsC-13 Copyright © Oracle Corporation, 2001. All rights reserved. SSQQLL**PPlluuss FFiillee CCoommaannddss • SAVE filename • GET filename • START filename • @filename • EDIT filename • SPOOL filename • EXIT C-14 Copyright © Oracle Corporation, 2001. All rights reserved. UUssiinngg tthhee SSAAVVEE aanndd SSTTAARRTTCCoommaannddss Created file my_query SQL> START my_query SQL> L 1 SELECT last_name, manager_id, department_id 2* FROM employees SQL> SAVE my_query LAST_NAME MANAGER_ID DEPARTMENT_ID ------------------------------------------------King 90 Kochhar 100 90 ... 20 rows selected.C-15 Copyright © Oracle Corporation, 2001. All rights reserved. SSuummaarryy Use SQL*Plus as an environment to: • Execute SQL statements • Edit SQL statements • Format output • Interact with script files Copyright © Oracle Corporation, 2001. All rights reserved. Writing Advanced ScriptsD-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this appendix, you should be able to do the following: • Describe the types of problems that are solved by using SQL to generate SQL • Write a script that generates a script of DROP TABLE statements • Write a script that generates a script of INSERT INTO statementsD-3 Copyright © Oracle Corporation, 2001. All rights reserved. Using SQL to Generate SQL Data dictionary SQL script SQL • SQL can be used to generate scripts in SQL • The data dictionary – Is a collection of tables and views that contain database information – Is created and maintained by the Oracle server D-4 Copyright © Oracle Corporation, 2001. All rights reserved. Creating a Basic Script SELECT 'CREATE TABLE ' || table_name || '_test ' || 'AS SELECT * FROM ' || table_name ||' WHERE 1=2;' AS "Create Table Script" FROM user_tables; D-5 Copyright © Oracle Corporation, 2001. All rights reserved. SPOOL dropem.sql SPOOL OFF Controlling the Environment Set system variables to appropriate values. Set system variables back to the default value. SQL STATEMENT SET ECHO OFF SET FEEDBACK OFF SET PAGESIZE 0 SET FEEDBACK ON SET PAGESIZE 24 SET ECHO OND-6 Copyright © Oracle Corporation, 2001. All rights reserved. The Complete Picture SET ECHO OFF SET FEEDBACK OFF SET PAGESIZE 0 SELECT 'DROP TABLE ' || object_name || ';' FROM user_objects WHERE object_type = 'TABLE' /SET FEEDBACK ON SET PAGESIZE 24 SET ECHO OND-7 Copyright © Oracle Corporation, 2001. All rights reserved. Dumping the Contents of a Table to a File SET HEADING OFF ECHO OFF FEEDBACK OFF SET PAGESIZE 0 SELECT 'INSERT INTO departments_test VALUES (' || department_id || ', ''' || department_name || ''', ''' || location_id || ''');' AS "Insert Statements Script" FROM departments /SET PAGESIZE 24 SET HEADING ON ECHO ON FEEDBACK OND-8 Copyright © Oracle Corporation, 2001. All rights reserved. Dumping the Contents of a Table to a File Source '''X''' '''' ''''||department_name||'''' ''', ''' ''');' Result 'X' ''Administration' ',' ');D-9 Copyright © Oracle Corporation, 2001. All rights reserved. Generating a Dynamic Predicate COLUMN my_col NEW_VALUE dyn_where_clause SELECT DECODE('&&deptno', null, DECODE ('&&hiredate', null, ' ', 'WHERE hire_date=TO_DATE('''||'&&hiredate'',''DD-MON-YYYY'')'), DECODE ('&&hiredate', null, 'WHERE department_id = ' || '&&deptno', 'WHERE department_id = ' || '&&deptno' || ' AND hire_date = TO_DATE('''||'&&hiredate'',''DD-MON-YYYY'')')) AS my_col FROM dual; SELECT last_name FROM employees &dyn_where_clause;D-11 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this appendix, you should have learned the following: • You can write a SQL script to generate another SQL script. • Script files often use the data dictionary. • You can capture the output in a file.D-12 Copyright © Oracle Corporation, 2001. All rights reserved. Practice D Overview This practice covers the following topics: • Writing a script to describe and select the data from your tables • Writing a script to revoke user privilegesCopyright © Oracle Corporation, 2001. All rights reserved.E-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this appendix, you should be able to do the following: • Describe the Oracle Server architecture and its main components • List the structures involved in connecting a user to an Oracle instance • List the stages in processing: – Queries – DML statements – CommitsE-3 Copyright © Oracle Corporation, 2001. All rights reserved. Database Redo log files Control files Data files OOvveerrvviieeww User process Server process PGA Archived log files Parameter file Password file Instance SGA Redo log buffer Data buffer cache Shared pool Data dict. cache Library cache SMON DBW0 PMON CKPT LGWR OthersE-5 Copyright © Oracle Corporation, 2001. All rights reserved. OOrraaccllee DDaattaabbaassee FFiilleess Database Redo log files Control files Data files Archived log files Parameter file Password fileE-6 Copyright © Oracle Corporation, 2001. All rights reserved. OOtthheerr KKeeyy PPhhyyssiiccaall SSttrruuccttuurreess Archived log files Database Archived log files Parameter file Password fileE-7 Copyright © Oracle Corporation, 2001. All rights reserved. OOrraaccllee IInnssttaannccee Background processes An Oracle instance: • Is a means to access an Oracle database • Always opens one and only one database Memory structures Instance SGA Redo log buffer Data buffer cache Shared pool SMON DBW0 PMON CKPT LGWR OthersE-9 Copyright © Oracle Corporation, 2001. All rights reserved. PPrroocceessssiinngg aa SSQQLL SSttaatteemeenntt • Connect to an instance using: – The user process – The server process • The Oracle Server components that are used depend on the type of SQL statement: – Queries return rows – DML statements log changes – Commit ensures transaction recovery • Some Oracle Server components do not participate in SQL statement processing. E-10 Copyright © Oracle Corporation, 2001. All rights reserved. CCoonnnneeccttiinngg ttoo aann IInnssttaannccee User Server User Server ClientUser Server Oracle server Application server Server BrowserE-12 Copyright © Oracle Corporation, 2001. All rights reserved. PPrroocceessssiinngg aa QQuueerryy • Parse: – Search for identical statement – Check syntax, object names, and privileges – Lock objects used during parse – Create and store execution plan • Execute: Identify rows selected • Fetch: Return rows to user process E-13 Copyright © Oracle Corporation, 2001. All rights reserved. TThhee SShhaarreedd PPooooll • The library cache contains the SQL statement text, parsed code, and execution plan. • The data dictionary cache contains table, column, and other object definitions and privileges. • The shared pool is sized by SHARED_POOL_SIZE. Data dictionary cache Library cache Shared poolE-15 Copyright © Oracle Corporation, 2001. All rights reserved. DDaattaabbaassee BBuuffffeerr CCaacchhee • Stores the most recently used blocks • Size of a buffer based on DB_BLOCK_SIZE • Number of buffers defined by DB_BLOCK_BUFFERS Data buffer cacheE-16 Copyright © Oracle Corporation, 2001. All rights reserved. PPrrooggrraam GGlloobbaall AArreeaa ((PPGGAA)) • Not shared • Writable only by the server process • Contains: – Sort area – Session information – Cursor state – Stack space Server process PGAE-17 Copyright © Oracle Corporation, 2001. All rights reserved. 1 1 PPrroocceessssiinngg aa DDMMLL SSttaatteemeenntt 2 3 4 UPDATE emp ... User process Server process Database Redo log files Control files Data files SGA Redo log buffer Data buffer cache Shared poolE-19 Copyright © Oracle Corporation, 2001. All rights reserved. RReeddoo LLoogg BBuuffffeerr • Has its size defined by LOG_BUFFER • Records changes made through the instance • Is used sequentially • Is a circular buffer Redo log bufferE-20 Copyright © Oracle Corporation, 2001. All rights reserved. RRoollllbbaacckk SSeeggmeenntt DML statement Old image New image Rollback segment TableE-21 Copyright © Oracle Corporation, 2001. All rights reserved. SGA Redo log buffer Data buffer cache Shared pool LGWR Instance Database Redo log files Control files Data files CCOOMMMMIITTPPrroocceessssiinngg 1 2 3 4 User process Server processE-23 Copyright © Oracle Corporation, 2001. All rights reserved. LLoogg Wrriitteerr ((LLGGWWRR)) LGWR writes when: • There is a commit • The redo buffer log is one-third full • There is more than 1 MB of redo • Before DBW0 writes SGA Redo log buffer Data buffer cache Shared pool LGWR Database Redo log files Control files Data files E-24 Copyright © Oracle Corporation, 2001. All rights reserved. Other Instance Processes • Other required processes: – Database Writer (DBW0) – Process Monitor (PMON) – System Monitor (SMON) – Checkpoint (CKPT) • The archive process (ARC0) is usually created in a production database E-25 Copyright © Oracle Corporation, 2001. All rights reserved. DDaattaabbaassee Wrriitteerr ((DDBBWW00)) DBW0 writes when: • There are many dirty buffers • There are few free buffers • Timeout occurs • Checkpoint occurs SGA Redo log buffer Data buffer cache Shared pool Database Redo log files Control files Data files DBW0E-26 Copyright © Oracle Corporation, 2001. All rights reserved. SMON: System Monitor • Automatically recovers the instance: – Rolls forward changes in the redo logs – Opens the database for user access – Rolls back uncommitted transactions • Coalesces free space • Deallocates temporary segmentsE-27 Copyright © Oracle Corporation, 2001. All rights reserved. PMON: Process Monitor Cleans up after failed processes by: • Rolling back the transaction • Releasing locks • Releasing other resourcesE-28 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this appendix, you should have learned how to: • Identify database files: data files, control files, online redo logs • Describe SGA memory structures: DB buffer cache, shared SQL pool, and redo log buffer • Explain primary background processes: DBW0, LGWR, CKPT, PMON, SMON, and ARC0 • List SQL processing steps: parse, execute, fetch