SQL Code and Relational Algebra Expressions
1. List all the female employees.
SQL: Relational Algebra Expression using
SELECT ()Operator
SELECT *
FROM employee EMP_FEM
sex=”F” (EMPLOYEE)
WHERE sex= 'F';
2. List all the employees belonging to department no. 5.
SQL: Relational Algebra Expression using SELECT
( )Operator:
SELECT *
FROM employee
EMP_DNO5 (EMPLOYEE)
WHERE Dno=5; DNO = 5
3. Display the employee tuples whose salary is greater than $30,000.
SQL: Relational Algebra Expression using SELECT
()Operator:
SELECT *
EMP_SAL30K
FROM employee
(EMPLOYEE)
WHERE salary >30000; SALARY >30000
4. List all the employees belonging to department no. 1,4 and 5.
SQL: Relational Algebra Expression using SELECT
SELECT * ( )Operator:
FROM employee
EMP_DNO (EMPLOYEE)
(DNO = 5 OR DNO = 7)
WHERE Dno=5 OR Dno=7;
5. To list each employee’s first and last name and salary.
SQL: Relational Algebra Expression using PROJECT
SELECT lname, fname, salary ( (pi)) Operator:
FROM employee;
EMP1 (EMPLOYEE)
LNAME, FNAME, SALARY
6. To display the employees who work either in Department 4 and have salary greater than 25000 or in department 5 and have salary greater
than 30000
SQL: Relational Algebra Expression using SELECT
SELECT * ( )Operator:
FROM employee
WHERE (DNO = 4 AND SALARY >25000) OR EMP (DNO = 4 AND SALARY >25000) OR (DNO=5 AND SALARY >30000) (EMPLOYEE)
(DNO=5 AND SALARY >30000;
7. Retrieve the department number, department name, manager’s ssn, name of the manager of each department.
SQL: Relational Algebra Expression using PROJECT
SELECT dnumber, dname, mgrssn, fname, ((pi)) and JOIN Operators:
lname
FROM department, employee WHERE DEPT_MGR1 DEPARTMENT MGRSSN=SSN
EMPLOYEE
[Link] = [Link];
DEPT_MGR2 (DEPT_MGR1)
DNUMBER, DNAME, MGRSSN, FNAME, LNAME
8. Retrieve the ssn, fname, lname, project number and number of hour each employed has worked on.
SQL: Relational Algebra Expression using PROJECT
SELECT ssn, lname, fname, pnumber, and NATURAL JOIN Operators:
hours
FROM EMPLOYEE NATURAL JOIN WORKS_ON; EMP_PROJ EMPLOYEE * WORKS_ON
EMP_PROJ1 (EMP_PROJ)
SSN, FNAME, LNAME, PNUMBER, HOURS
9. Retrieve the department number, department name, manager’s ssn, name of the manager of department 4 and 7.
SQL: Relational Algebra Expression using SELECT
(), PROJECT ((pi)) and JOIN
Operators:
SELECT dnumber, dname, mgrssn, fname,
lname
FROM department, employee DNUM ( (DEPARTMENT)
DNUMBER = 4 OR DNUMBER = 7)
WHERE [Link] = [Link]
AND dno=4 OR dno=7; DEPT_MGR1 EMPLOYEE DNUM
MGRSSN=SSN
DEPT_MGR2 (DEPT_MGR1)
DNUMBER, DNAME, MGRSSN, FNAME, LNAME
10. Exercise: Display the employee’s ssn, first name, last name, department number, dependents’ name, gender, and their relation.
SQL: Relational Algebra Expression using PROJECT
((pi)) and JOIN Operators:
11. List the total number of employees and their average salary in the company. Rename the heading as Total_No_Emps
SQL: Relational Algebra Expression using aggregate
SELECT COUNT(SSN) AS Total_Num_Emp, AVG(SALARY) functions:
AS Average_Salary DEP_EMP_SAL(Total_Num_Emp, Average_Salary)
FROM employee; ℱ (EMPLOYEE)
COUNT(SSN), AVERAGE (Salary)
12. List the total number of employees, maximum salary, minimum salary, average salary Display the heading as Tot_Number_Emp,
Maximum_Salary, Minimum_Salary, Average_Salary respectively.
SQL: Relational Algebra Expression using
aggregate functions:
SELECT COUNT(SSN) Total_Num_Emp
DEP_EMP_SAL(Total_Num_Emp, Maximum_Salary,
Minimum_Salary, Average_Salary)
max(salary) Maximum_Salary, ℱ
COUNT(SSN), MAX (Salary), MIN (Salary)AVERAGE (Salary)
min(salary) Minimum_Salary, (EMPLOYEE)
avg(salary) Average_Salary
FROM employee;
13. List the total number of employees, their average salary for every department.
SQL: Relational Algebra Expression using
SELECT dno, COUNT(SSN) AS group by and aggregate functions:
DEP_EMP_SAL(Total_Num_Emp, Average_Salary)
Total_Num_Emp, AVG(SALARY) AS DNO ℱCOUNT SSN, AVERAGE Salary (EMPLOYEE)
Average_Salary
FROM employee
GROUP BY dno;
14. Display the Department Numbers, and average salary. Display only for those departments whose average salary is greater than 40,000.
SQL: Relational Algebra Expression using
SELECT dno, COUNT(SSN) AS group by and aggregate functions:
DEP_EMP_SAL(Total_Num_Emp, Average_Salary)
Total_Num_Emp, AVG(SALARY) AS DNO ℱCOUNT SSN, AVERAGE Salary (EMPLOYEE)
Average_Salary
FROM employee
GROUP BY dno DEP_EMP_SAL_GT4K
HAVING avg(salary) > 40000;; Average_Salary>40000
(DEP_EMP_SAL)
15. UNION
To retrieve the social security numbers of all employees who supervise employees working in department 5 OR department 1.
DEP5_EMPS DNO=5 (EMPLOYEE)
SELECT superssn
FROM EMPLOYEE
WHERE DNO= 5 RESULT1 SUPERSSN(DEP5_EMPS)
UNION
SELECT superssn DEP1_EMPS DNO=1(EMPLOYEE)
FROM EMPLOYEE
WHERE DNO= 1 RESULT2 SUPERSSN(DEP1_EMPS)
RESULT RESULT1 RESULT2
The union operation produces the tuples that are in either
RESULT1 or RESULT2 or both
16. INTERSECT
To retrieve the social security numbers of all employees who supervise employees working in department 5 and department 4.
DEP5_EMPS DNO=5 (EMPLOYEE)
SELECT superssn
FROM EMPLOYEE
WHERE DNO= 5 RESULT1 SUPERSSN(DEP5_EMPS)
INTERSECT
SELECT superssn DEP4_EMPS DNO=4(EMPLOYEE)
FROM EMPLOYEE
RESULT2 SUPERSSN(DEP4_EMPS)
WHERE DNO= 4 RESULT RESULT1 RESULT2
The INTERSECT operation produces the tuples that are in
either RESULT1 AND RESULT2.
17. MINUS/EXCEPT
To retrieve the social security numbers of all employees who supervise employees working in department 5 but not employees in department
4.
DEP5_EMPS DNO =5 (EMPLOYEE)
SELECT superssn
FROM EMPLOYEE
WHERE DNO= 5 RESULT1 SUPERSSN(DEP5_EMPS)
MINUS
SELECT superssn DEP4_EMPS DNO =4(EMPLOYEE)
FROM EMPLOYEE
WHERE DNO= 4 RESULT2 SUPERSSN(DEP4_EMPS)
RESULT RESULT1 - RESULT2
The INTERSECT operation produces the tuples that are in
either RESULT1 BUT NOT IN RESULT2.