Database engine for information systerm
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (123.77 KB, 2 trang )
A company wants to manage all information employees. After analyzing and designing, they created the following
tables and relationships.
POSITIONS
POS_ID
1
2
3
4
5
6
POSID
ACE
ACM
ITE
ITM
MKE
MKM
DEPARTMENTS
POSName
POSSalary
POSAllowance
DEP_ID
DEPID
Accounting Employee
Accounting Manager
IT Employee
IT Manager
Marketing Employee
Marketing Manager
2700
5500
2500
6000
2300
6000
100
500
100
500
100
400
1
2
3
4
E0001
E0002
E0003
E0004
DEPName
IT
Accounting
Maketing
Security
EMPLOYEES
EMP_ID EMPID EMPLastName
1
2
3
4
5
6
7
8
EM01
EM02
EM03
EM04
EM05
EM06
EM07
EM10
Nguyen Huu
Phung Minh
Bui Bach
Phan Hong
Ton Ngoc
Nguyen Thi
Pham Sy
Nguyen Minh
EMP
FirstName
Anh
Thu
Khoa
Phuong
Hien
Hoa
Nguyen
Phuong
EMP
EMPDOB EMPHiredDate EMPAddress POS_ID DEP_ID
Gender
1
0
1
0
0
0
1
1
08/10/76
15/04/83
15/12/84
25/08/72
10/10/82
10/12/82
02/10/70
10/07/80
Page 1
10/10/06
12/02/06
10/12/06
08/08/05
08/02/06
25/10/06
05/03/07
03/12/07
Binh Thanh
District 10
District 5
District 7
District 4
Binh Thanh
District 2
Binh Thanh
4
3
3
2
1
1
5
6
1
1
1
2
2
2
3
3
REQUIREMENTS
Write SQL statements (queries) for all questions below
1. Display Last name and First name of all employees whose last name start with “Nguyen”.
2. Display all information of employees whose age from 20 to 28.
3. Display department(s) that doesn’t/don’t have any employees.
4. List all department(s) that have the number of employees is greater than two.
5. Display First name, Last name, Gender and Salary of employee(s) who has/have got the lowest
salary.
6. Display the number of employees of each department. The following is the result format:
Department
IT
Accounting
…
Number of Employees
3
4
…
7. Display total payment of each department. The following is the result format:
Department
IT
Accounting
…
Total Payment
11700
11600
…
8. Show all information of departments have the highest number of employees
Page 2
