Question

Question 1) Some customers have not purchased any products. The store hopes to encourage these customers to buy their products through various discount coupons. For this purpose, the store's management would like to see all details of customers who have never purchased any products.  Use at least one RIGHT JOIN in this query.

Question 2) Some products have never been purchased by any customer. The store's management is considering removing these products from their shelves. They would like to get a list containing the name of vendors and the names of their products that have never been bought by any customer. Use at least one LEFT JOIN in this query.

The script file is given below.

Thank You

CREATE DATABASE salesDB;
USE salesDB;
CREATE TABLE customer (

CUS_CODE INT PRIMARY KEY,
CUS_LNAME VARCHAR(15),
CUS_FNAME VARCHAR(15),
CUS_INITIAL VARCHAR(1),
CUS_AREACODE VARCHAR(3),
CUS_PHONE VARCHAR(8),
CUS_BALANCE DECIMAL(15,3)
);
INSERT INTO customer VALUES('10010','Ramas','Alfred','A','615','844-2573','0');

INSERT INTO customer VALUES('10011','Dunne','Leona','K','713','894-1238','0');

INSERT INTO customer VALUES('10012','Smith','Kathy','W','615','894-2285','345.859985351562');

INSERT INTO customer VALUES('10013','Olowski','Paul','F','615','894-2180','536.75');

INSERT INTO customer VALUES('10014','Orlando','Myron','','615','222-1672','0');

INSERT INTO customer VALUES('10015','O''Brian','Amy','B','713','442-3381','0');

INSERT INTO customer VALUES('10016','Brown','James','G','615','297-1228','221.190002441406');

INSERT INTO customer VALUES('10017','Williams','George','','615','290-2556','768.929992675781');

INSERT INTO customer VALUES('10018','Farriss','Anne','G','713','382-7185','216.550003051758');

INSERT INTO customer VALUES('10019','Smith','Olette','K','615','297-3809','0');

CREATE TABLE vendor
(
V_CODE INT PRIMARY KEY,
V_NAME VARCHAR(15),
V_CONTACT VARCHAR(50),
V_AREACODE VARCHAR(3),
V_PHONE VARCHAR(8),
V_STATE VARCHAR(2),
V_ORDER VARCHAR(1)
);
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');
INSERT INTO vendor
VALUES('22567','Dome Supply','Smith','901','678-1419','GA','N');
INSERT INTO vendor
VALUES('23119','Randsets Ltd.','Anderson','901','678-3998','GA','Y');
INSERT INTO vendor
VALUES('24004','Brackman Bros.','Browning','615','228-1410','TN','N');
INSERT INTO vendor
VALUES('24288','ORDVA, Inc.','Hakford','615','898-1234','TN','Y');
INSERT INTO vendor
VALUES('25443','B&K, Inc.','Smith','904','227-0093','FL','N');
INSERT INTO vendor
VALUES('25501','Damal Supplies','Smythe','615','890-3529','TN','N');
INSERT INTO vendor
VALUES('25595','Rubicon Systems','Orton','904','456-0092','FL','Y');

/* -- */
CREATE TABLE invoice
(
INV_NUMBER INT PRIMARY KEY,
CUS_CODE INT,
INV_DATE DATETIME,
FOREIGN KEY(CUS_CODE) REFERENCES customer(CUS_CODE)

);
INSERT INTO invoice
VALUES('1001','10014','2016-1-16');
INSERT INTO invoice
VALUES('1002','10011','2016-1-16');
INSERT INTO invoice
VALUES('1003','10012','2016-1-16');
INSERT INTO invoice
VALUES('1004','10011','2016-1-17');
INSERT INTO invoice
VALUES('1005','10018','2016-1-17');
INSERT INTO invoice
VALUES('1006','10014','2016-1-17');
INSERT INTO invoice
VALUES('1007','10015','2016-1-17');
INSERT INTO invoice
VALUES('1008','10011','2016-1-17');

/* -- */
CREATE TABLE product
(
P_CODE VARCHAR(10) PRIMARY KEY,
P_DESCRIPT VARCHAR(35),
P_INDATE DATETIME,
P_QOH INT,
P_MIN INT,
P_PRICE DECIMAL(15,3),
P_DISCOUNT DECIMAL(15,3),
V_CODE INT,
FOREIGN KEY (V_CODE) REFERENCES vendor
(V_CODE)
);
INSERT INTO product
VALUES('11QER/31','Power painter, 15 psi., 3-nozzle','2015-11-3','8','5','109.99','0','25595');
INSERT INTO product
VALUES('13-Q2/P2','7.25-in. pwr. saw blade', '2015-12-13','32','15', '14.99','0.05','21344');
INSERT INTO product
VALUES('14-Q1/L3','9.00-in. pwr. saw blade', '2015-11-13','18','12','17.49','0','21344');
INSERT INTO product
VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50', '2016-1-15','15','8','39.95','0','23119');
INSERT INTO product
VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50', '2016-1-15','23','5','43.99','0','23119');
INSERT INTO product
VALUES('2232/QTY','B\&D jigsaw, 12-in. blade', '2015-12-30','8','5','109.92','0.05','24288');
INSERT INTO product
VALUES('2232/QWE','B\&D jigsaw, 8-in. blade', '2015-12-24','6','5','99.87','0.05','24288');
INSERT INTO product
VALUES('2238/QPD','B\&D cordless drill, 1/2-in.', '2016-1-20','12','5','38.95','0.05','25595');
INSERT INTO product
VALUES('23109-HB','Claw hammer', '2016-1-20','23','10','9.95','0.1','21225');
INSERT INTO product
VALUES('23114-AA','Sledge hammer, 12 lb.', '2016-1-2','8','5','14.40','0.05', NULL);
INSERT INTO product
VALUES('54778-2T','Rat-tail file, 1/8-in. fine', '2015-12-15','43','20','4.99','0','21344');
INSERT INTO product
VALUES('89-WRE-Q','Hicut chain saw, 16 in.', '2016-2-7','11','5','256.99','0.05','24288');
INSERT INTO product
VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft', '2016-2-20','188','75','5.87','0', NULL);
INSERT INTO product
VALUES('SM-18277','1.25-in. metal screw, 25', '2016-3-1','172','75','6.99','0','21225');
INSERT INTO product
VALUES('SW-23116','2.5-in. wd. screw, 50', '2016-2-24','237','100','8.45','0','21231');
INSERT INTO product
VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','2016-1-17','18','5','119.95','0.1','25595');

/* -- */
CREATE TABLE line (
INV_NUMBER INT,
line_NUMBER INT,
P_CODE VARCHAR(10),
line_UNITS DECIMAL(8),
line_PRICE DECIMAL(15,3),
PRIMARY KEY(INV_NUMBER, line_NUMBER),
FOREIGN KEY(INV_NUMBER) REFERENCES invoice
(INV_NUMBER),
FOREIGN KEY(P_CODE) REFERENCES product
(P_CODE)
);
INSERT INTO line VALUES('1001','1','13-Q2/P2','1','14.99');
INSERT INTO line VALUES('1001','2','23109-HB','1','9.95');
INSERT INTO line VALUES('1002','1','54778-2T','2','4.99');
INSERT INTO line VALUES('1003','1','2238/QPD','1','38.95');
INSERT INTO line VALUES('1003','2','1546-QQ2','1','39.95');
INSERT INTO line VALUES('1003','3','13-Q2/P2','5','14.99');
INSERT INTO line VALUES('1004','1','54778-2T','3','4.99');
INSERT INTO line VALUES('1004','2','23109-HB','2','9.95');
INSERT INTO line VALUES('1005','1','PVC23DRT','12','5.87');
INSERT INTO line VALUES('1006','1','SM-18277','3','6.99');
INSERT INTO line VALUES('1006','2','2232/QTY','1','109.92');
INSERT INTO line VALUES('1006','3','23109-HB','1','9.95');
INSERT INTO line VALUES('1006','4','89-WRE-Q','1','256.99');
INSERT INTO line VALUES('1007','1','13-Q2/P2','2','14.99');
INSERT INTO line VALUES('1007','2','54778-2T','1','4.99');
INSERT INTO line VALUES('1008','1','PVC23DRT','5','5.87');
INSERT INTO line VALUES('1008','2','WR3/TT3','3','119.95');
INSERT INTO line VALUES('1008','3','23109-HB','1','9.95');

/* -- */
CREATE TABLE emp (
emp_NUM INT PRIMARY KEY,
emp_TITLE VARCHAR(4),
emp_LNAME VARCHAR(15),
emp_FNAME VARCHAR(15),
emp_INITIAL VARCHAR(1),
emp_DOB DATETIME,
emp_HIRE_DATE DATETIME,
emp_AREACODE VARCHAR(3),
emp_PHONE VARCHAR(8),
emp_MGR INT
);
INSERT INTO emp VALUES('100','Mr.','Kolmycz','George','D','1945-6-15','1985-3-15','615','324-5456',NULL);
INSERT INTO emp VALUES('101','Ms.','Lewis','Rhonda','G','1965-3-19','1986-4-25','615','324-4472','100');
INSERT INTO emp VALUES('102','Mr.','VanDam','Rhett','','1958-11-14','1990-12-20','901','675-8993','100');
INSERT INTO emp VALUES('103','Ms.','Jones','Anne','M','1974-10-16','1994-8-28','615','898-3456','100');
INSERT INTO emp VALUES('104','Mr.','Lange','John','P','1971-11-8','1994-10-20','901','504-4430','105');
INSERT INTO emp VALUES('105','Mr.','Williams','Robert','D','1975-3-14','1998-11-8','615','890-3220', NULL);
INSERT INTO emp VALUES('106','Mrs.','Smith','Jeanine','K','1968-2-12','1989-1-5','615','324-7883','105');
INSERT INTO emp VALUES('107','Mr.','Diante','Jorge','D','1974-8-21','1994-7-2','615','890-4567','105');
INSERT INTO emp VALUES('108','Mr.','Wiesenbach','Paul','R','1966-2-14','1992-11-18','615','897-4358',NULL);
INSERT INTO emp VALUES('109','Mr.','Smith','George','K','1961-6-18','1989-4-14','901','504-3339','108');
INSERT INTO emp VALUES('110','Mrs.','Genkazi','Leighla','W','1970-5-19','1990-12-1','901','569-0093','108');
INSERT INTO emp VALUES('111','Mr.','Washington','Rupert','E','1966-1-3','1993-6-21','615','890-4925','105');
INSERT INTO emp VALUES('112','Mr.','Johnson','Edward','E','1961-5-14','1983-12-1','615','898-4387','100');
INSERT INTO emp VALUES('113','Ms.','Smythe','Melanie','P','1970-9-15','1999-5-11','615','324-9006','105');
INSERT INTO emp VALUES('114','Ms.','Brandon','Marie','G','1956-11-2','1979-11-15','901','882-0845','108');
INSERT INTO emp VALUES('115','Mrs.','Saranda','Hermine','R','1972-7-25','1993-4-23','615','324-5505','105');
INSERT INTO emp VALUES('116','Mr.','Smith','George','A','1965-11-8','1988-12-10','615','890-2984','108');

/* -- */
CREATE TABLE employee (
emp_NUM INT PRIMARY KEY,
emp_TITLE VARCHAR(4),
emp_LNAME VARCHAR(15),
emp_FNAME VARCHAR(15),
emp_INITIAL VARCHAR(1),
emp_DOB DATETIME,
emp_HIRE_DATE DATETIME,
emp_YEARS INT,
emp_AREACODE VARCHAR(3),
emp_PHONE VARCHAR(8)
);
INSERT INTO employee VALUES('100','Mr.','Kolmycz','George','D','1942-6-15','1985-3-15','18','615','324-5456');
INSERT INTO employee VALUES('101','Ms.','Lewis','Rhonda','G','1965-3-19','1986-4-25','16','615','324-4472');
INSERT INTO employee VALUES('102','Mr.','VanDam','Rhett','','1958-11-14','1990-12-20','12','901','675-8993');
INSERT INTO employee VALUES('103','Ms.','Jones','Anne','M','1974-10-16','1994-8-28','8','615','898-3456');
INSERT INTO employee VALUES('104','Mr.','Lange','John','P','1971-11-8','1994-10-20','8','901','504-4430');
INSERT INTO employee VALUES('105','Mr.','Williams','Robert','D','1975-3-14','1998-11-8','4','615','890-3220');
INSERT INTO employee VALUES('106','Mrs.','Smith','Jeanine','K','1968-2-12','1989-1-5','14','615','324-7883');
INSERT INTO employee VALUES('107','Mr.','Diante','Jorge','D','1974-8-21','1994-7-2','8','615','890-4567');
INSERT INTO employee VALUES('108','Mr.','Wiesenbach','Paul','R','1966-2-14','1992-11-18','10','615','897-4358');
INSERT INTO employee VALUES('109','Mr.','Smith','George','K','1961-6-18','1989-4-14','13','901','504-3339');
INSERT INTO employee VALUES('110','Mrs.','Genkazi','Leighla','W','1970-5-19','1990-12-1','12','901','569-0093');
INSERT INTO employee VALUES('111','Mr.','Washington','Rupert','E','1966-1-3','1993-6-21','9','615','890-4925');
INSERT INTO employee VALUES('112','Mr.','Johnson','Edward','E','1961-5-14','1983-12-1','19','615','898-4387');
INSERT INTO employee VALUES('113','Ms.','Smythe','Melanie','P','1970-9-15','1999-5-11','3','615','324-9006');
INSERT INTO employee VALUES('114','Ms.','Brandon','Marie','G','1956-11-2','1979-11-15','23','901','882-0845');
INSERT INTO employee VALUES('115','Mrs.','Saranda','Hermine','R','1972-7-25','1993-4-23','9','615','324-5505');
INSERT INTO employee VALUES('116','Mr.','Smith','George','A','1965-11-8','1988-12-10','14','615','890-2984');

Answer 1

Question 1:

SQL query :

select cus_code,concat(cus_fname,' ',cus_lname) as 'Customer Name'
from customer where cus_code not in (
select invoice.cus_code from customer right join invoice
on
customer.CUS_CODE=invoice.CUS_CODE);

Query result :

**********************************************

Question 2 :

SQL query :

select V_name,p_descript from vendor inner join product
on vendor.V_CODE=product.V_CODE where P_CODE not in
(select line.P_CODE from line left join product on
line.P_CODE=product.P_CODE);

Explanation :

• Inner SQL query will return all the products purchased by the customer from line table
• and outer query will return only thoese products that are not purchased by the customer

Query result :