# 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? Dec 9, 2012 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 Dec 9, 2012 at 3:18

## 1 Answer

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

If your course requires you to return sets (as opposed to multi-sets) you may need to eliminate potential duplicates from some of those queries. Your results may not show duplication now, due to the data in the tables, but to be correct for different states of the database (different contents) you may need to add DISTINCT, unless constraints exist to make it unnecessary.

Some of your queries could equally be expressed using joins - if you have covered those, it might be a good idea to use some.