[HELP ME]SQL server query assignment

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

[HELP ME]SQL server query assignment

Author

Topic

vziera
Starting Member

4 Posts

Posted - 2011-08-12 : 08:11:33

First off please download the questions from here:

http://www.mediafire.com/file/7lcit11b16jbbe3/M056421673.doc

And then help me out with the answer.

please please please, tomorrow is my judgement day

thank you so much in advance!

super melancholy person

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-12 : 08:21:06

sorry, very sorry, very very sorry, we don't do homework here.

You may ask any specific question that you are in doubt and we will be happy to answer it

KH
[spoiler]Time is always against us[/spoiler]

vziera
Starting Member

4 Posts

Posted - 2011-08-12 : 08:23:23

please just this time I promise won't do this again
please...

super melancholy person

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-12 : 08:27:19

you should at least give it a try . . .

KH
[spoiler]Time is always against us[/spoiler]

vziera
Starting Member

4 Posts

Posted - 2011-08-12 : 08:44:54

I've tried it, just don't get it :'(

1. Explain the function of each of the clauses in the SELECT statement. What restrictions are imposed on these clauses?

==============
All I can answer:
SELECT * FROM Student, Course, Section, Grade Report
:(

super melancholy person

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-12 : 10:33:53

Have you asked your teacher/professor/instructor? If not, start there.

What select statement is the question referring to? It's not asking you to write a select, it's asking you to explain a select.

I'm willing to offer advice, but I am not doing your homework for you. You learn nothing from that and I don't need the practice.

--
Gail Shaw
SQL Server MVP

vziera
Starting Member

4 Posts

Posted - 2011-08-13 : 10:56:48

quote:
Originally posted by GilaMonster

Have you asked your teacher/professor/instructor? If not, start there.

What select statement is the question referring to? It's not asking you to write a select, it's asking you to explain a select.

I'm willing to offer advice, but I am not doing your homework for you. You learn nothing from that and I don't need the practice.

--
Gail Shaw
SQL Server MVP

rofl my bad, nevermind, I've finally done the essay myself.
now, can anyone help me with the rest:

2. Retrieve the names of all courses taught by “King” in 1998 and 1999
3. For each section taught by “King”, retrieve the course number, semester, year and number of students who took the section
4. Retrieve the names and transcript of each senior student (Class = 5) Majoring in CS. A transcript includes course name, course Id, Credit Hours, Semester, Year and Grade for each course completed by the student.
5. Retrieve the names and major departments of all straight A students (students who have a grade of A in all their courses).
6. Retrieve the names and major departments of all students who do not have a grade of A in any of their courses.

LOL

super melancholy person

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-13 : 13:31:34

Show us what you have done so far for each of those questions, explain where you are stuck.

As I said before, I'm willing to offer advice, but I am not doing your homework for you. You learn nothing from that and I don't need the practice.

--
Gail Shaw
SQL Server MVP

sravz
Starting Member

42 Posts

Posted - 2011-08-15 : 13:04:12

@GilaMonster,

I am trying to get good exp with queries in SQL SERVER . I wrote the answers for the questions posted here.I want to check whether the solutions I used are correct or not.Can I post them here.Please let me know

Thanks,
Sravz

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-15 : 15:35:21

Sure. Whether anyone will comment on them, depends on how busy people are

Have you tested the queries? Do they do what they are supposed to?

--
Gail Shaw
SQL Server MVP

sravz
Starting Member

42 Posts

Posted - 2011-08-15 : 16:33:06

Here are the answers I wrote.I tested all of them.The 5th Question Solution is not working.please suggest me another way to write it.
1. Retrieve the names of all senior students majoring in ‘CS’

SELECT Name
FROM Student
WHERE Major='CS'
AND Class IN(SELECT MAX(Class) FROM Student)

2. Retrieve the names of all courses taught by “King” in 1998 and 1999

Select CourseName
From Course
INNER JOIN Section ON Section.Course_Id=Course.Course_Id
WHERE Instructor='King'
AND ([Year]='98' OR [Year]='99')

3. For each section taught by “King”, retrieve the course number, semester, year and number of students who took the section

Select C.Course_Id,COUNT(G.Student_Id) StudentCount,S.Semester,S.[Year]
From Course C
INNER JOIN Section S ON S.Course_Id=C.Course_Id
INNER JOIN Grade_Report G ON S.SectionIdentifier =G.SectionIdentifier
WHERE S.SectionIdentifier=(SELECT SectionIdentifier FROM Section WHERE Instructor='King')
GROUP BY C.Course_Id,S.Semester,S.[Year]

4. Retrieve the names and transcript of each senior student (Class = 5) Majoring in CS. A transcript includes course name, course Id, Credit Hours, Semester, Year and Grade for each course completed by the student.

SELECT St.Name,C.Course_Id,C.CreditHours,Semester,[Year],Grade
FROM Course C
INNER JOIN Student St ON St.Major=C.Department
INNER JOIN Section S ON C.Course_Id= S.Course_Id
INNER JOIN Grade_Report G ON (S.SectionIdentifier =G.SectionIdentifier AND G.Student_Id=St.Student_Id)
WHERE St.Class=5
AND St.Major='CS'

5. Retrieve the names and major departments of all straight A students (students who have a grade of A in all their courses).

NOT WORKING this way

SELECT St.Name,St.Major
FROM Student St
WHERE Student_Id IN(SELECT Student_Id FROM Grade_Report WHERE Grade='A'
AND Grade <>'B'
AND Grade <> 'C'
Group by Student_Id)

Thanks,
Sravz

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-15 : 16:54:51

Let me give you a hint for the one not working :)

Join the "student" table with "grade report" and impose only grade check in there where clause.

If you don't know how to join tables then go through http://msdn.microsoft.com/en-us/library/ms191472.aspx
and the links for different types of joins listed on this page.

Cheers
MIK

sravz
Starting Member

42 Posts

Posted - 2011-08-15 : 17:24:58

Hey Mike,

I Tried that way too its not working.It gives the same result as before:

SELECT St.Name,St.Major
FROM Student St
INNER JOIN Grade_Report G ON G.Student_Id=St.Student_Id
WHERE (Grade='A'
AND Grade <>'B'
AND Grade <> 'C')
GROUP BY St.Name,St.Major

Name Major
Brown CS

The result should be blank because there is no student with all courses A grade.

But I get the this result:

I Think it should be a cursor,because you need to check through each course of a particular Student_Id Whether the Grade is A or Not

Thanks,
Sravz

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-15 : 17:45:54

You don't need a cursor for any of these...

What your current query does is look to see if there is ANY row where Grade = 'A' and <> 'B' and <> 'C' (which is unnecessary, if the grade = 'A' it can't be anything else, the column can't have two different values for the same row). You need to find cases where ALL rows for that student Grade = A

One option is subqueries - one to see how many courses the student took, one so see how many they got an A for, and then compare the two.

Give it a go, see if you can figure it out.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-15 : 17:49:45

quote:
Select C.Course_Id,COUNT(G.Student_Id) StudentCount,S.Semester,S.[Year]
From Course C
INNER JOIN Section S ON S.Course_Id=C.Course_Id
INNER JOIN Grade_Report G ON S.SectionIdentifier =G.SectionIdentifier
WHERE S.SectionIdentifier=(SELECT SectionIdentifier FROM Section WHERE Instructor='King')
GROUP BY C.Course_Id,S.Semester,S.[Year]

This will throw an error if King taught more than one Section. Rather than = for the comparison with the subquery, try IN.

--
Gail Shaw
SQL Server MVP

sravz
Starting Member

42 Posts

Posted - 2011-08-15 : 20:37:45

quote:
Originally posted by GilaMonster

quote:
Select C.Course_Id,COUNT(G.Student_Id) StudentCount,S.Semester,S.[Year]
From Course C
INNER JOIN Section S ON S.Course_Id=C.Course_Id
INNER JOIN Grade_Report G ON S.SectionIdentifier =G.SectionIdentifier
WHERE S.SectionIdentifier=(SELECT SectionIdentifier FROM Section WHERE Instructor='King')
GROUP BY C.Course_Id,S.Semester,S.[Year]

This will throw an error if King taught more than one Section. Rather than = for the comparison with the subquery, try IN.

--
Gail Shaw
SQL Server MVP

Actually I tried with adding more records and got an error.But I forgot to change in the post.

Thanks,
Sravz

sravz
Starting Member

42 Posts

Posted - 2011-08-15 : 21:33:51

quote:
Originally posted by GilaMonster

One option is subqueries - one to see how many courses the student took, one so see how many they got an A for, and then compare the two.

--
Gail Shaw
SQL Server MVP

Hey Gail ,

I wrote the two queries to count how many corses a student took

SELECT St.Name,St.Major,COUNT(S.Course_Id) CourseCount
FROM Student St

LEFT OUTER JOIN Grade_Report G ON St.Student_Id=G.Student_Id

LEFT OUTER JOIN Section S ON S.SectionIdentifier=G.SectionIdentifier

GROUP BY St.Name,St.Major

This gives me this result
Name Major CourseCount
Brown CS 4
John CS 0
Mark CS 0
smith CS 2
Mack IS 0

And the second Query Count how many of them got a

SELECT COUNT(S.Course_Id) CourseCount
FROM Student St

LEFT OUTER JOIN Grade_Report G ON St.Student_Id=G.Student_Id

LEFT OUTER JOIN Section S ON S.SectionIdentifier=G.SectionIdentifier

WHERE Grade='A'

This gave me this result

GradeCount
3

But I did not get how to compare them.

Thanks,
Sravz

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-16 : 05:44:03

Read up on subqueries.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources