SQL Questions..

[DJKAOS]

Ok I have 3 tables
1) S...and it has
sno*, sname, status, city

2) P.......and it has
pno*, pname, color, weight, city

3) SP.....and it has
sno*, pno*, qty

THis is actually for a class at school but I tried for 3 hours on this problem and can't get it to work.

My problem is this:
-----------------------------------------------------------
Get the names of the suppliers located in London or Paris, and who have shipped at least one green part and at least one red part. The supplier names must be listed in lexicographical order, and each supplier name must appear at most once.
-------------------------------------------------------


The problem before it was exacly the same exept it was with at least one red part OR at least one green part.

I got that to work with this statment.
select distinct s.sname
from s,p, sp where (s.city = 'Paris' or s.city = 'London'
and (p.color = 'red' or p.color = 'green') and sp.qty > 1 and sp.sno = s.sno and sp.pno = p.pno
order by s.sname asc



Any help is appriciated as I dont know what to do.

 

[stoolpigeon]

I don't want to just give you a query as this is homework and all.

But I'll throw a couple things your way that may help.


When I write a query I go ahead and break it down into simple parts and just build up. First I get rid of the fluff. I would think your core problem is "what suppliers have shipped a red AND a green part?" The rest is just formatting kind of stuff (other than the location I guess but that is secondary too.)

One way to get at that key information may be a nested query-- Query for all the suppliers who have shipped red parts where those suppliers are also in a set that has shipped green parts.

Lastly- your join syntax is not compliant w/the newer ansi standard. On SQL Server this would cause less than optimal performance. But I imagine that is not a primary concern of yours right now.

Hope that helps.

 

[cmmrfrds]

Here is an example of a group by query from the pubs database which comes standard with SQL Server. One way to solve the problem is to use the group by. SELECT pub_id, SUM(advance) AS AmountAdvanced,
AVG(price) AS AveragePrice
FROM pubs.dbo.titles
WHERE pub_id > '0800'
AND price >= $5
GROUP BY pub_id
HAVING SUM(advance) > $15000
AND AVG(price) < $20
ORDER BY pub_id DESC

The where clause qualifies the data before the summary and the having clause qualifies the data after the summary. In a group by query all the data elements must be part of the select or a summary value, such as, avg(price) or count(*) or sum(price), or included in the group by clause.

 

[DJKAOS]

Theologian, thanks I already had it broke down like that and I can't use nested querry because thats for another problem in the homework.

I just talked with my teacher and he said I have to use Aliases..... I kinda understand them but not really, I dont really understand how they work.

I had been trying to use aliases the whole time but couldnt get it to come out right so I figured I was doing it wrong.

 

[cutiger93]

Alias' are pretty simple.
Here is an example.

SELECT C.ICOMPANYID,C.VCHCOMPANYNAME,C.VCHADDRESS1,C.VCHCITY,
I.IINDIVIDUALID,I.VCHADDRESS,I.VCHCITY

FROM COMPANY C
INNER JOIN INDIVIDUAL I
ON
C.ICOMPANYID=I.IOWNERID
WHERE I.VCHFIRSTNAME='DJKAOS'

RESULTS:
1234 MINISOFT CORP 1 MINI WAY SEATTLE 55456 300 BELLVEU DR BELVUE

The alias referes to the table and allows you to place a letter or letters in place of the whole table name.

Think of an alias as a nick name: For example Larry Jones might be called Chipper!