初学sql时常用查询语句

*/

--1. 查询年龄大于等形势形势于20且姓名以‘s’开头的学生姓名、所在系.
select sname,sdept from xs where sname like 's%'

--2. 查询所在系是计算机或者中文的学生信息.
select * from xs where sdept in('计算机','中文')

--3. 查询课号为1或3的学生学号、课程名和成绩.
select xs.sid,kname,score 
from xs join xk on xs.sid=xk.sid 
join kc on kc.kid=xk.kid
where xk.kid in(1,3)

--4. 查找学生数据库中每门课程的情况和选修情况.
select kc.*,sid,score from kc left outer join xk on kc.kid=xk.kid

--5. 从数据库中查询学生的姓名、课程名和成绩.
select sname,kname,score 
from xs join xk on xs.sid=xk.sid 
join kc on kc.kid=xk.kid

--6. 查询计算机系的学生学号、所选课号和成绩.
select xs.sid,xk.kid,score 
from xs join xk on xs.sid=xk.sid
where sdept='计算机'

--7. 查询学号为3的学生所选修的课程名，成绩，并成绩的升序排列.
select kname,score
from xk join kc on kc.kid=xk.kid
where sid=3
order by score asc

--8. 查询cc同学的学号，姓名，所学的课程名和成绩.
select xs.sid,sname,kname,score
from xs join xk on xs.sid=xk.sid
join kc on kc.kid=xk.kid
where sname='cc'

--9. 查询选修了数据库的学生学号和成绩.
select sid,score
from xk join kc on kc.kid=xk.kid
where kname='数据库'

--10. 求学生的平均年龄
select avg(score) as 平均成绩 from xk

--11. 查询学生成绩的最高分和最底分.
select max(score) as 最高分, min(score) as 最低分 from xk

--12. 查询每个课号有多少个学生选修.
select kc.kid,count(*) as 人数 from kc join xk on xk.kid=kc.kid 
group by kc.kid

--13. 查询每个性别的人数.
select sgender,count(*)as 人数 from xs
group by sgender

--14. 求选修课程大于等于2个的学生学号和课程门数.
select xs.sid,count(*)as 选修的课程门数 from xs join xk on xs.sid=xk.sid
group by xs.sid having count(*)>=2

--15. 查询年龄比平均年龄小的学生学号,姓名.
select sid,sname from xs where sage<(select avg(sage) from xs)

--16. 统计每个系有多少学生，并以学生人数按降序排列.
select sdept,count(*)as 人数 from xs group by sdept order by count(*) desc

--17. 统计每个学生的总成绩，并输出总成绩大于200的学生学号、总成绩.
select xs.sid,sum(score)as 总成绩 from xs join xk on xs.sid=xk.sid 
group by xs.sid having sum(score)>200

--18. 查询选修课号为5，且成绩在80分以上的学生学号，姓名，课程名，成绩.
select xs.sid,sname,kname,score 
from xs join xk on xs.sid=xk.sid 
join kc on xk.kid=kc.kid
where kc.kid=5 and score>80

--19. 查询女同学平均成绩在80分以上的学生学号和平均成绩.
select xs.sid,avg(score)as 平均成绩 
from xs join xk on xs.sid=xk.sid
where sgender=0
group by xs.sid having avg(score)>80

--20. 查询计算机专业选修课程数多于1门的学生学号和课程数.
select xs.sid,count(*)as 课程数 
from xs join xk on xs.sid=xk.sid
where sdept='计算机' group by xs.sid having count(*)>1

/*
以下是扩展的内容 ：
--查询选修了1001学生选修的任一课程的学生学号
select sid from xk 
where kid in(select kid from xk where sid=1001)

--查询成绩是92分的学生姓名
select sname from xs 
where sid in(select sid from xk where score=92)

select * from xs inner join xk on xs.sid=xk.sid

--查找选修了课程号为2且成绩在80分以上的学生姓名和成绩
select sname,score from xs inner join xk on xs.sid=xk.sid 
where kid=2 and score>=80

--查询选修了数据库的学生学号和成绩

select sid,score from xk
inner join kc on xk.kid=kc.kid
where kname='数据库'

--查找刘德华选修2号课程的成绩
select score from xs inner join xk on xs.sid=xk.sid
where sname='刘德华' and kid=2

--查询学生的姓名、课程名和成绩
select sname,kname,score 
from xs join xk on xs.sid=xk.sid
        join kc on xk.kid=kc.kid

--查询周杰伦选修古汉语的成绩

select score 
from xs join xk on xs.sid=xk.sid
        join kc on xk.kid=kc.kid 
where sname='周杰伦' and kname='古汉语'

--查询选修了数据库的学生姓名和成绩
select sname,score from xs join xk on xs.sid=xk.sid
                           join kc on kc.kid=xk.kid
where kname='数据库'

select sum(score) as 总成绩 from xk
select max(score) as 最高分 from xk

select avg(sage) as 平均年龄 from xs

select count(*) as 总人数 from xs

select sdept from xs group by sdept

--求学生数据库中每个专业的学生数
select sdept,count(*) as 人数 from xs group by sdept

--求被选修的各门课程的课程号,平均成绩及选修该门课程的人数。
select kid,avg(score) as 平均成绩 ,count(*) as 人数 
from xk
group by kid
--统计每个学生的总成绩
select sid,sum(score) as 总成绩 from xk group by sid

select sid,avg(score) as 平均成绩 from xk group by sid
having avg(score)>85

--查找中文专业平均成绩在90分以上的学生的学号和平均成绩
select xk.sid,avg(score) as 平均成绩 
from xk inner join xs on xs.sid=xk.sid
where sdept='中文'
group by xk.sid
having avg(score)>90
--查询男同学总成绩大于200的学生学号和总成绩,
select xs.sid,sum(score) as 总成绩 
from xk inner join xs on xs.sid=xk.sid
where sgender=1
group by xs.sid
having sum(score)>200

*/

/*