SQL help

[Koing]

Hi I need some SQL help.

I have the following questions asked of me here:

http://homepage.ntlworld.com/ph.chea/Sql%20work.pdf

I have written out the answers here. I was wondering if they are correct.

Question 1:
Query1:
SELECT sname
FROM supplier s
WHERE NOT EXISTS
(SELECT *
FROM Part p
WHERE p.colour = ?blue? AND
NOT EXISTS
(SELECT *
FROM project j
WHERE j.jcity = ?Athens? V j.jcity = ?Rome? AND
NOT EXISTS
(SELECT *
FROM spj x
WHERE s.s# = x.s# AND p.p# = x.p# AND j.j# = x.j#)));

Query2:
SELECT sname, status
FROM supplier s
WHERE s.scity = ?Paris? AND
NOT EXISTS
(SELECT *
FROM Part p , Project j
WHERE p.colour = ?Red? AND j.jcity = ?London? AND
EXISTS
(SELECT *
FROM spj x
WHERE s.s# = x.s# AND p.p# = x.p# AND j.j# = x.j#));

Question 2:

SELECT spj.p# , SUM(spj.qty)
FROM spj
PROJECT WHERE NOT PROJECT.jcity = ?Oslo?
GROUP BY spj.p#
HAVING SUM (spj.qty) > 500

Question 3:

UPDATE spj
SET qty = qty * 1.1
SELECT scity, pname, jcity
FROM supplier s, part p , project j
WHERE s.scity = ?London? AND p.pname = ?nuts? AND j.jcity = ?Paris?

Thanks Koing

 

[Koing]

bump

 

[Koing]

bump

 

[mundane]

I know it's not what you're looking for, but it wouldn't be too much work to whip up a quick db in mySQL/Postgres/Cloudscape, populate it with some values (escpecially boundary conditions), and then run the queries against that.

 

[Noirish]

real life problems are not as "wordy" as those stupid questions...
one thing i hate about college...

 

[Rias]

Question 1:
Query1:
SELECT sname
FROM supplier s
WHERE NOT EXISTS
(SELeECT *
FROM Part p
WHERE p.colour = ?blue? AND
NOT EXISTS
(SELECT *
FROM project j
WHERE j.jcity = ?Athens? V j.jcity = ?Rome? AND
NOT EXISTS
(SELECT *
FROM spj x
WHERE s.s# = x.s# AND p.p# = x.p# AND j.j# = x.j#)));

Is there a reason you're not "just" doing a regular join in the first query? They are not asking for ONLY supplying blue parts as far as I understand?

select s.sname from supplier s, part p, project j, spj x
where j.jcity in ('Athens','Rome') and p.colour = 'blue'
and x.s# = s.s# and x.p# = p.p# and j.j# and x.j#

?

Sorry, I'm typing on a sh*tty laptop where I can hardly see what I'm writing

 

[Koing]

Originally posted by: diegoalcatraz
I know it's not what you're looking for, but it wouldn't be too much work to whip up a quick db in mySQL/Postgres/Cloudscape, populate it with some values (escpecially boundary conditions), and then run the queries against that.

Yeah I tried that but I don't know what the fvck I am diong with mySQL so couldn't do anything with it

Yeah orangel it is wordy but hey man I got to do it or not pass.

Koing

 

[Koing]

Originally posted by: Rias

Question 1:
Query1:
SELECT sname
FROM supplier s
WHERE NOT EXISTS
(SELeECT *
FROM Part p
WHERE p.colour = ?blue? AND
NOT EXISTS
(SELECT *
FROM project j
WHERE j.jcity = ?Athens? V j.jcity = ?Rome? AND
NOT EXISTS
(SELECT *
FROM spj x
WHERE s.s# = x.s# AND p.p# = x.p# AND j.j# = x.j#)));

Is there a reason you're not "just" doing a regular join in the first query? They are not asking for ONLY supplying blue parts as far as I understand?

select s.sname from supplier s, part p, project j, spj x
where j.jcity in ('Athens','Rome') and p.colour = 'blue'
and x.s# = s.s# and x.p# = p.p# and j.j# and x.j#

?

Sorry, I'm typing on a sh*tty laptop where I can hardly see what I'm writing

The thing is that are only suppose to use SQL Structured Query Language only. We can't use *other* things built in to say mySQL or whatever SQL program. We are only suppose to use the constraints of SQL to make our queries up for this piece of work. So I don't know if your answer is correct as you didn't know this info. And my SQL knowledge is only from my notes.

The thing is they are asking for all blue parts in Athens or Rome. But from our notes SQL can not handle the all semantics so you have to use not exisit instead and and on subsquent exisits replace it with *not exisits* also.

But I don't know about the whole thing.

Koing

ps don't worry about typo's or grammer etc I'm no narzi! Just want info that helps.

 

[Rias]

You probably wont get far with mysql anyway as it has a few limitations. If it's specific to the not exists I don't know, but I know you can do subselects. The only thing that might be considered non-standard is the in part. If not you could just do it with logic and say "and (jcity = thens' or jcity = 'Paris')"

Edit: that would be "can't do subselects" sorry.

 

[Koing]

Originally posted by: Rias
You probably wont get far with mysql anyway as it has a few limitations. If it's specific to the not exists I don't know, but I know you can do subselects. The only thing that might be considered non-standard is the in part. If not you could just do it with logic and say "and (jcity = thens' or jcity = 'Paris')"

Edit: that would be "can't do subselects" sorry.

I'm just going to go with my way as it is due tomorrow.

Thanks for the help anyway Rias and everyone else!

Koing