need answer in sql: For each order placed on October 21, 2007, list the order nu
ID: 3576955 • Letter: N
Question
need answer in sql:
For each order placed on October 21, 2007, list the order number along with the number and name of the customer that placed that order.
SQL information 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 ord.order_num, cust.customer_num, cust.customer_name from customer cust, orders ord where cust.customer_num = ord.customer_num and ord.order_date = '2007-10-21';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.