SQL之查询语句的实践运用

第一次数据库系统上机作业

1、
mysql> select sno,grade
    -> from sc
    -> where cno="CS3121014";

2、
mysql> select sc.sno,sname
    -> from sc,student
    -> where student.sno=sc.sno and
    -> sc.cno="CS3121014";

3、

mysql> select student.sno,sname,grade
    -> from student,sc,course
    -> where course.cno=sc.cno and
    -> student.sno=sc.sno and
    -> cname="数据库系统"
    -> order by grade desc;

4、
mysql> select distinct sno
    -> from sc
    -> where cno in ("CS3121014","CS3221018");

5、
mysql> select sno
    -> from sc
    -> where cno="CS3121014" and sno in
    -> (select sno
    -> from sc
    -> where cno="CS3221018");

6、
mysql> select sno
    -> from sc
    -> where not exists
    -> (select sno
    -> from sc
    -> where cno="CS3121014");

7、
mysql> select cpno
    -> from pcourse
    -> where cno="CS3121014";

8、***有难度
mysql> select sno
    -> from student
    -> where not exists
    -> (select *
    -> from course
    -> where not exists
    -> (select *
    -> from sc
    -> where sno=student.sno
    -> and cno=course.cno));

9、***
mysql> select distinct sno
    -> from sc scx
    -> where not exists
    -> (select *
    -> from sc scy
    -> where scy.sno="03051066" and
    -> not exists
    -> (select *
    -> from sc scz
    -> where scz.sno=scx.sno and
    -> scz.cno=scy.cno));

10、
mysql> select sname,sno,ssex
    -> from student
    -> where sname like "刘%";

11、
mysql> select sname,sno
    -> from student
    -> where sname like "_阳%";

12、
mysql> select count(distinct sno)
    -> from sc;

13、
mysql> select max(grade)
    -> from sc
    -> where cno="CS3121014";

14、
mysql> select sum(ccredit)
    -> from sc,course
    -> where sc.cno=course.cno and
    -> sc.sno="03051014";

15、
mysql> select sno
    -> from sc
    -> group by sno
    -> having count(*)>3;

16、
 mysql> select sno,count(*)
    -> from sc
    -> where grade>92
    -> group by sno
    -> having count(*)>4;

以上估计有很多错误的地方，等到时候和同学对一下答案再来改一下。