Question

In: Computer Science

Question 1) Some customers have not purchased any products. The store hopes to encourage these customers...

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');

Solutions

Expert Solution

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 :

venereology answered 3 months ago
Next > < Previous

Related Solutions

Sweater Co offers its customers the right to return any products purchased up to 30 days...
Sweater Co offers its customers the right to return any products purchased up to 30 days after the sale, for any reason. Last Tuesday, Sweater Co sold 100 red sweaters to different customers. Based on historical experience, Sweater Co expects 15 of those sweaters to be returned for a full refund. Each sweater sells for $80 and costs the company $35 to produce. What entries should be made?
The records of a computer retail store show that out of the 20 customers who purchased...
The records of a computer retail store show that out of the 20 customers who purchased a desktop computer last month, all but 5 also purchased a service plan that extends the warranty for an extra year. Out of the 100 customers who purchased a notebook computer last month, all but 12 purchased the same service plan. Fill in the blanks of the statement below to make the statement the most reasonable possible. Last month, customers of the store who...
Question 2 You have just purchased a new VCR to show videos to your customers. The...
Question 2 You have just purchased a new VCR to show videos to your customers. The VCR cost $500 in real dollars, and you depreciate the machine at a rate of 25% each year. You can borrow money from the bank at a nominal interest rate of 10%, or receive a 6% nominal interest rate for depositing money at the bank. The expected inflation rate in the coming year is 5%.You used the company's own funds to purchase the VCR....
An offline retail store called CoolStore have customers that are either students or not. Customers can...
An offline retail store called CoolStore have customers that are either students or not. Customers can buy a plastic bag or not. We know for a fact that: Twenty-five percent of all customers are students. Among customers that are students, eighty percent buy a plastic bag. Among customers who buy a plastic bag, fifty percent are students. Forty customers are chosen at random. The probability is one percent that the number of customers buying a plastic bag exceeds what number?
A taxpayer is a plumbing supplies store that offers credit terms to some of its customers...
A taxpayer is a plumbing supplies store that offers credit terms to some of its customers . On 30 June , accounts receivables were $ 50,000 . Of this amount , $ 20,000 was doubtful debts . No bad debts were written off in the accounting records during the income year . The director has signed a written document prepared by the company accountant on 29 June . This document authorised writing off a debt of $ 15,000 , which...
QUESTION 19 Jean guesses that 35% of customers at a local grocery store will choose the...
QUESTION 19 Jean guesses that 35% of customers at a local grocery store will choose the self check-out lane over the full service cashier lane. Throughout one day, she gathers data by observing 228 customers in the store. She finds that 74 of them choose the self check-out lane. Was her guess reasonable? Assume 95% confidence. (Hint: You may use either a confidence interval or hypothesis test to help you answer this question.) A. Yes, her guess was reasonable. B....
1. Determine the sample size to survey the customers of a store in a mall, if...
1. Determine the sample size to survey the customers of a store in a mall, if there are 500 customers out of 11,000 that are in the stores of the corporation. Assume that the confidence level is 95% and the accepted margin of error is 0.05. 2. Determine the sample size from the previous exercise, assuming that the customer sample was taken from department stores where there are a total of 200 customers.
Question 1 Alex is a young investor with high risk tolerance level and hopes to earn...
Question 1 Alex is a young investor with high risk tolerance level and hopes to earn money as fast as he can. Analyse each of the following plans and evaluate which is the most suitable plan for Alex. (1) Dollar-cost averaging (2) Constant-dollar plan (3) Constant-ratio plan (4) Variable-ratio plan Question 2 An investor estimates that next year’s net income for Hilary Pullman Hotel would be RM 8 million. The company has 0.5 million shares outstanding and decided to pay...
At two different branches of a department store, pollsters randomly sampled 100 customers at store 1...
At two different branches of a department store, pollsters randomly sampled 100 customers at store 1 and 80 customers at store 2, all on the same day. At store 1, the average amount purchased was $41.25 per customer with a sample standard deviation of $24.25. At store 2 the average amount purchased was $45.75 with a sample standard deviation of $34.76. a) Construct a 95% confidence interval for the mean amount purchased per customer in Store 1 and Store 2....
At two different branches of a department store, pollsters randomly sampled 100 customers at store 1...
At two different branches of a department store, pollsters randomly sampled 100 customers at store 1 and 80 customers at store 2, all on the same day. At store 1, the average amount purchased was $41.25 per customer with a sample standard deviation of $24.25. At store 2 the average amount purchased was $45.75 with a sample standard deviation of $34.76. a) Construct a 95% confidence interval for the mean amount purchased per customer in Store 1 and Store 2....
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT