pta数据库SQL语句练习3

目录

R10-65 查询与“陈红”同一个学院的同学姓名

R10-66 查询没有选课的学生学号、姓名和班级

R10-67 查询同时选修了“0000008”和“0000007”课程的学生姓名

R10-68 查询成绩比所有课程的平均成绩高的学生的学号及成绩

R10-69 71.显示期中成绩不及格1门以上的同学学号、姓名、门数（Num）

R10-70 72.统计每个班级期末成绩的最高分（Max），显示班级名称、期末最高成绩

R10-71 73.显示一周8节课及以上的学生的名单，显示学号、姓名、班级名称

 R10-72 74.显示计算机科学与技术1班一周上课的时间（周几，哪几节课）、地点（哪个教室），课程名称

 R10-73 75.统计教授每周上课的课时数（Hours），显示姓名、课时数

 R10-74 76.显示没有班导师的班级名称、院系名称

 R10-75 77.显示指导过两个班级以上的班导的姓名、所指导的班级名称

 R10-76 79.计算机科学与技术3班所有学生都选修了2009-2010-1的操作系统(课程编号为4)，请记录相关信息

 R10-77 81.将李飞同学的联系方式改为661010

 R10-78 82.如果平时成绩大于90，则将总评成绩低于70的重置为70

---

R10-65 查询与“陈红”同一个学院的同学姓名

select sname
from students 
where students.sdept in (
    select sdept
    from students 
    where students.sname = '陈红'
) and students.sname not in ('陈红')

R10-66 查询没有选课的学生学号、姓名和班级

select students.sno , students.sname,students.class
from students 
where students.sno not in (
    select sno 
    from sc
)

R10-67 查询同时选修了“0000008”和“0000007”课程的学生姓名

select students.sname
from students 
where students.sno in (
    select sc.sno 
    from sc 
    where sc.cno = '0000008' 
) and students.sno in (
    select sc.sno 
    from sc 
    where sc.cno = '0000007' 
)

R10-68 查询成绩比所有课程的平均成绩高的学生的学号及成绩

select sno,score
from sc
where sc.score > (
    select AVG(score)
    from sc
    
)

R10-69 71.显示期中成绩不及格1门以上的同学学号、姓名、门数（Num）

select sc.SId,SName,count(SCScore2) as Num
from sc join student on sc.SId = student.SId
where SCScore2 < 60
group by sc.SId,SName
having count(SCScore2) > 1

R10-70 72.统计每个班级期末成绩的最高分（Max），显示班级名称、期末最高成绩

select GName,b as Max
from grade join(
    select GId,max(SCScore3) as b
    from student join sc on sc.SId=student.SId
    group by GId
)as a on grade.GId=a.GId

R10-71 73.显示一周8节课及以上的学生的名单，显示学号、姓名、班级名称

select student.SId,student.SName,grade.GName
from student,grade,information
where student.GId = grade.GId
and student.GId = information.GId
group by student.SId,student.SName,grade.GName
having sum(length(information.ITimeseg)) > 8;

 R10-72 74.显示计算机科学与技术1班一周上课的时间（周几，哪几节课）、地点（哪个教室），课程名称

SELECT information.IWeek, information.ITimeseg, information.IRoom, course.CName
FROM information,
     course,
     grade
WHERE grade.GName = '计算机科学与技术1班'
  AND course.CId = information.CId
  AND grade.GId = information.GId

 R10-73 75.统计教授每周上课的课时数（Hours），显示姓名、课时数

SELECT teacher.TName, SUM(LENGTH(information.ITimeseg)) AS Hours
FROM teacher join information on teacher.TId = information.TId
WHERE teacher.TProf = '教授'
GROUP BY teacher.TName

 R10-74 76.显示没有班导师的班级名称、院系名称

SELECT grade.GName, dept.DName
FROM grade,
     dept
WHERE grade.DId = dept.DId
  AND grade.TId IS NULL

 R10-75 77.显示指导过两个班级以上的班导的姓名、所指导的班级名称

SELECT TName, GName
FROM grade,
     teacher
WHERE grade.TId = teacher.TId
  AND grade.TId IN (
    SELECT grade.TId
    FROM grade
    WHERE TId IS NOT NULL
    GROUP BY grade.TId
    HAVING COUNT(GId) > 2)

 R10-76 79.计算机科学与技术3班所有学生都选修了2009-2010-1的操作系统(课程编号为4)，请记录相关信息

INSERT INTO sc
    (SCId, SId, CId, SCTerm)
SELECT (@row_number:=@row_number+1) as SCId, student.SId, '4' as CId, '2009-2010-1' as SCTerm
FROM student,
     grade,
     (SELECT @row_number:=MAX(SCId) FROM sc) as a
WHERE student.GId = grade.GId
  AND GName = '计算机科学与技术3班'

 R10-77 81.将李飞同学的联系方式改为661010

update student 
set STele = '661010'
where SName = '李飞'

 R10-78 82.如果平时成绩大于90，则将总评成绩低于70的重置为70

UPDATE sc
SET SCScore = 70
WHERE SCScore1 > 90 AND SCScore < 70;