sql mistake - SQL Server Forums

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

sql mistake

Author

Topic

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-06 : 04:22:30

Hi I have a problem with two exercises. Here's the first one:
Assume there are these tables
STUDENT(CODE_S,NAME_S) (code_s=PRIMARY KEY)
LESSON(CODE_L,NAME_L) (code_L=PRIMARY KEY)
EXAMS(CODE_S,CODE_L,GRADES)

And the SQL
Select STUDENT.code_s,name_s,avg(grades)
From student inner join exams on (code_s.student=code_s.exams)
where (avg(grades)>9)
group by student.code_s,name_s
order by avg;

The question now:
Which of the following statements have mistakes:
a)Select STUDENT.code_s,name_s,avg(grades)?
b)From student inner join exams on (code_s.student=code_s.exams)?
c)where (avg(grades)>9)?
d)group by student.code_s,name_s

Thank you..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-06 : 04:26:35

try like this
Select STUDENT.code_s,name_s,avg(grades)
From student inner join exams on (student.code_s=exams.code_s)
group by student.code_s,name_s
having avg(grades)>9
order by avg;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 04:26:46

If you try to run it a query window, sql server would have told you b) is wrong.

E 12°55'05.63"
N 56°04'39.26"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-06 : 04:26:52

where (avg(grades)>9) in this u have an error bcoz aggregate functions May not appear in where clause

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-06 : 04:27:31

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Jai Krishna

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-06 : 04:32:58

Thank you very very much . But I am a bit confused. Both b and c are wrong ? sorry but I just started working with sql.

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-06 : 04:35:12

Thank you very very much . But I am a bit confused. Both b and c are wrong ? sorry but I just started working with sql.
And what is aggregate ??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 04:36:10

An aggregation is a sum, or minimum value, or in this case an average.
An aggregation is always calculated on a whole set/group and not per record.

E 12°55'05.63"
N 56°04'39.26"

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-06 : 04:36:39

Both B and C are wrong

B should be like this
From student inner join exams on (student.code_s=exams.code_s)

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-06 : 04:38:35

aggregate functions are max(column),min(column),avg(column)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-06 : 04:38:51

select * from table where normalcondtions group by (while using aggregatefunctions) having will follows the condtion to check on aggregatefuncitons order by to sort the values

in case of b
while join condition on tablename.column u have to give

and first i have given u query which should be like that

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-06 : 04:41:08

quote:
Originally posted by Jai Krishna

Both B and C are wrong

B should be like this
From student inner join exams on (student.code_s=exams.code_s)

Jai Krishna

Thank you I will post in a few minutes my other exercise. You ' re all very Kind. Thank you a lot.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-06 : 04:45:06

Welcome

Jai Krishna

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-06 : 05:39:33

Number2:
Assume there are these tables
STUDENT(CODE_S,NAME_S) (code_s=PRIMARY KEY)
LESSON(CODE_L,NAME_L) (code_L=PRIMARY KEY)
EXAMS(CODE_S,CODE_L,GRADES)

Which of the following statements will answer the question: For each lesson (mention name, Name_L) find the average of grades of the students that have attented it.

1)select lesson.name_L,avg(exams.grades)
from lesson ,exams
where (lesson.code_L=exams.code_L)
group by Lesson.Code_L;

2)select lesson.name_L,avg(exams.grades)
from lesson,exams
where (lesson.code_L=exams.code_L)
group by Lesson.name_L;

3)select lesson.name_L,avg(exams.grades)
from lesson inner join exams on lesson.code_L=exams.code_L;

4)select lesson.name_L,avg(exams.grades)
from lesson inner join exams on lesson.code_L=exams.code_L
group by Lesson.name_L;

Thank you once more.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-06 : 05:42:42

select lesson.name_L,avg(exams.grades)
from lesson inner join exams on lesson.code_L=exams.code_L
group by Lesson.name_L;

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-06 : 05:42:43

Have u tried all these queries

Jai Krishna

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-06 : 05:42:54

And something else
I tried to make these tables in SQL and they don't work.
I think I dont have a propre syntax in the table exams:

create table exams
(code_L int,
code_S int,
grades number(2),
foreign key (code_L) references Lesson (code_L),
foreign key (code_S) references Student (code_S));

Is that the propre way?

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-06 : 05:47:15

quote:
Originally posted by Jai Krishna

Have u tried all these queries

Jai Krishna

I try for two days now but I must have the tables wrong.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-06 : 05:47:16

create table
(
grades number(2),
code_L Int Constraint FK_Column1 Foreign Key References Lesson (code_L),
code_S Int Constraint FK_Column2 Foreign Key References Student (code_S)

)

misty
Yak Posting Veteran

80 Posts

Posted - 2009-02-06 : 05:48:38

quote:
Originally posted by Nageswar9

create table
(
grades number(2),
foreign key (code_L) references Lesson (code_L),
foreign key (code_S) references Student (code_S)
)

Thanks I'll try again.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-06 : 05:50:55

quote:
Originally posted by misty

quote:
Originally posted by Nageswar9

create table
(
grades number(2),
foreign key (code_L) references Lesson (code_L),
foreign key (code_S) references Student (code_S)
)

Thanks I'll try again.

Just check my answer
i modified the table Structure once go through it

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources