已知学生表S、课程表C 和学生选课表SC,它们的关系模式如下: S(S#,SN,SEX,AGE,DEPT) C(C#,CN) SC(S#,C#,GRADE) 其中:S#为学号,SN 为姓名,SEX 为性别,AGE 为年龄,DEPT 为系别,C#为课程号,CN 为课程名,GRADE 为成绩。试用SQL 语言完成下列问题。 (1) 查询选修了全部课程的学生姓名 select sn from s where not exists (select * from c where not exists (select * from sc where sc.s#=s.s# and sc.c#=c.c#)); (2) 找出选了5门以上课程的学生 select sn from s where s# in (select sn, count(c#) from sc group by sn having count(c#) > 5);(3) 找出所有课程的分数在80分以上的学生 select sn from s where not exists(select * from sc where k.s#=s.s# and grade<80);
select sn from s where not exists(select * from sc where sc.s#=s.s# and grade <80);搜索一下,网络上答案很多的
1: SELECT 学生表.学生 FROM 学生表 WHERE 学生表.学生ID in( select 学生表.学生ID from 选课表 group by 学生表.学生ID having count(*)=(select count(*) from 课程表) ) 2: SELECT 学生表.学生 FROM 学生表,选课表 WHERE 选课表.学生ID=学生表.学生ID GROUP BY 学生表.ID HAVING COUNT(选课表.课程)>5 3: SELECT 学生表.学生 FROM 学生表 WHERE 学生表.分数 >=80 GROUP BY 学生表.学生ID HAVING COUNT(*) =(SELECT COUNT(DISTINCT 课程) FROM 成绩表 ) 印象里是这样的,你也没有给出表结构和表间关系,纯印象给出答案,自己代入试试
假如学生表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);
一、创建表 -- 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;
这三个问题都可以把三个表as成三个字段 然后用条件来筛选 1.条件为等于这个课程的学生的count 2.count出所有的课程 然后条件是学生选的count>=5 3.order by
S(S#,SN,SEX,AGE,DEPT)
C(C#,CN)
SC(S#,C#,GRADE)
其中:S#为学号,SN 为姓名,SEX 为性别,AGE 为年龄,DEPT 为系别,C#为课程号,CN
为课程名,GRADE 为成绩。试用SQL 语言完成下列问题。
(1) 查询选修了全部课程的学生姓名
select sn from s where not exists (select * from c where not exists (select * from sc where sc.s#=s.s# and sc.c#=c.c#));
(2) 找出选了5门以上课程的学生
select sn from s where s# in (select sn, count(c#) from sc group by sn having count(c#) > 5);(3) 找出所有课程的分数在80分以上的学生
select sn from s where not exists(select * from sc where k.s#=s.s# and grade<80);
WHERE 学生表.学生ID in(
select 学生表.学生ID from 选课表
group by 学生表.学生ID
having count(*)=(select count(*) from 课程表)
)
2: SELECT 学生表.学生 FROM 学生表,选课表
WHERE 选课表.学生ID=学生表.学生ID
GROUP BY 学生表.ID
HAVING COUNT(选课表.课程)>5
3:
SELECT 学生表.学生 FROM 学生表 WHERE 学生表.分数 >=80 GROUP BY 学生表.学生ID
HAVING COUNT(*) =(SELECT COUNT(DISTINCT 课程) FROM 成绩表 )
印象里是这样的,你也没有给出表结构和表间关系,纯印象给出答案,自己代入试试
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);
一、创建表
-- 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;
1.条件为等于这个课程的学生的count
2.count出所有的课程 然后条件是学生选的count>=5
3.order by