Oracle Sql-4

Joins

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Join Conditions

Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list.

Equijoins

An equijoin is a join with a join condition containing an equality operator ( = ). An equijoin combines rows that have equivalent values for the specified columns.
For example the following query returns empno,name,sal,deptno and department name and city from department table.
select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,dept.city from emp,dept where emp.deptno=dept.deptno;
The above query can also be written like, using aliases, given below.

select e.empno, e.ename, e.sal, e.deptno, d.dname, d.city from emp e, dept d where emp.deptno=dept.deptno;

The above query can also be written like given below without using  table qualifiers.

select empno,ename,sal,dname,city from emp,dept where emp.deptno=dept.deptno;

And if you want to see all the columns of both tables then the query can be written like this.

select * from emp,dept where emp.deptno=dept.deptno;

Non EquiJoins.

Non equi joins is used to return result from two or more tables where exact join is not possible.
For example we have emp table and salgrade table. The salgrade table contains grade and their low salary and high salary. Suppose you want to find the grade of employees based on their salaries then you can use NON EQUI join.

select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s
     where e.sal between s.lowsal and s.hisal

Self Joins

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.
For example the following query returns employee names and their manager names for whom they are working.

Select e.empno, e.ename, m.ename  “Manager” from emp e,
      emp m where e.mgrid=m.empno

Inner Join

An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition.

Outer Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

• To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax, or apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B. 
•  
• To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax, or apply the outer join operator (+) to all columns of A in the join condition. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
• To write a query that performs an outer join and and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the ANSI FULL [OUTER] JOIN syntax.

For example the following query returns all the employees and department names and even those department names where no employee is working.

select e.empno,e.ename,e.sal,e.deptno,d.dname,d.city from emp e, dept d
    where e.deptno(+)=d.deptno;

That is specify the (+) sign to the column which is lacking values.

Cartesian Products

If two tables in a join query have no join condition, Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product.

Data Manipulation Language (DML) Statements

Data manipulation language (DML) statements query and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction.
The following are the DML statements available in Oracle.

• INSERT          :Use to Add Rows to existing table.
• UPDATE        :Use to Edit Existing Rows in tables.
• DELETE         :Use to Delete Rows from tables.
• MERGE          :Use to Update or Insert Rows depending on condition.

Insert

Use the Insert Statement to Add records to existing Tables.
Examples.
To add a new row to an emp table.

Insert into emp values (101,’Sami’,’G.Manager’,
             ’8-aug-1998’,2000);

If you want to add a new row by supplying values for some columns not all the columns then you have to mention the name of the columns in insert statements.

For example the following statement inserts row in emp table by supplying values for empno, ename, and sal columns only. The Job and Hiredate columns will be null.

Insert into emp (empno,ename,sal) values (102,’Ashi’,5000);

Suppose you want to add rows from one table to another i.e. suppose we have Old_Emp table and emp table with the following structure

Now we want to add rows from old_emp table to emp table. Then you can give the following insert statement:-

Insert into emp (empno, ename, sal)
      select empno, ename, sal from old_emp;

Multitable Insert

Suppose we have sales table with the following structure.

Sales

Now we want to add the rows from SALES table  Weekly_Sales Table in the following Structure.

To achieve the above we can give a multi table INSERT statement given below

Insert all
     Into week_sales(prodid,prodname,weekday,amount)
     Values (prodid,prodname,’Mon’,mon_amt)
     Into week_sales(prodid,prodname,weekday,amount)
     Values (prodid,prodname,’Tue’,tue_amt)
     Into week_sales(prodid,prodname,weekday,amount)
     Values (prodid,prodname,’Wed’,wed_amt)
     Into week_sales(prodid,prodname,weekday,amount)
     Values (prodid,prodname,’Thu’,thu_amt)
     Into week_sales(prodid,prodname,weekday,amount)
     Values (prodid,prodname,’Fri’,fri_amt)
     Into week_sales(prodid,prodname,weekday,amount)
     Values (prodid,prodname,’Sat’,sat_amt)
        Select prodid,prodname,mon_amt,tue_amt,wed_amt,thu_amt
            Fri_amt,sat_amt from sales;

Update

Update statement is used to  update rows in existing tables which is in your own schema or if you have update privilege on them.
For example to raise the salary by Rs.500 of employee number 104. You can give the following statement.

update emp set sal=sal+500 where empno = 104;

In the above statement if we did not give the where condition then all employees salary will be raised by Rs. 500. That’s why always specify proper WHERE condition if don’t want to update all employees.

For example We want to change the name of employee no 102 from ‘Sami’ to ‘Mohd Sami’ and to raise the salary by 10%. Then the statement will be.

update emp set name=’Mohd Sami’,
  sal=sal+(sal*10/100) where empno=102;

Now we want to raise the salary of all employees by 5%.

update emp set sal=sal+(sal*5/100);

Now to change the names of all employees to uppercase.

update emp set name=upper(name);

Suppose We have a student table with the following structure.

Now to compute total which is sum of Maths,Phy and Chem and average.

update student set total=maths+phy+chem,

    average=(maths+phy+chem)/3;

Using Sub Query in the Update Set Clause.
Suppose we added the city column in the employee table and now we want to set this column with corresponding city column in department table which is join to employee table on deptno.

update emp set city=(select city from dept
                          where deptno= emp.deptno);

Delete

Use the DELETE statement to delete the rows from existing tables which are in your schema or if you have DELETE privilege on them.
For example to delete the employee whose empno is 102.

delete from emp where empno=102;

If you don’t mention the WHERE condition then all rows will be deleted.
Suppose we want to delete all employees whose salary is above 2000. Then give the following DELETE statement.

delete from emp where salary > 2000;

The following statement has the same effect as the preceding example, but uses a subquery:

DELETE FROM (SELECT * FROM emp)
    WHERE sal > 2000;

To delete all rows from emp table.

delete from emp;

Merge

Use the MERGE statement to select rows from one table for update or insertion into another table. The decision whether to update or insert into the target table is based on a condition in the ON clause. It is a new feature of Oracle Ver. 9i. It is also known as UPSERT i.e. combination of UPDATE and INSERT.
For example suppose we are having sales and sales_history table with the following structure.



Now we want to update sales_history table from sales table i.e. those rows which are already present in sales_history, their amount should be updated and those rows which are not present in sales_history table should be inserted.

merge into sales_history sh
  using sales s
  on (s.prod=sh.prod and s.month=sh.month)
   when matched then update set sh.amount=s.amount
   when not matched then insert values (prod,month,amount);

After the statement is executed sales_history table will look like this.

Data Definition Language (DDL) Statements

Data definition language (DDL) statements enable you to perform these tasks:

• Create, alter, and drop schema objects
• Grant and revoke privileges and roles
• Analyze information on a table, index, or cluster
• Establish auditing options
• Add comments to the data dictionary

The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.
The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.
Oracle implicitly commits the current transaction before and after every DDL statement.
Many DDL statements may cause Oracle to recompile or reauthorize schema objects.

DDL Statements are
CREATE        :Use to create objects like CREATE TABLE, CREATE FUNCTION,
                        CREATE SYNONYM, CREATE VIEW. Etc.
ALTER           :Use to Alter Objects like ALTER TABLE, ALTER USER, ALTER
                         TABLESPACE, ALTER DATABASE. Etc.
DROP             :Use to Drop Objects like DROP TABLE, DROP USER, DROP
                        TABLESPACE, DROP FUNCTION. Etc.
REPLACE      :Use to Rename table names.
TRUNCATE   :Use to truncate (delete all rows) a table.

Create

To create tables, views, synonyms, sequences, functions, procedures, packages etc.

Example
To create a table, you can give the following statement

create table emp (empno number(5) primary key,
                   name varchar2(20),
                   sal number(10,2),
                   job varchar2(20),
                   mgr  number(5),
                   Hiredate  date,
                   comm number(10,2));

Now Suppose you have emp table now you want to create a TAX table with the following structure and also insert rows of those employees whose salary is above 5000.

Tax
Empno           Tax	Number(5)                Number(10,2)

To do this we can first create TAX table by defining column names and datatypes and then use INSERT into EMP SELECT …. statement to insert rows from emp table. like given below.

create table tax (empno number(5), tax number(10,2));

insert into tax select empno,(sal-5000)*0.40
                     from emp where sal > 5000;

Instead of executing the above two statements the same result can be achieved by giving a single CREATE TABLE AS statement.

create table tax as select empno,(sal-5000)*0.4
   as tax from emp where sal>5000

You can also use CREATE TABLE AS statement to create copies of tables. Like to create a copy EMP table as EMP2 you can give the following statement.

create table emp2 as select * from emp;

To copy tables without rows i.e. to just copy the structure give the following statement

create table emp2 as select * from emp where 1=2;

Transaction Control Language (TCL)

Transaction control statements manage changes made by DML statements.

What is a Transaction?

A transaction is a set of SQL statements which Oracle treats as a Single Unit. i.e. all the statements should execute successfully or none of the statements should execute.
To control transactions Oracle does not made permanent any DML statements unless you commit it. If you don’t commit the transaction and power goes off or system crashes then the transaction is roll backed.
TCL Statements available in Oracle are

COMMIT       :    Make changes done in  transaction permanent.
ROLLBACK  :    Rollbacks the state of database to the last commit point.
SAVEPOINT :    Use to specify a point in transaction to which later you can rollback.

COMMIT

To make the changes done in a transaction permanent issue the COMMIT statement.
The syntax of COMMIT Statement is
COMMIT  [WORK]  [COMMENT ‘your comment’];
WORK is optional.
COMMENT is also optional, specify this if you want to identify this transaction in data dictionary DBA_2PC_PENDING.
Example
insert into emp (empno,ename,sal) values (101,’Abid’,2300);
commit;

ROLLBACK

To rollback the changes done in a transaction give rollback statement. Rollback restore the state of the database to the last commit point.
Example :

delete from emp;

rollback;          /* undo the changes */

SAVEPOINT

Specify a point in a transaction to which later you can roll back.\

Example

insert into emp (empno,ename,sal) values (109,’Sami’,3000);
savepoint a;
insert into dept values (10,’Sales’,’Hyd’);
savepoint b;
insert into salgrade values (‘III’,9000,12000);

Now if you give

rollback to a; 

Then  row from salgrade table and dept will be roll backed. At this point you can commit the row inserted into emp table or rollback the transaction.

If you give

rollback to b;

Then row inserted into salgrade table will be roll backed. At this point you can commit the row inserted into dept table and emp table or rollback to savepoint a or completely roll backed the transaction.
If you give

rollback; 

Then the whole transactions is roll backed.

If you give

commit;

Then the whole transaction is committed and all savepoints are removed.

Data Control Language (DCL) Statements

Data Control Language Statements are used to grant privileges on tables, views, sequences, synonyms, procedures to other users or roles.
The DCL statements are

GRANT          :Use to grant privileges to other users or roles.
REVOKE       :Use to take back privileges granted to other users and roles.

Privileges are of two types :

• System Privileges
  
• Object privileges
  

System Privileges are normally granted by a DBA to users. Examples of system privileges are CREATE SESSION, CREATE TABLE, CREATE USER etc.
Object privileges means privileges on objects such as tables, views, synonyms, procedure. These are granted by owner of the object.
Object Privileges are

ALTER	Change the table definition with the ALTER TABLE statement.
DELETE	Remove rows from the table with the DELETE statement. Note: You must grant the SELECT privilege on the table along with the DELETE privilege.
INDEX	Create an index on the table with the CREATE INDEX statement.
INSERT	Add new rows to the table with the INSERT statement.
REFERENCES	Create a constraint that refers to the table. You cannot grant this privilege to a role.
SELECT	Query the table with the SELECT statement.
UPDATE	Change data in the table with the UPDATE statement.
	Note: You must grant the SELECT privilege on the table along with the UPDATE privilege.

Grant

Grant is use to grant privileges on tables, view, procedure to other users or roles

Examples

Suppose you own emp table. Now you want to grant select,update,insert privilege on this table to other user “SAMI”.

grant select, update, insert on emp to sami;

Suppose you want to grant all privileges on emp table to sami. Then

grant  all on emp to sami;

Suppose you want to grant select privilege on emp to all other users of the database. Then

grant select on emp to public;

Suppose you want to grant update and insert privilege on only certain columns not on all the columns then include the column names in grant statement. For example you want to grant update privilege on ename column only and insert privilege on empno and ename columns only. Then give the following statement

grant update (ename),insert (empno, ename)  on emp to sami;

To grant select statement on emp table to sami and to make sami be able further pass on this privilege you have to give WITH GRANT OPTION clause in GRANT statement like this.

grant select on emp to sami with grant option;

REVOKE

Use to revoke privileges already granted to other users.
For example to revoke select, update, insert privilege you have granted to Sami then give the following statement.

revoke select, update, insert on emp from sami;

To revoke select statement on emp granted to public give the following command.

revoke select on emp from public;

To revoke update privilege on ename column and insert privilege on empno and ename columns give the following revoke statement.

revoke update, insert on emp from sami;

Note :You cannot take back column level privileges. Suppose you just want to take back  insert privilege on ename column then you have to first take back the whole insert privilege and then grant privilege on empno column.

ROLES

A role is a group of Privileges. A role is very handy in managing privileges, Particularly in such situation when number of users should have the same set of privileges.

For example you have four users :Sami, Scott, Ashi, Tanya in the database. To these users you want to grant select ,update privilege on emp table, select,delete privilege on dept table. To do this first create a role by giving the following statement

create role clerks

Then grant privileges to this role.

grant select,update on emp to clerks;
grant select,delete on dept to clerks;

Now grant this clerks role to users like this

grant clerks to sami, scott, ashi, tanya ;

Now Sami, Scott, Ashi and Tanya have all the privileges granted on clerks role.
Suppose after one month you want grant delete on privilege on emp table all these users then just grant this privilege to clerks role and automatically all the users will have the privilege.

grant delete on emp to clerks;

If you want to take back update privilege on emp table from these users just take it back from clerks role.

revoke update on emp from clerks;

To Drop a role

Drop role clerks;