sql练习

sql servser 记录

1.只有一个数据相同，其他都不相同去重
create table cst (gq nvarchar(100),bg nvarchar(100),rs nvarchar(100))

insert into cst values(‘fr’,‘z’,‘b’),(‘lr’,‘ss’,‘cc’),(‘fr’,‘vv’,‘44’)

写法：select px.* from
(select cst.*,row_number() over (partition by gq ORDER BY gq desc ) px from cst ) px
where px=1

1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名

name course grade
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90

select name from name_zheng group by name having min(grade)>80

2. 现有学生表如下:
   自动编号 学号 姓名 课程编号 课程名称 分数
   1 2005001 张三 0001 数学 69
   2 2005002 李四 0001 数学 89
   3 2005001 张三 0001 数学 69
   删除除了自动编号不同, 其他都相同的学生冗余信息

create table xs_zheng (serial nvarchar(100),student nvarchar(100),name nvarchar(100),course_id nvarchar(100),course_name nvarchar(100),grade_digit int)

insert into xs_zheng values(‘6’,‘2005001’, ‘张三’, ‘0001’, ‘数学’, 69 ),(‘2’,‘2005002’, ‘李四’, ‘0001’, ‘数学’, 89 ),(‘3’,‘2005001’, ‘张三’, ‘0001’, ‘数学’, 69 )

select max(serial) from xs_zheng group by student,name,course_id,course_name,grade_digit

delete from xs_zheng where serial not in (

select max(serial) from xs_zheng group by student,name,course_id,course_name,grade_digit
)

3. 一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球队，现在四个球队进行比赛，用一条sql 语句显示所有可能的比赛组合

select * from team a,team b ORDER BY a.name,b.name

5. 怎么把这样一个数据表
   year month amount
   1991 1 1.1
   1991 2 1.2
   1991 3 1.3
   1991 4 1.4
   1992 1 2.1
   1992 2 2.2
   1992 3 2.3
   1992 4 2.4
   查成这样一个结果？
   year m1 m2 m3 m4
   1991 1.1 1.2 1.3 1.4
   1992 2.1 2.2 2.3 2.4

select year,
(select amount from test_zheng where month=‘1’ and year=a.year) as m1,
(select amount from test_zheng where month=‘2’ and year=a.year) as m2,
(select amount from test_zheng where month=‘3’ and year=a.year) as m3,
(select amount from test_zheng where month=‘4’ and year=a.year) as m4
from test_zheng a GROUP BY year

6. 有表A，结构如下：
   p_ID p_Num s_id
   1 10 01
   1 12 02
   2 8 01
   3 11 01
   3 8 03
   其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。
   请用SQL语句实现将上表中的数据合并，合并后的数据为：
   p_ID s1_id s2_id s3_id
   1 10 12 0
   2 8 0 0
   3 11 0 8
   其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。

select p_id,
(select P_Num from A where s_id=1 and p_id=1) as s1_id,
(select P_Num from A where s_id=2 and p_id=2) as s2_id,
(select P_Num from A where s_id=3 and p_id=3) as s3_id
from A group by p_id

select p_id,P_num,s_id from a where s_id=1
select p_id,P_num,s_id from a where s_id=2
select p_id,P_num,s_id from a where s_id=3

select p_id ,
sum(case when s_id=1 then p_num else 0 end) as s1_id
,sum(case when s_id=2 then p_num else 0 end) as s2_id
,sum(case when s_id=3 then p_num else 0 end) as s3_id
from A group by p_id

下面进入正题。首先创建数据表：

学生表 Student

create table Student(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10));
insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
insert into Student values(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
insert into Student values(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’);
insert into Student values(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);
insert into Student values(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’)

成绩表
create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1));
insert into SC values(‘01’ , ‘01’ , 80);
insert into SC values(‘01’ , ‘02’ , 90);
insert into SC values(‘01’ , ‘03’ , 99);
insert into SC values(‘02’ , ‘01’ , 70);
insert into SC values(‘02’ , ‘02’ , 60);
insert into SC values(‘02’ , ‘03’ , 80);
insert into SC values(‘03’ , ‘01’ , 80);
insert into SC values(‘03’ , ‘02’ , 80);
insert into SC values(‘03’ , ‘03’ , 80);
insert into SC values(‘04’ , ‘01’ , 50);
insert into SC values(‘04’ , ‘02’ , 30);
insert into SC values(‘04’ , ‘03’ , 20);
insert into SC values(‘05’ , ‘01’ , 76);
insert into SC values(‘05’ , ‘02’ , 87);
insert into SC values(‘06’ , ‘01’ , 31);
insert into SC values(‘06’ , ‘03’ , 34);
insert into SC values(‘07’ , ‘02’ , 89);
insert into SC values(‘07’ , ‘03’ , 98)

课程表 Course

create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’)

教师表 Teacher

create table Teacher(Tid varchar(10),Tname varchar(10));
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’)

四张表之间的关联很简单：

（以下题目的顺序和原文相对应）

1. 查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数

SELECT
a.Sid,
a.Sname,
b.score ‘语文’,
c.score ‘数学’
FROM
Student a
FULL JOIN ( SELECT * FROM sc WHERE sc.CId= ‘01’ ) b ON a.sid= b.sid
LEFT JOIN ( SELECT * FROM sc WHERE sc.CId= ‘02’ ) c ON a.sid= c.sid
LEFT JOIN Course d ON d.cid= b.cid
WHERE
b.score> c.score

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT
a.sid,
a.Sname,
AVG ( b.score ) ‘平均成绩’
FROM
Student a
FULL JOIN SC b ON a.sid= b.sid
LEFT JOIN Course c ON c.cid= b.cid
GROUP BY
a.sid,
a.Sname
HAVING
AVG ( b.score ) > 60

3. 查询在 SC 表存在成绩的学生信息

select a.*,b.score,c.cname from Student a FULL JOIN sc b on a.sid=b.sid left join Course c on c.cid=b.cid WHERE b.score is not null

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT
a.sid,
a.Sname,
COUNT ( c.Cid ) ‘选课总数’,
SUM ( b.score ) ‘所有课程总分数’
FROM
Student a
FULL JOIN sc b ON a.sid= b.sid
LEFT JOIN Course c ON c.cid= b.cid
GROUP BY
a.sid,
a.Sname

4.1 查有成绩的学生信息

select a.*,b.score,c.cname from Student a FULL JOIN sc b on a.sid=b.sid left join Course c on c.cid=b.cid WHERE b.score is not null

5. 查询「李」姓老师的数量

select count(Tname) from Teacher WHERE Tname like ‘李%’

6. 查询学过「张三」老师授课的同学的信息

select a.* from Student a left join SC b on a.sid=b.sid left join Course c on c.cid=b.cid left join Teacher d on d.tid=c.tid WHERE d.Tname=‘张三’ 有重复

select a.* from Student a,SC b,Course c,Teacher d where a.sid=b.sid and b.cid =c.cid and c.tid=d.tid and d.Tname=‘张三’

7. 查询没有学全所有课程的同学的信息

SELECT
a.sid,
a.Sname,
COUNT ( c.Cid ) ‘选课总数’,
SUM ( b.score ) ‘所有课程总分数’
FROM
Student a
FULL JOIN sc b ON a.sid= b.sid
LEFT JOIN Course c ON c.cid= b.cid
GROUP BY
a.sid,
a.Sname HAVING COUNT ( c.Cid )❤️

8. 查询和” 01 “号的同学学习的课程完全相同的其他同学的信息
   这道题号称是所有题目里最难的一道，我虽然做了出来，但是写法很麻烦，不必要。原作者写的很简洁：

select a.sid,COUNT(c.cname) from Student a left join SC b on a.sid=b.sid left join Course c on c.cid=b.cid WHERE a.sid=1 GROUP BY a.sid,a.sname !=
select a.sid,COUNT(c.cname) from Student a left join SC b on a.sid=b.sid left join Course c on c.cid=b.cid WHERE a.sid<>1 GROUP BY a.sid,a.sname
没有做出来留后

正确解答 赋值比较数字
select g.* from (select a.sid,a.Sname,sum (case when c.cname=‘数学’ then 1 when c.cname=‘英语’ then 2 when c.cname=‘语文’ then 4 end ) as v
from Student a left join SC b on a.sid=b.sid left join Course c on c.cid=b.cid GROUP BY a.sid,a.sname ) g WHERE g.v=(
select sum(case when c.cname=‘数学’ then 1 when c.cname=‘英语’ then 2 when c.cname=‘语文’ then 4 end )
from Student a left join SC b on a.sid=b.sid left join Course c on c.cid=b.cid WHERE a.sid=1
) and g.sid != 1

第二种 方法比较字符串 先根据a.sid ,c.cid 排序查询信息 然后用
select * from Student a LEFT JOIN SC b on a.Sid=b.Sid LEFT JOIN Course c on b.Cid=c.Cid order by a.sid ,c.cid

9. 查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息
   没有做出来留后

10. 查询没学过”张三”老师讲授的任一门课程的学生姓名

select a.* from Student a where Sid not in
(select b.sid from SC b, Course c, Teacher d where b.cid = c.cid and c.tid = d.tid and d.Tname =‘张三’) order by a.sid

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

SELECT
Student.sid,
Student.Sname,
AVG ( SC.score ) ‘平均成绩’
FROM
Student
JOIN SC ON Student.sid= SC.sid GROUP BY Student.sid,
Student.Sname
HAVING
Student.Sid in (
SELECT
g.sid
FROM
(
SELECT
a.sid
FROM
Student a
FULL JOIN SC b ON a.sid= b.sid
LEFT JOIN Course c ON c.cid= b.cid
WHERE
b.score < 60
) g
GROUP BY
g.sid
HAVING
g.sid> 2
)

12. 检索” 01 “课程分数小于 60，按分数降序排列的学生信息

select a.,b. from Student a LEFT JOIN SC b on a.sid=b.sid LEFT JOIN Course c on b.cid=c.cid WHERE b.score< 60 and c.cid=1 ORDER BY b.score DESC

13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select avg(g.score) ‘平均分’ from (
select a.sid,a.Sname,a.Sage,a.ssex,b.score,c.Cname from Student a LEFT JOIN SC b on a.sid=b.sid LEFT JOIN Course c on b.cid=c.cid GROUP BY a.sid,a.Sname,a.Sage,a.ssex,b.score,c.Cname
) g GROUP BY g.Sname,g.sid,g.Sage,g.ssex,g.score,g.Cname ORDER BY avg(g.score) DESC
错误

正确写法 但是还可以优化
select h.sid,h.sname,h.avgScore,j.score ‘语文’,k.score ‘数学’,l.score ‘英语’ from
(select a.sid,a.Sname,avg(b.score) avgScore from Student a LEFT JOIN SC b on a.sid=b.sid LEFT JOIN Course c on b.cid=c.cid GROUP BY a.sid,a.Sname) as h
LEFT JOIN
(select a.sid,a.Sname,b.score,c.Cname ‘语文’ from Student a LEFT JOIN SC b on a.sid=b.sid LEFT JOIN Course c on b.cid=c.cid GROUP BY a.sid,a.Sname,b.score,c.Cname,c.Cid HAVING c.Cid=2) as j
on h.Sid=j.Sid LEFT JOIN
(select a.sid,a.Sname,b.score,c.Cname ‘数学’ from Student a LEFT JOIN SC b on a.sid=b.sid LEFT JOIN Course c on b.cid=c.cid GROUP BY a.sid,a.Sname,b.score,c.Cname,c.Cid HAVING c.Cid=1) as k on j.sid=k.Sid LEFT JOIN
(select a.sid,a.Sname,b.score,c.Cname ‘英语’ from Student a LEFT JOIN SC b on a.sid=b.sid LEFT JOIN Course c on b.cid=c.cid GROUP BY a.sid,a.Sname,b.score,c.Cname,c.Cid HAVING c.Cid=3) as l on l.Sid=k.sid ORDER BY avgScore desc

14. 查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c.Cid,c.Cname,max(b.score) ‘最高分数’,min(b.score) ‘最低分’,avg(b.score) ‘平均分’ FROM SC b FULL JOIN Course c on c.cid=b.cid GROUP BY c.Cname,c.Cid

SELECT
c.Cname,
b.cid,
max( b.score ) AS 最高分,
min( b.score ) AS 最低分,
avg( b.score ) AS 平均分,
count( b.Sid ) AS 选修人数,
sum( CASE WHEN b.score >= 60 THEN 1 ELSE 0 END ) / count( b.Sid ) AS 及格率,
sum( CASE WHEN b.score >= 70 AND b.score < 80 THEN 1 ELSE 0 END ) / count( b.Sid ) AS 中等率,
sum( CASE WHEN b.score >= 80 AND b.score < 90 THEN 1 ELSE 0 END ) / count( b.Cid ) AS 优良率,
sum( CASE WHEN b.score >= 90 THEN 1 ELSE 0 END ) / count(b.Cid) AS 优秀率
FROM
SC b LEFT JOIN Course c on b.Cid=c.Cid
GROUP BY
b.cid ,
c.Cname
ORDER BY
count( b.Sid ) DESC,
b.cid ASC;

select sum( CASE WHEN b.score >= 60 THEN 1 ELSE 0 END ) AS 及格率 from SC b

select count( b.Sid ) from SC b

有小数展示问题 不知道怎么解决CAST(13.123 as DECIMAL(13,2)) 函数语法错误

15. 按平均成绩进行排序，显示总排名和各科排名，Score 重复时保留名次空缺
    select a.cid, a.sid, a.score, count(b.score)+1 as rank

from sc as a

left join sc as b

on a.score<b.score and a.cid = b.cid

group by a.cid, a.sid,a.score

order by a.cid, rank ASC;

15.1 按平均成绩进行排序，显示总排名和各科排名，Score 重复时合并名次

select a.* ,count(b.score)+1 rank from sc a left join sc b
on a.cid = b.cid and (a.score < b.score or (a.score = b.score and a.sid > b.sid))
group by a.cid,a.sid
order by a.cid,count(b.score)

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

select m.cid , m.Cname , (
case when n.score >= 85 then ‘85-100’
when n.score >= 70 and n.score < 85 then ‘70-85’
when n.score >= 60 and n.score < 70 then ‘60-70’
else ‘0-60’
end) as px,
count(1)
from Course m , sc n
where m.cid = n.Cid
group by m.Cid , m.Cname , (
case when n.score >= 85 then ‘85-100’
when n.score >= 70 and n.score < 85 then ‘70-85’
when n.score >= 60 and n.score < 70 then ‘60-70’
else ‘0-60’
end)
order by m.Cid , m.Cname , px
*
18. 查询各科成绩前三名的记录

SELECT
c.cname,
a.sid,
a.Sname,
b.score
FROM
Student a
LEFT JOIN Sc b ON a.sid= b.sid
LEFT JOIN Course c ON b.cid= c.cid
GROUP BY
c.cname,
a.Sname,
a.sid,
b.score
HAVING
a.sid != 8
ORDER BY
c.cname,
b.score DESC

top 3 的话有分数一样的不知道咋办

20. 查询出只选修两门课程的学生学号和姓名
    SELECT
    a.sid,
    a.Sname,
    COUNT ( c.Cid ) ‘选课总数’
    FROM
    Student a
    FULL JOIN sc b ON a.sid= b.sid
    LEFT JOIN Course c ON c.cid= b.cid
    GROUP BY
    a.sid,
    a.Sname HAVING COUNT ( c.Cid )=2

21. 查询名字中含有「风」字的学生信息
    select * from Student WHERE Sname like ‘%风%’

22. 查询 1990 年出生的学生名单

select * from Student WHERE Year(sage) =1990

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select top 1 * from Student a left join SC b on a.sid=b.sid left join Course c on b.cid=c.cid left join Teacher d on c.Tid=d.Tid WHERE d.tname=‘张三’ ORDER BY b.score DESC

34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select * from Student a left join SC b on a.sid=b.sid WHERE b.score=(
select top 1 b.score from Student a left join SC b on a.sid=b.sid left join Course c on b.cid=c.cid left join Teacher d on c.Tid=d.Tid WHERE d.tname=‘张三’ ORDER BY b.score DESC
)

40. 查询各学生的年龄，只按年份来算

select Year(GETDATE())-Year(a.Sage) FROM Student a