17,086
社区成员
发帖
与我相关
我的任务
分享
一、创建表
-- Create table
create table T_STUDENTS
(
ID NUMBER,
NAME VARCHAR2(50),
CODE VARCHAR2(255)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table T_STUDENTS
is '学生表';
-- Create table
create table T_CHOOSE
(
ID NUMBER not null,
SID NUMBER,
CID NUMBER,
GRADE NUMBER(4)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table T_CHOOSE
is '选课';
-- Create table
create table T_COURSE
(
ID NUMBER not null,
COURSENAME VARCHAR2(50)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table T_COURSE
is '课程表';
二、插数据
insert into t_students (ID, NAME, CODE)
values (1, '张山', '');
insert into t_students (ID, NAME, CODE)
values (2, '李四', '');
insert into t_students (ID, NAME, CODE)
values (3, '王六', '');
insert into t_students (ID, NAME, CODE)
values (4, '小明', '');
insert into t_students (ID, NAME, CODE)
values (5, '小刚', '');
insert into t_students (ID, NAME, CODE)
values (6, '小虎', '');
insert into t_choose (ID, SID, CID, GRADE)
values (10, 4, 1, 80);
insert into t_choose (ID, SID, CID, GRADE)
values (11, 4, 2, 76);
insert into t_choose (ID, SID, CID, GRADE)
values (12, 4, 3, 67);
insert into t_choose (ID, SID, CID, GRADE)
values (13, 4, 4, 86);
insert into t_choose (ID, SID, CID, GRADE)
values (14, 4, 5, 78);
insert into t_choose (ID, SID, CID, GRADE)
values (15, 4, 6, 67);
insert into t_choose (ID, SID, CID, GRADE)
values (16, 4, 7, 67);
insert into t_choose (ID, SID, CID, GRADE)
values (17, 4, 8, 78);
insert into t_choose (ID, SID, CID, GRADE)
values (1, 1, 1, 78);
insert into t_choose (ID, SID, CID, GRADE)
values (2, 1, 2, 78);
insert into t_choose (ID, SID, CID, GRADE)
values (3, 1, 3, 78);
insert into t_choose (ID, SID, CID, GRADE)
values (4, 1, 4, 96);
insert into t_choose (ID, SID, CID, GRADE)
values (5, 1, 5, 69);
insert into t_choose (ID, SID, CID, GRADE)
values (6, 1, 6, 66);
insert into t_choose (ID, SID, CID, GRADE)
values (7, 2, 1, 96);
insert into t_choose (ID, SID, CID, GRADE)
values (8, 3, 3, 90);
insert into t_choose (ID, SID, CID, GRADE)
values (9, 3, 1, 92);
insert into t_course (ID, COURSENAME)
values (1, '语文');
insert into t_course (ID, COURSENAME)
values (2, '数学');
insert into t_course (ID, COURSENAME)
values (3, '英语');
insert into t_course (ID, COURSENAME)
values (4, '化学');
insert into t_course (ID, COURSENAME)
values (5, '历史');
insert into t_course (ID, COURSENAME)
values (6, '政治');
insert into t_course (ID, COURSENAME)
values (7, '生物');
insert into t_course (ID, COURSENAME)
values (8, '美术');
三、查询
SELECT T.*, T.ROWID FROM T_CHOOSE T;
SELECT T.*, T.ROWID FROM T_STUDENTS T;
SELECT T.*, T.ROWID FROM T_COURSE T;
1) 查询选修了全部课程的学生姓名
SELECT DISTINCT TT.NAME
FROM T_STUDENTS TT, T_CHOOSE TC, T_COURSE TCS
WHERE TT.ID = TC.SID
AND TC.CID = TCS.ID
AND (SELECT COUNT(1) FROM T_CHOOSE TSE WHERE TSE.SID = TT.ID) =
(SELECT COUNT(1) FROM T_COURSE);
2) 找出选了5门以上课程的学生
SELECT DISTINCT TT.NAME
FROM T_STUDENTS TT, T_CHOOSE TC
WHERE TT.ID = TC.SID
AND EXISTS (SELECT 1
FROM T_CHOOSE TSE
WHERE TSE.SID = TT.ID
GROUP BY TSE.SID
HAVING COUNT(1) > 5);
3) 找出所有课程的分数在80分以上的学生
SELECT DISTINCT TT.NAME
FROM T_STUDENTS TT, T_CHOOSE TC
WHERE NOT EXISTS (SELECT 1
FROM T_CHOOSE TC
WHERE TC.GRADE <= 80
AND TT.ID = TC.SID)
AND TT.ID = TC.SID;
假如学生表s、课程表c 和学生选课表sc,它们的关系模式如下:
s (sid, sn, sex, age, dept)
c (cid, cn)
sc (scid, sid, cid, grade)
其中:sid为学号,sn 为姓名,sex 为性别,age 为年龄,dept 为专业号,cid为课程号,cn
为课程名,grade 为成绩。
1 查询选修了全部课程的学生姓名
select s.*
from s,c,sc
where sc.sid=s.sid
and sc.cid=c.cid
and (select count(*) from c)=(select count(*) from sc where sc.sid=s.sid);--选了全部课程的学生条件判断
2 找出选了5门以上课程的学生
select s.*
from s
where exists
(select 1 from sc where sc.sid=s.sid group by sc.sid having count(sc.cid)>5 );
3 找出所有课程的分数在80分以上的学生
select s.*
from s
where not exists
(select 1 from sc where sc.grade<=80 and s.sid=sc.sid);