Displaying Data from Multiple Tables

Displaying Data from Multiple Tables

Obtaining Data from Multiple Tables EMP DEPT EMPNO ENAME ... DEPTNO------ ----- ... ------ 7839 KING ... 10 7698 BLAKE ... 30 ... 7934 MILLER ... 10 DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON EMPNO DEPTNO LOC ----- ------- -------- 7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO ... 14 rows selected.

Displaying Data from Multiple Tables • What is a join? • When data from more than one table in the database is required. A join condition is used. Rows in one table can be joined to rows in another table according to common values existing in corresponding columns, usually primary and foreign key columns.

Guidelines: • When writing a SELECT statement that joins tables. Precede the column name with the table name for clarity and to enhance database access. • Write the join condition in the where clause. • If the same column name appears in more than one table, the column name must be prefixed with the table name. • To join n tables together, you need minimum of (n-1) join conditions. Therefore, to join four tables, a minimum of three joins are required. This rule may not apply if your table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.

Query 1 • Retrieve the name and address of all employees who work for the 'Research' department • SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO;

Query 2 • For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate. • SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM PROJECT, DEPARTMENT, EMPLOYEEWHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford'

Query 3 • The name of the females employees and the name of their male dependents . SELECT fname , lname ,d.dependent_name FROM employee e , dependent d WHERE ssn=d.ESSN and d.sex='M' and e.sex='F'

Query 4 • All managers first and last names who begun managing the department during 1981 and 1991 • select fname , lname • from employee , department • where mgrssn=ssn and (mgrstartdate between '01-jan-1981' and '31-dec-1991')

Query 5 Retrieve Employee name and the project name of all employees who worked on a project 10 to 30 hours. SELECT fname , lname ,pname FROM employee , works_on , project WHERE pnumber=pno and essn=ssn and (hours between 10 and 30 )

Query 6 • List the project names and the total hours the employees have spent on each. SELECT pname , sum(hours) FROM works_on , project WHERE pno=pnumber GROUP BY pname

Query 7 • For each department that has more that 2 employees , retrieve the department name and the average salary of all employees working in that department arranged in descend order by the average salary . SELECT dname , count(*), avg(salary) FROM employee , department WHERE dno=dnumber GROUP BY dname HAVING count(*)>=2 ORDER BY avg(salary) desc

Query 8 Retrieve the names of all employees who have more than two dependents SELECT fname , lname , count(*) "Number of dependents" FROM employee, dependent WHERE ssn=essn GROUP BY fname , lname HAVING count(*) >2;

Self joinsJoining table to itself • Consider the situation, where we want to display the name of Smith’s supervisor , you need to: • Find Smith in the employee table by looking at the LNAME column. • Find the supervisor number for Smith by looking at the SUPERSSN column. Smith’s supervisor SSN is 333445555. • Find the name of the supervisor with SSN 333445555 by looking at LNAME column. Franklin SSN is 333445555 , so Franklin is Smith’s manager.

Query 9 • For each employee, retrieve the employee's name, and the name of his or her immediate supervisor SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAMEFROM EMPLOYEE E, EMPLOYEE SWHERE E.SUPERSSN=S.SSN

Example of table aliases • Retrieve the name and address of all employees who work for the 'Research' department SELECT E.FNAME, E.LNAME, E.ADDRESS FROM EMPLOYEE E, DEPARTMENT D WHERE D.DNAME='Research' AND D.DNUMBER=E.DNO;

The DELETE Command Removes rows from a relation Includes a WHERE-clause to select the rows to be deleted rows are deleted from only one table at a time A missing WHERE-clause specifies that all rows in the relation are to be deleted; the table then becomes an empty table The number of rows deleted depends on the number of rows in the relation that satisfy the WHERE-clause Referential integrity should be enforced

The DELETE Command DELETE FROM EMPLOYEEWHERE LNAME='Brown’DELETE FROM EMPLOYEEWHERE SSN='123456789’DELETE FROM EMPLOYEE

The UPDATE Command Used to modify attribute values of one or more selected rows A WHERE-clause selects the rows to be modified An additional SET-clause specifies the attributes to be modified and their new values Each command modifies rows in the same relation Referential integrity should be enforced

The UPDATE Command Change the location and controlling department number of project number 10 to 'Bellaire' and 5, respectively. UPDATE PROJECTSET PLOCATION = 'Bellaire', DNUM = 5WHERE PNUMBER=10

Displaying Data from Multiple Tables