Help w/ SQL Newbie Student

Hi,

I am working on a school project ...

I have the following schema:
<b>
DIVISION (dvname, manager)
DEPT (dname, parent-dname/parent-dvname, manager, floor#)
EMP (ename, salary, dname/dvname)
ITEM (iname, color, price, type)
SELL (dname, iname)
SUPPLY (sname, iname, dname)
</b>
Each of the first fields is the PKey.

I have to figure out the SQL for the following statement:

<b>List the items supplied by all companies that supply all items of type A.</b>

I have gotten this far, but do not understand division in SQL well enough .. I have a relational algebra solution that works ... but am having a hell of a time with a SQL solution ... Please help,

This is what I have in SQL:
SELECT SE.iname FROM SUPPLY SE WHERE NOT EXISTS
(SELECT I.iname FROM ITEM I WHERE I.type='A' AND NOT EXISTS
(SELECT S.iname FROM SUPPLY S WHERE S.iname=I.iname AND S.dname=SE.dname))

I have not work with SQL much so please help out ... I am using SQL Server if that matters ... Thanks

... I have done the frist 49 of the queries ... and they all made sense ... but this one is just bugging me ... PLEASE HELPDon't feel bad. This is not a simple task.

Some questions:
Does this have to be done in a single SELECT statement, or can it be done using a stored procedure or user-defined function that contains multiple SQL statements?

You need to list items supplied by companies, but all I see in your structure are departments and divisions. Please clarify.

Your DIVISION and DEPT tables are kind of a goofy schema, but I suppose you have no control over that?

What is the purpose of the SELL table vs the SUPPLY table? Can we ignore the SELL table?

Is a company considered to be a supplier of all type "A" items if its departments and divisions collectively supply all the items, or does it have to supply the items itself directly?

Can we ignore the EMP table? It appears to have nothing to do with the problem.

blindman|||Originally posted by blindman
Don't feel bad. This is not a simple task.

Some questions:
Does this have to be done in a single SELECT statement, or can it be done using a stored procedure or user-defined function that contains multiple SQL statements?

You need to list items supplied by companies, but all I see in your structure are departments and divisions. Please clarify.

Your DIVISION and DEPT tables are kind of a goofy schema, but I suppose you have no control over that?

What is the purpose of the SELL table vs the SUPPLY table? Can we ignore the SELL table?

Is a company considered to be a supplier of all type "A" items if its departments and divisions collectively supply all the items, or does it have to supply the items itself directly?

Can we ignore the EMP table? It appears to have nothing to do with the problem.

blindman

The assignment had 50 queries ... of all different kinds.

We have to do it in a single SELECT statement (with subqueries of course)

I do not think that an table except the SUPPLY and ITEM are needed but I dont know exactly how they interact ...|||If your answer must be a single select statement, then that eliminates any possibility of searching the departmental hierarchy, and so actually makes the problem easier.

Break your problem into parts.

If you had a list of companies (departments?) then it would be trivial to list all the products they sell, right?

So your task is to create a subquery that lists all the departments that sell all type "A" products. How do you determine if a department sells all of the type "A" products?

The most direct method would be to find Departments where the number of distinct type "A" products they sell equals the number of type "A" products that exist.

The more subtle method (which your code seems to be attempting) is to create a list of departments that DON'T sell all the type "A" items, and then find all the departments that AREN'T on that list. This is certainly a more confusing method than the product count technique, but it may have some efficient advantages for extremely large tables. I don't know. But I do know you can get a list of departments that don't sell a full line of type "A" products by using a FULL OUTER JOIN between DEPARTMENT and a list of type "A" suppliers.

blindman

Help w/ Calculation

I need to calculate the overall GPA for a student in a particular class.

YEAR SCHOOL STUDENT IDENT GRADE TEACHER CLASS GPA
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 1.6700
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.3300
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 1.0000

The problem I'm having is that a student may not taken the class for four terms (as in the Elementary Algebra example above). So I can't hard code it to sum the gpa and divide by 4; it needs to be the number of terms the student took the class.

Here's my sql:

select
trnscrpt.schyear as [Year],
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as suniq,
stugrp_active.graden as Grade,
trnscrpt.teachname as Teacher,
trnscrpt.descript as Class,
gpamarks.gpavallvl0 AS GPA

from
dbo.trnscrpt
inner join dbo.stugrp_active on trnscrpt.suniq = stugrp_active.suniq INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
trnscrpt.graden >= 6 and
trnscrpt.markawd1 not in ('NC','NG','P','W','WA','WF','WI','WP') and
trnscrpt.subjectc in ('LA', 'MA', 'CP', 'CB') and
trnscrpt.schyear = 2007 and
stugrp_active.schoolc = 725

order by
school.schname,
student,
grade,
class

Have you considered a AVG() and a GROUP BY?

|||

That worked. In the beginning the averages weren't what I expected but it was mistake on my part. Thanks and I like your signature. So true.