Oracle数据库建表、查询

Oracle数据库建表、查询

一. 创建三张表：
1.学生信息表(stuInfo): 学号(主键), 姓名(不能为空), 性别(只能是男或女), 生日

` create table stuInfo(
  2  stuInfoID number primary key,
  3  stuInfoName varchar2(32) not null,
  4  stuInfoSex varchar2(32) not null check(stuInfoSex in('男','女')),
  5  stuInfoBrithday date
  6  )
  7  /`

2.课程信息表(subInfo)：编号（主键），课程名（不能为空）

create table subInfo(
  2  subInfoID number primary key,
  3  subInfoName varchar2(32) not null
  4  )
  5  /

3.分数信息表(scoreInfo)：编号（主键），学号（外键），课程号（外键），分数（0-100之间）

 create table scoreInfo(
  2  scoreInfoID number primary key,
  3   scoreInfo_stuInfoID number references stuInfo(stuInfoID),
  4  scoreInfo_subInfoID number references subInfo(subInfoID),
  5  score number check(score >= 0 and score <= 100)
  6  )
  7  /

二. 分别向三张表中插入数据，不少于5行

stuInfo表插入数据:

//stuInfo表
insert into stuInfo values(1,'张三','男',to_date('1990-01-01','yyyy-MM-dd'));
insert into stuInfo values(2,'李四','男',to_date('1990-02-01','yyyy-MM-dd'));
insert into stuInfo values(3,'王五','女',to_date('1990-03-01','yyyy-MM-dd'));
insert into stuInfo values(4,'马六','男',to_date('1990-04-01','yyyy-MM-dd'));
insert into stuInfo values(5,'刘七','女',to_date('1990-05-01','yyyy-MM-dd'));
insert into stuInfo values(6,'小明','男',to_date('2003-06-01','yyyy-MM-dd'));
insert into stuInfo values(7,'小红','女',to_date('2002-07-01','yyyy-MM-dd'));
insert into stuInfo values(8,'小亮','男',to_date('2002-07-01','yyyy-MM-dd'));

stuInfo表插入数据后的效果：

select stuInfoID as 学号,stuInfoName as 姓名,stuInfoSex as 性别,stuInfoBrithday as 生日 from stuInfo;

subInfo表插入数据：

//subInfo表
insert into subInfo values(1,'语文');
insert into subInfo values(2,'数学');
insert into subInfo values(3,'英语');
insert into subInfo values(4,'物理');
insert into subInfo values(5,'化学');
insert into subInfo values(6,'生物');
insert into subInfo values(7,'地理');
insert into subInfo values(8,'历史');

subInfo表插入数据后的效果：

select subInfoID as 编号,subInfoName as 课程名 from subInfo;

score表插入数据：

//score表
insert into scoreInfo values(1,1,1,95);
insert into scoreInfo values(2,2,2,97);
insert into scoreInfo values(3,3,3,100);
insert into scoreInfo values(4,4,4,86);
insert into scoreInfo values(5,5,5,89);
insert into scoreInfo values(6,6,6,91);
insert into scoreInfo values(7,7,7,93);
insert into scoreInfo values(8,8,8,90);

scoreInfo表插入数据后的效果：

select scoreInfoId as 编号,scoreInfo_stuInfoID as 学号,scoreInfo_subInfoID as 课程号,score as 分数 from scoreInfo;

三. 创建新表stu1并将stuinfo中的性别为女的学生的学号，姓名和性别的信息插入到该表中

create table stu1 as select stuInfoId,stuInfoName,stuInfoSex from stuInfo where stuInfoSex='女';

效果：

select stuInfoId as 学号,stuInfoName as 姓名,stuInfoSex as 性别 from stu1;

四. 完成下列查询：
1.查询所有姓张的学员信息(模糊查询)

select stuInfoId as 学号,stuInfoName as 姓名,stuInfoSex as 性别 ,stuInfoBrithday as 生日 from stuInfo where stuInfoName like '张%';

2.查询所有20岁以上的学员信息(用日期函数实现)

select stuInfoId as 学号,stuInfoName as 姓名,stuInfoSex as 性别 ,stuInfoBrithday as 生日 from stuInfo where months_between(sysdate,stuInfoBrithday)/12>20;

3.查询同一天出生的学员信息（表连接）

 select a .stuInfoID as 编号,a.stuInfoName as 姓名,a.stuInfoSex as 性别,a.stuInfoBrithday as 生日,b.score as 成绩 from stuInfo a left join scoreInfo b on a.stuInfoID = b.scoreInfoID where a.stuInfoBrithday in (select stuInfoBrithday from stuInfo group by stuInfoBrithday having count(stuInfoBrithday)>1);

4.查询参加考试的所有学员的名称，科目和成绩(按成绩排序)

select c.scoreInfoID as 学生编号,a.stuInfoName as 学生姓名,b.subInfoName as 课程名称 ,c.score as 课程成绩 from stuInfo a,subInfo b,scoreInfo c where a.stuInfoID = c.scoreInfo_stuInfoID and b.subInfoID = c.scoreInfo_subInfoID order by c.score asc;

5.查询所有没有参加考试的学员学号,姓名，只要有1门课没考都要查询出来(子查询)

select stuInfoID as 学员学号,stuInfoName as 姓名 from stuInfo where stuInfoID = (select scoreInfo_stuInfoID from scoreInfo where score is null);

6.查询出没有学生考试的课程名称

select b.subInfoName as 课程名称 from stuInfo a,subInfo b, scoreInfo c where a.stuInfoID = c.scoreInfo_stuInfoID and b.subInfoID = c.scoreInfo_subInfoID and c.score is null;