I'm taking a database intro master's class. We are working on SQL. The professor likes to be ambiguous with certain explains.
Here's my question. Certain questions we are required to find out the opposite of a query something like if a supplier ships parts that are red and blue what colors don't the ship.
here is how I figured out a solution
SELECT distinct PARTS.COLOR FROM PARTS, SHIPMENTS WHERE PARTS.COLOR NOT IN( SELECT distinct PARTS.COLOR FROM SHIPMENTS, PARTS WHERE PARTS.PARTNO IN( SELECT distinct SHIPMENTS.PARTNO FROM SHIPMENTS WHERE SHIPMENTS.SUPPLIERNO='S1')) AND SHIPMENTS.PARTNO = PARTS.PARTNO;
What I was wondering is, is this best approach to this question. This works but I'm not sure it is how it should be done.
I should also mention he does not want us to use all available operations. He did not show us JOIN, EXISTS,
he showed us SELECT, IN, ALL/ANY, Aggregates so MAX, MIN, SUM, GROUP BY, and HAVING
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `PARTS` ADD INDEX `parts_idx_partno` (`PARTNO`); ALTER TABLE `PARTS` ADD INDEX `parts_idx_color` (`COLOR`); ALTER TABLE `SHIPMENTS` ADD INDEX `shipments_idx_partno` (`PARTNO`); ALTER TABLE `SHIPMENTS` ADD INDEX `shipments_idx_supplierno_partno` (`SUPPLIERNO`,`PARTNO`);
SELECT DISTINCT PARTS.COLOR FROM PARTS, SHIPMENTS WHERE NOT EXISTS ( SELECT DISTINCT 1 FROM SHIPMENTS, PARTS AS PARTS1 WHERE ( EXISTS ( SELECT DISTINCT 1 FROM SHIPMENTS WHERE ( SHIPMENTS.SUPPLIERNO = 'S1' ) AND ( PARTS1.PARTNO = SHIPMENTS.PARTNO ) ) ) AND ( PARTS.COLOR = PARTS1.COLOR ) ) AND SHIPMENTS.PARTNO = PARTS.PARTNO