Question

. 简答题 根据以下关系模式完成a、b、c、d四个查询,写出SQL语句。 Student(Sn

Answer 1

问：. 简答题 根据以下关系模式完成a、b、c、d四个查询,写出SQL语句。 Student(Sn

答：你的题目不完整

问：根据以下关系模式完成a、b、c、d四个查询，写出SQL语句。Student(Sno, Sname, Ssex，Sage, Sdept), Course(Cno, Cname, Cpno, Ccredit, Tno), SC(Sno, Cno, Grade), Teacher(Tno, Tname, Salary)a. 查询每一位同学分数最低的一门课。b. 查询没有选修1号课程的同学。此查询要求给出三种写法。c. 检索选修了李明老师主讲课程的同学姓名。d. 检索没有选修李明老师主讲课程的同学姓名。

答：a. 查询每一位同学分数最低的一门课。

答：SELECT Sno, Sname, MIN(Grade) AS MinGradeFROM SC, StudentWHERE SC.Sno = Student.SnoGROUP BY Sno, Sname;

答：b. 查询没有选修1号课程的同学。此查询要求给出三种写法。

答：-- 方法一：使用NOT IN子查询SELECT Sno, SnameFROM StudentWHERE Sno NOT IN ( SELECT Sno FROM SC WHERE Cno = '1');-- 方法二：使用NOT EXISTS子查询SELECT Sno, SnameFROM StudentWHERE NOT EXISTS ( SELECT * FROM SC WHERE SC.Sno = Student.Sno AND SC.Cno = '1');-- 方法三：使用LEFT JOIN和IS NULLSELECT Sno, SnameFROM StudentLEFT JOIN SC ON Student.Sno = SC.Sno AND SC.Cno = '1'WHERE SC.Cno IS NULL;

答：c. 检索选修了李明老师主讲课程的同学姓名。

答：SELECT DISTINCT Student.SnameFROM Student, SC, Course, TeacherWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Tno = Teacher.Tno AND Teacher.Tname = '李明';

答：d. 检索没有选修李明老师主讲课程的同学姓名。

答：SELECT DISTINCT Student.SnameFROM Student, SC, Course, TeacherWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Tno = Teacher.Tno AND Teacher.Tname > '李明' AND Student.Sno NOT IN ( SELECT SC.Sno FROM SC, Course, Teacher WHERE SC.Cno = Course.Cno AND Course.Tno = Teacher.Tno AND Teacher.Tname = '李明' );