Please use SQL to do the following questions: select NVL(ORDER_QTY,0),CUSTOMER_L
ID: 3788357 • Letter: P
Question
Please use SQL to do the following questions:
select NVL(ORDER_QTY,0),CUSTOMER_LAST_NAME
from ORDER_DETAILS OD inner join ORDERS O
on OD.ORDER_ID=O.ORDER_ID
right outer join CUSTOMERS C
on O.CUSTOMER_ID=C.CUSTOMER_ID
where CUSTOMER_LAST_NAME='Blanca';
A. Modify the above query to get the total order quantities per customer (sum over all the orders for each customer). Should you use both first and last name? What would happen if you use only the first name and two customers have the same first name?
B.Now generalize the query for all customers and check how the number of returned records changes based on whether you are using only the first, only the last or both first and last names.
The tables are
CUSTOMERS
-CUSTOMER_ID
-CUSTOMER_FIRST_NAME
-CUSTOMER_LAST_NAME
-CUSTOMER_CITY
ORDER_DETAILS
-ORDER_ID
-ORDER_QTY
ORDERS
-ORDER_ID
-CUSTOMER_ID
Explanation / Answer
TO CREATE THE TABLES
CREATE TABLE CUSTOMER
(
CUSTOMER_ID NUMBER NOT NULL
, CUSTOMER_FIRSTNAME VARCHAR2(20) NOT NULL
, CUSTOMER_LASTNAME VARCHAR2(20) NOT NULL
, CITY VARCHAR2(20)
, CONSTRAINT CUSTOMER_PK PRIMARY KEY
(
CUSTOMER_ID
)
ENABLE
);
CREATE TABLE ORDERS
(
ORDER_ID VARCHAR2(20) NOT NULL
, CUSTOMER_ID NUMBER NOT NULL
, CONSTRAINT ORDERS_PK PRIMARY KEY
(
ORDER_ID
, CUSTOMER_ID
)
ENABLE
);
CREATE TABLE ORDER_DETAILS(
ORDER_ID VARCHAR2(20) NOT NULL
, QUANTITY NUMBER
, CONSTRAINT ORDER_DETAILS_PK PRIMARY KEY
(
ORDER_ID
)
ENABLE
);
TO INSERT THE ROWS INTO THE TABLES
INSERT INTO "SCOTT"."CUSTOMER" (CUSTOMER_ID, CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME, CITY) VALUES ('1', 'KOTI', ' B', 'HYDERABAD')
INSERT INTO "SCOTT"."CUSTOMER" (CUSTOMER_ID, CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME, CITY) VALUES ('2', 'RAMESH', 'C', 'GUNTUR')
INSERT INTO "SCOTT"."CUSTOMER" (CUSTOMER_ID, CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME, CITY) VALUES ('3', 'RAJU', 'D', 'NEW YORK')
Commit Successful
INSERT INTO "SCOTT"."ORDER_DETAILS" (ORDER_ID, QUANTITY) VALUES ('1', '25')
INSERT INTO "SCOTT"."ORDER_DETAILS" (ORDER_ID, QUANTITY) VALUES ('2', '1')
INSERT INTO "SCOTT"."ORDER_DETAILS" (ORDER_ID) VALUES ('3')
Commit Successful
INSERT INTO "SCOTT"."ORDERS" (ORDER_ID, CUSTOMER_ID) VALUES ('1', '2')
INSERT INTO "SCOTT"."ORDERS" (ORDER_ID, CUSTOMER_ID) VALUES ('2', '1')
INSERT INTO "SCOTT"."ORDERS" (ORDER_ID, CUSTOMER_ID) VALUES ('1', '3')
Commit Successful
INSERT INTO "SCOTT"."CUSTOMER" (CUSTOMER_ID, CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME, CITY) VALUES ('4', 'RAJU', 'BLANCA', 'CANADA')
INSERT INTO "SCOTT"."CUSTOMER" (CUSTOMER_ID, CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME, CITY) VALUES ('5', 'SANDY', 'BLANCA', 'SECUNDERABAD')
INSERT INTO "SCOTT"."CUSTOMER" (CUSTOMER_ID, CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME, CITY) VALUES ('6', 'DAVID', 'E', 'PIDUGURALLA')
Commit Successful
INSERT INTO "SCOTT"."ORDERS" (ORDER_ID, CUSTOMER_ID) VALUES ('1', '4')
INSERT INTO "SCOTT"."ORDERS" (ORDER_ID, CUSTOMER_ID) VALUES ('2', '5')
Commit Successful
4 ANS)
SELECT NVL(QUANTITY,0),
CUSTOMER_LASTNAME,
CUSTOMER_FIRSTNAME
FROM ORDER_DETAILS OD
INNER JOIN ORDERS O
ON OD.ORDER_ID=O.ORDER_ID
RIGHT OUTER JOIN CUSTOMER C
ON O.CUSTOMER_ID =C.CUSTOMER_ID
WHERE CUSTOMER_FIRSTNAME='RAJU'
AND CUSTOMER_LASTNAME ='BLANCA';
SOL)IF YOU USE FIRSTNAME TO GET THE QUANTITY AND LASTNAME OUT OF THIS QUERY YOU MAY A GET INCORRECT RESULTS.IF TWO CUSTOMER FIRSTNAME IS SAME BUT LASTNAME IS NOT SAME THAT MEANS ALL FIRSTNAME CUSTOMER QUANITY DETAILS YOU GET OUT OF THAT SO DON'T USE FIRSTNAME TO GET THE QUERY
SAME YOU GET THE WHILE USING LASTNAME ALSO
USE BOTH AND ALTER TABLE FIRSTNAME AND LASTNAME BOTH AS UNIQUE CONSTRAINT
B ANS)
select NVL(ORDER_QTY,0),CUSTOMER_LAST_NAME
from ORDER_DETAILS OD inner join ORDERS O
on OD.ORDER_ID=O.ORDER_ID
right outer join CUSTOMERS C
on O.CUSTOMER_ID=C.CUSTOMER_ID
where CUSTOMER_LAST_NAME='Blanca';
THE NO OF CUSTOMERS WHOSE LASTNAME IS BLANCA ALL CUSTOMERS QUANITY RECORDS WILL BE DISPLAYED
select NVL(ORDER_QTY,0),CUSTOMER_FIRSTNAME
from ORDER_DETAILS OD inner join ORDERS O
on OD.ORDER_ID=O.ORDER_ID
right outer join CUSTOMERS C
on O.CUSTOMER_ID=C.CUSTOMER_ID
where CUSTOMER_FIRSTNAME='SMITH';
THE NO OF CUSTOMERS WHOSE FIRSTNAME IS SMITH AND THEIR ORDERED QUANTITY WILL BE DISPLAYED
select NVL(ORDER_QTY,0),CUSTOMER_LASTNAME,CUSOTMER_FIRSTNAME
from ORDER_DETAILS OD inner join ORDERS O
on OD.ORDER_ID=O.ORDER_ID
right outer join CUSTOMERS C
on O.CUSTOMER_ID=C.CUSTOMER_ID
where CUSTOMER_LASTNAME='Blanca' AND CUSTOMER_FIRSTNAME='SMITH';
THE NO OF CUSTOMERS MATCHING WITH THEIR FIRSTNAME AND LASTNAME ABOVE MENTIONED IN THE QUERY ALL THIER QUANTITIES DISPLAYED.
THE NO OF RECORD CHANGES DEPENDS UPON BASED ON THE TEST DATE INSERTED IN THE TABLES