Module – IV, Complex Queries

Company Database

Retrieve the names of all employees who do not have supervisors.

SELECT Fname, Lname FROM EMPLOYEE WHERE Super_ssn IS NULL;

Ravikumar, Assistant Professor, Dept.of CSE, SDMIT, Ujire - 574240

 Module – IV, Complex Queries

Nested Queries
Retrieve project numbers of projects that have an employee with last name ‘Smith’
involved as manager or involved as a worker.

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

Pnumber IN ( SELECT Pno FROM WORKS_ON, EMPLOYEE WHERE

Essn = Ssn AND Lname = ‘Smith’ );

The first nested query selects the project numbers of projects that have an employee with last
name ‘Smith’ involved as manager, and whereas the second nested query selects the project
numbers of projects that have an employee with last name ‘Smith’ involved as worker.

The OR logical connective is used to retrieve a PROJECT tuple if the PNUMBER value of
that tuple is in the result of either nested query.

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

SELECT E.Fname, E.Lname FROM EMPLOYEE AS E WHERE E.Ssn IN

( SELECT D.Essn FROM DEPENDENT AS D WHERE E.Fname =

D.Dependent_name AND E.Sex = D.Sex );

The EXISTS and NOT EXISTS Functions in SQL

 EXISTS and NOT EXISTS are Boolean functions that return TRUE or FALSE;
hence, they can be used in a WHERE clause condition.
 The EXISTS function in SQL is used to check whether the result of a nested query is
empty (contains no tuples) or not.
 The result of EXISTS is a Boolean value TRUE if the nested query result contains at
least one tuple, or FALSE if the nested query result contains no tuples.
 NOT EXISTS returns TRUE if there are no tuples in the result of nested query and
returns FALSE if nested query returns at least one tuple.

Ravikumar, Assistant Professor, Dept.of CSE, SDMIT, Ujire - 574240

 Module – IV, Complex Queries

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

SELECT E.Fname, E.Lname FROM EMPLOYEE AS E

WHERE EXISTS ( SELECT * FROM DEPENDENT AS D WHERE E.Ssn

= D.Essn AND E.Sex = D.Sex AND E.Fname = D.Dependent_name);

Retrieve the names of employees who have no dependents.

SELECT Fname, Lname FROM EMPLOYEE

WHERE NOT EXISTS ( SELECT * FROM DEPENDENT WHERE Ssn = Essn );

List the names of managers who have at least one dependent.

SELECT Fname, Lname FROM EMPLOYEE

WHERE EXISTS ( SELECT * FROM DEPENDENT WHERE Ssn = Essn )

AND

EXISTS ( SELECT * FROM DEPARTMENT WHERE Ssn = Mgr_ssn );

Aggregate Functions in SQL

Aggregate functions are used to summarize information from multiple tuples into a single-
tuple summary.

Grouping is used to create subgroups of tuples before summarization. Grouping and

aggregation are required in many database applications.

A number of built-in aggregate functions available in SQL

1. COUNT
2. SUM
3. MAX
4. MIN
5. AVG

 The COUNT function returns the number of tuples or values as specified in a query.
 The functions SUM, MAX, MIN, and AVG can be applied to a set or multiset of
numeric values and return, respectively, the sum, maximum value, minimum value,
and average (mean) of those values.
 These functions can be used in the SELECT clause or in a HAVING clause.

Ravikumar, Assistant Professor, Dept.of CSE, SDMIT, Ujire - 574240

 Module – IV, Complex Queries

 The functions MAX and MIN can also be used with attributes that have nonnumeric
domains if the domain values have a total ordering among one another.

Find the sum of the salaries of all employees, the maximum salary, the minimum salary,
and the average salary.

SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary) FROM EMPLOYEE;

Find the sum of the salaries of all employees of the ‘Research’ department, as well as
the maximum salary, the minimum salary, and the average salary in this department.

SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary) FROM (EMPLOYEE
JOIN DEPARTMENT ON Dno = Dnumber) WHERE Dname = ‘Research’;

Retrieve the total number of employees in the company.

SELECT COUNT (*) FROM EMPLOYEE;

Retrieve the total number of employees in the ‘Research’ department.

SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENT WHERE DNO = DNUMBER

AND DNAME = ‘Research’;

Count the number of distinct salary values in the database.

SELECT COUNT (DISTINCT Salary) FROM EMPLOYEE;

Retrieve the names of all employees who have two or more dependents.

SELECT Lname, Fname FROM EMPLOYEE WHERE ( SELECT COUNT (*) FROM
DEPENDENT WHERE Ssn = Essn ) > = 2;

The GROUP BY and HAVING Clauses

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;

For each project, retrieve the project number, the 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;

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

Ravikumar, Assistant Professor, Dept.of CSE, SDMIT, Ujire - 574240

 Module – IV, Complex Queries

SELECT Dno, COUNT (*) FROM EMPLOYEE WHERE Salary>50000 AND Dno IN (
SELECT Dno FROM EMPLOYEE GROUP BY Dno HAVING COUNT (*) > 5) GROUP
BY Dno;

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;

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

SELECT Pnumber, Pname, COUNT (*) FROM PROJECT, WORKS_ON WHERE Pnumber
= Pno GROUP BY Pnumber, Pname HAVING COUNT (*) > 2;

Source

Text book

1. Fundamentals of Database Systems, Ramez Elmasri and Shamkant B. Navathe, 7th

Edition, 2017, Pearson.

Ravikumar, Assistant Professor, Dept.of CSE, SDMIT, Ujire - 574240