The following tables form part of a database held in a relational DBMS: Employee

Question

The following tables form part of a database held in a relational DBMS: Employee(Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno) Department(Dname, Dnumber, Mgr_ssn, Mgr_start_date)

Project(Pname, Pnumber, Plocation, Dnum)

Works_on(Essn, Pno, Hours)

The underlined attribute(s) in each relational schema is primary key.

Employee contains employee details, Super_ssn is the SSN of supervisor, which is a foreign key that refers to Ssn in table Employee, and Dno is the department number, which is another foreign key that refers to Dnumber in table Department.

Department contains department details and Mgr_ssn is SSN of the department manager, which is a foreign key that refers to Ssn in table Employee.

Project contains project details, and Dnum is a foreign key which refers to Dnumber in table

Department.

Works_on records who work on which project, Essn is a foreign key that refers to Ssn in table

Employee, and Pno is another foreign key that refers to Pnumber in table Project.

Based on the schema defined above, write SQL statements to answer the following queries:

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

2, Retrieve the name and address of all employees who work for the ‘Research’ department.

3, For each employee, retrieve the employee’s first name and last name and the first name and

last name of his or her immediate supervisor.

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

5, For each project, retrieve the project number, the project name, and the number of employees who work on that project.

Describe what the following SQL statements return and give the equivalent relational algebra expressions.

6, Select Fname, Lname, Sex, Address

From Employee;

7, Select Fname, Lname

From Employee

Where Sex=’M’;

Describe what the following relational algebra expressions return and give the equivalent SQL

statements.

8, Lname, Fname, Salary(salary>30000(Employee))

9, Dname, Lname, Fname(Department   Mgr_ssn=SsnEmployee)

Explanation / Answer

#1

select concat(Fname,' ',Lname) as employeename,Bdate, Address

from Employee

where concat(Fname,' ',Lname) = 'John B. Smith';

#2

select concat(Fname,' ',Lname), Address

from Employee as E join Department as D

on E.Dno = D.Dnumber

where D.Dname = 'Research';

#3   

select E1.Fname as emp_fname, E1.Lname as emp_lname, E2.Fname as supervisor_fname, E2.Lname as supervisor_lname

from Employee as E1 join Employee as E2

on E1.Super_ssn = E2.ssn;

#4

select sum(salary) as sum_of_salary, max(salary) as maximum_salary,

min(salary) as minimum_salary, avg(salary) as average_salary

from Employee;

#5

select P.Pnumber, P.Pname, count(W.Essn) no_of_employees

from Project as P join Works_on W

on P.Pnumber = W.Pno

group by P.Pnumber, P.Pname;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.