Database Principle and Application - 6 ========================= There is a Company Database that manages the data of employee, department and project. The relational schemas are as follows: EMPLOYEE (Fname, Lname, Employee_id, Gender, Salary, DNO). The attribute means: First name, Last name, employee id, gender, salary, the department number in which the employee works. Each employee has a unique employee id, and can join in projects that in different department. DEPARTMENT (DNO, Dname, Manager_id, Dlocation). The attribute means: Department number, department name, department manager id, department location. The department manager is a type of the employee, thus the Manager_id is a foreign key that references the Employee_id in EMPLOYEE table. PROJECT (Project_NO, Pname, DNO). The attribute means: project number, project name, department number of the department that the project belongs to. Part of the data in the relations are as follows： Table1 EMPLOYEE Fname Lname Employee_id Gender Salary DNO John Smith 20182101 Male 40000 2 Franklin Wong 20182102 Male 50000 1 Jennifer Wallace 20183101 Female 35000 3 Joyce Clinton 20183102 Female 28000 3 Peter Bush 20184101 Male 33000 4 Table 2 DEPARTMENT DNO Dname Manager_id Dlocation 1 Research 20181105 Stanford 2 Technology 20182102 Houston 3 Service 20183101 Houston 4 Administration 20184101 Stanford Table 3 PROJECT Project_NO Pname DNO 1001 TransportationMap 1 1002 TwitterFriendship 1 1003 PowerSystem 1 2001 Python 2 2002 Parallel 2 3001 Customer 3 Based on the above relational schema, please write the SQL scripts to do the question - 3. Notes: you do not need to give the query results. You only need to give the query script or the relational algebra expression. Question: 3. Find the number of employees in different department
=========================
There is a Company Database that manages the data of employee, department and project. The relational schemas are as follows:
EMPLOYEE (Fname, Lname, Employee_id, Gender, Salary, DNO). The attribute means: First name, Last name, employee id, gender, salary, the department number in which the employee works. Each employee has a unique employee id, and can join in projects that in different department.
DEPARTMENT (DNO, Dname, Manager_id, Dlocation). The attribute means: Department number, department name, department manager id, department location. The department manager is a type of the employee, thus the Manager_id is a foreign key that references the Employee_id in EMPLOYEE table.
PROJECT (Project_NO, Pname, DNO). The attribute means: project number, project name, department number of the department that the project belongs to.
Part of the data in the relations are as follows：
Table1 EMPLOYEE
|
Fname |
Lname |
Employee_id |
Gender |
Salary |
DNO |
|
John |
Smith |
20182101 |
Male |
40000 |
2 |
|
Franklin |
Wong |
20182102 |
Male |
50000 |
1 |
|
Jennifer |
Wallace |
20183101 |
Female |
35000 |
3 |
|
Joyce |
Clinton |
20183102 |
Female |
28000 |
3 |
|
Peter |
Bush |
20184101 |
Male |
33000 |
4 |
Table 2 DEPARTMENT
|
DNO |
Dname |
Manager_id |
Dlocation |
|
1 |
Research |
20181105 |
Stanford |
|
2 |
Technology |
20182102 |
Houston |
|
3 |
Service |
20183101 |
Houston |
|
4 |
Administration |
20184101 |
Stanford |
Table 3 PROJECT
|
Project_NO |
Pname |
DNO |
|
1001 |
TransportationMap |
1 |
|
1002 |
TwitterFriendship |
1 |
|
1003 |
PowerSystem |
1 |
|
2001 |
Python |
2 |
|
2002 |
Parallel |
2 |
|
3001 |
Customer |
3 |
Based on the above relational schema, please write the SQL scripts to do the question - 3. Notes: you do not need to give the query results. You only need to give the query script or the relational algebra expression.
Question:
3. Find the number of employees in different department.
Step by step
Solved in 2 steps
