Download Intro to Database - Lab Manual 4 and more Lecture notes Introduction to Database Management Systems in PDF only on Docsity!
USMAN
INSTITUTE OF
TECHNOLOGY
Department of
Computer Science
CS311 Introduction to Database Systems
Lab#
Objective:
Data retrieval operations in SQL using join operations.
Name of Student: _____________________________________________
Roll No: ______________________________Sec. ___________
Date of Experiment: ___________________________________________
Marks Obtained/Remarks: _____________________________
Signature: _____________________________
THEORY
In lab session 2, we learned different ways to retrieve data from a single table. However, we frequently need data from more than one table. For example, suppose we need a report that displays employee id, name, job and department name. The first three attributes are present in
Compiled by: Miss shabina mushtaque
EMP table where as the last one is in DEPT table (see lab session 1). To produce the report, we need to link the EMP and DEPT tables and access data from both of them. This is called join operation. To gain better understanding of join, it would be helpful to first clarify the concept of Cartesian Product.
Figure 3.1: The join operation collects data from multiple sources
Cartesian Product
A Cartesian Product results when all rows in the first table are joined to all rows in the second table. A Cartesian product is formed under following conditions:- i. When a join condition is omitted ii. When a join condition is invalid Consider the following example:- SELECT
FROM EMP, DEPT; In the above example, if EMP table has 14 rows and DEPT table has 4 rows, then their Cartesian product would generate 14 x 4 = 56 rows. In fact, the ISO standard provides a special format of the SELECT statement for the Cartesian product:- SELECT * FROM EMP CROSS JOIN DEPT;
A Cartesian product tends to generate a large number of rows and its result is rarely useful. It is always necessary to include a valid join condition in a WHERE clause. Hence a join is always a subset of a Cartesian product.
Types of Joins
There are various forms of join operation, each with subtle differences, some more useful than others. The Oracle 9i database offers join syntax that is SQL 1999 compliant. Prior to release 9i, the join syntax was different from the ANSI standard. However, the new syntax does not offer any performance benefits over the Oracle proprietary join syntax that existed in prior releases. i. Inner-Join/Equi-Join If the join contains an equality condition, it is called equi-join. Examples i. To retrieve the employee name, their job and department name, we need to extract data from two tables, EMP and DEPT. This type of join is called equijoin -that is, values in the DEPTNO column on both tables must be equal. Equijoin is also called simple join or inner join. SELECT E.ENAME, E.JOB, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO; The SQL-1999 standard provides the following alternative ways to specify this join:- SELECT ENAME, JOB, DNAME Compiled by: Miss shabina mushtaque
Figure 3.2: Joining tables using right outer-join
Full Outer Join The SQL-1999 standard provides the following way to specify this join:- SELECT E.ENAME, D.DEPTNO, D.DNAME FROM EMP E FULL OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);
iii. Non-Equijoin If the join contains inequality condition, it is called non-equijoin. E.g. to retrieve employee name, salary and their grades using non-equijoins , we need to extract data from two tables, EMP and SALGRADE. SELECT E.ENAME, E.SAL, S.GRADE FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
iv. Self Join
To find the name of each employee’s manager, we need to join the EMP table to itself, or perform a self join. SELECT WORKER.ENAME || ‘ works for ‘ || MANAGER.ENAME FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO;
EXERCISES
i. To display the employee name, department name, and location of all employees who earn a commission.
ii. To display all the employee’s name (including KING who has no manager) and their manager name.
iii. To display the name of all employees whose manager is KING.
Compiled by: Miss shabina mushtaque
iv. Write a query to display the name, job, department number and department name for all employees who work in DALLAS.
v. Display the employee name and employee number along with their manager’s name Manager Number. Label the columns Employee, Emp#, Manager, and Manager#, respectively.
