首页 > 代码库 > Oracle 小案例

Oracle 小案例

create database cstd;
use cstd;
/*1:建立学生表*/
create table student (
   学号 char(3) primary key,
   姓名 char(8),
   性别 char(4),
   年龄 int,
   班级 char(5)
);
insert into student values(108,曾华,,19,95033);
insert into student values(105,匡明,,20,95031);
insert into student values(107,王丽,,20,95033);
insert into student values(101,李军,,19,95033);
insert into student values(109,王芳,,22,95031);
insert into student values(103,陆君,,20,95031);
/*2:建立教师表*/
create table teacher(
   教师号 char(3) primary key,
   姓名 char(8),
   性别 char(4),
   年龄 int ,
   级别 char(12),
   专业 char(16)
);
insert into teacher values(804,李成,,42,副教授,计算机系);
insert into teacher values(856,张旭,,35,讲师,电子工程);
insert into teacher values(825,王萍,,28,助教,计算机系);
insert into teacher values(831,刘冰,,25,助教,电子工程);
/*3:建立课程表*/
create table course (
   课程号 char(5) primary key,
   课程名 char(20),
   教师号 char(3),
   foreign key(教师号) references teacher(教师号)
);
insert into course values(3-105,计算机导论,825);
insert into course values(3-245,操作系统,804);
insert into course values(6-166,数字电路,856);
insert into course values(9-888,高等数学,831);
/*4:建立选课表*/
create table sc(
   学号 char(3),
   课程号 char(5),
   primary key(学号,课程号),
   成绩 int,
   foreign key(学号) references student(学号),
   foreign key(课程号) references course(课程号)
);
insert into sc values(103,3-245,86);
insert into sc values(105,3-245,75);
insert into sc values(109,3-245,68);
insert into sc values(103,3-105,92);
insert into sc values(105,3-105,88);
insert into sc values(109,3-105,76);
insert into sc values(101,3-105,64);
insert into sc values(107,3-105,91);
insert into sc values(108,3-105,78);
insert into sc values(101,6-166,85);
insert into sc values(107,6-166,79);
insert into sc values(108,6-166,81);
/*5:所有表内容*/
select * from student;
select * from course;
select * from teacher;
select * from sc;


--1.查询选修课程‘3-105‘且成绩在60到80之间的所有记录。
--注释:用于指定某个范围使用between and,也可以使用and连接符;
select * from sc where 课程号=3-105 and 成绩 between 60 and 80;
select * from sc where 课程号=3-105 and 成绩 > 60 and 成绩 < 80;

--2.查询成绩为85、86或88的记录。
select * from sc where 成绩 in (85,86,88);
select * from sc where 成绩=85 or 成绩=86 or 成绩=88;

--3.查询‘95031‘班的学生人数。
select count(学号) "95031班的学生人数" from student where 班级=95031;

--4.查询最低分大于70,且最高分小于90的学号列。

/*学生的最高分和最低分
select 学号,max(成绩),min(成绩) from sc group by 学号;
*/
select 学号,max(成绩),min(成绩) from sc 
having max(成绩)<90 and  min(成绩) > 70
group by 学号;



--5.查询至少有5名学生选修并以3开头的课程的平均成绩。
/*
-- 3开头的课程
select 课程号,课程名 from course where 课程号 like ‘3%‘;
-- 3开头的课程的所有学生
select 学号,课程号,成绩 from sc 
where 课程号 in (select 课程号 from course where 课程号 like ‘3%‘);
*/
select 学号,avg(成绩) from sc 
where 课程号 in (select 课程号 from course where 课程号 like 3%)
group by 学号;

--6.查询平均分大于80分的学生的成绩表
select 学号,avg(成绩) from sc having avg(成绩)>80 group by 学号;

--7.查询‘95033‘班每个学生所选课程的平均分。
/*
-- ‘95033‘班的学生
select 学号  from student where 班级=‘95033‘;
*/
select 学号,avg(成绩) from sc 
where 学号 in (select 学号  from student where 班级=95033)
group by 学号;


--8.以选修 ‘3-105‘为例,查询成绩高于‘109‘号同学的所有同学的记录。
/*
-- 选修 ‘3-105‘的学生
select 学号,课程号,成绩 from sc where 课程号=‘3-105‘;
-- ‘109‘号同学 ‘3-105‘课程的成绩
select 学号,课程号,成绩 from sc where 课程号=‘3-105‘ and 学号=‘109‘;
*/
select 学号,课程号,成绩 from sc 
where 课程号=3-105 and 成绩>(select 成绩 from sc 
where 课程号=3-105 and 学号=109);

--9.查询与学号为‘108‘的同学同岁的所有学生的学号、姓名和年龄。
/*
--‘108‘同学的年龄
select 学号,姓名,年龄 from student where 学号=‘108‘;
--所有同学的年龄
select 学号,姓名,年龄 from student;
*/
-- 包含‘108‘的同学
select 学号,姓名,年龄 from student
 where 年龄=(select 年龄 from student where 学号=108)
-- 不包含‘108‘的同学
select 学号,姓名,年龄 from student
 where 年龄=(select 年龄 from student where 学号=108)
 and 学号 <>108
 
--10.查询‘张旭‘教师任课的课程号,选修其课程学生的学号和成绩。
/*
--‘张旭‘教师的教师号
select 教师号,姓名 from teacher where 姓名 =‘张旭‘;
---‘张旭‘教师的课程号
select 教师号,课程号,课程名 from course 
where 教师号 =(select 教师号 from teacher where 姓名 =‘张旭‘);
*/
select 学号,成绩,课程号 from sc where 课程号=(select 课程号 from course 
where 教师号 =(select 教师号 from teacher where 姓名 =张旭));


--11.查询选修其课程的学生人数多于5人的教师姓名。
/*
--大于5人的课程号
select 课程号,count(学号) from sc 
having count(学号) > 5
group by 课程号;
--大于5人的课程号的教师号
select 教师号,课程号 from course 
where 课程号=(select 课程号 from sc 
having count(学号) > 5
group by 课程号)
*/
select 教师号,姓名 from teacher 
where 教师号 = (select 教师号 from course 
where 课程号=(select 课程号 from sc 
having count(学号) > 5
group by 课程号));

--13.查询选修编号为‘3-105‘课程
--且成绩至少高于选修编号为‘3-245‘课程的同学
--的课程号、学号 、成绩并按成绩从高到低次序排列。

select 课程号,学号,成绩 from sc where 课程号 = 3-245;
select 课程号,学号,成绩 from sc where 课程号 = 3-105 
and 成绩 > (select min(成绩) from sc where 课程号 = 3-245)
order by 成绩 desc;
--第二种方法
select 课程号,学号,成绩 from sc where 课程号 = 3-245;
select 课程号,学号,成绩 from sc where 课程号 = 3-105 
and 成绩 > any(select 成绩 from sc where 课程号 = 3-245)
order by 成绩 desc;

--14.查询选修编号为‘3-105‘课程
--且成绩高于选修编号为‘3-245‘课程的同学的课程号、学号 、成绩。
select 课程号,学号,成绩 from sc where 课程号 = 3-245;
select 课程号,学号,成绩 from sc where 课程号 = 3-105 
and 成绩 > (select max(成绩) from sc where 课程号 = 3-245);
--第二种方法
select 课程号,学号,成绩 from sc where 课程号 = 3-245;
select 课程号,学号,成绩 from sc where 课程号 = 3-105 
and 成绩 > all(select 成绩 from sc where 课程号 = 3-245)
order by 成绩 desc;


--15.列出所有教师和同学的姓名 、性别 、年龄。
select 姓名 ,性别,年龄 from teacher union select 姓名 ,性别,年龄 from student;


--16.查询成绩比‘3-105‘课程的平均成绩低的学生的成绩表。
/*
--‘3-105‘课程的平均成绩
select avg(成绩) from sc where 课程号=‘3-105‘;
*/
select 学号,avg(成绩) from sc 
having avg(成绩)>(select avg(成绩) from sc where 课程号=3-105)  
group by 学号;

--17.查询成绩比该课程平均成绩低的学生的成绩表。
/*
--每个课程的平均成绩
select 课程号,avg(成绩) from sc group by 课程号;
*/
select 学号,课程号,成绩 from sc 
where 成绩<all(select avg(成绩) from sc group by 课程号);

--18.列出所有任课教师的姓名和专业。
select 姓名,专业,教师号 from teacher
where 教师号 in (select 教师号 from course);


--19.列出所有未讲课教师的姓名和专业。
select 姓名,专业,教师号 from teacher 
minus select 姓名,专业,教师号 from teacher
where 教师号 in (select 教师号 from course);

--20.列出至少有2名男生的班号。
select 学号,姓名,性别,班级 from student where 性别=;


--21.查询不姓‘王‘的学生记录。
select 学号,姓名 from student minus 
select 学号,姓名 from student where 姓名 like 王%;


--22.查询每门课最高分的学生的学号、课程号、成绩。
select 学号,课程号 ,成绩 from sc where 成绩 
in (select max(成绩) from sc group by 课程号);


--23.查询与‘李军‘同性别并同班的同学名字。
-- 不包括‘李军‘本人
select 姓名,性别 from student 
where 性别 = (select 性别 from student where 姓名=李军)
and 班级 = (select 班级 from student where 姓名=李军)
and 姓名<>李军;

--24.查询‘男‘教师及其所上的课程。
select  课程号 , 课程名,教师号 from course 
where 教师号 in (select 教师号 from teacher where 性别=);

--25.查询选修‘计算机导论‘课程的‘男‘同学的成绩表。
/*
--‘计算机导论‘课程的课程号
select 课程号,课程名 from course where 课程名=‘计算机导论‘;
--‘男‘同学
select 学号,姓名,性别 from student where 性别=‘男‘;
*/
select 学号,课程号,成绩 from sc where 学号 
in (select 学号 from student where 性别=)
and 课程号=(select 课程号 from course where 课程名=计算机导论);

 

Oracle 小案例