表连接练习一

表连接练习一

导入表:

CREATE TABLE COURSE
  (
  CNO   VARCHAR2 (5) NOT NULL,
  CNAME VARCHAR2 (15) NOT NULL,
  TNO   VARCHAR2 (10) NOT NULL,
  CONSTRAINT PK_COURSE PRIMARY KEY (CNO)
  );

COMMENT ON COLUMN COURSE.CNO IS '学科编号';
COMMENT ON COLUMN COURSE.CNAME IS '学科名称';
COMMENT ON COLUMN COURSE.TNO IS '教师编号';

CREATE TABLE GRADE
  (
  LOW  NUMBER (3) NOT NULL,
  UPP  NUMBER (3) NOT NULL,
  RANK CHAR (1) NOT NULL,
  CONSTRAINT PK_GRADE PRIMARY KEY (LOW, UPP)
  );

COMMENT ON COLUMN GRADE.LOW IS '分数范围下限';
COMMENT ON COLUMN GRADE.UPP IS '分数范围上限';
COMMENT ON COLUMN GRADE.RANK IS '评级';

CREATE TABLE SCORE
  (
  SNO    VARCHAR2 (3) NOT NULL,
  CNO    VARCHAR2 (5) NOT NULL,
  DEGREE NUMBER (10,1) NOT NULL,
  CONSTRAINT PK_SCORE PRIMARY KEY (SNO, CNO)
  );

COMMENT ON COLUMN SCORE.SNO IS '学生编号';
COMMENT ON COLUMN SCORE.CNO IS '学科编号';
COMMENT ON COLUMN SCORE.DEGREE IS '成绩';

CREATE TABLE STUDENT
  (
  SNO       VARCHAR2 (3) NOT NULL,
  SNAME     VARCHAR2 (6) NOT NULL,
  SSEX      VARCHAR2 (3) NOT NULL,
  SBIRTHDAY DATE NOT NULL,
  CLASS     VARCHAR2 (5) NOT NULL
  );

COMMENT ON COLUMN STUDENT.SNO IS '学生编号';
COMMENT ON COLUMN STUDENT.SNAME IS '学生姓名';
COMMENT ON COLUMN STUDENT.SSEX IS '学生性别';
COMMENT ON COLUMN STUDENT.SBIRTHDAY IS '生日';
COMMENT ON COLUMN STUDENT.CLASS IS '班级';

CREATE TABLE TEACHER
  (
  TNO       VARCHAR2 (3) NOT NULL,
  TNAME     VARCHAR2 (6) NOT NULL,
  TSEX      VARCHAR2 (3) NOT NULL,
  TBIRTHDAY DATE NOT NULL,
  PROF      VARCHAR2 (9) NOT NULL,
  DEPART    VARCHAR2 (15) NOT NULL,
  CONSTRAINT PK_TEACHER PRIMARY KEY (TNO)
  );

COMMENT ON COLUMN TEACHER.TNO IS '教师编号';
COMMENT ON COLUMN TEACHER.TNAME IS '教师姓名';
COMMENT ON COLUMN TEACHER.TSEX IS '性别';
COMMENT ON COLUMN TEACHER.TBIRTHDAY IS '生日';
COMMENT ON COLUMN TEACHER.PROF IS '职称';
COMMENT ON COLUMN TEACHER.DEPART IS '部门';

INSERT INTO COURSE (CNO, CNAME, TNO)
VALUES ('3-105', '计算机导论', '825');
INSERT INTO COURSE (CNO, CNAME, TNO)
VALUES ('3-245', '操作系统', '804');
INSERT INTO COURSE (CNO, CNAME, TNO)
VALUES ('6-166', '数据电路', '856');
INSERT INTO COURSE (CNO, CNAME, TNO)
VALUES ('9-888', '高等数学', '100');


INSERT INTO GRADE (LOW, UPP, RANK)
VALUES (90, 100, 'A');
INSERT INTO GRADE (LOW, UPP, RANK)
VALUES (80, 89, 'B');
INSERT INTO GRADE (LOW, UPP, RANK)
VALUES (70, 79, 'C');
INSERT INTO GRADE (LOW, UPP, RANK)
VALUES (60, 69, 'D');
INSERT INTO GRADE (LOW, UPP, RANK)
VALUES (0, 59, 'E');

INSERT INTO SCORE (SNO, CNO, DEGREE)
VALUES ('103', '3-245', 86);
INSERT INTO SCORE (SNO, CNO, DEGREE)
VALUES ('105', '3-245', 75);
INSERT INTO SCORE (SNO, CNO, DEGREE)
VALUES ('109', '3-245', 68);
INSERT INTO SCORE (SNO, CNO, DEGREE)
VALUES ('103', '3-105', 92);
INSERT INTO SCORE (SNO, CNO, DEGREE)
VALUES ('105', '3-105', 88);
INSERT INTO SCORE (SNO, CNO, DEGREE)
VALUES ('109', '3-105', 76);
INSERT INTO SCORE (SNO, CNO, DEGREE)
VALUES ('101', '3-105', 64);
INSERT INTO SCORE (SNO, CNO, DEGREE)
VALUES ('107', '3-105', 91);
INSERT INTO SCORE (SNO, CNO, DEGREE)
VALUES ('108', '3-105', 78);
INSERT INTO SCORE (SNO, CNO, DEGREE)
VALUES ('101', '6-166', 85);
INSERT INTO SCORE (SNO, CNO, DEGREE)
VALUES ('107', '6-166', 79);
INSERT INTO SCORE (SNO, CNO, DEGREE)
VALUES ('108', '6-166', 81);


INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS)
VALUES ('108', '曾华', '男', TO_DATE ('1977-09-01', 'YYYY-MM-DD'), '95033');
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS)
VALUES ('105', '匡明', '男', TO_DATE ('1975-10-02', 'YYYY-MM-DD'), '95031');
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS)
VALUES ('107', '王丽', '女', TO_DATE ('1976-01-23', 'YYYY-MM-DD'), '95033');
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS)
VALUES ('101', '李军', '男', TO_DATE ('1976-02-20', 'YYYY-MM-DD'), '95033');
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS)
VALUES ('109', '王芳', '女', TO_DATE ('1975-02-10', 'YYYY-MM-DD'), '95031');
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS)
VALUES ('103', '陆君', '男', TO_DATE ('1974-06-03', 'YYYY-MM-DD'), '95031');

INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES ('804', '李诚', '男', TO_DATE ('1958-12-02', 'YYYY-MM-DD'), '副教授', '计算机系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES ('856', '张旭', '男', TO_DATE ('1969-03-12', 'YYYY-MM-DD'), '讲师', '电子工程系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES ('825', '王萍', '女', TO_DATE ('1972-05-05', 'YYYY-MM-DD'), '助教', '计算机系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES ('831', '刘冰', '女', TO_DATE ('1977-08-14', 'YYYY-MM-DD'), '助教', '电子工程系');

题目

--查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from student;
--查询教师所有的单位即不重复的Depart列。
select distinct depart from teacher;
select  depart from teacher  group by depart;
--查询Student表的所有记录。
select * from student;
--查询Score表中成绩在60到80之间的所有记录。
select * from score where degree between 60 and 80;
--查询Score表中成绩为85,86或88的记录。
select * from score where degree in(85,86,88);
--查询Student表中“95031”班或性别为“女”的同学记录。
select * from student where class='95031' or ssex='女';
--以Class降序查询Student表的所有记录。
select * from student order by class desc;

--以Cno升序、Degree降序查询Score表的所有记录。
select * from score order by cno,degree desc;
--查询“95031”班的学生人数。
select count(1) from student where CLASS='95031';
select count(1) from student group by class having class = '95031';
--查询Score表中的最高分的学生学号和课程号。
select sno,cno from score where degree =(
 select max(degree) from score
);
--查询‘3-105’号课程的平均分。
select avg(degree) from score where cno='3-105';
--查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select cno, avg(degree) 
  from score
 group by cno
having count(*) >= 5 and cno like '3%'

--查询学生的所有选课程分数中最低分大于70,最高分小于90的学生编号Sno列。
select sno from score group by sno having min(degree)>70 and max(degree)<90;

--查询所有学生的Sname、Cno和Degree列。
select sname, cno, degree
  from student stu, score sco
 where stu.sno = sco.sno ;
 
 select sname,cno,degree from student stu join score sco on stu.sno = sco.sno;

--查询所有学生的Sno、Cname和Degree列。
select sno, cname, degree
  from course cou, score sco
 where sco.cno= cou.cno;

--查询所有学生的Sname、Cname和Degree列。
select sname, cname, degree
  from student stu, course cou, score sco
 where stu.sno = sco.sno
   and sco.cno= cou.cno;

select sname, cname, degree
  from student stu
  join score sco
    on stu.sno = sco.sno
  join course cou
    on sco.cno = cou.cno;
   
--查询“95033”班所选课程的平均分
select avg(degree)
  from student stu, score sco
 where stu.sno = sco.sno
   and class = '95033';

--查询95033班级学生的学生编号
select sno from student where class='95033';


select avg(degree)
  from score
 where sno in (select sno from student where class = '95033');
 
select * from STUDENT;
select * from TEACHER;
select * from COURSE;
select * from GRADE;
select * from SCORE;


--现查询所有同学的Sno、Cno和rank列。
select sno,cno,rank from grade g,score s where s.degree between low and upp;  --表连接条件
--查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select *
  from score
 where cno = '3-105'
   and degree > (select degree
                   from score
                  where sno = '109'
                    and cno = '3-105');
                    
select *
  from score
 where cno = '3-105'
   and degree > (select degree
                   from score
                  where sno = '109'
                    and cno = '3-105');
                    
--查询到所有选择3-105课程的学生
select * from score where cno = '3-105';
--查询到109号学生的3-105课程的分数
select degree from score where sno='109' and cno='3-105';


--查询score中选学一门以上课程的同学中分数为非所选科目最高分成绩的记录。
select * from score s1 where sno in(select sno from score group by sno having count(1)>1) and degree < (所在科目的最高分);

select *
  from score s1
 where sno in (select sno from score group by sno having count(1) > 1)
   and degree < (select max(degree) from score s2 where s2.cno = s1.cno);

--求某一个科目的最高分
select max(degree) from score s2 where s2.cno = s1.cno;


select *
  from score
 where sno in
       (select sno
          from score
         group by sno
        having count(1) > 1) and degree <> (select max(degree) from score);
--查询score中选学一门以上课程的同学
select sno from score group by sno having count(1)>1;

--查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select *
  from score
 where degree > (select degree
                   from score
                  where sno = '109'
                    and cno = '3-105');
--查询和学号为109的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select sno, sname, sbirthday
  from student
 where to_char(sbirthday, 'yyyy') =  --每一个学生的生日的字符串
       (select to_char(sbirthday, 'yyyy') from student where sno = '109'); --109学生的生日的字符串
       
select Sbirthday from student where sno = '109';


--查询“张旭“教师任课的学生成绩。
select s.*
  from score s, teacher t, course c
 where s.cno = c.cno
   and t.tno = c.tno
   and t.tname = '张旭';
--子查询
select *
  from score
 where cno =
       (select cno
          from course
         where tno = (select tno from teacher where tname = '张旭'));
   
--张旭老师教授的教师编号,根据教师编号找到教授的课程编号
select tno from teacher where tname='张旭';
select cno from course where tno = '856';
select * from score where cno = '6-166';




--查询选修某课程的同学人数多于5人的教师姓名。
select tname
  from teacher t, course c
 where t.tno = c.tno
 and cno in (select cno from score
   group by cno having(count(1))>5);
   
--分数表 中的数据根据课程进行分组,找到每个课程的所选人数>5人课程编号
select cno from score group by cno having count(1)>5;

--根据课程编号可以查询教师编号
select tno from course where cno in(select cno from score group by cno having count(1)>5);

--根据教师编号查询教师姓名
select tname
  from teacher
 where tno in
       (select tno
          from course
         where cno in
               (select cno from score group by cno having count(1) > 5));

   

--分数表
select * from score;   
select * from teacher;  

--查询95033班和95031班全体学生的记录
select * from student where class in('95033','95031');
--查询存在有85分以上成绩的课程Cno.
select distinct cno from score where degree >85;
--查询出“计算机系“教师所教课程的成绩表。
select * from score where cno in(
       select cno from course c,teacher t where c.tno=t.tno
       and t.depart='计算机系'
);

--1)查询计算机系的教师编号
select tno from teacher where depart = '计算机系';

--2)在课程表中根据教师编号查询课程编号
select cno from course where tno in (select tno from teacher where depart = '计算机系');

--3)在分数表中根据课程编号查询分数
select * from score where cno in (select cno from course where tno in (select tno from teacher where depart = '计算机系'));

--查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的任何一个同学的成绩的Cno、Sno和Degree,并按Degree从高到低次序排序。
select cno, sno, degree
  from score
 where cno = '3-105'
   and degree > (select min(degree) from score where cno = '3-245')
 order by degree desc;

--查询选修编号为“3-105“课程且成绩至少高于自己的选修编号为“3-245”的的Cno、Sno和Degree,并按Degree从高到低次序排序。
select cno, sno, degree
  from score s1
 where cno = '3-105'
   and degree > (select degree from score s2 where s2.sno = s1.sno and s2.cno = '3-245')
 order by degree desc;
 
--
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值