ZuoTi.Pro
Question

Oracle 12c SQL Chapter 12 Determine which orders were shipped to the same state as order...

Oracle 12c SQL Chapter 12

Determine which orders were shipped to the same state as order 1014.

CREATE TABLE Orders
(Order# NUMBER(4),
Customer# NUMBER(4),
OrderDate DATE NOT NULL,
ShipDate DATE,
ShipStreet VARCHAR2(18),
ShipCity VARCHAR2(15),
ShipState VARCHAR2(2),
ShipZip VARCHAR2(5),
ShipCost NUMBER(4,2),
CONSTRAINT orders_order#_pk PRIMARY KEY(order#),
CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#)
REFERENCES customers(customer#));

INSERT INTO ORDERS
VALUES (1000,1005,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('02-APR-09','DD-MON-YY'),'1201 ORANGE AVE', 'SEATTLE', 'WA', '98114' , 2.00);
INSERT INTO ORDERS
VALUES (1001,1010,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'), '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314', 3.00);
INSERT INTO ORDERS
VALUES (1002,1011,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'),'58 TILA CIRCLE', 'CHICAGO', 'IL', '60605', 3.00);
INSERT INTO ORDERS
VALUES (1003,1001,TO_DATE('01-APR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'),'958 MAGNOLIA LANE', 'EASTPOINT', 'FL', '32328', 4.00);
INSERT INTO ORDERS
VALUES (1004,1020,TO_DATE('01-APR-09','DD-MON-YY'),TO_DATE('05-APR-09','DD-MON-YY'),'561 ROUNDABOUT WAY', 'TRENTON', 'NJ', '08601', NULL);
INSERT INTO ORDERS
VALUES (1005,1018,TO_DATE('01-APR-09','DD-MON-YY'),TO_DATE('02-APR-09','DD-MON-YY'), '1008 GRAND AVENUE', 'MACON', 'GA', '31206', 2.00);
INSERT INTO ORDERS
VALUES (1006,1003,TO_DATE('01-APR-09','DD-MON-YY'),TO_DATE('02-APR-09','DD-MON-YY'),'558A CAPITOL HWY.', 'TALLAHASSEE', 'FL', '32307', 2.00);
INSERT INTO ORDERS
VALUES (1007,1007,TO_DATE('02-APR-09','DD-MON-YY'),TO_DATE('04-APR-09','DD-MON-YY'), '9153 MAIN STREET', 'AUSTIN', 'TX', '78710', 7.00);
INSERT INTO ORDERS
VALUES (1008,1004,TO_DATE('02-APR-09','DD-MON-YY'),TO_DATE('03-APR-09','DD-MON-YY'), '69821 SOUTH AVENUE', 'BOISE', 'ID', '83707', 3.00);
INSERT INTO ORDERS
VALUES (1009,1005,TO_DATE('03-APR-09','DD-MON-YY'),TO_DATE('05-APR-09','DD-MON-YY'),'9 LIGHTENING RD.', 'SEATTLE', 'WA', '98110', NULL);
INSERT INTO ORDERS
VALUES (1010,1019,TO_DATE('03-APR-09','DD-MON-YY'),TO_DATE('04-APR-09','DD-MON-YY'),'384 WRONG WAY HOME', 'MORRISTOWN', 'NJ', '07960', 2.00);
INSERT INTO ORDERS
VALUES (1011,1010,TO_DATE('03-APR-09','DD-MON-YY'),TO_DATE('05-APR-09','DD-MON-YY'), '102 WEST LAFAYETTE', 'ATLANTA', 'GA', '30311', 2.00);
INSERT INTO ORDERS
VALUES (1012,1017,TO_DATE('03-APR-09','DD-MON-YY'),NULL,'1295 WINDY AVENUE', 'KALMAZOO', 'MI', '49002', 6.00);
INSERT INTO ORDERS
VALUES (1013,1014,TO_DATE('03-APR-09','DD-MON-YY'),TO_DATE('04-APR-09','DD-MON-YY'),'7618 MOUNTAIN RD.', 'CODY', 'WY', '82414', 2.00);
INSERT INTO ORDERS
VALUES (1014,1007,TO_DATE('04-APR-09','DD-MON-YY'),TO_DATE('05-APR-09','DD-MON-YY'), '9153 MAIN STREET', 'AUSTIN', 'TX', '78710', 3.00);
INSERT INTO ORDERS
VALUES (1015,1020,TO_DATE('04-APR-09','DD-MON-YY'),NULL,'557 GLITTER ST.', 'TRENTON', 'NJ', '08606', 2.00);
INSERT INTO ORDERS
VALUES (1016,1003,TO_DATE('04-APR-09','DD-MON-YY'),NULL,'9901 SEMINOLE WAY', 'TALLAHASSEE', 'FL', '32307', 2.00);
INSERT INTO ORDERS
VALUES (1017,1015,TO_DATE('04-APR-09','DD-MON-YY'),TO_DATE('05-APR-09','DD-MON-YY'),'887 HOT ASPHALT ST', 'MIAMI', 'FL', '33112', 3.00);
INSERT INTO ORDERS
VALUES (1018,1001,TO_DATE('05-APR-09','DD-MON-YY'),NULL,'95812 HIGHWAY 98', 'EASTPOINT', 'FL', '32328', NULL);
INSERT INTO ORDERS
VALUES (1019,1018,TO_DATE('05-APR-09','DD-MON-YY'),NULL, '1008 GRAND AVENUE', 'MACON', 'GA', '31206', 2.00);
INSERT INTO ORDERS
VALUES (1020,1008,TO_DATE('05-APR-09','DD-MON-YY'),NULL,'195 JAMISON LANE', 'CHEYENNE', 'WY', '82003', 2.00);

CREATE TABLE Publisher
(PubID NUMBER(2),
Name VARCHAR2(23),
Contact VARCHAR2(15),
Phone VARCHAR2(12),
CONSTRAINT publisher_pubid_pk PRIMARY KEY(pubid));

INSERT INTO PUBLISHER
VALUES(1,'PRINTING IS US','TOMMIE SEYMOUR','000-714-8321');
INSERT INTO PUBLISHER
VALUES(2,'PUBLISH OUR WAY','JANE TOMLIN','010-410-0010');
INSERT INTO PUBLISHER
VALUES(3,'AMERICAN PUBLISHING','DAVID DAVIDSON','800-555-1211');
INSERT INTO PUBLISHER
VALUES(4,'READING MATERIALS INC.','RENEE SMITH','800-555-9743');
INSERT INTO PUBLISHER
VALUES(5,'REED-N-RITE','SEBASTIAN JONES','800-555-8284');

CREATE TABLE Author
(AuthorID VARCHAR2(4),
Lname VARCHAR2(10),
Fname VARCHAR2(10),
CONSTRAINT author_authorid_pk PRIMARY KEY(authorid));

INSERT INTO AUTHOR
VALUES ('S100','SMITH', 'SAM');
INSERT INTO AUTHOR
VALUES ('J100','JONES','JANICE');
INSERT INTO AUTHOR
VALUES ('A100','AUSTIN','JAMES');
INSERT INTO AUTHOR
VALUES ('M100','MARTINEZ','SHEILA');
INSERT INTO AUTHOR
VALUES ('K100','KZOCHSKY','TAMARA');
INSERT INTO AUTHOR
VALUES ('P100','PORTER','LISA');
INSERT INTO AUTHOR
VALUES ('A105','ADAMS','JUAN');
INSERT INTO AUTHOR
VALUES ('B100','BAKER','JACK');
INSERT INTO AUTHOR
VALUES ('P105','PETERSON','TINA');
INSERT INTO AUTHOR
VALUES ('W100','WHITE','WILLIAM');
INSERT INTO AUTHOR
VALUES ('W105','WHITE','LISA');
INSERT INTO AUTHOR
VALUES ('R100','ROBINSON','ROBERT');
INSERT INTO AUTHOR
VALUES ('F100','FIELDS','OSCAR');
INSERT INTO AUTHOR
VALUES ('W110','WILKINSON','ANTHONY');

CREATE TABLE Books
(ISBN VARCHAR2(10),
Title VARCHAR2(30),
PubDate DATE,
PubID NUMBER (2),
Cost NUMBER (5,2),
Retail NUMBER (5,2),
Discount NUMBER (4,2),
Category VARCHAR2(12),
CONSTRAINT books_isbn_pk PRIMARY KEY(isbn),
CONSTRAINT books_pubid_fk FOREIGN KEY (pubid)
REFERENCES publisher (pubid));

INSERT INTO BOOKS
VALUES ('1059831198','BODYBUILD IN 10 MINUTES A DAY',TO_DATE('21-JAN-05','DD-MON-YY'),4,18.75,30.95, NULL, 'FITNESS');
INSERT INTO BOOKS
VALUES ('0401140733','REVENGE OF MICKEY',TO_DATE('14-DEC-05','DD-MON-YY'),1,14.20,22.00, NULL, 'FAMILY LIFE');
INSERT INTO BOOKS
VALUES ('4981341710','BUILDING A CAR WITH TOOTHPICKS',TO_DATE('18-MAR-06','DD-MON-YY'),2,37.80,59.95, 3.00, 'CHILDREN');
INSERT INTO BOOKS
VALUES ('8843172113','DATABASE IMPLEMENTATION',TO_DATE('04-JUN-03','DD-MON-YY'),3,31.40,55.95, NULL, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('3437212490','COOKING WITH MUSHROOMS',TO_DATE('28-FEB-04','DD-MON-YY'),4,12.50,19.95, NULL, 'COOKING');
INSERT INTO BOOKS
VALUES ('3957136468','HOLY GRAIL OF ORACLE',TO_DATE('31-DEC-05','DD-MON-YY'),3,47.25,75.95, 3.80, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('1915762492','HANDCRANKED COMPUTERS',TO_DATE('21-JAN-05','DD-MON-YY'),3,21.80,25.00, NULL, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('9959789321','E-BUSINESS THE EASY WAY',TO_DATE('01-MAR-06','DD-MON-YY'),2,37.90,54.50, NULL, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('2491748320','PAINLESS CHILD-REARING',TO_DATE('17-JUL-04','DD-MON-YY'),5,48.00,89.95, 4.50, 'FAMILY LIFE');
INSERT INTO BOOKS
VALUES ('0299282519','THE WOK WAY TO COOK',TO_DATE('11-SEP-04','DD-MON-YY'),4,19.00,28.75, NULL, 'COOKING');
INSERT INTO BOOKS
VALUES ('8117949391','BIG BEAR AND LITTLE DOVE',TO_DATE('08-NOV-05','DD-MON-YY'),5,5.32,8.95, NULL, 'CHILDREN');
INSERT INTO BOOKS
VALUES ('0132149871','HOW TO GET FASTER PIZZA',TO_DATE('11-NOV-06','DD-MON-YY'),4,17.85,29.95, 1.50, 'SELF HELP');
INSERT INTO BOOKS
VALUES ('9247381001','HOW TO MANAGE THE MANAGER',TO_DATE('09-MAY-03','DD-MON-YY'),1,15.40,31.95, NULL, 'BUSINESS');
INSERT INTO BOOKS
VALUES ('2147428890','SHORTEST POEMS',TO_DATE('01-MAY-05','DD-MON-YY'),5,21.85,39.95, NULL, 'LITERATURE');


CREATE TABLE OrderItems
(Order# NUMBER(4),
Item# NUMBER(2),
ISBN VARCHAR2(10),
Quantity NUMBER(3) NOT NULL,
PaidEach NUMBER(5,2) NOT NULL,
CONSTRAINT orderitems_pk PRIMARY KEY (order#, item#),
CONSTRAINT orderitems_order#_fk FOREIGN KEY (order#)
REFERENCES orders (order#) ,
CONSTRAINT orderitems_isbn_fk FOREIGN KEY (isbn)
REFERENCES books (isbn) ,
CONSTRAINT oderitems_quantity_ck CHECK (quantity > 0) );

INSERT INTO ORDERITEMS
VALUES (1000,1,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1001,1,'9247381001',1,31.95);
INSERT INTO ORDERITEMS
VALUES (1001,2,'2491748320',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1002,1,'8843172113',2,55.95);
INSERT INTO ORDERITEMS
VALUES (1003,1,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1003,2,'1059831198',1,30.95);
INSERT INTO ORDERITEMS
VALUES (1003,3,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1004,1,'2491748320',2,85.45);
INSERT INTO ORDERITEMS
VALUES (1005,1,'2147428890',1,39.95);
INSERT INTO ORDERITEMS
VALUES (1006,1,'9959789321',1,54.50);
INSERT INTO ORDERITEMS
VALUES (1007,1,'3957136468',3,72.15);
INSERT INTO ORDERITEMS
VALUES (1007,2,'9959789321',1,54.50);
INSERT INTO ORDERITEMS
VALUES (1007,3,'8117949391',1,8.95);
INSERT INTO ORDERITEMS
VALUES (1007,4,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1008,1,'3437212490',2,19.95);
INSERT INTO ORDERITEMS
VALUES (1009,1,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1009,2,'0401140733',1,22.00);
INSERT INTO ORDERITEMS
VALUES (1010,1,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1011,1,'2491748320',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1012,1,'8117949391',1,8.95);
INSERT INTO ORDERITEMS
VALUES (1012,2,'1915762492',2,25.00);
INSERT INTO ORDERITEMS
VALUES (1012,3,'2491748320',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1012,4,'0401140733',1,22.00);
INSERT INTO ORDERITEMS
VALUES (1013,1,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1014,1,'0401140733',2,22.00);
INSERT INTO ORDERITEMS
VALUES (1015,1,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1016,1,'2491748320',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1017,1,'8117949391',2,8.95);
INSERT INTO ORDERITEMS
VALUES (1018,1,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1018,2,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1019,1,'0401140733',1,22.00);
INSERT INTO ORDERITEMS
VALUES (1020,1,'3437212490',1,19.95);

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1 
select * from Orders
where ShipState = (select ShipState from orders where Order# = 1014);
0 0
Add a comment
Know the answer?
Add Answer to:
Oracle 12c SQL Chapter 12 Determine which orders were shipped to the same state as order...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • Oracle only database: DROP TABLE ORDERITEMS; DROP TABLE Orders; DROP TABLE BOOKAUTHOR; DRO...

    oracle only database: DROP TABLE ORDERITEMS; DROP TABLE Orders; DROP TABLE BOOKAUTHOR; DROP TABLE BOOKS; DROP TABLE PROMOTION; DROP TABLE AUTHOR; DROP TABLE CUSTOMERS; DROP TABLE PUBLISHER; CREATE TABLE Customers ( Customer# NUMBER(4), LastName VARCHAR2(10) NOT NULL, FirstName VARCHAR2(10) NOT NULL, Email VARCHAR(40), Address VARCHAR2(20), City VARCHAR2(12), State VARCHAR2(2), Zip VARCHAR2(5), Referred NUMBER(4), Region CHAR(2), CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#) ); INSERT INTO CUSTOMERS VALUES (1001, 'MORALES', 'BONITA', '[email protected]', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL, 'SE'); INSERT INTO CUSTOMERS VALUES...

  • SQL: CREATE STATEMENT For patients whose last name matches the name entered by the user, display...

    SQL: CREATE STATEMENT For patients whose last name matches the name entered by the user, display their first name, phone number, and doctor's name. Note: Try Davis. (TEXT FOR DATABASE BELOW) DROP TABLE PATIENT; DROP TABLE BILLING; DROP TABLE DOCTOR; CREATE TABLE DOCTOR( DOC_ID VARCHAR2(10) NOT NULL, DOC_NAME VARCHAR2(20), DATEHIRED DATE, SALPERMON NUMBER(8), AREA VARCHAR2(20), SUPERVISOR_ID NUMBER(8), CHGPERAPPT NUMBER(8), ANNUAL_BONUS NUMBER(8), PRIMARY KEY (DOC_ID) ); INSERT INTO DOCTOR VALUES('432', 'Harrison', to_date('05-DEC-1994','dd-mon-yyyy'), 12000, 'Pediatrics', 100, 75, 4500); INSERT INTO DOCTOR VALUES('509',...

  • SQL - create statement for query For each patient, display his or her last name, first...

    SQL - create statement for query For each patient, display his or her last name, first name, and the name of his or her doctor. For each pediatrics patient, display his or her ID and his or her doctor's ID and name. For each doctor, display the name and the name of the doctor's supervisor in alphabetic order of supervisor's name. Include column aliases for clarity. Note: Check for accuracy. For each doctor in one of the two areas entered...

  • I am using oracle sql developer to create some queries to generated reports and it is...

    I am using oracle sql developer to create some queries to generated reports and it is not working. I am not sure how to use count, group by, and order by. Help me fix my query and explain to me how you did, so can do it next time. Also, I have to convert this query to a stored procedure, so can you help me do it too? Here is my query: SELECT COUNT(GUEST.FIRSTNAME), GUEST.FIRSTNAME, GUEST.LASTNAME, GUEST.GUESTTYPE, RESERVATION.RESERVATIONDATE, GUEST.EMAIL, FROM...

  • Write a pl/sql block of code that will make a reservation for a customer for any...

    Write a pl/sql block of code that will make a reservation for a customer for any destination. The destination id, number of peolpe going and date will be entered by the user. Your code should calculate the cost and enter the data in the sales table. No destination can have more than 10 people going during the same dates. If the number is greater than 10 raise 'Sold_out' exception and print a message 'Sorry we are sold out for ths...

  • Query #2:       List the name of the project and total number of hours worked on by...

    Query #2:       List the name of the project and total number of hours worked on by all the employees on this project, also report the number of employees working on each project. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null,...

  • Query 1: Retrieve names of all the projects as well as First and Last name of...

    Query 1: Retrieve names of all the projects as well as First and Last name of managers if they are working on any of these projects. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null, Mgr_start_date date, primary key (dnumber)); CREATE...

  • Please help me on the SQL queries, thank you so much. Write a query to display the title and publisher as well as the p...

    Please help me on the SQL queries, thank you so much. Write a query to display the title and publisher as well as the publisher contact for each book using JOIN...USING clause. Write a query to show the first and last names of customers who have ordered cooking books. Use the WHERE clause to join the tables. Write a query to show the title, cost and ISBN of each book in the books table. If the book has been ordered,...

  • Provide the syntax answers for the following script, doesn't matter what table or columns. CREATE TABLE...

    Provide the syntax answers for the following script, doesn't matter what table or columns. CREATE TABLE customer (c_id NUMBER(5), c_last VARCHAR2(30), c_first VARCHAR2(30), c_mi CHAR(1), c_birthdate DATE, c_address VARCHAR2(30), c_city VARCHAR2(30), c_state CHAR(2), c_zip VARCHAR2(10), c_dphone VARCHAR2(10), c_ephone VARCHAR2(10), c_userid VARCHAR2(50), c_password VARCHAR2(15), CONSTRAINT customer_c_id_pk PRIMARY KEY (c_id)); CREATE TABLE order_source (os_id NUMBER(3), os_desc VARCHAR2(30), CONSTRAINT order_source_os_id_pk PRIMARY KEY(os_id)); CREATE TABLE orders (o_id NUMBER(8), o_date DATE, o_methpmt VARCHAR2(10), c_id NUMBER(5), os_id NUMBER(3), CONSTRAINT orders_o_id_pk PRIMARY KEY (o_id), CONSTRAINT orders_c_id_fk FOREIGN...

  • Q2. Retrieve the names of all employees from the employee table to produce output on CSV...

    Q2. Retrieve the names of all employees from the employee table to produce output on CSV format or delimited format with a common delimeter, rather than separete columns. Hint:Put the whole row into a string with a semicolon as the seperator(delimeter) between thecolumns: FORMAT:(fname;minit;lname) Example: EMPLOYEES -------------- James;E;Borg Frank;T;Wong Q3. Write a query to show the employees name from the employee table in this format: first letter of the first name, followed by a dot, a blank, and the full...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT