大数据学习笔记——MySql练习

#1

#查询“李新”老师所授课程的课程名称 select   distinct  course.cname from teacher,teaching,sc,course where   teacher.Tno = teaching.tno   and teaching.cno = sc.cno   and sc.cno = course.cno   and sc.degree>0   and teacher.Tname = '李新'   ; 

#2

#查询女教师所授课程的课程号及课程名称 select distinct course.cname,course.cno from teacher,teaching,sc,course where   teacher.Tno = teaching.tno   and teaching.cno = sc.cno   and sc.cno = course.cno   and sc.degree>0   and teacher.Tsex = '女';  

#3

#查询“李勇”同学所选课程的成绩 select sc.cno 课程,sc.degree from student,sc where         student.sno = sc.sno   and student.sname = '李勇  '; 

#4

#查询姓“王”的学生所学的课程名称。   select student.sname 姓名,course.cname 选课 from student,sc,course where         student.sno = sc.sno   and sc.cno = course.cno   and sc.degree>0   and student.sname regexp '^王';  

#5

#（5）查询至少选修一门课程的女学生姓名。 select distinct student.sname 姓名 ,student.ssex 性别 from student,sc where       student.sno = sc.sno   and sc.degree is not null   and student.ssex = '女'; 

#6

#查询选修课程名为“数学”的学生学号和姓名。 select distinct student.sname 姓名,student.sno 学号 from student,sc,course where         student.sno = sc.sno   and sc.cno = course.cno   and course.cname = '高等数学' ; 

#7

#查询选修“数据库”课程且成绩在80～90分的学生学号及成绩  select distinct student.sno 学号,sc.degree 成绩 from student,sc,course where     student.sno = sc.sno and     sc.degree between 80 and 90 order by 成绩;  

#8

#查询课程成绩及格的男同学的学生信息及课程号与成绩。 select distinct student.*,sc.cno,sc.degree from student,sc  where          student.sno = sc.sno   and sc.degree>=60   and student.ssex='男';  

#9

#查询选修“C04”课程的学生的平均年龄。 select distinct round(AVG(sc.degree),2) 平均成绩,sc.cno 课程号 from student,sc where   student.sno = sc.sno   and sc.cno = 'c04';  

#10

#查询同时选修了“C04”和“C02”课程的学生姓名和成绩。 select  distinct student.sname, a.degree c02成绩,b.degree c04成绩 from student,sc a,sc b where       student.sno  = a.sno   and student.sno  = b.sno   and b.cno = 'c04'and a.cno='c02'   and a.degree is not null and b.degree is not null ;  

子查询

#1

#查询“李新”老师所授课程的课程名称。 select course.cname from course where course.cno = (     select teaching.cno from teaching where teaching.tno=                                             (     select teacher.Tno from teacher where Tname='李新' )) 

#2

#查询女教师所授课程的课程号及课程名称。 select course.* from course where  course.cno in(     select teaching.cno from teaching where teaching.tno in(         select teacher.tno from teacher where teacher.Tsex='女'     )     );  

#3

#查询“李勇”同学所选课程的成绩。 select  sc.degree from sc where sno in(     select student.sno from student where student.sname='李勇'     ); 

#4

#查询姓“王”的学生所学的课程名称。 select course.cname from course where course.cno in(     select sc.cno from sc where sc.sno in (         select student.sno from student where student.sname in (             select student.sname from student where  sname like '王%'             )         )     ); 

#5

#查询选修课程名为“高等数学”的学生学号和姓名。 select student.sno,student.sname from student where student.sno in (     select sc.sno from sc where cno in (         select course.cno from  course where course.cname ='高等数学'         )     ); 

#6

#查询选修“数据库原理及应用”课程且成绩在80～90分的学生学号及成绩。 select sc.sno,sc.degree from sc where sc.cno in (     select course.cno from course where course.cname='数据库原理及应用'     ) and sc.degree between 80 and 90 

#7

#查询选修“C04”课程的学生的平均年龄。 select avg((year(now())-year(student.sbirthday))) 年龄平均值 from student where     student.sno in( select sc.sno from sc where cno ='c04'); 

#8

#查询出生日期大于所有女同学出生日期的男同学的姓名及系别 select distinct student.sname ,department.deptname a from student,department  where       year(student.sbirthday) >(select min(year(student.sbirthday)) from student where student.ssex='女') and       department.deptno in(         select class.deptno         from class         where class.classno in (             select student.classno from student where student.ssex='男'             )         ) 

#9

#查询成绩比该课程平均成绩高的学生的学号及成绩。 select   student.sno, sc.degree from student,sc where student.sno in (     select sc.sno from sc where sc.degree > (select avg(sc.degree) from sc)     );  

#10

#查询没有选修“C02”课程的学生学号及姓名。 select student.sno,student.sname from student where sno in(     select sc.sno     from sc     where cno <> 'c02'     );