数据库基础

点击下载附件 test.zip

1、查询Student表中的所有记录的Sname、Ssex和Class列。
SQL> select s_name, s_sex, s_class from student;
S_NAME S_SEX S_CLASS

曾华男 95033
匡明男 95031
王丽女 95033
李军男 95033
王芳女 95031
陆君男 95031
6 rows selected

2、查询教师所有的单位即不重复的Depart列。
SQL> select distinct t_depart from teacher;
T_DEPART

电子工程系
计算机系

3、查询Student表的所有记录。
SQL> select * from student;
S_NO S_NAME S_SEX S_BIRTHDAY S_CLASS

108 曾华男 1977/9/1 95033
105 匡明男 1975/10/2 95031
107 王丽女 1976/1/23 95033
101 李军男 1976/2/20 95033
109 王芳女 1975/2/10 95031
103 陆君男 1974/6/3 95031
6 rows selected

4、查询Score表中成绩在60到80之间的所有记录。
SQL> select * from score where degree between 60 and 80;
S_NO C_NO DEGREE

105 3-245 75.0
109 3-245 68.0
109 3-105 76.0
101 3-105 64.0
108 3-105 78.0
107 6-166 79.0
6 rows selected

5、查询Score表中成绩为85，86或88的记录。
SQL> select * from score where degree in (85, 86, 88);
S_NO C_NO DEGREE

103 3-245 86.0
105 3-105 88.0
101 6-166 85.0

6、查询Student表中“95031”班或性别为“女”的同学记录。
SQL> select * from student where s_class='95031' or s_sex='女';
S_NO S_NAME S_SEX S_BIRTHDAY S_CLASS

105 匡明男 1975/10/2 95031
107 王丽女 1976/1/23 95033
109 王芳女 1975/2/10 95031
103 陆君男 1974/6/3 95031

7、以Class降序查询Student表的所有记录。
SQL> select * from student order by s_class desc;
S_NO S_NAME S_SEX S_BIRTHDAY S_CLASS

108 曾华男 1977/9/1 95033
107 王丽女 1976/1/23 95033
101 李军男 1976/2/20 95033
109 王芳女 1975/2/10 95031
103 陆君男 1974/6/3 95031
105 匡明男 1975/10/2 95031
6 rows selected

8、以Cno升序、Degree降序查询Score表的所有记录。
SQL> select * from score order by c_no asc, degree desc;
S_NO C_NO DEGREE

103 3-105 92.0
107 3-105 91.0
105 3-105 88.0
108 3-105 78.0
109 3-105 76.0
101 3-105 64.0
103 3-245 86.0
105 3-245 75.0
109 3-245 68.0
101 6-166 85.0
108 6-166 81.0
107 6-166 79.0
12 rows selected

9、查询“95031”班的学生人数。
SQL> select count(s_no) from student where s_class='95031';
COUNT(S_NO)

10、查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
SQL> select s.s_no, sc.c_no from student s, score sc where s.s_no=sc.s_no and sc.degree=(select max(degree) from score);
S_NO C_NO

103 3-105

11、查询每门课的平均成绩。
SQL> select c.c_name, round(avg(s.degree), 2) as avg_score from score s, course c where s.c_no=c.c_no group by c.c_name;
C_NAME AVG_SCORE

数字电路 81.67
计算机导论 81.5
操作系统 76.33

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SQL> select s.c_no, round(avg(s.degree), 2) as avg_score from score s where s.c_no like '3%' group by s.c_no having (count(s.s_no)>5);
C_NO AVG_SCORE

3-105 81.5

13、查询分数大于70，小于90的Sno列。
SQL> select s_no from score where degree between 70 and 90;
S_NO DEGREE

103 86.0
105 75.0
105 88.0
109 76.0
108 78.0
101 85.0
107 79.0
108 81.0
8 rows selected

14、查询所有学生的Sname、Cno和Degree列。
SQL> select s.s_name, sc.c_no, sc.degree from student s, score sc where s.s_no=sc.s_no;
S_NAME C_NO DEGREE

陆君 3-245 86.0
匡明 3-245 75.0
王芳 3-245 68.0
陆君 3-105 92.0
匡明 3-105 88.0
王芳 3-105 76.0
李军 3-105 64.0
王丽 3-105 91.0
曾华 3-105 78.0
李军 6-166 85.0
王丽 6-166 79.0
曾华 6-166 81.0
12 rows selected

15、查询所有学生的Sno、Cname和Degree列。
SQL> select sc.s_no, c.c_name, sc.degree from course c, score sc where c.c_no=sc.c_no;
S_NO C_NAME DEGREE

103 操作系统 86.0
105 操作系统 75.0
109 操作系统 68.0
103 计算机导论 92.0
105 计算机导论 88.0
109 计算机导论 76.0
101 计算机导论 64.0
107 计算机导论 91.0
108 计算机导论 78.0
101 数字电路 85.0
107 数字电路 79.0
108 数字电路 81.0
12 rows selected

16、查询所有学生的Sname、Cname和Degree列。
SQL> select s.s_name, c.c_name, sc.degree from student s, course c, score sc where s.s_no=sc.s_no and c.c_no=sc.c_no;
S_NAME C_NAME DEGREE

曾华数字电路 81.0
曾华计算机导论 78.0
匡明计算机导论 88.0
匡明操作系统 75.0
王丽数字电路 79.0
王丽计算机导论 91.0
李军数字电路 85.0
李军计算机导论 64.0
王芳计算机导论 76.0
王芳操作系统 68.0
陆君计算机导论 92.0
陆君操作系统 86.0
12 rows selected

17、查询“95033”班学生的平均分。
SQL> select round(avg(degree), 2) as avg_score from student s, score sc where s.s_no=sc.s_no and s.s_class='95033';
AVG_SCORE

 79.67

18、假设使用如下命令建立了一个grade表：
create table grade(low int(3),upp int(3),rank char(1))
insert into grade values(90,100,’A’)
insert into grade values(80,89,’B’)
insert into grade values(70,79,’C’)
insert into grade values(60,69,’D’)
insert into grade values(0,59,’E’)
现查询所有同学的Sno、Cno和rank列。
SQL> select s_no, c_no, rank from score, grade where degree between low and upp order by rank;
S_NO C_NO RANK

107 3-105 A
103 3-105 A
103 3-245 B
101 6-166 B
108 6-166 B
105 3-105 B
108 3-105 C
109 3-105 C
105 3-245 C
107 6-166 C
101 3-105 D
109 3-245 D
12 rows selected

18、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SQL> select a.* from score a, score b where a.c_no='3-105' and a.degree > b.degree and b.s_no='105' and b.c_no='3-105';
S_NO C_NO DEGREE

103 3-105 92.0
107 3-105 91.0

20、查询score中选学多门课程的同学中分数为非本科最高分成绩的记录。
--各科最高分
SQL> select c_no, max(degree) as max_score from score group by c_no;
C_NO MAX_SCORE

3-245 86
6-166 85
3-105 92
--非各科最高分的所有人
SQL> select a.* from score a, (select c_no, max(degree) as max_score from score group by c_no) b where a.c_no=b.c_no and a.degree<b.max_score order by a.c_no asc, a.degree desc;
S_NO C_NO DEGREE

107 3-105 91.0
105 3-105 88.0
108 3-105 78.0
109 3-105 76.0
101 3-105 64.0
105 3-245 75.0
109 3-245 68.0
108 6-166 81.0
107 6-166 79.0
9 rows selected
--选课数>1的所有人
SQL> select s_no from score group by s_no having count(s_no) > 1;
S_NO

101
105
109
103
108
107
6 rows selected
--选学多门课程的&分数为非各科最高分
SQL> select a.* from score a, (select c_no, max(degree) as max_score from score group by c_no) b where a.c_no=b.c_no and a.degree<b.max_score and s_no in (select s_no from score group by s_no having count(s_no) > 1)order by a.c_no asc, a.degree desc;
S_NO C_NO DEGREE

107 3-105 91.0
105 3-105 88.0
108 3-105 78.0
109 3-105 76.0
101 3-105 64.0
105 3-245 75.0
109 3-245 68.0
108 6-166 81.0
107 6-166 79.0
9 rows selected

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SQL> select a.* from score a, score b where a.degree>b.degree and b.s_no='105' and b.c_no='3-105';
S_NO C_NO DEGREE

103 3-105 92.0
107 3-105 91.0

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SQL> select a.s_no, a.s_name, a.s_birthday from student a, student b where to_char(a.s_birthday, 'yyyy')=to_char(b.s_birthday,'yyyy') and b.s_no='107';
S_NO S_NAME S_BIRTHDAY

107 王丽 1976/1/23
101 李军 1976/2/20

23、查询“张旭“教师任课的学生成绩。
SQL> select s.s_no, c.c_name, t.t_name from score s, course c, teacher t where s.c_no=c.c_no and c.t_no=t.t_no and t.t_name='张旭';
S_NO C_NAME T_NAME

108 数字电路张旭
107 数字电路张旭
101 数字电路张旭

24、查询选修某课程的同学人数多于5人的教师姓名。
SQL> select s.c_no, count(s.s_no) as cnt, t.t_name from score s, course c, teacher t where s.c_no=c.c_no and c.t_no=t.t_no group by s.c_no, t.t_name having count(s.s_no)>5;
C_NO CNT T_NAME

3-105 6 王萍

25、查询95033班和95031班全体学生的记录。
SQL> select s.* from student s where s.s_class in(95033, 95031);
S_NO S_NAME S_SEX S_BIRTHDAY S_CLASS

108 曾华男 1977/9/1 95033
105 匡明男 1975/10/2 95031
107 王丽女 1976/1/23 95033
101 李军男 1976/2/20 95033
109 王芳女 1975/2/10 95031
103 陆君男 1974/6/3 95031
6 rows selected

26、查询存在有85分以上成绩的课程Cno.
SQL> select distinct c_no from score where degree>85;
C_NO

3-245
3-105

27、查询出“计算机系“教师所教课程的成绩表。
SQL> select s.* from course c, teacher t, score s where s.c_no=c.c_no and c.t_no=t.t_no and t.t_depart='计算机系';
S_NO C_NO DEGREE

103 3-245 86.0
105 3-245 75.0
109 3-245 68.0
103 3-105 92.0
105 3-105 88.0
109 3-105 76.0
101 3-105 64.0
107 3-105 91.0
108 3-105 78.0
9 rows selected

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。（各系独有的职称）

相关子查询：内部查询的执行依赖于外部查询的数据，外部查询每执行一次，内部查询也会执行一次。
每一次都是外部查询先执行，取出外部查询表中的一个元组，将当前元组中的数据传递给内部查询，然后执行内部查询。
根据内部查询执行的结果，判断当前元组是否满足外部查询中的where条件，若满足则当前元组是符合要求的记录，否则不符合要求。
然后，外部查询继续取出下一个元组数据，执行上述的操作，直到全部元组均被处理完毕。
SQL> select a.* from teacher a where a.t_prof not in (select t_prof from teacher b where a.t_depart!=b.t_depart);
T_NO T_NAME T_SEX T_BIRTHDAY T_PROF T_DEPART

804 李诚男 1958/12/2 副教授计算机系
856 张旭男 1969/3/12 讲师电子工程系

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学（同一个人）的Cno、Sno和Degree,并按Degree从高到低次序排序。
SQL> select a.* from score a where (select b.degree from score b where a.s_no=b.s_no and b.c_no='3-105') >= (select c.degree from score c where a.s_no=c.s_no and c.c_no='3-245') order by degree asc;
S_NO C_NO DEGREE

109 3-245 68.0
105 3-245 75.0
109 3-105 76.0
103 3-245 86.0
105 3-105 88.0
103 3-105 92.0
6 rows selected

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

31、查询所有教师和同学的name、sex和birthday.
SQL> select s_name, s_sex, s_birthday from student union select t_name, t_sex, t_birthday from teacher;
S_NAME S_SEX S_BIRTHDAY

匡明男 1975/10/2
李诚男 1958/12/2
李军男 1976/2/20
刘冰女 1977/8/14
陆君男 1974/6/3
王芳女 1975/2/10
王丽女 1976/1/23
王萍女 1972/5/5
曾华男 1977/9/1
张旭男 1969/3/12
10 rows selected

32、查询所有“女”教师和“女”同学的name、sex和birthday.
SQL> select s_name, s_sex, s_birthday from student where s_sex='女' union select t_name, t_sex, t_birthday from teacher where t_sex='女';
S_NAME S_SEX S_BIRTHDAY

刘冰女 1977/8/14
王芳女 1975/2/10
王丽女 1976/1/23
王萍女 1972/5/5

33、查询成绩比该课程平均成绩低的同学的成绩表。
SQL> select a.* from score a where a.degree<(select avg(degree) from score);
S_NO C_NO DEGREE

105 3-245 75.0
109 3-245 68.0
109 3-105 76.0
101 3-105 64.0
108 3-105 78.0
107 6-166 79.0
6 rows selected

34、查询所有任课教师的Tname和Depart.
SQL> select distinct t.t_name, t.t_depart from score s, course c, teacher t where c.c_no=s.c_no and c.t_no=t.t_no;
T_NAME T_DEPART

王萍计算机系
张旭电子工程系
李诚计算机系

35 、查询所有未讲课的教师的Tname和Depart.
SQL> select tt.t_name, tt.t_depart from teacher tt where not exists(select t.t_no from score s, course c, teacher t where c.c_no=s.c_no and c.t_no=t.t_no and t.t_no=tt.t_no);
T_NAME T_DEPART

刘冰电子工程系

36、查询至少有2名男生的班号。
SQL> select s_class from student where s_sex='男' group by s_class having count(s_no)>=2;
S_CLASS

95033
95031

37、查询Student表中不姓“王”的同学记录。
SQL> select * from student where s_name not like '王%';
S_NO S_NAME S_SEX S_BIRTHDAY S_CLASS

108 曾华男 1977/9/1 95033
105 匡明男 1975/10/2 95031
101 李军男 1976/2/20 95033
103 陆君男 1974/6/3 95031

38、查询Student表中每个学生的姓名和年龄。
SQL> select s_name, to_char(sysdate, 'yyyy')-to_char(s_birthday, 'yyyy') as age from student;
S_NAME AGE

曾华 42
匡明 44
王丽 43
李军 43
王芳 44
陆君 45
6 rows selected

39、查询Student表中最大和最小的Sbirthday日期值。
SQL> select max(s_birthday),min(s_birthday) from student;
MAX(S_BIRTHDAY) MIN(S_BIRTHDAY)

1977/9/1 1974/6/3

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SQL> select * from student order by s_class desc, s_birthday asc;
S_NO S_NAME S_SEX S_BIRTHDAY S_CLASS

107 王丽女 1976/1/23 95033
101 李军男 1976/2/20 95033
108 曾华男 1977/9/1 95033
103 陆君男 1974/6/3 95031
109 王芳女 1975/2/10 95031
105 匡明男 1975/10/2 95031
6 rows selected

41、查询“男”教师及其所上的课程。
SQL> select t.t_name, c.c_name from teacher t, course c where t.t_no=c.t_no and t.t_sex='男';
T_NAME C_NAME

李诚操作系统
张旭数字电路

42、查询最高分同学的Sno、Cno和Degree列。
SQL> select s_no, c_no, degree from score where degree=(select max(degree) from score);
S_NO C_NO DEGREE

103 3-105 92.0

43、查询和“李军”同性别的所有同学的Sname.
SQL> select a.s_name from student a, student b where a.s_sex=b.s_sex and b.s_name='李军';
S_NAME

曾华
匡明
李军
陆君

44、查询和“李军”同性别并同班的同学Sname.
SQL> select a.s_name from student a, student b where a.s_sex=b.s_sex and a.s_class=b.s_class and b.s_name='李军';
S_NAME

曾华
李军

45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SQL> select s.s_name, s.s_sex, sc.degree from score sc, course c, student s where s.s_no=sc.s_no and c.c_no=sc.c_no and c.c_name='计算机导论' and s.s_sex='男';
S_NAME S_SEX DEGREE

曾华男 78.0
匡明男 88.0
李军男 64.0
陆君男 92.0

posted @ 2019-12-19 11:52 秋裤队长阅读(364) 评论(0) 编辑收藏举报

指间灵动，快码加编

刷新页面返回顶部

MaXiaobing's Blog

要非常非常努力，也要非常非常快乐。

数据库基础

2、查询教师所有的单位即不重复的Depart列。
SQL> select distinct t_depart from teacher;
T_DEPART

9、查询“95031”班的学生人数。
SQL> select count(s_no) from student where s_class='95031';
COUNT(S_NO)

17、查询“95033”班学生的平均分。
SQL> select round(avg(degree), 2) as avg_score from student s, score sc where s.s_no=sc.s_no and s.s_class='95033';
AVG_SCORE

107 3-105 91.0
105 3-105 88.0
108 3-105 78.0
109 3-105 76.0
101 3-105 64.0
105 3-245 75.0
109 3-245 68.0
108 6-166 81.0
107 6-166 79.0
9 rows selected
--选课数>1的所有人
SQL> select s_no from score group by s_no having count(s_no) > 1;
S_NO

26、查询存在有85分以上成绩的课程Cno.
SQL> select distinct c_no from score where degree>85;
C_NO

36、查询至少有2名男生的班号。
SQL> select s_class from student where s_sex='男' group by s_class having count(s_no)>=2;
S_CLASS

43、查询和“李军”同性别的所有同学的Sname.
SQL> select a.s_name from student a, student b where a.s_sex=b.s_sex and b.s_name='李军';
S_NAME

44、查询和“李军”同性别并同班的同学Sname.
SQL> select a.s_name from student a, student b where a.s_sex=b.s_sex and a.s_class=b.s_class and b.s_name='李军';
S_NAME

公告

MaXiaobing's Blog

要非常非常努力，也要非常非常快乐。

数据库基础

2、 查询教师所有的单位即不重复的Depart列。 SQL> select distinct t_depart from teacher; T_DEPART

9、 查询“95031”班的学生人数。 SQL> select count(s_no) from student where s_class='95031'; COUNT(S_NO)

17、 查询“95033”班学生的平均分。 SQL> select round(avg(degree), 2) as avg_score from student s, score sc where s.s_no=sc.s_no and s.s_class='95033'; AVG_SCORE

107 3-105 91.0 105 3-105 88.0 108 3-105 78.0 109 3-105 76.0 101 3-105 64.0 105 3-245 75.0 109 3-245 68.0 108 6-166 81.0 107 6-166 79.0 9 rows selected --选课数>1的所有人 SQL> select s_no from score group by s_no having count(s_no) > 1; S_NO

26、 查询存在有85分以上成绩的课程Cno. SQL> select distinct c_no from score where degree>85; C_NO

36、查询至少有2名男生的班号。 SQL> select s_class from student where s_sex='男' group by s_class having count(s_no)>=2; S_CLASS

43、查询和“李军”同性别的所有同学的Sname. SQL> select a.s_name from student a, student b where a.s_sex=b.s_sex and b.s_name='李军'; S_NAME

44、查询和“李军”同性别并同班的同学Sname. SQL> select a.s_name from student a, student b where a.s_sex=b.s_sex and a.s_class=b.s_class and b.s_name='李军'; S_NAME

公告

2、查询教师所有的单位即不重复的Depart列。
SQL> select distinct t_depart from teacher;
T_DEPART

9、查询“95031”班的学生人数。
SQL> select count(s_no) from student where s_class='95031';
COUNT(S_NO)

17、查询“95033”班学生的平均分。
SQL> select round(avg(degree), 2) as avg_score from student s, score sc where s.s_no=sc.s_no and s.s_class='95033';
AVG_SCORE

107 3-105 91.0
105 3-105 88.0
108 3-105 78.0
109 3-105 76.0
101 3-105 64.0
105 3-245 75.0
109 3-245 68.0
108 6-166 81.0
107 6-166 79.0
9 rows selected
--选课数>1的所有人
SQL> select s_no from score group by s_no having count(s_no) > 1;
S_NO

26、查询存在有85分以上成绩的课程Cno.
SQL> select distinct c_no from score where degree>85;
C_NO

36、查询至少有2名男生的班号。
SQL> select s_class from student where s_sex='男' group by s_class having count(s_no)>=2;
S_CLASS

43、查询和“李军”同性别的所有同学的Sname.
SQL> select a.s_name from student a, student b where a.s_sex=b.s_sex and b.s_name='李军';
S_NAME

44、查询和“李军”同性别并同班的同学Sname.
SQL> select a.s_name from student a, student b where a.s_sex=b.s_sex and a.s_class=b.s_class and b.s_name='李军';
S_NAME