Oracle 9i - SQL

Tata Consultancy Services ltd.

19 February 2009

. 1 1

Relational Database

. 2

. 3

. 4

. 5

. 6

. 7

Creating and Managing Tables

Tata Consultancy Services ltd.

19 February 2009

. 8 8

Database Objects

. 9

Data Types

10

. 10

The CREATE TABLE Statement

CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);

You must have: CREATE TABLE privilege A storage area You specify: Table name Column name, column data type, and column size

11

. 11

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 information

12

. 12

Data Dictionary Views

USER_ These views contain information about objects owned by the user ALL_ These views contain information about all of the tables (object tables and relational tables) accessible to the user. DBA_ These views are restricted views, which can be accessed only by people who have been assigned the DBA role. V$ These views are dynamic performance views, database server performance, memory, and locking.

13

. 13

Querying the Data Dictionary

See the names of tables owned by the user. SELECT table_name FROM user_tables View distinct object types owned by the user. SELECT DISTINCT object_type FROM user_objects View tables, views, synonyms, and sequences owned by the user. SELECT * FROM user_catalog

14

. 14

The ALTER and DELETE TABLE Statements

Use the ALTER TABLE statement to: Add a new column Modify an existing column Define a default value for the new column Drop a column ADD Clause to add a new column MODIFY to change the data type of a column DROP COLUMN to drop a column DROP TABLE tablename drops a table TRUNCATE TABLE removes all the rows. Cannot be rollbacked.

15

. 15

Including Constraints

Tata Consultancy Services ltd.

19 February 2009

. 1616

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 CHECK A UNIQUE or PRIMARY KEY index is automatically created if you enable a UNIQUE key or PRIMARYKEY constraint.

17

. 17

Constraints
Example1 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)); Example 2 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)); 18

. 18

The CHECK Constraint

Defines a condition that each row must satisfy The following expressions are not allowed: References to CURRVAL, NEXTVAL, and ROWNUM , Sysdate etc Queries that refer to other values in other rows ..., salary NUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0),...

19

. 19

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);

20

. 20

DROPPING and DISABLING Constraints

ALTER TABLE table DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraint [CASCADE]; ALTER TABLE table DISABLE CONSTRAINT constraint CASCADE;

21

. 21

Manipulating Data

Tata Consultancy Services ltd.

19 February 2009

. 2222

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.

23

. 23

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.

24

. 24

Insert Statement
Insertion of rows from data in other Tables are possible. Requires the data type and no of columns to be consistent Values clause should not be used Example INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE %REP%;

25

. 25

The UPDATE Statement Syntax

Modify existing rows with the UPDATE statement. Update more than one row at a time, if required

26

. 26

The DELETE Statement

27

. 27

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); With Check option Default Value
28 . 28

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 applications

29

. 29

Syntax
MERGE INTO table_name table_alias USING (table|view|sub_query) 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);
30 . 30

Example
MERGE INTO copy_emp 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);
. 31

31

32

. 32

33

. 33

34

. 34

35

. 35

36

. 36

37

. 37

38

. 38

39

. 39

40

. 40

41

. 41

42

. 42

43

. 43

44

. 44

45

. 45

46

. 46

47

. 47

48

. 48

49

. 49

50

. 50

51

. 51

52

. 52

Character Functions

53

. 53

54

. 54

55

. 55

56

. 56

57

. 57

58

. 58

59

. 59

60

. 60

61

. 61

62

. 62

63

. 63

64

. 64

65

. 65

66

. 66

67

. 67

68

. 68

69

. 69

70

. 70

71

. 71

72

. 72

73

. 73

74

. 74

75

. 75

76

. 76

77

. 77

78

. 78

79

. 79

80

. 80

81

. 81

82

. 82

83

. 83

Creating Views

Tata Consultancy Services ltd.

19 February 2009

. 8484

Why Use Views?

To restrict data access To make complex queries easy To provide data independence To present different views of the same data

85

. 85

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]]; DROP VIEW view drops the View

86

. 86

Creating a Complex Views

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;

87

. 87

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 You cannot modify data in a view if it contains: Group functions A GROUP BY clause The DISTINCT keyword Columns defined by expressions You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view

88

. 88

In-Line Views
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. Top-N Analysis Top-N queries ask for the n largest or smallest values of a column. SELECT ROWNUM as RANK, last_name, salary FROM (SELECT last_name,salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 3

89

. 89

90

. 90

Types of Joins
EquiJoin Non-equijoin Outer join Self join

91

. 91

92

. 92

93

. 93

94

. 94

95

. 95

96

. 96

97

. 97

98

. 98

99

. 99

100

. 100

101

. 101

102

. 102

103

. 103

104

. 104

105

. 105

106

. 106

107

. 107

108

. 108

109

. 109

110

. 110

111

. 111

Producing Readable Output with iSQL*Plus

Tata Consultancy Services ltd.

19 February 2009

112 . 112

Substitution Variables
Use iSQL*Plus substitution variables to: Temporarily store values Single ampersand (&) Double ampersand (&&) DEFINE command Pass variable values between SQL statements Dynamically alter headers and footers

113

. 113

iSQL command
Set verify on/off Set echo on/off Show systemVariable Other command variable Heading on/off Long{80|n} TTitle on/off BTitle on/off

114

. 114

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. 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.

115

. 115

Example

116

. 116

Script
SET FEEDBACK OFF TTITLE Employee|Report BTITLE Confidential BREAK ON job_id COLUMN job_id HEADING Job|Category COLUMN last_name HEADING Employee COLUMN salary HEADING Salary FORMAT $99,999.99 REM ** Insert SELECT statement SELECT job_id, last_name, salary FROM employees WHERE salary < 15000 ORDER BY job_id, last_name / REM clear all formatting commands ... COLUMN job_id CLEAR COLUMN last_name CLEAR COLUMN salary CLEAR CLEAR BREAK ...

117

. 117

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 statement 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 crashes

118

. 118

Implicit Transaction Processing

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.

119

. 119

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. Locking Prevent destructive interaction between concurrent transactions Require no user action explicit locking and implicit locking

120

. 120

Implicit Locking
Two lock modes: Exclusive: Locks out other users Share: Allows other users to access High level of data concurrency: DML: Table share, row exclusive Queries: No locks required DDL: Protects object definitions Locks held until commit or rollback

121

. 121

Sequences, Index and Synonyms

122

. 122

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 memory

123

. 123

Sequence

NEXTVAL CURRVAL 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 ALTER SEQUENCE DROP SEQUENCE

124

. 124

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

125

. 125

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 percent of the rows

126

. 126

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. 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. DROP INDEX

127

. 127

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 DROP SYNONYM

128

. 128

Controlling User Access

Tata Consultancy Services ltd.

19 February 2009

129 . 129

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 sequences

130

. 130

Creating Users
CREATE USER user IDENTIFIED BY password; System Privileges GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...]; An application developer, for example, may have the following system privileges: CREATE SESSION CREATE TABLE CREATE SEQUENCE CREATE VIEW CREATE PROCEDURE

131

. 131

Creating and Granting Privileges to a Role

Create a role CREATE ROLE role1; Grant privileges to a role GRANT create table, create view TO role1; Grant a role to users GRANT role1 TO user1, user2;

132

. 132

Object Privileges

133

. 133

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 owners object.

GRANT object_priv [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION]; 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];
134 . 134

Thank you

Tata Consultancy Services ltd.

19 February 2009

135 . 135