473,327 Members | 1,919 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

What is the Query?

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
3 1602
debasisdas
8,127 Expert 4TB
That seems to be a home work / assignment.

Kindly post the code that you have tried / working on.
Dec 5 '09 #2
nbiswas
149 100+
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
mzahid
3
Thank you very much sir.....
Dec 7 '09 #4

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

Similar topics

4
by: J Sharman | last post by:
What is the easiest way to import an XML Datafeed from a URL into a MYSQL Database? Possibly using PHP Regards Joe PS Please answer to group and joe@joesharman.co.uk
4
by: lawrence | last post by:
I'm very unhappy with the error message that I'm giving in this method: /** * 11-23-03 - getter * * We want to run the query against a MySql database and get back a
5
by: lkrubner | last post by:
I have a webserver through Rackspace. I create a domain. I create an FTP user. I upload some files. I create a database called testOfSetupScript and then I create a database user named setup. I...
0
by: John Phelan | last post by:
I had a very large number of query statements that I had converted to SQL a long time ago aticipating that I would some day upsize my application. Every now and then when I do an import to a new...
3
by: White Bilky | last post by:
A97. Simplified description: I have a query which returns one field containing numbers. I have a table where one field contains the same numbers as the query (amongst others). The query and...
59
by: Chris Dunaway | last post by:
The C# 3.0 spec (http://msdn.microsoft.com/vcsharp/future/) contains a feature called "Implicitly typed local variables". The type of the variable is determined at compile time based on the...
1
by: Dot Net Daddy | last post by:
Hi, I am new to MS-SQL and I have a problem. Actually I have made similar queries on Oracle, but now I failed on MS Sql. First of all I am using SQL Server 2005, but I think it is not that much...
2
by: Aaron Reimann | last post by:
I have a lot of check boxes. This is an update of the check boxes, I want something was checked, then to do an insert (which is currently working), if something is no longer checked...delete the...
5
by: Bob Bridges | last post by:
Start with two tables, parent records in one and child records in the other, a one-to-many relationship. Create a select statement joining the two. Display the query in datasheet mode. When I...
4
bilibytes
by: bilibytes | last post by:
Hello i'm intrigued by what happens behind the scenes when I do a DB query to a database from php. I would like to know what is the exact life cycle of a mysql_query() I ask this question...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.