Question

SQL

Write the queries necessary to obtain the required information

1) Use sub query and “in” keyword to print the salesreps (ids) who have taken order for the companies ‘Zetacorp’ or ‘JCP Inc.’ . Duplicate rows are not allowed

2) Use sub query to find the id and the name of every sales rep that represents at least one customer with a credit limit of greater than $5000.

3) Use sub query and keyword “exists” to list the id and the name of the salesreps in which some customers have orders some products in their hiredate.

4) List all the products (Mfr_ID and Product_ID) that have never been sold. Use the ‘Exists’ clause.

Below are the provided tables:

drop table salesreps;
create table salesreps
(empl_num number(3,0) primary key,
name varchar2(15) not null,
age number(3,0),
rep_office number(2,0),
title varchar2(10),
hire_date varchar2(10) not null,
manager number(3,0),
quota number(10,2),
sales number(10,2) not null);
insert into salesreps values (105,'Bill Adams',37,13,'Sales Rep','12-FEB-88',104,350000,367911);
insert into salesreps values (109,'Mary Jones',31,11,'Sales Rep','12-OCT-89',106,300000,392725);
insert into salesreps values (102,'Sue Smith',48,21,'Sales Rep','10-DEC-86',108,350000,474050);
insert into salesreps values (106,'Sam Clark',52,11,'VP Sales','14-JUN-88',NULL,275000,299912);
insert into salesreps values (104,'Bob Smith',33,12,'Sales Mgr','19-MAY-87',106,200000,142594);
insert into salesreps values (101,'Dan Roberts',45,12,'Sales Rep','20-OCT-86',104,300000,305673);
insert into salesreps values (110,'Tom Synder',41,NULL,'Sales Rep','13-JAN-90',101,NULL,75985);
insert into salesreps values (108,'Larry Fitch',62,21,'Sales Mgr','12-OCT-89',106,350000,361865);
insert into salesreps values (103,'Paul Cruz',29,12,'Sales Rep','01-MAR-87',104,275000,286775);
insert into salesreps values (107,'Nacy Angelli',49,22,'Sales Rep','14-NOV-88',108,300000,186042);

drop table customers;
CREATE TABLE CUSTOMERS
(CUST_NUM NUMBER(4,0) PRIMARY KEY,
COMPANY VARCHAR(20) NOT NULL,
CUST_REP NUMBER(3,0),
CREDIT_LIMIT NUMBER(10,2)
);
insert into customers values(2111,'JCP Inc.',103,50000);
insert into customers values(2102,'First Corp.',101,65000);
insert into customers values(2103,'Acme Mfg.',105,50000);
insert into customers values(2123,'Carter and Sons',102,40000);
insert into customers values(2107,'Ace International',110,35000);
insert into customers values(2115,'Smithson Corp.',101,20000);
insert into customers values(2101,'Jones Mfg.',106,65000);
insert into customers values(2112,'Zetacorp',108,50000);
insert into customers values(2121,'QMA Assoc.',103,45000);
insert into customers values(2114,'Orion Corp.',102,20000);
insert into customers values(2124,'Peter Brothers',107,40000);
insert into customers values(2108,'Holm and Landis',109,55000);
insert into customers values(2117,'J.P. Sinclair',106,35000);
insert into customers values(2122,'Three-Way Lines',105,30000);
insert into customers values(2120,'Rico Enterprises',102,50000);
insert into customers values(2106,'Fred Lewis Corp.',102,65000);
insert into customers values(2119,'Solomon Inc.',109,25000);
insert into customers values(2118,'Midwest Systems',108,60000);
insert into customers values(2113,'Ian and Schmidt',104,20000);
insert into customers values(2109,'Chen Associates',103,25000);
insert into customers values(2105,'AAA Investments',101,45000);

drop table offices;
create table offices
(office number(2,0) primary key,
city varchar2(15) not null,
region varchar2(10) not null,
mgr number(3,0),
target number(10,2),
sales number(10,2) not null);
insert into offices values(22,'Denver','Western',108,300000,186042);
insert into offices values(11,'New York','Eastern',106,575000,692637);
insert into offices values(12,'Chicago','Eastern',104,800000,735042);
insert into offices values(13,'Atlanta','Eastern',105,350000,367911);
insert into offices values(21,'Los Angeles','Western',108,725000,835915);

DROP TABLE ORDERS;
CREATE TABLE ORDERS
(ORDER_NUM NUMBER(6,0) PRIMARY KEY,
ORDER_DATE Date NOT NULL,
CUST NUMBER(4,0) NOT NULL,
REP NUMBER(3,0),
MFR VARCHAR2(3) NOT NULL,
PRODUCT VARCHAR2(5) NOT NULL,
QTY NUMBER(4,0) NOT NULL,
AMOUNT NUMBER(9,2) NOT NULL
);
INSERT INTO ORDERS VALUES (112961,'17-DEC-89',2117,106,'REI','2A44L',7,31500);
INSERT INTO ORDERS VALUES (113012,'11-JAN-90',2111,105,'ACI','41003',35,3745);
INSERT INTO ORDERS VALUES (112989,'03-JAN-90',2101,106,'FEA','114',6,1458);
INSERT INTO ORDERS VALUES (113051,'10-FEB-90',2118,108,'QSA','K47',4,1420);
INSERT INTO ORDERS VALUES (112968,'12-OCT-89',2102,101,'ACI','41004',34,3978);
INSERT INTO ORDERS VALUES (113036,'30-JAN-90',2107,110,'ACI','4100Z',9,22500);
INSERT INTO ORDERS VALUES (113045,'02-FEB-90',2112,108,'REI','2A44R',10,45000);
INSERT INTO ORDERS VALUES (112963,'17-DEC-89',2103,105,'ACI','41004',28,3276);
INSERT INTO ORDERS VALUES (113013,'14-JAN-90',2118,108,'BIC','41003',1,652);
INSERT INTO ORDERS VALUES (113058,'23-FEB-90',2108,109,'FEA','112',10,1480);
INSERT INTO ORDERS VALUES (112997,'08-JAN-90',2124,107,'BIC','41003',1,652);
INSERT INTO ORDERS VALUES (112983,'27-DEC-89',2103,105,'ACI','41004',6,702);
INSERT INTO ORDERS VALUES (113024,'20-JAN-90',2114,108,'QSA','XK47',20,7100);
INSERT INTO ORDERS VALUES (113062,'24-FEB-90',2124,107,'FEA','114',10,2430);
INSERT INTO ORDERS VALUES (112979,'12-OCT-89',2114,102,'ACI','4100Z',6,15000);
INSERT INTO ORDERS VALUES (113027,'22-JAN-90',2103,105,'ACI','41002',54,4104);
INSERT INTO ORDERS VALUES (113007,'08-JAN-90',2112,108,'IMM','773C',3,2925);
INSERT INTO ORDERS VALUES (113069,'02-MAR-90',2109,107,'IMM','775C',22,31350);
INSERT INTO ORDERS VALUES (113034,'29-JAN-90',2107,110,'REI','2A45C',8,632);
INSERT INTO ORDERS VALUES (112992,'04-NOV-89',2118,108,'ACI','41002',10,760);
INSERT INTO ORDERS VALUES (112975,'12-OCT-89',2111,103,'REI','2A44G',6,2100);
INSERT INTO ORDERS VALUES (113055,'15-FEB-90',2108,101,'ACI','4100X',6,150);
INSERT INTO ORDERS VALUES (113048,'10-FEB-90',2120,102,'IMM','779C',2,3750);
INSERT INTO ORDERS VALUES (112993,'04-JAN-89',2106,102,'REI','2A45C',24,1896);
INSERT INTO ORDERS VALUES (113065,'27-FEB-90',2106,102,'QSA','XK47',6,2130);
INSERT INTO ORDERS VALUES (113003,'25-JAN-90',2108,109,'IMM','779C',3,5625);
INSERT INTO ORDERS VALUES (113049,'10-FEB-90',2118,108,'QSA','XK47',2,776);
INSERT INTO ORDERS VALUES (112987,'31-DEC-89',2103,105,'ACI','4100Y',11,27500);
INSERT INTO ORDERS VALUES (113057,'18-FEB-90',2111,103,'ACI','4100X',24,600);
INSERT INTO ORDERS VALUES (113042,'02-FEB-90',2113,101,'REI','2A44R',5,22500);

DROP TABLE PRODUCTS;
CREATE TABLE PRODUCTS
(MFR_ID VARCHAR2(3) NOT NULL,
PRODUCT_ID VARCHAR2(5) NOT NULL,
DESCRIPTION VARCHAR2(20) NOT NULL,
PRICE NUMBER(9,2) NOT NULL,
QTY_ON_HAND NUMBER(5,0) NOT NULL);
INSERT INTO PRODUCTS VALUES('REI','2A45C','RATCHET LINK',79,210);
INSERT INTO PRODUCTS VALUES('ACI','4100Y','WIDGET REMOVER',2750,25);
INSERT INTO PRODUCTS VALUES('QSA','XK47','REDUCER',355,38);
INSERT INTO PRODUCTS VALUES('BIC','41672','PLATE',180,0);
INSERT INTO PRODUCTS VALUES('IMM','779C','900-LB BRACE',1875,9);
INSERT INTO PRODUCTS VALUES('ACI','41003','SIZE 3 WIDGET',107,207);
INSERT INTO PRODUCTS VALUES('ACI','41004','SIZE 4 WIDGET',117,139);
INSERT INTO PRODUCTS VALUES('BIC','41003','HANDLE',652,3);
INSERT INTO PRODUCTS VALUES('IMM','887P','BRACE PIN',250,24);
INSERT INTO PRODUCTS VALUES('QSA','XK48','REDUCER',134,203);
INSERT INTO PRODUCTS VALUES('REI','2A44L','LEFT HINGE',4500,12);
INSERT INTO PRODUCTS VALUES('FEA','112','HOUSING',148,115);
INSERT INTO PRODUCTS VALUES('IMM','887F','BRACE HOLDER',54,223);
INSERT INTO PRODUCTS VALUES('BIC','41089','RETAINER',225,78);
INSERT INTO PRODUCTS VALUES('ACI','41001','SIZE 1 WIDGET',55,277);
INSERT INTO PRODUCTS VALUES('IMM','775C','500-LB BRACE',1425,5);
INSERT INTO PRODUCTS VALUES('ACI','4100Z','WIDGET INSTALLER',2500,28);
INSERT INTO PRODUCTS VALUES('QSA','XK48A','REDUCER',177,37);
INSERT INTO PRODUCTS VALUES('ACI','41002','SIZE 2 WIDGET',76,167);
INSERT INTO PRODUCTS VALUES('REI','2A44R','RIGHT HINGE',4500,12);
INSERT INTO PRODUCTS VALUES('IMM','773C','300-LB BRACE',975,28);
INSERT INTO PRODUCTS VALUES('ACI','4100X','WIDGET ADJUSTER',25,37);
INSERT INTO PRODUCTS VALUES('FEA','114','MOTOR MOUNT',243,15);
INSERT INTO PRODUCTS VALUES('IMM','887X','BRACE RETAINER',475,32);
INSERT INTO PRODUCTS VALUES('REI','2A44G','HINGE PIN',350,14);

Answer 1

1) the salesreps (ids) who have taken order for the companies ‘Zetacorp’ or ‘JCP Inc.’ :

select distinct(REP) as salesreps from ORDERS where REP in
(select CUST_REP from customers where COMPANY in ('JCP Inc.','Zetacorp'));

Query result :

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

2) the id and the name of every sales rep that represents at least one customer with a credit limit of greater than $5000 :

select EMPL_NUM,NAME from salesreps where EMPL_NUM in
(select CUST_REP from CUSTOMERS where CREDIT_LIMIT>5000);

Query result :

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

3) List the id and the name of the salesreps in which some customers have orders some products in their hiredate :

select EMPL_NUM,NAME from salesreps where exists
(select REP from orders where salesreps.hire_date =orders.ORDER_DATE);

Query result :

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

4) List all the products (Mfr_ID and Product_ID) that have never been sold :

select MFR_ID,PRODUCT_ID from products where not exists
(select PRODUCT from orders where orders.PRODUCT=products.PRODUCT_ID);

Query output :