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;
以上估计有很多错误的地方,等到时候和同学对一下答案再来改一下。