Data Retreive Problem

Question

0.00/5 (No votes)

See more:

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.

Posted 24-Jun-14 17:54pm

nuke_infer

Add a Solution

3 solutions

Solution 1

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")))

Posted 24-Jun-14 18:07pm

nuke_infer

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2014-06-24T19:27:00

Try to interpret the following solution to your first question:

SQL

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')

Then, you should have no problem finding your answer to your second question. The hint is to include a NOT.

_Asif_ · Accepted Answer · 2014-06-24T19:39:00

1. List all stuids which are enrolled in ART 103A and which are enrolled in MTH 103C

SQL

Select C.STUID
FROM   CLASS C
INNER JOIN ENROLL E ON C.CLASSID = E.CLASSID
WHERE C.CLASSID in ('ART103A', 'MTH103C')

2. List all stuids which are enrolled in ART 103A and which are not enrolled in MTH 103C

SQL

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

Data Retreive Problem

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0