2023面试高手

这个屌丝很懒，什么也没留下！

热门标签

ORACLE与SQL查询，更新_数据库中student表course表sc表的数据

作者：2023面试高手 | 2024-04-25 14:37:06

踩

数据库中student表course表sc表的数据

简单查询

内容和主要步骤：

1）创建学生表student、课程表course和选课表SC,并输入数据（注意数据的完整性。）;（可以使用实验一中已经建立的表和数据）

2) 对各表中的数据进行不同条件的查询；

a)查询全体学生的学号和姓名

b)查询全体学生的详细记录

c)查询所有选修过课程的学生学号

d)查询考试有不及格的学生学号

e)查询不是信息系（IS）、计算机系（CS）的学生性别、年龄、系别

f)查询选修了4号课的学生学号和成绩，结果按成绩降序排列

g)查询每个课程号和相应的选课人数

h)查询计算机系（CS）的学生姓名、年龄、系别

i)查询年龄18－20岁的学生学号、姓名、系别、年龄；

j)查询姓刘的学生情况

k)查询既选修1号课程，又选修2号课程的学生学号和成绩

l)查询学生的姓名和出生年份（今年2003年）

m)查询没有成绩的学生学号和课程号

n)查询总成绩大于200分的学生学号

o)查询每门课程不及格学生人数

p)查询不及格课程超过3门的学生学号

q)查询年龄在10到19岁之间的学生信息

r)查询全体学生情况，按所在系升序排列，同一个系的学生按年龄降序排列

s)查询选了1号课程的学生平均成绩

t)查询选了3号课程的学生的最高分

u)查询每个同学的总成绩

复杂查询

内容和主要步骤：

1）实验一中的数据为基础

2) 对各表中的数据进行不同条件的连接查询和嵌套查询；

(1)?查询每个学生及其选课情况；

(2)?查询每门课的间接先修课

(3)?将STUDENT,SC进行右连接

(4)?查询有不及格的学生姓名和所在系

(5)?查询所有成绩为优秀（大于90分）的学生姓名

(6)?查询既选修了2号课程又选修了3号课程的学生姓名、学号；

(7)?查询和刘晨同一年龄的学生

(8)?选修了课程名为“数据库”的学生姓名和年龄

(9)?查询其他系比IS系任一学生年龄小的学生名单

(10)?查询其他系中比IS系所有学生年龄都小的学生名单

(11)?查询选修了全部课程的学生姓名

(12)?查询计算机系学生及其性别是男的学生

(13)?查询选修课程1的学生集合和选修2号课程学生集合的差集

(14)?查询李丽同学不学的课程的课程号

(15)?查询选修了3号课程的学生平均年龄

(16)?求每门课程学生的平均成绩

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

(18)?查询学号比刘晨大，而年龄比他小的学生姓名。

(19)?求年龄大于女同学平均年龄的男同学姓名和年龄

(20)?求年龄大于所有女同学年龄的男同学姓名和年龄

(21)?查询至少选修了95002选修的全部课程的学生号码

(22)?查询95001和95002两个学生都选修的课程的信息

更新查询题目：

1）应用INSERT,UPDATE,DELETE语句进行更新操作；

a) 插入如下学生记录（学号：95030，姓名：李莉，年龄：18）

b) 插入如下选课记录（95030，1）

c) 计算机系学生年龄改成20

d) 数学系所有学生成绩改成0

e) 把低于总平均成绩的女同学成绩提高5分

f) 修改2号课程的成绩，若成绩小于75分提高5％，成绩大于75时提高

4％（两个语句实现，注意顺序）

g) 删除95030学生信息

h) 删除SC表中无成绩的记录

i) 删除张娜的选课记录

j) 删除数学系所有学生选课记录

k) 删除不及格的学生选课记录

l) 查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中

m) 把所有学生学号和课程号连接追加到新表中

n) 所有学生年龄增1

o) 统计3门以上课程不及格的学生把相应的学生姓名、系别追加到另外一个表中

答案：

1.
SQL> select sno,sname from student;

SNO SNAME
---------- ----------------
95001 张力
95002 李丽
95003 赵海
95004 张那
95005 刘晨
95006 刘丹
95007 刘立
95008 王江
95009 高晓
95010 张丽

2.SQL> select * from student;

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95001 张力 cs 男
18

95002 李丽 is 女
19

95003 赵海 ma 男
20


SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95004 张那 cs 女
17

95005 刘晨 is 男
18

95006 刘丹 ma 女
17


SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95007 刘立 cs 男
21

95008 王江 cs 男
19

95009 高晓 is 男
20


SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95010 张丽 cs 女
19

SQL> spool off
3SQL> select distinct sno from sc where cno is not null;

SNO
----------
95008
95005
95010
95009
95006
95001
95003
95004
95002
4SQL> select sno from sc where grade<60 or grade is null;

SNO
----------
95002
95006
95008
5.SQL> select ssex,sage,sdept from student where sdept !='is' and sdept!='cs';

SSEX SAGE SDEPT
---- ---------- ----------------------------------------
男 20 ma
女 17 ma
SQL> spool off
6SQL> select sno,grade from sc where cno ='4' order by grade DESC;

SNO GRADE
---------- ----------
95001 90
7SQL> select cno,count(cno) from sc group by cno;

CNO COUNT(CNO)
---------- ----------
1 5
6 1
2 2
4 1
3 5
8SQL> select sname,sage,sdept from student where sdept='cs';

SNAME SAGE SDEPT
---------------- ---------- ----------------------------------------
张力 18 cs
张那 17 cs
刘立 21 cs
王江 19 cs
张丽 19 cs
9SQL> select sno,sname,sdept,sage from student where sage between 18 and 20;

SNO SNAME SDEPT SAGE
---------- ---------------- ---------------------------------------- ----------
95001 张力 cs 18
95002 李丽 is 19
95003 赵海 ma 20
95005 刘晨 is 18
95008 王江 cs 19
95009 高晓 is 20
95010 张丽 cs 19
10
SQL> select * from student where sname like'刘%';

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95005 刘晨 is 男
18

95006 刘丹 ma 女
17

95007 刘立 cs 男
21

11
SQL> select Sno
2 from SC
3 where Cno='1' and sno in (select Sno from SC where Cno='2');
12SQL> select sname,(2003-sage) from student;

SNAME (2003-SAGE)
---------------- -----------
张力 1985
李丽 1984
赵海 1983
张那 1986
刘晨 1985
刘丹 1986
刘立 1982
王江 1984
高晓 1983
张丽 1984
13
SQL> select sno,cno from sc where grade is null;

SNO CNO
---------- ----------
95002 3
95006 3
95008 1
14
SQL> select sno,sum(grade) as su from sc group by sno having sum(grade)>200;

SNO SU
---------- ----------
95001 379
15
SQL> select cno,count(sno) from sc where grade<60 or grade is null group by cno;

CNO COUNT(SNO)
---------- ----------
1 1
3 2
16
SQL> select sno from sc where grade<60 group by sno having count(cno)>=3;
17SQL> select * from student where sage between 10 and 19;

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95001 张力 cs 男
18

95002 李丽 is 女
19

95004 张那 cs 女
17


SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95005 刘晨 is 男
18

95006 刘丹 ma 女
17

95008 王江 cs 男
19


SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95010 张丽 cs 女
19

18
SQL> select * from student order by sdept,sage desc;

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95007 刘立 cs 男
21

95010 张丽 cs 女
19

95008 王江 cs 男
19


SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95001 张力 cs 男
18

95004 张那 cs 女
17

95009 高晓 is 男
20


SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95002 李丽 is 女
19

95005 刘晨 is 男
18

95003 赵海 ma 男
20


SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95006 刘丹 ma 女
17

19SQL> select avg(grade) from sc where cno='3';

AVG(GRADE)
----------
95.6666667
20
SQL> select max(grade) from sc where cno='3';

MAX(GRADE)
----------
100
21
SQL> select max(grade) from sc where cno='1';

MAX(GRADE)
----------
100
22
SQL> select avg(grade) from sc where cno='1';

AVG(GRADE)
----------
94.5
23
SQL> select sno,count(grade) from sc group by sno;

SNO COUNT(GRADE)
---------- ------------
95008 0
95005 1
95010 1
95009 1
95006 0
95001 4
95003 2
95004 1
95002 1
SQL> spool off

https://wenku.baidu.com/view/4dd58d7501f69e31433294d2.html

复杂：
（1）SQL> select sno,cno from sc where sno in(select sno from sc group by sno);

SNO CNO
---------- ----------
95001 1
95002 2
95003 1
95004 1
95001 3
95001 4
95002 3
95003 3
95005 2
95006 3
95008 1

SNO CNO
---------- ----------
95009 1
95010 3
95001 6
(2)
SQL> SELECT course1.cno,course2.cpno
2 from course course1,course course2
3 where course1.cpno=course2.cno;

CNO CPNO
---------- ----------
1 3
5
2
4 3
(3)
SQL> select student.*,sc.*
2 from student right join sc on student.sno=sc.sno;

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE SNO CNO GRADE
---------- ---------- ---------- ----------
95001 张力 cs 男
18 95001 6 100

95001 张力 cs 男
18 95001 4 90

95001 张力 cs 男
18 95001 3 89

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE SNO CNO GRADE
---------- ---------- ---------- ----------
95001 张力 cs 男
18 95001 1 100

95002 李丽 is 女
19 95002 3

95002 李丽 is 女
19 95002 2 98

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE SNO CNO GRADE
---------- ---------- ---------- ----------
95003 赵海 ma 男
20 95003 3 98

95003 赵海 ma 男
20 95003 1 99

95004 张那 cs 女
17 95004 1 90

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE SNO CNO GRADE
---------- ---------- ---------- ----------
95005 刘晨 is 男
18 95005 2 97

95006 刘丹 ma 女
17 95006 3

95008 王江 cs 男
19 95008 1

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE SNO CNO GRADE
---------- ---------- ---------- ----------
95009 高晓 is 男
20 95009 1 89

95010 张丽 cs 女
19 95010 3 100

SQL>
(4)SQL> select sname,sdept from sc,student where student.sno=sc.sno and grade<60;
(5)

SQL> select sname from student,sc where student.sno=sc.sno group by sname having min(grade)>90;

SNAME
----------------
李丽
刘晨
赵海
张丽
（6）
SQL> select student.sno,sname from student,sc where student.sno=sc.sno and cno='002' and sc.sno in
2 (select sno from sc where cno='003');

SNO SNAME
---------- ----------------
95002 李丽
（7）
SQL> select * from student where sage = (select sage from student where sname='刘晨') and sname<>'刘晨';

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95001 张力 cs 男
18
（8）
SQL> select sname,sage from student,sc,course where student.sno=sc.sno and course.cno=sc.cno and cname='数据库';

SNAME SAGE
---------------- ----------
张力 18
赵海 20
张那 17
王江 19
高晓 20
（9）
SQL> select sno,sname,sdept,sage from student where sage< any(select sage from student where sdept='is') and sdept<>'is';

SNO SNAME SDEPT SAGE
---------- ---------------- ---------------------------------------- ----------
95006 刘丹 ma 17
95004 张那 cs 17
95001 张力 cs 18
95008 王江 cs 19
95010 张丽 cs 19
SQL>
（10）
SQL> select sno,sname,sdept,sage from student where sage<(select min(sage) from student where sdept='is') and sdept<>'is';

SNO SNAME SDEPT SAGE
---------- ---------------- ---------------------------------------- ----------
95004 张那 cs 17
95006 刘丹 ma 17
（11）SQL> select sname,sdept from student where not exists (select * from course where not exists(select * from sc where sno=student.sno and cno=course.cno));
（12）
SQL> select * from student where sdept='is' and ssex='男';

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95005 刘晨 is 男
18

95009 高晓 is 男
20
（13）

SQL> select sno from sc where cno ='001' and sno in (select sno from sc where cno<>'002');

SNO
----------
95001
95003
95004
95008
95009
（14）
SQL> SELECT distinct cno
2 from sc
3 where cno not in
4 (select cno
5 from student,sc
6 where student.sno=sc.sno and sname='李丽' );

CNO
----------
1
6
4
（15）
4
SQL> select avg(sage) from student,sc where student.sno=sc.sno and cno='003';

AVG(SAGE)
----------
18.6
（16）
95.6666667
SQL> select cno,avg(grade) from sc group by cno;

CNO AVG(GRADE)
---------- ----------
1 94.5
6 100
2 97.5
4 90
3 95.6666667
（17）
SQL> select cno,count(sno) from sc group by cno having count(sno)>3 order by count(sno) asc,cno desc;

CNO COUNT(SNO)
---------- ----------
3 5
1 5
（18）
1 5
SQL> select sname from student where sno>(select sno from student where sname='刘晨') and sage<(select sage from student where sname='刘晨');

SNAME
----------------
刘丹
（19）
SQL> select sname,sage from student where sage>(select avg(sage) from student where ssex='女') and ssex<>'女';

SNAME SAGE
---------------- ----------
赵海 20
刘立 21
王江 19
高晓 20
（20）
SQL> select sname,sage from student where sage>all(select sage from student where ssex='女');

SNAME SAGE
---------------- ----------
高晓 20
赵海 20
刘立 21
（21）
SQL> ed
已写入 file afiedt.buf

1 select sno
2 from sc
3 where
4 cno in (SELECT cno from sc where sno='95002')
5 and sno <>'95002'
6 group by sno
7* having count(cno)>=(select count(cno) from sc where sno='95002')
8 ;
SQL>
（22）
SQL> ed
已写入 file afiedt.buf

1 SELECT course.cno,cname,cpno,credit
2 from sc,course
3 where sc.cno=course.cno and
4 sno='95001' and
5* sc.cno in (select cno from sc where sno='95002')
SQL> /

CNO CNAME CPNO CREDIT
---------- ---------------------------------------- ---------- ----------
3 程序设计 2
SQL>

更新查询
（1）
SQL> insert into student(sno,sname,sage) values('95030','李莉',18);
SQL> select * from student where sname='李莉';

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95030 李莉
18

SQL>
（2）

SQL> insert into sc values ('95030',1,null);
SQL> select * from sc where sno='95030';

SNO CNO GRADE
---------- ---------- ----------
95030 1
SQL>
（3）
SQL> update student set sage = 20 where sdept='is' ;
SQL> select * from student where sdept='is';

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95002 李丽 is 女
20

95005 刘晨 is 男
20

95009 高晓 is 男
20

SQL>
（4）

SQL> update sc set grade=0 where 'ma' =(select sdept from student where student.sno=sc.sno);
SQL> select * from sc ;

SNO CNO GRADE
---------- ---------- ----------
95001 1 100
95002 2 98
95003 1 0
95004 1 90
95001 3 89
95001 4 90
95002 3
95003 3 0
95005 2 97
95006 3 0
95008 1

SNO CNO GRADE
---------- ---------- ----------
95009 1 89
95010 3 100
95001 6 100
95030 1
SQL>
（5）
SQL> update sc set grade=grade+5 where sno in (select student.sno from student,sc where ssex='女' and student.sno=sc.sno and grade<(select avg(grade) from sc));
SQL> select * from sc;

SNO CNO GRADE
---------- ---------- ----------
95001 1 100
95002 2 98
95003 1 0
95004 1 90
95001 3 89
95001 4 90
95002 3
95003 3 0
95005 2 97
95006 3 5
95008 1

SNO CNO GRADE
---------- ---------- ----------
95009 1 89
95010 3 100
95001 6 100
95030 1
（6）
SQL> update sc set grade=(case when grade<75 then grade*(1+0.05) when grade>75 then grade*(1+0.1) else grade end) where cno='2';
SQL> select * from sc;

SNO CNO GRADE
---------- ---------- ----------
95001 1 100
95002 2 108
95003 1 0
95004 1 90
95001 3 89
95001 4 90
95002 3
95003 3 0
95005 2 107
95006 3 5
95008 1

SNO CNO GRADE
---------- ---------- ----------
95009 1 89
95010 3 100
95001 6 100
95030 1
（7）
SQL> select * from student;

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95001 张力 cs 男
18

95002 李丽 is 女
20

95003 赵海 ma 男
20

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95004 张那 cs 女
17

95005 刘晨 is 男
20

95006 刘丹 ma 女
17

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95007 刘立 cs 男
21

95008 王江 cs 男
19

95009 高晓 is 男
20

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95010 张丽 cs 女
19

95030 李莉
18

SQL> delete from student where sno='95030';
SQL> select * from student;

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95001 张力 cs 男
18

95002 李丽 is 女
20

95003 赵海 ma 男
20

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95004 张那 cs 女
17

95005 刘晨 is 男
20

95006 刘丹 ma 女
17

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95007 刘立 cs 男
21

95008 王江 cs 男
19

95009 高晓 is 男
20

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95010 张丽 cs 女
19
（8）
SQL> select * from sc;

SNO CNO GRADE
---------- ---------- ----------
95001 1 100
95002 2 108
95003 1 0
95004 1 90
95001 3 89
95001 4 90
95002 3
95003 3 0
95005 2 107
95006 3 5
95008 1

SNO CNO GRADE
---------- ---------- ----------
95009 1 89
95010 3 100
95001 6 100
SQL> delete from sc where grade is null;
SQL> select * from sc;

SNO CNO GRADE
---------- ---------- ----------
95001 1 100
95002 2 108
95003 1 0
95004 1 90
95001 3 89
95001 4 90
95003 3 0
95005 2 107
95006 3 5
95009 1 89
95010 3 100

SNO CNO GRADE
---------- ---------- ----------
95001 6 100
（9）
SQL> delete from sc where sno =(select sno from student where sname='张娜');
SQL> select * from sc;

SNO CNO GRADE
---------- ---------- ----------
95001 1 100
95002 2 108
95003 1 0
95004 1 90
95001 3 89
95001 4 90
95003 3 0
95005 2 107
95006 3 5
95009 1 89
95010 3 100

SNO CNO GRADE
---------- ---------- ----------
95001 6 100
SQL> select * from student where sname='张娜';
SQL>
（10）

SQL> delete from sc where sno in(select sno from student where sdept='ma');
SQL> select * from sc;

SNO CNO GRADE
---------- ---------- ----------
95001 1 100
95002 2 108
95004 1 90
95001 3 89
95001 4 90
95005 2 107
95009 1 89
95010 3 100
95001 6 100
SQL>
（11）
SQL> delete from sc where grade<60;
（12）
SQL> create table stu
2 ( SNO NUMBER(10),
3 SNAME NVARCHAR2(8) not null,
4 SSEX NVARCHAR2(2));
SQL> select sno,sname,ssex into stu from student where sno in(select sno from sc group by sno having min(grade)>80);

（13）
SQL> Select sc.sno,cno into xinbiao(sno,sname) from student,sc where student.sno=sc.sno;
（14）
SQL> update student set sage =sage+1 ;
SQL> select * from student;

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95001 张力 cs 男
19

95002 李丽 is 女
21

95003 赵海 ma 男
21

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95004 张那 cs 女
18

95005 刘晨 is 男
21

95006 刘丹 ma 女
18

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95007 刘立 cs 男
22

95008 王江 cs 男
20

95009 高晓 is 男
21

SNO SNAME SDEPT SSEX
---------- ---------------- ---------------------------------------- ----
SAGE
----------
95010 张丽 cs 女
（15）SQL> Select sname，sdept into XB from student where sno in (select sno from sc where grade<’60’ group by sno having count(grade)>3 );

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/485813