sql复习小练习

建表语句

Student(Snum varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
Course(Cnum varchar(10),Cname varchar(10),Tnum varchar(10));
Teacher(Tnum varchar(10),Tname varchar(10));
SC(Snum varchar(10),Cnum varchar(10),score decimal(18,1));
(–根据网上一个帖子整理而来，原帖 http://bbs.csdn.net/topics/340046949?page=3 –)
select a.*,b.score,c.score from student a,SC b,SC c
where a.Snum=b.Snum and a.Snum=c.Snum
and b.Cnum=’01’ and c.Cnum=’02’
and b.score > c.score

SELECT a.Snum,a.Sname,CAST(AVG(b.score) AS DECIMAL(18,2)) AS AVG
FROM student a,sc b
WHERE a.Snum=b.Snum
GROUP BY a.Snum,a.Sname
HAVING CAST(AVG(b.score) AS DECIMAL(18,2)) >70
ORDER BY a.Snum

选择zhangsan老师课程的同学信息

SELECT a.* ,d.Tname
FROM student a
INNER JOIN sc b
ON a.Snum = b.Snum
INNER JOIN course c
ON b.Cnum = c.Cnum
INNER JOIN teacher d
ON c.Tnum=d.Tnum
AND d.Tname=’zhangsan’

没有选择zhagnsan老师课程的同学

SELECT * FROM student
WHERE Snum NOT IN
(SELECT a.Snum
FROM student a
INNER JOIN sc b
ON a.Snum = b.Snum
INNER JOIN course c
ON b.Cnum = c.Cnum
INNER JOIN teacher d
ON c.Tnum=d.Tnum
AND d.Tname=’zhangsan’)

ISNULL的用法，若price为空则用第二个变量替换

SELECT AVG(ISNULL(price, $10.00))

查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息

@方法一
SELECT a.* FROM student a
INNER JOIN sc b
ON a.Snum = b.Snum
AND b.Cnum=’01’
AND EXISTS(
SELECT 1 FROM sc c
WHERE c.Snum=a.Snum AND c.Cnum=’02’
)
ORDER BY a.Snum
@方法二
SELECT m.* FROM student m
WHERE Snum IN
(
SELECT Snum FROM (
SELECT DISTINCT snum FROM sc WHERE cnum=’01’
UNION ALL
SELECT DISTINCT snum FROM sc WHERE cnum=’02’
)t GROUP BY snum HAVING COUNT(1)=2
)
ORDER BY m.snum

学完三门课程的同学信息

SELECT * FROM student
WHERE snum IN
(SELECT a.snum
FROM student a
INNER JOIN sc b
WHERE a.snum = b.Snum
GROUP BY a.snum
HAVING (COUNT(cnum))>2)

此网站使用的插件 alipay webmod control

选择01而没有选择02课程的同学的信息

@方法一 使用exists
SELECT a.* FROM student a
INNER JOIN sc b
ON a.Snum=b.Snum
AND b.cnum=’01’
AND NOT EXISTS
(
SELECT 1 FROM sc
WHERE b.Snum=sc.Snum
AND sc.Cnum=’02’
)
@方法二 使用in
SELECT a.* FROM student a
INNER JOIN sc b
ON a.Snum=b.Snum
AND b.cnum=’01’
AND a.snum NOT IN
(
SELECT a.snum FROM student a,sc
WHERE a.Snum=sc.Snum
AND sc.Cnum=’02’
)

查询和01同学课程完全相同的同学信息

SELECT a.*
FROM student a
INNER JOIN sc b
WHERE a.snum=b.Snum
AND a.Snum<>’01’
AND b.Cnum IN
(
SELECT cnum FROM sc
WHERE sc.Snum=’01’
)
GROUP BY b.Snum
HAVING COUNT(1) = (SELECT COUNT(*) FROM sc WHERE sc.Snum=’01’ GROUP BY sc.Snum)

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 

SELECT a.snum,a.sname,CAST(AVG(b.score) AS DECIMAL(18,2)) AS AVG
FROM student a
INNER JOIN sc b
WHERE a.Snum=b.Snum
AND a.snum IN
(
SELECT snum FROM sc
WHERE sc.score < 60
GROUP BY snum
HAVING COUNT(1)>=2
)
GROUP BY a.Snum,a.Sname

查询各科成绩最高分、最低分和平均分：显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

SELECT m.Cnum,MAX(n.score) AS MAX,MIN(n.score) AS MIN,CAST(AVG(n.score) AS DECIMAL(18,2)) AS AVG,
CAST(((SELECT COUNT(1) FROM sc WHERE sc.cnum = m.cnum AND
score >=60)*100.0/(SELECT COUNT(1) FROM sc WHERE sc.cnum = m.cnum)) AS DECIMAL(18,2)) AS cntD,
CAST(((SELECT COUNT(1) FROM sc WHERE sc.cnum = m.cnum AND score >=70 AND
score <80)*100.0/(SELECT COUNT(1) FROM sc WHERE sc.cnum = m.cnum)) AS DECIMAL(18,2)) AS cntC,
CAST(((SELECT COUNT(1) FROM sc WHERE sc.cnum = m.cnum AND score >=80 AND score <90)*100.0/(SELECT COUNT(1) FROM sc WHERE sc.cnum = m.cnum)) AS DECIMAL(18,2)) AS cntB,
CAST(((SELECT COUNT(1) FROM sc WHERE sc.cnum = m.cnum AND score >=90)*100.0/(SELECT COUNT(1) FROM sc WHERE sc.cnum = m.cnum)) AS DECIMAL(18,2)) AS cntA
FROM course m,sc n
WHERE m.cnum = n.cnum
GROUP BY m.cnum,m.cname
ORDER BY m.cnum

B-Tree索引可以在表达式中使用=, >, >=, <, <=用作列比较或者 BETWEEN 运算符。还能使用LIKE比较，

查询每门课程被选修的学生数

select c#### , count(S####)[学生数] from sc group by C####

查询出只有两门课程的全部学生的学号和姓名

SELECT A.snum,A.sname
FROM student A INNER JOIN sc B
ON A.Snum = B.Snum
GROUP BY A.snum,A.Sname
HAVING COUNT(B.Cnum) = 2
ORDER BY A.snum

查询男生、女生人数

select
sum(case when ssex=’男’ then 1 else 0 end) as 男生人数,
sum(case when ssex=’女’ then 1 else 0 end )as 女生人数
from student

查询名字中含有”风”字的学生信息

select * from student where sname like ‘%风%’
select * from student where charindex(‘风’ , sname) > 0

查询同名同性学生名单，并统计同名人数

select Sname , count(*)from Student
group by Sname
having count(*) > 1

查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

select * from Student where year(sage) = 1990
select * from Student where datediff(yy,sage,’2000-01-01’) = 0
select * from Student where datepart(yy,sage) = 1990

查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT m.cnum,m.cname,
CAST(AVG(n.score) AS DECIMAL(18,2)) AVG
FROM course m INNER JOIN sc n
WHERE m.Cnum = n.Cnum
GROUP BY m.Cnum,m.Cname
ORDER BY AVG DESC,cnum ASC

查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select a.snum,a.sname,cast(avg(b.score) as decimal(18,2)) avg
from student a inner join sc b
on a.Snum = b.Snum
group by a.snum,a.sname
having avg >= 85
order by a.snum

查询课程名称为”math”，且分数低于60的学生姓名和分数

select a.sname,b.score
from student a inner join sc b inner join course c
on a.snum = b.Snum and b.Cnum = c.Cnum
where b.score < 60 and c.Cname=’math’

查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

select a.sname,c.cname,b.score
from student a,sc b,course c
where a.snum = b.Snum and b.Cnum = c.Cnum and b.score > 70
order by a.Snum,b.Cnum

求每门课程的学生人数

select c.cnum,c.cname,count(*)
from sc b,course c
where b.Cnum = c.Cnum
group by c.cnum
order by c.cname

查询每门功成绩最好的前两名 (myql 不支持top n 查询)

select a.*,b.score
from student a inner join sc b
on a.snum = b.snum
where b.score in
(
select top 2 score from sc
order by score desc
)
order by a.Snum

统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c.cnum,c.cname,count(*) as cnt
from course c inner join sc b
on c.Cnum = b.Cnum
group by c.Cnum
having count(*)>5
order by cnt desc

检索至少选修两门课程的学生学号

select a.snum,a.sname
from student a inner join sc b
on a.snum = b.snum
group by a.Snum
having count(1)>2
order by a.Snum

查询选修了全部课程的学生信息

–方法1 根据数量来完成
select a.*
from student a
where a.snum in
(
select snum from sc
group by snum
having count(1) = (select count(*) from course)
)

查询各学生的年龄

select * , datediff(yy , sage , getdate()) [年龄] from student