What is the difference between sub-query & co-related sub query?

A sub query is executed once for the parent statement

whereas the correlated sub query is executed once for each

row of the parent query.

Sub Query:

Example:

 Select deptno, ename, sal from emp a  where sal  in (select sal from Grade  where sal_grade=’A’ or  sal_grade=’B’)

Co-Related Sun query:

Example:

Find all employees who earn more than the average salary in their department.

SELECT last-named, salary, department_id  FROM employees A

WHERE salary > (SELECT AVG (salary)

FROM employees B WHERE B.department_id =A.department_id

Group by B.department_id)

Sub-query	Co-related sub-query
A sub-query is executed once for the parent Query	Where as co-related sub-query is executed once for each row of the parent query.
Example: Select * from emp where deptno in (select deptno from dept);	Example: Select a.* from emp e where sal >= (select avg(sal) from emp a where a.deptno=e.deptno group by  a.deptno);

View & Materialized View & Inline View

View:

Why Use Views?

• To restrict data access

• To make complex queries easy

• To provide data independence

  A simple view is one that:

– Derives data from only one table

– Contains no functions or groups of data

– Can perform DML operations through the view.

 A complex view is one that:

– Derives data from many tables

– Contains functions or groups of data

– Does not always allow DML operations through the view

A view has a logical existence but a materialized view has 

a physical existence.Moreover a materialized view can be 

Indexed, analysed and so on....that is all the things that 

we can do with a table can also be done with a materialized 

view.

We can keep aggregated data into materialized view. we can schedule the MV to refresh but table can’t.MV can be created based on multiple tables.

Materialized View:

In DWH materialized views are very essential because in reporting side if we do aggregate calculations as per the business requirement report performance would be de graded. So to improve report performance rather than doing report calculations and joins at reporting side if we put same logic in the MV then we can directly select the data from MV without any joins and aggregations. We can also schedule MV (Materialize View).

Inline view:

If we write a select statement in from clause that is nothing but inline view.

Ex:

Get dept wise max sal along with empname and emp no.

Select a.empname, a.empno, b.sal, b.deptno 

From EMP a, (Select max (sal) sal, deptno from EMP group by deptno) b

Where

a.sal=b.sal and

a.deptno=b.deptno

Differences between where clause and having clause

Where clause	Having clause
Both where and having clause can be used to filter the data.
Where as in where clause it is not mandatory.	But having clause we need to use it with the group by.
Where clause applies to the individual rows.	Where as having clause is used to test some condition on the group rather than on individual rows.
Where clause is used to restrict rows.	But having clause is used to restrict groups.
Restrict normal query by where	Restrict group by function by having
In where clause every record is filtered based on where.	In having clause it is with aggregate records (group by functions).

Order of where and having:

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

The WHERE clause cannot be used to restrict groups. you use the

HAVING clause to restrict groups.

Difference between Rowid and Rownum?

ROWID

A globally unique identifier for a row in a database. It is created at the time the row is inserted into a table, and destroyed when it is removed from a table.'BBBBBBBB.RRRR.FFFF' where BBBBBBBB is the block number, RRRR is the slot(row) number, and FFFF is a file number.

ROWNUM

For each row returned by a query, the ROWNUM pseudo column returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;

Rowid	Row-num
Rowid is an oracle internal id that is allocated every time a new record is inserted in a table. This ID is unique and cannot be changed by the user.	Row-num is a row number returned by a select statement.
Rowid is permanent.	Row-num is temporary.
Rowid is a globally unique identifier for a row in a database. It is created at the time the row is inserted into the table, and destroyed when it is removed from a table.	The row-num pseudocoloumn returns a number indicating the order in which oracle selects the row from a table or set of joined rows.

What is the Difference between Delete, Truncate and Drop?

DELETE

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

DROP

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. The operation cannot be rolled back.

What is the difference between view and materialized view?

View	Materialized view
A view has a logical existence. It does not contain data.	A materialized view has a physical existence.
Its not a database object.	It is a database object.
We cannot perform DML operation on view.	We can perform DML operation on materialized view.
When we do select * from view it will fetch the data from base table.	When we do select * from materialized view it will fetch the data from materialized view.
In view we cannot schedule to refresh.	In materialized view we can schedule to refresh.
	We can keep aggregated data into materialized view. Materialized view can be created based on multiple tables.

IMPORTANT QUERIES - PART 1

1)   To find the nth row of a table

SQL> Select *from emp where rowid = (select max(rowid) from emp where rownum <= 4);

Or

   SQL> Select *from emp where rownum <= 4 minus select *from emp where rownum <= 3;

2)   To find duplicate rows

SQL> Select *from emp where rowid in (select max(rowid) from emp group by empno,          

         ename, mgr, job, hiredate, comm, deptno, sal);

Or

 SQL> Select empno,ename,sal,job,hiredate,comm , count(*) from emp group by

         empno,ename,sal,job,hiredate,comm  having count(*) >=1;

3)   To delete duplicate rows

      SQL> Delete emp where rowid in (select max(rowid) from emp group by

              empno,ename,mgr,job,hiredate,sal,comm,deptno);

4)   To find the count of duplicate rows

      SQL> Select ename, count(*) from emp group by ename having count(*) >= 1;

5)   How to display alternative rows in a table?

          SQL> select *from emp where (rowid,0) in (select rowid,mod(rownum,2) from emp);

6)   Getting employee details of each department who is drawing maximum sal?

       SQL> select *from emp where (deptno,sal) in

               ( select deptno,max(sal)  from emp group by deptno);

7)   How to get number of employees in each department  , in which department is having more than 2500 employees?

       SQL> Select deptno,count(*) from emp group by  deptno having count(*) >2500;

           9) To reset the time to the beginning of the day

                  SQL> Select to_char(trunc(sysdate),’dd-mon-yyyy hh:mi:ss am’) from dual;

10) To find nth maximum sal

  SQL> Select *from emp where sal in (select max(sal) from (select *from emp order by sal)

          where rownum <= 5);