# Help with SELECT queries

I need help with some queries. I struggled do find out how to do it, but I think I finally caught on or at least got the results I was looking for in the database.

These are the tables (given by my professor):

Here are the problem statements and the solutions I have come up with:

1) From that database, get supplier numbers for supplier who supply project J2, in supplier number order.

``````select SNO
from SPJ
Where JNO = 'J2'
Order by SNO ASC;
``````

2) Get part numbers for parts supplied by a supplier in LA

``````select DISTINCT PNO
from spj
where SNO in
(select SNO
from Suppliers
where CITY = 'LA' ) ;
``````

3) Get part numbers for parts supplied by a supplier in LA to a project in LA:

``````select PNO
from spj
where JNO in
(select JNO
from projects
where CITY in
(Select CITY
from Suppliers
Where CITY = 'LA' ));
``````

4) Get the total quantity of part P2 supplied by supplier S3

``````select SUM(QTY)
From spj
where PNO = 'P2'
and SNO = 'S3' ;
``````

5) for each part being supplied to a project, get the part number, the project number, and the corresponding total quantity

``````select PNO, JNO, QTY
from spj
``````

6) Get project names for project supplied by supplier S1 located in HON

``````Select Jname
from projects
where CITY = 'HON'
and JNO in
(Select JNO
from spj
where SNO = 'S1' );
``````

7) Get part numbers for parts supplied to any project in LA

``````select PNO
from spj
where JNO in
(Select JNO
from projects
where CITY = 'LA' );
``````

SQLFiddle with test data here

-
Could you help clarify what your question is? I love that you have shown your work, your data and the structure---that will serve you well on this and other forums. Your comment leads me to believe you have satisfactorily solved the problems, so what can we do? – billinkc Dec 9 '12 at 3:01
Do they seem right to you? My question is just that, does those look like the right queries to search for the needed answers of the questions. I'm not very sure/confident with my answers since I had to teach myself sql in like 3 hours ( my teacher is horrible ). My queries seemed to give me the answers ,but again I wanted someone with SQL knowledge to check it over – austinsql Dec 9 '12 at 3:18

Your answer to number 5 is lacking a `GROUP BY` (corresponding total quantity).