9 Replies - 1131 Views - Last Post: 06 October 2013 - 05:53 PM Rate Topic: -----

#1 HGPolumbus  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 92
  • Joined: 22-March 13

Basic table Joins

Posted 06 October 2013 - 03:12 PM

Hello, I'm taking my first SQL class and am getting some errors I don't understand. The table information is given to me so I know it's all correct.

The question:
"Using the BOOK_ORDER table, create a query using the correct function to return the order number, the date ordered, the date shipped, and a column representing the number of months between the two dates for all columns where a date shipped exists. Format the number returned from the function to display only two decimals, and give the column an alias of "Months Between". The numbers in Months Between must be positive."

SELECT OrderID, OrderDate, ShipDate, TO_CHAR(OrderDate - ShipDate) AS "Months Between" FROM BOOK_ORDER WHERE ShipDate IS NOT NULL;


I THINK this is what I have to do, but my numbers are not positive, and I'm not sure if I'm actually subtracting the months or not....

This next one I'm unsure of because it asks for a join between two tables that don't have a connection...

"Using the correct tables in your schema, create a query using either join operation that will list the title of each book and author name(s). Give the title column an alias of "Book Title". Sort the results by title and then by author last name."

I assume that I need the Author and Books tables, but there's no connection between them...

Create table Books
(BOOKID NUMBER(15) CONSTRAINT PK_BOOKS PRIMARY KEY,
ISBN VARCHAR2(10),
Title VARCHAR2(30),
PubDate DATE,
PubID NUMBER (2) CONSTRAINT FK_BOOKS_PUBLISHER REFERENCES PUBLISHER (PUBID),
Cost NUMBER (5,2),
Retail NUMBER (5,2),
Category VARCHAR2(12));

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

Create Table Author
(AuthorID Number(4) CONSTRAINT PK_AUTHOR PRIMARY KEY,
Lname VARCHAR2(10),
Fname VARCHAR2(10));

INSERT INTO AUTHOR
VALUES ('5100','SMITH', 'SAM');
INSERT INTO AUTHOR
VALUES ('6100','JONES','JANICE');
INSERT INTO AUTHOR
VALUES ('7100','AUSTIN','JAMES');
INSERT INTO AUTHOR
VALUES ('8100','MARTINEZ','SHEILA');
INSERT INTO AUTHOR
VALUES ('5110','KZOCHSKY','TAMARA');
INSERT INTO AUTHOR
VALUES ('1100','PORTER','LISA');
INSERT INTO AUTHOR
VALUES ('7105','ADAMS','JUAN');
INSERT INTO AUTHOR
VALUES ('9100','BAKER','JACK'); 
INSERT INTO AUTHOR
VALUES ('1105','PETERSON','TINA');
INSERT INTO AUTHOR
VALUES ('2100','WHITE','WILLIAM');
INSERT INTO AUTHOR
VALUES ('2105','WHITE','LISA');
INSERT INTO AUTHOR
VALUES ('3110','ROBINSON','ROBERT');
INSERT INTO AUTHOR
VALUES ('4110','FIELDS','OSCAR');
INSERT INTO AUTHOR
VALUES ('2110','WILKINSON','ANTHONY');



I have a few more but I would like to figure these out before I continue. Thanks for any insight anyone can provide. :helpsmilie:

Is This A Good Question/Topic? 0
  • +

Replies To: Basic table Joins

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3512
  • View blog
  • Posts: 11,993
  • Joined: 12-December 12

Re: Basic table Joins

Posted 06 October 2013 - 03:25 PM

Is this MySQL, MS SQL Server.. or some other?

Presumably OrderDate and ShipDate are DATE fields? Depending on the database you are using each has a range of different date functions that you can use to perform date arithmetic, and work out the number of months between.

Please provide sample input and output for your query. Again, different databases (or, more specifically, DBMS) will produce different results when subtracting dates. Some might give a number like 20.3445 where 20 is the number of days, others might give 435500 (or some other large value).

As you say, there is no connection between BOOKS and AUTHOR. A table is missing that would join them.

This post has been edited by andrewsw: 06 October 2013 - 03:26 PM

Was This Post Helpful? 1
  • +
  • -

#3 HGPolumbus  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 92
  • Joined: 22-March 13

Re: Basic table Joins

Posted 06 October 2013 - 03:42 PM

I'm sorry it's just Oracle SQL (I'm using the SQL*Plus program provided to me). My output looks like:

  ORDERID ORDERDATE SHIPDATE  Months Between
--------- --------- --------- ----------------------------------------
     1000 31-MAR-09 02-APR-09 -2
     1001 31-MAR-09 01-APR-09 -1
     1002 31-MAR-09 01-APR-09 -1
     1003 01-APR-09 01-APR-09 0
     1004 01-APR-09 05-APR-09 -4
     1005 01-APR-09 02-APR-09 -1
     1006 01-APR-09 02-APR-09 -1
     1007 02-APR-09 04-APR-09 -2
     1008 02-APR-09 03-APR-09 -1
     1009 03-APR-09 05-APR-09 -2
     1010 03-APR-09 04-APR-09 -1
     1011 03-APR-09 05-APR-09 -2
     1013 03-APR-09 04-APR-09 -1
     1014 04-DEC-09 05-JAN-10 -32
     1017 04-OCT-09 05-OCT-09 -1






Here is my table entries, as far as I can tell there is no relational connection to Author anywhere...

Create table Book_customer
(CustomerID  NUMBER(4) CONSTRAINT PK_BOOKCUSTOMER PRIMARY KEY,
LastName VARCHAR2(10),
FirstName VARCHAR2(10),
Address VARCHAR2(20),
City VARCHAR2(20),
State VARCHAR2(2),
Zip VARCHAR2(5),
Referred NUMBER(4));

INSERT INTO BOOK_CUSTOMER
VALUES (1001, 'MORALES', 'BONITA', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1002, 'THOMPSON', 'RYAN', 'P.O. BOX 9835', 'SANTA MONICA', 'CA', '90404', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1003, 'SMITH', 'LEILA', 'P.O. BOX 66', 'TALLAHASSEE', 'FL', '32306', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1004, 'PIERSON', 'THOMAS', '69821 SOUTH AVENUE', 'BOISE', 'ID', '83707', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1005, 'GIRARD', 'CINDY', 'P.O. BOX 851', 'SEATTLE', 'WA', '98115', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1006, 'CRUZ', 'MESHIA', '82 DIRT ROAD', 'ALBANY', 'NY', '12211', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1007, 'GIANA', 'TAMMY', '9153 MAIN STREET', 'AUSTIN', 'TX', '78710', 1003);
INSERT INTO BOOK_CUSTOMER
VALUES (1008, 'JONES', 'KENNETH', 'P.O. BOX 137', 'CHEYENNE', 'WY', '82003', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1009, 'PEREZ', 'JORGE', 'P.O. BOX 8564', 'BURBANK', 'CA', '91510', 1003);
INSERT INTO BOOK_CUSTOMER
VALUES (1010, 'LUCAS', 'JAKE', '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1011, 'MCGOVERN', 'REESE', 'P.O. BOX 18', 'CHICAGO', 'IL', '60606', NULL); 
INSERT INTO BOOK_CUSTOMER
VALUES (1012, 'MCKENZIE', 'WILLIAM', 'P.O. BOX 971', 'BOSTON', 'MA', '02110', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1013, 'NGUYEN', 'NICHOLAS', '357 WHITE EAGLE AVE.', 'CLERMONT', 'FL', '34711', 1006);
INSERT INTO BOOK_CUSTOMER
VALUES (1014, 'LEE', 'JASMINE', 'P.O. BOX 2947', 'CODY', 'WY', '82414', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1015, 'SCHELL', 'STEVE', 'P.O. BOX 677', 'MIAMI', 'FL', '33111', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1016, 'DAUM', 'MICHELL', '9851231 LONG ROAD', 'BURBANK', 'CA', '91508', 1010);
INSERT INTO BOOK_CUSTOMER
VALUES (1017, 'NELSON', 'BECCA', 'P.O. BOX 563', 'KALMAZOO', 'MI', '49006', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1018, 'MONTIASA', 'GREG', '1008 GRAND AVENUE', 'MACON', 'GA', '31206', NULL);
INSERT INTO BOOK_CUSTOMER
VALUES (1019, 'SMITH', 'JENNIFER', 'P.O. BOX 1151', 'MORRISTOWN', 'NJ', '07962', 1003);
INSERT INTO BOOK_CUSTOMER
VALUES (1020, 'FALAH', 'KENNETH', 'P.O. BOX 335', 'TRENTON', 'NJ', '08607', NULL);

Create Table Book_order
(OrderID NUMBER(4) CONSTRAINT PK_BOOKORDER_ORDERID PRIMARY KEY,
CustomerID NUMBER(4) CONSTRAINT FK_BookOrder_BookCustomer REFERENCES Book_Customer (CustomerID),
OrderDate DATE,
ShipDate DATE,
ShipStreet VARCHAR2(20),
ShipCity VARCHAR2(20),
ShipState VARCHAR2(2),
ShipZip VARCHAR2(5));

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

Create Table Publisher
(PubID NUMBER(2) CONSTRAINT PK_PUBLISHER PRIMARY KEY,
PublisherName VarCHAR2(23),
ContactName VARCHAR2(20),
Phone VARCHAR2(12));

INSERT INTO PUBLISHER
VALUES(1,'PRINTING IS US','TOMMIE SEYMOUR','800-714-8321');
INSERT INTO PUBLISHER
VALUES(2,'PUBLISH OUR WAY','JANE TOMLIN','800-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');
INSERT INTO PUBLISHER
VALUES(6,'LITTLE HOUSE','DOUG COLLINS','800-515-2665');


Create Table Author
(AuthorID Number(4) CONSTRAINT PK_AUTHOR PRIMARY KEY,
Lname VARCHAR2(10),
Fname VARCHAR2(10));

INSERT INTO AUTHOR
VALUES ('5100','SMITH', 'SAM');
INSERT INTO AUTHOR
VALUES ('6100','JONES','JANICE');
INSERT INTO AUTHOR
VALUES ('7100','AUSTIN','JAMES');
INSERT INTO AUTHOR
VALUES ('8100','MARTINEZ','SHEILA');
INSERT INTO AUTHOR
VALUES ('5110','KZOCHSKY','TAMARA');
INSERT INTO AUTHOR
VALUES ('1100','PORTER','LISA');
INSERT INTO AUTHOR
VALUES ('7105','ADAMS','JUAN');
INSERT INTO AUTHOR
VALUES ('9100','BAKER','JACK'); 
INSERT INTO AUTHOR
VALUES ('1105','PETERSON','TINA');
INSERT INTO AUTHOR
VALUES ('2100','WHITE','WILLIAM');
INSERT INTO AUTHOR
VALUES ('2105','WHITE','LISA');
INSERT INTO AUTHOR
VALUES ('3110','ROBINSON','ROBERT');
INSERT INTO AUTHOR
VALUES ('4110','FIELDS','OSCAR');
INSERT INTO AUTHOR
VALUES ('2110','WILKINSON','ANTHONY');



Create table Books
(BOOKID NUMBER(15) CONSTRAINT PK_BOOKS PRIMARY KEY,
ISBN VARCHAR2(10),
Title VARCHAR2(30),
PubDate DATE,
PubID NUMBER (2) CONSTRAINT FK_BOOKS_PUBLISHER REFERENCES PUBLISHER (PUBID),
Cost NUMBER (5,2),
Retail NUMBER (5,2),
Category VARCHAR2(12));

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

CREATE TABLE ORDER_ITEMS
(ORDERID NUMBER(4) NOT NULL CONSTRAINT FK_ORDERITEMS_BOOKORDER REFERENCES Book_Order (OrderID),
ITEMNUM NUMBER(2) NOT NULL,
BOOKID NUMBER(15) CONSTRAINT FK_ORDERITEMS_BOOKS REFERENCES BOOKS (BOOKID),
QUANTITY NUMBER(3), 
constraint pk_ORDER_ITEMS PRIMARY KEY (orderid, bookid));

INSERT INTO ORDER_ITEMS
VALUES (1000,1,5005,1); 
INSERT INTO ORDER_ITEMS
VALUES (1001,1,5013,1); 
INSERT INTO ORDER_ITEMS
VALUES (1001,2,5009,1); 
INSERT INTO ORDER_ITEMS
VALUES (1002,1,5004,2); 
INSERT INTO ORDER_ITEMS
VALUES (1003,1,5004,1); 
INSERT INTO ORDER_ITEMS
VALUES (1003,2,5001,1);
INSERT INTO ORDER_ITEMS
VALUES (1003,3,5005,1);
INSERT INTO ORDER_ITEMS
VALUES (1004,1,5009,2);
INSERT INTO ORDER_ITEMS
VALUES (1005,1,5014,1);
INSERT INTO ORDER_ITEMS
VALUES (1006,1,5008,1);
INSERT INTO ORDER_ITEMS
VALUES (1007,1,5006,3);
INSERT INTO ORDER_ITEMS
VALUES (1007,2,5008,1);
INSERT INTO ORDER_ITEMS
VALUES (1007,3,5011,1);
INSERT INTO ORDER_ITEMS
VALUES (1007,4,5004,1);
INSERT INTO ORDER_ITEMS
VALUES (1008,1,5005,2);
INSERT INTO ORDER_ITEMS
VALUES (1009,1,5005,1);
INSERT INTO ORDER_ITEMS
VALUES (1009,2,5002,1);
INSERT INTO ORDER_ITEMS
VALUES (1010,1,5004,4);
INSERT INTO ORDER_ITEMS
VALUES (1011,1,5009,1);
INSERT INTO ORDER_ITEMS
VALUES (1012,1,5011,1);
INSERT INTO ORDER_ITEMS
VALUES (1012,2,5007,2);
INSERT INTO ORDER_ITEMS
VALUES (1012,3,5009,1);
INSERT INTO ORDER_ITEMS
VALUES (1012,4,5002,1);
INSERT INTO ORDER_ITEMS
VALUES (1013,1,5004,1);
INSERT INTO ORDER_ITEMS
VALUES (1014,1,5002,2);
INSERT INTO ORDER_ITEMS
VALUES (1015,1,5005,1);
INSERT INTO ORDER_ITEMS
VALUES (1016,1,5009,1);
INSERT INTO ORDER_ITEMS
VALUES (1017,1,5011,2);
INSERT INTO ORDER_ITEMS
VALUES (1018,1,5005,1);
INSERT INTO ORDER_ITEMS
VALUES (1018,2,5004,1);
INSERT INTO ORDER_ITEMS
VALUES (1019,1,5002,1);
INSERT INTO ORDER_ITEMS
VALUES (1020,1,5005,1);

CREATE TABLE BOOK_AUTHOR
(BOOKID NUMBER(15) CONSTRAINT fk_BookAuthor_Books REFERENCES BOOKS (BOOKID),
AUTHORid NUMBER(4) CONSTRAINT fk_BookAuthor_Author REFERENCES Author (AUTHORID),
CONSTRAINT pk_BOOK_AUTHOR PRIMARY KEY (BOOKID,AUTHORID));

INSERT INTO BOOK_AUTHOR
VALUES (5001,'5100');
INSERT INTO BOOK_AUTHOR
VALUES (5001,'1100');
INSERT INTO BOOK_AUTHOR
VALUES (5002,'6100');
INSERT INTO BOOK_AUTHOR
VALUES (5003,'5110');
INSERT INTO BOOK_AUTHOR
VALUES (5004,'1105');
INSERT INTO BOOK_AUTHOR
VALUES (5004,'7100');
INSERT INTO BOOK_AUTHOR
VALUES (5004,'7105');
INSERT INTO BOOK_AUTHOR
VALUES (5005,'9100');
INSERT INTO BOOK_AUTHOR
VALUES (5006,'7100');
INSERT INTO BOOK_AUTHOR
VALUES (5007,'2100');
INSERT INTO BOOK_AUTHOR
VALUES (5007,'2105');
INSERT INTO BOOK_AUTHOR
VALUES (5008,'6100');
INSERT INTO BOOK_AUTHOR
VALUES (5009,'3110');
INSERT INTO BOOK_AUTHOR
VALUES (5009,'4110');
INSERT INTO BOOK_AUTHOR
VALUES (5009,'9100');
INSERT INTO BOOK_AUTHOR
VALUES (5010,'5100');
INSERT INTO BOOK_AUTHOR
VALUES (5011,'3110');
INSERT INTO BOOK_AUTHOR
VALUES (5012,'5100');
INSERT INTO BOOK_AUTHOR
VALUES (5013,'2100');
INSERT INTO BOOK_AUTHOR
VALUES (5014,'2105');

create table promotion
(PromotionID NUMBER(3) CONSTRAINT pk_promotion PRIMARY KEY,
gift  varchar2(15),
minretail number(5,2),
maxretail number(5,2));

insert into promotion
 values (1,'BOOKMARKER', 0, 12);

insert into promotion
 values (2,'BOOK LABELS', 12.01, 25);

insert into promotion
values (3, 'BOOK COVER', 25.01, 56);

insert into promotion
values (4, 'FREE SHIPPING', 56.01, 999.99);



Thanks for the reply, I really appreciate it.

This post has been edited by andrewsw: 06 October 2013 - 03:47 PM
Reason for edit:: Removed previous quote

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3512
  • View blog
  • Posts: 11,993
  • Joined: 12-December 12

Re: Basic table Joins

Posted 06 October 2013 - 03:54 PM

Can you not see that the date-subtraction is returning the number of days difference? But they are negative because the OrderDate is before the ShipDate. I'm really surprised that you can't see this :dontgetit:

If you want to know the number of months between these dates then you could take a simplistic approach and divide the these number of days by 30. Otherwise, investigate Oracle date-functions to find ones that can be used to return the number of months between two dates.

And, erm, you do have a table that links BOOKS to AUTHOR. It is called BOOK_AUTHOR. :whistling:
Was This Post Helpful? 1
  • +
  • -

#5 HGPolumbus  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 92
  • Joined: 22-March 13

Re: Basic table Joins

Posted 06 October 2013 - 04:51 PM

I'm sorry these are really dumb questions, I should've seen that. I have to do 3 joins then to match the authorID to BookID right?
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3512
  • View blog
  • Posts: 11,993
  • Joined: 12-December 12

Re: Basic table Joins

Posted 06 October 2013 - 04:54 PM

View PostHGPolumbus, on 06 October 2013 - 11:51 PM, said:

I'm sorry these are really dumb questions, I should've seen that. I have to do 3 joins then to match the authorID to BookID right?

2 joins, between 3 tables..
Was This Post Helpful? 1
  • +
  • -

#7 HGPolumbus  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 92
  • Joined: 22-March 13

Re: Basic table Joins

Posted 06 October 2013 - 04:58 PM

View Postandrewsw, on 06 October 2013 - 04:54 PM, said:

View PostHGPolumbus, on 06 October 2013 - 11:51 PM, said:

I'm sorry these are really dumb questions, I should've seen that. I have to do 3 joins then to match the authorID to BookID right?

2 joins, between 3 tables..


Right. My bad, sorry I'm not good at this stuff obviously. I should be able to use:

months_between(shipdate,orderdate)

for my other one.
Was This Post Helpful? 0
  • +
  • -

#8 HGPolumbus  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 92
  • Joined: 22-March 13

Re: Basic table Joins

Posted 06 October 2013 - 05:15 PM

I have another one which says "Using the BOOK_ORDER, ORDER_ITEMS, and BOOKS tables, create a query using an OUTER JOIN operation that will list the book title, order date, and order number for all books in the BOOKS table. Order your output in descending order by ORDER_ITEMS.BOOKID. There are three books that have never been ordered which should show up at the top of your listing."

So my query looks like:
SELECT BOOKS.Title, ORDER_ITEMS.OrderDate, BOOK_ORDER.OrderID 
FROM 
BOOKS OUTER JOIN ORDER_ITEMS
ON BOOKS.BookID = ORDER_ITEMS.BookID 
JOIN BOOK_ORDER
ON ORDER_ITEMS.OrderID = BOOK_ORDER.OrderID
ORDER BY ORDER_ITEMS.BookID desc;



It tells me "BOOKS"."BOOKID": invalid identifier. But if I take 'outer' out it works. Is there a syntax difference for outer joins that I'm missing?
Was This Post Helpful? 0
  • +
  • -

#9 HGPolumbus  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 92
  • Joined: 22-March 13

Re: Basic table Joins

Posted 06 October 2013 - 05:42 PM

Got that one, I didn't specify LEFT OUTER JOIN, etc.
Was This Post Helpful? 0
  • +
  • -

#10 HGPolumbus  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 92
  • Joined: 22-March 13

Re: Basic table Joins

Posted 06 October 2013 - 05:53 PM

Ok I have two more, and I thought I had it but, of course...

"Using the BOOKS and ORDER_ITEMS table, write a query using the correct Relational Set Operator that will show all of the Book IDs in the BOOKS table that have not been ordered."

I figured I had to do DIFFERENCE which I assumed worked like UNION...

SELECT BookID FROM BOOKS DIFFERENCE SELECT BookID FROM ORDER_ITEMS;



But under the second Select, I get the error "SQL command not properly ended" I actually can't find a concrete usage example of difference in my text or online, so maybe I'm totally wrong (probably).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1