I stuck with a following problem, I have four tables:
CREATE TABLE Customer(
customer_id DECIMAL(10) NOT NULL,
customer_first VARCHAR(30),
customer_last VARCHAR(40),
customer_total DECIMAL(12, 2),
PRIMARY KEY (customer_id));
CREATE TABLE Item(
item_id DECIMAL(10) NOT NULL,
item_description VARCHAR(30),
item_price DECIMAL(10),
PRIMARY KEY (item_id));
CREATE TABLE Customer_order (
order_id DECIMAL(10) NOT NULL,
customer_id DECIMAL(10) NOT NULL,
order_total DECIMAL(12,2),
order_date DATE,
PRIMARY KEY (order_id),
FOREIGN KEY (customer_id) REFERENCES customer);
CREATE TABLE Order_line(
order_id DECIMAL(10) NOT NULL,
item_id DECIMAL(10) NOT NULL,
item_quantity DECIMAL(10) NOT NULL,
line_price DECIMAL(12,2),
PRIMARY KEY (order_id, item_id),
FOREIGN KEY (order_id) REFERENCES Customer_order,
FOREIGN KEY (item_id) REFERENCES Item);
This is an example data I put in:
INSERT INTO Customer values(1,'John','Smith',0);
INSERT INTO Customer values(2,'Mary','Berman',0);
INSERT INTO Customer values(3,'Elizabeth','Johnson',0);
INSERT INTO Customer values(4,'Peter','Quigley',0);
INSERT INTO Customer values(5,'Stanton','Hurley',0);
INSERT INTO Customer values(6,'Yvette','Presley',0);
INSERT INTO Customer values(7,'Hilary','Marsh',0);
INSERT INTO Item values(1,'Plate',10);
INSERT INTO Item values(2,'Bowl',11);
INSERT INTO Item values(3,'Knife',5);
INSERT INTO Item values(4,'Fork',5);
INSERT INTO Item values(5,'Spoon',5);
INSERT INTO Item values(6,'Cup',12);
INSERT INTO Customer_order values(1,1,506,CAST('18-DEC-2003' AS DATE));
INSERT INTO Customer_order values(2,1,1000,CAST('17-DEC-2003' AS DATE));
INSERT INTO Customer_order values(3,3,15,CAST('18-DEC-2003' AS DATE));
INSERT INTO Customer_order values(4,3,15,CAST('19-DEC-2003' AS DATE));
INSERT INTO Customer_order values(5,2,1584,CAST('20-DEC-2003' AS DATE));
INSERT INTO Customer_order values(6,4,100,CAST('21-DEC-2003' AS DATE));
INSERT INTO Customer_order values(7,5,40,CAST('22-DEC-2003' AS DATE));
INSERT INTO Customer_order values(8,1,100,CAST('23-DEC-2003' AS DATE));
INSERT INTO Order_line values(1,1,10,100);
INSERT INTO Order_line values(1,5,2,10);
INSERT INTO Order_line values(1,2,36,396);
INSERT INTO Order_line values(2,1,95,950);
INSERT INTO Order_line values(2,3,10,50);
INSERT INTO Order_line values(3,4,3,15);
INSERT INTO Order_line values(4,4,3,15);
INSERT INTO Order_line values(5,6,132,1584);
INSERT INTO Order_line values(6,1,10,100);
INSERT INTO Order_line values(7,5,5,25);
INSERT INTO Order_line values(7,4,3,15);
I need to show the order line information for an invoice. The results must have the item description, price, quantity and order line total (the quantity times the price). To simplify let me limit results to order_id = 1.
Went through numerous tutorials already. I was trying old style and new ANSI, but always running in a different mistakes.
These are my attempts:
select item_description, item_price, order_id, item_quantity
from customer_order
inner join item on item_id=1
inner join order_line on item_id=1;
inner join order_line on item_id=1
*
ERROR at line 4:
ORA-00918: column ambiguously defined
Or like this:
select item_description, item_price, order_id, item_quantity
from item
natural join order_line
where item_id=1
natural join
customer_order where item_id=1
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
