Basic and Complex SQL Queries

(Chap 6 & 7- Company DB)

1. Display all the details of all employees working in the company.
SELECT * from employee;
2. Display ssn, lname, fname, address of employees who work in department no 7.

SELECT ssn, lname, fname, address

FROM employee
WHERE dno=7;

3. Retrieve the birthdate and address of the employee whose name is 'Franklin T.Wong'

SELECT bdate, address

FROM employee
WHERE fname="Franklin" and mname="T" and lname="Wong";

4. Retrieve the name and salary of every employee

SELECT fname, mname, lname, salary

FROM employee;

5. Retrieve all distinct salary values

SELECT distinct salary

FROM employee;

6. Retrieve all employee names whose address is in ‘Bellaire’

SELECT fname, mname, lname

FROM employee
WHERE address=’Bellaire’;

7. Retrieve all employees who were born during the 1950s

SELECT fname
FROM employee
WHERE bdate between #01-01-50# and #31-12- 59#;
8. Retrieve all employees in department 5 whose salary is between 50,000 and 60,000(inclusive)

SELECT *
FROM employee
WHERE dno=5 and salary >=50000 and salary<=60000;

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

SELECT fname, mname, lname

FROM employee
WHERE superssn is null;

10. Retrieve SSN and department name for all employees

SELECT e.ssn, d.dname

FROM employee e, department d;

11. Retrieve the name and address of all employees who work for the 'Research' department

SELECT e.fname, e.address

FROM employee e, department d
WHERE d.dname="Research" and d.dnumber = e.dno;
or
SELECT fname, address
from employee
where dno in (SELECT dnumber
FROM department
WHERE dname =’research’);

12. 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 p.pnumber, p.dnum, e.lname, e.address, e.bdate

FROM project p, department d, employee e
WHERE p.plocation="Stafford" and p.dnum= d.dnumber and d.mgrssn=e.ssn;
13. 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.lname

FROM employee as e, employee as s
WHERE s.superssn=e.ssn;

14. Retrieve all combinations of Employee Name and Department Name

SELECT e.fname, e.lname, d.dname

FROM employee e, department d;

15. Make a list of all project numbers for projects that involve an employee whose last name is
'Narayan’ either as a worker or as a manager of the department that controls the project

(SELECT distinct pnumber

FROM project, department, employee

WHERE dnum=dnumber and mgrssn=ssn and lname="Narayan")

UNION
(SELECT distinct pnumber

FROM project, works_on, employee

WHERE pnumber=pno and essn=ssn and lname="Narayan");

16. Increase the salary of all employees working on the 'ProductX' project by 10%

SELECT fname, lname, 1.1*salary as increased_sal

FROM employee, works_on, project
WHERE ssn=essn and pno=pnumber and pname="productX";

17. Retrieve a list of employees and the project name each works in, ordered by the employee's
 department, and within each department ordered alphabetically by employee first name

SELECT dname, lname, fname, pname

FROM department, employee, works_on, project
WHERE dnumber=dno and ssn=essn and pno=pnumber
ORDER BY dname, lname, fname;

18. Select the names of employees whose salary does not match with salary of any employee in
department

SELECT fname
FROM employee
WHERE salary > all (SELECT salary
FROM employee
WHERE dno=5);

19. 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 as e
WHERE e.ssn IN (SELECT essn
FROM dependent
WHERE e.fname=dependent_name and e.sex=sex);

20. Retrieve the employee numbers of all employees who work on project located in Bellaire,
Houston, or Stafford.

SELECT ssn
FROM employee
WHERE ((SELECT pno
FROM works_on
WHERE ssn=essn) contains (SELECT pnumber
FROM project
WHERE dnum=5));

21. Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the
average salary. Display with proper headings
 SELECT sum(salary), max(salary), min(salary), avg(salary)
FROM employee;

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

SELECT sum(salary), count(*)

FROM employee, department
WHERE dname like "market%";
23. Select the names of employees whose salary is greater than the average salary of all employees
in department

SELECT fname
FROM employee
WHERE dno=10
GROUP BY salary having salary>avg(salary);

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

25. 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
GROUP BY pnumber;

26. Change the location and controlling department number for all projects having more than 5
employees to ‘Bellaire’ and 6 respectively

UPDATE project
SET plocation="Bellaire", dnum=6
 WHERE (SELECT count(essn)
FROM works_on
WHERE pno=pnumber)>5;

27. For each department having more than 10 employees, retrieve the department no, no of
employees drawing more than 40,000 as salary

SELECT dno
FROM employee
WHERE salary>40000
GROUP BY dno
HAVING count(*)>10;

28. Insert a record in Project table which violates referential integrity constraint with respect to
Department number. Now remove the violation by making the necessary insertion in the
Department table.

INSERT into project

values ("Research and development",25,"Bhopal",9);

/* The above query will give an error since there exists no department with department
number 9 exists in the department table */

/* To remove this error, we create a record in table department with dnumber as 9 */

INSERT into department

values ("Research",9,"123","20-08-2012");

29. Delete all dependents of employee whose ssn is ‘123456789’

DELETE from dependent

WHERE essn=123456789;

30. Delete an employee from Employee table with ssn = ‘12345’ (make sure that this employee has
some dependents, is working on some project, is a manager of some department and is
supervising some employees). Check and display the cascading effect on Dependent and Works
on table. In Department table MGRSSN should be set to default value and in Employee table
SUPERSSN should be set to NULL
 DELETE from employee
WHERE ssn=1234567891 cascade****;

31. Perform a query using alter command to drop/add field and a constraint in Employee table.

ALTER table
DROP foreign key(superssn);