数据回归问题 [英] Data Retreive Problem
问题描述
亲爱的所有人，
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
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 table
STUID STUNAME MAJOR CREDITS
S1001 Smith,Tom History 15
S1002 Chin,Ann Math 15
S1010 Billy,Thornton Art 15
S1013 McCarthy, Owen Math 15
S1015 Jones, Mary Math 15
S1020 Rivera, Jane 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
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.
推荐答案
尝试将以下解决方案解释为您的第一个问题：
Try to interpret the following solution to your first question:
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.
我试过这个来自访问螺母的错误，请帮助解决这两个问题
1.select * from student s ，注册e，c级
其中s.STUDID = e.STUDID AND（（（c.CLASSID =ART 103A和e.CLASSID =ART 103A）AND（c.CLASSID =
MTH 103C和e.CLASSID =MTH 103C）））
i tried this from access nut its wrong,please help to solve these 2 problems
1.select * from student s,enrol e, class c
where s.STUDID = e.STUDID AND (((c.CLASSID ="ART 103A" AND e.CLASSID ="ART 103A")AND (c.CLASSID=
"MTH 103C" AND e.CLASSID ="MTH 103C")))
1。列出在ART 103A中注册并在MTH 103C注册的所有stuids
1. List all stuids which are enrolled in ART 103A and which are enrolled in MTH 103C
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
这篇关于数据回归问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
