pl/sql练习1

     --创建学生表
    CREATE TABLE tb_Student(
      ID number  PRIMARY KEY, --学生编号
      NAME VARCHAR2(20) NOT NULL,--学生姓名
      Sex VARCHAR2(2) CHECK(Sex IN('男','女')) , --学生性别
      Age number, --学生年龄
      Address VARCHAR2(50) , --学生地址
      Tel VARCHAR2(30), --学生电话
      Email VARCHAR2(30)--学生邮箱
    );

    --插入学生表数据
    INSERT INTO tb_Student
    VALUES
    (1,'小强','男',20,'湖南省长沙市伍家岭江南苑9栋203号','0731-4230123','xq@sina.com');
    INSERT INTO tb_Student
    VALUES
    (2,'李云','女',19,'湖南省长沙市东风路东风新村21栋502号','0731-4145268','ly@163.com');
    INSERT INTO tb_Student
    VALUES
    (3,'鲁智深','男',30,'湖南省株洲市601厂宿舍15栋308号','0732-8342567',NULL);
    INSERT INTO tb_Student
    VALUES
    (4,'张飞','男',28,'湖南省郴洲市人民医院20栋301号','0735-2245214',NULL);
    INSERT INTO tb_Student
    VALUES
    (5,'翠花','女',21,'湖南省长沙市望月湖12栋403号','0731-8325124','ch@sina.com');

    --创建考试表
    CREATE TABLE tb_Exam
    (
      ID number ,         --学生编号
      NAME VARCHAR2(20) NOT NULL,                  --课程名称
      Score number CHECK(Score BETWEEN 0 AND 100),          --考试分数
      exam_date DATE,                      --考试时间
      PRIMARY KEY(ID,NAME),   --学生编号和课程名称做联合主键 
      CONSTRAINT tb_exam_fk FOREIGN KEY (id) REFERENCES tb_Student(id)
    );
    --插入数据
    INSERT INTO tb_Exam VALUES(1,'C语言',78,to_date('2004-06-10','yyyy-mm-dd'));
    INSERT INTO tb_Exam VALUES(2,'C语言',90,to_date('2004-06-10','yyyy-mm-dd'));
    INSERT INTO tb_Exam VALUES(3,'C语言',0,NULL);
    INSERT INTO tb_Exam VALUES(3,'VB',35,to_date('2004-07-16','yyyy-mm-dd'));
    INSERT INTO tb_Exam VALUES(4,'VB',35,to_date('2004-07-16','yyyy-mm-dd'));
    INSERT INTO tb_Exam VALUES(5,'VB',85,to_date('2004-07-16','yyyy-mm-dd'));
    INSERT INTO tb_Exam VALUES(2,'网页编程',85,to_date('2004-08-20','yyyy-mm-dd'));
    INSERT INTO tb_Exam VALUES(3,'网页编程',85,NULL);

--1.查询小强的详细信息
select * from tb_Student where name='小强';
--2.查询20岁以上的所有男同学的信息
select * from tb_Student where sex='男' and age>20;
--3.按年龄降序显示学生的信息
select * from tb_Student order by age desc;
--4.按学生ID升序显示学生的ID，姓名，性别，课程，分数
select s.id,s.name,s.sex,e.name classname,e.score 
from tb_Student s,tb_Exam e
where s.id=e.id;
--5.按学生分数降序显示学生的ID，姓名，性别，课程，分数
select s.id,s.name,s.sex,e.name classnamename,e.score 
from tb_Student s,tb_Exam e
where s.id=e.id 
order by e.score desc;
--7.查询所有学生的信息
select * from tb_Student;
--8.显示成绩表的前4条信息
select * from tb_Exam where rownum<=4;
--10.列出所有考试分数的一个总和
select sum(score) from tb_Exam;
--11.列出鲁智深的成绩总和
select sum(e.score) from  Tb_Student s,tb_Exam e 
where s.name='鲁智深' and s.id=e.id;
--12.列出C语言考试的平均分
select avg(score) from tb_Exam where name='C语言';
--14.列出李云参加了几次考试
select count(1) 
from tb_Student s,tb_Exam e
where s.id=e.id and s.name='李云';
--15.列出学生中最大的年龄
select max(age) 
from tb_Student s,tb_Exam e
where s.id=e.id;
--16.列出C语言最高的分数
select max(score) 
from tb_Student s,tb_Exam e
where s.id=e.id and e.name='C语言';
--17.列出每种考试的考试名称和成绩总和
select name,sum(score) 
from tb_Exam 
group by name;
--18.列出每个学生的姓名和考试次数
select s.name,count(*) 
from tb_Student s,tb_Exam e
where s.id=e.id
group by s.name;
--19.列出每个学生的姓名和考试次数,小于2次的不显示
select s.name,count(*) 
from tb_Student s,tb_Exam e
where s.id=e.id
group by s.name
having count(*)>=2; 
--20.显示年龄在20到25岁之间的学生信息
select * from tb_Student where age between 20 and 25;