By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

What is the Query?

P: 3
I have 3 tables student(s_id,s_name),course(c_id,c_name) and student_course(s_id(FK),c_id(FK)).
I would Like to select those students Name who have enrolled more then and equal to 3 courses.
What is the Query?
Dec 5 '09 #1
Share this Question
Share on Google+
3 Replies


debasisdas
Expert 5K+
P: 8,127
That seems to be a home work / assignment.

Kindly post the code that you have tried / working on.
Dec 5 '09 #2

nbiswas
100+
P: 149
Try this

First let's create some sample data

Expand|Select|Wrap|Line Numbers
  1. declare @student table(sid int, sname varchar(50))
  2. declare @course table(cid int, cname varchar(50))
  3. declare @student_course table(sid int, cid int)
  4.  
  5. insert into @student 
  6.     select 1,'student1' union all select 2,'student2' union all
  7.     select 3,'student3' union all select 4,'student4' union all
  8.     select 5,'student5' union all select 6,'student6'
  9. insert into @course 
  10.     select 101,'course1' union all select 202, 'course2' union all
  11.     select 303,'course3' union all select 404, 'course4' union all
  12.     select 505,'course5' union all select 606, 'course6' 
  13. insert into @student_course
  14.     select 1,101 union all select 1,202 union all select 1,303 union all
  15.     select 1,404 union all select 1,505 union all select 1,606 union all
  16.     select 2,101 union all select 2,505 union all select 2,606 union all
  17.     select 3,303 union all select 3,202 union all select 4,303 union all
  18.     select 4,404 union all select 4,202 union all select 5,101 union all 
  19.     select 6,101 union all select 6,202
Query 1:

Expand|Select|Wrap|Line Numbers
  1. select s.sid,s.sname from @student s
  2. inner join
  3. (    
  4. select sid,COUNT(cid) Courses from @student_course 
  5. group by sid
  6. having (COUNT(cid)>=3))X
  7. on X.sid = s.sid 
Query 2:
Expand|Select|Wrap|Line Numbers
  1. select sid,sname from @student where sid in(
  2. select sid from @student_course 
  3. group by sid
  4. having (COUNT(cid)>=3))
The output(in both the cases)

Expand|Select|Wrap|Line Numbers
  1. sid    sname
  2. 1    student1
  3. 2    student2
  4. 4    student4
Hope this helps
Dec 7 '09 #3

P: 3
Thank you very much sir.....
Dec 7 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.