SQL and Relational Algebra Related question There is a Company Database that manages the data of employee, department and project. The relational schemas are as follows: EMPLOYEE (Gname, Fname, Employee_id, Bdate, Gender, Salary, Dno). The attribute means: given name, family name, employee id, birthdate, 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, Dloc). 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_id, Pname, DNO). The attribute means: project id, project name, department number of the department that the project belongs to. Part of the data in the relations are as follows： Table1 EMPLOYEE Gname Fname Employee_id Bdate Gender Salary DNO Franklin Wang 20181101 1981-01-16 M 50000 1 Stefan Li 20182102 1980-05-15 M 60000 2 Jennifer Wallace 20133101 1978-08-16 F 45000 3 Joyce Clinton 20183102 1975-06-23 F 28000 3 Peter Bush 20194101 1990-11-25 M 35000 4 Lucy Hilton 20181302 1985-10-13 F 65000 1 Table 2 DEPARTMENT DNO Dname Manager_id Dloc 1 Research 20181105 Shanghai 2 Technology 20182102 Beijing 3 Service 20183101 Shanghai 4 Administration 20184101 Shenzhen 5 Marketing 20135203 Beijing Table 3 PROJECT Project_id Pname DNO 1001 TransportationMap 1 1002 TwitterFriendship 1 1003 PowerSystem 1 2001 Python 2 2002 Parallel 2 3001 Customer 3 4003 Communication 4 Based on the above relational schema, please write the SQL scripts to do questions 1-6. Notes: Below this short question-answer you need to give the query results and script take a screenshot and paste it here. Find the information of employees whose salary is equal to or greater than 50000. Find the department name and its location which locates in Shenzhen. Find the number of employees in each department. The result should demonstrate the DNO and the number of employees.

SQL and Relational Algebra Related question There is a Company Database that manages the data of employee, department and project. The relational schemas are as follows: EMPLOYEE (Gname, Fname, Employee_id, Bdate, Gender, Salary, Dno). The attribute means: given name, family name, employee id, birthdate, 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, Dloc). 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_id, Pname, DNO). The attribute means: project id, project name, department number of the department that the project belongs to. Part of the data in the relations are as follows： Table1 EMPLOYEE Gname Fname Employee_id Bdate Gender Salary DNO Franklin Wang 20181101 1981-01-16 M 50000 1 Stefan Li 20182102 1980-05-15 M 60000 2 Jennifer Wallace 20133101 1978-08-16 F 45000 3 Joyce Clinton 20183102 1975-06-23 F 28000 3 Peter Bush 20194101 1990-11-25 M 35000 4 Lucy Hilton 20181302 1985-10-13 F 65000 1 Table 2 DEPARTMENT DNO Dname Manager_id Dloc 1 Research 20181105 Shanghai 2 Technology 20182102 Beijing 3 Service 20183101 Shanghai 4 Administration 20184101 Shenzhen 5 Marketing 20135203 Beijing Table 3 PROJECT Project_id Pname DNO 1001 TransportationMap 1 1002 TwitterFriendship 1 1003 PowerSystem 1 2001 Python 2 2002 Parallel 2 3001 Customer 3 4003 Communication 4 Based on the above relational schema, please write the SQL scripts to do questions 1-6. Notes: Below this short question-answer you need to give the query results and script take a screenshot and paste it here. Find the information of employees whose salary is equal to or greater than 50000. Find the department name and its location which locates in Shenzhen. Find the number of employees in each department. The result should demonstrate the DNO and the number of employees.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

SQL and Relational Algebra Related question

There is a Company Database that manages the data of employee, department and project. The relational schemas are as follows:

EMPLOYEE (Gname, Fname, Employee_id, Bdate, Gender, Salary, Dno). The attribute means: given name, family name, employee id, birthdate, 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, Dloc). 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_id, Pname, DNO). The attribute means: project id, project name, department number of the department that the project belongs to.

Part of the data in the relations are as follows：

Table1 EMPLOYEE

Gname	Fname	Employee_id	Bdate	Gender	Salary	DNO
Franklin	Wang	20181101	1981-01-16	M	50000	1
Stefan	Li	20182102	1980-05-15	M	60000	2
Jennifer	Wallace	20133101	1978-08-16	F	45000	3
Joyce	Clinton	20183102	1975-06-23	F	28000	3
Peter	Bush	20194101	1990-11-25	M	35000	4
Lucy	Hilton	20181302	1985-10-13	F	65000	1

Table 2 DEPARTMENT

DNO	Dname	Manager_id	Dloc
1	Research	20181105	Shanghai
2	Technology	20182102	Beijing
3	Service	20183101	Shanghai
4	Administration	20184101	Shenzhen
5	Marketing	20135203	Beijing

Table 3 PROJECT

Project_id	Pname	DNO
1001	TransportationMap	1
1002	TwitterFriendship	1
1003	PowerSystem	1
2001	Python	2
2002	Parallel	2
3001	Customer	3
4003	Communication	4

Based on the above relational schema, please write the SQL scripts to do questions 1-6.

Notes: Below this short question-answer you need to give the query results and script take a screenshot and paste it here.

Find the information of employees whose salary is equal to or greater than 50000.
Find the department name and its location which locates in Shenzhen.
Find the number of employees in each department. The result should demonstrate the DNO and the number of employees.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 4 steps with 5 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.