Having trouble with the count function

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

Old Forums

CLOSED - General SQL Server

Having trouble with the count function

Author

Topic

jamjar
Starting Member

2 Posts

Posted - 2002-10-18 : 14:15:41

I would appreciate any help anyone could give me on this, I am pretty new to SQL and finding it a bit of a steep learning curve. My problem is I have to find a list of teachers who have at least 3 degree students in their class. Here is my shot at it that hasn't been very successful:

select l.title, l.name,u.unit_code
from u,l
where l.STAFF_NO in (
select STAFF_NO
from u
where UNIT_CODE in (
select count (UNIT_CODE)
from e
where STU_NO > 3));

That part so far doesn't work, I wanted to some how connect the stu_no with this query:

select STU_NO
from s
where course='degree';

I don't know how to link this part either. I have been scratching my head over this for days.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-18 : 14:23:54

Can you post some CREATE TABLE Statements along with a bit of sample data?

Here's an example of what I'm looking for.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=20811

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jamjar
Starting Member

2 Posts

Posted - 2002-10-18 : 14:33:18

No problem:

create table l
(staff_no varchar2(3) constraint pk_l_no primary key,
initials varchar2(6),
name varchar2(20),
title varchar2(10));

create table s
(stu_no varchar2(8) constraint pk_s_no primary key,
initials varchar2(6),
name varchar2(20),
street varchar2(20),
city varchar2(15),
course varchar2(6),
constraint fk_s foreign key (degree) references d(degree));

create table u
(unit_code varchar2(6) constraint pk_u_code primary key,
name varchar2(30),
staff_no varchar2(3),
room_no varchar2(5),
constraint fk_u foreign key (staff_no) references l(staff_no),
constraint fk_u2 foreign key (room_no) references r(room_no));

create table e
(unit_code varchar2(6),
stu_no varchar2(8),
result number,
grade varchar2(3),
constraint fk_e_code foreign key (unit_code) references u(unit_code),
constraint fk_e_no foreign key (stu_no) references s(stu_no),
constraint pk_e primary key (unit_code, stu_no));

Does that help, thanks for answering so quick!

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-18 : 15:13:26

What version of SQL Server are you running?

What is a varchar2(3)???

I can't create these tables on my SQL 2000 box.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-18 : 15:21:17

Sounds like Oracle...we're a SQL Server site.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-18 : 16:10:49

When I saw the single letter table names, the "Oracle" alarm when to DefCon 3. When I saw varchar2(3), it went to DefCon 1.

I knew something was weird with those statements.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources