· 8 years ago · Apr 03, 2018, 04:30 PM
1-- MySQL Script generated by MySQL Workbench
2-- Tue Apr 3 22:06:42 2018
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5-- Author: Xuan_Thang
6
7SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
8SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
9SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14-- -----------------------------------------------------
15-- Schema qlsv
16-- -----------------------------------------------------
17
18-- -----------------------------------------------------
19-- Schema qlsv
20-- -----------------------------------------------------
21CREATE SCHEMA IF NOT EXISTS `qlsv` DEFAULT CHARACTER SET utf8 ;
22USE `qlsv` ;
23
24-- -----------------------------------------------------
25-- Table `qlsv`.`khoa`
26-- -----------------------------------------------------
27CREATE TABLE IF NOT EXISTS `qlsv`.`khoa` (
28 `makh` VARCHAR(4) CHARACTER SET 'utf8' NOT NULL,
29 `tenkhoa` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL,
30 ` namthanhlap` INT(11) NULL DEFAULT NULL,
31 PRIMARY KEY (`makh`))
32ENGINE = InnoDB
33DEFAULT CHARACTER SET = utf8
34COLLATE = utf8_unicode_ci;
35
36
37-- -----------------------------------------------------
38-- Table `qlsv`.`mhoc`
39-- -----------------------------------------------------
40CREATE TABLE IF NOT EXISTS `qlsv`.`mhoc` (
41 `tenmh` VARCHAR(45) CHARACTER SET 'utf8' NULL DEFAULT NULL,
42 `mamh` VARCHAR(8) CHARACTER SET 'utf8' NOT NULL,
43 `tinchi` INT(11) NULL DEFAULT NULL,
44 `makh` VARCHAR(4) CHARACTER SET 'utf8' NULL DEFAULT NULL,
45 PRIMARY KEY (`mamh`),
46 INDEX `fk_mhoc_khoa` (`makh` ASC),
47 CONSTRAINT `fk_mhoc_khoa`
48 FOREIGN KEY (`makh`)
49 REFERENCES `qlsv`.`khoa` (`makh`))
50ENGINE = InnoDB
51DEFAULT CHARACTER SET = utf8
52COLLATE = utf8_unicode_ci;
53
54
55-- -----------------------------------------------------
56-- Table `qlsv`.`dkien`
57-- -----------------------------------------------------
58CREATE TABLE IF NOT EXISTS `qlsv`.`dkien` (
59 `mamh` VARCHAR(8) CHARACTER SET 'utf8' NULL DEFAULT NULL,
60 `mamh_truoc` VARCHAR(8) CHARACTER SET 'utf8' NULL DEFAULT NULL,
61 INDEX `fk_dkien_mhoc` (`mamh` ASC),
62 CONSTRAINT `fk_dkien_mhoc`
63 FOREIGN KEY (`mamh`)
64 REFERENCES `qlsv`.`mhoc` (`mamh`))
65ENGINE = InnoDB
66DEFAULT CHARACTER SET = utf8
67COLLATE = utf8_unicode_ci;
68
69
70-- -----------------------------------------------------
71-- Table `qlsv`.`hocphan`
72-- -----------------------------------------------------
73CREATE TABLE IF NOT EXISTS `qlsv`.`hocphan` (
74 `mahp` INT(11) NOT NULL,
75 `mamh` VARCHAR(8) CHARACTER SET 'utf8' NULL DEFAULT NULL,
76 `hocky` INT(11) NULL DEFAULT NULL,
77 `nam` INT(11) NULL DEFAULT NULL,
78 `gv` VARCHAR(45) CHARACTER SET 'utf8' NULL DEFAULT NULL,
79 PRIMARY KEY (`mahp`))
80ENGINE = InnoDB
81DEFAULT CHARACTER SET = utf8
82COLLATE = utf8_unicode_ci;
83
84
85-- -----------------------------------------------------
86-- Table `qlsv`.`svien`
87-- -----------------------------------------------------
88CREATE TABLE IF NOT EXISTS `qlsv`.`svien` (
89 `ten` VARCHAR(30) CHARACTER SET 'utf8' NULL DEFAULT NULL,
90 `masv` INT(11) NOT NULL,
91 `nam` INT(11) NULL DEFAULT NULL,
92 `makh` VARCHAR(4) CHARACTER SET 'utf8' NULL DEFAULT NULL,
93 PRIMARY KEY (`masv`),
94 INDEX `fk_svien_khoa` (`makh` ASC),
95 CONSTRAINT `fk_svien_khoa`
96 FOREIGN KEY (`makh`)
97 REFERENCES `qlsv`.`khoa` (`makh`))
98ENGINE = InnoDB
99DEFAULT CHARACTER SET = utf8
100COLLATE = utf8_unicode_ci;
101
102
103-- -----------------------------------------------------
104-- Alter table foreign key references
105-- -----------------------------------------------------
106
107/*alter table kqua add constraint fk_svien_kqua foreign key(masv) references svien(masv);
108alter table kqua add constraint fk_hphan_kqua foreign key(mahp) references hocphan(mahp);
109alter table dkien add constraint fk_dkien_mhoc foreign key(mamh) references mhoc(mamh);
110alter table mhoc add constraint fk_mhoc_khoa foreign key(makh) references khoa(makh);*/
111
112
113
114-- -----------------------------------------------------
115-- Table `qlsv`.`kqua`
116-- -----------------------------------------------------
117CREATE TABLE IF NOT EXISTS `qlsv`.`kqua` (
118 `masv` INT(11) NULL DEFAULT NULL,
119 `mahp` INT(11) NULL DEFAULT NULL,
120 `diem` DOUBLE NULL DEFAULT NULL,
121 INDEX `fk_svien_kqua` (`masv` ASC),
122 INDEX `fk_hphan_kqua` (`mahp` ASC),
123 CONSTRAINT `fk_hphan_kqua`
124 FOREIGN KEY (`mahp`)
125 REFERENCES `qlsv`.`hocphan` (`mahp`),
126 CONSTRAINT `fk_svien_kqua`
127 FOREIGN KEY (`masv`)
128 REFERENCES `qlsv`.`svien` (`masv`))
129ENGINE = InnoDB
130DEFAULT CHARACTER SET = utf8
131COLLATE = utf8_unicode_ci;
132
133
134SET SQL_MODE=@OLD_SQL_MODE;
135SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
136SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
137
138-- -----------------------------------------------------
139-- Insert `qlsv`.`khoa`
140-- -----------------------------------------------------
141
142INSERT INTO `qlsv`.`khoa` (`makh`, `tenkhoa`, ` namthanhlap`) VALUES ('CNTT', 'Công Nghệ Thông Tin', '1995');
143INSERT INTO `qlsv`.`khoa` (`makh`, `tenkhoa`, ` namthanhlap`) VALUES ('VL', 'Váºt Lý', '1976');
144INSERT INTO `qlsv`.`khoa` (`makh`, `tenkhoa`, ` namthanhlap`) VALUES ('TOAN', 'Toán', '1976');
145INSERT INTO `qlsv`.`khoa` (`makh`, `tenkhoa`, ` namthanhlap`) VALUES ('LUAT', 'Luáºt', '1995');
146
147
148-- -----------------------------------------------------
149-- Insert `qlsv`.`mhoc`
150-- -----------------------------------------------------
151
152INSERT INTO `qlsv`.`mhoc` (`tenmh`, `mamh`, `tinchi`, `makh`) VALUES ('Nháºp môn Tin há»c', 'COSC1310', '4', 'CNTT');
153INSERT INTO `qlsv`.`mhoc` (`tenmh`, `mamh`, `tinchi`, `makh`) VALUES ('Cấu trúc dữ liệu', 'COSC3320', '4', 'CNTT');
154INSERT INTO `qlsv`.`mhoc` (`tenmh`, `mamh`, `tinchi`, `makh`) VALUES ('Toán rá»i rạc', 'MATH2410', '3', 'TOAN');
155INSERT INTO `qlsv`.`mhoc` (`tenmh`, `mamh`, `tinchi`, `makh`) VALUES ('Cơ sở dữ liệu', 'COSC3380', '3', 'CNTT');
156INSERT INTO `qlsv`.`mhoc` (`tenmh`, `mamh`, `tinchi`, `makh`) VALUES ('Váºt lý đại cÆ°Æ¡ng', 'PHYS3332', '3', 'VL');
157
158
159-- -----------------------------------------------------
160-- Insert `qlsv`.`hocphan``
161-- -----------------------------------------------------
162
163INSERT INTO `qlsv`.`hocphan` (`mahp`, `mamh`, `hocky`, `nam`, `gv`) VALUES ('85', 'MATH2410', '1', '1996', 'Kim');
164INSERT INTO `qlsv`.`hocphan` (`mahp`, `mamh`, `hocky`, `nam`, `gv`) VALUES ('92', 'COSC1310', '1', '1996', 'An');
165INSERT INTO `qlsv`.`hocphan` (`mahp`, `mamh`, `hocky`, `nam`, `gv`) VALUES ('102', 'COSC3320', '2', '1997', 'Nhiên');
166INSERT INTO `qlsv`.`hocphan` (`mahp`, `mamh`, `hocky`, `nam`, `gv`) VALUES ('112', 'MATH2410', '1', '1997', 'Vân');
167INSERT INTO `qlsv`.`hocphan` (`mahp`, `mamh`, `hocky`, `nam`, `gv`) VALUES ('119', 'COSC1310', '1', '1997', 'An');
168INSERT INTO `qlsv`.`hocphan` (`mahp`, `mamh`, `hocky`, `nam`, `gv`) VALUES ('135', 'COSC3380', '1', '1997', 'SÆ¡n');
169
170
171-- -----------------------------------------------------
172-- Insert `qlsv`.`svien`
173-- -----------------------------------------------------
174
175INSERT INTO `qlsv`.`svien` (`ten`, `masv`, `nam`, `makh`) VALUES ('Tran Xuan Thang', '0151', '1996', 'CNTT');
176INSERT INTO `qlsv`.`svien` (`ten`, `masv`, `nam`, `makh`) VALUES ('Tran Trong Thuc', '123456798', '1998', 'TOAN');
177INSERT INTO `qlsv`.`svien` (`ten`, `masv`, `nam`, `makh`) VALUES ('Trần Trá»ng Äức', '987654123', '2009', 'VL');
178INSERT INTO `qlsv`.`svien` (`ten`, `masv`, `nam`, `makh`) VALUES ('Nguyễn Văn A', '15988', '2018', 'CNTT');
179
180
181-- -----------------------------------------------------
182-- Insert `qlsv`.`kqua`
183-- -----------------------------------------------------
184
185INSERT INTO `qlsv`.`kqua` (`masv`, `mahp`, `diem`) VALUES ('123456798', '85', '8');
186INSERT INTO `qlsv`.`kqua` (`masv`, `mahp`, `diem`) VALUES ('123456798', '119', '6');
187INSERT INTO `qlsv`.`kqua` (`masv`, `mahp`, `diem`) VALUES ('151', '102', '10');
188INSERT INTO `qlsv`.`kqua` (`masv`, `mahp`, `diem`) VALUES ('15988', '92', '10');
189INSERT INTO `qlsv`.`kqua` (`masv`, `mahp`, `diem`) VALUES ('987654123', '85', '8');
190INSERT INTO `qlsv`.`kqua` (`masv`, `mahp`, `diem`) VALUES ('151', '135', '9');
191
192
193-- -----------------------------------------------------
194-- Insert `qlsv`.`dkien`
195-- -----------------------------------------------------
196
197INSERT INTO `qlsv`.`dkien` (`mamh`, `mamh_truoc`) VALUES ('COSC3380', 'COSC3320');
198INSERT INTO `qlsv`.`dkien` (`mamh`, `mamh_truoc`) VALUES ('COSC3380', 'MATH2410');
199INSERT INTO `qlsv`.`dkien` (`mamh`, `mamh_truoc`) VALUES ('COSC3320', 'COSC1310');
200
201
202-- -----------------------------------------------------
203-- Execute Query
204-- -----------------------------------------------------
205
206
207-- Câu 1: Cho biết tên các SV khoa CNTT
208
209select sv.ten from svien sv join khoa kh on sv.makh = kh.makh
210 where kh.tenkhoa = 'Công Nghệ Thông Tin';
211
212-- Câu 2: Kết quả há»c táºp SV có mã số 0151;
213
214 select kq.* from kqua kq join svien sv on kq.masv = sv.masv
215 where sv.masv = '0151';
216
217 -- Câu 3: Tên các SV thuá»™c vá» khoa dạy môn 'Toán rá»i rạc';
218
219 select sv.ten,sv.masv,kh.tenkhoa,mh.mamh from svien sv join khoa kh on sv.makh = kh.makh
220 join mhoc mh on mh.makh = kh.makh
221 where mh.tenmh = 'Toán rá»i rạc';
222
223-- Câu 4: Mã há»c phần và số lượng SV đăng ký theo từng há»c phần;
224
225select hp.mahp , count(sv.masv) as 'Số lượng' from hocphan hp join kqua kq on hp.mahp = kq.mahp
226 join svien sv on kq.masv = sv.masv
227 group by hp.mahp;
228
229-- Câu 5: Tên SV, Ä‘iểm trung bình của SV đó theo từng há»c kỳ của từng năm há»c
230
231
232select sv.ten,sum(kq.diem),hp.hocky,hp.nam from svien sv join kqua kq on sv.masv = kq.masv
233 join hocphan hp on hp.mahp = kq.mahp
234 group by sv.ten,hp.hocky,hp.nam;
235
236
237
238
239-- -----------------------------------------------------
240-- The End
241-- -----------------------------------------------------