已知表中'L5'是教师,'SB420'是教室insert into u values ('SCC103', 'Intro. to I.T.', 'L5', 'SB420');
insert into u values ('SCC104', 'Intro. to Software Dev.', 'L12', 'SB420');
insert into u values ('SCC105', 'Basic Programming Concepts', 'L12', 'SB420');
insert into u values ('SCC107', 'Concepts and Practice for S.E.', 'L11', 'SB420');
第一个问题是成对列出使用共同教室的老师还有两个问题都是关于记数,比如列出至少有三个BIT专业的学生上的课程,我用select unit_code from e wherestu_no in (select degree from s group by degree having count(*)>=3 in ('M402') );不行,请教高手 谢谢!
insert into u values ('SCC104', 'Intro. to Software Dev.', 'L12', 'SB420');
insert into u values ('SCC105', 'Basic Programming Concepts', 'L12', 'SB420');
insert into u values ('SCC107', 'Concepts and Practice for S.E.', 'L11', 'SB420');
第一个问题是成对列出使用共同教室的老师还有两个问题都是关于记数,比如列出至少有三个BIT专业的学生上的课程,我用select unit_code from e wherestu_no in (select degree from s group by degree having count(*)>=3 in ('M402') );不行,请教高手 谢谢!
select u.* from u inner join
(select 教室 from u group by 教室 having count(*)>=2) b
on a.教室=b.教室还有两个问题都是关于记数,比如列出至少有三个BIT专业的学生上的课程
正确结果贴出来看看
select distinct u.t from u inner join (select room from u group by room h
aving count(*)>=2) b on u.room =b.room;
insert into l values ('L1', 'A.M.','Goscinski', 'Professor');
insert into l values ('L2', 'G.I.','Webb', 'Professor');
insert into l values ('L3', 'B.J.','Garner', 'Professor');
insert into l values ('L4', 'C.Q.','Zhang', 'Ass. Prof.');
insert into l values ('L5', 'M.','Warren', 'Dr.');
insert into l values ('L6', 'B.B.','Zhou', 'Dr.');
insert into l values ('L7', 'D.','De Paoli', 'Dr.');
insert into l values ('L8', 'J.','Coldwell', 'Dr.');
insert into l values ('L9', 'J.J.','Silcock', 'Dr.');
insert into l values ('L10', 'D.A.','Newlands', 'Dr.');
insert into l values ('L11', 'P.','Horan', 'Mr.');
insert into l values ('L12', 'R.A.','Dew', 'Mr.');
insert into l values ('L13', 'L.','Liu', 'Ms.');
insert into l values ('L14', 'P.','Padhye', 'Dr.');是不是外联接+所提供的内联接完成?谢谢!
但用这条搜索不到,我的COUNT不是很了解。
select distinct l.name, l.title from l,u where unit_code in (select distinct unit_code from e where stu_no in (select stu_no from s where degree ='M402') AND stu_no in (select stu_no from s group by stu_no having count(*)>=3));
\! echo "Creating Lecturer (l) table"create table l
(staff_no char(3),
initials char(6),
name char(20),
title char(10),
primary key(staff_no));\! echo "Creating Degree (d) table"create table d
(degree char(6),
degree_name char(20),
primary key (degree));\! echo "Creating Room (r) table"create table r
(room_no char(5),
room_size int,
primary key (room_no));\! echo "Creating Student (s) table"create table s
(stu_no char(8),
initials char(6),
name char(20),
street char(20),
city char(15),
degree char(6),
primary key (stu_no),
foreign key (degree) references d(degree));\! echo "Creating Unit (u) table"create table u
(unit_code char(6),
name char(30),
staff_no char(3),
room_no char(5),
primary key (unit_code),
foreign key (staff_no) references l(staff_no),
foreign key (room_no) references r(room_no));\! echo "Creating Enrolment (e) table"create table e
(unit_code char(6),
stu_no char(8),
result int,
grade char(3),
foreign key (unit_code) references u(unit_code),
foreign key (stu_no) references s(stu_no),
primary key (unit_code, stu_no));insert into l values ('L1', 'A.M.','Goscinski', 'Professor');
insert into l values ('L2', 'G.I.','Webb', 'Professor');
insert into l values ('L3', 'B.J.','Garner', 'Professor');
insert into l values ('L4', 'C.Q.','Zhang', 'Ass. Prof.');
insert into d values ('S375', 'B.Computing(CS/SD)');
insert into d values ('S376', 'B.Computing(ISD)');
insert into d values ('S367', 'B.Engineering');
insert into d values ('M402', 'B. Info. Tech.');insert into r values ('SB420', 200);
insert into r values ('SC422', 80);
insert into r values ('SC424', 80);insert into s values ('200001', 'J.','Appleton', '1 Ardmore St.', 'Rothesay', 'S375');
insert into s values ('200002', 'E.','Brown', '5 Green St.', 'Geelong', 'S367');
insert into s values ('200003', 'J.','Coughlin', '1 York Cl.', 'Masham', 'S376');
insert into s values ('200004', 'P.','Dew', '3 Caledonian Rd.', 'Angel Islington', 'S375');
insert into s values ('200005', 'A.','Einstein', '5 Relative St.', 'Vienna', 'S375');
insert into s values ('200006', 'F.R.G.','Webb', '17 Pond St.', 'Adelaide', 'S376');
insert into s values ('200007', 'B.','Green', '25 Verdant St.', 'Greenesborough', 'S375');
insert into s values ('200008', 'X.','Hobson', '1 Choice St.', 'Wellington', 'S376');
insert into s values ('200009', 'I.','Isaacson', '3 Schlepp St.', 'Haifa', 'S366');
insert into s values ('200010', 'J.','Juliffe', '7 Pigdons Rd.', 'Geelong', 'S376');
insert into s values ('200011', 'J.','Knox', '1 Main St.', 'Edinburgh', 'M402');
insert into s values ('200012', 'H.','Littleton', '4 Trumpet St.', 'New Orleans', 'S367');
insert into s values ('200013', 'G.','Marx', '5 Eumore St.', 'Los Angeles', null);
insert into s values ('200014', 'I.','Newton', '41 Apple St.', 'Greenwich', 'S375');
insert into s values ('200015', 'T.','Oates', '1 Snow St.', 'Hobart South', 'S376');
insert into s values ('200016', 'W.','Pinkerton', '20 Agency St.', 'New York', 'S367');
insert into s values ('200017', 'E.','Queen', 'Buck House', 'London', 'S375');
insert into u values ('SCC103', 'Intro. to I.T.', 'L5', 'SB420');
insert into u values ('SCC104', 'Intro. to Software Dev.', 'L12', 'SB420');
insert into u values ('SCC105', 'Basic Programming Concepts', 'L12', 'SB420');
insert into u values ('SCC107', 'Concepts and Practice for S.E.', 'L11', 'SB420');
insert into u values ('SCC108', 'Database and Info. Retrieval', 'L10', 'SB420');
\! echo "Entering SCC103 results"insert into e values('SCC103', '200001', 55, 'P');
insert into e values('SCC103', '200002', 74, 'D');
insert into e values('SCC103', '200003', 82, 'HD');
insert into e values('SCC103', '200004', 56, 'P');
insert into e values('SCC103', '200005', 63, 'C');
insert into e values('SCC103', '200006', 34, 'N');
insert into e values('SCC103', '200007', null, null);
insert into e values('SCC103', '200008', 52, 'P');
insert into e values('SCC103', '200009', 69, 'C');
insert into e values('SCC103', '200010', 70, 'D');
insert into e values('SCC103', '200011', 51, 'P');
insert into e values('SCC103', '200012', 56, 'P');
insert into e values('SCC103', '200013', 53, 'P');
insert into e values('SCC103', '200014', 66, 'C');
insert into e values('SCC103', '200015', 61, 'C');
insert into e values('SCC103', '200016', 56, 'P');
1
-- List lecturer name, lecturer title and unit code for all
-- lecturers who teach a class with at least three B.I.T.
-- students in it.
2
-- For each room, list all students who have had at least
-- 4 classes in that room"
结构就是这样的。
第一个问题我用
select distinct l.name, l.title from l,u where unit_code in (select distinct unit_code from e where stu_no in (select stu_no from s where degree ='M402') ) AND stu_no in (select unit_code from e group by unit_code having count(*)>=3));不行,只能得到教M402学生的课程老师,不能得到教3个以上M402学生的课程的老师,数量的条件不知道怎么写进去,
,用WINRAR压缩
select room_no from u group by room_no having count(*)>=4
只招出了上4门课以上的房间,而我要找出学生(有4门课在一个房间上的)不知道怎么关联。
传了,谢谢!
我想解决7后,8页差不多了。
你觉得7应该怎么写呢?