Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Beginner SQL question (select statement)
If you intend to do serious work with ORACLE (or any RDBMS, for that matter), I strongly recommend that you learn data modelling principles, especially data normalization. Your Products table is not even in First Normal Form (1NF) when it should have been in 3NF. In 3NF, the Products table looks like this:
(part_number, warehouse_id, qty_available), so there will be two records for the part number 123456, one for Houston warehouse and one for the Phoenix warehouse. The primary key (which uniquely identifies a row, or tuple, in a table) for the Products table is (part_number, warehouse_id). I hope you agree that the normalized table definition makes the query you want much easier to write (although it will be more complicated to put the two qty_available values in the same row, but somehow I don't think that this was your problem). There are other issues involved, especially if you have more than two warehouses, etc. BTW, I do not see any reason to de-normalize the Products table, if you indeed had it normalized initially.
Please take it as friendly advice, not flaming or anything of that nature. My intent is to help, not discourage, you. As far as readings re data normalization, they are too numerous to name. Any decent college-level course on relational database theory must have at least one in its bibliography.
I hope this helps.
Michael Serbanescu