Answer needs to be in SQL: Question: For each part, list the part number, descri

Question

Answer needs to be in SQL:

Question:

For each part, list the part number, description, units   on hand, order number, and number of units ordered. All parts should be listed in the results, and the parts    that are currently not on order should display the order number and the number of units ordered as    blanks. Order the results by part number.   

Database:

Create Database lesson5;

Use lesson5;

/*FOUR TABLES*/

CREATE TABLE REP
(REP_NUM CHAR(2) PRIMARY KEY,
LAST_NAME CHAR(15) NOT NULL,
FIRST_NAME CHAR(15) NOT NULL,
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(5),
COMMISSION DECIMAL(7,2),
RATE DECIMAL(3,2) );

CREATE TABLE CUSTOMER
(CUSTOMER_NUM CHAR(3) PRIMARY KEY,
CUSTOMER_NAME CHAR(35) NOT NULL,
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
ZIP CHAR(5),
BALANCE DECIMAL(8,2),
CREDIT_LIMIT DECIMAL(8,2),
REP_NUM CHAR(2) );

CREATE TABLE ORDERS
(ORDER_NUM CHAR(5) PRIMARY KEY,
ORDER_DATE DATE,
CUSTOMER_NUM CHAR(3) );

CREATE TABLE PART
   (PART_NUM CHAR(4) PRIMARY KEY,
       DESCRIPTION CHAR(15),
       ON_HAND DECIMAL(4,0) NOT NULL,
       CLASS CHAR(2),
       WAREHOUSE CHAR(1),
       PRICE DECIMAL(6,2) );

CREATE TABLE ORDER_LINE
   (ORDER_NUM CHAR(5),
       PART_NUM CHAR(4),
       NUM_ORDERED DECIMAL(3,0),
       QUOTED_PRICE DECIMAL(6,2),
       PRIMARY KEY (ORDER_NUM, PART_NUM) );

INSERT INTO REP
VALUES
('20','Kaiser','Valerie','624 Randall', 'Grove', 'FL','33321',20542.50,0.05);

INSERT INTO REP
VALUES
('35','Hull','Richard','532 Jackson','Sheldon', 'FL','33553',39216.00,0.07);

INSERT INTO REP
VALUES
('65','Perez','Juan','1626 Taylor','Fillmore', 'FL','33336',23487.00,0.05);

INSERT INTO CUSTOMER
VALUES
('148','Al''s Appliance and Sport','2837 Greenway','Fillmore','FL','33336',6550.00,7500.00,'20');
INSERT INTO CUSTOMER
VALUES
('282','Brookings Direct','3827 Devon','Grove','FL','33321',431.50,10000.00,'35');
INSERT INTO CUSTOMER
VALUES
('356','Ferguson''s','382 Wildwood','Northfield','FL','33146',5785.00,7500.00,'65');
INSERT INTO CUSTOMER
VALUES
('408','The Everything Shop','1828 Raven','Crystal','FL','33503',5285.25,5000.00,'35');
INSERT INTO CUSTOMER
VALUES
('462','Bargains Galore','3829 Central','Grove','FL','33321',3412.00,10000.00,'65');

INSERT INTO CUSTOMER
VALUES
('524','Kline''s','838 Ridgeland','Fillmore','FL','33336',12762.00,15000.00,'20');
INSERT INTO CUSTOMER
VALUES
('608','Johnson''s Department Store','372 Oxford','Sheldon','FL','33553',2106.00,10000.00,'65');
INSERT INTO CUSTOMER
VALUES
('687','Lee''s Sport and Appliance','282 Evergreen','Altonville','FL','32543',2851.00,5000.00,'35');
INSERT INTO CUSTOMER
VALUES
('725','Deerfield''s Four Seasons','282 Columbia','Sheldon','FL','33553',248.00,7500.00,'35');
INSERT INTO CUSTOMER
VALUES
('842','All Season','28 Lakeview','Grove','FL','33321',8221.00,7500.00,'20');

INSERT INTO ORDERS
VALUES
('21608','2007-10-20','148');
INSERT INTO ORDERS
VALUES
('21610','2007-10-20','356');
INSERT INTO ORDERS
VALUES
('21613','2007-10-21','408');
INSERT INTO ORDERS
VALUES
('21614','2007-10-21','282');
INSERT INTO ORDERS
VALUES
('21617','2007-10-23','608');
INSERT INTO ORDERS
VALUES
('21619','2007-10-23','148');
INSERT INTO ORDERS
VALUES
('21623','2007-10-23','608');

INSERT INTO PART
VALUES
('AT94','Iron',50,'HW','3',24.95);
INSERT INTO PART
VALUES
('BV06','Home Gym',45,'SG','2',794.95);
INSERT INTO PART
VALUES
('CD52','Microwave Oven',32,'AP','1',165.00);
INSERT INTO PART
VALUES
('DL71','Cordless Drill',21,'HW','3',129.95);
INSERT INTO PART
VALUES
('DR93','Gas Range',8,'AP','2',495.00);
INSERT INTO PART
VALUES
('DW11','Washer',12,'AP','3',399.99);
INSERT INTO PART
VALUES
('FD21','Stand Mixer',22,'HW','3',159.95);
INSERT INTO PART
VALUES
('KL62','Dryer',12,'AP','1',349.95);
INSERT INTO PART
VALUES
('KT03','Dishwasher',8,'AP','3',595.00);
INSERT INTO PART
VALUES
('KV29','Treadmill',9,'SG','2',1390.00);

INSERT INTO ORDER_LINE
VALUES ('21608','AT94',11,21.95);

INSERT INTO ORDER_LINE
VALUES ('21610','DR93',1,495.00);

INSERT INTO ORDER_LINE
VALUES ('21610','DW11',1,399.99);

INSERT INTO ORDER_LINE
VALUES ('21613','KL62',4,329.95);

INSERT INTO ORDER_LINE
VALUES ('21614','KT03',2,595.00);

INSERT INTO ORDER_LINE
VALUES ('21617','BV06',2,794.95);

INSERT INTO ORDER_LINE
VALUES ('21617','CD52',4,150.00);

INSERT INTO ORDER_LINE
VALUES ('21619','DR93',1,495.00);

INSERT INTO ORDER_LINE
VALUES ('21623','KV29',2,1290.00);

DESC REP;

DESC ORDERS;

DESC CUSTOMER;

DESC PART;

/*Joining two tables */
SELECT R.rep_num, last_name, first_name, C.customer_num, customer_name
FROM REP R, CUSTOMER C
WHERE R.rep_num = C.rep_num
ORDER BY rep_num;

/*Joining a table to self */

SELECT F.customer_num, F.customer_name, S.customer_num, S.customer_name, F.City
FROM CUSTOMER F, CUSTOMER S
WHERE F.city = S.city
AND F.customer_num < S.customer_num
ORDER BY F.customer_num, S.customer_num;

SELECT E.employee_num, E.first_name, E.last_name,M.employee_num AS MGR_NUM, M.first_name AS MGR_FIRST, M.last_name AS MGR_LAST
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.mgr_emp_num = M.employee_num
ORDER BY M.employee_num, E.employee_num;

/*SET operations */

/*Union */
SELECT customer_num, customer_name
FROM CUSTOMER
WHERE rep_num = '65'
UNION
SELECT CUSTOMER.customer_num, customer_name
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.customer_num = ORDERS.customer_num;

/*Intersection */

SELECT customer_num, customer_name
FROM CUSTOMER
WHERE rep_num = '65'
AND customer_num IN (SELECT customer_num
               FROM ORDERS);

/*Difference */
SELECT customer_num, customer_name
FROM CUSTOMER
WHERE rep_num = '65'
AND customer_num NOT IN (SELECT customer_num
               FROM ORDERS);

/* INNER JOIN */
SELECT CUSTOMER.customer_num, customer_name, order_num, order_date
FROM CUSTOMER
INNER JOIN ORDERS
ON CUSTOMER.customer_num = ORDERS.customer_num;

/*The INNER JOIN yields the same result as a multi table query with a WHERE clause. */

SELECT CUSTOMER.customer_num, customer_name, order_num, order_date
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.customer_num = ORDERS.customer_num;

/* LEFT OUTER JOIN */

SELECT CUSTOMER.customer_num, customer_name, order_num, order_date
FROM CUSTOMER
LEFT JOIN ORDERS
ON CUSTOMER.customer_num = ORDERS.customer_num
ORDER BY CUSTOMER.customer_num;

/* RIGHT OUTER JOIN */
SELECT CUSTOMER.customer_num, customer_name, order_num, order_date
FROM CUSTOMER
RIGHT JOIN ORDERS
ON CUSTOMER.customer_num = ORDERS.customer_num
ORDER BY CUSTOMER.customer_num;

/* FULL OUTER JOIN */

SELECT CUSTOMER.customer_num, customer_name, order_num, order_date
FROM CUSTOMER
LEFT JOIN ORDERS ON CUSTOMER.customer_num = ORDERS.customer_num

UNION

SELECT CUSTOMER.customer_num, customer_name, order_num, order_date
FROM CUSTOMER
RIGHT JOIN ORDERS ON CUSTOMER.customer_num = ORDERS.customer_num;

/* PRODUCT */

SELECT CUSTOMER.customer_num,customer_name,order_num, order_date
FROM CUSTOMER, ORDERS;

/* Referential Integrity */
ALTER TABLE REP
ADD PRIMARY KEY (REP_NUM);

ALTER TABLE ORDERS
ADD FOREIGN KEY (Customer_Num) REFERENCES
               CUSTOMER (Customer_num);

INSERT INTO ORDERS
VALUES
('21620','2007-10-23','500');

Explanation / Answer

select PART.PART_NUM, PART.DESCRIPTION, PART.ON_HAND, ORDER_LINE.ORDER_NUM, ORDER_LINE.NUM_ORDERED from PART left JOIN ORDER_LINE ON PART.PART_NUM = ORDER_LINE.PART_NUM order by PART.PART_NUM;

left join will include all parts even if they are not ordered

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.