Need help for creating couple SQL queries

Question

Need help for creating couple SQL queries

dejansoftware

Grasshopper

Points: 12
More actions
August 29, 2009 at 8:59 am

#95769

Table: Students
index# PK
Name
Surname
Table: Exams
index#
subject#
Grade
Table: Subjects
subject# PK
Subject_name
profesor#
Table: Profesors
profesor# PK
Name
Surname
Town
And the queries are:
1. Who are the professors who are teaching more than 2 subjects
2. Who are the students who have grade A from Visual Basic
3. How many students passed Visual Basic
4. Who is the professor who gave the most E grades
5. What is the name of the student who passed lowest number of subjects
6. What is the avarage grade of Dejan
Please help me to solve this.
Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

This looks very much like homework. Is it?

We don't solve people's homework problems for them, they learn nothing if we do. Have a go at them yourself, if you get stuck feel free to ask for advice.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

And the answers are:

1. Who are the professors who are teaching more than 2 subjects...

try linking profesor to exams and use a Group and Count

2. Who are the students who have grade A from Visual Basic...

link Student to Exams and filter on Grades

3. How many students passed Visual Basic...

unless you post any data..I haven't got any idea...perhaps you know?

4. Who is the professor who gave the most E grades...

ditto

5. What is the name of the student who passed lowest number of subjects

Dejan...??

6. What is the avarage grade of Dejan

lazy !

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131962

and

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/221395b8-2308-4b79-9589-1789fe55d67b

same question appears to be posted on other forums as well...homework.....hmmmmm !

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

dejansoftware Grasshopper Points: 12 More actions · Answer 3

Ok, I have done it.

I still need help with one little thing:

Question: What is the professor name and surname with lowest grade standard, but instead of professor code I would like to see his name and surname:

SELECT Predmeti.profesor#, AVG(Ispiti.Ocjena) AS prosjek

FROM Ispiti INNER JOIN

Predmeti ON Ispiti.predmet# = Predmeti.predmet# INNER JOIN

Profesori ON Predmeti.profesor# = Profesori.profesor#

GROUP BY Predmeti.profesor#

HAVING (AVG(Ispiti.Ocjena) =

(SELECT MIN(prosjek) AS Expr1

FROM (SELECT Predmeti_1.profesor#, AVG(Ispiti_1.Ocjena) AS prosjek

FROM Ispiti AS Ispiti_1 INNER JOIN

Predmeti AS Predmeti_1 ON Ispiti_1.predmet# = Predmeti_1.predmet#

GROUP BY Predmeti_1.profesor#) AS pros))

AGENDA:

predmet/i = subject/s

ocjena = grade

ispit/i = Exam/s

prosjek = AVG

profesor/i = professor/s

All the tables are in first post

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

So add the name and surname to the SELECT and Group By. The correct table is already in the query.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass