05 综合练习

create table student(
id number(3) PRIMARY key,
name VARCHAR2(20) not null,
sex varchar2(4),
birth number(4),
department varchar2(20),
address VARCHAR2(50))

create table score(
id number(3)PRIMARY key,
stu_id number(3)not null,
c_name VARCHAR(20),
grade number(3)
);

insert into student values(901,'张老大','男',1985,'计算机系','北京市海淀区');
insert into student values(903,'张老二','男',1986,'中文系','北京市昌平区');
insert into student values(904,'张三','女',1990,'中文系','湖南省永州市');
insert into student values(905,'李四','男',1990,'英语系','辽宁省阜新市');
insert into student values(906,'王五','女',1991,'英语系','福建省厦门市');
insert into student values(907,'赵六','男',1988,'计算机系','湖南省衡阳市');

insert into score values(1,901,'计算机',98);
insert into score values(2,901,'英语',80);
insert into score values(3,902,'计算机',65);
insert into score values(4,902,'中文',88);
insert into score values(5,903,'中文',95);
insert into score values(6,904,'计算机',70);
insert into score values(7,904,'英语',920);
insert into score values(8,905,'英语',94);
insert into score values(9,906,'计算机',90);
insert into score values(10,906,'英语',85);

select * FROM student;
select *from score;

--rownum 系统自动维护从1开始分页查询

select s1.*,rownum
from(
select s.*,rownum num
from student s
where rownum <= 4)s1 
where s1.num>=2;

--分页查询

select s1.*
from(
select s.*,rownum num
from student s)s1 
where  s1.num <= 4 and s1.num>=2;

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

select id,name,department
from student;

--3、从student表中查询计算机系和英语系的学生的信息

select * 
from student 
--where department='计算机系' or department='英语系';
where department in('计算机系','英语系');

--4、从student表中查询年龄40~45岁的学生信息

select *
from student
where (EXTRACT(year from sysdate)- birth) between 40 and 45;

--5、从student表中查询每个院系有多少人

select department,count(*)
from student
group by department;

--6、从score表中查询每个科目的最高分

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

--7、查询李四的考试科目（c_name）和考试成绩（grade）
--注意：'=！只有在确定结果是一个的情况下使用，不确定的使用用'in！

select score.c_name,grade from student,score
where student.name='王五' and student.id = score.stu_id;

select c_name,grade
from score 
where score.stu_id in(
 select id from student where name='王五' 
);

select c_name,grade
from score 
where EXISTS( --是否满足下面的子查询
 select id from student where name='王五' and student.id = score.stu_id
);

--8、用内连接的方式查询所有学生的信息和考试信息

select * 
from student s1 inner join score s2 on s1.id=s2.stu_id;

--9、计算每个学生的总成绩

select s1.name,sum(s2.grade)
from student s1 inner join score s2 on s1.id=s2.stu_id group by s1.name;

--10、计算每个考试科目的平均成绩

select c_name,avg(grade)
from score 
group by c_name;

--11、查询计算机成绩低于95的学生信息

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

--12、查询同时参加计算机和英语考试的学生的信息

select s1.stu_id 
from (
select * from score where c_name='计算机')s1 ,
(select * from score where c_name='英语')s2
where s1.stu_id=s2.stu_id;

--13、将计算机考试成绩按从高到低进行排序

 select * 
 from score 
 where c_name='计算机' order by grade desc;

-- 14、从student表和score表中查询出学生的学号，
-- 然后合并查询结果UNION与union all

   select id from student union all select stu_id from score;

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

  select s1.name,s1.department,s2.c_name,s2.grade
  from (select * from student where  name like '张%' or name like '王%')s1 left join score s2 on s1.id=s2.stu_id;

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

  select s1.name,(EXTRACT(year from sysdate)-s1.birth)age,s1.department,s2.c_name,s2.grade
  from (select * from student where  address like '%湖南%')s1 left join score s2 on s1.id=s2.stu_id;

xiaoyouyu

叨叨几句...