Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Relational schema for SQL queries
Typology: Exercises
1 / 13
This page cannot be seen from the preview
Don't miss anything!
Query 0
Retrieve the birthdate 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’;
Query 1
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;
Query 1A
Ambiguous attribute names
SELECT FNAME, EMPLOYEE.NAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DEPARTMENT.NAME = ‘Research’ AND DEPARTMENT.DNUMBER = EMPLOYEE.DNUMBER;
Query 1B
Aliasing
SELECT E.FNAME, E.NAME, E.ADDRESS FROM EMPLOYEE E, DEPARTMENT D WHERE D.NAME = ‘Research’ AND D.DNUMBER = E.DNUMBER;
Query 1C
Retrieve all the attribute values of EMPLOYEE tuples who work in department number 5
SELECT * FROM EMPLOYEE WHERE DNO = 5;
Query 1D
Retrieve all the attributes of an EMPLOYEE and the attributes of the DEPARTMENT he or she works in for every employee of the ‘Research’ department
SELECT * FROM EMPLOYEE, DEPARTMENT WHERE DNAME = ‘Research’ AND DNO = DNUMBER;
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 birthdate
SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM = DNUMBER AND MGRSSN = SSN AND PLOCATION = ‘Stafford’;
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
(SELECT DISTINCT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM = DNUMBER AND MGRSSN = SSN AND LNAME = ‘Smith’)
UNION
(SELECT DISTINCT PNUMBER FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER = PNO AND ESSN = SSN AND LNAME = ‘Smith’);
Query 4A
Reformulation of query 4 using nested queries
SELECT DISTINCT PNUMBER FROM PROJECT WHERE PNUMBER IN (SELECT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM = DNUMBER AND MGRSSN = SSN AND LNAME = ‘Smith’) OR PNUMBER IN ( SELECT PNO FROM WORKS_ON, EMPLOYEE WHERE ESSN = SSN AND LNAME = ‘Smith’);
Query 5
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;
Query 6
Retrieve the names of employees who have no dependents
SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE SSN = ESSN);
Query 7
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 = MGRSSN);
Query 8
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 AS E, EMPLOYEE AS S WHERE E.SUPERSSN = S.SSN;
Query 8A
Reformulation of query 8 to retrieve the last name of each employee and his or her supervisor, while renaming the resulting attribute names as EMPLOYEE_NAME and SUPERVISOR_NAME
SELECT E.LNAME AS EMPLOYEE_NAME, S.LNAME AS SUPERVISOR_NAME FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.SUPERSSN = S.SSN;
Query 9
Select all EMPLOYEE SSNs in the database
SELECT SSN FROM EMPLOYEE;
Query 10
Select all combination of EMPLOYEE SSN and DEPARTMENT DNAME in the database
SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT;
Query 10A
Select the CROSS PRODUCT of the EMPLOYEE and DEPARTMENT relations
SELECT * FROM EMPLOYEE, DEPARTMENT;
Query 11
Retrieve the salary of every employee
SELECT ALL SALARY FROM EMPLOYEE;
Query 16A
Reformulation of query 16 as single block query
SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E, DEPENDENT AS D WHERE E.SSN = D.ESSN AND E.SEX = D.SEX AND E.FNAME = D.DEPENDENT_NAME;
Query 16B
Reformulation of query 16 using EXISTS
SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE EXISTS (SELECT * FROM DEPENDENT WHERE E.SSN = ESSN AND E.SEX = SEX AND E.FNAME = DEPENDENT_NAME);
Query 17
Retrieve the social security numbers of all employees who work on project number 1, 2 or 3
SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1,2,3);
Query 18
Retrieve the names of all employees who do not have supervisors
SELECT FNAME, LNAME FROM EMPLOYEE WHERE SUPERSSN IS NULL;
Query 19
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;
Query 20
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, DEPARTMENT WHERE DNO = DNUMBER AND DNAME = ‘Research’;
Query 21
Retrieve the total number of employees in the company
SELECT COUNT (*) FROM EMPLOYEE;
Query 22
Retrieve the number of employees in the ‘Research’ department
SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENT WHERE DNO = DNUMBER AND DNAME = ‘Research’;
Query 23
Count the number of distinct salary values in the database
SELECT COUNT (DISTINCT SALARY) FROM EMPLOYEE;
Query 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;
Query 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, WORKS_ON WHERE PNUMBER = PNO GROUP BY PNUMBER, PNAME;
Query 26
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;
Query 27
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;
Update 1
Add a new tuple to the EMPLOYEE relation
INSERT INTO EMPLOYEE VALUES (‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-30’, ’98 Oak Forest, Katy, TX’, ‘M’, 37000, ‘987654321’, 4);
Update 1A
Enter a tuple for a new employee for whom only FNAME, LNAME, DNO AND SSN attributes are known
INSERT INTO EMPLOYEE (FNAME, LNAME, DNO, SSN) VALUES (‘Richard’, ‘Marini’, 4, ‘653298653’);
Update 2
Rejected if referential integrity constraint enforced by DBMS and no department tuple with DNUMBER 2 exists
INSERT INTO EMPLOYEE (FNAME, LNAME, SSN, DNO) VALUES (‘Robert’, ‘Hatcher’, ‘980760540’, 2);
Update 2A
Rejected if NOT NULL checking enforced by DBMS (SSN not present)
INSERT INTO EMPLOYEE (FNAME, LNAME, DNO) VALUES (‘Robert’, ‘Hatcher’, 5);
Update 3A / B
Create a temporary table that has the name, number of employees and total salaries for each department
CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(15), NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER);
INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL) SELECT DNAME, COUNT (*), SUM (SALARY) FROM (DEPARTMENT JOIN EMPLOYEE ON DNUMBER = DNO) GROUP BY DNAME;
Update 4A
Deletion of tuples
DELETE FROM EMPLOYEE WHERE LNAME = ‘Brown’;
Update 4B
Deletion of tuples
DELETE FROM EMPLOYEE WHERE SSN = ‘123456789’;
Update 4C
Deletion of tuples
DELETE FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME = ‘Research’);
Update 4D
Deletion of tuples
DELETE FROM EMPLOYEE
Update 5
Change the location and controlling department number of project number 10
UPDATE PROJECT SET PLOCATION = ‘Bellaire’, DNUM = 5 WHERE PNUMBER = 10;
Update 6
Give all employees in the Research department a 10% raise in salary
UPDATE EMPLOYEE SET SALARY = SALARY * 1. WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME = ‘Research’);
Update view 2
Does not make sense because TOTAL_SAL is the sum of individual employees’ salaries
UPDATE DEPT_INFO SET TOTAL_SAL = 100000 WHERE DNAME = ‘Research’;
