Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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):

Tables

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

share|improve this question
    
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

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.