Chapter08 B B 2 INSQueries

---------------------------------------------------------------------------------------------------------------------------------------------------------
Relational Algebra and SQL Queries

---------------------------------------------------------------------------------------------------------------------------------------------------------
Query A: Select the EMPLOYEE tuples whose department number is 4
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query B: Select the employee tuples whose salary is greater than $30,000.
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query C: Select the tuples for all employees who either work in department 4 and make over $25,000 per year, or work
in department 5 and make over $30,000.
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query D: Retrieve the last name, first name, and salary of all employees.
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query E: Retrieve the gender and salary of each employee.
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query F: Retrieve a list of all employee names as well as the name of the departments they manage IF they happen to
manage a department .
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query G: Retrieve the first name, last name, and salary of all employees who work in department number 5
OR
SELECT E.Fname, E.Lname, E.Salary

FROM Employee E
WHERE E.Dno=5
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query H: Retrieve the social security numbers of all employees who either work in department 5 or directly supervise an
employee who works in department 5
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query I: Retrieve a list of names of each female employee and their dependents.
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query J: Retrieve the name of the manager of each department.
SELECT FNAME, LNAME, DNAME

FROM EMPLOYEE JOIN DEPARTMENT ON SSN = MGR_SSN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query K: Natural Join
Cartesian product followed by tuple selection
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query L: Aggregate functions.
Retrieve the maximum salary value from the EMPLOYEE relation

ℱMAX Salary (EMPLOYEE)
Retrieve the minimum Salary value from the EMPLOYEE relation
ℱMIN Salary (EMPLOYEE)
Retrieve the sum of the Salary from the EMPLOYEE relation
ℱSUM Salary (EMPLOYEE)
Compute the count (number) of employees and their average salary
ℱCOUNT SSN, AVERAGE Salary (EMPLOYEE)
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query M: 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;
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query N: Retrieve each department number, the number of employees in the department, and their average salary,
while renaming the resulting attributes as indicated below: Dno, No_of_employees, Average_sal.
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query O: A list of all employee names as well as the name of the departments they manage if they happen to manage a
department.
SELECT E.Fname, E.Lname, D.Dname

FROM employee E LEFT OUTER JOIN department D ON E.Ssn=D.Mgr_ssn
----------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------
BIGGER QUERIES
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query 1: Retrieve the name and address of all employees who work for the ‘Research’ department.
----------------------------------------------------------------------------------------------------------------------------------------------------------
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 birth date.
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query 4: Make a list of 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.
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query 5. List the names of all employees with more than 2 dependents.
SELECT E.SSN, COUNT (*)

FROM EMPLOYEE E, DEPENDENT D
WHERE E.Ssn = D.Essn
GROUP BY E.SSN
HAVING COUNT (*) > 2
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query 6: Retrieve the names of employees who have no dependents.
SELECT SSN
FROM employee
WHERE SSN NOT IN
(SELECT DISTINCT E.SSN
FROM employee E, dependent D
WHERE E.Ssn = D.Essn)
----------------------------------------------------------------------------------------------------------------------------------------------------------
Query 7: List the names of managers who have at least one dependent.
SELECT E.Lname, E.Fname

FROM department D, employee E
WHERE D.Mgr_ssn = E.Ssn and E.Ssn in
(SELECT DISTINCT DT.Essn
FROM dependent DT )
----------------------------------------------------------------------------------------------------------------------------------------------------------

Chapter08 B B 2 INSQueries

Uploaded by

Copyright:

Available Formats

Chapter08 B B 2 INSQueries

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Chapter08 B B 2 INSQueries

Uploaded by

Copyright:

Available Formats

---------------------------------------------------------------------------------------------------------------------------------------------------------

Relational Algebra and SQL Queries

Query E: Retrieve the gender and salary of each employee.

SELECT E.Fname, E.Lname, E.Salary

SELECT FNAME, LNAME, DNAME

Cartesian product followed by tuple selection

Retrieve the maximum salary value from the EMPLOYEE relation

SELECT Dno, COUNT (*), AVG (Salary)

SELECT E.Fname, E.Lname, D.Dname

SELECT E.SSN, COUNT (*)

SELECT E.Lname, E.Fname

You might also like