I was wondering if there is something wrong with the query because it gives me the following error:
Q1. ORA-00942: table or view does not exist
Q2. no data found
- Question and queries:
1) Create a list that displays the title of each book and the name and phone number of the contact at the publisher's office for reordering each book.
[the screenShot1 is what I have written.]
SELECT title, contact, phone
FROM Books b JOIN Publisher pb
ON b.pubid = pb.pubid;
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#)
);
CREATE TABLE Customers (
Customer# NUMBER(4),
LastName VARCHAR2(10) NOT NULL,
FirstName VARCHAR2(10) NOT NULL,
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#),
CONSTRAINT customers_region_ck CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E'))
);
2) Determine which orders haven’t yet shipped and the name of the customer who placed the order. Sort the results by the date on which the order was placed.
SELECT Order#, (firstname || ' ' || lastname) name
FROM orders o JOIN customers c
ON o.customer# = c.customer#
AND o.shipdate IS NULL;
Extracted text: -- Answer to Q1. SELECT title, contact, phone FROM Books b JOIN Publisher pb ON b.pubid = pb.pubid; 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#) ); CREATE TABLE Customers ( Customer# NUMBER (4), LastName VARCHAR2 (10) NOT NULL, FirstName VARCHAR2 (10) NOT NULL, 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#), CONSTRAINT customers_region_ck CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')) ); Answer to Q2. || lastname) name SELECT Order#, (firstname || FROM orders o JOIN customers c ON o.customer# = c.customer# AND o.shipdate IS NULL;
Extracted text: 1 CREATE TABLE warehouses ( wh_id NUMBER(2), location VARCHAR2(12) 2 3. 4. ); 5 6. INSERT INT0 warehouses VALUES (10, 'Boston'); 7 INSERT INTO warehouses VALUES (20, Norfolk'); INSERT INTO warehouses VALUES (30, 'San Diego'); 9. 10 11 Create Table Publisher2 ( 12 ID NUMBER(2), 13 Name VarCHAR2 (23), 14 Contact VARCHAR2 (15), 15 Phone VARCHAR2 (12), 16 CONSTRAINT publisher2_pubid pk PRIMARY KEY(id) 17 18 ); 19 INSERT INTO PUBLISHER2 VALUES (1, 'PRINTING IS US', 'TOMMIE SEYMOUR', '000-714-8321'); 20 21 INSERT INTO PUBLISHER2 VALUES (2, 'PUBLISH OUR WAY','JANE TOMLIN','010-410-0010'); INSERT INTO PUBLISHER2 VALUES (3, 'AMERICAN PUBLISHING', 'DAVID DAVIDSON','800-555-1211'); 23 INSERT INTO PUBLISHER2 VALUES (4, 'READING MATERIALS INC.','RENEE SMITH','800-555-9743'); INSERT INTO PUBLISHER2 VALUES (5, 'REED-N-RITE','SEBASTIAN JONES','800-555-8284'); 25 commit; 22 24 26 27 28 Create Table Publisher3 ( ID NUMBER(2), Name VarCHAR2 (23), 29 30 31 Contact VARCHAR2 (15), Phone VARCHAR2 (12), 32 33 34 CONSTRAINT publisher3 pubid pk PRIMARY KEY(id) 35 ); 36 INSERT INTO PUBLISHER3 VALUES (2, 'PUBLISH OUR WAY','JANE TOMLIN','010-410-0010'); 37 INSERT INTO PUBLISHER3 VALUES (3, 'AMERICAN PUB','DAVID DAVIDSON','800-555-1211'); 38 INSERT INTO PUBLISHER3 VALUES (6, 'PRINTING HERE',' SAM HUNT','000-714-8321'); 40 INSERT INTO PUBLISHER3 VALUES (7, 'PRINT THERE','CINDY TIKE','010-410-0010'); 41 commit; 39 42 43 44 CREATE TABLE Employees ( 45 46 47 NUMBER(4), VARCHAR2 ( 20), EMPNO LNAME FNAME VARCHAR2 (15), 48 JOB VARCHAR2 (9), 49 HIREDATE DATE, 50 DEPTNO NUMBER (2) NOT NULL, NUMBER(7,2), NUMBER (4), 51 MTHSAL 52 MGR 53 CONSTRAINT employees empno PK PRIMARY KEY (EMPNO) ); 55 INSERT INTO employees VALUES (7839, 'KING', 'BEN', 'GTECH2', TO DATE('17-NOV-91','DD-MON-YY'),10,6000, NULL); 56 INSERT INTO employees VALUES (8888,' JONES', 'LARRY', 'MTech2', TO DATE('17-NOV-98', 'DD-MON-YY'),10,4200,7839); INSERT INTO employees VALUES (7344, 'SMITH','SAM','GTech1',TO_DATE ('17-NOv-95', 'DD-MON-YY'), 20,4900,7839); INSERT INTO employees VALUES (7355, 'POTTS','JIM', 'GTech1', TO DATE ('17-NOV-95','DD-MON-YY'), 20,4900,7839); 54 57 58 INSERT INTO employees VALUES (8844, 'STUART','SUE','MTech1',TO_DATE('17-NOV-98', 'DD-MON-YY'),10,3700,8888); 60 59 COMMIT; 61 ALTER TABLE employees ADD (region CHAR(2)); 62