一个项目涉及到的个sql语句 弄懂后做项目没压力了

1、/p/1668756067说明：以下五十个语句都按照测试数据进行过测试，最好每次只单独运行一个语句。 问题及描述： -1.学生表 Student(Sno,Sname,Sage,Ssex) -Sno 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 -2.课程表 Course(Cno,Cname,Tno) -Cno -课程编号,Cname 课程名称,Tno 教师编号 -3.教师表 Teacher(Tno,Tname) -Tno 教师编号,Tname 教师姓名 -4.成绩表 SC(Sno,Cno,Score) -Sno 学生编号,Cno 课程编号,score 分数 */-创建

2、测试数据 -1.学生表 Student(Sno,Sname,Sage,Ssex)create table Student(Sno varchar2(10),Sname varchar2(10),Sage date,Ssex varchar2(10)insert into Student(sno,sname,sage,ssex) values(01 , 赵雷 , 1-1月-1990 ,男); insert into Student(sno,sname,sage,ssex) values(02 , 钱电 , 21-12月-1990 ,男); insert into Student(sno,snam

3、e,sage,ssex) values(03 , 孙风 , 20-5月-1990 ,男); insert into Student(sno,sname,sage,ssex) values(04 , 李云 , 6-8月-1990 ,男); insert into Student(sno,sname,sage,ssex) values(05 , 周梅 , 1-12月-1991 , 女); insert into Student(sno,sname,sage,ssex) values(06 , 吴兰 , 1-3月-1992 , 女); insert into Student(sno,sname,sa

4、ge,ssex) values(07 , 郑竹 , 1-7月-1989 , 女); insert into Student(sno,sname,sage,ssex) values(08 , 王菊 , 20-1月-1990 , 女); -2.课程表 Course(Cno,Cname,Tno)create table Course(Cno varchar2(10),Cname nvarchar2(10),Tno varchar2(10)insert into Course(Cno,Cname,Tno)values(01 , 语文 , 02); insert into Course(Cno,Cnam

5、e,Tno) values(02 , 数学 , 01); insert into Course(Cno,Cname,Tno) values(03 , 英语 , 03); -3.教师表 Teacher(Tno,Tname)create table Teacher(Tno varchar2(10),Tname varchar2(10)insert into Teacher(Tno,Tname) values(01 , 张三); insert into Teacher(Tno,Tname) values(02 , 李四); insert into Teacher(Tno,Tname) values(

6、03 , 王五); -4.成绩表 SC(Sno,Cno,Score) create table SC(Sno varchar2(10),Cno varchar2(10),score decimal(18,1)insert into SC(Sno,Cno,score) values(01 , 01 , 80); insert into SC(Sno,Cno,score) values(01 , 02 , 90); insert into SC(Sno,Cno,score) values(01 , 03 , 99); insert into SC(Sno,Cno,score) values(02

7、, 01 , 70); insert into SC(Sno,Cno,score) values(02 , 02 , 60); insert into SC(Sno,Cno,score) values(02 , 03 , 80); insert into SC(Sno,Cno,score) values(03 , 01 , 80); insert into SC(Sno,Cno,score) values(03 , 02 , 80); insert into SC(Sno,Cno,score) values(03 , 03 , 80); insert into SC(Sno,Cno,score

8、) values(04 , 01 , 50); insert into SC(Sno,Cno,score) values(04 , 02 , 30); insert into SC(Sno,Cno,score) values(04 , 03 , 20); insert into SC(Sno,Cno,score) values(05 , 01 , 76); insert into SC(Sno,Cno,score) values(05 , 02 , 87); insert into SC(Sno,Cno,score) values(06 , 01 , 31); insert into SC(S

9、no,Cno,score) values(06 , 03 , 34); insert into SC(Sno,Cno,score) values(07 , 02 , 89); insert into SC(Sno,Cno,score) values(07 , 03 , 98);-1、查询01课程比02课程成绩高的学生的信息及课程分数-1.1、查询同时存在01课程和02课程的情况select a.* , b.score 课程01的分数,c.score 课程02的分数from Student a , SC b , SC c where a.Sno = b.Sno and a.Sno = c.Sno

10、 and b.Cno =01and c.Cno =02and b.score c.score-1.2、查询同时存在01课程和02课程的情况和存在01课程但可能不存在02课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)select a.* , b.score 课程01的分数,c.score 课程02的分数from Student a leftjoin SC b on a.Sno = b.Sno and b.Cno =01leftjoin SC c on a.Sno = c.Sno and c.Cno =02where b.score isnull(c.score,0)-2、查询

11、01课程比02课程成绩低的学生的信息及课程分数-2.1、查询同时存在01课程和02课程的情况select a.* , b.score 课程01的分数,c.score 课程02的分数from Student a , SC b , SC c where a.Sno = b.Sno and a.Sno = c.Sno and b.Cno =01and c.Cno =02and b.score c.score-2.2、查询同时存在01课程和02课程的情况和不存在01课程但存在02课程的情况select a.* , b.score 课程01的分数,c.score 课程02的分数from Student

12、a leftjoin SC b on a.Sno = b.Sno and b.Cno =01leftjoin SC c on a.Sno = c.Sno and c.Cno =02whereisnull(b.score,0) =60orderby a.Sno-4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩-4.1、查询在sc表存在成绩的学生信息的SQL语句。select a.Sno , a.Sname , cast(avg(b.score) asdecimal(18,2) avg_scorefrom Student a , sc bwhere a.Sno = b.Snogrou

13、pby a.Sno , a.Snamehavingcast(avg(b.score) asdecimal(18,2) 60orderby a.Sno-4.2、查询在sc表中不存在成绩的学生信息的SQL语句。select a.Sno , a.Sname , isnull(cast(avg(b.score) asdecimal(18,2),0) avg_scorefrom Student a leftjoin sc bon a.Sno = b.Snogroupby a.Sno , a.Snamehavingisnull(cast(avg(b.score) asdecimal(18,2),0) 60

14、orderby a.Sno-5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩-5.1、查询所有有成绩的SQL。select a.Sno 学生编号, a.Sname 学生姓名, count(b.Cno) 选课总数, sum(score) 所有课程的总成绩from Student a , SC bwhere a.Sno = b.Snogroupby a.Sno,a.Snameorderby a.Sno-5.2、查询所有(包括有成绩和无成绩)的SQL。select a.Sno 学生编号, a.Sname 学生姓名, count(b.Cno) 选课总数, sum(score) 所有课程

15、的总成绩from Student a leftjoin SC bon a.Sno = b.Snogroupby a.Sno,a.Snameorderby a.Sno-6、查询李姓老师的数量-方法1selectcount(Tname) 李姓老师的数量from Teacher where Tname like N李%-方法2selectcount(Tname) 李姓老师的数量from Teacher whereleft(Tname,1) = N李-7、查询学过张三老师授课的同学的信息selectdistinct Student.*from Student , SC , Course , Teach

16、erwhere Student.Sno = SC.Sno and SC.Cno = Course.Cno and Course.Tno = Teacher.Tno and Teacher.Tname = N张三orderby Student.Sno-8、查询没学过张三老师授课的同学的信息select m.*from Student m where Sno notin (selectdistinct SC.Sno from SC , Course , Teacher where SC.Cno = Course.Cno and Course.Tno = Teacher.Tno and Teache

17、r.Tname = N张三) orderby m.Sno-9、查询学过编号为01并且也学过编号为02的课程的同学的信息-方法1select Student.*from Student , SC where Student.Sno = SC.Sno and SC.Cno =01andexists (Select1from SC SC_2 where SC_2.Sno = SC.Sno and SC_2.Cno =02) orderby Student.Sno-方法2select Student.*from Student , SC where Student.Sno = SC.Sno and S

18、C.Cno =02andexists (Select1from SC SC_2 where SC_2.Sno = SC.Sno and SC_2.Cno =01) orderby Student.Sno-方法3select m.*from Student m where Sno in(select Sno from(selectdistinct Sno from SC where Cno =01unionallselectdistinct Sno from SC where Cno =02) t groupby Sno havingcount(1) =2)orderby m.Sno-10、查询

19、学过编号为01但是没有学过编号为02的课程的同学的信息-方法1select Student.*from Student , SC where Student.Sno = SC.Sno and SC.Cno =01andnotexists (Select1from SC SC_2 where SC_2.Sno = SC.Sno and SC_2.Cno =02) orderby Student.Sno-方法2select Student.*from Student , SC where Student.Sno = SC.Sno and SC.Cno =01and Student.Sno noti

20、n (Select SC_2.Sno from SC SC_2 where SC_2.Sno = SC.Sno and SC_2.Cno =02) orderby Student.Sno-11、查询没有学全所有课程的同学的信息-11.1、select Student.*from Student , SCwhere Student.Sno = SC.Snogroupby Student.Sno , Student.Sname , Student.Sage , Student.Ssex havingcount(Cno) (selectcount(Cno) from Course)-11.2sele

21、ct Student.*from Student leftjoin SCon Student.Sno = SC.Snogroupby Student.Sno , Student.Sname , Student.Sage , Student.Ssex havingcount(Cno) (selectcount(Cno) from Course)-12、查询至少有一门课与学号为01的同学所学相同的同学的信息selectdistinct Student.*from Student , SC where Student.Sno = SC.Sno and SC.Cno in (select Cno fr

22、om SC where Sno =01) and Student.Sno 01-13、查询和01号的同学学习的课程完全相同的其他同学的信息select Student.*from Student where Sno in(selectdistinct SC.Sno from SC where Sno 01and SC.Cno in (selectdistinct Cno from SC where Sno =01)groupby SC.Sno havingcount(1) = (selectcount(1) from SC where Sno=01)-14、查询没学过张三老师讲授的任一门课程的

23、学生姓名select student.*from student where student.Sno notin(selectdistinct sc.Sno from sc , course , teacher where sc.Cno = course.Cno and course.Tno = teacher.Tno and teacher.tname = N张三)orderby student.Sno-15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 select student.Sno , student.sname , cast(avg(score) asdecimal(

24、18,2) avg_score from student , sc where student.Sno = SC.Sno and student.Sno in (select Sno from SC where score =2)groupby student.Sno , student.sname-16、检索01课程分数小于60，按分数降序排列的学生信息select student.* , sc.Cno , sc.score from student , sc where student.Sno = SC.Sno and sc.score =60，中等为：70-80，优良为：80-90，优秀

25、为：=90-方法1select m.Cno 课程编号, m.Cname 课程名称,max(n.score) 最高分,min(n.score) 最低分,cast(avg(n.score) asdecimal(18,2) 平均分,cast(selectcount(1) from SC where Cno = m.Cno and score =60)*100.0/ (selectcount(1) from SC where Cno = m.Cno) asdecimal(18,2) 及格率(%),cast(selectcount(1) from SC where Cno = m.Cno and sco

26、re =70and score =80and score =90)*100.0/ (selectcount(1) from SC where Cno = m.Cno) asdecimal(18,2) 优秀率(%)from Course m , SC nwhere m.Cno = n.Cnogroupby m.Cno , m.Cnameorderby m.Cno-方法2select m.Cno 课程编号, m.Cname 课程名称,(selectmax(score) from SC where Cno = m.Cno) 最高分,(selectmin(score) from SC where Cn

27、o = m.Cno) 最低分,(selectcast(avg(score) asdecimal(18,2) from SC where Cno = m.Cno) 平均分,cast(selectcount(1) from SC where Cno = m.Cno and score =60)*100.0/ (selectcount(1) from SC where Cno = m.Cno) asdecimal(18,2) 及格率(%),cast(selectcount(1) from SC where Cno = m.Cno and score =70and score =80and score

28、 =90)*100.0/ (selectcount(1) from SC where Cno = m.Cno) asdecimal(18,2) 优秀率(%)from Course morderby m.Cno-19、按各科成绩进行排序，并显示排名-19.1 sql 2000用子查询完成-Score重复时保留名次空缺select t.* , px = (selectcount(1) from SC where Cno = t.Cno and score t.score) +1from sc t orderby o , px-Score重复时合并名次select t.* , px = (selec

29、tcount(distinct score) from SC where Cno = t.Cno and score = t.score) from sc t orderby o , px-19.2 sql 2005用rank,DENSE_RANK完成-Score重复时保留名次空缺(rank完成)select t.* , px = rank() over(partition by cno orderby score desc) from sc t orderby t.Cno , px-Score重复时合并名次(DENSE_RANK完成)select t.* , px = DENSE_RANK(

30、) over(partition by cno orderby score desc) from sc t orderby t.Cno , px-20、查询学生的总成绩并进行排名-20.1 查询学生的总成绩select m.Sno 学生编号 ,m.Sname 学生姓名 ,isnull(sum(score),0) 总成绩from Student m leftjoin SC n on m.Sno = n.Snogroupby m.Sno , m.Snameorderby总成绩desc-20.2 查询学生的总成绩并进行排名，sql 2000用子查询完成，分总分重复时保留名次空缺和不保留名次空缺两种。

31、select t1.* , px = (selectcount(1) from(select m.Sno 学生编号 ,m.Sname 学生姓名 ,isnull(sum(score),0) 总成绩from Student m leftjoin SC n on m.Sno = n.Snogroupby m.Sno , m.Sname) t2 where 总成绩 t1.总成绩) +1from(select m.Sno 学生编号 ,m.Sname 学生姓名 ,isnull(sum(score),0) 总成绩from Student m leftjoin SC n on m.Sno = n.Snogro

32、upby m.Sno , m.Sname) t1orderby pxselect t1.* , px = (selectcount(distinct 总成绩) from(select m.Sno 学生编号 ,m.Sname 学生姓名 ,isnull(sum(score),0) 总成绩from Student m leftjoin SC n on m.Sno = n.Snogroupby m.Sno , m.Sname) t2 where 总成绩 = t1.总成绩) from(select m.Sno 学生编号 ,m.Sname 学生姓名 ,isnull(sum(score),0) 总成绩fro

33、m Student m leftjoin SC n on m.Sno = n.Snogroupby m.Sno , m.Sname) t1orderby px-20.3 查询学生的总成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分总分重复时保留名次空缺和不保留名次空缺两种。select t.* , px = rank() over(orderby总成绩desc) from(select m.Sno 学生编号 , m.Sname 学生姓名 ,isnull(sum(score),0) 总成绩from Student m leftjoin SC n on m.Sno = n.

34、Sno groupby m.Sno , m.Sname) torderby pxselect t.* , px = DENSE_RANK() over(orderby总成绩desc) from(select m.Sno 学生编号 , m.Sname 学生姓名 ,isnull(sum(score),0) 总成绩from Student m leftjoin SC n on m.Sno = n.Sno groupby m.Sno , m.Sname) torderby px-21、查询不同老师所教不同课程平均分从高到低显示select m.Tno , m.Tname , cast(avg(o.sc

35、ore) asdecimal(18,2) avg_scorefrom Teacher m , Course n , SC owhere m.Tno = n.Tno and n.Cno = o.Cnogroupby m.Tno , m.Tnameorderby avg_score desc-22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩-22.1 sql 2000用子查询完成-Score重复时保留名次空缺select*from (select t.* , px = (selectcount(1) from SC where Cno = t.Cno and score t.score

36、) +1from sc t) m where px between2and3orderby o , m.px-Score重复时合并名次select*from (select t.* , px = (selectcount(distinct score) from SC where Cno = t.Cno and score = t.score) from sc t) m where px between2and3orderby o , m.px -22.2 sql 2005用rank,DENSE_RANK完成-Score重复时保留名次空缺(rank完成)select*from (select

37、t.* , px = rank() over(partition by cno orderby score desc) from sc t) m where px between2and3orderby m.Cno , m.px-Score重复时合并名次(DENSE_RANK完成)select*from (select t.* , px = DENSE_RANK() over(partition by cno orderby score desc) from sc t) m where px between2and3orderby m.Cno , m.px-23、统计各科成绩各分数段人数：课程

38、编号,课程名称,100-85,85-70,70-60,0-60及所占百分比-23.1 统计各科成绩各分数段人数：课程编号,课程名称,100-85,85-70,70-60,0-60-横向显示select Course.Cno 课程编号 , Cname as课程名称 ,sum(casewhen score =85then1else0end) 85-100,sum(casewhen score =70and score =60and score 70then1else0end) 60-70,sum(casewhen score =85then85-100when n.score =70and n.s

39、core =60and n.score =85then85-100when n.score =70and n.score =60and n.score =85then85-100when n.score =70and n.score =60and n.score =85then85-100when n.score =70and n.score =60and n.score 70then60-70else0-60end)orderby m.Cno , m.Cname , 分数段-23.2 统计各科成绩各分数段人数：课程编号,课程名称,100-85,85-70,70-60,60及所占百分比-横向显

40、示select m.Cno 课程编号, m.Cname 课程名称,(selectcount(1) from SC where Cno = m.Cno and score 60) 0-60,cast(selectcount(1) from SC where Cno = m.Cno and score =60and score =60and score =70and score =70and score =85) 85-100,cast(selectcount(1) from SC where Cno = m.Cno and score =85)*100.0/ (selectcount(1) fr

41、om SC where Cno = m.Cno) asdecimal(18,2) 百分比(%)from Course morderby m.Cno-纵向显示1(显示存在的分数段)select m.Cno 课程编号 , m.Cname 课程名称 , 分数段 = (casewhen n.score =85then85-100when n.score =70and n.score =60and n.score =85then85-100when n.score =70and n.score =60and n.score =85then85-100when n.score =70and n.score

42、 =60and n.score =85then85-100when n.score =70and n.score =60and n.score t1.平均成绩) +1from (select m.Sno 学生编号 , m.Sname 学生姓名 ,isnull(cast(avg(score) asdecimal(18,2),0) 平均成绩from Student m leftjoin SC n on m.Sno = n.Sno groupby m.Sno , m.Sname) t1orderby pxselect t1.* , px = (selectcount(distinct 平均成绩) from (select m.Sno 学生编号 , m.Sname 学生姓名 ,isnull(cast(avg(score) asdecimal(18,2),0) 平均成绩from Student m leftjoin SC n on m.Sno = n.Sno groupby m.Sno , m.Sname) t2 where 平均成绩 = t1.平均成绩) from (select m.Sno 学生编号 , m.Sname 学生姓名 ,isnull(cast(avg(sc