Complex Queries

Syllabus

Additional features of SQL, Complex Queries: Handling Null values, Nested and Co-Related Nested queries. (6.5)

Nested Queries

• Some queries require that existing values in the database be fetched and then used in a comparison condition.

• Such queries can be conveniently formulated by using nested queries, which are complete select-from-where blocks within the WHERE clause of another query.

• That other query is called the outer query.

• A complete SELECT query, called a nested query, can be specified within the WHERE-clause of another query, called the outer query.

• Many of the previous queries can be specified in an alternative form using nesting.

Example 1: Retrieve the name and address of all employees who work for the 'Research' department.

SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
              FROM DEPARTMENT
              WHERE DNAME='Research' );

Example 2:
Make a list of all project numbers for projects that involve an employee whose last name is 'Smith' either as a worker or as a manager of the department that controls the project.

SELECT DISTINCT Pnumber FROM PROJECT
WHERE Pnumber IN (SELECT Pnumber 
                  FROM PROJECT, DEPARTMENT, EMPLOYEE 
                  WHERE Dnum=Dnumber 
					AND Mgr_ssn=Ssn 
					AND Lname='Smith')
				OR
		SELECT DISTINCT Pnumber FROM PROJECT
			WHERE Pnumber IN (SELECT Pno
                  FROM WORKS_ON, EMPLOYEE
                  WHERE Essn=Ssn AND Lname='Smith');

Example 3:
Retrieve the Ssns of all employees who work on the same (project,hours) combination on some project that employee (whose Ssn is equal to ‘33344555’) works on.

SELECT DISTINCT Essn FROM WORKS_ON 
	WHERE (Pno,Hours) IN (SELECT (Pno,Hours) 
                      FROM WORKS_ON 
                      WHERE Essn='333445555');

Other Operators

• =ANY (or =SOME) operator returns TRUE if the value v is equal to some value in the set V and is hence equivalent to IN

• ANY and SOME have the same meaning

• Other operators that can be combined with ANY (or SOME) include >, >=, <, <=, and <>

• The keyword ALL also combined with each of these operators Eg., (v>ALL V)

• Returns TRUE if the value v is greater than all the values in the set V.

Example:
Retrieve the names of employees whose salary is greater than the salary of all the employees in department 5

SELECT Fname, Lname 
FROM EMPLOYEE 
WHERE Salary > ALL (SELECT Salary 
                    FROM EMPLOYEE 
                    WHERE Dno=5);

Ambiguity of attribute names

Example:
Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.

SELECT E.Fname, E.Lname 
FROM EMPLOYEE E 
WHERE E.Ssn IN (SELECT Essn 
                FROM DEPENDENT 
                WHERE E.Fname = Dependent_name 
                AND E.Sex = Sex);

Correlated nested queries

• If a condition in the WHERE-clause of a nested query references an attribute of a relation declared in the outer query, the two queries are said to be correlated.

• The result of a correlated nested query is different for each tuple (or combination of tuples) of the relation(s) the outer query.

Example 1:
Retrieve the name of each employee who has a dependent with the same first name as the employee.

SELECT E.FNAME, E.LNAME 
FROM EMPLOYEE AS E 
WHERE E.SSN IN (SELECT ESSN 
                FROM DEPENDENT 
                WHERE ESSN=E.SSN 
                AND E.FNAME=DEPENDENT_NAME);

Example 2:
A query written with nested SELECT... FROM... WHERE... blocks and using the = or IN comparison operators can always be expressed as a single block query.

SELECT E.FNAME, E.LNAME 
FROM EMPLOYEE E, DEPENDENT D 
	WHERE E.SSN=D.ESSN 
		AND E.FNAME=D.DEPENDENT_NAME;

Example 3:
Retrieve the name of each employee who works on all the projects controlled by department number 5.

SELECT FNAME, LNAME 
FROM EMPLOYEE 
WHERE ((SELECT PNO 
        FROM WORKS_ON 
        WHERE SSN=ESSN) 
        CONTAINS (SELECT PNUMBER 
                  FROM PROJECT 
                  WHERE DNUM=5));

EXISTS Function

• EXISTS is used to check whether the result of a correlated nested query is empty (contains no tuples) or not

• The result of EXISTS is a boolean value TRUE or FALSE

Example 4:
Retrieve the name of each employee who has a dependent with the same first name as the employee.

SELECT FNAME, LNAME 
FROM EMPLOYEE 
WHERE EXISTS (SELECT * 
              FROM DEPENDENT 
              WHERE SSN=ESSN 
              AND FNAME=DEPENDENT_NAME);

Example 5:
Retrieve the names of employees who have no dependents.

SELECT FNAME, LNAME 
FROM EMPLOYEE 
WHERE NOT EXISTS (SELECT * 
                  FROM DEPENDENT 
                  WHERE SSN=ESSN);

Aggregate Functions

• SQL provides several built-in functions for computing certain types of aggregate values

• COUNT, SUM, MAX, MIN, and AVG

• COUNT(*) counts all rows in a specified table

Example:
For each department, retrieve the department number, number of employees, and average salary

SELECT DNO, COUNT (*), AVG (SALARY) 
	FROM EMPLOYEE 
	GROUP BY DNO;

Grouping

• GROUP BY clause specifies the grouping attributes, which should also appear in the SELECT clause

Example:
For each department, retrieve the department number, the number of employees in the department, and their average salary.

SELECT DNO, COUNT (*), AVG (SALARY) 
	FROM EMPLOYEE 
	GROUP BY DNO;

• The EMPLOYEE tuples are divided into groups – each group having the same value for the grouping attribute DNO.

• The COUNT and AVG functions are applied to each such group of tuples separately.

• The SELECT-clause includes only the grouping attribute and the functions to be applied on each group of tuples.

• A join condition can be used in conjunction with grouping

• If NULLs exist in the grouping attribute, then a separate group is created for all tuples with a NULL value in the grouping attribute.

Example:
For each project, retrieve the project number, project name, and the number of employees who work on that project.

SELECT PNUMBER, PNAME, COUNT (*) 
	FROM PROJECT, WORKS_ON 
	WHERE PNUMBER=PNO 
	GROUP BY PNUMBER, PNAME;

The HAVING Clause

• To retrieve the values of these functions for only those groups that satisfy certain conditions.

• The HAVING-clause is used for specifying a selection condition on groups (rather than on individual tuples).

Example:
For each project on which more than two employees work, retrieve the project number, project name, and the number of employees who work on that project.

SELECT PNUMBER, PNAME, COUNT(*) 
	FROM PROJECT, WORKS_ON 
	WHERE PNUMBER=PNO 
	GROUP BY PNUMBER, PNAME 
	HAVING COUNT (*) > 2;

WHERE vs. HAVING

• Selection conditions in the WHERE clause limit the tuples to which functions are applied

• The HAVING clause serves to choose whole groups

Example:
For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project.

SELECT Pnumber, Pname, COUNT (*) 
	FROM PROJECT, WORKS_ON, EMPLOYEE 
		WHERE Pnumber=Pno 
			AND Ssn=Essn 
			AND Dno=5 
	GROUP BY Pnumber, Pname;

Example:
For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than $40,000.

SELECT Dnumber, COUNT (*) 
	FROM DEPARTMENT, EMPLOYEE 
		WHERE Dnumber=Dno 
			AND Salary>40000 
			AND (SELECT Dno 
				FROM EMPLOYEE 
				GROUP BY Dno 
				HAVING COUNT (*) > 5);