USING DATA FROM MULTIPLE TABLES (JOINS)

 

 

Join:

It helps to attach multiple tables.The value from the table join is called self join. It is used to query data from more than one table.

 

 

Cartesian Product:

It is formed when a join condition is ommited.

The join condition is invalid.

 

 

Example:

 

 

SQL> SELECT ename, dname

FROM emp, dept;

 

 

Output:

 

ENAME      DNAME

———- ————–

SMITH      ACCOUNTING

ALLEN      ACCOUNTING

BLAKE      RESEARCH

CLARK      RESEARCH

SMITH      SALES

ALLEN      SALES

SMITH      OPERATIONS

ALLEN      OPERATIONS

 

 

Types of Joins:

 

1-Equi Joins:

 

EMP DEPT
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
7839 KING 10 10 ACCOUNTING NY
7698 BLAKE 30 30 SALES CHICAGO
Primary Key   Foreign Key Primary Key    

 

Primary key, a field name which uniquely describes a row. It can never be repeated.

Foreign Key, a  reference of the primary key in a table, different from the originating table. They are allowed to repeat

 

 

SQL> SELECT emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc

FROM emp, dept

WHERE emp.deptno = dept.deptno;

 

 

Output:

 

EMPNO ENAME         DEPTNO    DEPTNO   LOC

———      ———-         ———        ———    ————-

7369     SMITH                  20        20   DALLAS

7499    ALLEN             30        30   CHICAGO

 

 

 

SE1) Select the empno, ename, deptno & loc for KING.

 

Table Aliases:

They are used to simplify the query writing by defining variable names for the tables.

 

 

SQL> SELECT e.empno, e.ename, e.deptno, d.loc

FROM emp e, dept d

WHERE e.deptno=d.deptno;

 

 

Output:

 

EMPNO ENAME         DEPTNO LOC

———       ———-     ——— ————-

7369 SMITH                20         DALLAS

7499 ALLEN                30         CHICAGO

 

 

 

2-Non Equi-Joins:

 

EMP DEPT
EMPNO ENAME DEPTNO SAL DEPTNO DNAME LOC
7839 KING 10 5000 10 ACCOUNTING NY
7698 BLAKE 30 2850 30 SALES CHICAGO

 

SALGRADE

GRADE LOSAL HISAL

1

700

1200

2

1201

1400

 

 

 

SE2) Display the ename, sal & grade for all employees. Use table aliases.

 

 

Output:

 

ENAME            SAL     GRADE

———- ———  ———

SMITH            800          1

ADAMS           1100         1

 

…..

 

 

 

 

3-Outer Joins:

Example: If we have a department ‘OPERATIONS’ but no employees in it, no ename would be displayed but dept is important.A (+) sign is used with the table name which is deficient in the information.

 

SQL> SELECT e.ename, d.deptno, d.dname

FROM emp e, dept d

WHERE e.deptno(+)=d.deptno

ORDER BY e.deptno;

 

Output:

 

ENAME         DEPTNO DNAME

———-      ——— ————–

CLARK             10       ACCOUNTING

KING                   10       ACCOUNTING

 

 

4-Self Joins:

In it, both the fields of the join condition lie in the same table.

For Example: If you want to find the name of each employee’s manager, etc.

 

 

 

SE3) Produce the output:

            WORKER ENAME works for MANAGER ENAME

Output:

 

WORKER.ENAME||’WORKSFOR’||MANAG

——————————-

SMITH works for FORD

ALLEN works for BLAKE

…………

 

SE4) Write a query to display the ename, deptno, dept name for all employees.

 

Output:

 

ENAME         DEPTNO DNAME

———-      ——— ————–

SMITH              20           RESEARCH

ALLEN             30       SALES

……………

 

SE5) Display the job & loc of all jobs in dept 30.

 

Output:

 

JOB       LOC

———       ————-

SALESMAN  CHICAGO

SALESMAN  CHICAGO

…………..

 

SE6) Display the ename& dname for all employees who have an A in their names.

 

Output:

 

ENAME      DNAME

———- ————–

ALLEN      SALES

WARD       SALES

……………

 

SE7) Display the ename, empno, mgr & mgrno for all employees.

 

Output:

 

ENAME          EMPNO     EMPNO ENAME

———-      ———         ——— ———-

SMITH              7369         7902 FORD

ALLEN             7499          7698 BLAKE

…….

Group Functions:

 

They operate on sets of rows to given result.

AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE

 

SQL> SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal)

FROM EMP

WHERE job LIKE ‘SALES%’;

 

 

Output:

 

AVG(SAL)  MAX(SAL)  MIN(SAL)  SUM(SAL)

——— ———           ———       ———

1400        1600           1250         5600

 

 

SQL> SELECT MIN(hiredate), MAX(hiredate)

FROM EMP;

 

 

Output:

 

MIN(HIRED MAX(HIRED

———       ———

17-DEC-80 23-MAY-87

 

Note: Average(AVG), SUM, VARIANCE and STDDEV can only be used with

numerical data types.

 

 

 

COUNT:

It returns the number of rows in a table.

 

 

 

SQL> SELECT COUNT(*)

FROM EMP

WHERE deptno=30;

 

 

Output:

 

COUNT(*)

———

6

 

COUNT(expr): Counts a number of non-null rows.

 

 

SQL> SELECT COUNT(comm)

FROM EMP

WHERE deptno=30;

 

 

Output:

 

COUNT(COMM)

———–

4

 

 

SQL> SELECT COUNT(DISTINCT(deptno))

FROM EMP;

 

 

Output:

 

COUNT(DISTINCT(DEPTNO))

———————–

3

 

SE8)  Find the empno , ename , sal, department and grade for all employees who earn more than $ 800.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s