1.
用什么语句可以把下面的多行数据显示为一行?好像用group by不行阿
STUDENTID FIRSTNAME LASTNAME
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso 2.
列出刚刚选择了专业(degreecourse)但没有选择课程(module)的学生的名字(first and last name)。
注意已经入学的学生会有一个入学时间(dateenrolled),而且不会有辍学时间(datewithdrawn)和毕业时间(dategraduated因为还没毕业)
数据:
-------------
degreecourse |
-------------|
courseID(PK) |
courseTitle |
--------------------------
student |
-------------|
studentID{PK)|
Firstname |
Lastname |
courseID(FK) |
dateEnrolled |
dateWithdrawn|
dateGraduated|
-----------------------------
registration |
----------------|
studentID(PK,FK)|
moduleID(PK,FK) |
year(PK) |
semester(PK) |
-----------------------------
module |
-------------|
moduleID(PK) |
moduleTitle |
-------------
====================================================================================
下面是一个错误的代码,仅供参考,不知道怎么错了,结果是未选定行
select firstname,lastname
from student s, degreecourse d, registration r,module m
where s.courseid=d.courseid
and r.studentid=s.studentid
and r.moduleid=m.moduleid
and datewithdrawn is null
and dategraduated is null
and m.moduleid not in
(select moduleid
from registration);
用什么语句可以把下面的多行数据显示为一行?好像用group by不行阿
STUDENTID FIRSTNAME LASTNAME
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso
241234 Pablo Picasso 2.
列出刚刚选择了专业(degreecourse)但没有选择课程(module)的学生的名字(first and last name)。
注意已经入学的学生会有一个入学时间(dateenrolled),而且不会有辍学时间(datewithdrawn)和毕业时间(dategraduated因为还没毕业)
数据:
-------------
degreecourse |
-------------|
courseID(PK) |
courseTitle |
--------------------------
student |
-------------|
studentID{PK)|
Firstname |
Lastname |
courseID(FK) |
dateEnrolled |
dateWithdrawn|
dateGraduated|
-----------------------------
registration |
----------------|
studentID(PK,FK)|
moduleID(PK,FK) |
year(PK) |
semester(PK) |
-----------------------------
module |
-------------|
moduleID(PK) |
moduleTitle |
-------------
====================================================================================
下面是一个错误的代码,仅供参考,不知道怎么错了,结果是未选定行
select firstname,lastname
from student s, degreecourse d, registration r,module m
where s.courseid=d.courseid
and r.studentid=s.studentid
and r.moduleid=m.moduleid
and datewithdrawn is null
and dategraduated is null
and m.moduleid not in
(select moduleid
from registration);
SELECT DISTINCT STUDENTID,FIRSTNAME, LASTNAME FROM TABLE
2、第二个问题
SELECT * FROM STUDENT
WHERE
COURESID IS NOT NULL
AND STUDENTID NOT IN(SELECT * FROM registration)
265478 Alvar Aalto
254988 Henry Moore
242346 Claude Monet
256677 Barbara Epstein
245743 Rachel Whiteread
265676 Ieoh Ming Pei
265479 Antonio Gaudi
267489 Le Corbusier
268548 Aldo Rossi
241234 Pablo Picasso
263734 Beryl Cook
258234 Edvard Munch 我的原代码如下,不知道是不是错了
select s2.studentid,s2.firstname,s2.lastname
from student s1,student s2,registration r1,registration r2
where r1.studentid=s1.studentid
and r2.studentid=r1.studentid
and s2.studentid=r2.studentid
and s2.firstname<>'Salvador'
and s2.lastname<>'Dali'
select firstname,lastname
from student s, degreecourse d, registration r,module m
where s.courseid=d.courseid
and r.studentid=s.studentid
and r.moduleid=m.moduleid
and s.courseid is not null
AND s.STUDENTID NOT IN
(SELECT STUDENTID
FROM registration)
第二个问题select firstname, lastname
from student s, registration r
where s.studentid = r.studentid(+)
and r.studentid is null
and s.datewithdrawn is null
and s.dategraduated is null
修改后这样就行了:
select firstname, lastname
from student s, registration r,degreecourse d
where s.studentid = r.studentid(+)
and s.courseid = d.courseid(+)
and s.courseid is not null
and r.studentid is null
and s.datewithdrawn is null
and s.dategraduated is null
题目是这样的:
用自插方法(self join)列出与Salvador Dali注册在同样课程的学生的学生号码(studentid)和名字(firstname,lastname)-------------
student |
-------------|
studentID{PK)|
Firstname |
Lastname |
courseID(FK) |
dateEnrolled |
dateWithdrawn|
dateGraduated|
-----------------------------
registration |
----------------|
studentID(PK,FK)|
moduleID(PK,FK) |
year(PK) |
semester(PK) |
cw |
exam |
----------------
===============================================================
--load Student with data
insert into Student values
('241234','Pablo','Picasso', 'FA', '01-sep-2004','','');insert into Student values
('242346','Claude', 'Monet', 'FA', '01-sep-2004','','01-jun-2006');insert into Student values
('245743','Rachel', 'Whiteread', 'SCU', '01-sep-2004','','');insert into Student values
('254988','Henry', 'Moore', 'SCU', '01-sep-2005','','01-jun-2006');insert into Student values
('256677','Barbara','Epstein', 'SCU', '01-sep-2005','','');insert into Student values
('258234','Edvard', 'Munch', 'FA', '01-sep-2005','','');insert into Student values
('259234','Salvador', 'Dali', 'FA', '01-sep-2005','','');insert into Student values
('263734','Beryl', 'Cook', 'FA', '01-sep-2006','','');insert into Student values
('265478','Alvar', 'Aalto', 'ARCH', '01-sep-2006','','');insert into Student values
('265676','Ieoh Ming', 'Pei', 'ARCH', '01-sep-2006','','');insert into Student values
('265479','Antonio', 'Gaudi', 'ARCH', '01-sep-2006','','');insert into Student values
('267489','Le', 'Corbusier', 'DCS', '01-sep-2006','','');insert into Student values
('268548','Aldo', 'Rossi', 'IA', '01-sep-2006','01-oct-2006','');insert into Student values
('269111','Damien', 'Hirst', '', '','','');insert into Student values
('269123','Andy', 'Warhol', 'DCS', '01-sep-2006','','');
--load Registration with data
insert into Registration values
('241234','FA101','04','1',90,0); insert into Registration values
('241234','FA111','04','1',45,35); insert into Registration values
('241234','FA201','04','2',78,0); insert into Registration values
('242346','FA101','04','1',76,0); insert into Registration values
('242346','FA111','04','1',65,44); insert into Registration values
('242346','AR131','04','2',72,51); insert into Registration values
('242346','AR231','04','2',56,61); insert into Registration values
('245743','FA101','04','1',52,0);insert into Registration values
('245743','SC101','04','1',72,0);insert into Registration values
('254988','FA101','05','1',59,0);insert into Registration values
('254988','SC101','05','1',88,0);insert into Registration values
('254988','FA201','05','2',67,0);insert into Registration values
('256677','FA101','05','1',34,0);insert into Registration values
('256677','SC101','05','1',98,0);
insert into Registration values
('256677','AR231','05','2',61,51);insert into Registration values
('256677','FA201','05','2',45,0); insert into Registration values
('256677','FA222','05','2',69,0);insert into Registration values
('258234','FA101','05','1',49,0);insert into Registration values
('258234','FA201','05','2',53,0);insert into Registration values
('259234','FA101','05','1',32,18);insert into Registration values
('259234','FA111','05','1',22,12);
insert into Registration values
('263734','FA101','06','1',31,0);insert into Registration values
('263734','SC101','06','1',34,0);insert into Registration values
('263734','FA111','06','1',53,45);insert into Registration values
('263734','FA211','06','2',41,33);insert into Registration values
('263734','AR201','06','2',58,54);insert into Registration values
('265478','AR101','06','1',88,91); insert into Registration values
('265478','AR122','06','1',85,76); insert into Registration values
('265478','AR123','06','2',71,0); insert into Registration values
('265478','AR201','06','2',56,41); insert into Registration values
('265478','FA222','06','2',44,0); insert into Registration values
('265676','AR101','06','1',99,84); insert into Registration values
('265479','AR101','06','1',64,64); insert into Registration values
('265479','AR122','06','1',88,45);insert into Registration values
('265479','FA222','06','2',17,0); insert into Registration values
('265479','AR201','06','2',78,79); insert into Registration values
('265479','AR122','06','2',97,0); insert into Registration values
('267489','AR131','06','1',83,56); insert into Registration values
('267489','AR231','06','1',66,56);insert into Registration values
('267489','AR101','06','1',64,43);insert into Registration values
('268548','AR101','06','1',50,0);拜托大侠了
====================================================
下面是我做的答案,不知道有没有错,select s2.studentid,s2.firstname,s2.lastname
from student s1,student s2,registration r1,registration r2
where r1.studentid=s1.studentid
and r2.studentid=r1.studentid
and s2.studentid=r2.studentid
and s2.firstname<>'Salvador'
and s2.lastname<>'Dali'
insert into Student values
('241234','Pablo','Picasso', 'FA', '01-9月-2004','','');
from student s1,student s2,registration r1,registration r2
where r1.studentid=s1.studentid
and r2.moduleID=r1.moduleID
and s2.studentid=r2.studentid
and s2.studentid<>s1.studentid
and s1.firstname ='Salvador'
and s1.lastname ='Dali' 没测试不知道对不对,我没有搞明白哪个是选课,还有,题目要求是所有的课程都和S.D选的一样 还是只要有一样的就可以