关于SQL Server语句入门级应用阶段性学习——找工作必备（一）

最新推荐文章于 2022-12-09 10:30:29 发布

置顶 lz_N_one

最新推荐文章于 2022-12-09 10:30:29 发布

阅读量326

点赞数

分类专栏： sql语句文章标签： sql 数据库

本文链接：https://blog.csdn.net/lz_N_one/article/details/105439643

版权

sql语句专栏收录该内容

19 篇文章 1 订阅

订阅专栏

第一阶段练习

*************** 本章节适合零基础学习者或有一定基础但是对知识点掌握不牢靠者观看******************************
以下练习题是根据初始化到数据库中的表执行的，请务必先执行如下建表语句。

create table s1_student( sno varchar2(10) primary key, sname varchar2(30), sbirthday varchar2(30), ssex varchar2(10),sclass varchar2(10) ); 
create table s1_teacher(  tno varchar2(10) primary key, tname varchar2(30),tsex varchar2(10),tbirthday varchar2(30),prof varchar2(30),depart varchar2(30) );  
create table s1_course( cno varchar2(10), cname varchar2(30),  tno varchar2(10) ); 
create table s1_score( sno varchar2(10), cno varchar2(10),  degree number );
create table s1_grade( low number, upp number,  rank varchar2(2) );

*/初始化学生表s1_student/

insert into s1_student values ('108', '曾华', '1997/9/1', '男', '95033');
insert into s1_student values ('105', '匡明', '1995/10/2', '男', '95031');
insert into s1_student values ('107', '王丽', '1996/1/23', '女', '95033');
insert into s1_student values ('101', '李军', '1996/2/20', '男', '95033');
insert into s1_student values ('109', '王芳', '1995/2/10', '女', '95031');
insert into s1_student values ('103', '陆君', '1994/6/3', '男', '95031');
commit;

/初始化教师表s1_teacher/

insert into s1_teacher values ('804', '李诚','男','1979-12-2','副教授','计算机系'); 
insert into s1_teacher values ('856', '张旭','男','1985-3-12','讲师','电子工程系'); 
insert into s1_teacher values ('825', '王萍','女','1989-5-2','助教','计算机系'); 
insert into s1_teacher values ('831', '刘冰','女','1988-8-2','助教','电子工程系'); 
commit; 
/*******初

始化课程表s1_course********/

insert into s1_course values ('3-105', '计算机导论', '825');
insert into s1_course values ('3-245', '操作系统', '804');
insert into s1_course values ('6-166', '数据电路', '856');
insert into s1_course values ('9-888', '高等数学', '800');
commit;

/初始化成绩表s1_score*/

insert into s1_score values ('103', '3-245', 86);
insert into s1_score values ('105', '3-245', 75);
insert into s1_score values ('109', '3-245', 68);
insert into s1_score values ('103', '3-105', 92);
insert into s1_score values ('105', '3-105', 88);
insert into s1_score values ('109', '3-105', 76);
insert into s1_score values ('101', '3-105', 64);
insert into s1_score values ('107', '3-105', 91);
insert into s1_score values ('108', '3-105', 78);
insert into s1_score values ('101', '6-166', 85);
insert into s1_score values ('107', '6-106', 79);
insert into s1_score values ('108', '6-166', 81);
commit;

/初始化成绩分类表s1_grade*/

insert into s1_grade values ('90', '100', 'A');
insert into s1_grade values ('80', '89', 'B');
insert into s1_grade values ('70', '79', 'C');
insert into s1_grade values ('60', '69', 'D');
insert into s1_grade values ('0', '59', 'E');
commit;

1.查询s1_student表中的所有记录的Sname、Ssex和Class列.

select sname,ssex,sclass from s1_student;

2.查询教师所有的单位即不重复的Depart列.

select distinct depart from s1_teacher;

3.查询s1_student表的所有记录.

select * from s1_student;

4.查询s1_score表中成绩在60到80之间的所有记录.

select * from s1_score where degree between 60 and 80;

5.查询s1_score表中成绩为85，86或88的记录.

select * from s1_score where degree in (85,86,88);

6.查询s1_student表中“95031”班或性别为“女”的同学记录.

select * from s1_student where sclass='95031' or ssex='女';

7.以Class降序查询s1_student表的所有记录.

select * from s1_student order by sclass desc;

8.以Cno升序、Degree降序查询s1_score表的所有记录。

select * from s1_score order by cno,degree desc;

9.查询“95031”班的学生人数。

select count(sno) from s1_student where sclass='95031';

10.查询s1_score表中的最高分的学生学号和课程号。

select sno,cno from s1_score where degree =(select max(degree) from s1_score);

11.查询‘3-105’号课程的平均分。

select avg(degree) from s1_score where cno='3-105';

12.查询s1_score表中至少有5名学生选修的并以3开头的课程的平均分数。

select cno,avg(degree) from s1_score where cno like '3%' group by cno having count(sno)>5;

13.查询最低分大于70，最高分小于90的Sno列。

select sno from s1_score group by sno having min(degree)>70 and max(degree)<90;

14.查询所有学生的Sname、Cno和Degree列。

select sname,cno ,degree from s1_student st,s1_score sc where st.sno=sc.sno(+);

15.查询所有学生的Sno、Cname和Degree列。

select sno,cname,degree from s1_score sc,s1_course co where sc.cno(+)=co.cno;

16.查询所有学生的Sname、Cname和Degree列。

select sname,cname,degree from s1_student st,s1_score sc,s1_course co where  st.sno=sc.sno(+) and sc.cno=co.cno(+);

17.查询“95033”班所选课程的平均分。

select avg(degree) from s1_score sc,s1_student st where st.sno=sc.sno and sclass='95033';

18.现查询所有同学的Sno、Cno和rank列。

select sno,cno,degree,(select rank from s1_grade where low<=degree and upp>=degree)as rank  from s1_score;

19.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

(1)select * from s1_score where cno='3-105' and degree >(select degree from s1_score where cno='3-105' and sno='109');
(2)select x.CNO ,x.SNO ,x.DEGREE  from s1_score  x,s1_score  y
 where x.CNO ='3-105' and x.DEGREE >y.DEGREE and y.SNO ='109'and y.CNO='3-105';

20.查询s1_score中选学一门以上课程的同学中分数为非最高分成绩的记录。

select * from s1_score j  where j.SNO  in (
       select SNO  from s1_score group by SNO having count(*)>=2) and 
              j.DEGREE <(select max(DEGREE)from s1_score b where b.CNO = j.CNO );

21.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

select * from s1_score where degree>(select degree from s1_score where sno='109'and cno='3-105') and cno='3-105';

22.查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

select sno,sname,sbirthday from s1_student st1 where substr(sbirthday,1,4)in (select substr(sbirthday,1,4) from s1_student where sno='101');

23.查询“张旭“教师任课的学生成绩。

select sc.* from s1_score sc,s1_teacher th,s1_course co where sc.cno=co.cno and co.tno=th.tno and th.tname='张旭';

24.查询选修某课程的同学人数多于5人的教师姓名。

(1)select tname from s1_teacher th,s1_course co where th.tno=co.tno and co.cno in       (select cno from s1_score group by cno having count(sno)>5);
(2)select TNAME  from s1_teacher where TNO in(
     select x.TNO from s1_course x inner join s1_score y on x.CNO =y.CNO 
            group by x.TNO having count(x.TNO )>5);

25.查询95033班和95031班全体学生的记录。

select * from s1_student where sclass in('95033','95031');

26.查询存在有85分以上成绩的课程Cno.

select cno from s1_score group by cno having max(degree)>=85;

27.查询出“计算机系“教师所教课程的成绩表。

select sc.* from s1_score sc,s1_teacher th,s1_course co where sc.cno=co.cno and co.tno=th.tno and th.depart='计算机系';

28.查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

select TNAME,PROF from s1_teacher  where DEPART ='计算机系' and PROF not in(
       select PROF from s1_teacher where DEPART ='电子工程系');

29.查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

(1)select sno,cno,degree from s1_score where cno='3-105' and degree >(
         select min(degree) from s1_score where cno='3-245')order by degree desc;
(2)select * from s1_score where CNO ='3-105' and DEGREE >any (
          select DEGREE  from s1_score where CNO ='3-245')order by DEGREE desc;

30.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

(1)select sno,cno,degree from s1_score where cno='3-105' and degree >(
         select max(degree) from s1_score where cno='3-245')order by degree desc;
(2)select * from s1_score where CNO ='3-105' and DEGREE >all(
         select DEGREE  from s1_score where CNO ='3-245');

31.查询所有教师和同学的name、sex和birthday.

select sname as name,ssex as sex from s1_student
union all
select tname as name,tsex as sex from s1_teacher;

32.查询所有“女”教师和“女”同学的name、sex和birthday.

select sname as name,ssex as sex,sbirthday as birthday from s1_student where ssex='女'
union all
select tname as name,tsex as sex,tbirthday as birthday from s1_teacher where tsex='女';

33.查询成绩比该课程平均成绩低的同学的成绩表。

(1)select sc1.* from s1_score sc1,(select cno,avg(degree)as degree from s1_score group   by cno) sc2   where sc1.cno=sc2.cno and sc1.degree<sc2.degree;
(2)select * from s1_score a where DEGREE <(
        select avg(DEGREE) from s1_score b where a.CNO=b.CNO);

34.查询所有任课教师的Tname和Depart.

(1)select tname,depart from s1_teacher th,s1_course co where th.tno=co.tno;
(2)select TNAME,DEPART from s1_teacher where exists
     (select * from s1_course  where s1_course.TNO = s1_teacher.TNO );

35.查询所有未讲课的教师的Tname和Depart.

(1)select tname,depart from s1_teacher th where th.tno not in (
          select tno from s1_course);
(2)select tname,depart from s1_teacher th where not exists (
          select tno from s1_course co where th.tno=co.tno);

36.查询至少有2名男生的班号。

select sclass from s1_student where ssex='男' group by sclass having count(sno)>=2;

37.查询s1_student表中不姓“王”的同学记录。

select * from s1_student where sname not like '王%';

38.查询s1_student表中每个学生的姓名和年龄。

select sname,to_char(sysdate,'yyyy')-substr(sbirthday,1,4) as tage from s1_student;

39.查询s1_student表中最大和最小的Sbirthday日期值。

select max(sbirthday),min(sbirthday) from s1_student;

40.以班号和年龄从大到小的顺序查询s1_student表中的全部记录。

select * from s1_student order by sclass desc,sbirthday asc;

41.查询“男”教师及其所上的课程。

select th.tno,th.tname,th.tsex,co.cno from s1_teacher th,s1_course co where th.tno=co.tno and th.tsex='男';

42.查询最高分同学的Sno、Cno和Degree列。

select * from s1_score where degree =(select max(degree) from s1_score);

43.查询和“李军”同性别的所有同学的Sname.

select sname from s1_student where ssex =(select ssex from s1_student where sname='李军') and sname<>'李军';

44.查询和“李军”同性别并同班的同学Sname.

(1)select sname from s1_student st1 where exists 
    (select *  from s1_student st2 where st2.sname='李军' and st1.sclass=st2.sclass and st1.ssex=st2.ssex);
(2)select SNAME from s1_student where SSEX =(select SSEX from s1_student where SNAME ='李军') and SCLASS =(select SCLASS from s1_student where SNAME ='李军');

45.查询所有选修“计算机导论”课程的“男”同学的成绩表

(1)select sc.* from s1_score sc,s1_course co,s1_student st 
         where sc.sno=st.sno and sc.cno=co.cno and co.cname='计算机导论' and st.ssex='男';
(2)select * from s1_score  where SNO  in(select SNO  from s1_STUDENT  where SSEX ='男') 
         and CNO =(select CNO  from s1_COURSE  where CNAME ='计算机导论');