oracle两表查询练习附答案

Sutdent表的定义

字段名 Id Name Sex Birth Department Address 字段描述 学号 姓名 性别 出生年份 院系 家庭住址 数据类型 INT(10) VARCHAR(20) VARCHAR(4) YEAR VARCHAR(20) VARCHAR(50) 主键 是 否 否 否 否 否 外键 否 否 否 否 否 否 非空 是 是 否 否 是 否 唯一 是 否 否 否 否 否 自增 是 否 否 否 否 否 Score表的定义 字段名 Id Stu_id C_name Grade 字段描述 编号 学号 课程名 分数 数据类型 INT(10) INT(10) VARCHAR(20) INT(10) 主键 是 否 否 否 外键 否 否 否 否 非空 是 是 否 否 唯一 是 否 否 否 自增 是 否 否 否 1.创建student和score表

create table student(

id number(10) not null primary key, name varchar2(20) not null, sex varchar2(4), birth number,

department varchar2(20) not null, address varchar2(50) );

create table score(

id number(10) not null primary key, stu_id number(10) not null, c_name varchar2(20), grade number(10) );

2.为student表和score表增加记录

向student表插入记录的INSERT语句如下：

Insert into student values(100101,'张三','男',23,'计算机系','北京市朝阳区'); Insert into student values(100102,'李四','男',21,'英语系','北京市海淀区'); Insert into student values(100103,'王五','女',19,'建工系','北京市昌平区'); Insert into student values(100104,'孙六','女',21,'化学系','北京市苏州桥'); Insert into student values(100105,'齐七','男',23,'英语系','北京市海淀区');

向score表插入记录的INSERT语句如下：

Insert into score values(001,100101,'计算机基础',89); Insert into score values(002,100101,'英语',93); Insert into score values(003,100101,'数学',87);

Insert into score values(004,100102,'计算机基础',83); Insert into score values(005,100102,'英语',81); Insert into score values(006,100102,'数学',77); Insert into score values(007,100103,'计算机基础',91); Insert into score values(008,100103,'英语',85); Insert into score values(009,100103,'数学',88); Insert into score values(010,100104,'计算机基础',84); Insert into score values(011,100104,'英语',71); Insert into score values(012,100104,'数学',83);

3.查询student表的所有记录

select * from student;

4.查询student表的第2条到4条记录

1.select *,rownum as con from student where rownum<=4 and rownum>=2; (select * from student where rownum<=4) minus (select * from student where rownum <=1);

1、Minus:两个查询,MINUS是从第一个查询结果减去第二个查询结果,如果有相交部

分就减去相交部分;否则和第一个查询结果没有区别. INTERSECT是两个查询结果的交集,UNION ALL是两个查询的并集;

2、Rownum：对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第

一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。

5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

select id,name,department from student;

6.从student表中查询计算机系和英语系的学生的信息

select * from student where address='计算机系'or address='英语系';

7.从student表中查询年龄18~22岁的学生信息

select * from student where birth between 18 and 22;

8.从student表中查询每个院系有多少人

select department,count(*) 学院人数from student group by department;

9.从score表中查询每个科目的最高分

select c_name,max(grade) 最高分 from score group by c_name;

10.查询李四的考试科目（c_name）和考试成绩（grade）

select c_name, grade from score where stu_id in (select id from student where name ='李四');

11.用连接的方式查询所有学生的信息和考试信息

select st.*, sc.* from student st full join score sc on st.id = stu_id;

12.计算每个学生的总成绩

select name,sum(grade) 总成绩 from student st, score sc where st.id = sc.stu_id group by name;

13.计算每个考试科目的平均成绩

select c_name,avg(nvl(grade,0)) 平均成绩 from score group by c_name;

14.查询计算机成绩低于95的学生信息

select * from student where id in (select stu_id from score where grade<95 and c_name ='计算机基础');

15.查询同时参加计算机和英语考试的学生的信息

Select * from student st,(select stu_id from score where c_name ='计算机'）s1, (select stu_id from score where c_name ='英语')s2 where st.id = s1.stu_id and st.id = s2.stu_id;

16.将计算机考试成绩按从高到低进行排序

（1）、select grade 计算机成绩 from score where c_name='计算机基础' order by grade desc; （2）、select st.id 学号,st.name 姓名,grade 计算机成绩 from student st,score sc where c_name='计算机基础'and st.id=sc.stu_id order by grade desc;

17.从student表和score表中查询出学生的学号，然后合并查询结果

select id from student union select stu_id from score;

18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

select st.name,st.department,sc.c_name,sc.grade from student st,score sc where st.name like '张%'or st.name like'王%'and st.id= sc.stu_id; 没有出来结果，但是不知道哪里不对啊 Like模糊查询，正学写法如下：

select st.name,st.department,sc.c_name,sc.grade from student st,score sc where (st.name like

'张%'or st.name like'王%')and st.id= sc.stu_id;

19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

select st.name, st.birth, st.department, sc.c_name, sc.grade from student st, score sc where address like '湖南%'and st.id = sc.stu_id;

本文来源：https://www.bwwdw.com/article/4z6a.html