Home » SQL & PL/SQL » SQL & PL/SQL » One URGENT problem with SQL
One URGENT problem with SQL [message #409] Mon, 11 February 2002 06:14 Go to next message
Chen Wen
Messages: 26
Registered: February 2002
Junior Member
There are three tables: course, sect and enrolled.
How to list all departments, numbers, minimum enrollment, and how far they're under that will be cancelled due to under-enrollment.
course:
DEPT NUM NAME CREDITS
-------------------------------
CSCI 541 Database 3
CPT 541 Access 4
CSCI 702 PhD Thesis 3

sect:
DEPT NUM SECT MIN_NUM MAX_NUM
----------------------------------------
CSCI 541 B332 20 30
CPT 541 S873 30 150
CSCI 701 B411 1 1
CSCI 702 B445 1 3
CSCI 701 B412 1 2
CSCI 701 B4131 1 1

enrolled:
SIDN SECT CREDITS
---------- ----- -------
111111111 B411 2
222222222 B332 3
111111111 B412 3
222222222 B412 4
333333333 B412 3
Re: One URGENT problem with SQL [message #413 is a reply to message #409] Mon, 11 February 2002 07:02 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
hi,

here is the basic query and add the restriction acoordingly.otherwise explain the meaning of each field.

select c.dept,
c.num,
s.min_num
from course c,
sect s,
enrolled e
where c.dept=s.dept
and c.num=s.num
and s.sect=e.sect

cheers
pratap
Re: One URGENT problem with SQL [message #415 is a reply to message #409] Mon, 11 February 2002 07:11 Go to previous messageGo to next message
Chen Wen
Messages: 26
Registered: February 2002
Junior Member
Hi,
Thanks.
But how to list the number to indicate how far they're under that will be cancelled due to under-enrollment? That is the student number enrolled in each course substract min_num.
Re: One URGENT problem with SQL [message #417 is a reply to message #409] Mon, 11 February 2002 07:36 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
select c.dept,
c.num,
s.min_num,
s.min_num-c.creadit,
from course c,
sect s,
enrolled e
where c.dept=s.dept
and c.num=s.num
and s.sect=e.sect
Re: One URGENT problem with SQL [message #439 is a reply to message #409] Tue, 12 February 2002 09:02 Go to previous message
Chen Wen
Messages: 26
Registered: February 2002
Junior Member
Thanks a lot.
Previous Topic: Inserting data using a view.
Next Topic: create a csv ?
Goto Forum:
  


Current Time: Sat Sep 18 04:52:52 CDT 2021