April 9, 2012 at 4:52 pm
i want to find out Students who enrolled in more than 5 courses Spring semester 2005**
select fname,coursename from tstudent a
join tStudentCourseMap b
on a.studentID=b.StudentID
join tcourses c
on c.courseid=b.CourseID
join tCourseDepartmentMap d
on d.CourseID=c.courseid
group by fname,coursename
can someone point out what i need to add, please?
April 9, 2012 at 4:54 pm
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Is this homework?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2012 at 4:55 pm
Please read and follow the instructions in this article:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 9, 2012 at 5:40 pm
My tables are:
1. tstudent
Create table tstudent
(
studentID int identity (1,1),
Fname varchar(50),
Lname varchar(50),
Sex char(1),
JoinDate date,
Address varchar(100),
[Email-ID] varchar(50),
Contact varchar(25)
)
2.tcourses
create table tcourses
(
courseid int primary key,
CourseName varchar(50),
Credits int,
Fees money,
Semester varchar(50)
)
3.tstudentcoursemap
(
StudentID int, -- foreign key to studentid (tsudent)
CourseID int, --foreign key to courseid (tcourses)
EnrollmentDate date
)
4.tcoursedepartmentmap
create table tCourseDepartmentMap
(
CourseID int, --foreign key to courseid (tcourses)
Deptid int) --foreign key to departmentid (tdepartment)
5.tdepartment
create table tDepartment
(
DeptID int primary key identity(1,1),
DepartName varchar(50),
)
April 9, 2012 at 5:41 pm
This is one of the practise question i didn't get the output for ..we will discuss in next class. ty
April 9, 2012 at 5:45 pm
Not doing your homework for you...
What have you got so far and what precisely are you struggling with?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2012 at 5:50 pm
Thank You...Again this is not homework..If you want i can't send you all the 15 questions but we are not suppose to give as homework..we are suppose to practise it and then discuss it in next class how we did it...i did all of them and was having problem with questions that said "more than".. i used "group by" but i could not get the right output for this one..the query i send you was done my me and i couldn't figure out hoe to get the number of course more than 5 ? Appreciate your help
April 10, 2012 at 2:00 am
You say it's not homework, then you say it's practice for the next class. How is that not homework?
First thing, how do you get the list of students with the number of courses they each have taken? Figure that out, post the answer and I'll help you with the next part.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2012 at 9:27 am
Ty again. i don't want the whole query myself ..i just want to know my mistakes and improve my concepts.
list of students with the number of courses they each have taken:
select fname from tstudents a
inner join tstudentcoursemap b
on a.studentid=b.studentid
inner join tcourses c
on c.courseid=b.courseid
April 10, 2012 at 9:35 am
You sure that query returns the list of students with the number of courses they took? Looks to me like it'll just return a list of students.
What you need is something like this:
Student CountOfCourses
Michelle 2
Michael 5
Bob 1
Jenny 10
See if you can figure out the query to get that. Hint: COUNT and GROUP BY.
Edit: Never mind....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2012 at 9:39 am
--edit--
Removed my post as it's less helpful to the OP than Gail's. My post was more of a spoon-feeding exercise.
April 10, 2012 at 12:25 pm
--8**)Students who enrolled in more than 5 courses Spring semester 2005**
select fname,CourseName,count(d.courseid),b.enrollmentdate,semester from tstudent a
join tStudentCourseMap b
on a.studentID=b.StudentID
join tcourses c
on c.courseid=b.CourseID
join tCourseDepartmentMap d
on d.CourseID=c.courseid
group by fname,coursename,d.courseid,semester,b.EnrollmentDate
having Semester= 'spring' and b.EnrollmentDate='2005'
The thing i am confused is what am i counting here...the coursename or courseid and how do i give a condition for more than 5 courses....
April 10, 2012 at 12:28 pm
Do some reading on HAVING in Books Online.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply