Scribd
Importer
6 vues15 pages

CH 2

Ch 2 oracle

Transféré par

shyamfaldu000
Copyright
© All Rights Reserved
Nous prenons très au sérieux les droits relatifs au contenu. Si vous pensez qu’il s’agit de votre contenu, signalez une atteinte au droit d’auteur ici.
Formats disponibles
Téléchargez aux formats PDF ou lisez en ligne sur Scribd
6 vues15 pages

CH 2

Ch 2 oracle

Transféré par

shyamfaldu000
Copyright
© All Rights Reserved
Nous prenons très au sérieux les droits relatifs au contenu. Si vous pensez qu’il s’agit de votre contenu, signalez une atteinte au droit d’auteur ici.
Formats disponibles
Téléchargez aux formats PDF ou lisez en ligne sur Scribd
Sub: RDBMS using Oracle Ghapter -2 B. CA. Sen3 If data constraints are defined along with the column definition when creating or altering a table structure that are column level constraint. Table level constraints: If data constraints are defined after defining all the table columns when creating or altering table structureis a table level consteaint, PRIMARY KEY CONSTRAINT : ‘A primary key is one or more column in a table used to uniquely identify each row in the table. Primary key fields cannot contain NULL values. A single column primary key is called a Simple key A multicolumn primary key is called a Composite pr A primary key column ina table has special attributes: © Itdefines the column, as ¢ mandatory column (ie. the column can not be left blank), ‘As the NOT NULL attribute is active. ©The data held across the column MUST be UNIQUE nary key, ‘A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement. PRIMARY KEY Constraint Defined At Column Level : ‘Syntax: - Primary key) Example: Create table Employee (Emp_no varchar2 (10) Primary key, paame varchar2 (15)); oP: ‘Table created PRIMARY KEY Constraint Defined At Table Level (Column Definition>) , Primary key (,); Create table Employee (Emp_no varchar? (10) , name varchar? (15),Primary key (Emp_no)); ‘Table created Primary Key - Using ALTER TABLE statement ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (colurnn, column2, ccolumn_n); ALTER TABLE student ADD CONSTRAINT student_pk PRIMARY KEY (id); ‘Table altered Drop Primary Key ‘Syntax: ALTER TABLE table_name Page Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 DROP CONSTRAINT coastraint_name; Example: ALTER TABLE student DROP CONSTRAINT student_pk op: ‘Table dropped Feature of Primary kev: + Primary key is a column or a set of column that uniquely identifies a row. Its main purpose is the Record Uniqueness. + Primary key will not allow duplicate values. + Primary key will also not allow null values. + Primary key helps to identify one record from another record and also helps in relating tables with one another. + Primary key can not be LONG or LONG RAW data type. * Unique Index is create automatically if there is a Primary key. * One table can combine up to 16 columns in a Composite Primary key. 2) FOREIGN KEY CONSTRAINT: + A foreign key is a column or group of columns in a relational database table that provides ‘allink between data in two tables, + A foreign key is a way to enforce referential integrity within your Oracle database. A foreign key means that values in one table must also appear in another table. + The referenced table is called the parent table while the table with the foreign key is called the child table. ‘The foreign key in the child table will generally reference ‘a primary key in the parent table. +A foreign key can be defined in either @ CREATE TABLE statement or an ALTER TABLE statement. Feature of Foreign key. Foreign key constraint can be specified on child but not on parent. Parent that is being referenced has to be unique or Primary key Child may have duplicates and nulls but unless itis specified, ‘When we delete rows in the parent table (i.e.. the one holding the primary key), the same columns in the other table (ie., the one holding a foreign key) also gets deleted, this action is known as Caseade. Principles of Foreign kev/References Constraint: + Rejects an INSERT or UPDATE of a valve, if a corresponding value does not currently exist in the master key table. + the ON DELETE CASCADE option is set, a DELETE operation in the master table will trigger a DELETE operation for corresponding records in all detail tables. + If the ON DELETE SET NULL option is set, a DELETE operation in the master table will set the value held by the foreign key of the detail tables to rll + Rejects a DELETE from the Master table if corresponding records in the DETAIL table exist Must reference a PRIMARY KEY or UNIQUE columns in primary table. Requires that the FOREIGN KEY columns and the CONSTRAINT columns have matching data types Can reference the same table named in the CREATE TABLE statement. FOREIGN KEY Constraint Defined At Column Level: ‘Syntai CREATE TABLE table_name Page 6 of 50 Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 CONSTRAINT fk_column FOREIGN KEY (column1, column2, ... column_n) REFERENCES parent_table (column, column?, ... column_n) ON DELETE SET NULL x ‘xample: CREATE TABLE supplier (supplier_id number (10) not null, supplier_name varchar2(50) not null, coniact_name varchar2(50), CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) > CREATE TABLE products (product_id number (10) not null, supplier id number (10), CONSTRAINT tk_supplier FOREIGNKEY (supplier_i) REFERENCES supplier(supplier_id) ON DELETE SET NULL d Using an ALTER TABLE statement Syntax: ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column!, column2, .. colurin_n) REFERENCES parent_table(column!, column2, ... colurmn_n) ON DELETE SET NULL; Example: ALTER TABLE products ADD CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ‘ON DELETE SET NULL; ‘Drop a Foreign Key Syntax ALTER TABLE table_name DROP CONSTRAINT constraint_name; Example: ALTER TABLE products DROP CONSTRAINT fk_supplier; 3) UNIQUE KEY CONSTRAINT: + A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table + You can say that it is litte like primary key but it can ascept only one null value and it cannot have duplicate values. + The unique key and primary key both provide a guarantee for uniqueness for 2 column or a set of columns + Thereis an automatically defined unique key constraint within a primary key constraint. Page 9 of 50 Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 + There may be many unique key constraints for one table, but only one PRIMARY KEY constraint for one table. y point about Unique Constraint: Unique key will not allow duplicate values. Unique index is created automatically A table can have more than one Unique key which is not possible in Primary key Unique key can combine up to 16 column in a Composite Unique key Unique key can not be LONG or LONG ROW data type. z UNIQUE KEY Constraint Defined At Column Level : ‘Synta: CREATE TABLE students r $Id int NOT NULL UNIQUE, LastName varchar (255) NOT NULL, FirstName varchar (255), City varchar (255) Dd: Examph Create table college no number G) unique, raame varchar2 (20), course number (3) address varchar? (25)); CREATE TABLE TableName (Column Namel> (), ( (), UNIQUE (,)); Create table college (no number B), name varchar2 (20), course number (3), aaldress varchar2 (25), unique (no): On: ‘Table created UNIQUE KEY - Using ALTER TABLE statement ‘Synta ALTER TABLE Persons ADD UNIQUE (ID); UNIQUE KEY - Using DROP statement Syntax: ALTER TABLE Persons DROP CONSTRAINT UC_Person; Page 10 of 50 Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 B. Business Rule Constraints: + Oracle allows the application of business rules to table columns, Business managers determine business rules; they vary from system to mention earlier. ‘These rules are applied to data prior the data is being inserted into table columns ‘This ensures that the data in the table have integrity. ‘Check Constraints can be bound to a column or a table using the CREATE TABLE or ALTER TABLE command + Oracle allows programmers to define constraints at: 1. Column Level 2. Table Level 1. Column level constraints: + Ifdata constraints are defined along with the column definition when creating or altering ‘table structure that are column level constraints ‘Table level constraints: © If data constraints are defined after defining all the table columns when creating or altering table structure is a table level constraint. > What isa NULL Value? + A field with « NULL values a field with no value, + If afield in a table is optional, it is possible to insect a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value. Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has beea left blank during record creation! + Iris not possible 1o test for NULL values with comparison operators, such as =, 5 OF >. 1) NOT NULL CONSTRAINT: + By default, all columns in a table allow nulls (the absence of a value). + ANOT NULL constraint requires that no nulls be allowed in a column of a table + The NOT NULL column constraint ensures that a table column can not be left empty. + When column is defined as not null, then that column becomes a mandatory column, NOT NULL Constraint At Column Lev Synta: () NOT NULL, Example, (CREATE TABLE Persons ( ID int NOT NULL, LastName varchar2(20) NOT NULL, FirstName varchar2(20) NOT NULL, Age int): On: Table created 2) CHECK CONSTRAINT: + The CHECK constraint is used to limit the value range that can be placed in a column, + If you define a CHECK constraint on a column it will allow only certain values for this column. + If you define a CHECK constraint on a table it can limit the values in certain columns based ‘on values in other columns in the row. Page 11 of 50 Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 WHERE CustomerName LIKE '2%'; OmP:- CustomerID CustomerName ContactName Address City PostalCode Country 1 Alfreds Maria Anders Obere St. Berlin 12209 Germany Futterkiste 37 Ana Tryjillo Ana Trujillo Avda. dela México 05021 Mexico Emparedados y Constitucién DLE. helados 2229 3 Anionio Moreno Antonio Mataderos México 05023. Mexico ‘Taqueria Moreno 2312 DE. 4 Around the ‘Thomas 120 London WAL IDP UK Horn Hardy Hanover So, Join (inner join, outer join, self +A join isa query that combines rows from two or more tables or views + There may be at least one join condition either in the FROM clause or in the WHERE clause for joining two tables. + It compares two columns from different tables and combines pair of rows, each containing ‘one row from each table, for which join condition is true ‘Types of Joins 1. Inner Joins (Simple Join) 2. Outer Joins © Left Outer Join (Left Join) co Right Outer Join (Right Join) © Full Outer Join (Full Join) 3. Self Joins inner join + Inner Join is the simplest and mosi commoa type of joi. + ILis alsoknown as simple join, + returns all rows from multiple tables where the join condition is met Svatax SELECT columns FROM table! INNER JOIN table2 ON able! column = [Link]; Image representation of ane Lain Example: + Lot's take an exampleto perform Inner Join on two tables "Suppliers" and "Order!" Suppliers Page 20 of 50 Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 [Link] SUPPLIER_NAME _ SUPPLIER_ADDRESS 1 Baa shees Age 2 Kingfisher Datht 3 ‘voro ado, Order! ORDER_NUMBER [Link] CITY 101 1 Allahabad 102 2 Kanpur SELECT suppliers.supplier_id. suppliers.supplier_name, order order_number FROM suppliers INNER JOIN order ON suppliers. supplier_id = order supplier Op. SUPPLIER | SUPPLIER_NAME _ ORDER_NUMBER 1 Bat shoe 101 2 Kingfisher 102 outer + An outer join is similar to equijoin but it gets also the non-matched rows from the table. It is categorized in Left Outer Join, Right Outer Join and Full Outer Join by Oracle 91 ANSVISO 1999 standard Left Outer Join (Left Join) © Left Outer Join returns all rows from the left (first) table specified in the ON condition and only those rows from the right (second) table where the j is met, SELECT columns FROM table! LEFT [OUTER] JOIN table? ON ublel column = [Link]; Amaze representation of left outer ioin, Examnle; ‘SELECT suppliers.supplier_id, suppliers.supplier_name, order|.order_number FROM suppliers LEFT OUTER JOIN order! (ON suppliers supplicr_id = order supplier_id; Page 21 of 50 Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 uuu ee ees Right Outer Join (Right Join) © The Right Outer Join returns all rows from the right-hand table specified in the ON condition and only these rows from the other table where the join condition is met, ‘Syntax SELECT columns FROM table! RIGHT [OUTER] JOIN table2 ON table! column = [Link]; Example: ‘SELECT order|.order_number, order city, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN order ON suppliers supplier_id = order supplier_id; OP. ORDER NUMBER arty ‘SUPPLIER_NAME 101 ‘Allahabad Batasioes 102 Karpor Kingfisher Full Outer Join (Full Join) © The Full Outer Join returns all rows from the left hand table and right hand table. It places NULL where the join condition is not met. Sux SELECT columns FROM table! FULL [OUTER] JOIN table2 ON table! column = table?.column; Anase representation ef fulloutar join Page 22 of 50 Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 Example: ‘SELECT suppliers supplier_td, suppliers.supplier_name, order order_number FROM suppliers FULL OUTER JOIN order! ON suppliers supplier_id = order supplier_id; ow: ‘SUPPLIER_ID ‘SUPPLIER. NAME ORDER NUMBER Kingsuiner 12. 2 oe . 2 ple . a Microot - 2 pasted . ” Goose 5 2 KingSuer . self join + Self Join is a sp. relationship). A self join simply specifies that each rows of a table is every other row of the table Swntax SELECT acolumn_name, b.column_name. FROM table! a, tablel b WHERE a.common_filed = heommon_field; ic type of Join. In Self Join, a table is joined with itself (Unary mbined with itself and Let's take a table “customers”, SELECT [Link],[Link], aSALARY FROM CUSTOMERS a, CUSTOMERS b WHERE aSALARY <[Link]: NAME AGE SALARY. Sul ‘Subquery: + A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. Page 23 of 50 Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 + A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. + Subgueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <,>, >=,<=, IN, BETWEEN, etc. + There are a few rules that subqueries must follow — (© Subqueries must be enclosed within parentheses. © A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns. (© Subqueries that return more than one row can only be used with multiple value ‘operators such as the IN operator. © A subquery cannot be immediately enclosed in a set function © The BETWEEN operator cannot be used with a subquery. However, the BETWEEN ‘operator can be used within the subquery. ‘Subqueries with the SELECT Statement ‘+ Subgueries are most frequently used with the SELECT statement. yntax SELECT column_name [, column_name } FROM table! {,table2 ] WHERE column_name OPERATOR (SELECT colurin_name {, column_name } FROM tablel [, table2 ] [WHERE)) Example SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROMCUSTOMERS WHERE SALARY > 4500) ; Output - | | Mame | AGE [YAOoness*|"saLaRy | | 4 | Chaitali | 25 | Munbai | 6500.00 | | 5 | vardik | 27 | Bhopal | 500.00 | \ 7 | miffy | 24| Indore | 10000.00 | the INSERT Statement ubgueries also can be used with INSERT statements, The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date or number functions. syntax INSERT INTO table_name [ (column! (, column? }) ] SELECT { “column! [, column? } FROM table! [. table? ] [ WHERE VALUE OPERATOR ] Example INSERT INTO CUSTOMERS_BKP ‘SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS): the UPDATE Statement UPDATE table SET column_name Page 24 of 50 Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 Exp_full_database ‘Select any table, backup any table, insert update ,delete on [Link], [Link], [Link] Tmp_full_database Become user Delete_catalog_role Delete on sysaud® Excoute_catalog_role Execute on data Select_catalog role ‘Select on data dictionary views Suntax: Create role role_name [identified by password}; Examule: ‘CREATE ROLE data: Role crested. Grant rivilesestoarole— GRANT create table, create view TO data; Grant succeeded. GRANT manager TO SAM, STARK; Grant succeeded. REVOKE create table FROM manager: Drowaboles DROP ROLE manager; rant, Revoke command rant ‘© Oracle provides extensive security feature in oracle to safeguard information stored in its tables from unauthorized viewing damage. © Depending on a user’s status and responsbility appropriate right on oracle’s resource can be assigned to the user by the DBA. ‘© Therights that allow the use of some or all of orscle’s resource on the server are called privileges. © Oracles that are crested by auser owned and controlled by that user ‘If auser wishes to access any of the objects belonging to another user, the owner of the object will have to give permissions for such access ‘+ This is called granting of privileges. ‘© The owner of the object can take privileges once gi ‘+ This is called revoking of privileges. © Granting privileges using the grant statement ‘Swutax: GRANT privilege_name ON object_name TO (user_name [PUBLIC [role_name} [WITH GRANT OPTION}; Parameters Used: + privilege name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT. Page 45 of 50 Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 + object_name is the name of an database object like TABLE, VIEW, STORED PROC and SEQUENCE. ‘user_name is the name of the user to whom an access right is being granted. PUBLICis used to grant access rights to all users. ROLES are a set of privileges grouped together. WITH GRANT OPTION - allows a user to grant access rights to other users, Deseript Seer em INSERT insert statement on the table DELETE Talaha Aa aaE oi He TB te am indaxcom an axccting table CREATE Create table statements ALTER Ability to perform ALTER TABLE to change the table definition DRor Drop wile satemenes ALL (Grune all permissions except GRANT OPTION UrDAIE Upiiate statements on the table GRANT Allows to grant the privilege that Eauunle: To give all privileges: Grant all on client_master to abe; To give selected privileges: Grant select, insert on client_master to abe; 3. To further grant: Grant select on client_master to abe with grant option; Now abe user can see records Select * from xyz.client_master; For further grant abe can write: Grant select on xyz.client_master to meet: Revoke + Privileges once given canbe d +The object owner canrevoke privileges grant to another user. + [Link] of an object who is not owner, but has been granted the grant privileges has the power of revoke the privileges from a grantee. + evoking permission using the revoke statement. ied w a user using the revoke command, REVOKE ‘ON FROM ; Revoke delete on emp_mstr from ABC; Revoke select on xyz.client_master from meet; 1 2. ROLE + Acrole is set of privileges that a user can grant to another user. + Oracle has eight system-supplied roles as listed in the following. Page 46 of 50 Ghapter -2 [Link].Sen3 + The first three are most commonly used. Role Name Description ‘Connect Toclude the following system privileges: Alter, Session, Create cluster, Create database link, Create session, Create synonym, Create table, Create view Resource Tnclude the following system privileges: Create cluster, Create procedure, Create sequence, Create table, Create trigger, Create type. DBA Al system privileges with admin option Exp_full_daiabase ‘Select any table, backup any table, insert -update ,delete on [Link], [Link] [Link] Trip_full database Become user Delete_catalog_role Delete on sysaud Execute_catalog.role Fxecute on data dictionary pi Select_catalog_role ‘Select on data dictionary views Sune: Create role role_name [identified by password]; Example: 1. Create role data; 2. Grant select on client_master to data; 3. Grant select on product_master to data 4. Grant select, update on student_master to da What is transactio + A transaction is a unit of work that is performed against a database. Transactions are units oF sequences of work accomplished in a logical order, whether ina manual fashion by a user or ‘automatically by some sort of a database program, Properties of Transactions Transactions have the following four standard properties, usually referred to by the seronym ACID. * Atomicity~ ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state. + Consistency~ ensures that the database properly changes states upon a successfully committed transaction. + Isolation ~ enables transactions to operate independently ofand transparent to each other. + Durability — ensures that the result or effect of a committed transaction persis in case of a system failure. ‘Transaction Contra! ‘The following commands are used to control transactions. + COMMIT = to save the changes. + ROLLBACK = to roll back the changes. + SAVEPOINT ~ creates points within the groups of transactions in which to ROLLBACK. + SET TRANSACTION ~ Places a name on a transaction. f]_ Pose 47 of 50 Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 Starting and Ending of Transaction * A transaction has a beginning and an end. A transaction starts when one of the following events take place — co The first SQL statement is performed after connecting to the database. © Ateach new SQL statement issued after a transaction is completed. + A transaction ends when one of the following events take place — © ACOMMIT or a ROLLBACK statement is issued, © ADDL statement, such as CREATE TABLEstatement, is issued; because in that case a COMMIT is automatically performed, © A DCL statement, such as a GRANT statement, is issued: because in that ease a COMMIT is automatically performed. © User disconnects from the database. © User exits from SQL*PLUS by issuing the EXIT command, a COMMIT is automatically performed. © SQL*Plus terminates abnormally, a ROLLBACK is automatically performed, © AML statement fails; in that case a ROLLBACK jis automatically performed for undoing that DML statement. Commit, Rollback, Save Poi Commit + The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. + The COMMIT command saves all the transactions to the databasé since the last COMMIT or # Following is an example which would delete those records from the table which have ag and then COMMIT the changes in the database. SQL> DELETE FROM CUSTOMERS WHERE AGE = 25; SQL> COMMIT: + Thus, two rows from the table would be deleted and the SELECT statement would produce the fol 1 | 2 | Ramesh | 32 | Ahmedabad | 2000.00 | | 3 | kauehit | 23 | Kota | 2000.00 | | 5 | Hardik | 27 | Bhopal =| 8500.00 | | 6 | Komat = | 22 | mp | 4800.00 | | 7 | Muffy | 24 | Indore | 1¢@00.00 | Page 48 of 50 Sub: RDBMS using Oracle Ghapter -2 [Link].Sen3 Rollback Save ‘The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database, ‘This command can only be used to undo transactions since the last COMMIT or ROLLBACK ‘command was issued, ROLLBACK; Exams ‘Consider the CUSTOMERS ta TO | NAME Ace | 1 i | a [| Ramesh | 32 | Ahmedabad | 2 | knatan | 25 | Deine | 3 | Kaushik | 23 | Kota | 4 | chedeers | 25 | Mumbes | S | Harask | 27 | | 6 1 Komar 1 221 17 1 mutty | 2a | following records — Following is an example, which would delete those records from the table which have the age = 25 and then ROLLBACK the changes in the database. ‘SOL> DELETE FROM CUSTOMERS WHERE AGE = 25; SQL> ROLLBACK; ‘Thus, the delete operation would not impact the table and the SELECT statement would produce the following result. gut 1 | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Kaien | 25 | Deana 1 1 | 3 | Kaushik | 23 | Kota ' 1 | 4 | Chaitaai | 25 | mumbai | 1 | 5 | Maraik | 27 | Bhopat = | 1 | 6 | Kemet = | 22 | mp 1 1 | 7 | murey | 24 | qndore | ' ‘A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction sums SAVEPOINT SAVEPOINT_NAME; ‘This command serves only in the creation of a SAVEPOINT among all the transactional statements. The ROLLBACK command is used to undo a group of transactions. eto pallid eu SANE POLS | iss show balou ROLLBACK TO SAVEPOINT_NAME Exams Cons the CUSTOMERS table having the following records. Page 49 of 50

Vous aimerez peut-être aussi