>>>>>>>>>>

1.2 要求一......用SQL语言完成表的创建以及数据的插入。

#创建student

CREATE TABLE student(

sno INT(10) COMMENT ‘学号‘,

sname VARCHAR(10) COMMENT ‘姓名‘,

age INT(3) COMMENT ‘年龄‘,

sex VARCHAR(1) COMMENT ‘性别‘)

 

#insert插入student数据

INSERT INTO student(sno,sname,age,sex) VALUES(1,‘李强‘,18,‘男‘)

INSERT INTO student(sno,sname,age,sex) VALUES(2,‘刘丽‘,188,‘女‘)

INSERT INTO student(sno,sname,age,sex) VALUES(3,‘凤凰‘,19,‘女‘)

INSERT INTO student(sno,sname,age,sex) VALUES(4,‘婕拉‘,20,‘女‘)

INSERT INTO student(sno,sname,age,sex) VALUES(5,‘张友‘,21,‘男‘)

INSERT INTO student(sno,sname,age,sex) VALUES(6,‘孙悟空‘,500,‘男‘)

 

#create创建course

CREATE TABLE course(

cno VARCHAR(10) COMMENT ‘课程代码‘,

cname VARCHAR(10) COMMENT ‘课程名称‘,

teacher VARCHAR(10) COMMENT ‘教师‘)

 

#insert插入course数据

INSERT INTO course(cno,cname,teacher) VALUES(‘K1‘,‘C语言‘,‘王华‘)

INSERT INTO course(cno,cname,teacher) VALUES(‘K2‘,‘C+语言‘,‘薛哥‘)

INSERT INTO course(cno,cname,teacher) VALUES(‘K3‘,‘C++语言‘,‘薛哥‘)

INSERT INTO course(cno,cname,teacher) VALUES(‘K4‘,‘java语言‘,‘薛哥‘)

INSERT INTO course(cno,cname,teacher) VALUES(‘K5‘,‘数据库原理‘,‘程军‘)

INSERT INTO course(cno,cname,teacher) VALUES(‘K6‘,‘linux语言‘,‘薛哥‘)

INSERT INTO course(cno,cname,teacher) VALUES(‘K7‘,‘PHP语言‘,‘薛哥‘)

INSERT INTO course(cno,cname,teacher) VALUES(‘K8‘,‘编译原理‘,‘程军‘)

 

#create创建表SC

CREATE TABLE sc(

sno INT(10) COMMENT ‘学号‘,

cno VARCHAR(10) COMMENT ‘课程代码‘,

score INT(10) COMMENT ‘成绩‘)

 

#insert插入sc数据

INSERT INTO sc(sno,cno,score) VALUES(1,‘K1‘,83)

INSERT INTO sc(sno,cno,score) VALUES(2,‘K1‘,85)

INSERT INTO sc(sno,cno,score) VALUES(5,‘K1‘,92)

INSERT INTO sc(sno,cno,score) VALUES(2,‘K5‘,90)

INSERT INTO sc(sno,cno,score) VALUES(5,‘K5‘,84)

INSERT INTO sc(sno,cno,score) VALUES(5,‘K8‘,80)

 

1.3 要求二.....用SQL语言完成如下要求：

（1） 检索至少选修＂程军＂老师所授全部课程的学生姓名（SNAME）；

SELECT sname FROM student  

SELECT cno FROM course WHERE `teacher`=‘程军‘

SELECT sno FROM sc WHERE cno IN (SELECT cno FROM course WHERE `teacher`=‘程军‘)

SELECT sname FROM student WHERE sno IN (SELECT sno FROM sc WHERE cno IN (SELECT cno FROM course WHERE `teacher`=‘程军‘))

##以上结果为错误的查询方法！正确结果如下（运用exists）：

 

 

（2） 检索＂李强＂同学不学课程的课程号(CNO)；

 

 

 

 

 

（1）检索王华老师所授课程的课程号和课程名。 

SELECT cno,cname FROM course WHERE teacher = ‘王华‘

 

（2）检索年龄大于23岁的男学生的学号和姓名。 

SELECT sno,sname FROM student WHERE age >= 23 AND sex = ‘男‘

 

（3）检索至少选修王老师所授课程中一门课程的女学生姓名。 

SELECT sname FROM student WHERE EXISTS (

SELECT * FROM course WHERE teacher = ‘王华‘ AND EXISTS (

SELECT * FROM sc WHERE sno=student.`sno` AND cno=course.`cno`)) AND sex = ‘女‘

 

（4）检索李强同学不学的课程的课程号。

SELECT cno FROM course WHERE NOT EXISTS(

SELECT * FROM student WHERE sname = ‘李强‘ AND EXISTS (

SELECT * FROM sc WHERE sno=student.`sno` AND cno=course.`cno`))

 

（5）检索至少选修两门课程的学生学号。

SELECT sno FROM sc GROUP BY sno HAVING COUNT(*) >= 2

 

（6）检索全部学生都选修的课程的课程号与课程名。

（7）检索选修课程包含王华老师所授课的学生学号。

SELECT sno FROM student WHERE EXISTS(

SELECT * FROM course WHERE teacher = ‘王华‘ AND EXISTS(

SELECT * FROM sc WHERE sc.`sno`=student.`sno` AND sc.`cno`=course.`cno`))

（8）统计所有学生选修的课程门数。

SELECT sname,sc.`sno`,COUNT(cno) FROM student,sc  WHERE  sc.`sno`=student.`sno` GROUP BY sno

（9）求选修K1课程的学生的平均年龄。

SELECT AVG(age) FROM student WHERE EXISTS (

SELECT * FROM sc WHERE cno=‘k1‘ AND sc.`sno`=student.`sno`)

（10）求王华老师所授课程的每门课程的学生平均成绩。 

 

（11）统计每门课程的学生选修人数（超过2人的课程才统计）。要求输出课程号和选修人数，查询结果 按人数降序排列，若人数相同，按课程号升序排列。

SELECT COUNT(sno),cno FROM sc GROUP BY cno HAVING COUNT(sno)>=2 ORDER BY COUNT(sno) ASC

（12）检索学号比李强同学大，而年龄比他小的学生姓名。 

SELECT sname FROM student WHERE sno>(SELECT sno FROM student WHERE sname=‘李强‘) AND age> (SELECT age FROM student WHERE sname=‘李强‘)

（13）检索姓名以李打头的所有学生的姓名和年龄。 

SELECT sname,age FROM student WHERE sname LIKE ‘%李%‘

（14）在SC中检索成绩为空值的学生学号和课程号。 

SELECT sno,cno FROM student,course WHERE EXISTS (

SELECT * FROM sc WHERE score is NULL AND sc.`sno`=student.`sno` AND sc.`cno`=course.`cno`)

（15）求年龄大于女同学平均年龄的男学生姓名和年龄。

SELECT sname,age FROM student WHERE sex = ‘男‘ AND age >(SELECT AVG(age) FROM student WHERE sex = ‘女‘)

（16）求年龄大于所有女同学年龄的男学生姓名和年龄。

SELECT sname,age FROM student WHERE age>(SELECT max(age) FROM student WHERE sex=‘女‘) AND sex=‘男‘

>>>>>>>>>>

 

查询学号为1的学生 选修了的课程

#方式一：

SELECT * FROM course WHERE cno = (

SELECT cno FROM sc WHERE sno=1)

#方式二：

SELECT * FROM course WHERE  EXISTS (

SELECT * FROM student WHERE sno = 1 AND EXISTS (

SELECT * FROM sc WHERE sc.`sno`=student.sno AND sc.`cno`=course.`cno`))

 

查询学号为1的学生 是否选修“程军”老师的课程

SELECT * FROM course WHERE teacher = ‘程军‘ AND EXISTS (

SELECT * FROM student WHERE sno = 1 AND EXISTS (

SELECT * FROM sc WHERE sc.`sno`=student.sno AND sc.`cno`=course.`cno`))

 

查询学号为1的学生 是否选修了“程军”老师的课程，如果没有选修，请输出该同学没有选修的课程信息

SELECT * FROM course WHERE teacher = ‘程军‘ AND NOT EXISTS (

SELECT * FROM student WHERE sno = 1 AND EXISTS (

SELECT * FROM sc WHERE sc.`sno`=student.sno AND sc.`cno`=course.`cno`))

 

查看选修了程军老师课程的学生姓名信息：

SELECT * FROM student WHERE EXISTS (

SELECT * FROM course WHERE teacher = ‘程军‘ AND  EXISTS (

SELECT * FROM sc WHERE sc.`sno`=student.sno AND sc.`cno`=course.`cno`))

查看没有全部选修程军老师课程的学生信息

SELECT * FROM student WHERE EXISTS (

SELECT * FROM course WHERE teacher = ‘程军‘ AND NOT EXISTS (

SELECT * FROM sc WHERE sc.`sno`=student.sno AND sc.`cno`=course.`cno`))

查看没有选修程军老师课程的学生姓名信息:

SELECT * FROM student WHERE NOT EXISTS (

SELECT * FROM course WHERE teacher = ‘程军‘ AND EXISTS (

SELECT * FROM sc WHERE sc.`sno`=student.sno AND sc.`cno`=course.`cno`))

查看选修了程军老师所有课程的学生姓名信息：

SELECT * FROM student WHERE NOT EXISTS (

SELECT * FROM course WHERE teacher=‘程军‘ AND NOT EXISTS(

SELECT cno FROM sc WHERE sc.`sno`=student.`sno` AND course.`cno`=sc.`cno`))

 

 

（注：个人所写，并不保证全部正确、写法最优，如有意见，欢迎指出）