1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

SQL practice in 24 hours

SQL practice from scratch
SQL quries taken from
IVAN BAYROSS text book
---------------------------------
1. Oracle installation was given, it include step by step installation of
the procut on the windowsNT as well as on 98 paltforms. the product is in two flavors
    a. enterprise version (which is the network version) for NT editions and
    b. personal version (which is the desktop version) for normal editions.
2. insert CD and go to setup option
3. global database name
    Global DB Name : SCT.Domain
    SID    :    sct
4. and follow the directions observed on screen.
5. the auto configuration process will occur for the following tools
    a. Net8 configuration assistance
    b. Oracle database configuration assistance
    c. OMS NT Service
6. finally a message box will be dispalyed with default passwords given to
oracle database administator logins. these are
    Login Name    Password
    ------------------------
    SYS        change_in_install
    SYSTEM        Manager

7. After installation all the changes in password will done with DBA Studio.
8. Disabling Automatic Startup
    start->control Panel->Services->OracleOraHome81TNSlistener
    and make it manual. and follow the screen options.



INTERACTIVE SQL - TABLE FUNDAMENTALS
------------------------------------

1.creating a table:

create table BankMaster(
AccHolderId varchar2(10),
FirstName varchar2(20),
LastName varchar2(20),
Address1 varchar2(30),
Address2 varchar2(30),
Address3 varchar2(30),
Pincode number(6)
);


2.inserting data into table:

insert into BankMaster(AccHolderId,FirstName,LastName,Address1,Address2,Address3,Pincode)
values ('SB100','Ramesh','Vamsi','11-8-153','Leninnagar','Khammam',507001);

(or)

insert into BankMaster values(
'&AccHolderId',
'&FirstName',
'&LastName',
'&Address1',
'&Address2',
'&Address3',
&Pincode
);

update faculty set qual = 'M.Tech' where fno = 10101;
update csemarks set total = (ds+os+dbms+cpp+os) where markid = 5001;


3.viewing the data in the tables:

a. all rows and all coloumns
    
    select * from BankMaster;

b. selecting only desired columns

    select AccHolderId,Fisrtname,LastName from BankMaster;


4.filtering table data:

a.selected columns and all rows

    select FirstName,LastName from BankMaster;

b.select rows and all columns (using where clause)
    
    select * from BankMaster where AcctHolderID = 'SB102';

c.selected columns and selected rows

    select AcctHolderId,FirstName,LastName from Bankmaster where PinCode=507001;


5.eliminating Duplicate Rows when using a select statement:

    select distint * from BankMaster;
    select distint Pincode from BankMaster;

6.sorting data in a table:

    select * from BankMaster order by FirtName; (by default it is in the incremental order)
    select * from bankMaster order by Firstname desc; (which is in decremental order)

7.creating a table from a table

    create table SavingMaster(SavingId,FirstName,LastName,Address1,Address2,Address3,Pincode)
    as select AcctHolderId,FirstName,LastName,Address1,Address2,Address3,Pincode from BankMaster;

8.inserting data into a table from anoter table

    insert into Savingmaster select acctHolderId,FirstName,lastName,Address1,Address2,
    Address3,pincode from Bankmaster;

    insert into Savingmaster select acctHolderId,FirstName,lastName,Address1,Address2,
    Address3,pincode from Bankmaster where FirstName='Ramesh'; (with where clause)

9.delete opertions

a. removal of all rows (use delete key)

    delete from BankMaster;

    delete from BankMaster where FirstName='Ramesh'; (with where clause)

10. updating the content of the table (use update key)

    update BankMaster set address3 = 'Bombay';

    update BankMaster set FirstName = 'Vamsi', LastName = 'Ramesh' where Pincode = 507001;
    (using where clause)

11. Modifying the structure of a table: (use alter key)

a. Adding new Columns

    alter table BankMaster add (ResTel number(8));

b. dropping a column from a table (which was added from oracle8)

    alter table BankMaster drop column ResTel;

c. modifying existing columns

    alter table BankMaster modify (FirstName varchar2(21));

the following are not possible with alter keyword:
    a. change the name of the table
    b. change the name of the column
    c. decrese the size of a column if table data exists.

12. renaming tables
    
    rename BankMaster to BankAccMaster;

13. Other operations on table

a. Truncating table

    truncate table BankAccMaster;

b. destroying tables

    drop table BankAccMaster;

c. examining objects created by a user

    select * from tab;

d. finding out of column details of a table

    describe BankAccMaster;
        (or)

    desc BankAccMaster;

points to remmember:

1. date is a data type its input format is 'dd-mon-yyy', ex: 17-may-2006
2. Datetime is another datatype of indicating data as wel as time and the time format is as
follows 'hh:mi:ss', ex: 16:05:20
3. long is another dataype for variable length character string containing 2GB size.
4. raw/ long raw are the datatypes which are used for storing binary data.
5. the way of filtering table data will be
    a. selected columns and all rows
    b. selected rows and all columns
    c. selected columns and selected rows


INTERACTIVE SQL - DATA CONSTRAINTS
----------------------------------

1. Types of data constraints
    
    a. i/o constraints
    b. primary key constraints
    c. foreign key constraints
    c. bussiness rule constraints
    d. column level constrints
    e. table level constraints

2. Both create and alter can be used to write sql sentences that attach constraints to a
table column.


3. NOT NULL constrint

create table Client_Master(
Client_no varchar2(10) NOT NULL,
Name varchar2(20) NOT NULL,
Address varchar2(20) NOT NULL,
Pincode number(6)
);    

4.UNIQUE key constrints

create table Client_Master(
Client_no varchar2(10) UNIQUE,
Name varchar2(20),
Address varchar2(20),
Pincode number(6)
);    

5. overruling the unique and not null with primary key

<column level declaration>
create table Client_Master(
Client_no varchar2(10) primary key,
Name varchar2(20),
Address varchar2(20),
Pincode number(6)
);    

<table level declaration of primary key>

create table Client_Master(
Client_no varchar2(10),
Name varchar2(20),
Address varchar2(20),
Pincode number(6),
primary key (Client_no,Name)
); (where Client_no and Name are combinly called primary key)    

create table sales_order(
order_no varchar2(6) primary key,
order_date date,client_no varchar2(6), dely_addr varchar2(25),
sales_no varchar2(6), dely_date date, order_status varchar2(10));


6. creating foreign key

<columns level declaration of foreign key>

create table sales_order_details(
deltorder_no varchar2(6) references sales_order,
product_no varchar2(6),
qty_ordered number(8),
product_rate number(5,2),
primary key(deltorder_no,product_no)
);


<table level declaration of foreign key>

create table sales_order_details(
deltorder_no varchar2(6),
product_no varchar2(6),
qty_ordered number(8),
product_rate number(5,2),
primary key(deltorder_no,product_no),
foreign key(deltorder_no)
references sales_order.order_no
);


7. Assigning use defined names to constraints

ex: create a table client_master with a primary key constriant on column
client_no. the constraint name must be p_clientkey.

create table Client_Master(
Client_no varchar2(10) constraint p_clientkey primary key,
Name varchar2(20),
Address varchar2(20),
Pincode number(6)
);    


create table sales_order_details(
deltorder_no varchar2(6),
product_no varchar2(6),
qty_ordered number(8),
product_rate number(5,2),
constraint f_orderkey
foreign key(deltorder_no)
references sales_order.order_no
);


8.applying check constraints

these are for checking purpose. following table illustrates its use

create table Client_Master(
Client_no varchar2(10) check (Client_no like 'C%'),
Name varchar2(20) check(Name = upper(name)),
Address varchar2(20),
Pincode number(6)
);    
The above table contains the check constraints declared at column level.
check on first column indicates that the Client_no should start with 'C' only.
check second column indicates the Name should be in upper case only.

declaring check constraints at table level

create table Client_Master(
Client_no varchar2(10),
Name varchar2(20),
Address varchar2(20),
Pincode number(6),
check (Client_no like 'C%'),
check(Name = upper(name))
);    

restriction on check
a.the condtion must be a boolean
b.the condition can't contains subqueries or sequences
c.the condition can't include SYSDATE,UID,USER or USERENV SQL functions


9.the user constraint table
oracle stores information about user constraints in table called USER_CONSTRAINTS.
querying this table gives the information about the table on which constraints are
imposed.

select Owner,Constraint_Name,Constraint_Type form User_Constraints
where table_name = 'Sales_Order_Details';

10. Defining integrity constraint via alter table

alter table Sales_Order_Details add constraint
Order_fkey foreign key(deltorder_no) references Sales_Order
modify(qty_ordered number(8) not null);

11.dropping integrity constraints via alter table

alter table suppler_Master drop primary key;

alter table sales_Order_Details drop constraint fk_prno;


12.Default value concept

if we want to make any column as default then we use this default key, the following
illustrates this concept

create table sales_order(
order_no primary key,
del_type char(1) default 'F'
);

in the above table del_type column is decalred as 'F' by default. which mean the delivery
type of the order is whether Full time(F) / Part Time (P). if you left that column
blank while entering the records in to the table it assumes the default value as Full Time (F).

INTERACTIVE SQL - COMPUTATION DONE ON TABLE DATA
------------------------------------------------
the arthematic operations like +,-,/,*,**,() can be performed on table data.

13.
select * Product_no, Description, Sell_price*0.05, Sell_price*1.05 from product_master;

14.Renaming columns used with expression list

select * Product_no, Description, Sell_price*0.05 Increse, Sell_price*1.05 New_Price
from product_master;

where Increse and New_Price are the aliase names for the default columns which are
goin to generate.

15.logical operators

a. AND operator
select product_no, Description from product_master where profit_percent>=10
and profit_percent<=20;

b. OR Operator

select Client_no,Client_name form Client_Master where (PinCode = 507001 or PinCode = 507002);

c. NOT Operator

select Client_no,Client_name form Client_Master where NOT (PinCode = 507001 or PinCode = 507002);

c. Range searching with BETWEEN

select product_no, Description from product_master where profit_percent
between 10 and 20;

d. pattern matching with like operator

% - matchs any string
_ - matchs any character

select * from client_master where Name like 'ra%';

select * from client_master where Name like '_r%' or Name like '_a%';

e. IN and NOT IN Predicates

select Name,Address1,Address2,PinCode from Client_Master where Name IN ('vamsi','Ramesh');

it selects name address and pincode from client_maste table where
the name is eigther vamsi or Ramesh.

select Name,Address1,Address2,PinCode from Client_Master where Name NOT IN ('vamsi','Ramesh');
        
it selects name address and pincode from client_maste table where
the name is not eigther vamsi or Ramesh.


16. Manipulations on Oracle DUAL Table

DUAL is a small work table which consists of only one row and one column and
contains value x in that column.beside arthematic calculations it also supports
date retrivel and its formatting.

ex:
select 2*2 from dual;

select sysdate from dual;

17. various oracle functions
a. group functions - aggregate functions
b. scalar functions - single row functions it includes
    a. string functions
    b. number functions
    c. conversion function
    d. date functions


17.1 Aggregate Functions

a. select avg(Sell_Price) "Average" from Product_Master;

b. select min(Bal_due) "Minimum Due" from Clent_Master;

c. select count(bal_due) "No of Balances" from Client_Master;

d. select count(*) "Toal" from Product_Master;

e. select max(Bal_due) "maximum Due" from Client_Master;

f. select sum(Bal_due) "Toatal Bal Due" from Client_Master;

17.2 Numeric Functions

a. select abs(-15) "Absolute" from dual;

b. select power(3,2) "Power" from dual;

c. select round(15.19,1) "Round" from dual;

d. select sqrt(25) from dual;

17.3 String functions

a. select lower('Ramesh VAMSI') from dual;

b. select initcap('Ramesh VAMSI') from dual;

c. select upper('Ramesh vamsi') from dual;

d. select sustr('cobra',3,4) from dual;

e. select length('Ramesh') from dual;

f. select ltrim('tanisha','ta') from dual;

g. select rtrim('sunila','a') from dual;

h. select lpad('vamsi',10,'*') from dual;

i. slect rpad(Name,10,'*') from Client_Master where Name='tuner';

j. select to_char(sysdate,'dd-mon-yyyy') from dual;

k. select to_number('a') from dual;


INTERACTIVE SQL - GROUPING DATA FROM TABLE IN SQL
-------------------------------------------------

18.GROUP BY CLAUSE

select product_no,sum(qty_ordered) "total quality ordered" from sales_order_details
GROUP BY product_no;

select 'Student ' || (select sname from student where sno = markid) ||' secured total score of ' || total ||'in 1st sem exams. ' "Result in words" from csemarks;

select dno,dname from depart where dno=101 union select dept,sname from student where dept=101;
select dno,dname from depart where dno=101 intersect select dept,sname from student where dept=101;
select dno,dname from depart where dno=101 minus select dept,sname from student where dept=101;

19.INDEXES

A index is an ordered list of the contents of a column,(or a group of columns) of a table.

Duplicate/Unique indexes:
The indexes that allows duplicate values for the indexed column i,e DUPLICATE INDEX
The indexes that allows no duplicate values for the indexed column i,e UNIQUE INDEX

Creation of An Index

Two types of indexes existing are
1.Simple Index
2.Composite Index.


Creating Simple Index:

    create index idx_student_dept on student(dept);
    
Creating Composite Index:

    create index idx_student_no on student(sno,dept);
    
Deleting an index:    

    drop index idx_student_no;
    
Creating Unique Index:    

    create unique index idx_student_no on student(sno,dept);
    
    
Rverse Key Index:

Creating a reverse key index, when compared to a simple index, reverses each byte
of the column being indexed while keeping the column order.

    create index idx_student_dept on student(dept) REVERSE;

converting reverse index to no reverse index.

    alter index idx_student_dept REBUILD NOREVERSE;
    
Note: a normal index cannot be rebuild as a reverse key index.

Bitmap Indexes:

    The advantage of using bitmap indexes are gratest for low cardinality columns
    i,e columns in which the number of distinct values is small compared to the
    number of rows in the table.
    
    create BITMAP INDEX idx_student_dept on student(dept);
    
Benfits of using bitmap indexes:

a.Reduced response time for large classes of ad hoc queries.
b.A substantial reduction of space usage compared to other indexing techniques.
c.Dramastic performace gain even in very low end hardware.

Function Based Indexes:

create index idx_student_dept on student(upper(sname));


Key Compressed Index:

key compression breaks an index key into a prefix and suffix entry. Compression is
achived by sharing the prefix entries among all the suffix entries in a index block.


    create index idx_student_dept on student (dept) compress 1;
    
Partitioning Indexes
    Similar to partitioning the tables oracle allows to partition the indexes. however
    partitioning the index is difficult than a table. the types of indexes are as follows.
    
local indexes
    -    local prefixed
    -    local non-prefixed
    
    create index idx_student_dept on student(deptno) local;
    
Global indexes
    -    global prefixed
    -    global non-prefixed
    
    
INTERACTIVE SQL - TRIGGERS
--------------------------

The oracle engine allows the user to define procedures that are implicitly executed (i,e
executed by oracle engine itself), when insert,update or delete is issued against a table
from SQL* plus or through an application.

use of database triggers:
- trigger applied to DML statements
- prevent invalid transactions
- used for audit trail
- enforce complex security authorization

database triggers Vs Procedures

- triggers won't accept perameters
- tiggers will execute internally by database engine itself

how to appllly database triggers

- a trigger event or statement ( insert, delete or update).
- a trigger restiction ( using 'when' clause)
- a trigger action ( encounters any triggering statement is true)

Types of triggers

- row triggers
    it is fired each time a row in the table is affected by the triggering statement.
- statement triggers
    it is fired once behalf of the triggering statement.
- Before Vs after triggers
    when defining trigger it is nessesary to define trigger timing, i.e when the
    trigger is need to fire.
    
    - Before trigger
        with this the trigger will execute before the statement.it is generally used
        before insert or update statement.
        
    - After trigger
        with this the trigger will execute after the statement.if a before trigger
        is already present an after trigger can perform different actions on the same
        staement.
- Combination triggers
    - before row trigger - before modifying each row
    - after row trigger - after modifying each row
    
- Syntax

    CREATE OR REPLACE TRIGGER <TRIGGER NAME> [BEFORE, AFTER]
    {DELETE, INSERT, UPDATE} ON <TABLE NAME>
    [REFERENCING {OLD AS old,NEW AS new}]
    {FOR EACH ROW [WHEN <CONDITION>]}
    DECLARE
        <DECLARATIONS IF ANY>
    BEGIN
        <BODY OF THE PROGRAM>
    EXCEPTION
        <EXCEPTION BODY>
    END;
    
- deleting a trigger

    DROP TRIGGER <TRIGGER NAME>;
    
- RAISE_APPLICATION_ERROR PROCEDURE
    it is used to issue user defined error messages
    
    - syntax
        RAISE_APPLICATION_ERROR(<error number>,<message>);
        
    error number - is a negative integer in the range -20000 to -20999
    message - is a character string up to 2048 bytes in length.
    
-EXAMPLE - 1

    create or replace trigger csemarks_danger
    before insert
    on csemarks
    for each row
    declare
        dsm number(3);
    begin
        dsm := :new.ds;
        
        if dsm<=20 then
            raise_application_error(-20000,'PLEASE GIVE PASSMARKS FOR DS');
        end if;
    end;

    insert into csemarks values(5001,15,61,88,97,45,262,88);
    
    SQL> insert into csemarks values(5001,15,61,88,97,45,262,88);
    insert into csemarks values(5001,15,61,88,97,45,262,88)
     *
    ERROR at line 1:
    ORA-20000: PLEASE GIVE PASSMARKS
    ORA-06512: at "ITCDISC.CSEMARKS_DANGER", line 7
    ORA-04088: error during execution of trigger 'ITCDISC.CSEMARKS_DANGER'
    
    DROP TRIGGER CSEMARKS_DANGER;
    
-EXAMPLE - 2
    
    
    create table company(empid number(4),empname varchar(20),desgnation varchar2(20),deptno number(2),salary number(7,2));
    
    insert into company values(&empid,'&empname','&designation',&deptno,&salary);
    
     EMPID EMPNAME DESGNATION DEPTNO SALARY
    ------ -------------------- -------------------- ---------- ----------
     1002 Ramesh programmer 11 15000
     1003 krishna operator 11 10000
     1004 ravi sales boy 11 5000
     1005 guru incharge 11 7000
     1006 guru supervisor 11 10000
     1001 vamsi manager 11 20000
    
    
    -- trigger to be raised if salary of all other desgnations should not
    -- exceed salary of manager
    
    set serveroutput on;
    
    create or replace trigger salary_danger
    before insert
    on company
    for each row
    
    declare
        tdeptno company.deptno%type;
        tdesg company.desgnation%type;
        tdesgn company.desgnation%type;
        tsal company.salary%type;
        tsaln company.salary%type;
    begin
        tdeptno := :new.deptno;
        tdesgn := :new.desgnation;
        tsal := :new.salary;
                    
        select desgnation,salary into tdesg,tsaln from company where
        salary = (select max(salary) from company group by deptno having deptno = tdeptno);
        
            if tsal >= tsaln and tdesg != tdesgn then
                raise_application_error(-20202,'ENTERED SALARY IS MORE THAN MANAGER SALARY');            
            end if;        
        
        
    exception
        when no_data_found then
        dbms_output.put_line('sorry no data found');
    end;

    /    


GENERATING PRIMARY KEY USING SEQUENCE

    create table order_details (orderno number(4),client_no number(3),total_bill number(5,2),address varchar2(30));
    
systax for sequence creation

    create sequence order_sequence
    increment by 1
    start with 101;
    
the following is trigger code to generate primary key

    create or replace trigger order_sequence_trigger
    before insert on order_details
    for each row
    
    declare
        p_key order_details.orderno%type;
        
    begin
        select order_sequence.nextval into p_key from dual;
        :new.orderno := p_key;
    end;
    
    /
    
Now user only need to insert the values other than orderno

    insert into order_details(client_no,total_bill,address) values(101,900,'khammam');
    insert into order_details(client_no,total_bill,address) values(101,100,'warangal');
    insert into order_details(client_no,total_bill,address) values(101,100,'karimnagar');
        
        
    select * from order_details;
    
     orderno client_no total_bill address
    -------- ---------- ---------- ---------------
     101 101 900 khammam
     102 101 100 warangal
     103 101 100 karimnagar
        
        
    
CREATING PRIMARY KEY WITH MAX FUNCTION

consider the company table
the following trigger will take the maximum number of the given column and assign
the incremented value to the column.

create or replace trigger max_value_trigger
before insert on company
for each row

declare
    max_p_key company.empid%type;
    p_key company.empid%type;
    
begin

    select max(empid) into max_p_key from company;
    p_key := max_p_key+1;
    :new.empid := p_key;
    
end;


insert into company(empname,desgnation,deptno,salary) values ('sandeep','watchman',11,1500);
    

VIEWS:
-----

To reduce reduendant data to the minimum possible, Oracle allows the creation of an object
called view. View is that it is stored only as definition in oracle system catalog.When a
view is used to manipulate table data the underlying base table will be completely invisible.
This will give the level of data security required.

A query fired on a view will run slowly than a query fired on a base table.

Reasons to crate views

- when data security is required.
- when data redundancy is to be kept minimum.

SYNTAX:

    CREATE VIEW <VIEW NAME> AS
    SELECT <COLUMNS> FROM <TABLE NAME>
    WHERE <CONDITION>
    GROUP BY <GROUP BY CRITERIA>
    HAVING <PREDICATE>

ORDER BY clause can't be used in creation of a view.


EXAMPLE:
    create view order_details_view as select * from order_details;
    
     and as usually all the DML statements can be implemented.

    insert into order_details_view values(104,102,200,'vijayawada');
    insert into order_details_view values(105,103,250,'hyderabad');
    insert into order_details_view values(106,107,205,'khammam');
    insert into order_details_view values(107,104,500,'warangal');

If a view defined from multiple tables which have no referencing clause then
insert, update and delete operations are not allowed.

If a view defined from multiple tables which have been created with referencing
clause then the behavior of the view is
- insert cperation is not allowed
- delete or modify operation do not affect master table.
- the view can be used to modify the columns of the detail table included in the view
- if delete operation is executed in the view the corresponding records from the
detail table will be deleted.


Common restrictions on updatable views

for a view to be updatable then the view definition must not have

- aggregate function
- distinct, grop by or having clause
- subquries
- constants, strings or value expressions like sell_price * 15;
- union,intersect or minus clause
- if a view is defined from another view then the second view should be updatable


destroying a view

drop view <view name>;

CLUSTERS:
--------

It is an important concept of improving performace in oracle. the concept of a cluster is where member records are stored physically near the parent records. Each cluster stores table data as well as maintains the cluster index that is used to sort table data.

cluster may have a negative performace impact on the data manipulation transaction and on queries that only reference on of the tables in the cluster.


create cluster sales_ord(ordno varchar2(5));

create table slaes_order(ordno varchar2(5) primary key,order_date date,client_no varchar(6),
address varchar2(30)) cluster sales_ord(ordno);

create table sales_ord_det(det_no varchar2(5) primary key,product_no number(6)) cluster sales_ord(ordno);

destroying the cluster;

drop cluster <cluster name>;


SNAPSHOTS:
---------

A snapshot is a recent copy of a table from database or in some cases, a subset of rows/columns
of a table.

in a distributed environment these are usefull because:
- respose time improves
- once a snapshot is built on a database it will work even though
the database node doesn't exists.

these are used dynamically replicate data between distributed databases.

read-only snapshots are the most comman type of snapshots.

snapshots can be simple or complex.


example:

    create snapshot new_company
    pctfree 10
    pctused 70
    tablespace system
    storage(initial 50k next 50k pctincrease 0)
    refresh
        start with round(sysdate+7)+2/24
        as select * from orderdetails;


destroying a snapshot

drop snapshot <snapshot name>;




SECURITY MANAGMENT USING SQL:
----------------------------

GRANT AND REVOKING PERMISSIONS:
- The rights that allows the user of some or all of the oracle resource
on the server are called PRIVILEGES.
- If a owner of a object given permission to acces that object by another user then
it is said to be GRANTING OF PRIVILEGES.
- Privileges once granted can be taken back by the owner then it is said to be
REVOKING OF PRIVILEGES.


EXAMPLES:

    GRANT <OBJECT PRIVILEGE>
    ON <TABLE NAME>
    TO <USER NAME>
    [WITH GRANT OPTION];
    
    
    INSERT ,DELETE, ALTER, INDEX, SELECT, UPDATE ARE THE OBJECT PRIVILEGES
    IF OWNER WANT TO GIVE ALL THE ABOVE PRIVILEGES THEN KEEP THE OBJECT PRIVILEGE
    AS ALL.
    
WITH GRANT OPTION allows the grantee to in turn grant object privileges to other users.

grant select on company to scott;

grant select,update on company to scott;


revoking the privileges:

revoke select on company from scott;

revoke select,update on company from scott;


Referencing a table belonging to another user

select * from sunita.product_master;

Granting the privilleges when a grantee has been given the grant privileges

grant select on sunita.product_master to scott;

OOPS IN ORACLE:
--------------

An upgradation to Oracle 8i immediately means that for developers there are three differenr
flavors oracle avilable those are

Relational - The traditional ORACLE relational database
Object Realational - The traditional ORACLE relational database, extended to include
         Object oriented concepts and structures such as abstract datatypes
         nested tables,and varying arrays.
        
Object Oriented - Whose design is based solely on Object Oriented Analysis and Design Principles

Object Types:
Abstract Datatypes : it has one or more subtypes.

Nested Tables: A table with in a table is nested table.
Varying arrays: It is a set of objects, each with the same datatype. the size of arry is
limited when it is created.When a table is created with varying array the array is a nested
table with a limited set of rows.

Naming conventions for Objects

- Table and column name will be singular
- Abstract datatype names will be singular nouns with a _ty suffix
- Table and datatype names will always be uppercase
- Column names will always be lower case
- Object view names will always be singular nouns with a _OV suffix
- Nested table names will be plural nouns with a _NT suffix
- Varying array names will be plural with a _VA suffix

create type address_ty as object
(street varchar2(30),city varchar2(20),state varchar2(20),zip number(6));

create type person_ty as object
(name varchar2(30),address address_ty);

create table customer (customerid number,person person_ty);


the user_tab_column data dictionary view can be quried to see the datatype assosiated
with each column in the given table.

select column_name,data_type from
user_tab_columns where table_name = 'customer';

in the same way user_type_attrs view displays information about the attributes of users
abstract data types.

select attr_name,length,attr_type_name from user_type_attrs
where type_name = 'person_ty';


Inserting records

insert into customer values(101,person_ty('vamsi',address_ty('leninnagar',
'khammam','Andhrapradesh',507001)));

insert into customer values(102,person_ty('Ramesh',address_ty('KU X Roads',
'Warangal','Andhrapradesh',507001)));

retriving data:

select customerid, client.person.name from customer client;

select customerid, client.person.name,client.person.address.city from customer client;

Object Views: They provide ability to overlay object oriented structures such as
datatypes on existing relational tables.

create table customer(customerid number(4),name varchar2(30),street varchar2(30),
city varchar2(20),state varchar2(20),zip number);
        

create or replace type address_ty as object
(street varchar2(30),city varchar2(20),state varchar2(20),zip number(6));

create or replace type person_ty as object
(name varchar2(30),address address_ty);

create or replace type customer_ty (customerid number,person person_ty);

create or replace view customer_ov(custpmerid,person) as select customerid,
person_ty(name,address_ty(street,city,state,zip)) from customer;

insert into customer_ov values(111,person_ty('vamsi',address_ty('leninnagar','Khammam',
'Andhrapradesh',507001)));

insert into customer_ov values(112,person_ty('Ramesh',address_ty('ku x road','warangal',
'Andhrapradesh',507001)));


Nested tables:

create type name_ty as object(name varchar2(30),address address_ty);

create or replace type dependent_ty as object (relation varchar2(20),
name name_ty,age number);

create or replace type dependent_list as table of dependent_ty;

create or replace type employee_info_ty as object(empid number(3),name name_ty,
salary number(6,2),dept_id number(5),dependents dependent_list);

create table employee_info of employee_info_ty oidindex oid_employee_info
nested table dependents store as dependents_ty;

To Create Varying array: use the -- as varray() -- clause of the create type command

create type company_address_ty as varray(3) of varchar2(1000);

create table company_info(company_name varchar2(20),address company_address_ty);

Oracle stores the varying array data internally using the raw datatype.

desc company_info;

insert into company_info values('RAM computers',company_address_ty('leninnagar,khammam,ap',
'ku x road,warangal,ap','dilsuknagar,hyderabad,ap'));

REFERENCING OBJECTS
-------------------
It will be done with REF datatype

Examples for the use of REF datatype

create type dept_ty as object(dname varchar2(20),address varchar2(30));

create table department of dept_ty;

create table employ(ename varchar2(20),enum number,edept REF dept_ty SCOPE IS department);

insert into department values(dept_ty('sales','khammam'));
insert into department values(dept_ty('spares','warangal'));
    
select * from department;

select REF(d) from department d;

insert into employ select 'vamsi',101,ref(d) from department d where d.dname = 'sales';

select * from employ;

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy