Question 4 (50%) Consider the following relational database Employee Participati

Question

Question 4 (50%) Consider the following relational database Employee Participation ID Lastname Firstname Age Emp 101 Jack 102 Jackie Stewart 78 103 Niki 104 Alberto Ascari 36 Project Proj Acronym Title Cost Brabham 88 101 MO 101 MA 102 MO 103 MA 103 VE MO Moon 120 MA Mars 140 JU Jupyter 140 VE Venus 140 Lauda 68 Formulate queries that find: 1. In relational algebra: the age of employees working on the Mars project 2. In SQL: the lastname of employees working on the Mars project 3. In SQL: the projects with only one employee, showing the project acronym 4. In relational algebra: the projects with only one employee, showing the acronyms of the projects and the lastnames of the employees.

Explanation / Answer

Solution:

Please find the SQL queries

2.

SELECT LastName
FROM Employee
WHERE ID =
(SELECT Emp FROM Participation WHERE Proj =
(SELECT Acronym FROM Project WHERE Title = "Mars"));

3.

SELECT COUNT(*) AS PROJECT_WITH_ONE_EMPLOYEE, Acronym,
FROM Project, Participation
WHERE Project.Acronym=Participation.Proj
HAVING PROJECT_WITH_ONE_EMPLOYEE =
(SELECT COUNT(*) AS EMPLOYEE_COUNT
FROM Particpation
GROUP BY Emp
ORDER BY NO_OF_EMPLOYEES DESC
LIMIT 1
);

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

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