-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathQLDeTai.sql
2171 lines (1925 loc) · 131 KB
/
QLDeTai.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
set nocount on
if exists (select * from SysObjects where name='spGetTableList' and type='P')
drop proc spGetTableList
go
create proc spGetTableList
@arrTables cursor varying output
as begin
set @arrTables = cursor for
select table_name
from Information_Schema. TABLES
where table_type='BASE TABLE'
--and table_name <> 'dtproperties'
open @arrTables
end
go
if exists (select * from SysObjects where name='spGetFKList' and type='P')
drop proc spGetFKList
go
create proc spGetFKList
@arrFKs cursor varying output
as begin
set @arrFKs = cursor for
select constraint_name, table_name
from Information_Schema. TABLE_CONSTRAINTS
where constraint_type='FOREIGN KEY'
open @arrFKs
end
go
-----------------------------------------------------------------------------------------------
-- đoạn này dùng để lấy tên table và fk để drop các constraints
if exists (select * from SysObjects where name='spClearFKs' and type='P')
drop proc spClearFKs
go
create proc spClearFKs
@dbName varchar(66)
as begin
--save db hiện hành
declare @curDB varchar(66)
set @curDB=db_name()
if (@dbName is null) or (@dbName='') begin
set @dbName=db_name()
end
if @dbName in ('master') begin
raisError ('Không thể xóa object(s) hệ thống!',16,1)
return -1
end
--db_name() này ở đâu ra vậy , có phải là hàm có sẵn ko ?
----La` ha`m co' san~
--chuyen de'n db muo'n xoa'
declare @sc nvarchar(333)
set @sc='use '+@dbName
exec sp_executeSQL @sc
--lấy ds các FK & bảng chứa chúng
declare @c cursor
exec spGetFKList @c output
--thực hiện xóa FK
declare @tableName varchar(66), @fkName varchar(66)
fetch next from @c into @fkName, @tableName
while @@fetch_status=0 begin
set @sc='alter table '+@tableName+' drop constraint '+ @fkName
exec sp_executeSQL @sc
fetch next from @c into @fkName, @tableName
end
close @c
deallocate @c
--
set @sc='use '+@curDB
exec sp_executeSQL @sc
end
go
-----------------------------------------------------------------------------------------------
--Đoạn này để drop table
if exists (select * from SysObjects where name='spClearTables' and type='P')
drop proc spClearTables
go
create proc spClearTables
@dbName varchar(66)
as begin
declare @curDB varchar(66)
set @curDB=db_name()
--
declare @sc nvarchar(333)
if (@dbName is null) or (@dbName='') begin
set @dbName=db_name()
end
if @dbName in ('master') begin
raisError ('Không thể xóa object(s) hệ thống!',16,1)
return -1
end
set @sc='use '+@dbName
exec sp_executeSQL @sc
--
declare @c cursor
exec spGetTableList @c output
declare @tableName varchar(66)
fetch next from @c into @tableName
while @@fetch_status=0 begin
set @sc='drop table '+@tableName
exec sp_executeSQL @sc
fetch next from @c into @tableName
end
close @c
deallocate @c
--
set @sc = 'use '+@curDB
exec sp_executeSQL @sc
--
return 0
end
go
-----------------------------------------------------------------------------------------------
--doạn này drop db
if exists (select * from SysObjects where name='spClearDB' and type='P') --type ='P' or 'U' là gì vậy
drop proc spClearDB
go
create proc spClearDB
@dbName varchar(66)
as begin
declare @curDB varchar(66)
set @curDB=db_name()
--
if (@dbName is null) or (@dbName='') begin
set @dbName=db_name()
end
if @dbName in ('master') begin
raisError ('Không thể xóa object(s) hệ thống!',16,1)
return -1
end
--
exec spClearFKs @dbName
exec spClearTables @dbName
--
declare @sc nvarchar(333)
set @sc = 'use '+@curDB
exec sp_executeSQL @sc
--
return 0
end
go
exec spClearDB ''
go
create database QLDT_week#4_5_6_7
go
use QLDT_week#4_5_6_7
go
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
create table GIAOVIEN
(
MAGV char (5),
HOTEN nvarchar(40),
LUONG float,
PHAI nchar(3),
NGSINH datetime,
DIACHI nvarchar(100),
GVQLCM char(5),
MABM nchar(5)
PRIMARY KEY (MAGV)
)
create table GV_DT
(
MAGV char(5),
DIENTHOAI char(12),
PRIMARY KEY (MAGV, DIENTHOAI)
)
create table BOMON
(
MABM nchar(5),
TENBM nvarchar(40),
PHONG char(5),
DIENTHOAI char(12),
TRUONGBM char(5),
MAKHOA char(4),
NGAYNHANCHUC datetime,
PRIMARY KEY (MABM)
)
create table KHOA
(
MAKHOA char(4),
TENKHOA nvarchar(40),
NAMTL int,
PHONG char(5),
DIENTHOAI char(12),
TRUONGKHOA char(5),
NGAYNHANCHUC datetime,
PRIMARY KEY (MAKHOA)
)
create table DETAI
(
MADT char(3),
TENDT nvarchar(100),
CAPQL nvarchar(40),
KINHPHI float,
NGAYBD datetime,
NGAYKT datetime,
MACD nchar(4),
GVCNDT char(5),
PRIMARY KEY (MADT)
)
create table CHUDE
(
MACD nchar(4),
TENCD nvarchar(50),
PRIMARY KEY (MACD)
)
create table CONGVIEC
(
MADT char(3),
SOTT int,
TENCV nvarchar(40),
NGAYBD datetime,
NGAYKT datetime,
PRIMARY KEY (MADT, SOTT)
)
create table THAMGIADT
(
MAGV char(5),
MADT char(3),
STT int,
PHUCAP float ,
KETQUA nvarchar(40),
PRIMARY KEY (MAGV, MADT, STT)
)
create table NGUOITHAN
(
MAGV char(5),
TEN nvarchar(20),
NGSINH datetime,
PHAI nchar(3),
PRIMARY KEY (MAGV, TEN)
)
alter table GIAOVIEN add
constraint FK_GIAOVIEN_BOMON foreign key (MABM) references BOMON (MABM),
constraint FK_GIAOVIEN_GIAOVIEN foreign key (GVQLCM) references GIAOVIEN (MAGV)
alter table KHOA add
constraint FK_KHOA_GIAOVIEN foreign key (TRUONGKHOA) references GIAOVIEN (MAGV)
alter table BOMON add
constraint FK_BOMON_KHOA foreign key (MAKHOA) references KHOA(MAKHOA),
constraint FK_BOMON_GIAOVIEN foreign key (TRUONGBM) references GIAOVIEN (MAGV)
alter table NGUOITHAN add
constraint FK_NGUOITHAN_GIAOVIEN foreign key (MAGV)references GIAOVIEN (MAGV)
alter table THAMGIADT add
constraint FK_PHANCONG_GIAOVIEN foreign key (MAGV)references GIAOVIEN (MAGV),
constraint FK_PHANCONG_CONGVIEC foreign key (MADT, STT)references CONGVIEC(MADT, SOTT)
alter table DETAI add
constraint FK_DETAI_CHUDE foreign key (MACD)references CHUDE (MACD)
alter table DETAI add
constraint FK_DETAI_GIAOVIEN foreign key (GVCNDT)references GIAOVIEN (MAGV)
alter table GV_DT add
constraint FK_DIENTHOAI_GIAOVIEN foreign key (MAGV)references GIAOVIEN (MAGV)
alter table CONGVIEC add
constraint FK_CONGVIEC_DETAI foreign key (MADT)references DETAI (MADT)
----------------
insert into KHOA values ('CNTT',N'Công nghệ thông tin',1995,'B11','0838123456',null,'02/20/2005')
insert into KHOA values ('VL',N'Vật lý',1976,'B21','0838223223',null,'09/18/2003')
insert into KHOA values ('SH',N'Sinh học',1980,'B31','0838454545',null,'10/11/2000')
insert into KHOA values ('HH',N'Hóa học',1980,'B41','0838456456',null,'10/15/2001')
----------------
insert into BOMON values (N'HTTT',N'Hệ thống thông tin','B13','0838125125',null,'CNTT','09/20/2004')
insert into BOMON values (N'CNTT',N'Công nghệ tri thức','B15','0838126126',null, 'CNTT', null)
insert into BOMON values (N'MMT',N'Mạng máy tính','B16','0838676767 ',null,'CNTT','05/15/2005')
insert into BOMON values (N'VLĐT',N'Vật lý điện tử','B23','0838234234',null, 'VL', null)
insert into BOMON values (N'VLƯD',N'Vật lý ứng dụng','B24','0838454545',null,'VL','02/18/2006')
insert into BOMON values (N'VS',N'Vi sinh','B32','0838909090',null,'SH','01/01/2007')
insert into BOMON values (N'SH',N'Sinh hóa','B33','0838898989',null, 'SH', null)
insert into BOMON values (N'HL',N'Hóa lý','B42','0838878787',null, 'HH', null)
insert into BOMON values (N'HPT',N'Hóa phân tích','B43','0838777777',null,'HH','10/15/2007')
insert into BOMON values (N'HHC',N'Hóa hữu cơ','B44','838222222',null, 'HH', null)
----------------
insert into GIAOVIEN values ('001',N'Nguyễn Hoài An',2000,N'Nam','02/15/1973',N'25/3 Lạc Long Quân, Q.10, TP HCM', null, N'MMT')
insert into GIAOVIEN values ('002',N'Trần Trà Hương',2500,N'Nữ','06/20/1960',N'125 Trần Hưng Đạo, Q.1,TP HCM', null, N'HTTT')
insert into GIAOVIEN values ('003',N'Nguyễn Ngọc Ánh',2200,N'Nữ','05/11/1975',N'12/21 Võ Văn Ngân Thủ Đức, TP HCM', '002',N'HTTT')
insert into GIAOVIEN values ('004',N'Trương Nam Sơn',2300,N'Nam','06/20/1959',N'215 Lý Thường Kiệt,TP Biên Hòa',null, N'VS')
insert into GIAOVIEN values ('005',N'Lý Hoàng Hà',2500,N'Nam','10/23/1954',N'22/5 Nguyễn Xí, Q.Bình Thạnh, TP HCM',null, N'VLĐT')
insert into GIAOVIEN values ('006',N'Trần Bạch Tuyết',1500,N'Nữ','05/20/1980',N'127 Hùng Vương, TP Mỹ Tho','004',N'VS')
insert into GIAOVIEN values ('007',N'Nguyễn An Trung',2100,N'Nam','06/05/1976',N'234 3/2, TP Biên Hòa',null, N'HPT')
insert into GIAOVIEN values ('008',N'Trần Trung Hiếu',1800,N'Nam','08/06/1977',N'22/11 Lý Thường Kiệt, TP Mỹ Tho','007',N'HPT')
insert into GIAOVIEN values ('009',N'Trần Hoàng Nam',2000,N'Nam','11/22/1975',N'234 Trấn Não, An Phú,TP HCM','001',N'MMT')
insert into GIAOVIEN values ('010',N'Phạm Nam Thanh',1500,N'Nam','12/12/1980',N'221 Hùng Vương, Q.5, TP HCM','007',N'HPT')
----------------
insert into GV_DT values ('001','0903123123')
insert into GV_DT values ('001','0838912112')
insert into GV_DT values ('002','0913454545')
insert into GV_DT values ('003','0903656565')
insert into GV_DT values ('003','0838121212')
insert into GV_DT values ('003','0937125125')
insert into GV_DT values ('006','0937888888')
insert into GV_DT values ('008','0913232323')
insert into GV_DT values ('008','0653717171')
----------------
insert into CHUDE values (N'QLGD',N'Quản lý giáo dục')
insert into CHUDE values (N'NCPT',N'Nghiên cứu phát triển')
insert into CHUDE values (N'ƯDCN',N'Ứng dụng công nghệ')
----------------
insert into DETAI (MADT, TENDT, KINHPHI, CAPQL, NGAYBD, NGAYKT, MACD, GVCNDT) values ('001',N'HTTT quản lý các trường ĐH',20,N'ĐHQG','10/20/2007','10/20/2008',N'QLGD','002')
insert into DETAI (MADT, TENDT, KINHPHI, CAPQL, NGAYBD, NGAYKT, MACD, GVCNDT) values ('002',N'HTTT quản lý giáo vụ cho một Khoa','20',N'Trường','10/12/2000','10/12/2001',N'QLGD','002')
insert into DETAI (MADT, TENDT, KINHPHI, CAPQL, NGAYBD, NGAYKT, MACD, GVCNDT) values ('003',N'Nghiên cứu chế tạo sợi Nanô Platin','300',N'ĐHQG','05/15/2008','05/15/2010',N'NCPT','005')
insert into DETAI (MADT, TENDT, KINHPHI, CAPQL, NGAYBD, NGAYKT, MACD, GVCNDT) values ('004',N'Tạo vật liệu sinh học bằng màng ối người','100',N'Nhà nước','01/01/2007','12/31/2009',N'NCPT','004')
insert into DETAI (MADT, TENDT, KINHPHI, CAPQL, NGAYBD, NGAYKT, MACD, GVCNDT) values ('005',N'Ứng dụng hóa học xanh','200',N'Trường','10/10/2003','12/10/2004',N'ƯDCN','007')
insert into DETAI (MADT, TENDT, KINHPHI, CAPQL, NGAYBD, NGAYKT, MACD, GVCNDT) values ('006',N'Nghiên cứu tế bào gốc','4000',N'Nhà nước','10/20/2006','10/20/2009',N'NCPT','004')
insert into DETAI (MADT, TENDT, KINHPHI, CAPQL, NGAYBD, NGAYKT, MACD, GVCNDT) values ('007',N'HTTT quản lý thư viện ở các trường ĐH','20',N'Trường','5/10/2009','05/10/2010',N'QLGD','001')
----------------
set dateformat dmy
insert into CONGVIEC values ('001',1,N'Khởi tạo và Lập kế hoạch','20/10/2007','20/12/2008')
insert into CONGVIEC values ('001',2,N'Xác định yêu cầu','21/12/2008','21/03/2008')
insert into CONGVIEC values ('001',3,N'Phân tích hệ thống','22/03/2008','22/5/2008')
insert into CONGVIEC values ('001',4,N'Thiết kế hệ thống','23/05/2008','23/06/2008')
insert into CONGVIEC values ('001',5,N'Cài đặt thử nghiệm','24/06/2008','20/10/2008')
insert into CONGVIEC values ('006',1,N'Lấy mẫu','20/10/2006','20/02/2007')
insert into CONGVIEC values ('006',2,N'Nuôi cấy','21/02/2007','21/08/2008')
insert into CONGVIEC values ('002',1,N'Khởi tạo và Lập kế hoạch','10/05/2009','10/07/2009')
insert into CONGVIEC values ('002',2,N'Xác định yêu cầu','11/07/2009','11/10/2009')
insert into CONGVIEC values ('002',3,N'Phân tích hệ thống','12/10/2009','20/12/2009')
insert into CONGVIEC values ('002',4,N'Thiết kế hệ thống','21/12/2009','22/03/2010')
insert into CONGVIEC values ('002',5,N'Cài đặt thử nghiệm','23/03/2010','10/05/2010')
set dateformat mdy
----------------
insert into THAMGIADT values ('003','001',1,1,N'Đạt')
insert into THAMGIADT values ('003','001',2,0,N'Đạt')
insert into THAMGIADT values ('002','001',4,2,N'Đạt')
insert into THAMGIADT values ('003','001',4,1,N'Đạt')
insert into THAMGIADT values ('004','006',1,0,N'Đạt')
insert into THAMGIADT values ('004','006',2,1,N'Đạt')
insert into THAMGIADT values ('006','006',2,1.5,N'Đạt')
insert into THAMGIADT values ('001','002',1,0, null)
insert into THAMGIADT values ('001','002',2,2, null)
insert into THAMGIADT values ('003','002',2,0, null)
insert into THAMGIADT values ('009','002',3,0.5, null)
insert into THAMGIADT values ('009','002',4,1.5, null)
----------------
update KHOA set TRUONGKHOA = '002' where MAKHOA='CNTT'
update KHOA set TRUONGKHOA = '005' where MAKHOA='VL'
update KHOA set TRUONGKHOA = '004' where MAKHOA='SH'
update KHOA set TRUONGKHOA = '007' where MAKHOA='HH'
----------------
update BOMON set TRUONGBM = '002' where MABM=N'HTTT'
update BOMON set TRUONGBM = '001' where MABM=N'MMT'
update BOMON set TRUONGBM = '005' where MABM=N'VLƯD'
update BOMON set TRUONGBM = '004' where MABM=N'VS'
update BOMON set TRUONGBM = '007' where MABM=N'HPT'
----------------
insert into NGUOITHAN values ('001', N'Hùng', '1/14/1990', N'Nam')
insert into NGUOITHAN values ('001', N'Thủy', '12/8/1994', N'Nữ')
insert into NGUOITHAN values ('003', N'Thu', '9/3/1998', N'Nữ')
insert into NGUOITHAN values ('003', N'Hà', '9/3/1998', N'Nữ')
insert into NGUOITHAN values ('008', N'Nam', '5/6/1991', N'Nam')
insert into NGUOITHAN values ('010', N'Nguyệt', '1/14/2006', N'Nữ')
insert into NGUOITHAN values ('007', N'Vy', '2/14/2000', N'Nữ')
insert into NGUOITHAN values ('007', N'Mai', '3/26/2003', N'Nữ')
insert into NGUOITHAN values ('009', N'An', '8/19/1996', N'Nam')
select * FROM dbo.BOMON
select * FROM dbo.CHUDE
select * FROM dbo.CONGVIEC
select * FROM dbo.DETAI
select * FROM dbo.GIAOVIEN
select * FROM dbo.GV_DT
select * FROM dbo.KHOA
select * FROM dbo.NGUOITHAN
select * FROM dbo.THAMGIADT
-------------------------------
--------Truy vấn sử dụng-------
----hàm kết hợp và gom nhóm----
-------------------------------
-- các toán tử IN, NOT IN, ALL,ANY,SOME
-- các toán tử EXIST, NOT EXIST
-- CHO BIẾT GV tham gia nhiều công việc nhất
SELECT DT.MAGV, COUNT(*)
FROM THAMGIADT DT
GROUP BY DT.MAGV
having COUNT(*) >= ALL(SELECT COUNT(*)
FROM THAMGIADT TGDT
GROUP BY TGDT.MAGV)
-- Cho biết GV tham gia nhiều công việc nhất trong bộ môn của GV đó
SELECT TGDT.MAGV, COUNT(*) as N'SL Công việc'
FROM THAMGIADT TGDT JOIN GIAOVIEN GV
ON TGDT.MAGV = GV.MAGV
GROUP BY TGDT.MAGV, GV.MABM
having COUNT(*) >= ALL(SELECT COUNT(*)
FROM THAMGIADT TGDT2 JOIN GIAOVIEN GV2
ON TGDT2.MAGV = GV2.MAGV
WHERE GV2.MABM = GV.MABM
GROUP BY TGDT2.MAGV
--HAVING GV2.MABM = GV.MABM
)
-- câu 35: mức lương cao nhất của các giáo viên
SELECT MAX(GV.LUONG) AS 'Mức lương cao nhất của các giáo viên'
FROM GIAOVIEN GV
-- câu 37: lương cao nhất trong bộ môn HTTT
SELECT DISTINCT GV.LUONG as 'Lương cao nhất trong bộ môn HTTT'
FROM GIAOVIEN GV
where GV.LUONG = (SELECT MAX(GV2.LUONG)
FROM GIAOVIEN GV2)
-- câu 39: tên GV nhỏ tuổi nhất khoa Công nghệ thông tin
SELECT GV.HOTEN AS 'Tên GV nhỏ tuổi nhất khoa Công nghệ thông tin'
FROM GIAOVIEN GV JOIN BOMON BM
ON GV.MABM = BM.MABM -- tạo LK GiaoVien với BOMON
JOIN KHOA K
ON BM.MAKHOA = K.MAKHOA -- tạo LK BOMON với KHOA
WHERE GV.NGSINH >= ALL(SELECT NGSINH
FROM BOMON BM2 JOIN KHOA K2
ON BM2.MAKHOA = K2.MAKHOA -- tạo LK BOMON2 với KHOA2
JOIN GIAOVIEN GV2
ON GV2.MABM = BM2.MABM -- tạo LK GiaoVien2 với BOMON2
WHERE K2.TENKHOA = N'Công nghệ thông tin')
AND K.TENKHOA = N'Công nghệ thông tin'
-- câu 41: những GV có lương lớn nhất trong Bộ môn của họ
SELECT GV1.MAGV as N'Mã giáo viên có lương lớn nhất trong Bộ môn của họ', GV1.MABM as N'Mã BM', GV1.HOTEN as N'Họ và tên', GV1.LUONG as N'Lương'
FROM GIAOVIEN GV1
GROUP BY GV1.MAGV, GV1.MABM, GV1.HOTEN, GV1.LUONG
HAVING GV1.LUONG >= ALL(SELECT GV2.LUONG
FROM GIAOVIEN GV2
GROUP BY GV2.LUONG,GV2.MABM -- có sử dụng Luong và Mã BM để so sánh 2 bảng GIAOVIEN với nhau
HAVING GV1.MABM = GV2.MABM) -- tạo LK GiaoVien1 với GiaoVien2
-- câu 43: những đề tài mà giáo viên Nguyễn Hoài An chưa tham gia
-- Xuất ra tên đề tài, tên người chủ nhiệm đề tài
SELECT DISTINCT DT.TENDT, GV.HOTEN
FROM DETAI DT JOIN GIAOVIEN GV ON DT.GVCNDT = GV.MAGV
where DT.TENDT NOT IN (SELECT distinct DT2.TENDT
FROM THAMGIADT TGDT2 join GIAOVIEN GV2
ON TGDT2.MAGV = GV2.MAGV
JOIN DETAI DT2
ON DT2.MADT = TGDT2.MADT
WHERE GV2.HOTEN = N'Nguyễn Hoài An'
)
/*SELECT DISTINCT DT.TENDT, GV.HOTEN
FROM DETAI DT JOIN GIAOVIEN GV ON DT.GVCNDT = GV.MAGV
EXCEPT
SELECT DISTINCT DT2.TENDT, GV2.HOTEN
FROM THAMGIADT TGDT2 join GIAOVIEN GV2
ON TGDT2.MAGV = GV2.MAGV
JOIN DETAI DT2
ON DT2.MADT = TGDT2.MADT
WHERE GV2.HOTEN = N'Nguyễn Hoài An'*/
-- câu 45: những GV ko tham gia bất kì đề tài nào
SELECT*
FROM GIAOVIEN GV
WHERE GV.MAGV NOT IN (SELECT TGDT.MAGV
FROM THAMGIADT TGDT)
-- câu 47: Những trưởng BM
-- tham gia tối thiểu 1 đề tài
SELECT BM.TRUONGBM AS N'Mã trưởng Bộ môn tham gia tối thiểu 1 đề tài', COUNT(*) AS 'SL DETAI'
FROM THAMGIADT TGDT JOIN BOMON BM
ON TGDT.MAGV = BM.TRUONGBM
GROUP BY BM.TRUONGBM
HAVING COUNT(TGDT.MADT) >= 1
-- câu 49: tìm những GV có lương > lương của ít nhất 1 GV thuộc Bộ môn Công nghệ phần mềm
SELECT GV2.MAGV as N'những GV có lương lớn hơn lương của ít nhất 1 GV thuộc Bộ môn Công nghệ phần mềm'
FROM GIAOVIEN GV2
WHERE EXISTS(SELECT DISTINCT GV.LUONG
FROM KHOA JOIN BOMON BM
ON KHOA.MAKHOA = BM.MAKHOA
JOIN GIAOVIEN GV
ON GV.MABM = BM.MABM
WHERE BM.TENBM = N'Công nghệ phần mềm'--N'Hệ thống thông tin'
AND GV2.LUONG > GV.LUONG
)
-- câu 51: tên khoa có đông GV nhất
SELECT KHOA.MAKHOA, KHOA.TENKHOA as 'Tên khoa có đông GV nhất'--GV.MABM
FROM KHOA JOIN BOMON BM
ON KHOA.MAKHOA = BM.MAKHOA -- tạo LK KHOA với BOMON
JOIN GIAOVIEN GV
ON GV.MABM = BM.MABM -- tạo LK GIAOVIEN với BOMON
GROUP BY KHOA.MAKHOA, KHOA.TENKHOA--GV.MABM
HAVING COUNT(*) >= ALL( SELECT COUNT(*)
FROM KHOA K2 JOIN BOMON BM2
ON K2.MAKHOA = BM2.MAKHOA -- tạo LK KHOA2 với BOMON2
JOIN GIAOVIEN GV2
ON GV2.MABM = BM2.MABM -- tạo LK GIAOVIEN2 với BOMON2
GROUP BY K2.MAKHOA, K2.TENKHOA--GV2.MABM)
)
-- câu 53: mã BM có nhiều GV nhất
SELECT GV.MABM AS N'mã BM có nhiều GV nhất'
FROM GIAOVIEN GV
GROUP BY GV.MABM
HAVING COUNT(*) >= ALL( SELECT COUNT(*)
FROM GIAOVIEN GV2
GROUP BY GV2.MABM
)
-- câu 55: tên GV
-- tham gia nhiều đề tài nhất
-- của bộ môn HTTT
SELECT GV.HOTEN AS N'Họ và tên GV tham gia nhiều đề tài nhất của bộ môn HTTT', COUNT(TGDT.MAGV) AS N'Số lượng đề tài'
FROM GIAOVIEN GV JOIN THAMGIADT TGDT
ON TGDT.MAGV = GV.MAGV
AND GV.MABM = 'HTTT'
GROUP BY GV.HOTEN
HAVING COUNT(TGDT.MAGV) >= ALL (SELECT COUNT(TGDT2.MAGV)
FROM THAMGIADT TGDT2
GROUP BY TGDT2.MAGV
)
-- câu 57: tên trưởng bộ môn
-- mà chủ nhiệm nhiều đề tài nhất
SELECT DISTINCT GV.HOTEN as N'Tên trưởng bộ môn', COUNT(DT.MADT) as N'Số lượng đề tài chủ nhiệm nhiều nhất'
FROM GIAOVIEN GV, BOMON BM, DETAI DT
WHERE BM.TRUONGBM = GV.MAGV -- là trưởng bộ môn
AND DT.GVCNDT = GV.MAGV -- là GV chủ nhiệm đề tài
GROUP BY GV.HOTEN
HAVING COUNT(DT.MADT) >= ALL (SELECT COUNT(DT2.MADT)
FROM GIAOVIEN GV2, DETAI DT2
WHERE GV2.MAGV = DT2.GVCNDT
GROUP BY GV2.MAGV
)
-------------------------------
--------Bài tập về nhà-------
----hàm kết hợp và gom nhóm----
-------------------------------
-- câu 36: các giáo viên có mức lương cao nhất
SELECT GV.MAGV AS 'Những giáo viên có mức lương cao nhất'
FROM GIAOVIEN GV
where GV.LUONG = (SELECT MAX(GV2.LUONG)
FROM GIAOVIEN GV2)
-- câu 38: tên GV lớn tuổi nhất của Bộ môn Hệ thống thông tin
SELECT GV.HOTEN AS 'Tên GV lớn tuổi nhất của Bộ môn Hệ thống thông tin'
FROM GIAOVIEN GV JOIN BOMON BM
ON GV.MABM = BM.MABM -- tạo LK GiaoVien với BOMON
WHERE GV.NGSINH <= ALL(SELECT NGSINH
FROM BOMON BM2 JOIN GIAOVIEN GV2
ON GV2.MABM = BM2.MABM -- tạo LK GiaoVien2 với BOMON2
WHERE BM2.TENBM = N'Hệ thống thông tin')
AND BM.TENBM = N'Hệ thống thông tin'
-- câu 40: tên GV và tên Khoa của GV có lương cao nhất
SELECT GV.HOTEN as N'Họ và tên giáo viên', K.TENKHOA as N'Tên Khoa của GV có lương cao nhất'--, GV.LUONG
FROM GIAOVIEN GV, KHOA K JOIN BOMON BM ON K.MAKHOA = BM.MAKHOA -- tạo LK Khoa với BOMON
WHERE GV.MABM = BM.MABM -- tạo LK GiaoVien với BOMON
AND GV.LUONG >= ALL(SELECT GV2.LUONG
FROM GIAOVIEN GV2
)
-- câu 42: tên những đề tài
-- mà GV Nguyễn Hoài An chưa tham gia
SELECT DISTINCT DT.TENDT AS N'Tên những đề tài mà GV Nguyễn Hoài An chưa tham gia'
FROM DETAI DT
where DT.TENDT NOT IN (SELECT distinct DT2.TENDT
FROM THAMGIADT TGDT2 join GIAOVIEN GV2
ON TGDT2.MAGV = GV2.MAGV -- tạo LK THAMGIADT với GIAOVIEN
JOIN DETAI DT2
ON DT2.MADT = TGDT2.MADT -- tạo LK DeTai với THAMGIADT
WHERE GV2.HOTEN = N'Nguyễn Hoài An'
)
-- Table toàn bộ tên đề tài - Table đề tài mà GV Nguyễn Hoài An tham gia = Kết quả
-- câu 44: tên những GV khoa Công nghệ thông tin
-- mà chưa tham gia đề tài nào
SELECT GV.HOTEN as N'Tên những GV khoa Công nghệ thông tin mà chưa tham gia đề tài nào'
FROM GIAOVIEN GV JOIN BOMON BM
ON GV.MABM = BM.MABM
JOIN KHOA
ON KHOA.MAKHOA = BM.MAKHOA
AND KHOA.TENKHOA = N'Công nghệ thông tin'
WHERE GV.MAGV NOT IN (SELECT TGDT.MAGV
FROM THAMGIADT TGDT)
-- câu 46: GV có lương > lương của GV "Nguyễn Hoài An"
SELECT*
FROM GIAOVIEN GV
WHERE GV.LUONG > (SELECT GV2.LUONG
FROM GIAOVIEN GV2
WHERE GV2.HOTEN = N'Nguyễn Hoài An'
)
-- TABLE toàn bộ giáo viên (lương) - Table GV Nguyễn Hoài An (lương) = Kết quả
-- câu 48: những GV trùng tên
-- và cùng giới tính
-- với GV khác trong cùng 1 bộ môn
SELECT *
FROM GIAOVIEN GV
WHERE EXISTS (SELECT*
FROM GIAOVIEN GV2
WHERE GV.HOTEN = GV2.HOTEN -- trùng tên
AND GV.PHAI = GV2.PHAI -- cùng giới tính
AND GV.MABM = GV2.MABM -- cùng 1 bộ môn
AND GV.MAGV != GV2.MAGV -- để phân biệt 2 bảng GIAOVIEN với nhau
)
-- câu 50: những GV có lương > lương của tất cả GV
-- thuộc bộ môn "Hệ thống thông tin"
SELECT *
FROM GIAOVIEN GV
WHERE GV.LUONG > ALL(SELECT GV2.LUONG
FROM GIAOVIEN GV2 JOIN BOMON BM ON BM.MABM = GV2.MABM
WHERE BM.TENBM = N'Hệ thống thông tin'
)
-- Table Lương toàn bộ GV > ALL (Table toàn bộ GV thuộc bộ môn "Hệ thống thông tin) = Kết quả
-- câu 52: Cho biết tên GV chủ nhiệm nhiều đề tài nhất
SELECT GV.HOTEN AS N'tên GV chủ nhiệm nhiều đề tài nhất'
FROM GIAOVIEN GV JOIN DETAI DT
ON GV.MAGV = DT.GVCNDT
GROUP BY GV.HOTEN
HAVING COUNT(DT.GVCNDT) >= ALL (SELECT COUNT(DT2.GVCNDT)
FROM DETAI DT2 JOIN GIAOVIEN GV2 ON GV2.MAGV = DT2.GVCNDT
GROUP BY GV2.MAGV
)
-- câu 54: Cho biết tên GV và tên bộ môn của GV tham gia nhiều đề tài nhất
SELECT GV.HOTEN AS N'Tên GV', BM.TENBM AS N'Tên bộ môn của GV tham gia nhiều đề tài nhất'
FROM GIAOVIEN GV JOIN BOMON BM ON GV.MABM = BM.MABM
JOIN THAMGIADT TGDT
ON TGDT.MAGV = GV.MAGV
GROUP BY GV.HOTEN, BM.TENBM
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM GIAOVIEN GV2 JOIN THAMGIADT TGDT2
ON TGDT2.MAGV = GV2.MAGV
GROUP BY TGDT2.MAGV
)
-- câu 56: Cho biết tên GV và tên bộ môn của GV có nhiều người thân nhất
SELECT GV.HOTEN AS N'Tên GV', BM.TENBM AS N'Tên bộ môn của GV có nhiều người thân nhất'
FROM GIAOVIEN GV JOIN BOMON BM ON GV.MABM = BM.MABM
join NGUOITHAN NT ON NT.MAGV = GV.MAGV
GROUP BY GV.HOTEN, BM.TENBM
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM GIAOVIEN GV2 JOIN NGUOITHAN NT2 ON NT2.MAGV = GV2.MAGV
GROUP BY GV2.HOTEN
)
--------------------------------
-----Truy vấn lồng nâng cao-----
-------------BTTL#7-------------
SELECT DISTINCT TG1.MAGV
FROM THAMGIADT TG1
WHERE NOT EXISTS (
(SELECT MADT FROM DETAI)
EXCEPT
(SELECT MADT
FROM THAMGIADT TG2
WHERE TG2.MAGV = TG1.MAGV
)
)
------
-- 9: Tìm tên các giáo viên ‘HTTT’
-- mà tham gia tất cả các đề tài thuộc chủ đề 'QLGD'
-- EXCEPT
--Select GV.MAGV, GV.HOTEN
--FROM THAMGIADT TG JOIN GIAOVIEN GV ON GV.MAGV = TG.MAGV
--WHERE NOT EXISTS (Select DT1.MADT
-- FROM DETAI DT1
-- EXCEPT
-- SELECT TG2.MADT
-- FROM THAMGIADT TG2
-- WHERE TG2.MAGV = GV.MAGV
--)
--SELECT DT.MADT, DT.TENDT
--FROM DETAI DT
--WHERE NOT EXISTS (SELECT GV1.MAGV
-- FROM GIAOVIEN GV1
-- EXCEPT
-- SELECT TG2.MAGV
-- FROM THAMGIADT TG2
-- WHERE TG2.MADT = DT.MADT
--)
--select * FROM CONGVIEC CV
--select GV.MAGV, GV.HOTEN
--FROM GIAOVIEN GV
--WHERE NOT EXISTS (SELECT DISTINCT CV1.MADT, CV1.SOTT
-- FROM CONGVIEC CV1
-- EXCEPT
-- SELECT DISTINCT TG2.MADT, TG2.STT
-- FROM THAMGIADT TG2
-- WHERE TG2.MAGV = GV.MAGV
--)
SELECT DISTINCT TG1.MAGV,GV.HOTEN
FROM THAMGIADT TG1,GIAOVIEN GV
WHERE TG1.MAGV = GV.MAGV
AND GV.MABM = N'HTTT'
AND NOT EXISTS (
(SELECT MADT FROM DETAI WHERE MACD = N'QLGD')
EXCEPT
(SELECT MADT
FROM THAMGIADT TG2
WHERE TG2.MAGV=TG1.MAGV )
)
-- NOT EXISTS
SELECT TG1.MAGV,GV.HOTEN
FROM THAMGIADT TG1, GIAOVIEN GV
WHERE TG1.MAGV = GV.MAGV
AND GV.MABM= N'HTTT'
AND NOT EXISTS (
SELECT *
FROM DETAI DT
WHERE MACD= N'QLGD' AND NOT EXISTS (
SELECT *
FROM THAMGIADT TG2
WHERE TG2.MADT=DT.MADT AND TG2.MAGV=TG1.MAGV
)
)
-- COUNT
SELECT TG1.MAGV , GV.HOTEN
FROM THAMGIADT TG1 , GIAOVIEN GV
WHERE TG1.MAGV = GV.MAGV
AND GV.MABM = N'HTTT'
AND TG1.MADT IN (SELECT MADT FROM DETAI WHERE MACD = N'QLGD')
GROUP BY TG1.MAGV , GV.HOTEN
HAVING COUNT(DISTINCT TG1.MADT) =
(
SELECT COUNT(MADT)
FROM DETAI
WHERE MACD= N'QLGD'
)
-- Q58. Cho biết tên giáo viên nào
-- mà tham gia đề tài đủ tất cả các chủ đề.
-- (...) EXCEPT (...)
SELECT DISTINCT GV.HOTEN
FROM THAMGIADT TG1 join GIAOVIEN GV ON GV.MAGV = TG1.MAGV
WHERE NOT EXISTS (
(SELECT CD.MACD FROM CHUDE CD)
EXCEPT
(SELECT CD2.MACD
FROM CHUDE CD2 JOIN DETAI DT ON DT.MACD = CD2.MACD
JOIN THAMGIADT TG2 ON TG2.MADT = DT.MADT
JOIN GIAOVIEN GV2 ON GV2.MAGV = TG2.MAGV
WHERE TG2.MAGV = TG1.MAGV
)
)
-- WHERE NOT EXSISTS (...)
-- => tạo 1 liên kết từ bảng dưới với bảng trên, thường cho 2 bảng giống nhau sẽ dễ hơn
-- => nếu SELECT có 2 thuộc tính thì tạo 2 liên kết như trên
SELECT DISTINCT GV.HOTEN
FROM THAMGIADT TG1 join GIAOVIEN GV ON GV.MAGV = TG1.MAGV
WHERE NOT EXISTS (SELECT CD.MACD FROM CHUDE CD
WHERE NOT EXISTS
(SELECT CD2.MACD
FROM CHUDE CD2 JOIN DETAI DT ON DT.MACD = CD2.MACD
JOIN THAMGIADT TG2 ON TG2.MADT = DT.MADT
JOIN GIAOVIEN GV2 ON GV2.MAGV = TG2.MAGV
WHERE TG2.MAGV = TG1.MAGV AND CD.MACD = CD2.MACD
)
)
-- WHERE ... NOT IN (...)
-- => lấy giá trị sau Select làm thành phần ... NOT IN
SELECT DISTINCT GV.HOTEN
FROM THAMGIADT TG1 JOIN GIAOVIEN GV ON GV.MAGV = TG1.MAGV
WHERE NOT EXISTS (
SELECT CD.MACD FROM CHUDE CD
WHERE CD.MACD NOT IN
(
SELECT CD2.MACD
FROM CHUDE CD2 JOIN DETAI DT ON DT.MACD = CD2.MACD
JOIN THAMGIADT TG2 ON TG2.MADT = DT.MADT
JOIN GIAOVIEN GV2 ON GV2.MAGV = TG2.MAGV
WHERE TG2.MAGV = TG1.MAGV
)
)
-- câu 60: Cho biết tên đề tài
-- có tất cả giảng viên bộ môn “Hệ thống thông tin”
-- tham gia
-- EXCEPT
SELECT DT.TENDT AS N'Tên đề tài có tất cả giảng viên bộ môn “Hệ thống thông tin tham gia'
FROM DETAI DT
WHERE NOT EXISTS (
SELECT GV.MAGV
FROM GIAOVIEN GV JOIN BOMON BM ON BM.MABM = GV.MABM
WHERE BM.TENBM = N'Hệ thống thông tin'
EXCEPT
SELECT GV2.MAGV
FROM GIAOVIEN GV2 JOIN THAMGIADT TG2 ON TG2.MAGV = GV2.MAGV
JOIN BOMON BM2 ON BM2.MABM = GV2.MABM
WHERE TG2.MADT = DT.MADT
)
-- WHERE NOT EXSISTS (...)
-- => tạo 1 liên kết từ bảng dưới với bảng trên, thường cho 2 bảng giống nhau sẽ dễ hơn
-- => nếu SELECT có 2 thuộc tính thì tạo 2 liên kết như trên
SELECT DT.TENDT AS N'Tên đề tài có tất cả giảng viên bộ môn “Hệ thống thông tin tham gia'
FROM DETAI DT
WHERE NOT EXISTS (
SELECT GV.MAGV
FROM GIAOVIEN GV JOIN BOMON BM ON BM.MABM = GV.MABM AND BM.TENBM = N'Hệ thống thông tin'
WHERE NOT EXISTS
(
SELECT GV2.MAGV
FROM GIAOVIEN GV2 JOIN THAMGIADT TG2 ON TG2.MAGV = GV2.MAGV
JOIN BOMON BM2 ON BM2.MABM = GV2.MABM
WHERE TG2.MADT = DT.MADT
AND GV.MAGV = GV2.MAGV -- LIEN KET CHO 2 BANG VOI NHAU
)
)
-- WHERE ... NOT IN (...)
-- => lấy giá trị sau Select làm thành phần ... NOT IN
SELECT DT.TENDT AS N'Tên đề tài có tất cả giảng viên bộ môn “Hệ thống thông tin tham gia'
FROM DETAI DT
WHERE NOT EXISTS (
SELECT GV.MAGV
FROM GIAOVIEN GV JOIN BOMON BM ON BM.MABM = GV.MABM
AND BM.TENBM = N'Hệ thống thông tin'
WHERE GV.MAGV NOT IN (
SELECT GV2.MAGV
FROM GIAOVIEN GV2 JOIN THAMGIADT TG2 ON TG2.MAGV = GV2.MAGV
JOIN BOMON BM2 ON BM2.MABM = GV2.MABM
WHERE TG2.MADT = DT.MADT
)
)
-- câu 62:Cho biết tên giáo viên
-- nào tham gia
-- tất cả các đề tài
-- mà giáo viên Trần Trà Hương đã tham gia.
SELECT GV.HOTEN
FROM GIAOVIEN GV
WHERE NOT EXISTS (SELECT DISTINCT TG.MADT
FROM THAMGIADT TG JOIN GIAOVIEN GV1 ON GV1.MAGV = TG.MAGV
WHERE GV1.HOTEN = N'Trần Trà Hương'
EXCEPT
SELECT DISTINCT TG2.MADT
FROM THAMGIADT TG2
WHERE TG2.MAGV = GV.MAGV
)
AND GV.HOTEN != N'Trần Trà Hương'
-- WHERE NOT EXSISTS (...)
-- => tạo 1 liên kết từ bảng dưới với bảng trên, thường cho 2 bảng giống nhau sẽ dễ hơn
-- => nếu SELECT có 2 thuộc tính thì tạo 2 liên kết như trên
SELECT GV.HOTEN
FROM GIAOVIEN GV
WHERE NOT EXISTS (SELECT DISTINCT TG.MADT
FROM THAMGIADT TG JOIN GIAOVIEN GV1 ON GV1.MAGV = TG.MAGV
WHERE GV1.HOTEN = N'Trần Trà Hương'
AND NOT EXISTS (SELECT DISTINCT TG2.MADT
FROM THAMGIADT TG2
WHERE TG2.MAGV = GV.MAGV
AND TG.MADT = TG2.MADT
)
)
AND GV.HOTEN != N'Trần Trà Hương'
-- WHERE ... NOT IN (...)
-- => lấy giá trị sau Select làm thành phần ... NOT IN
SELECT GV.HOTEN
FROM GIAOVIEN GV
WHERE NOT EXISTS (SELECT DISTINCT TG1.MADT
FROM THAMGIADT TG1 JOIN GIAOVIEN GV1 ON GV1.MAGV = TG1.MAGV
WHERE GV1.HOTEN = N'Trần Trà Hương'
AND TG1.MADT NOT IN (SELECT DISTINCT TG2.MADT
FROM THAMGIADT TG2
WHERE TG2.MAGV = GV.MAGV
)
)
AND GV.HOTEN != N'Trần Trà Hương'
-- câu 64: Cho biết tên giáo viên
-- nào mà tham gia
-- tất cả các công việc
-- của đề tài 006.
-- EXCEPT
SELECT GV.HOTEN
FROM GIAOVIEN GV
WHERE NOT EXISTS (SELECT CV.SOTT, CV.MADT
FROM CONGVIEC CV
WHERE CV.MADT = '006'
EXCEPT
SELECT TG.STT, CV2.MADT
FROM CONGVIEC CV2 JOIN THAMGIADT TG ON TG.MADT = CV2.MADT
WHERE TG.MAGV = GV.MAGV
)
-- WHERE NOT EXSISTS (...)
-- => tạo 1 liên kết từ bảng dưới với bảng trên, thường cho 2 bảng giống nhau sẽ dễ hơn
-- => nếu SELECT có 2 thuộc tính thì tạo 2 liên kết như trên
SELECT GV.HOTEN
FROM GIAOVIEN GV
WHERE NOT EXISTS (SELECT CV.SOTT, CV.MADT
FROM CONGVIEC CV
WHERE CV.MADT = '006'
AND NOT EXISTS (SELECT TG.STT, CV2.MADT
FROM CONGVIEC CV2 JOIN THAMGIADT TG ON TG.MADT = CV2.MADT
WHERE TG.MAGV = GV.MAGV
AND CV.SOTT = TG.STT -- LIEN KET 1
AND CV.MADT = CV2.MADT -- LIEN KET 1
)
)
-- WHERE ... NOT IN (...)
-- => lấy giá trị sau Select làm thành phần ... NOT IN
SELECT GV.HOTEN
FROM GIAOVIEN GV
WHERE NOT EXISTS (SELECT CV.SOTT
FROM CONGVIEC CV
WHERE CV.MADT = '006'
AND CV.SOTT NOT IN (SELECT TG.STT
FROM CONGVIEC CV2 JOIN THAMGIADT TG ON TG.MADT = CV2.MADT
WHERE TG.MAGV = GV.MAGV
AND CV2.MADT = CV.MADT -- điều kiện thêm
)
)
-- câu 66: Cho biết tên giáo viên
-- nào đã tham gia
-- tất cả các đề tài
-- của do Trần Trà Hương
-- làm chủ nhiệm
-- EXCEPT
SELECT GV.HOTEN
FROM GIAOVIEN GV
WHERE NOT EXISTS (SELECT DT1.MADT
FROM DETAI DT1 JOIN GIAOVIEN GV1 ON GV1.MAGV = DT1.GVCNDT
WHERE GV1.HOTEN = N'Trần Trà Hương'
EXCEPT
SELECT TG2.MADT
FROM THAMGIADT TG2
WHERE TG2.MAGV = GV.MAGV
)
-- WHERE NOT EXSISTS (...)
-- => tạo 1 liên kết từ bảng dưới với bảng trên, thường cho 2 bảng giống nhau sẽ dễ hơn
-- => nếu SELECT có 2 thuộc tính thì tạo 2 liên kết như trên
SELECT GV.HOTEN
FROM GIAOVIEN GV
WHERE NOT EXISTS (SELECT DT1.MADT
FROM DETAI DT1 JOIN GIAOVIEN GV1 ON GV1.MAGV = DT1.GVCNDT
WHERE GV1.HOTEN = N'Trần Trà Hương'
AND NOT EXISTS (SELECT TG2.MADT
FROM THAMGIADT TG2
WHERE TG2.MAGV = GV.MAGV