Infolinks

Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Friday, 10 August 2012

Oracle/PLSQL: Insert multiple rows with a single INSERT statement


Oracle/PLSQL: Insert multiple rows with a single INSERT statement


Question: How can I insert multiple rows of explicit data in one SQL command in Oracle?
Answer: You can insert multiple rows using the following syntax:
INSERT ALL
   INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3')
   INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3')
   INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3')
SELECT * FROM dual;

Example #1

If you wanted to insert 3 rows into the suppliers table, you could run the following SQL statement:
INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

Example #2

You can also insert multiple rows into multiple tables. For example, if you wanted to insert into both the suppliers and customers table, you could run the following SQL statement:
INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;
This example will insert 2 rows into the suppliers table and 1 row into the customers table.

Retrieve third lowest value from a table


Retrieve third lowest value from a table


Question: How can I retrieve the third lowest salary amount from a salary table?

Answer: To retrieve the third lowest salary from a salary table, you could run the following query: (please note that the subquery is sorted in ascending order)
SELECT salary_amount
FROM(select salary2.*, rownum rnum from
(select * from salary ORDER BY salary_amount) salary2
where rownum <= 3 )
WHERE rnum >= 3;

If you wanted to retrieve all fields from the salary table for the third lowest salary, you could run the following query: (please note that the subquery is sorted in ascending order)
SELECT *
FROM(select salary2.*, rownum rnum from
(select * from salary ORDER BY salary_amount) salary2
where rownum <= 3 )
WHERE rnum >= 3;

Retrieve second lowest value from a table


Retrieve second lowest value from a table


Question: How can I retrieve the second lowest salary amount from a salary table?

Answer: To retrieve the second lowest salary from a salary table, you could run the following query: (please note that the subquery is sorted in ascending order)
<
SELECT salary_amount
FROM(select salary2.*, rownum rnum from
(select * from salary ORDER BY salary_amount) salary2
where rownum <= 2 )
WHERE rnum >= 2;

If you wanted to retrieve all fields from the salary table for the second lowest salary, you could run the following query: (please note that the subquery is sorted in ascending order)
SELECT *
FROM(select salary2.*, rownum rnum from
(select * from salary ORDER BY salary_amount) salary2
where rownum <= 2 )
WHERE rnum >= 2;

Retrieve third highest value from a table


Retrieve third highest value from a table


Question: How can I retrieve the third highest salary amount from a salary table?

Answer: To retrieve the third highest salary from a salary table, you could run the following query: (please note that the subquery is sorted in descending order)
SELECT salary_amount
FROM(select salary2.*, rownum rnum from
(select * from salary ORDER BY salary_amount DESC) salary2
where rownum <= 3 )
WHERE rnum >= 3;

If you wanted to retrieve all fields from the salary table for the third highest salary, you could run the following query: (please note that the subquery is sorted in descending order)
SELECT *
FROM(select salary2.*, rownum rnum from
(select * from salary ORDER BY salary_amount DESC) salary2
where rownum <= 3 )
WHERE rnum >= 3;

Retrieve second highest value from a table


Retrieve second highest value from a table


Question: How can I retrieve the second highest salary amount from a salary table?

Answer: To retrieve the second highest salary from a salary table, you could run the following query: (please note that the subquery is sorted in descending order)
SELECT salary_amount
FROM(select salary2.*, rownum rnum from
(select * from salary ORDER BY salary_amount DESC) salary2
where rownum <= 2 )
WHERE rnum >= 2;

If you wanted to retrieve all fields from the salary table for the second highest salary, you could run the following query: (please note that the subquery is sorted in descending order)
SELECT *
FROM(select salary2.*, rownum rnum from
(select * from salary ORDER BY salary_amount DESC) salary2
where rownum <= 2 )
WHERE rnum >= 2;

Retrieve Middle N records from a query


Retrieve Middle N records from a query


Question: How can I retrieve the Middle N records from a query?
For example, what if I wanted to retrieve records 3 to 5 from my query results. How can I do this?

Answer: To retrieve the Middle N records from a query, you can use the following syntax:
SELECT *
FROM(select alias_name.*, rownum rnum from
(-- your ordered query --) alias_name
where rownum <= UPPER_BOUND )
WHERE rnum >= LOWER_BOUND;

For example, if you wanted to retrieve records 3 through 5 from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:
SELECT *
FROM(select suppliers2.*, rownum rnum from
(select * from suppliers ORDER BY supplier_name) suppliers2
where rownum <= 5 )
WHERE rnum >= 3;

If you wanted to retrieve records 3 through 5 from the suppliers table, sorted by supplier_name in descending order, you would run the following query:
SELECT *
FROM(select suppliers2.*, rownum rnum from
(select * from suppliers ORDER BY supplier_name DESC) suppliers2
where rownum <= 5 )
WHERE rnum >= 3;

If you wanted to retrieve records 2 through 4 from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:
SELECT *
FROM(select suppliers2.*, rownum rnum from
(select * from suppliers ORDER BY supplier_id) suppliers2
where rownum <= 4 )
WHERE rnum >= 2;

If you wanted to retrieve records 2 through 4 from the suppliers table, sorted by supplier_id in descending order, you would run the following query:
SELECT *
FROM(select suppliers2.*, rownum rnum from
(select * from suppliers ORDER BY supplier_id DESC) suppliers2
where rownum <= 4 )
WHERE rnum >= 2;

Retrieve Bottom N records from a query


Retrieve Bottom N records from a query


Question: How can I retrieve the Bottom N records from a query?
For example, what if I wanted to retrieve the last 3 records from my query results. How can I do this?

Answer: To retrieve the Bottom N records from a query, you can use the following syntax:
SELECT *
FROM (your query ordered in reverse) alias_name
WHERE rownum <= Rows_to_return
ORDER BY rownum DESC;
If you want to retrieve the bottom records from a query, you need to order your results in reverse in the "your query ordered in reverse" section of the solution above.

For example, if you wanted to retrieve the last 3 records from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:
SELECT *
FROM (select * from suppliers ORDER BY supplier_name DESC) suppliers2
WHERE rownum <= 3
ORDER BY rownum DESC;
Notice that although you want the last 3 records sorted by supplier_name in ascending order, you actually sort the supplier_name in descending order in this solution.

If you wanted to retrieve the last 3 records from the suppliers table, sorted by supplier_name in descending order, you would run the following query:
SELECT *
FROM (select * from suppliers ORDER BY supplier_name) suppliers2
WHERE rownum <= 3
ORDER BY rownum DESC;

If you wanted to retrieve the last 5 records from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:
SELECT *
FROM (select * from suppliers ORDER BY supplier_id DESC) suppliers2
WHERE rownum <= 5
ORDER BY rownum DESC;

If you wanted to retrieve the last 5 records from the suppliers table, sorted by supplier_id in descending order, you would run the following query:
SELECT *
FROM (select * from suppliers ORDER BY supplier_id) suppliers2
WHERE rownum <= 5
ORDER BY rownum DESC;

Retrieve Top N records from a query


Retrieve Top N records from a query


Question: How can I retrieve the Top N records from a query?
For example, what if I wanted to retrieve the first 3 records from my query results. How can I do this?

Answer: To retrieve the Top N records from a query, you can use the following syntax:
SELECT *
FROM (your ordered query) alias_name
WHERE rownum <= Rows_to_return
ORDER BY rownum;

For example, if you wanted to retrieve the first 3 records from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:
SELECT *
FROM (select * from suppliers ORDER BY supplier_name) suppliers2
WHERE rownum <= 3
ORDER BY rownum;

If you wanted to retrieve the first 3 records from the suppliers table, sorted by supplier_name in descending order, you would run the following query:
SELECT *
FROM (select * from suppliers ORDER BY supplier_name DESC) suppliers2
WHERE rownum <= 3
ORDER BY rownum;

If you wanted to retrieve the first 5 records from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:
SELECT *
FROM (select * from suppliers ORDER BY supplier_id) suppliers2
WHERE rownum <= 5
ORDER BY rownum;

If you wanted to retrieve the first 5 records from the suppliers table, sorted by supplier_id in descending order, you would run the following query:
SELECT *
FROM (select * from suppliers ORDER BY supplier_id DESC) suppliers2
WHERE rownum <= 5
ORDER BY rownum;

EXISTS Condition


SQL: EXISTS Condition


The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS ( subquery );
The EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.

Note:

These EXISTS queries are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use Exists.

Example #1:

Let's take a look at a simple example. The following is an SQL statement that uses the EXISTS condition:
SELECT *
FROM suppliers
WHERE EXISTS
  (select *
    from orders
    where suppliers.supplier_id = orders.supplier_id);
This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.

Example #2 - NOT EXISTS:

The EXISTS condition can also be combined with the NOT operator.
For example,
SELECT *
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);
This will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.

Example #3 - DELETE Statement:

The following is an example of a delete statement that utilizes the EXISTS condition:
DELETE FROM suppliers
WHERE EXISTS
  (select *
    from orders
    where suppliers.supplier_id = orders.supplier_id);

Example #4 - UPDATE Statement:

The following is an example of an update statement that utilizes the EXISTS condition:
UPDATE suppliers
SET supplier_name =( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS
  ( SELECT customers.name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id);

Example #5 - INSERT Statement:

The following is an example of an insert statement that utilizes the EXISTS condition:
INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM suppliers
WHERE exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

RANK() VS DENSE_RANK()

The RANK()function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.

The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.

Let us understand this difference with an example and then observe the results while using these two functions:

We will run two queries, one using RANK() and the other using DENSE_RANK() and observe the difference in the results. We will be using the ORDERS table of the NORTHWIND database to demonstrate the difference. The query will fetch the list of Customers ordered by the highest number of orders each has placed.

Using the RANK() function


SELECT RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt

FROM (SELECT CustomerID, COUNT(*) AS TotCnt

FROM Orders Group BY CustomerID) AS Cust


OUTPUT



As shown in the results above, while using the RANK() function, if two or more rows tie for a rank, each tied rows receives the same rank, however with gaps in the ranking where there are ties. For example, Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 6 instead of 5, because there are two rows that are ranked higher at 4.

Using the DENSE_RANK() function


SELECT DENSE_RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt

FROM (SELECT CustomerID, COUNT(*) AS TotCnt

FROM Orders Group BY CustomerID) AS Cust


OUTPUT



As shown in the results above, while using the DENSE_RANK() function, if two or more rows tie for a rank in the same partition, each tied rows receives the same rank, however leaving no gaps in the ranking where there are ties. Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 5. This is not the same as the RANK() function where the Customer with the next highest number of orders were ranked number 6.

Well I hope after seeing these example, you will understand the difference between the RANK() and DENSE_RANK() and will know where to use what.

Friday, 13 July 2012

CHECK CONSTRAINTS ON SYSDATE IN ORACLE

CHECK CONSTRAINTS ON SYSDATE IN ORACLE

RESTRICTIONS ON CHECK CONSTRAINTS

I.We cannot specify a check constraint for a view. However, you can define the view using the WITH CHECK    OPTION clause, which is equivalent to specifying a check constraint for the view.
II.The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other        tables.
III.Conditions of check constraints cannot contain the following constructs:


  1. Subqueries and scalar subquery expressions
  2. Calls to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP,  SESSIONTIMEZONE,   SYSDATE, SYSTIMESTAMP, UID,  USER, and USERENV)
  3. Calls to user-defined functions
  4. Dereferencing of REF columns (for example, using the DEREF function)
  5. Nested table columns or attributes
  6. The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
  7. Date constants that are not fully specified
So from the above detail its quite clear that we can not write any comparison with SYSDATE in check constrains..

Now imagine a situation that we are having one table and in that table we want some validation. The requirement is that we are taking Date Of Birth from the user and we want that only that user's details can reside inside our table who is older then 18 years else his insert should not take place.

So for this validation we have to write one trigger to take care of this situation.




/*This trigger will fire on every insert on table name TABLE_NAME and check that the date inserted in DOB_COLUMN_NAME column is greater than the difference of SYSDATE and inserted value by 18 and accordingly either it will allow the data to go inside the table or abort the transaction. :NEW and :OLD are co-relation identifiers used to check for the value inserted, updated or deleted. It will work only in triggers*/


CREATE OR REPLACE TRIGGER PROJECT_NAME_AGE_CHECK_TRIGGER
 AFTER INSERT AFTER UPDATE ON TABLE_NAME
 FOR EACH ROW
 DECLARE
 V_AGECHECK EXCEPTION;
 BEGIN
 IF MONTHS_BETWEEN(SYSDATE,:NEW.DOB_COLUMN_NAME)/12 < 18 THEN
 RAISE V_AGECHECK;
 END IF;
 EXCEPTION
 WHEN V_AGECHECK THEN
 RAISE_APPLICATION_ERROR(-20001,'YOU ARE MINOR') ;
 END;

SQL Statement to get the 2nd highest salaried/ nth highest paid Employee.


Best Solution:

select *
from emp
where sal =
(
select max(sal)
from emp
where level=2
connect by prior sal>sal
)
;


SQL Statement to get Nth highest salaried/paid employee(s)

Best.
select *
from emp
where sal =
(
select max(sal)
from emp
where level=&level
connect by prior sal>sal
)
;

Other Solutions
1.)
select *
from emp
where sal=
(
select sal from
(
select rownum Row1,sal
from (
select distinct(sal) sal
from emp
order by sal desc
)
)
where row1=&level
)


2.)

Select empno,ename,job,sal,comm,deptno
from (
select em.*,dense_rank() over
(
partition by grp order by sal desc
) as topsal
from (
select 1 as grp,e.* from emp e
) em
)
where topsal=&topsal

Wednesday, 11 July 2012

SUBQUERIES

 
SUBQUERIES
What is a subquery?
Multiple Subqueries
Nested subquery
Using subquery in DML commands
Correlated subquery
EXISTS, NOT EXISTS, ANY and ALL operators
What Is A Subquery
A subquery is a query within another query. The outer query is called as main query and
inner query is called as subquery.
In this chapter, we will see how subqueries are used to retrieve the required data and also
how to use subqueries in DML and DDL commands.
The general syntax of subquery will be as follows:
Query
(subquery)
The following is a simple example of a subquery.
select name, qual from faculty
where fcode in
( select fcode from course_faculty
where ccode = 'ora')
 
NAME QUAL
------------------------------ ------------------------------
George Koch MS Computer Science
Jason Couchman OCP DBA
Kevin Loney MS Electronics
In the above example we will take the details of faculty members who can handle course
COURSE_FACULTY table contains information about which faculty members can take course
ora. So we first use a subquery to get the list of the codes of the faculty members who can
handle Oracle course. Then we send the list of faculty codes to outer query, which will then
display the details of those faculty members.
Subquery is always executed first and the result is passed to the main query. Main query is
executed by taking the result from subquery.
IN operator plays a very important role in subqueries as generally subqueries generate a list of
values and main query is to compare a single value against the list of values supplied by
subquery.
In the above example, subquery supplies the list of faculty codes to main query. Then main
query compares each faculty code of the FACULTY table with the list supplied by subquery. If
the faculty code exists in the list then it will display the details of the faculty.
ora.
The following are a few other examples of subqueries.
Get the details of students who have paid today.
select * from students where rollno in
( select rollno from payments where trunc(dp) = trunc(sysdate));
Display the details of batches handled by faculty name ‘Kevin Loney’.
select * from batches
where fcode =
( select fcode from faculty where name = 'Kevin Loney');
The following query displays the details of the faculty members who have not taken
any batch in the last three months.
 
select * from faculty
where fcode NOT IN
(select fcode from batches
where months_between(sysdate,stdate) <= 3)
For example, the following is invalid.
SQL> select * from subjects
2 where fulldur
3 between 25
4 and (select max(fulldur) from subjects);
and (select max(fulldur) from subjects)
*
ERROR at line 4:
ORA-00936: missing expression
Multiple Subqueries
It is possible for a main query to receive values from more than one subquery. The following
example displays the details of batches that are taken by faculty with qualification MS or the
course fee is more than 5000.
select from batches
where fcode in
(select fcode from faculty where qual like '%MS%')
or ccode in
( select ccode from courses where fee > 5000);
BCODE CCODE FCODE STDATE ENDDATE TIMING
----- ----- ----- --------- --------- ---------
b1 ora gk 12-JAN-01 20-FEB-01 1
b3 c hs 20-JAN-01 27-FEB-01 3
b4 xml dh 02-MAR-01 30-MAR-01 3
b5 java hs 05-APR-01 10-MAY-01 1
b6 vbnet da 12-JUL-01 1
In the above query first the subquery –
‘%MS%’
MS. Then it executes second subquery and then returns course codes of the courses where
FEE is more than 5000. After both the subqueries are executed then the main query is
executed with the data that is passed by subqueries to main query.
select fcode from faculty where qual like– is executed. It then retrieves faculty codes where qualification of the faculty contains
 
Nesting Subquery
It is also possible to nest subqueries. So far we have seen examples where a single subquery
is executed and sends values to main query. It is also possible for a subquery to depend on
another subquery and that subquery on another and so on.
The following example displays the details of the students who belong to batches that are
taken by faculty with qualification MS.
select rollno,name, bcode from students
where bcode in
( select bcode from batches
where fcode in ( select fcode from faculty where qual like '%MS%')
);
ROLLNO NAME BCODE
--------- ------------------------------ -----
1 George Micheal b1
2 Micheal Douglas b1
6 Chirs Evert b3
7 Ivan Lendal b3
8 George Micheal b4
9 Richard Marx b5
10 Tina Turner b5
11 Jody Foster b5
Note:
is not recommended to use more than 3 levels of nes
The following is another example where we will take details of payments made by
students of the batch that started on 12-jul-2001.
Subqueries can be nested up to 16 levels. But that limit is seldom reached. Moreover itting considering the performance.
select * from payments
where rollno in
(select rollno from students
where bcode in
(select bcode from batches
where stdate = '12-jul-01'
)
);
 
Comparing more than one value
A subquery can return multiple columns. These multiple columns must be compared with
multiple values. The following query displays the details of the batches that have taken
maximum duration among the batches of the same course.
select * from batches
where (ccode, enddate-stdate) in
(select ccode, max(enddate-stdate)
from batches
group by ccode);
BCODE CCODE FCODE STDATE ENDDATE TIMING
----- ----- ----- --------- --------- ---------
b2 asp da 15-JAN-01 05-MAR-01 2
b3 c hs 20-JAN-01 27-FEB-01 3
b5 java hs 05-APR-01 10-MAY-01 1
b1 ora gk 12-JAN-01 20-FEB-01 1
b4 xml dh 02-MAR-01 30-MAR-01 3
First subquery returns the course and maximum duration for that course from BATCHES table
using MAX function and GROUP BY clause. Then the values are sent to main query where they
are compared with CCODE and duration of each batch. If in a row of BATCHES table the
CCODE and the duration are equivalent to CCODE and maximum duration of any of rows
returned by subquery then the row of BATCHES table is selected.
Now let us see few more examples of subqueries.
Get the details of course that has highest course fee.
select ccode,name,fee from courses
where fee =
(select max(fee) from courses);
CCODE NAME FEE
----- ------------------------------ ---------
cs C Sharp 7000
Get the details of students who have made a payment in the last month but no in the
current month.
select * from students
where rollno not in
(select rollno from payments
where to_char(dp,'mmyyyy') = to_char(sysdate,'mmyyyy')
 
)
and rollno in
( select rollno from payments
where to_char(dp,'mmyyyy')=to_char(add_months(sysdate,-1),'mmyyyy')
);
First we take roll numbers of students who have made payment in the current month and roll
numbers of students who have made payment in the previous month. Then the outer query
selects students who are not part of the first list (who made payment in current month) and
part of second list ( who made payment in previous month).
Subqueries in DML and DDL commands
Subqueries can also be used with DML commands. WHERE clause of UPDATE and DELETE can
always contain a subquery. The following UPDATE command increases the FEE of the course if
more than 5 batches have started for that course.
update courses set fee = fee * 1.1
where ccode in
( select ccode from batches
group by ccode
having count(*) > 5);
In the above example, subquery returns course codes for which more than 5 batches have
started and then UPDATE will update only those courses.
The following DELETE command uses subquery in WHERE clause to find out batches for which
there are no students.
delete from batches
where bcode not in ( select bcode from students);
The examples above are the cases where we used subquery in WHERE clause and not
precisely in DML command. The following UPDATE command updates FCODE of batch
the faculty code of the batch
b7 tob1.
update batches set fcode =
(select fcode from batches where bcode = 'b1')
where bcode = 'b7';
Subquery returns a single value that is to be copied to FCODE of UPDATE.
 
Similarly the following INSERT inserts rows into a table called COMP_BATCH. The data is
taken from BATCHES table.
The following subquery creates a new table from an existing table.
create table new_batches
as select bcode, ccode,fcode, stdate ,timing from batches
where stdate > sysdate;
The subquery is used to retrieve the data using which the new table is created. The structure
of the new table will be same as the structure of the query. In the above query as query
selects BCODE,CCODE,FCODE, STDATE , and TIMING columns the table is also created with
same columns.
The following is the structure of the new table.
SQL> desc new_batches
Name Null? Type
-------------------------------------- -------- ------------------
BCODE VARCHAR2(5)
CCODE VARCHAR2(5)
FCODE VARCHAR2(5)
STDATE NOT NULL DATE
TIMING NUMBER(1)
SQL> desc batches
Name Null? Type
--------------------------------------- -------- ------------------
BCODE NOT NULL VARCHAR2(5)
CCODE VARCHAR2(5)
FCODE VARCHAR2(5)
STDATE NOT NULL DATE
ENDDATE DATE
TIMING NUMBER(1)
If you observe the above two structures, you would notice that only NOT NULL constraint of
STDATE of BATCHES table is taken to NEW_BATCHES table.
select * from new_batches
Will display the details of batches where STDATE of the batch is after SYSDATE.
If you want to insert details again of new batches after some time you can issue the following
INSERT command.

insert into new_batches
select bcode, ccode, fcode, stdate , timing from batches
where stdate > sysdate;
Note:
Otherwise you may have to list out columns in the query according to the requirement.
The order and type of columns in the query and NEW_BATCHES should be same.
Renaming a column using subquery
The following procedure will illustrate how to use subquery with DDL to rename a column in a
table. Renaming a column is not permitted in Oracle. So to rename a column, follow the given
procedure. However, it is to be noted that this procedure is lengthy and not very refined. But
you can consider in case of desperate need.
Assume we created a table called PRODUCTS as follows.
create table products
( id number(5) primary key ,
name varchar2(30),
qty number(4) check ( qty >= 0 ),
pric number(5)
);
But the column PRIC is misspelt. It should have been PRICE. Now let us see how to rename
the column.
First create a new table called newproducts using a subquery. Give an alias to column PRIC so
that the alias becomes the column name in the new table.
create table newproducts
as select id, name, qty, pric price from products;
see the structure of the new table using DESC command.
desc newproducts
name Null? Type
------------------------------------------------ -------- -------------
ID NUMBER(5)
NAME VARCHAR2(30)
QTY NUMBER(4)
PRICE NUMBER(5)
As you can see in the output of DESC command, no constraint are defined in new table -
NEWPRODUCTS. So we need to define all constraints again on this table using ALTER TABLE
 
command. But before that let us drop original table and rename NEWPRODUCTS to
PRODUCTS.
SQL> drop table products;
Table dropped.
Note:
those rows also. For example, if you have SALES table referring to PRODUCTS table then first
SALES table is to be emptied before rows in PRODUCTS table can be deleted.
If the table being dropped has any dependent tables with rows then you have to drop
SQL> rename newproducts to products;
Table renamed.
Now we have to define all constraints that we had in PRODUCTS table. This step is required
as constraints of PRODUCTS table are copied to NEWPRODUCTS table.
alter table products
add ( constraint products_pk primary key(id));
alter table products
add ( constraint products_qty_chk check(qty >= 0));
Now see the structure of the new table using DESCRIBE command and constraints using
USER_CONSTRAINTS view.
What’s new in Oracle8i?
Orale8i has introduced to new possibilities related to subqueries.
Subquery in VALUES clause
Since Oracle8i it is possible to use a subquery in VALUES clause of INSERT command. Prior to
Oracle8i it was possible to use subquery with INSERT command but not in VALUES clause of
INSERT command.
The following INSERT command gets the next ROLLNO for a new student by using a subquery.
insert into students values (
'b7', 'Robert Lafore', 'm', sysdate, null, null);
(select max(rollno) + 1 from students),
ORDER BY is permitted in subquery – TOPn analysis
Oracle8i has allowed the ORDER BY clause to be used with subquery. The following query will
use ORDER BY clause to get courses in descending order. Then main query will take the data
sent by the subquery and selects only first two rows. As the result, the query will display the
details of course with first two highest course fee.
ROWNUM pseudo column contains the row number for the retrieved rows. The query uses
ROWNUM and takes only those rows that have row number less than 3.
select ccode,name,fee from
(select * from courses order by fee desc)
where rownum < 3;
CCODE NAME FEE
----- ------------------------------ ---------
cs C Sharp 7000
vbnet VB.NET 5500
Correlated Subquery
If there is any correlation between main query and subquery then subquery is called as
correlated subquery
A correlated subquery is a subquery that receives some input from main query and sends
result back to main query. Unlike normal subquery, a correlated subquery receives value from
main query. It uses the value (generally in condition) and sends the results of the query back
to main query.
Though most of the requirements can be accomplished with normal subqueries, some
requirements do need correlated subquery. For example, we have to display the details of the
batches where duration of the batch is more than the average duration of all the batches of
that course.
Before we write the required query, let us look at the data present in BATCHES table.
.
select * from batches;
BCODE CCODE FCODE STDATE ENDDATE TIMING
----- ----- ----- --------- --------- ---------
b1 ora gk 12-JAN-01 20-FEB-01 1
b2 asp da 15-JAN-01 05-MAR-01 2
b3 c hs 20-JAN-01 27-FEB-01 3
b4 xml dh 02-MAR-01 30-MAR-01 3
b5 java hs 05-APR-01 10-MAY-01 1
b6 vbnet da 12-JUL-01 31-AUG-01 1
b7 ora gk 15-AUG-01 04-OCT-01 2
b9 ora kl 05-MAY-01 10-JUN-01 3
b10 c kl 15-JUN-01 20-JUL-01 2
b11 vbnet da 15-JUN-01 30-JUL-01 2
Now let us write correlated query to get details of batches where duration (ENDDATE-STDATE)
is more than the average duration of the course.
select * from batches
b1
where enddate - stdate >=
(select avg(enddate - stdate)
from batches
where
BCODE CCODE FCODE STDATE ENDDATE TIMING
----- ----- ----- --------- --------- ---------
b2 asp da 15-JAN-01 05-MAR-01 2
b3 c hs 20-JAN-01 27-FEB-01 3
b4 xml dh 02-MAR-01 30-MAR-01 3
b5 java hs 05-APR-01 10-MAY-01 1
b6 vbnet da 12-JUL-01 31-AUG-01 1
b7 ora gk 15-AUG-01 04-OCT-01 2
ccode = b1.ccode);
In correlated subquery, subquery is executed once for each row of the main query. This is
required as subquery uses a value sent from main query, which may change from row to row
in main query.
A correlated subquery is identified by the use of a column of main query in the subquery. In
the above example, for each row of the BATCHES table the course code (CCODE) is passed to
subquery and then subquery finds out the average duration of all batches of that course. The
condition
batch in the main query belongs.
When subquery returns the average duration then main query checks whether duration of the
batch is greater than or equal to the average duration sent by subquery. If the condition is
satisfied then row in the main query is selected otherwise it is not selected. The process
continues with the next row in the main query and so on until all rows of the main query are
processed.
ccode = b1.ccode is used to select batches that belong to the course to which the
Differences between normal and correlated subquery
The following are the differences between a correlated subquery and ordinary subquery.
Subquery Correlated subquery
Executed only for once before mainquery
Executed once for each row of main-query.
Sends a value to main-query. Receives value(s) from main query and sends
value(s) to main-query.
Table 1
: Normal subquery Vs. Correlated subquery
 
The following is another example of correlated subquery where we get third highest course
fee.
select name, fee from courses c1
where 2= ( select count(*)
from courses
where fee > c1.fee);
NAME FEE
------------------------------ ---------
ASP.NET 5000
In the above example, subquery returns the number of courses where FEE is more than the
fee of the row in the main query. If the count returned by subquery is equal to 2 then it
means above that course fee there are two more. That means that course fee becomes the
third highest.
The above query is pretty useful. Especially prior to Oracle8i. As Oracle8i allowed ORDER BY
clause in subquery, what was achieved with this correlated subquery can to some extent be
achieved with ORDER BY clause in subquery.
The following two queries, will display the details of courses with two lowest fees.
The first query uses ORDER BY clause in subquery to retrieve courses in the ascending order
of FEE. Then main query retrieves only first two rows of the subquery.
select * from ( select ccode, name, fee from courses order by fee)
where rownum < 3;
CCODE NAME FEE
----- ------------------------------ ---------
c C programming 3500
xml XML Programming 4000
The second query uses a correlated subquery to get number of rows where FEE is less than
the FEE of the row in main query. If the count returned by subquery is greater than or equal
to 1 then row of the main query is retrieved.
select ccode,name, fee from courses c1
where 1 >= ( select count(*)
from courses
where fee < c1.fee);
CCODE NAME FEE
----- ------------------------------ ---------
c C programming 3500
xml XML Programming 4000
EXISTS and NOT EXISTS operators
These two operators are exclusively used in correlated subquery. EXISTS checks whether any
row is returned by subquery and condition will be true if subquery returns any rows. Whereas,
NOT EXISTS returns true if subquery doesn’t retrieve any row.
EXISTS is different from other operators like IN,ANY etc., because it doesn’t compare values of
columns, instead, it checks whether any row is retrieved from subquery or not. If any row is
retrieved from subquery the EXISTS returns true otherwise it returns false.
The following query displays details of courses for which at least one batch started in this
month.
select * from courses
where exists
( select * from batches
where courses.ccode= ccode
and to_char(stdate,'mmyy') = to_char(sysdate,'mmyy')
);
When using EXISTS operator, what you select in the inner-query does NOT matter. What does
matter is whether any row is retrieved by inner query or not.
The following example displays the details of courses for which no batch has yes started.
select ccode,name,fee from courses
where
not exists
( select * from batches
where ccode = courses.ccode);
CCODE NAME FEE
----- ------------------------------ ---------
cs C Sharp 7000

It is possible to replace the above NOT EXISTS with NOT IN operator as shown below.
select ccode,name,fee from courses
where ccode not in ( select ccode from batches);
CCODE NAME FEE
----- ------------------------------ ---------
cs C Sharp 7000
Let us see two other operators ALL and ANY.
ANY and ALL Operators
Both are used for comparing one value against a set of values. ALL specifies that all the values
given in the list should be taken into account, whereas ANY specifies that the condition is
satisfied when any of the values satisfies the condition.
operator ANY list
operator ALL list
The operator can be any one of the standard relational operators (=, >=, >, <, <=, !=) , and
list is a series of values.
What if you want to display name of the courses where FEE is more than FEE of any course
with DURATION 25? The following query will do just that.
select name from courses
where fee > any ( select fee from courses where duration = 25);
NAME
------------------------------
VB.NET
ASP.NET
C Sharp
ANY operator specifies if FEE is more than any value in the list supplied by
subquery then the condition is true. The same query can also be written as
follows using MIN function.
select name from courses
where fee > ( select min(fee) from courses where duration = 25);
 
NAME
------------------------------
VB.NET
ASP.NET
C Sharp
The following list illustrates the result of ANY and ALL operator.
Rate ANY/ALL Operator Result
10
10
10
10
Rate > ANY (15,20)
Rate > ANY (5,15)
Rate > ALL (10,20)
Rate > ALL (5,7)
False
True
False
True
Summary
Subquery is a query placed within another query. A subquery may return either one or
multiple rows. Understanding how to use subqueries is very important. So, make sure you are
comfortable with subqueries. Because subqueries, joining, and grouping the data are the areas
which you must master.
Subqueries can be used with DML and even DDL commands. Oracle8i has allowed ORDER BY
clause in subqueries. This allows top n analysis. Oracle8i has also allowed usage of subquery
in VALUES clause of INSERT command.
When a subquery takes data from main query it is called as correlated subquery. And
operators EXISTS and NOT EXISTS are used exclusively with correlated subquery.
Exercises
1. A correlated subquery is executed for ______ number of times.
2. Subquery nesting can be up to ______ levels.
3. What is the result of x > ANY (10,20), if x is 15? ________.
4. Subquery always passes the result to the main-query [T/F] _____
5. Subquery can be used in VALUES clause of INSERT command.[T/F] ____.
6. Display details of courses taken by students who joined in the month of june, 2001.
7. Delete the details of students who haven’t paid anything so far.
8. Display the details of course for which there are more than 3 batches.
9. Display the details of course that has highest number of batches.
10. Change the ENDDATE of batch B8 to the ENDDATE of most recent batch.
11. Display the details of students who haven’t paid total amount so far.
12. Display the details of payment made by students of Oracle batch started on 5-dec-2000.