Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

Need answer in SQL: For each part, list the part number, description, units on h

ID: 3576961 • Letter: N

Question

Need answer in SQL:

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.

SQL below:

Create Database premiere;

Use premiere;

/*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) );


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

DESC REP;

DESC ORDERS;

DESC CUSTOMER;

DESC PART;

/* SORTING*/
SELECT customer_num, customer_name, balance
   FROM customer
   ORDER BY balance;

SELECT customer_num, customer_name, credit_limit
   FROM customer
   ORDER BY credit_limit DESC, customer_name;
  
/*FUNCTIONS*/
/*COUNT*/
SELECT COUNT(*)
   FROM part
WHERE class='HW';

SELECT * FROM PART;

SELECT COUNT(PART_NUM)
   FROM part
   WHERE class='HW';


/*SUM*/
SELECT COUNT(*), SUM(BALANCE)
   FROM customer;
  
SELECT * FROM customer;
  
SELECT COUNT(*), SUM(BALANCE)
   FROM customer
WHERE credit_limit=7500.00;


/*DISTINCT*/

SELECT customer_num
   FROM orders;

SELECT DISTINCT(customer_num)
   FROM orders;

  
SELECT COUNT(customer_num)
   FROM orders;
  
SELECT COUNT(DISTINCT(customer_num))
   FROM orders;


/*CHARACTER FUNCTIONS*/

/*UP*/
SELECT Rep_Num, UPPER(Last_Name)
   FROM REP;  

SELECT Rep_Num, LOWER(Last_Name)
   FROM REP;  

/*CONCAT*/
SELECT Rep_Num, CONCAT(First_Name, ' ', Last_Name) AS Full_Name
   FROM REP;  

/*REPLACE*/
SELECT Rep_Num, REPLACE(First_Name,'a','z')
   FROM REP;  

/*SUBSTRING*/
SELECT Part_Num, SUBSTRING(Part_Num,1,2) AS Part_T
   FROM PART;  
  
/*RPAD*/
SELECT Part_Num, RPAD(Description,30,'.')
   FROM PART;
  
/*other string function */
SELECT LTRIM(' barbar');

SELECT INSTR('foobarbar', 'bar');

SELECT INSTR('xbar', 'foobar');

SELECT LENGTH('text');

SELECT IFNULL(1,0);
  
SELECT IFNULL(NULL,10);


/*NUMERIC FUNCTIONS*/
/*ROUND AND FLOOR*/
SELECT Part_Num, Price, ROUND(Price,0) AS Rounded_Price
   FROM PART;
  
SELECT Part_Num, Price, FLOOR(Price) AS Floored_Price
   FROM PART;
  
SELECT ABS(2);

SELECT ABS(-32);

SELECT SQRT(4);

SELECT SQRT(20);

SELECT SQRT(-16);

SELECT SIGN(-32);

SELECT SIGN(0);

SELECT SIGN(234);

SELECT POW(2,2);

SELECT POW(2,-2);

/* SQL SCRIPT PART B */
/*DATE FUNCTIONS*/

DESC ORDERS;

SELECT Order_Num, Order_Date, DAYNAME(Order_Date) AS Dayname, MONTHNAME(Order_Date) AS Monthnam,
DAYOFYEAR(Order_Date) AS Dayofyear
   FROM ORDERS;

SELECT Order_Num, Order_Date, DATE_ADD(Order_Date, INTERVAL 2 MONTH) AS Future_Date
   FROM ORDERS;
  
/* IS NULL */
SELECT customer_num, customer_name
   FROM customer
   WHERE street IS NULL;

SELECT customer_num, customer_name
   FROM customer
   WHERE street IS NOT NULL;

  
/* GROUPING*/
SELECT rep_num, AVG(balance)
   FROM customer
   GROUP BY rep_num;
  
/* HAVING*/
SELECT rep_num, AVG(balance)
   FROM customer
   GROUP BY rep_num
   HAVING COUNT(*) < 4
   ORDER BY rep_num;

SELECT credit_limit, COUNT(*)
   FROM customer
   WHERE rep_num = '20'
   GROUP BY credit_limit
   HAVING COUNT(*) > 1;
  
/*Nesting Queries */

SELECT part_num
   FROM part
   WHERE class= 'AP';

SELECT description, class
   FROM part
   WHERE part_num IN ('CD52','DR93','DW11','KL62','KT03');

SELECT description, class
   FROM part
   WHERE part_num IN
   (SELECT part_num
       FROM part
       WHERE class= 'AP' );


SELECT customer_num, customer_name, balance
   FROM customer
   WHERE balance > (SELECT AVG(balance)
                   FROM customer);
  
  
SELECT AVG(balance) FROM customer;
  
  
/* UPDATING DATA */

CREATE TABLE LEVEL1_CUSTOMER LIKE CUSTOMER;

INSERT INTO LEVEL1_CUSTOMER
SELECT customer_num, customer_name, street, city, state, zip, balance, credit_limit, rep_num  
FROM CUSTOMER
WHERE credit_limit = 7500;

few columns?

select * from level1_customer;


SET SQL_SAFE_UPDATES = 0;

UPDATE LEVEL1_CUSTOMER
SET customer_name = 'All Season Sport'
WHERE customer_num = '842' ;

UPDATE LEVEL1_CUSTOMER
SET credit_limit = 8000
WHERE rep_num = '20'
AND balance < credit_limit;

UPDATE LEVEL1_CUSTOMER
SET balance = NULL
WHERE customer_num = '725' ;

DELETE FROM LEVEL1_CUSTOMER
WHERE customer_num = '895' ;


/*Changing a Table’s Structure */

ALTER TABLE LEVEL1_CUSTOMER
ADD COLUMN customer_type CHAR(1);

UPDATE LEVEL1_CUSTOMER
SET customer_type = 'R';

UPDATE LEVEL1_CUSTOMER
SET customer_type = 'S'
WHERE customer_num = '842' ;

UPDATE LEVEL1_CUSTOMER
SET customer_type = 'D'
WHERE customer_num = '148' ;

ALTER TABLE LEVEL1_CUSTOMER
MODIFY customer_name CHAR(50);

ALTER TABLE LEVEL1_CUSTOMER
MODIFY credit_limit DECIMAL(8,2) NOT NULL;

ALTER TABLE LEVEL1_CUSTOMER
DROP COLUMN customer_type;

/* COMMIT & ROLLBACK */

UPDATE LEVEL1_CUSTOMER
SET customer_type = 'D'
WHERE customer_num = '148' ;
COMMIT;

SET AUTOCOMMIT=0;

UPDATE LEVEL1_CUSTOMER
SET customer_type = 'D'
WHERE customer_num = '148';
ROLLBACK;

UPDATE LEVEL1_CUSTOMER
SET customer_name = 'Smith Sport'
WHERE customer_num = '356';

DELETE FROM LEVEL1_CUSTOMER
WHERE customer_num = '842';

SELECT * FROM LEVEL1_CUSTOMER;

ROLLBACK;

SELECT * FROM LEVEL1_CUSTOMER;

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

Need all answers for number 1 and 4 please Lab Questions 1. Why does the o-isome
Question #569466
Need all answers for number 1 and 4 please Lab Questions 1. Why does the o-isome
Question #1089347
Need all answers please 12. Which is in the correct order from the outside of yo
Question #3489291
Need all answers please 14) Bandura\'s \"Bobo doll\" experiments were intended t
Question #3484904
Need all answers please 32) What is one cause of forgetting? Not encoding the in
Question #3484907
Need all answers please Need all answers please 0 In cell mediated immunity 8 ce
Question #3519105
Need all answers please Need all answers please 10 The kidney receives about a 1
Question #3516169
Need all answers please The observable universe is made up of _____ and _____. A
Question #1628418
Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
Chat Now And Get Quote

Navigate

Previous
Need answer in SQL: Find the number and name of each customer that did not place
Next
Need answer in c++ programming using classes The Fibonacci sequence is the serie

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