大数据学习笔记——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'
    );