Page 1 :
Join and sub Queries, , Introduction to Join Operation :, Join means to access rows from two or more tables. A join operation is essential, to retrieve data from two or more tables. A tables must be joined with a WHERE, clause in which the common key field must be specified., Tables in a database are often related to each other with keys. A primary, key is a column (or a combination of columns) with a unique value for each row., Each primary key value must be unique within the table. The purpose is to bind, data together, across tables, without repeating all of the data in every table., , Types of Join :, Oracle provide different types of join operation., , 1) Inner Join or Equi Join, 2) Outer join, a) Left Outer Join, b) Right Outer Join, c) Full Join, 3) Self Join, 1) Inner Join or Equi Join :, Inner join is also known as equi join. This is the most common joins used, in SQL. They are known as equi join because the WHERE clause generally, compares two columns from the two different tables with the equivalence, operator (=)., For example consider the following tables. Person, Table, , Pld, 1, , LastName, Shinde, , FirstName, Kiran, , Address, Laxminager, , city, Phaltan, , 2, 3, , Salunkhe, More, , Rajesh, Sagar, , Dattanagar, Pune, Raviwar Peth Phaltan, , Note that the “Pld” column is the primary key in the “persons” table. This, means that no two rows can have the same Pld. The Pld distinguishes, two persons if they have the same name.

Page 2 :
Next, we have the “Orders” table :, Old, OrderNo, 1, 77895, 2, 44678, 3, 22456, 4, 24562, 5, 34764, , Pld, 3, 3, 1, 1, 15, , Note that the “Old” column is the primary key in the “Orders” tables and, that the “Pld” column refers to the persons in the “Persons” table without, using their names., The INNER JOIN keyword return rows when there is at least one match, in both tables., SQL INNER JOIN Syntax, SELECT column_name(s) FORM table_name1, INNER JOIN table_name2 ON, table_name1.column_name=table_name2.column_name, For Example:, SELECT persons.LastName, Persons.FirstName,, Orders.OrderNo FROM Persons, INNER JOIN Orders ON Persons.P_Id, ORDER BY Persons.LastName, The result-set will look like this:, LastName, Shinde, Shinde, More, More, , FirstName, Kiran, Kiran, Sagar, Sagar, , OrderNo, 22456, 24562, 77895, 44678, , 2) Outer Join :, Outer join is similar to inner joins, but give a bit more flexibility when, selecting data from related tables. this type of join can be used is, situations where it is desired, to select all rows from the table on the left,, right or both regardless of whether the other tables has values in common, enter NULL where data is missing.,  LEFT JOIN: Return all rows from the left table, even if there are, no matches in the right table.,  RIGHT JOIN: Return all rows from the right tables, even if there, are no matches in the left table.

Page 4 :
ON Persons.P_ld=Orders.P_ld ORDER BY, Persons.LastName, The result-set will look like this, LastName, FirstName, OrderNo, Shinde, Kiran, 22456, Shinde, Kiran, 24562, More, Sagar, 77895, More, Sagar, 44678, 34764, , The RIGHT JOIN keyword returns all the rows from the right table, (Orders), Even if there are no matches in the left table (Persons)., , c) Full Join:The FULL JOIN keyword return rows when there is a match in one of, the tables., SQL FULL JOIN Syntax, SELECT, column_name(s), FROM, table_name1 FULL JOIN table_name2, ON table_name1.column_name=table_name2.column_name, For Example, SELECT, Persons.LastName,, Persons., FirstName,, Orders.OrderNo FROM Persons FULL JOIN Orders, ON Persons.P_Id=Orders.P_Id ORDER BY Persons. LastName, , The result-set will look like this:, Last Name, Shinde, Shinde, More, More, Salunkhe, , First Name, Kiran, Kiran, Sagar, Sagar, Rajesh, , Order No., 22456, 24562, 77895, 44678, 34764, , The FULL JOIN keyword returns all the rows from the left table (Persons), and, all the rows from the right table (Orders) If there are rows in "Persons that do, not have matches in "Orders", or if there are rows in "Orders" that do not have, matches in "Persons", those rows will be listed as well.

Page 5 :
3)Self Join:, A Self Join is a type of sql join which is used to join a table to itself,, particulaty when the table has a FOREIGN KEY that references its own, PRIMARY KEY ILI necessary to ensure that the join statement defines an, alias for both copies of the table to avoid column ambiguity., The below query is an example of a self join,, SELECT a sales_person_id, a.name, a manager_id, b.sales_person_id,, b.name FROM sales person a, sales person b WHERE a.manager_id=, b.sales_person_id, Set Operations, , There are situations when we need to combine the results from two or more, SELECT statements. SQL enables us to handle these requirements by using, set operations. The result of each SELECT statement can be treated as a, set, and SQL set operations can be applied on those sets to arrive at a final, result., Oracle SQL supports the following four set operations:, , , , , , , UNION ALL, UNION, MINUS, INTERSECT, , 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

Page 6 :
INTERSECT, Returns only those rows that are returned by each of two SELECT statements, SQL Subquery, , Subquery or Inner query or Nested query is a query in a query A subquery is, usually added in the WHERE Clause of the sql statement. Most of the time,, a subquery is used when you know how to search for a value using a, SELECT statement, but do not know the exact value. Subqueries are an, alternate way of returning data from multiple tables., , Subqueries can be used with the following sql statements along with, the compansion operators like , <, >, >, <= etc, , Characteristics of Sub-query :, , 1. To insert records in a target table. 2. To create table and insert records, in the table created., , 3. To update records in the target table., 4. To create view, 5. The nested query must return a single column 6. The nested query, must return a single row when a standard operators such as =<> is used., 7. The BETWEEN operator cannot be used with subquery., Subqueries in DML commands :, 1. Subquery to Select data:, , In order to list the employees whose salary is less than average salary of, employees in emp table, a AVG function is used to calculate the average, salary. However, the AVG function cannot be used in the where clause. So,, sub query is used, Syntax:, Select from <tablename> where <fieldname> < (select avg(fieldname) from, <tablename>);, For Example

Page 7 :
Select from emp where sal (select avg(sal) from emp);, Example 2 To display the names of employee who works in sales, department Select ename from emp where deptno in (select deptno from, dept where dname='sales')., , 2. Subquery to create table and insert data:, Oracle provides the facility to create a table from the existing table and, also insert data into the desired table., Example Following query creates a empdup table from emp table, Create table empdup AS select from emp, Example 2: Following query creates only structure of table that means, rows will not be copied, Create table empdup as (select from emp where 1>2);, , Example 3 :Following query insert the records of clerk from emp to, empdup table, Insert into empdup (select from emp where job='clerk);, Example 4: Following query insert only empno, enmae and job from emp, to empdup table, Insert into empdup(erpno, ename, job ) (select empno, ename, job from emp)., , 3. Subquery to Delete data :, Oracle also provides the facility to implement the subquery and delete the, desired data from the desired table.., Example:, Delete from emp where empno in (select empno from emp where sal>2000), , 4. Subquery to Update data :, Oracle provides the subquery to update data into the desired table. We can, use UPDATE command in to the subquery and update the data into the, table. When using subquries with UPDATE, the certain subquery will no, return more than one record

Page 8 :
Example:, Update emp set sal=(select avg(sal) from emp where job=’clerk’), Where job=’clerk’;, Correlated sub queries:, A query is called correlated subquery when both the inner query and the outer, query are interdependent. For every row processed by the inner query, the, outer query is processed as well. The inner query depends on the outer query, before it can be processed, , Example:, List the names of employees who are managers, Select ename from emp a where empno in (select empno from emp, where empno=a.mgr), , Multi Column subquery :, , Multi column subquery process on more than one column., Example: Display the firstname and lastname of customers who are, employees., Select fname, laname from customer where (fname, laname) in (select, fname, Iname from emp);, , SQL Index, , Index in sql is created on existing tables to retrieve the rows quickly When, there are thousands of records in a table, retrieving information will take a, long time. Therefore indexes are created on columns which are accessed, frequently, so that the information can be retrieved quickly. Indexes can be, created on a single column or a group of columns. When a index is created,, it first sorts the data and then it assigns a ROWID for each row., , Types of Index, Oracle provides two different types of index, namely Simple Index

Page 9 :
and Composite Index., 1. Simple Index:, , When we create an Index on a single column of the desired fable then it, is called Simple Index, Syntax to create Index, CREATE INDEX index_name, ON table_name (<field name>), , Example Create index empindex on emp(empno). Create index studindex, on student(class);, , 2. Composite Index :, When we create an index on more than one columns of desired table then it, is called Composite Index, , Syntax to create Index:, CREATE INDEX index_name, ON table_name (<field name1>, <field name2>...<field name n>), Example Create index empindex on emp(empno, ename, job); Create, index studindex on student(rollno, class);, SQL unique Index:, A unique index can also be created on one or more fields. If an index is, created on a single field, it is called simple unique index and when it is, created on more than one field it is called composite unique index., , CREATE UNIQUE INDEX index_name, ON table_name (column_name1,column_name2...);

Page 10 :
 index_name is the name of the INDEX,  table_name is the name of the table to which the indexed column, belongs.,  column_name1, column_name2 is the list of columns which make, up the INDEX, Example, Create unique index empindex on emp(empno);, Create unique index studindex on student(rollno, class);, , Altering Existing Index:, Oracle provides the ALTER INDEX command to change the existing Index, , Syntax, ALTER INDEX <index name> ON <Table Name> (<field name>);, Example ALTER INDEX studindex ON Student(sex):, , Deleting Existing Index:, Oracle Provides the DROP INDEX command to delete the existing, index Syntax DROP, , INDEX, , <index, , name> ; Example DROP INDEX studindex;, , Sequences :, Oracle provides an object called a SEQUENCE that can be generate numeric, values upto maximum 38 digits., , Characteristics of Sequence :, , 1 Generate sequence in ascending or descending order depending, on programmer.

Page 11 :
2 Provide desired interval between numbers., 3. Caching of sequence numbers in memory to speed up their, availability. Syntax, Create sequence <sequence name> [ increment by <integer value> Start with, <integer value> maxvalue <integer value> /nomaxvalue Minvalue <integer, value> /nominvalue cycle / nocycle Cache <integer value> /nocache order, / noorder], , Keywords and Arguments, , Description, , Increment, , It specifies the interval between sequence, numbers. It can any positive or negative value, but not zero. Default, value is 1, , Minvalue, , Specify the, sequence., , Nominvalue, , Specify a minimum value is 1 for an, ascending order., Specify the maximum value of, sequence., , Maxvalue, , minimum, , value, , of, , Nomaxvalue, , Specify a maximum of 10^27 for an, ascending sequence. Or-1 for a, descending sequence., , Start with, , Specify the first number of sequence. Default, value is 1 for ascending and -1, for descending order, , Cycle, , Specify that the sequence continues to, generate repeat values after, reaching the maximum value., , Nocycle, , Specify that the sequence cannot generate, more values after reaching, the maximum value, , Cache, , Specify how many values are cached for faster, access. The minimum cache, value is 2, , Nocache, , Specifies that values of a sequence are, not pre allocated.

Page 12 :
Order, , Generate a ascending or descending order, sequence., , Noorder, , This does not generate order of, sequence., , Example:, Create sequence empno increment by 1 start with 1 minvalue 1 maxvalue, 9999 cycle;, Inserting sequence values in table:Insert into emp values (empno.nextval, 'ram', 'Manager, 7788, 5000);, , Altering Sequence :, Alter sequence empno increment by 5 cache 250;, , SQL Views, A VIEW is a virtual table, through which a selective portion of the data from one, or more tables can be seen. Views do not contain data of their own. They are, used to restrict access to the database or to hide data complexity., A view is stored as a SELECT statement in the database. DML operations, on a view like INSERT, UPDATE, DELETE affects the data in the original, table upon which the view is based, The Syntax to create a sql view, is CREATE VIEW view name, AS, SELECT column_list, FROM table_name [WHERE condition)., ,  view_name is the name of the VIEW,  The SELECT statement is used to define the columns and rows that, you want to display in the view, , For Example: to create a view on the emp table the sql query would be like, CREATE VIEW empview

Page 13 :
AS, SELECT empno, ename, FROM, , emp, , where, , sal, , 2000,, Display Structure of View :, Like normal table structure oracle also provides the facility to display the, structure of view by using desc command, Syntax –, DESC <View Name>:, Example, DESC empview,, , Delete a View :, Oracle provides DROP VIEW command to delete the desired view table. Like, normal table if you delete the view table, which mean delete all rows as well, as all structure of view table., Syntax, DROP, , VIEW, , <View Name>;, , Example –, DROP VIEW empview., , Advantages of View :, , a) data security - no need to give permission on the table, infact a view.can, be created, having only selected number of coloums in its definition. So user, will only be able to see those coloums., , b) simplicity - a very complicated query can be saved as a view, defenition, when needed can be called by its view name., , c) removes dependency - Can be very helpful to remove the dependecny from, the underlying tables. Suppose a view is created by joining several tables., After some time, there are some changes on the tables, so only defenition of, view

Page 14 :
can be changed and there is no need to change all the code where view, is used., , d) No space - takes no space (except materialized view), e) Customizing data. If you wish to display some computed values or, column names formatted differently than the base table columns, you can, do so by creating views.