GrassField

Joins

The specification of table aliases in the from clause is necessary to refer to columns that have the same name in different tables. For example, the column DEPTNO occurs in both EMP and
DEPT. If we want to refer to either of these columns in the where or select clause, a table
alias has to be specified and put in the front of the column name. Instead of a table alias also
the complete relation name can be put in front of the column such as DEPT.DEPTNO, but this
sometimes can lead to rather lengthy query formulations.

select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
---------------------- ------------------------------ ---------- ---------------------- -------------------- ---------------------- ----------------------
100 pandian clerk 1000 1/3/2005 0:0:0 800 20
101 muruga writer 1002 12/5/2004 0:0:0 1200 20
102 bala security 1003 7/10/2006 0:0:0 800 10

select * from dept
DEPTNO DNAME LOC
---------------------- --------------- ---------------
20 services singapore
10 delivery india

select * from salgrade
GRADE LOSAL HISAL
---------------------- ---------------------- ----------------------
1 500 800
2 801 1000
3 1001 1500

Comparisons in the where clause are used to combine rows from the tables listed in the from
clause.

In the table EMP only the numbers of the departments are stored, not their
name. For each salesman, we now want to retrieve the name as well as the
number and the name of the department where he is working:

select emp.empno, emp.ename, dept.dname
from emp, dept
where emp.deptno=dept.deptno
EMPNO ENAME DNAME
---------------------- ------------------------------ ---------------
102 bala delivery
100 pandian services
101 muruga services

it can also be written as

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

E and D are table aliases for EMP and DEPT, respectively. The computation of the query result occurs in the following manner (without optimization):
1. Each row from the table EMP is combined with each row from the table DEPT (this operation
is called Cartesian product). If EMP contains m rows and DEPT contains n rows, we
thus get n x m rows.
2. From these rows those that have the same department number are selected (where
E.DEPTNO = D.DEPTNO).

In this example the joining condition for the two tables is based on the equality operator “=”.
The columns compared by this operator are called join columns and the join operation is called
an equijoin.

Subqueries
———–
A query result can also be used in a condition of a where clause. In such a case the query is called a
subquery and the complete select statement is called a nested query.

A respective condition in the where clause then can have one of the following forms:
1. Set-valued subqueries
<expression> [not] in (<subquery>)
<expression> <comparison operator> [any|all] (<subquery>)
An <expression> can either be a column or a computed value.
2. Test for (non)existence
[not] exists (<subquery>)

List all employees who are working in a department located in Singapore

select empno, ename from emp where deptno in (select deptno from dept where loc='singapore')
EMPNO ENAME
---------------------- ------------------------------
100 pandian
101 muruga

The subquery retrieves the set of those deptnos whose location is Singapore. If the employee working in the selected set (in operator), this tuple belongs to the query result set.

Conditions of the form <expression> <comparison operator> [any|all] <subquery> are used
to compare a given <expression> with each value selected by <subquery>.

Retrieve all employees who are working in department 20 and who earn at
least as much as any (i.e., at least one) employee working in department 10:

select empno, ename, sal from emp where sal>=all (select sal from emp where deptno=10) and DEPTNO = 20;
EMPNO ENAME SAL
---------------------- ------------------------------ ----------------------
100 pandian 800
101 muruga 1200
102 bala 800

Another SQL post, after a long time

The definition of a table may include the specification of integrity constraints. Basically two
types of constraints are provided: column constraints are associated with a single column
whereas table constraints are typically associated with more than one column. However, any
column constraint can also be formulated as a table constraint. In this section we consider only
very simple constraints.

The specification of a (simple) constraint has the following form:

[constraint <name>] primary key | unique | not null

A constraint can be named. It is advisable to name a constraint in order to get more meaningful
information when this constraint is violated due to, e.g., an insertion of a tuple that violates
the constraint. If no name is specified for the constraint, Oracle automatically generates a
name of the pattern SYS C<number>.

create table PROJECT (
PNO number(3) constraint prj pk primary key,
PNAME varchar2(60) unique,
PMGR number(4) not null,
PERSONS number(5),
BUDGET number(8,2) not null,
PSTART date,
PEND date);

The above query uses the following constraints.

1. primary key, Unique element of the table
2. unique, not element cannot be same
Two parameters can also be used in unique constraint.

constraint no same dates unique(PEND, PSTART)

can be used to ensure that no two projects have same pend and pstart dates.

3. not null ensures that the value inserted to the table is not null

Even default value can also be declared with constraints. e.g.

PSTART date default(’01-JAN-95’)

will be used to have 1st jan 95 as the default date for pstart.

There are some more complex constraints, hope lets have one more classroom notes soon.

The following code demonstrates how to bind your values to jndi. It requires weblogic server running in the specified environment.

/*
* HomeCache.java
*
* Created on August 26, 2007, 10:05 AM
*
* This is the basic home cache i will be using to get the weblogic environment
*/

package vanavani;

import javax.naming.*;
import java.util.*;

/**
*
* @author pandian
*/
public class HomeCache
{
InitialContext ic;

/** Creates a new instance of HomeCache */
public HomeCache()
{
}

public InitialContext getInitialContext() throws Exception
{
Properties prop = new Properties();
prop.put(javax.naming.Context.INITIAL_CONTEXT_FACTORY, “weblogic.jndi.WLInitialContextFactory”);
prop.put(Context.PROVIDER_URL, “t3://localhost:7001″); //t3 is a proprietory protocol by weblogic
ic = new InitialContext(prop);
return ic;

}

}