一、实验目的
1. 掌握oracle中常用的sql语句的基本操作。
2. 掌握oracle中多中查询的具体实现。
二、实验任务
oracle学生选课查询——学生表student、成绩表sc、课程表c
1.创建学生表,表结构(学号,姓名,年龄,性别,系别,出生地),插入几条记录
2.创建成绩表,表结构(学号,课程号,成绩),插入几条记录
3.创建课程表,表结构(课程号,课程名,教师)
4.完成查询
创建学生表,表结构(学号,姓名,年龄,性别,系别,出生地),插入几条记录
create table student(sid number(3),sname varchar2(10),age number(2),sex char(3),department varchar2(10),birthplace varchar2(10));
insert into s values(100,'曾华',21,'男','信息系','威海');
insert into s values(105,'张明',20,'男','信息系','烟台');
insert into s values(108,'王小丽',21,'女','旅游系','威海');
insert into s values(101,'李军',19,'男','金融系','青岛');
insert into s values(107,'王芳',24,'女','机电系','临沂');
insert into s values(102,'刘红英',22,'男','信息系','枣庄');
insert into s values(109,'唐奇',21,'男','机电系','威海');
insert into s values(103,'赵晨',21,'女','金融系','济南');
创建成绩表,表结构(学号,课程号,成绩),插入几条记录
create table sc(sid number(3),cid number(4),grade number(5,2));
insert into sc values(103,3245,86);
insert into sc values(105,3245,75);
insert into sc values(109,3245,68);
insert into sc values(103,3105,92);
insert into sc values(105,3105,88);
insert into sc values(109,3105,76);
insert into sc values(101,3105,64);
insert into sc values(107,3105,91);
insert into sc values(108,3105,78);
insert into sc values(101,6166,85);
insert into sc values(107,6166,58);
insert into sc values(108,6166,81);
insert into sc values(100,9888,66);
insert into sc values(102,9888,59);
创建课程表,表结构(课程号,课程名,教师)
create table c(cid number(4),cname varchar2(20),teacher varchar2(10));
insert into c values(3105,'计算机导论','王萍');
insert into c values(3245,'操作系统','李诚');
insert into c values(6166,'英语','张旭');
insert into c values(9888,'数学','刘冰');
三、实验内容与要求
1.根据具体的要求建立表;
2.在表中添加数据;
3.完成对应的sql语句的编写;
4.截图给出查询出来的结果;
具体完成的sql语句:
1.李老师所教的课程号、课程名称
2.年龄大于23岁的女学生的学号和姓名
3.“王小丽”所选修的全部课程名称
4.所有成绩都在80分以上的学生姓名及所在系
5.没有选修“操作系统”课的学生姓名
6.与“王小丽”同乡的男生姓名及所在系英语成绩比数学成绩好的学生
7.选修同一门课程时,女生比男生成绩好的学生名单
8.至少选修两门以上课程的学生姓名、性别
9.选修了李老师所讲课程的学生人数;
10.没有选修李老师所讲课程的学生人数;
11.“操作系统”课程得最高分的学生姓名、性别、所在系;
四、实验过程
1.根据具体的要求建立表;
创建学生表,表结构(学号,姓名,年龄,性别,系别,出生地),插入几条记录
create table student(sid number(3),sname varchar2(10),age number(2),sex char(3),department varchar2(10),birthplace varchar2(10));
创建成绩表,表结构(学号,课程号,成绩),插入几条记录
create table sc(sid number(3),cid number(4),grade number(5,2));
创建课程表,表结构(课程号,课程名,教师)
create table c(cid number(4),cname varchar2(20),teacher varchar2(10));
2.在表中添加数据;
学生表
insert into student values(100,'曾华',21,'男','信息系','威海');
insert into student values(105,'张明',20,'男','信息系','烟台');
insert into student values(108,'王小丽',21,'女','旅游系','威海');
insert into student values(101,'李军',19,'男','金融系','青岛');
insert into student values(107,'王芳',24,'女','机电系','临沂');
insert into student values(102,'刘红英',22,'男','信息系','枣庄');
insert into student values(109,'唐奇',21,'男','机电系','威海');
insert into student values(103,'赵晨',21,'女','金融系','济南');
成绩表
insert into sc values(103,3245,86);
insert into sc values(105,3245,75);
insert into sc values(109,3245,68);
insert into sc values(103,3105,92);
insert into sc values(105,3105,88);
insert into sc values(109,3105,76);
insert into sc values(101,3105,64);
insert into sc values(107,3105,91);
insert into sc values(108,3105,78);
insert into sc values(101,6166,85);
insert into sc values(107,6166,58);
insert into sc values(108,6166,81);
insert into sc values(100,9888,66);
insert into sc values(102,9888,59);
课程表
insert into c values(3105,'计算机导论','王萍');
insert into c values(3245,'操作系统','李诚');
insert into c values(6166,'英语','张旭');
insert into c values(9888,'数学','刘冰');
3.具体完成的sql语句:
1.李老师所教的课程号、课程名称
select cid, cname from c where teacher like '李%';
2.年龄大于23岁的女学生的学号和姓名
select sid, sname from student where age > 23 and sex = '女';
3.“王小丽”所选修的全部课程名称
select cname from c, sc, student where c.cid = sc.cid and student.sid = sc.sid and sname = '王小丽';
4.所有成绩都在80分以上的学生姓名及所在系
select sname, department from student where sid = (select sid from sc group by sid having min(grade)>=80);
5.没有选修“操作系统”课的学生姓名
select sname from student where sid not in(select sc.sid from sc,c where cname='操作系统'and sc.cid=c.cid);
6.与“王小丽”同乡的男生姓名及所在系英语成绩比数学成绩好的学生
select sname,department from student where sex='男' and birthplace=(select birthplace from student where sname='王小丽') and sid = (select sid from student where (select grade from sc join c on sc.cid=c.cid where sc.sid=student.sid and cname='英语') >(select grade from sc join c on sc.cid=c.cid where sc.sid=student.sid and cname='数学'));
7.选修同一门课程时,女生比男生成绩好的学生名单
select sname,department from student where sex='男' and birthplace=(select birthplace from student where sname='王小丽') and sid = (select sid from student where (select grade from sc join c on sc.cid=c.cid where sc.sid=student.sid and cname='英语') >(select grade from sc join c on sc.cid=c.cid where sc.sid=student.sid and cname='数学'));
8.至少选修两门以上课程的学生姓名、性别
select sname,sex from student where sid in(select sid from sc group by sid having count(sid)>=2);
9.选修了李老师所讲课程的学生人数;
select count(*) from sc where cid=(select cid from c where teacher like '李%');
10. 没有选修李老师所讲课程的学生人数;
select count(DISTINCT sid) from sc where sid not in(select sid from sc,c where sc.cid=c.cid and teacher like '李%');
11.“操作系统”课程得最高分的学生姓名、性别、所在系;
select sname,sex,department from student,sc,c where student.sid=sc.sid and sc.cid=c.cid and cname='操作系统' and grade=(select max(grade)from sc,c where sc.cid=c.cid and cname='操作系统');
五、实验总结
通过本次实验,我对于sql语句的基本使用更加熟练了,也有了更加深入的理解。主要体现在:我学会了掌握oracle中常用的sql语句的基本操作;掌握oracle中查询的具体实现;学习了SQL语句的设计与编写;能够在常用场景下用sql语句来解决问题。