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?
3 1602
That seems to be a home work / assignment.
Kindly post the code that you have tried / working on.
Try this
First let's create some sample data - declare @student table(sid int, sname varchar(50))
-
declare @course table(cid int, cname varchar(50))
-
declare @student_course table(sid int, cid int)
-
-
insert into @student
-
select 1,'student1' union all select 2,'student2' union all
-
select 3,'student3' union all select 4,'student4' union all
-
select 5,'student5' union all select 6,'student6'
-
insert into @course
-
select 101,'course1' union all select 202, 'course2' union all
-
select 303,'course3' union all select 404, 'course4' union all
-
select 505,'course5' union all select 606, 'course6'
-
insert into @student_course
-
select 1,101 union all select 1,202 union all select 1,303 union all
-
select 1,404 union all select 1,505 union all select 1,606 union all
-
select 2,101 union all select 2,505 union all select 2,606 union all
-
select 3,303 union all select 3,202 union all select 4,303 union all
-
select 4,404 union all select 4,202 union all select 5,101 union all
-
select 6,101 union all select 6,202
Query 1: - select s.sid,s.sname from @student s
-
inner join
-
(
-
select sid,COUNT(cid) Courses from @student_course
-
group by sid
-
having (COUNT(cid)>=3))X
-
on X.sid = s.sid
Query 2: - select sid,sname from @student where sid in(
-
select sid from @student_course
-
group by sid
-
having (COUNT(cid)>=3))
The output(in both the cases) - sid sname
-
1 student1
-
2 student2
-
4 student4
Hope this helps
Thank you very much sir.....
Sign in to post your reply or Sign up for a free account.
Similar topics
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
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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....
|
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
|
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...
|
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...
| |