问题描述
亲爱的所有人，
i拥有5张桌子的数据库;
1.Campus table
CAMP ID校园名称
A Boise
B Twin Falls
2.faculty table
FAC ID FAC NAME DEPT RANK CAMP ID
F101亚当斯艺术教授A
F105 Thanaka CSC教练A
F110 Byrne数学助理A
F202史密斯历史助理A
3.注册表
CLASSID STUID GRADE
ART103A S1001 A
ART103A S1002 D
ART103A S1010
CSC201A S1002 F
CSC201A S1020 B
HST205A S1001 C
MTH101B S1020 A
MTH103C S1002 B
MTH103C S1010
4.student表
STUID STUNAME主要奖励
S1001史密斯，汤姆历史15
S1002 Chin，Ann Math 15
S1010比利，桑顿艺术15
S1013麦卡锡，欧文数学15
S1015琼斯，玛丽数学15
S1020里维拉，简CSC 15
5.class table
CLASSID FACID SCHED ROOM
ART103A F101 MWF9 H221
CSC201A F105 TUTHF10 M110
CSC203A F105 MTHF12 M110
HST205A F202 MWF11 H221
MTH101B F110 MTUTH9 H225
MTH103C F110 MWF11 H225
这两个quarrys遇到麻烦。
你可以帮我吗
1.列出所有参加ART 103A且已注册MTH 103C的学生。
2.列出所有参加ART 103A且未参加MTH 103C的学生。
Dear All,
i have Database with 5 tables;
1.Campus table
CAMP ID CAMPUS NAME
A Boise
B Twin Falls
2.faculty table
FAC ID FAC NAME DEPT RANK CAMP ID
F101 Adams Art Professor A
F105 Thanaka CSC Instructor A
F110 Byrne Math Assistant A
F202 Smith History Associate A
3.enroll table
CLASSIDSTUIDGRADE
ART103AS1001A
ART103AS1002D
ART103AS1010
CSC201AS1002F
CSC201AS1020B
HST205AS1001C
MTH101BS1020A
MTH103CS1002B
MTH103CS1010
4.student table
STUIDSTUNAME MAJORCREDITS
S1001Smith,TomHistory15
S1002Chin,AnnMath15
S1010Billy,ThorntonArt15
S1013McCarthy, OwenMath15
S1015Jones, MaryMath15
S1020Rivera, JaneCSC15
5.class table
CLASSIDFACIDSCHEDROOM
ART103AF101MWF9H221
CSC201AF105TUTHF10M110
CSC203AF105MTHF12M110
HST205AF202MWF11H221
MTH101BF110MTUTH9H225
MTH103CF110MWF11H225
iam in a trouble with these 2 quarrys.
can you please help me
1. List all stuids which are enrolled in ART 103A and which are enrolled in MTH 103C.
2. List all stuids which are enrolled in ART 103A and which are not enrolled in MTH 103C.
推荐答案
SELECT DISTINCT s.*
FROM student AS s INNER JOIN enroll AS e ON s.stuid = e.stuid
WHERE e.classid = 'ART103A' AND e.stuid IN
(SELECT stuid from enroll
WHERE enroll.classid='MTH103C')
然后，你应该没有问题找到第二个问题的答案。提示是包含一个NOT。
Then, you should have no problem finding your answer to your second question. The hint is to include a NOT.
Select C.STUID
FROM CLASS C
INNER JOIN ENROLL E ON C.CLASSID = E.CLASSID
WHERE C.CLASSID in ('ART103A', 'MTH103C')
2.列出所有在ART 103A中注册但未在MTH 103C注册的特技
2. List all stuids which are enrolled in ART 103A and which are not enrolled in MTH 103C
SELECT TMP.*
FROM
(
Select C.STUID
FROM CLASS C
INNER JOIN ENROLL E ON C.CLASSID = E.CLASSID
WHERE C.CLASSID = 'ART103A')
) TMP LEFT OUTER JOIN ENROLL EN ON TMP.STUID = EN.STUID AND EN.CLASSID = 'MTH103C'
WHERE EN.CLASSID IS NULL
这篇关于数据回归问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！