· 8 years ago · Mar 31, 2018, 04:06 PM
1IF EXISTS (SELECT name FROM sys.databases d WHERE d.name LIKE 'EMPLOYEE_MANAGEMENT')
2 DROP DATABASE EMPLOYEE_MANAGEMENTS
3GO
4--Create databases
5CREATE DATABASE EMPLOYEE_MANAGEMENTS
6GO
7
8USE EMPLOYEE_MANAGEMENTS
9
10--CREATE SCHEMA
11CREATE SCHEMA mr
12--DROP SCHEMA anp
13GO
14
15
16--Create table Employee
17CREATE TABLE mr.Employee(
18 emp_no INT IDENTITY(1,1) PRIMARY KEY,
19 emp_name NVARCHAR(100) NOT NULL,
20 bitrh_date DATE,
21 email NVARCHAR(150) UNIQUE,
22 dept_no INT,
23 mgr_no INT NOT NULL,
24 starts_date DATE DEFAULT CAST(GETDATE() AS DATE),
25 salary INT,
26 [level] INT DEFAULT 1,
27 [status] INT DEFAULT 0,
28 note TEXT,
29 CONSTRAINT CHK_LEVEL CHECK([level] >= 1 AND [level] <=7),
30 CONSTRAINT CHK_STATUS CHECK([status] >= 0 AND [status] <=3)
31 )
32 --DROP TABLE anp.Employee
33 --SELECT * FROM anp.Employee
34 --DELETE FROM anp.Employee
35GO
36
37--Create table Skill
38CREATE TABLE mr.Skill(
39 skill_no INT IDENTITY(1,1),
40 skill_name NVARCHAR(100),
41 note NTEXT,
42 CONSTRAINT PK_SKILL_NO PRIMARY KEY(skill_no)
43 )
44GO
45
46--Create table Emp_Skill
47CREATE TABLE mr.Emp_Skill(
48 skill_no INT,
49 emp_no INT,
50 skill_level INT DEFAULT 1,
51 reg_date DATE DEFAULT CAST(GETDATE() AS DATE),
52 [description] TEXT,
53 CONSTRAINT CHK_SKILL_LEVEL CHECK(skill_level >= 1 AND skill_level <=3),
54 CONSTRAINT PK_SKILL_EMP_NO PRIMARY KEY(skill_no,emp_no),
55 CONSTRAINT FK_EMP_NO FOREIGN KEY(emp_no) REFERENCES mr.Employee(emp_no),
56 CONSTRAINT FK_SKILL_NO FOREIGN KEY(skill_no) REFERENCES mr.Skill(skill_no)
57 )
58
59 --DROP TABLE anp.Emp_Skill
60GO
61
62
63--Create table Department
64CREATE TABLE mr.Department(
65 dept_no INT IDENTITY(1,1),
66 dept_name NVARCHAR(100),
67 note NTEXT,
68 CONSTRAINT PK_DEPT_NO PRIMARY KEY(dept_no)
69 )
70
71--DROP TABLE anp.Department
72
73GO
74
75--ALTER TABLE anp.Employee
76--ALTER CONSTRAINT CHK_STATUS CHECK([status] >= 0 AND [status] <=3)
77--ADD CONSTRAINT FK_EMP_DEPT FOREIGN KEY(dept_no) REFERENCES anp.Department(dept_no)
78--DROP CONSTRAINT FK_EMP_DEPT
79--GO
80
81--TRUNCATE TABLE anp.Employee
82INSERT INTO mr.Employee VALUES ('An','1993-10-10','phamduyan9x@gmail.com',1,1,'2017-3-29',1000,2,1,'empty')
83INSERT INTO mr.Employee VALUES ('Bach','1997-4-30','back@fsoft.com.vn',1,1,'2016-3-29',7000,1,1,'empty')
84INSERT INTO mr.Employee VALUES ('Chung','1995-9-15','Chung@fsoft.com.vn',1,1,'2015-3-29',8000,4,1,'empty')
85INSERT INTO mr.Employee VALUES ('Nam','1993-10-10','Nam@fsoft.com.vn',1,1,'2018-3-29',5000,4,1,'empty')
86INSERT INTO mr.Employee VALUES ('Anh1','1992-06-5','Anh1@fsoft.com.vn',1,1,'2018-3-29',4000,3,1,'empty')
87INSERT INTO mr.Employee VALUES ('Anh','1996-07-5','Anh@fsoft.com.vn',1,1,'2018-3-29',4000,3,1,'empty')
88INSERT INTO mr.Employee VALUES ('HAnh','1992-06-5','HAnh@fsoft.com.vn',1,1,'2018-3-29',4000,3,1,'empty')
89INSERT INTO mr.Employee VALUES ('Anh','1992-06-5','AnhPH@fsoft.com.vn',1,1,'2018-3-29',4000,3,1,'empty')
90
91SELECT * FROM mr.Employee
92
93GO
94
95--TRUNCATE TABLE anp.Department
96INSERT INTO mr.Department VALUES ('FA','Fresher')
97INSERT INTO mr.Department VALUES ('CTC','Training')
98INSERT INTO mr.Department VALUES ('FSU1','Fresher')
99INSERT INTO mr.Department VALUES ('N500','EU, USA')
100INSERT INTO mr.Department VALUES ('FSU17','Japan')
101INSERT INTO mr.Department VALUES ('FSU18','Japan')
102INSERT INTO mr.Department VALUES ('FSU19','Japan')
103INSERT INTO mr.Department VALUES ('FSU10','Japan')
104SELECT * FROM mr.Department
105GO
106
107--TRUNCATE TABLE anp.Skill
108INSERT INTO mr.Skill VALUES ('C++','Java Core')
109INSERT INTO mr.Skill VALUES ('SQL','SQL Server')
110INSERT INTO mr.Skill VALUES ('.NET','Spring MVC')
111INSERT INTO mr.Skill VALUES ('.NET','Hibernate')
112INSERT INTO mr.Skill VALUES ('JDBC','JDBC')
113INSERT INTO mr.Skill VALUES ('C/C++','C/C++')
114INSERT INTO mr.Skill VALUES ('.NET','.NET')
115INSERT INTO mr.Skill VALUES ('FRONEND','FRONEND')
116
117SELECT * FROM mr.Skill
118
119GO
120
121--TRUNCATE TABLE anp.Emp_Skill
122INSERT INTO mr.Emp_Skill VALUES (1,2,3,'2018-3-29',NULL)
123INSERT INTO mr.Emp_Skill VALUES (1,1,1,'2018-3-29',NULL)
124INSERT INTO mr.Emp_Skill VALUES (3,2,2,'2018-3-29',NULL)
125INSERT INTO mr.Emp_Skill VALUES (4,1,3,'2018-3-29',NULL)
126INSERT INTO mr.Emp_Skill VALUES (5,1,1,'2018-3-29',NULL)
127INSERT INTO mr.Emp_Skill VALUES (4,3,2,'2018-3-29',NULL)
128INSERT INTO mr.Emp_Skill VALUES (4,2,3,'2018-3-29',NULL)
129INSERT INTO mr.Emp_Skill VALUES (3,1,2,'2018-3-29',NULL)
130
131
132SELECT * FROM mr.Emp_Skill
133GO
134
135--b
136SELECT e.emp_name,e.email , d.dept_name, e.starts_date
137FROM mr.Employee e
138INNER JOIN mr.Department d ON e.dept_no = d.dept_no
139WHERE DATEDIFF(MM, e.starts_date, CAST(GETDATE() AS DATE)) >=6
140
141GO
142
143--c
144SELECT e.emp_name
145FROM mr.Employee e JOIN mr.Emp_Skill es ON e.emp_no = es.emp_no
146WHERE es.skill_no IN (
147 SELECT es.skill_no
148 FROM mr.Emp_Skill es JOIN mr.Skill s ON es.skill_no = s.skill_no
149 WHERE s.skill_name = 'C++' OR s.skill_name = '.NET'
150)
151GO
152-- d
153SELECT e1.emp_name, e2.emp_name, e2.email
154FROM mr.Employee e1 JOIN mr.Employee e2 ON e1.mgr_no = e2.emp_no
155GO
156
157-- e
158SELECT D_T.dept_no, e.*
159FROM (SELECT d.dept_no
160 FROM mr.Department d JOIN mr.Employee e ON d.dept_no = e.dept_no
161 GROUP BY d.dept_no
162 HAVING COUNT(e.dept_no) >= 2) AS D_T JOIN mr.Employee e ON D_T.dept_no = e.dept_no
163
164-- f
165SELECT e.emp_name, e.email, e.bitrh_date
166FROM mr.Employee e WHERE e.emp_no IN (
167 SELECT e.emp_no
168 FROM mr.Employee e JOIN mr.Emp_Skill es ON e.emp_no = es.emp_no
169 WHERE e.status = 0
170 GROUP BY e.emp_no
171 HAVING COUNT(es.skill_no) > 1
172 )
173
174SELECT * FROM mr.Employee
175-- h
176GO
177
178CREATE VIEW view_Employee_Are_Working
179AS
180SELECT e.emp_name, d.dept_name
181FROM mr.Employee e JOIN mr.Department d ON e.dept_no = d.dept_no
182JOIN mr.Emp_Skill es ON e.emp_no = es.emp_no
183JOIN mr.Skill s ON es.skill_no = s.skill_no
184WHERE e.[status] = 0