Question

Table product

P_CODE	P_DESCRIPT	P_INDATE	P_QOH	P_MIN	P_PRICE	P_DISCOUNT	V_CODE
23114-AA	Sledge hammer, 12 lb.	02-01-08	8	5	14.4	0.05
54778-2T	Rat-tail file, 1/8-in. fine	15-12-07	43	20	4.99	0	21344
89-WRE-Q	Hicut chain saw, 16 in.	07-02-08	11	5	256.99	0.05	24288

and so on.....

Table vendor

V_CODE	V_NAME	V_CONTACT	V_AREACODE	V_PHONE	V_STATE	V_ORDER
21225	Bryson, Inc.	Smithson	615	223-3234	TN	Y
21226	SuperLoo, Inc.	Flushing	904	215-8995	FL	N
21231	D&E Supply	Singh	615	228-3245	TN	Y
21344	Gomez Bros.	Ortega	615	889-2546	KY	N

And so on.....

write a query to get

Using EXCEPT show the codes of the vendors who did not supply any products.

Answer 1

Solution:

Here 2 tables are given. product table and vendor table. some vendor supplies some products and some are not supplying anything.

EXCEPT is SQL clause. When using EXCEPT clause, there are 2 SELECT statements and they are joined using EXCEPT clause

Query to get the display the codes of the vendors who did not supply any products using EXCEPT clause:

SELECT V_CODE
FROM vendor
EXCEPT
SELECT V_CODE
FROM product;

The vendor codes returned by SELECT statement for product table are removed from the records returned by the SELECT statement for vendor table.

PLEASE SEE THE DATA USED IN INSERT QUERY. NOTICE V_CODE 21225 IS THE ONLY VENDOR WHICH SUPPLIES ANY PRODUCT.

SQL QUERY AND DATA USED:

/* Create a table called NAMES */
CREATE TABLE product(
P_CODE VARCHAR(30) PRIMARY KEY,
P_DESCRIPT TEXT,
P_INDATE DATE,
P_QOH INTEGER,
P_MIN INTEGER,
P_PRICE FLOAT,
P_DISCOUNT FLOAT,
V_CODE INTEGER);

/* Create few records in this table */
INSERT INTO product VALUES("23114-AA","Sledge hammer, 12 lb.","02-01-08",8,5,14.4,0.05,NULL);
INSERT INTO product VALUES("54778-2T","Rat-tail file, 1/8-in. fine","15-12-07",43,20,4.99,0,21344);
INSERT INTO product VALUES("89-WRE-Q","Hicut chain saw, 16 in.","07-02-08",11,5,256.99,0.05,21225);

/* insert records in product table */
CREATE TABLE vendor(
V_CODE INTEGER PRIMARY KEY,
V_NAME TEXT,
V_CONTACT TEXT,
V_AREACODE INTEGER,
V_PHONE VARCHAR(9),
V_STATE VARCHAR(3),
V_ORDER CHAR);

/* insert record in vendor table */
INSERT INTO vendor VALUES(21225,"Bryson, Inc.","Smithson",615,"223-3234","TN",'Y');
INSERT INTO vendor VALUES(21226,"SuperLoo, Inc.","Flushing",904,"215-8995","FL",'N');
INSERT INTO vendor VALUES(21231,"D&E Supply","Singh",615,"228-3245","TN",'Y');
INSERT INTO vendor VALUES(21344,"Gomez Bros.","Ortega",615,"889-2546","KY",'N');

/* query to display vendors who didn't supply any product */
--SELECT * FROM product;
--SELECT * FROM vendor;
SELECT V_CODE
FROM vendor
EXCEPT
SELECT V_CODE
FROM product;

OUTPUT OF QUERY: