oracle sql

Siemens GreeceD.I.P.2.2006

Introduction to Oracle SQL

Oracle Training

Instructor



• Writing a BASIC SQL statement• Retrieving Data Using the SQL SELECT Statement• Restricting and Sorting Data• Using Single Row Functions to Customize Reports• Reporting Aggregated Data Using the Group Functions• Displaying Data From Multiple Tables• Subqueries• Using the SET Operators• Manipulating Data• Create and Manage Tables• Creating and Manage other Schema Objects• Controlling User Access• Hierarchical Data Retrieval• Data Dictionary Views

Training Index



• Querying data– Select

• Inserting, updating, deleting rows in a table (DML)– Insert– Update– Delete

• Creating, replacing, altering, dropping objects (DDL)– Create– Alter– Drop– Rename– Truncate

• Controlling access to the database and its objects (DCL)– Grant– Revoke

• Controlling transactions – Commit– Rollback– Savepoint

• Guaranteeing database consistency and integrity.

Introduction - Use of SQL



Introduction - Demo Schema

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ---------

7839 KING PRESIDENT 17-NOV-81 5000 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7900 JAMES CLERK 7698 03-DEC-81 950 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ---------

7839 KING PRESIDENT 17-NOV-81 5000 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7900 JAMES CLERK 7698 03-DEC-81 950 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

EMP

DEPT

DEPTNO DNAME LOC

--------- -------------- ----------

10 ACCOUNTING NEW

YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

DEPTNO DNAME LOC

--------- -------------- ----------

10 ACCOUNTING NEW

YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

GRADE LOSAL HISAL

--------- --------- ---------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

GRADE LOSAL HISAL

--------- --------- ---------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

SALGRADE



• Log in to SQL*Plus.– From Windows environment:

– From command line: sqlplus [username[/password[@database]]]

• Describe the table structure.– Describe tablename

• Execute SQL from SQL*Plus.

• Save SQL statements to files.– SPOOL filename

• Execute saved files.– START filename

– @ filename

• Load commands from file to buffer to edit.– EDIT filename

SQL *PLUS Overview



SELECT [DISTINCT] {*, column [alias],...}

FROM table;

• SQL statements are not case sensitive.

• SQL statements can be on one ormore lines.

• Keywords cannot be abbreviated or split across lines.

• Clauses are usually placed on separate lines.

• Tabs and indents are used to enhance readability.

Writing Basic SQL statements – Basic Select Statement



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

Operator Description

* Multiply

/ Divide

- Subtract

+ Add

Writing Basic SQL statements – Arithmetic Expressions



• A null is a value that is unavailable, unassigned, unknown, or inapplicable.

• A null is not the same as zero or a blank space.

• Arithmetic expressions containing a null value evaluate to null.

Writing Basic SQL statements – Null Values



• Renames a column heading

• Is useful with calculations

• Immediately follows column name; optional AS keyword between column name and alias

• Requires double quotation marks if it contains spaces or special characters or is case sensitive

Writing Basic SQL statements – Column Alias



• Concatenates columns or character strings to other columns

• Is represented by two vertical bars (||)

• Creates a resultant column that is a character expression

Writing Basic SQL statements – Concatenation Operator



• A literal 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.

Writing Basic SQL statements – Literal Character Strings



• Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause.

Writing Basic SQL statements – Distinct keyword



Restricting and Sorting Data - Limiting Rows Selected

SELECT [DISTINCT] {*| column [alias], ...}

FROM table

[WHERE condition(s)];

• Restrict the rows returned by using the WHERE clause.

• The WHERE clause follows the FROM clause.



• 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 depends on system configuration.

Restricting and Sorting Data - Limiting Rows Selected



Operator Meaning

= Equal to

> Greater Than

>= Greater Then or Equal to

< Less Than

<= Less Than or Equal to

<> Not Equal to

Between …. And …. Between two values (inclusive)

In (item1,item2,…,itemn) Match any of a list of values

Is Null Is a null value

Restricting and Sorting Data – Comparison Operators



• Use the LIKE operator 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.

Restricting and Sorting Data – LIKE Operator



• Test for null values with the IS NULL operator.

Restricting and Sorting Data – IS NULL Operator



Restricting and Sorting Data – Logical Operators

Operator Meaning

AND Returns TRUE if both component conditions are TRUE (2)

OR Returns TRUE if either component condition is TRUE (3)

NOT Returns TRUE if the following condition is FALSE (1)



SELECT [DISTINCT] {*| column [alias], ...}

FROM table

[WHERE condition(s)];

[ORDER BY {column | alias | order in select [ASC|DESC], ...} ]

• Sort rows with the ORDER BY clause– ASC: ascending order, default– DESC: descending order

• The ORDER BY clause comes last in the SELECT statement.

Restricting and Sorting Data – ORDER BY Clause



• Manipulate data items

• Accept arguments and return one value

• Act on each row returned

• Return one result per row

• May modify the datatype

• Can be nested

Single-Row Functions



ConversionConversion

CharacterCharacter

NumberNumber

DateDate

GeneralGeneralSingle-row Single-row functionsfunctions

Single-Row Functions - Categories



Single-Row Functions – Character Functions

• Case conversion functions– LOWER (converts to lower case)– UPPER (converts to upper case)– INITCAP (converts words initial letter to upper case and the rest to lower case)

• Character manipulation functions– CONCAT (concatenate)– SUBSTR (get a substring)– LENGTH (counts the characters)– INSTR (find the position of a substring)– LPAD/RDAP (add characters in the left/right)– TRIM (remove characters from the start or the end of string )



Single-Row Functions – Number Functions

• Number functions– ROUND (round value to specific decimal)– TRUNC (truncate value to specific decimal)– MOD (remainder of division)– ABS (absolute value)



Single-Row Functions – Working with Dates

• Oracle stores dates in an internal numeric format: century, year, month, day, hours, minutes, seconds.

• The default date format depends on system configuration.

• SYSDATE is a function returning date and time.

• DUAL is a dummy table used to view SYSDATE.

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



Single-Row Functions – Date Functions

• Date functions– MONTHS_BETWEEN (number of months between two dates)– ADD_MONTHS (add calendar months to date)– NEXT_DATE (next day of the date specified)– LAST_DATE (last day of the month)– ROUND (round date )– TRUNC (truncate date)



Single-Row Functions – Data Type Conversion

• Implicitly Conversion– From VARCHAR/CHAR to NUMBER– From VARCHAR/CHAR to DATE

• Explicitly Conversion– VARCHAR/CHAR to NUMBER using function TO_NUMBER– NUMBER to VARCHAR/CHAR using function TO_CHAR– VARCHAR/CHAR to DATE using function TO_DATE– DATE to VARCHAR/CHAR using function TO_CHAR

Formta Model Meaning

YYYY Full year in number

YEAR Year spellout

MM Two-digit value for month

MONTH Full name of month

DY Three-letter abbreviation of the day of the week

DAY Full name of the day



Single-Row Functions – NVL Function

• Converts null to an actual value– Datatypes that can be used are date, character, and number.– Datatypes must match



Single-Row Functions – DECODE Function

• Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-

ELSE statement



Aggregating Data Using Group Functions

• Group functions operate on sets of rows to give one result per group.

• Group functions ignore null values in the column.

• Group functions can be nested.



Aggregating Data Using Group Functions – Group Functions

• AVG (average value)

• COUNT (counts rows)

• MAX (maximum value)

• MIN (minimum value)

• STDDEV (standard deviation)

• SUM (sum of values)

• VARIANCE (variance)



Aggregating Data Using Group Functions – GROUP BY Clause

SELECT [column,] group_function(column)

FROM table

[WHERE condition]

[GROUP BY column]

[ORDER BY column];

• Divide rows in a table into smaller groups by using the GROUP BY clause.

• All columns in the SELECT list that are not in group functions must be in the GROUP BY clause.

• The GROUP BY column does not have to be in the SELECT list.



Aggregating Data Using Group Functions – HAVING Clause

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

• You cannot use the WHERE clause to restrict groups.• Use the HAVING clause to restrict groups

– Rows are grouped.– The group function is applied.– Groups matching the HAVING clause are displayed.



Aggregating Data Using Group Functions – ROLLUP CUBE Operator

• Use ROLLUP or CUBE with GROUP BY to produce superaggregate rows by cross-referencing columns.

• ROLLUP grouping produces a result set containing the regular grouped rows and the subtotal values.

• CUBE grouping produces a result set containing the rows from ROLLUP and cross-tabulation rows.



Aggregating Data Using Group Functions – ROLLUP Operator


FROM table

[WHERE condition]

[GROUP BY [ROLLUP] group_by_expression]


[ORDER BY column];

• ROLLUP is an extension to the GROUP BY clause.

• You can use the ROLLUP operation to produce cumulative aggregates

such as subtotals.



Aggregating Data Using Group Functions – CUBE Operator


FROM table

[WHERE condition]

[GROUP BY [CUBE] group_by_expression]


[ORDER BY column];

• CUBE is an extension to the GROUP BY clause.

• You can use the CUBE operator to produce cross-tabulation values.



Displaying Data From Multiple Tables – Data Type Conversion

Oracle

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;ANSI 99

SELECT table1.column, table2.columnFROM table1 [CROSS JOIN table2] |[NATURAL JOIN table2] |[JOIN table2 USING (column)] |[JOIN table2 ON (table1.column= table2.column)] |[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column= table2.column)];

• 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 or use table alias instead.



Displaying Data From Multiple Tables – Cartesian Product

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



Displaying Data From Multiple Tables – Types of Join

ORACLE ANSI 99

Equijoin NATURAL/INNER

Non-Equijoin JOIN USING

Outer join LEFT/RIGHT OUTER JOIN

Self Join JOIN ON

Cartesian CROSS JOIN



Subqueries

SELECT select_list

FROM table

WHERE expr operator

(SELECT select_list

FROM table);

• The subquery (inner query) executes once before the main query.

• The result of the subquery is used by the main query (outer query).

• Enclose subqueries in parentheses.

• Place subqueries on the right side of the comparison operator.

• Do not add an ORDER BY clause to a subquery.

• Use single-row operators with single-row subqueries.

• Use multiple-row operators with multiple-row subqueries.



Subqueries – Types of Subqueries

• Single-row subquery– Return only one row– Use single-row comparison operators (=,>,>=,<,<=,<>)

• Multiple-row subquery

– Return more than one row– Use multiple-row comparison operators (IN,ANY,ALL)

• <ANY means less than the maximum.

• >ANY means more than the minimum.

• =ANY is equivalent to IN

• >ALL means more than the maximum

• <ALL means less than the minimum.

• Multiple-column subquery– Pairwise– Nonpairwise

• FROM clause subquery (in line view)



Subqueries – Correlated Subqueries

• SELECT outer1, outer2, FROM table1 alias1 WHERE outer1 operator (SELECT inner1

FROM table2 alias2WHERE alias1.expr1 =alias2.expr2);

• The subquery references a column from the table in the parent query.



Subqueries – EXIST Operator

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



Using SET Operators – SET Operators

IntersectIntersect

AA BB

AA BB

Union/Union AllUnion/Union All

AA BB

AA BB

MinusMinus



Using SET operators – UNION Set Operator

• UNION is the combination of two tables.

• Use the UNION operator to return all rows from multiple queries and eliminate any duplicate rows.

– The number of columns and the data types of those columns must be identical between the two SELECT statements. The names of the columns need not be identical.

– UNION operates over all of the columns being selected. – NULL columns are ignored during duplicate checking. – Queries that use UNION in the WHERE clause must have the same number and

type of columns in their SELECT list.– The output is sorted in ascending order by default.



Using SET operators – UNION ALL Set Operator

• Use the UNION ALL operator to return all rows from multiple queries. – Unlike UNION, duplicate rows are not eliminated and the output is not sorted by

default. – The DISTINCT keyword cannot be used.



Using SET operators – MINUS Set Operator

• Use the MINUS operator to return rows returned by the first query but not the second query (the first select statement MINUS the second select statement).

– The number of columns and the data types of those columns must be identical between the two SELECT statements. The names of the columns need not be identical.

– All of the columns in the WHERE clause must be in the SELECT clause for the MINUS operator query to work.

– Queries that use MINUS in the WHERE clause must have the same number and type of columns in their SELECT list.



Using SET operators – INTERSECT Set Operator

• Use the INTERSECT operator to return all common rows in both queries.– The number of columns and the data types of those columns must be identical

between the two SELECT statements. The names of the columns need not be identical.

– Reversing the order of the Intersected tables does not alter the result.– INTERSECT, like UNION, ignores NULL columns.– Queries that use INTERSECT in the WHERE clause must have the same number

and type of columns in their SELECT list.



Manipulating Data – Data Manipulation Language (DML)

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



INSERT INTO table [(column [, column...])]

VALUES (value [, value...]);

• Add new rows to a table by using the INSERT statement.

• Only one row is inserted at a time with this syntax.

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

• Inserting rows with null values– Implicit method: Omit the column from the column list.– Explicit method: Specify the NULL keyword.

Manipulating Data – INSERT Statement



INSERT INTO table [(column [, column...])]

select [(column [, column...])]

from table

[WHERE condition];

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

Manipulating Data – INSERT Statement



UPDATE table

SET column = value [, column = value, ...]

[WHERE condition];

• Modify existing rows with the UPDATE statement.

• Update more than one row at a time, if required.– Specific row or rows are modified when you specify the WHERE clause.– All rows in the table are modified if you omit the WHERE clause.

Manipulating Data – UPDATE Statement



DELETE [FROM] table

[WHERE condition];

• You can remove existing rows from a table by using the DELETE statement.

• Specific rows are deleted when you specify the WHERE clause.

• All rows in the table are deleted if you omit the WHERE clause.

Manipulating Data – DELETE Statement



Manipulating Data – Transactions

• Begin when the first executable SQL statement is executed

• End with one of the following events:– COMMIT or ROLLBACK is issued

• Ensure data consistency

• Preview data changes before making changes permanent

• Group logically related operations

– DDL or DCL statement executes (automatic commit)– User exits– System crashes



Manipulating Data – Controlling Transactions

• Ensure data consistency

• Preview data changes before making changes permanent

• Group logically related operations

COMMITCOMMITSavepoint ASavepoint A

ROLLBACK to Savepoint BROLLBACK to Savepoint B

DELETEDELETE

Savepoint BSavepoint B

ROLLBACK to Savepoint AROLLBACK to Savepoint A

INSERTINSERTUPDATEUPDATEINSERTINSERT

ROLLBACKROLLBACK

INSERTINSERT

TransactionTransaction



Manipulating Data – Implicitly Transaction Processing

• An automatic commit occurs under the following circumstances:– DDL statement is issued– DCL statement is issued– Normal exit from SQL*Plus, without explicitly issuing COMMIT or ROLLBACK

• An automatic rollback occurs under an abnormal termination of SQL*Plus or a system failure.



Manipulating Data – 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.



Manipulating Data – 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.



Manipulating Data – 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.



Manipulating Data – Rolling Back to a Savepoint

• Create a marker in a current transaction by using the SAVEPOINT statement.

• Roll back to that marker by using the ROLLBACK TO SAVEPOINT statement.



Manipulating Data – 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.



Manipulating Data – 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 readers



UPDATE empSET sal = 2000WHERE ename = 'SCOTT';

Datablocks

Rollbacksegments

changedand unchanged data

before change“old” data

User A

User B

Readconsistentimage

SELECT *FROM emp;

Manipulating Data – Implementation of Read Consistency



Manipulating Data – Locking

• Prevent destructive interaction between concurrent transactions

• Require no user action

• Automatically use the lowest level of restrictiveness– Are held for the duration of the transaction– Have two basic modes

• Exclusive

• Share



Creating and Managing Tables – CREATE TABLE Statement

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

• You must have • CREATE TABLE privilege• A storage area

• You specify• Table name

– Must begin with a letter– Can be 1–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 word

• Column name, column datatype, and column size– Varchar2/Nvarchar2 (variable length character 4000 bytes)– Char/Nchar (character 2000 bytes)– Number (10-126 -10126 )– Long (character 2GByte)– Raw (binary data 2000 bytes)– Blob/Clob/Nclob (large objects binary,character 4GBytes)– Bfile (external database object 4 GByte)


Introduction to Oracle SQLCreating and Managing Tables – 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 the table.



Creating and Managing Tables – The DEFAULT Option

• Specify a default value for a column during an insert.

• Legal values are literal value, expression, or SQL function.

• Illegal values are another column’s name or pseudocolumn.

• The default datatype must match the column datatype.


Introduction to Oracle SQLCreating and Managing Tables – Tables in the Oracle

Database

• User Tables– Collection of tables created and maintained by the user– Contain user information

• Data Dictionary– Collection of tables created and maintained by the Oracle server– Contain database information



Creating and Managing Tables – Creating Table with Subquery

CREATE TABLE table

[(column, column...)]

AS subquery;

• Create a table and insert rows by combining the CREATE TABLE statement and AS subquery option.

• Match the number of specified columns to the number of subquery columns.

• Define columns with column names anddefault values.



Creating and Managing Tables – ALTER TABLE Statment

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 (column [, column]...);

• Use the ALTER TABLE statement to– Add a new column– Modify an existing column– Define a default value for the new column



Creating and Managing Tables – SET UNUSED Option

ALTER TABLE table

SET UNUSED (column);

ALTER TABLE table

SET UNUSED COLUMN column;

ALTER TABLE table

DROP UNUSED COLUMNS;

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



Creating and Managing Tables – Delete Table

DROP TABLE table;

• All data and structure in the table is deleted.

• Any pending transactions are committed.

• All indexes are dropped.

• You cannot roll back this statement.



Creating and Managing Tables – Rename Object

RENAME object_name TO new_object name;

• To change the name of a table, view, sequence, or synonym, you execute the RENAME statement.

• You must be the owner of the object.



Creating and Managing Tables – Truncate a Table

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



Creating and Managing Tables – Adding Comments to a Table

COMMENT ON TABLE table IS comment;

COMMENT ON COLUMN table.column IS comment;



Creating and Managing Tables – Table 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 in Oracle– NOT NULL– UNIQUE – PRIMARY KEY– FOREIGN KEY– CHECK

• Name a constraint or the Oracle Server will generate a name by using the SYS_Cn format.

• Create a constraint:– At the same time as the table is created– After the table has been created

• Define a constraint at the column or table level.



Creating and Managing Tables – Defining Constraints

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr]

[column_constraint],

...

[table_constraint][,...]);



• NOT NULL– Ensures that null values are not permitted for the column

Creating and Managing Tables – NOT NULL Constraint



• UNIQUE – Defined at either the table level or the column level– Ensure uniqueness of a column

Creating and Managing Tables – UNIQUE Constraint



Creating and Managing Tables – PRIMARY KEY Constraint

• PRIMARY KEY– Defined at either the table level or the column level– Defines the primary key of the table



Creating and Managing Tables – FOREING KEY Constraint

• FOREIGN KEY– CONSTRAINT costraint FOREIGN KEY (column [,column..])

REFERENCES table (column [,column..])– 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 Allows deletion in the parent table and deletion of the

dependent rows in the child table– Defined at either the table level or the column level



Creating and Managing Tables – CHECK Constraint

• CHECK– Defines a condition that each row must satisfy– Expressions that are not allowed

• References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns

• Calls to SYSDATE, UID, USER, and USERENV functions

• Queries



Creating and Managing Tables – Adding/Dropping Constraints

ALTER TABLE table

ADD/DROP [CONSTRAINT constraint] type (column);

• Add or drop, but not modify, a constraint

• Enable or disable constraints

• Add a NOT NULL constraint by using the MODIFY clause



Creating and Managing Tables – Enable/Disable Constraints

ALTER TABLE table

ENABLE/DISABLE CONSTRAINT constraint ;

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



Other Schema Objects (Views)

• Views are

– Logically represents subsets of data from one or more tables

• Why we use views– To restrict data access– To make complex queries easy– To allow data independence– To present different views of the same data

• Types of views

Feature Simple Views Complex Views

Number of tables One One or More

Contain functions No Yes

Contain groups of data

No Yes

DML through view Yes Not always



Other Schema Objects (Views) – Creating Views

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

[(alias[, alias]...)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY];

• You embed a subquery within the CREATE VIEW statement.

• The subquery can contain complex SELECT syntax.

• The subquery cannot contain an ORDER BY clause.



• 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 keyword• You cannot modify data in a view if it contains

– Any of the conditions mentioned mentioned above– Columns defined by expressions

• You cannot add data if– The view contains any of the conditions mentioned above

– There are NOT NULL columns in the base tables that are not selected by the view

Other Schema Objects (Views) – DML Operations on a View



• You can ensure that DML on the view stayswithin the domain of the view by using the WITH CHECK OPTION clause.

• Any attempt to change the columns in check option for any row in the view will fail because it violates the WITH CHECK OPTION constraint.

Other Schema Objects (Views) – Check Option on a View



• 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 will result in Oracle Server error.

Other Schema Objects (Views) – READ ONLY Option on a View



DROP VIEW view;

• Remove a view without losing data because a view is based on underlying

tables in the database

Other Schema Objects (Views) – Remove a View



• An inline view is a subquery with an alias (correlation name) that you can use within a SQL statement.

• An inline view is similar to using a named subquery in the FROM clause of the main query.

• An inline view is not a schema object.

Other Schema Objects (Views) – Inline View



SELECT [column_list], ROWNUM

FROM (SELECT [column_list] FROM table

ORDER BY Top-N_column)

WHERE ROWNUM <= N

• Top-N queries ask for the n largest or smallest values of a column. – 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.

Other Schema Objects (Views) – Top-N Analysis with Inline Views



CREATE SEQUENCE sequence

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];

• 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

Other Schema Objects (Sequence)



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

Other Schema Objects (Sequence) – NEXTVAL CURRVAL



• Caching sequence values in memory allows 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

Other Schema Objects (Sequence) – Using a Sequence



ALTER SEQUENCE sequence

[INCREMENT BY n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];

• Change the increment value, maximum value, minimum value, cycle option, or cache option.

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

Other Schema Objects (Sequence) – Modify a Sequence



DROP SEQUENCE sequence;

• Remove a sequence from the data dictionary by using the DROP SEQUENCE statement.

• Once removed, the sequence can no longer be referenced.

Other Schema Objects (Sequence) – Remove a Sequence



• Is a schema object– Created Automatically: A unique index is created automatically when you define

a PRIMARY KEY or UNIQUE constraint in a table definition.– Created Manually: Users can create nonunique indexes on columns to speed up

access time to the rows.

• Is used by the Oracle Server to speed up the retrieval of rows by using a pointer

• Can reduce disk I/O by using rapid path access method to locate the data quickly

• Is independent of the table it indexes

• Is used and maintained automatically by the Oracle Server

Other Schema Objects (Index)



CREATE INDEX indexON table (column[, column]...);

• Create an index on one or more columns when– The column is used frequently in the WHERE clause or in a join condition.– The column contains a wide range of values.– The column contains a large number of null values.– Two 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–4% of

the rows.

• DO NOT create an index on one or more columns when– 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–4% of the rows.– The table is updated frequently.

Other Schema Objects (Index) – Creating an Index



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

Other Schema Objects (Index) – Function Based Indexes



DROP INDEX index;

• Remove an index from the data dictionary.

• To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege.

Other Schema Objects (Index) – Removing an Index



• Simplify access to objects by creating a synonym (another name for an object).

– Refer to a table owned by another user.

– Shorten lengthy object names.

CREATE [PUBLIC] SYNONYM synonym

FOR object;

DROP SYNONYM synonym;

Other Schema Objects (Synonyms)



Controlling User Access

Database administrator

Users

Username and passwordprivileges



• Database security:– System security– Data security

• System privileges: Gain access to the database

• Object privileges: Manipulate the content of the database objects

• Schema: Collection of objects, such as tables, views, and sequences

Controlling User Access - Privileges



• More than 100 privileges are available.

• The DBA has high-level system privileges:

– Create new users

– Remove users

– Remove tables

– Back up tables

Controlling User Access – System Privileges



GRANT privilege [, privilege...]

TO user [, user...];

– CREATE SESSION– CREATE TABLE– CREATE SEQUENCE– CREATE VIEW– CREATE PROCEDURE

Controlling User Access – Assign System Privileges



Controlling User Access – Roles

Allocating privilegeswithout a role

Allocating privilegeswith a role

Privileges

Users

Manager



Controlling User Access – Creating/Granting Roles

CREATE ROLE role

GRANT privilege [, privilege...] ;

TO role [, role...];

– CREATE SESSION

– CREATE TABLE

– CREATE SEQUENCE

– CREATE VIEW

– CREATE PROCEDURE



Controlling User Access – Object Privilege

Object Privilege

Table View Sequence Procedure

ALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE



GRANT object_priv [(columns)]

ON object

TO {user|role|PUBLIC}

[WITH GRANT OPTION];

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

Controlling User Access – Object Privilege



• Give a user authority to pass along the privileges.

• Allow all users on the system to query data from table.

Controlling User Access – WITH GRANT OPTION & PUBLIC



REVOKE {privilege [, privilege...]|ALL}

ON object

FROM {user[, user...]|role|PUBLIC}

[CASCADE CONSTRAINTS];

• You use the REVOKE statement to revoke privileges granted to other users.

• Privileges granted to others through the WITH GRANT OPTION will also be revoked.

• CASCADE is required to remove any referential integrity constraints made to the CONSTRAINTS object by means of the REFERENCES privilege.

Controlling User Access – Revoke Privilege



SELECT [LEVEL], column, expr...

FROM table

[WHERE condition(s)]

[START WITH condition(s)]

[CONNECT BY PRIOR condition(s)];

• Hierarchical queries are a facility that enable you to retrieve data based on a

natural hierarchical relationship between rows in a table.

• CONNECT BY PRIOR column1 = column2 (parent key=child key walk top

down child key=parent key walk buttom up)

• START WITH column1 = value (Starting point )

• Pseudocolumn LEVEL show in which level of the tree are you.

Hierarchical queries



Data Dictionary Views

ALL_XXXXXX

Object accesible by the user

USER_XXXXXX

Object owned by the user




Data Dictionary Table Description

USER_TABLES View tables owned by the user

USER_OBJECTS View object owned by the user

USER_COL_COMMENTS Comments on table columns

USER_TAB_COMMENTS Comments on tables

USER_CONSTRAINTS View constraints

USER_CONS_COLUMNS View columns associated with constraints





USER_INDEXES View Indexes owned by the user

USER_IND_COLUMNS View columns associated with index

USER_CATALOG View tables, views, synonyms, and sequences owned by the user

USER_VIEWS View Views owned by the user

USER_SEQUENCES View sequences owned by the user

USER_SYNONYMS View synonyms owned by the user




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’sObjects

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 onspecific columns


oracle sql

Documents