use XSXK;-- 简单查询--1select sno as 学号, sname as 姓名
from student
where sdept ='数计学院';--2select sno
from student
where(selectcount(cno)from SC
where SC.sno = student.sno
)>0;--3select sno as 学生学号, grade as 成绩
from SC
where cno ='005'orderby grade desc, sno asc;--4 select sno as 学生学号, grade *0.8as 成绩
from SC
where cno ='005'and grade >=80and grade <=90;--5 select*from student
where sdept in('管理学院','数计学院')and sname like'张%';--6select sno as 学号, cno as 课程号
from SC
where grade isnull;-- 连接查询--1 select SC.sno as 学号,
student.sname as 姓名,
Course.cname as 选修的课程,
SC.grade as 成绩
from SC
join student on SC.sno = student.sno
join course on SC.cno = Course.cno
--2select Course.*from SC
join student on SC.sno = student.sno
join Course on SC.cno = Course.cno
where Course.ccredit >2and student.sdept ='数计学院'--3 select
student.*,
Course.cno as 选课编号,
SC.grade as 成绩
from SC
join student on SC.sno = student.sno
join Course on SC.cno = Course.cno
select student.*select
student.*,
Course.cno as 选课编号,
SC.grade as 成绩
from student
join SC on SC.sno = student.sno
join Course on SC.cno = Course.cno;--4select
SC.sno as 学号,
student.sname as 姓名,
SC.grade as 成绩
from SC
join student on student.sno = SC.sno
join Course on Course.cno = SC.cno
where
SC.cno ='002'and SC.grade >=90--5select a.cname as 课程, c.cname as 先行课
from Course a, Course b, Course c
where a.cpno = b.cno and b.cpno = c.cno
--6select sno from sc groupby sno havingcount(*)>=2selectdistinct s.sno as 学号
from student s
join SC sc1 on sc1.sno = s.sno
join SC sc2 on sc2.sno = s.sno and sc2.cno != sc1.cno
selectdistinct sc1.sno as 学号
from SC sc1
join SC sc2 on sc2.sno = sc1.sno and sc2.cno != sc1.cno
实验3 数据库的组合查询和嵌套查询
--嵌套查询--1select sno as 学生学号, sname as 姓名
from student
where sno in(select sno from SC
where cno =(select cno from Course where cname ='数据结构'))--2select sno as 学生学号, sname as 学生姓名
from student
where sage >(select sage from student where sname ='张飞')--3selectdistinct student.sno as 学生学号, SC.grade as 成绩
from student, SC
where grade <(select grade from SC where sno =(select sno from student where sname ='张飞')and cno =(select cno from Course where cname ='数据结构'))--4select student.*from student
where sage <(selectmin(sage)from student where sdept ='数计学院')--5select student.sname as 学生姓名
from student
where sno in(select sno from SC where cno =(select cno from Course where cname ='数据结构'));--6select sname
from student
wherenotexists(select*from course
wherenotexists(select*from sc
where sc.sno = student.sno and sc.cno = course.cno
));--7select sname as 学生姓名, sno as 学生学号
from student
where sno in(select sno from SC where cno in(select cno from SC where sno ='2001124001'))--8select sno as 学号, sname as 学生姓名
from student
wherenotexists(select*from course
wherenotexists(select*from sc
where sc.sno ='2001124001'and sc.cno = course.cno
))--9select sname as 学生姓名
from student
where sno in(select sno
from sc
where cno =(select cno
from course
where cname ='数据结构')and sno in(select sno
from sc
where cno =(select cno
from course
where cname ='数据库原理')))--10select sname as 学生姓名
from student
where sno in(select sno
from sc
where cno =(select cno
from course
where cname ='数据结构')or sno in(select sno
from sc
where cno =(select cno
from course
where cname ='数据库原理')))--11select sname as 学生姓名
from student
where sno in(select sno
from sc
where cno =(select cno
from course
where cname ='数据结构')and sno in(select sno
from sc
where cno in(select cno
from course
where cname !='数据库原理')))--12 select cno as 课程号
from sc x
wherenotexists(select*from student
where ssex ='女'andexists(select*from sc y
where x.cno = y.cno and x.sno = y.sno
))--组合查询和统计查询--1select sname
from student
where sno in(select sno
from sc
where cno =(select cno
from course
where cname ='数据结构'))intersectselect sname
from student
where sno in(select sno
from sc
where cno =(select cno
from course
where cname ='数据库原理'))--2select sname
from student
where sno in(select sno
from sc
where cno =(select cno
from course
where cname ='数据结构'))unionselect sname
from student
where sno in(select sno
from sc
where cno =(select cno
from course
where cname ='数据库原理'))--3select sname
from student
where sno in(select sno
from sc
where cno =(select cno
from course
where cname ='数据结构'))exceptselect sname
from student
where sno in(select sno
from sc
where cno =(select cno
from course
where cname ='数据库原理'))--4selectcount(distinct sc.sno)as 学生人数
from sc
--5select sno as 学生学号,sum(grade)as 总成绩
from sc
where grade >=60groupby sno havingcount(*)>4--6select sdept as 学院,count(*)as 学院人数
from student
groupby sdept
--7select sage as 年龄,count(*)as 人数
from student
groupby sage
--8selectcount(cno)as 选课数,avg(grade) 平均成绩
from sc
groupby sno
--9select course.*, cc.选课人数 from course leftjoin(select
Course.cno,COUNT(sno)'选课人数'from
course
leftjoin sc
on course.cno = sc.cno
groupby course.cno)as cc on course.cno = cc.cnos