面接問題は、3枚の表、学生表S、課程表C、学生課程表SCがあり、学生は複数の課程を選択することができ、1つの課程は複数の学生に選択され、SC表を通じて関連付けられる可能性がある.(1)テーブル作成および挿入文の書き出し(2)SQL文を書き出して、すべての選択科目を選択した学生を検索します;...

 
 1 CREATE TABLE student(
 2 id INT PRIMARY KEY AUTO_INCREMENT,
 3 stuname VARCHAR(20) NOT NULL
 4 )
 5 
 6 
 7 CREATE TABLE course(
 8 id INT PRIMARY KEY AUTO_INCREMENT,
 9 couname VARCHAR(20) NOT NULL
10 ) 
11 
12 CREATE TABLE stucou
13 (sid INT REFERENCES student(id),
14 cid INT REFERENCES course(id)
15 
16 )
17 
18 INSERT INTO student VALUES(1,'zhangsan');
19 INSERT INTO student VALUES(2,'lsii');
20 INSERT INTO student VALUES(3,'wangwu');
21 INSERT INTO student VALUES(4,'zhaoliu');
22 
23 INSERT INTO course VALUES(1,'Java');
24 INSERT INTO course VALUES(2,'C++');
25 INSERT INTO course VALUES(3,'Chinese');
26 INSERT INTO course VALUES(4,'English');
27 
28 INSERT INTO stucou VALUES(1,1);
29 INSERT INTO stucou VALUES(1,2);
30 INSERT INTO stucou VALUES(1,3);
31 INSERT INTO stucou VALUES(1,4);
32 INSERT INTO stucou VALUES(2,1);
33 INSERT INTO stucou VALUES(2,2);
34 INSERT INTO stucou VALUES(2,3);
35 INSERT INTO stucou VALUES(3,1);
36 INSERT INTO stucou VALUES(3,2);
37 INSERT INTO stucou VALUES(4,3);
38 INSERT INTO stucou VALUES(4,4);
39 --            --
40 SELECT student.id,student.stuname
41 FROM student 
42 WHERE student.id IN(
43 SELECT sid
44 FROM stucou
45 GROUP BY sid
46 HAVING COUNT(*)=
47 (SELECT COUNT(*)
48 FROM course)
49 );
50 SELECT COUNT(*)
51 FROM course
52 
53 --         2   --
54 
55 SELECT student.id,student.`stuname` 
56 FROM student
57 WHERE student.id IN(
58 SELECT sid
59 FROM stucou
60 GROUP BY sid
61 HAVING COUNT(*)>2
62 )