面试题，有三张表，学生表S，课程表C，学生课程表SC，学生可以选修多门课程，一门课程可能被多个学生选修，通过SC表关联。（1）写出建表以及插入语句；（2）写出SQL语句，查询选修了所有选修课程的学生；（3）写出SQL语句，查询选修了至少2门 

 
 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 )

 

posted on 2016-04-17 23:04  guanzhenqiang  阅读(8982)  评论(1)  编辑  收藏  举报

导航