SQL

ORACLE
DML DDL TCL DCL
(data manipulation): (data definition  lang.): (transaction control lang.): (data control lang.):
Auto commit-off Auto commit on
Insert Create Commit Grant
Update Alter Rollback revoke
Delete Drop Save point
merge truncate
RETRIVAL: select

SQL  is not case sensitive but it is case sensitive within the table
Describe : is used to see structure of a table
• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
Distinct : Used to remove duplicate values in column.
Ex: select  distinct deptno from emp .it displays distinct or unique department numbers
Where clause:  used for filtering records.
Ex: select * from emp
Where

>,<,=,not NULL,and, or…etc

AND: both left side and right side should be true to return a value.
OR: returns a value if either of conditions is ture.

LIKE: This allows you to perform pattern matching.
% allows you to match any string of any length (including zero length)
_ allows you to match on a single character



IN: The IN function helps reduce the need to use multiple OR conditions.
SELECT columns
FROM tables
WHERE column1 in (value1, value2, .... value_n);


Orderby:used  to arrange records in ascending or descending order, by default sql  arranges in ascending order.

Group by: The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
WHERE clause constraints are evaluated on the data before it is grouped.
HAVING clause constraints are evaluated on the results of the data after it has been grouped together. You can specify multiple columns in a GROUP BY clause
SELECT STAMETENT  and group statement should have same number of columns.
Sql functions: performs calculations on data, convert data to different forms. All sql funitons returns a value sometime they accept input parameters. Sql funtions are of 2 types
http://www.w3schools.com/sql/sql_functions.asp
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

SQL Scalar functions
SQL scalar functions return a single value, based on the input value.
Useful scalar functions:
• upper() - Converts a field to upper case
• lower() - Converts a field to lower case
length() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified or whole no.

Dual: is a dummy functions used with one row and one column.
Convertion functions :
1. Implicit convertion: automatically converts one type to another
A=number b=varchar
b=a , implicit convertion automatically converts number datatype to varchar.
And varchar to date
2. Explicit conversion:
To_char    from(date or number)-> char
To_number   from(varchar)->number
To_date
To_lob
Count: it counts number of rows in column and will not count null values.
DECODE: works like if,else if ,else condition
Ex: select  job, decode(job, ’analyst’,10,’manager’,20,40)  from emp
It assigns     10 to analyst
                   20 to manager
             40 t o others.
CASE: this is similar to decode but its advanced version over it. Decode is a deprecated  method  and not preferred to used in latest versions.
 Case when  job=’analyst’ then 10
When job=’manager’ then 20
Else 40
End case
From employe.

select  distinct job,
case JOB when 'ANALYST' then 10
when 'MANAGER' then 20
else 40
END CASE FROM EMP

TRANSLATE AND REPLACE:
Translate:does by character by character
or example:
translate('1tech23', '123','456'); would return '4tech56'
translate('222tech', '2ec', '3it'); would return '333tith'

Replace: replaces entire string


Joins: used to get data form multiple tables
Different types of joins are:
1.Cartesian join
Equi joint+ non equi joint
2.inner join
   a.equi join: Return rows when there is at least one match in both tables
3.outer join: matched rows plus ummatched rows from either of table. (+) used for outer  join
   a.left outer joint: Return all rows from the left table, even if there are no matches in the right table
  b. right outer   joint: Return all rows from the right table, even if there are no matches in the left table
    c.full outer oint: Return rows when there is a match in one of the tables
4.self joint



SETS: union, union all, intersection ,minus
Set operator should satisy
1. Number of columns  in each select statement  should  be same
2. Corresponding data type should be same
UNION ALL
Combines the results of two SELECT statements into one result set.
UNION
Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.
MINUS
Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.
INTERSECT
Returns only those rows that are returned by each of two SELECT statements.
.
• We cannot use orderby  in header select statement
• Always header select statement has priority
Sub query:  a sub query is a select statement that is embedded in one of the following classes of a select statement. Sub query may be single row or multi row..
     Where,having,from….
WHERE clause
Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.
FROM clause
A subquery can also be found in the FROM clause. These are called inline views.
For example:
select suppliers.name, subquery1.total_amt
from suppliers,
   (select supplier_id, Sum(orders.amount) as total_amt
   from orders
   group by supplier_id) subquery1,
where subquery1.supplier_id = suppliers.supplier_id;
In this example, we've created a subquery in the FROM clause as follows:
(select supplier_id, Sum(orders.amount) as total_amt
 from orders
 group by supplier_id) subquery1
This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.

SELECT clause
A subquery can also be found in the SELECT clause
The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as SUM, COUNT, MIN, or MAX is commonly used in the subquery.

Pseudo-column
A pseudo-column is an Oracle assigned value (pseudo-field) used in the same context as an Oracle Database column, but not stored on disk
1. SYSDATE
2. SYSTIMESTAMP
3. ROWID
4. ROWNUM
5. USER
6. UID
7. LEVEL
8. CURRVAL
9. NEXTVAL


1. SYSDATE
It shows the Current date from the local or remore database . We can use the CURRENT_DATE also with for the same purpose.
Code : SELECT SYSDATE FROM DUAL
2. SYSTIMESTAMP
Systimestamp function returns the current system date and time (including fractional seconds and time zone) on your database.
Code :SELECT SYSTIMESTAMP  FROM DUAL

3. ROWID
Rowid is a pseudo column that uniquely identifies a row within a table, but not within a database. It is possible for two rows of two different tables stored in the same cluster to have the same rowid
Code :SELECT ROWID FROM EMP
4. ROWNUM
Rownum numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.
Code :SELECT ROWNUM  FROM EMP WHERE ROWNUM <=10
5. USER
User is a pseudo column that returns the name of the user currently connected to the session.
Code :SELECT USER FROM DUAL
6. UID
Uid is a pseudo column that returns the id number of a user currently connected to the session.
Code :SELECT UID FROM DUAL
7. LEVEL
LEVEL pseudo-column is an indication of how deep in the tree one is. It is used in hierarchical queries along with CONNECT by clause.
Code :SELECT  level, empno, ename, mgrFROM     emp
CONNECT BY PRIOR empno = mgr
START WITH mgr IS NULL
8. NEXTVAL
NEXTVAL is used to invoke a sequence. If nextval is invoked on a sequence, it makes sure that a unique number is generated.
Code :SELECT .NEXTVAL FROM DUAL
9. CURRVAL
CURRVAL can only be used if a session has already called nextval on a trigger. currval will then return the same number that was generated with nextval.
Code :SELECT .CURRVAL FROM DUAL

DELETE STATEMENT: The DELETE statement allows you to delete a single record or multiple records from a table.
The syntax for the DELETE statement is:
               DELETE FROM table
              WHERE predicates;
TRUNCATE STATEMENT: The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
Syntax to TRUNCATE a table:
TRUNCATE TABLE table_name;
TCL :
Commit
If the transaction is commited, the database ensures that the modified data can always be found, even if the database or the hard disk crashes. Of course, for the latter to be true, a decent backup and recovery concept must be in place.
Rollback
If a transaction is rolled back, the database system ensures that no trace of the modified data will be found, that is, the database behaves as though the transaction had never been started.
Oracle uses undo to execute a rollback.
DELETE (DML) delete a single record or multiple records from a table.
TRUNCATE(DDL) all the rows from the table
DROP(DDL) Drops table

INSERT Statement
The INSERT statement allows you to insert a single record or multiple records into a table.
INSERT INTO VALUE
        INSERT INTO  table (column-1, column-2, ... column-n)
       VALUES(value-1, value-2, ... value-n);
INSERT INTO SELECT
INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2"

UPDATE statement
The UPDATE statement allows you to update a single record or multiple records in a table.
The syntax for the UPDATE statement is:
UPDATE tableNAME
SET column = expression
WHERE predicates;

CREATE TABLE Statement
The CREATE TABLE statement allows you to create and define a table.
The basic syntax for a CREATE TABLE statement is:
CREATE TABLE table_name
( column1 datatype null/not null,
  column2 datatype null/not null,
  ...
);

Creating table from another table with  data:
Create  table as
Creating table structure only from another table without any  data:
Create  table as where 1=2

Data dictionary/catalog:http://www.oracle.com/pls/db92/db92.catalog_views?remark=homepage

constraints

• "Check" Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, an Oracle check constraint can be added to the table definition to ensure the validity of the region column.
 
• Not Null Constraint: This Oracle constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL insert and update time.
 
• Primary Key Constraint: This Oracle constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and this Oracle constraint will create a unique index on the target primary key.
 
• References Constraint: This is the foreign key constraint as implemented by Oracle. A references constraint is only applied at SQL insert and delete times.  At SQL delete time, the references Oracle constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table.
 
• Unique Constraint: This Oracle constraint is used to ensure that all column values within a table never contain a duplicate entry.
• DEFAULT Constraint
The DEFAULT constraint is used to insert a default value into a column.
Oracle constraint views:
DBA ALL USER
dba_cons_columns all_cons_columns user_cons_columns
dba_constraints all_constraints user_constraints
dba_indexes all_indexes user_indexes
dba_ind_partitions all_ind_partitions user_ind_partitions
dba_ind_subpartitions all_ind_subpartitions user_ind_subpartitions
 

 




Note:every constraint is uniquely identifie by its name.oracle create unique constraint name if we donot  mention its name.
Syntax:
Create table
(
column1 datatype1 ;
.
.
.
)

Create table
(
column1 datatype1 ;
.
.
Constraint type (colunm1...columnN)
)

Constraint can be defined at column level or table level.


Check constraint CREATE TABLE Customer
(SID integer CHECK (SID > 0),
Last_Name varchar (30),
First_Name varchar(30));
Foreign key CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date date,
Customer_SID integer references CUSTOMER(SID),
Amount double);
Not null key CREATE TABLE Customer
(SID integer NOT NULL,
Last_Name varchar (30) NOT NULL,
First_Name varchar(30));
Unique key cREATE TABLE Customer
(SID integer Unique,
Last_Name varchar (30),
First_Name varchar(30));
Primary key CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
Default key cREATE TABLE Student
(Student_ID integer Unique,
Last_Name varchar (30),
First_Name varchar (30),
Score DEFAULT 80);



Alter table commands:
Its a ddl command.
• Add new column or columns
• Modify a existing column
• Drop a column
• Enable/disable constraints
• Add a constraint
• Drop a constaint
• Rename table
• Rename column
SCHEMA: A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user AND created at the time of user creation. Schema objects are logical structures created by users to contain, or reference, their data. Schema objects include structures like tables, views, and indexes. You can create and manipulate schema objects using sql commands.
“scott”(DB user)----à scott(schema name)

Some of the most common schema objects are defined in the following section.

§ Tables
Tables are the basic unit of data storage in an Oracle database. Database tables hold all user-accessible data. Each table has columns and rows. Columns in a table is the different types of information that the table will contain and all instances of such data is stored in rows.

§ Indexes
Indexes are optional structures associated with tables. Indexes can be created to increase the performance of data retrieval. Just as the index in a book helps you quickly locate specific information, an Oracle index provides an access path to table data.

Indexes are created on one or more columns of a table. After it is created, an index is automatically maintained and used by Oracle. Changes to table data (such as adding new rows, updating rows, or deleting rows) are automatically incorporated into all relevant indexes with complete transparency to the users.


§ Views
Views are customized presentations of data in one or more tables or other views. A view can also be considered a stored query. Views do not actually contain data. Rather, they derive their data from the tables on which they are based, referred to as the base tables of the views.
Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view.

Views provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.

§ Clusters
Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.

Privileges
A privilege is a right to execute an SQL statement or to access another user's object. In Oracle, there are two types of privileges: system privileges and object privileges. A privileges can be assigned to a user or a role
System privileges
select name from system_privilege_map
Executing this statement, we find privileges like create session, drop user, alter database, see system privileges.
NAME                                      EXEMPT ACCESS POLICY                     
---------------------------------------- ON COMMIT REFRESH                        
CREATE EXTERNAL JOB                       MERGE ANY VIEW                           
CHANGE NOTIFICATION                       ADMINISTER DATABASE TRIGGER              
READ ANY FILE GROUP                       ADMINISTER RESOURCE MANAGER              
MANAGE ANY FILE GROUP                     DROP ANY OUTLINE                         
MANAGE FILE GROUP                         ALTER ANY OUTLINE                        
EXEMPT IDENTITY POLICY                    CREATE ANY OUTLINE                       
CREATE ANY SQL PROFILE                    DROP ANY CONTEXT                         
ADMINISTER ANY SQL TUNING SET             CREATE ANY CONTEXT                       
ADMINISTER SQL TUNING SET                 DEQUEUE ANY QUEUE                        
ALTER ANY SQL PROFILE                     ENQUEUE ANY QUEUE                        
DROP ANY SQL PROFILE                      MANAGE ANY QUEUE                         
SELECT ANY TRANSACTION                    DROP ANY DIMENSION                       
MANAGE SCHEDULER                          ALTER ANY DIMENSION                      
EXECUTE ANY CLASS                         CREATE ANY DIMENSION                     
EXECUTE ANY PROGRAM                       CREATE DIMENSION                         
CREATE ANY JOB                            UNDER ANY TABLE                          
CREATE JOB                                EXECUTE ANY INDEXTYPE                    
ADVISOR                                   GLOBAL QUERY REWRITE                     
ANALYZE ANY DICTIONARY                    QUERY REWRITE                            
EXECUTE ANY RULE                          UNDER ANY VIEW                           
DROP ANY RULE                             DROP ANY INDEXTYPE                       
ALTER ANY RULE                            ALTER ANY INDEXTYPE                      
CREATE ANY RULE                           CREATE ANY INDEXTYPE                     
CREATE RULE                               CREATE INDEXTYPE                         
IMPORT FULL DATABASE                      EXECUTE ANY OPERATOR                     
EXPORT FULL DATABASE                      DROP ANY OPERATOR                        
EXECUTE ANY RULE SET                      ALTER ANY OPERATOR                       
DROP ANY RULE SET                         CREATE ANY OPERATOR                      
ALTER ANY RULE SET                        CREATE OPERATOR                          
CREATE ANY RULE SET                       EXECUTE ANY LIBRARY                      
CREATE RULE SET                           DROP ANY LIBRARY                         
EXECUTE ANY EVALUATION CONTEXT            ALTER ANY LIBRARY                        
DROP ANY EVALUATION CONTEXT               CREATE ANY LIBRARY                       
ALTER ANY EVALUATION CONTEXT              CREATE LIBRARY                           
CREATE ANY EVALUATION CONTEXT             UNDER ANY TYPE                           
CREATE EVALUATION CONTEXT                 EXECUTE ANY TYPE                         
GRANT ANY OBJECT PRIVILEGE                DROP ANY TYPE                            
FLASHBACK ANY TABLE                       ALTER ANY TYPE                           
DEBUG ANY PROCEDURE                       CREATE ANY TYPE                          
DEBUG CONNECT SESSION                     CREATE TYPE                              
SELECT ANY DICTIONARY                     DROP ANY DIRECTORY                       
RESUMABLE                                 CREATE ANY DIRECTORY                     
DROP ANY MATERIALIZED VIEW                CREATE SYNONYM                           
ALTER ANY MATERIALIZED VIEW               DROP ANY INDEX                           
CREATE ANY MATERIALIZED VIEW              ALTER ANY INDEX                          
CREATE MATERIALIZED VIEW                  CREATE ANY INDEX                         
GRANT ANY PRIVILEGE                       DROP ANY CLUSTER                         
ANALYZE ANY                               ALTER ANY CLUSTER                        
ALTER RESOURCE COST                       CREATE ANY CLUSTER                       
DROP PROFILE                              CREATE CLUSTER                           
ALTER PROFILE                             DELETE ANY TABLE                         
CREATE PROFILE                            UPDATE ANY TABLE                         
DROP ANY TRIGGER                          INSERT ANY TABLE                         
ALTER ANY TRIGGER                         SELECT ANY TABLE                         
CREATE ANY TRIGGER                        COMMENT ANY TABLE                        
CREATE TRIGGER                            LOCK ANY TABLE                           
EXECUTE ANY PROCEDURE                     DROP ANY TABLE                           
DROP ANY PROCEDURE                        BACKUP ANY TABLE                         
ALTER ANY PROCEDURE                       ALTER ANY TABLE                          
CREATE ANY PROCEDURE                      CREATE ANY TABLE                         
CREATE PROCEDURE                          CREATE TABLE                             
FORCE ANY TRANSACTION                     DROP ROLLBACK SEGMENT                    
FORCE TRANSACTION                         ALTER ROLLBACK SEGMENT                   
ALTER DATABASE                            CREATE ROLLBACK SEGMENT                  
AUDIT ANY                                 DROP USER                                
ALTER ANY ROLE                            ALTER USER                               
GRANT ANY ROLE                            BECOME USER                              
DROP ANY ROLE                             CREATE USER                              
CREATE ROLE                               UNLIMITED TABLESPACE                     
DROP PUBLIC DATABASE LINK                 DROP TABLESPACE                          
CREATE PUBLIC DATABASE LINK               MANAGE TABLESPACE                        
CREATE DATABASE LINK                      ALTER TABLESPACE                         
SELECT ANY SEQUENCE                       CREATE TABLESPACE                        
DROP ANY SEQUENCE                         RESTRICTED SESSION                       
ALTER ANY SEQUENCE                        ALTER SESSION                            
CREATE ANY SEQUENCE                       CREATE SESSION                           
CREATE SEQUENCE                           AUDIT SYSTEM                             
DROP ANY VIEW                             ALTER SYSTEM
CREATE ANY VIEW                          
CREATE VIEW                              
DROP PUBLIC SYNONYM                      
CREATE PUBLIC SYNONYM                    
SYSOPER                                  
SYSDBA                                   
DROP ANY SYNONYM                         
CREATE ANY SYNONYM                       

Arguably, the most important system privileges are:
• create session (A user cannot login without this privilege. If he tries, he gets an ORA-01045).
• create table
• create view
• create procedure
• sysdba
• sysoper
Object privileges
privileges can be assigned to the following types of database objects:
• Tables
select, insert, update, delete, alter, debug, flashback, on commit refresh, query rewrite, references, all
• Views
select, insert, update, delete, under, references, flashback, debug
• Sequence
alter, select
• Packeges, Procedures, Functions (Java classes, sources...)
execute, debug
• Materialized Views
delete, flashback, insert, select, update
• Directories
read, write
• Libraries
execute
• User defined types
execute, debug, under
• Operators
execute
• Indextypes
execute
For a user to be able to access an object in another user's schema, he needs the according object privilege.
Object privileges can be displayed using all_tab_privs_made or user_tab_privs_made.
Grant command: is used to grant permission to users and assigned by dba .
Revoke command is uesed to revoke privilages from databasel

Creating user:with the help of create user statement ,oracle database users can be created.
Create user
Identified by
Grant privilage1,....privilageN to user1............userN  to user1,..........userN
Revoke privilage1......privilageN to user1.........userN to user1,............userN
Grant create table to  :allows to create table in this own schema
Grant create any table to :allow to create table in any schema

Role:role is a named group of related privileges which can be granted to users
ADV:
1.Ease ot use
2.maintenance is easy
Steps to create and assign role to users:
1.create a role
Create role[identified by ]
2.assign  privileges to role
Grant to
3.assign role to  users
Grant to

*grant statement with ,with grant option
aàbàc
grant select on emp to b
with grant option
now b can access and b can grant  permission to c user.
*grant statement with ,public- it grants permission to all users of DB

Sequence:  is a DB object, used to generate a sequence numbere.
Most of the cases sequence number can be used to generate primary keys of table.
Syntax:
Create sequence
Increment by
Start with
Maxvalue
Minvalue
Cyle/no clycle         ----by default no cylcle
Cache/nocache -----pregenerate
If you omit the MAXVALUE option, your sequence will automatically default to:
MAXVALUE 999999999999999999999999999

In sequence we use nextval and currval pseudo columns
Question:  While creating a sequence, what does cache and nocache options mean? For example, you could create a sequence with a cache of 20 as follows:
CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

Or you could create the same sequence with the nocache option:
CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

Answer:  With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.
By using alter statement we can change the following;
• Maxvalue
• Increment by
• Cylce
• Cache
But we cannot change “start with “
Syntax: alter sequence
              
Datadictionary:
Dba_sequnce/all_sequence/user_sequence


Synonyms: Is an alias or alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.and will not occupy any of the space.
Advantages:
• Mask the name and owner of an object,additional security of object  level
• Location transparency for remote object of a distributed environment
• Simplify sql statements

Creating or replacing a synonym
The syntax for creating a synonym is:
create [or replace]  [public]  synonym [schema .] synonym_name
for [schema .] object_name [@ dblink];
public:any one can access
drop [public] synonym [schema .] synonym_name [force];
Datadictionary:
Dba_synonyms/all_synonyms/user_synonyms

INDEX:
• Index always assciated to table ,for faster information retrieval
• Physical independent objects,which occupies extrastorage on the database.
• We can create and drop indexes with out effecting base table
• Indexes can be created on column or columns
• If any index contains more than one column then it si called as a composite index.32 and 30for bitmap index
Note:we cannot create a index on long/long raw columns
Types of index:
1.B tree index ----default----32
2.bitmap index ---30
3.funtion based index ---32
4.unique or non unique index --32
When ever you create a primary key on column oracle will create unique index on that column.
General guide line for creating index:
1.create an index if you are retreving less than 15% of total rows from a table.
2.to improve performance on joints of multiple table indexed columns are best suitable for indexes
3.generally small table are not required for index
4.indexed column should be used in where clause for better performance

The syntax for creating a index is:
CREATE [UNIQUE|BITMAP] INDEX index_name
  ON table_name (column1, column2, . column_n)
  [ COMPUTE STATISTICS ];
Drop index
Bitmap:DSS(decesion support system)/data warehousing application .it stores index entries in form of bit(0 ro 1).so on what column we are going to create bitmap index? –on columns with less  cardinality i.e number of distinct rows are less
Ex:gender (F or M),deptno.
Empno/lastname/ssn-----not suitable for bit map index.
Funtional basec index:
Create indexon table_name
It is not recommend to enable indexing when inseritng large data because it degrades the process so we need to disable index during file load and enable after finishing.
During this process there is a chance for unbalancing of indexes so we need to rebuild indexes.
Alter index rebuild;
Monitor is used to monitor usefullness of indexes.
    Alter indexmonitoring usage;
   Then select statement.....
  Select * from v$object_usages;
To disable alter indexnomonotoring usage;
Composite index: it contains more than one index
Datadictionary:
Dba_index/all_index/user_index


VIEW:logica object or table based on a one or more table or another view associated to select statement.view does not contain any data like table when ever you are selecting a data from view,that means you are selecting data from table associated to view.we can do all dml operations on view with some restirctions.
Ex:         abc(select ename from emp);
           Select * from abc;
Syntax:
            Create or replace view
           As ;
           [with check option][with read only]
With check option With read only
Ex: Create view sampleview
Create view sampleview As
As select * from sample Select * form sample where name=’sample’
With read only With check option;
_--------------------------
We cannot operate dml commands on it


View are of two types:
1.simple view
Create or replace view emp_view
As select empno,ename,hiredate from emp
2.joint view:views from multiple table which contains join
Create or replace view empfin
As select a.empno,b.dname,a.sal,a.com,a.sal+nvl(a.com,0) “total salary” from emp a,dept b
Where a.deptno=b.deptno
*In joint view when ever we use funtions ,we need to specify it with alias name.
Advantages of view:
• We have additional level security
• Restricts access at column level
• Simplify complex sql statements
• Present same data in different point of view
• Hide data complexity
Restrictions on a class:
If a view is defined by query that contains sets (unoion,intersect,union all) or distinct or group funtion or expressions(such as decode or dml) then we cannot do any dml operations on that view.
“key preserved table”: the key of resulatant table is also key of base table is called key preserved table.
General rule:
any insert or update or delete operatons on a join view can modify only one underlying table at a time.
Only key preserved table can be modigied
Join view should not contain more than one key preserved table







MERGE:
Merge is also know as upsert=update+insert
Merge into < a >  -------destination table
Using ------source table
On
When matches then
Update set      col1=val1
                     Col2=val2......
When unmached then
Insert  (col1) value(value1)

Multitable insert:
There are 3types of multitable insert are available
1.unconditional insert:  no condition
2.conditional all insert:dependes on condition
3.conditional first insert:if it satisfies more than one condition then inserts into first condition







INSERT ALL|FIRST
  [WHEN condition THEN] INTO target [VALUES]
  [WHEN condition THEN] INTO target [VALUES]
  ...
  [ELSE] INTO target [VALUES]
SELECT ...
FROM   source_query;
conditional multi-table insert
Multi-table inserts can also be conditional (i.e. we do not need to insert every record into every table in the list). There are some key points to note about conditional multi-table inserts as follows.
• we cannot mix conditional with unconditional inserts. This means that in situations where we need a conditional insert on a subset of target tables, we will often need to "pad out" the unconditional inserts with a dummy condition such as "WHEN 1=1";
• we can optionally include an ELSE clause in our INSERT ALL|FIRST target list for when none of the explicit conditions are satisfied;
• an INSERT ALL conditional statement will evaluate every insert condition for each record. With INSERT FIRST, each record will stop being evaluated on the first condition it satisfies;
• the conditions in an INSERT FIRST statement will be evaluated in order from top to bottom. Oracle makes no such guarantees with an INSERT ALL statement.
With these restrictions in mind, we can now see an example of a conditional INSERT FIRST statement. Each source record will be directed to one target table at most. Note that for demonstration purposes, the following example includes varying column lists and an ELSE clause.
SQL> INSERT FIRST
 2     --<>--
 3     WHEN owner = 'SYSTEM'
 4     THEN
 5        INTO t1 (owner, object_name)
 6        VALUES  (owner, object_name)
 7     --<>--
 8     WHEN object_type = 'TABLE'
 9     THEN
10        INTO t2 (owner, object_name, object_type)
11        VALUES  (owner, object_name, object_type)
12     --<>--
13     WHEN object_name LIKE 'DBMS%'
14     THEN
15        INTO t3 (owner, object_name, object_type)
16        VALUES  (owner, object_name, object_type)
17     --<>--
18     ELSE
19        INTO t4 (owner, object_type, object_name, created, object_id)
20        VALUES  (owner, object_type, object_name, created, object_id)
21     --<>--
22  SELECT owner
23  ,      object_type
24  ,      object_name
25  ,      object_id
26  ,      created
27  FROM   all_objects;


Assignmen:
Create table emp_10,emp_20,emp_30 with structure similar to emp and copy data from emp into emp_10,emp_20,emp_30 based on deptno using single insert all condition.

Create table emp_10 as select * from emp where 1=2
Create table emp_20 as select * from emp where 1=2
Create table emp_30 as select * from emp where 1=2
insert all
when deptno=10 then
into emp_10(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(empno,ename,job,mgr,hiredate,sal,comm,deptno)
when deptno=20 then
into emp_20(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(empno,ename,job,mgr,hiredate,sal,comm,deptno)
when deptno=30 then
into emp_30(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(empno,ename,job,mgr,hiredate,sal,comm,deptno)
select * from emp


set serveroutput on
declare
first varchar2(100):=' srinivas';
last varchar2(100):='patlolla';
fullname varchar2(200):='&fullname';
begin

dbms_output.put_line('fullname :'|| fullname);
dbms_output.put_line('first name is :'||first);
dbms_output.put_line('last name is :'||last);
end;

123passportphoto is a very easy to use passport photo website that provides six enhanced photos. I have never had an issue while using this ...

• 

  Jamaica stamping process - details

  Effective October 31, 2014, Embassy Kingston does not accept interview applications from third country nationals Effective October...
• 

  Struts2

  Introduction/ History of Struts The first version of Struts was released in June 2001. It was born out of the idea that JSPs and servle...
• Cloudera Hadoop

  Cloudera Hadoop Offers: §   HDFS  – Self healing distributed file system §   MapReduce  – Powerful, parallel data processing framew...

Search This Blog