有 3 个表(15 分钟)

  • Student 学生表 (学号,姓名,性别,年龄,组织部门)
  • Course 课程表 (编号,课程名称)
  • Sc 选课表 (学号,课程编号,成绩)
    表结构如下:
  • 1)写一个 SQL 语句,查询选修了’计算机原理’的学生学号和姓名(3 分钟)
  • 2)写一个 SQL 语句,查询’周星驰’同学选修了的课程名字(3 分钟)
  • 3)写一个 SQL 语句,查询选修了 5 门课程的学生学号和姓名(9 分钟)
    答:1)SQL 语句如下:

    select stu.sno, stu.sname from Student stu 
    where (select count(*) from sc where sno=stu.sno and cno = 
    (select cno from Course where cname='计算机原理')) != 0; 

    2)SQL 语句如下:

    select cname from Course 
    where cno in ( select cno from sc where sno = 
    (select sno from Student where sname='周星驰'));

    3)SQL 语句如下:

    select stu.sno, stu.sname from student stu 
    where (select count(*) from sc where sno=stu.sno) = 5;