SQLStudyGuide

Retrieve the birth date and address of the employee(s) whose name is ‘John B. Smith’.

SELECT BDATE, ADDRESS

FROM EMPLOYEE

WHERE FNAME='John' AND MINIT='B’ AND LNAME='Smith’

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

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.

SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS

FROM PROJECT, DEPARTMENT, EMPLOYEE

WHERE PLOCATION=’Stafford’ AND DNUM=DNUMBER AND MGRSSN=SSN

Retrieve the name of each employee who works on all the projects controlled by department

number 5.

SELECT Fname, Lname

FROM EMPLOYEE

WHERE NOT EXISTS ( ( SELECT Pnumber

FROM PROJECT

WHERE Dnum = 5) EXCEPT (SELECT Pno

FROM WORKS_ON

WHERE SSN = ESSN));

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

Pnumber IN

(SELECT Pno

FROM WORKS_ON, EMPLOYEE

WHERE ESSN = SSN AND Lname = ‘Smith’);

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

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);

 For each employee, retrieve the employee’s first and last name and the first and last name of his or

her immediate supervisor

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME

FROM EMPLOYEE E, EMPLOYEE S

WHERE E.SUPERSSN=S.SSN

Select all EMPLOYEE SSNs SELECT SSN

FROM EMPLOYEE;

Select all combinations of EMPLOYEE SSN and DEPARTMENT D name in the database.

SELECT SSN, DNAME

FROM EMPLOYEE, DEPARTMENT;

Retrieve the salary of every employee.

SELECT SALARY

FROM EMPLOYEE;

Retrieve all distinct salary values.

SELECT DISTINCT SALARY

FROM EMPLOYEE;

Retrieve all employees whose address is in Houston, Texas.

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE ADDRESS LIKE '%Houston, TX%'

Find all employees who were born during the 1950s.

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE BDATE LIKE '_______5_’

Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10

percent raise

SELECT FNAME, LNAME, 1.1*SALARY

FROM EMPLOYEE, WORKS_ON, PROJECT

WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX’;

Retrieve all employees in department 5 whose salary is between $30,000 and $40,000.

SELECT *

FROM EMPLOYEE

WHERE (Salary BETWEEN 30000 AND 40000) AND Dno = 5;

Retrieve a list of employees and the projects they are working on, ordered by department and,

within each department, ordered alphabetically by last name, then first name

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

FROM DEPARTMENT AS D, EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P

WHERE D.Dnumber = E.Dno AND E.Ssn = W.Essn AND W.Pno = P.Pnumber

ORDER BY D.Dname, E.Lname, E.Fname;

Retrieve the name of each employee who has a dependent with the same first name and is the same gender 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.Gender = D.Gender );

Retrieve the Social Security numbers of all employees who work on project numbers 1, 2, or 3.

SELECT DISTINCT ESSN

FROM WORKS_ON

WHERE Pno IN (1, 2, 3);

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

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE SUPERSSN IS NULL

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

average salary.

SELECT SUM (Salary) AS Total_Sal, MAX (Salary) AS Highest_Sal, MIN (Salary) AS Lowest_Sal,

AVG (Salary) AS Average_Sal

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 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

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 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;

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 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 Dno, COUNT (*)

FROM EMPLOYEE

WHERE Salary>40000 AND Dno IN

(SELECT Dno

FROM EMPLOYEE

GROUP BY Dno

HAVING COUNT (*) > 5)

GROUP BY Dno;