So I'm taking this SQL class in college and I am just lost. I need to make a sales order DB that displays items sold, guest id, address, price, etc...
Now, thisenter image description here is what I have so far:
create table buyer (Guest int not NULL AUTO_INCREMENT,
buyer_first_name varchar(30) not null,
buyer_middle_name varchar(30) not null,
buyer_last_name varchar(30) not null,
address_street varchar(50) not null,
address_apt_num varchar(100) not null,
phone_home varchar(15) not null,
phone_mobile varchar(15) not null,
email varchar(50),
PRIMARY KEY (Guest));
create table item (ProductID int not NULL AUTO_INCREMENT,
PerUnit varchar(50) not null,
Product varchar(50) not null,
Details varchar(200),
PRIMARY KEY (ProductID));
create table sales_order (OrderID int not NULL AUTO_INCREMENT,
Guest int not null,
Date varchar(20),
SpecialInstructions varchar(200),
PRIMARY KEY (OrderID),
FOREIGN KEY (Guest) REFERENCES buyer(Guest)
)ENGINE=INNODB;
create table order_line (OrderID int not null,
ProductID int not null,
Amount int,
PRIMARY KEY (OrderID,ProductID),
FOREIGN KEY (OrderID) REFERENCES sales_order(OrderID),
FOREIGN KEY (ProductID) REFERENCES item(ProductID)
)ENGINE=INNODB;
insert into buyer(buyer_first_name,
buyer_middle_name ,
buyer_last_name ,
address_street ,
address_apt_num ,
phone_home ,
phone_mobile ,
email) values ('Naruto','Ninja','Uzumaki','1234 HokageDrive','1A', '1234567890', '1233126540','[email protected]');
INSERT INTO item(Product,Details,PerUnit)
VALUES
('Big Burger Deluxe','Single Patty with Cheese','$1.99'),
('Double Big','2 Patties on a Bun','$2.99'),
('Happy Size Fries','10oz Fries w/ Seasoning','$0.99'),
('Chocolate Shake','Thick Chocolate Shake','$1.49'),
('Strawberry Shake','Thick Strawberry Shake','$1.49'),
('Cherry Pie','Deep-fried cherry Pie with Cream Cheese Icing.','$1.29');
INSERT INTO sales_order(SpecialInstructions,Guest,Date)
VALUES
('Please double bag the order.',1,'Sept. 2, 2015');
INSERT INTO order_line(OrderID,ProductID,Amount) VALUES (1,1,2);
Now I'm terrible with SQL and would like friendly 'Nudge' into the correct direction. I really do love doing this stuff! :)
When I run:
select * from buyer a
join sales_order b on
a.Guest=b.Guest
join order_line c on
b.OrderID=c.OrderID
Join item d on
c.ProductID=d.ProductID
I do not get the expected output of something along these lines:https://ibb.co/h0nP9J (without line totals)
Where can I start?
P.S. I'm using SQL Fiddle for this assignment.
UPDATE: My Instructions: Provide the SQL code for a complex join query to display all of the information contained in the attached “Receipt” with the exception of the calculated attributes (Line Total, Subtotal, Sales Tax and Total).
So from what I understand they want all the information at 1 time? I'll request clarification form instructors. Expected Output:
Guest ID Order ID FN MI LN Address APT# Home Cell Email Product ID Per/Unit Product Details Amount
1 1 Naruto Ninja Uzumaki 1234 Hokage Dr 1A 123 123 [email protected] 1 1.99 Big Burger Deluxe Single patty with Cheese 2
1 1 Naruto Ninja Uzumaki 1234 Hokage Dr 1A 123 123 [email protected] 2 2.99 Double Big 2 Patties on a Bun 2
1 1 Naruto Ninja Uzumaki 1234 Hokage Dr 1A 123 123 [email protected] 3 0.99 Happy Size Fries 10oz Fries w/ Seasoning 4
1 1 Naruto Ninja Uzumaki 1234 Hokage Dr 1A 123 123 [email protected] 4 1.49 Chocolate Shake Thick Chocolate Shake 1
1 1 Naruto Ninja Uzumaki 1234 Hokage Dr 1A 123 123 [email protected] 5 1.49 Strawberry Shake Thick Strawberry Shake 3
1 1 Naruto Ninja Uzumaki 1234 Hokage Dr 1A 123 123 [email protected] 6 1.29 Cherry Pie Deep-fried cherry Pie with Cream Cheese Icing. 2
******Follow link 2 for what I believe is the expected output.** UPDATE
My data was fine and I was able to get that working with an inner join query!
select
b.Guest,
b.buyer_first_name,
b.buyer_middle_name,
b.buyer_last_name,
b.address_street,
b.address_apt_num,
b.phone_home,
b.phone_mobile,
b.email,
s.OrderID,
s.`Date`,
o.ProductID,
i.PerUnit,
i.Product,
i.Details,
s.SpecialInstructions,
o.Amount
from buyer b
inner join sales_order s on b.Guest = s.Guest
inner join order_line o on s.OrderID = o.OrderID
inner join item i on o.ProductID = i.ProductID;
EDIT: Took the time to play with it on DB-Fiddle;
The data itself is fine, it's the select statement that's going to give you grief.
Your current statement is SELECT * with a join on several tables, that means that it will pull all the columns from all the tables. Looking at the example image you've provided, you don't need to present everything in one query.
For instance, the order grid doesn't require buyer_first_name, but the select statement includes it, whereas the Guest information doesn't require PerUnit from the item table. If I were you, I'd break the SELECT * statement into several smaller ones where you specify explicitly what columns you want to include.
As an aside: "INNER JOIN" is clearer to read than "JOIN" especially since they mean the same thing and you'll likely need to be able to easily differentiate the various types of JOIN queries. Likewise, buyer.buyer_middle_name probably shouldn't be set to NOT NULL. not everyone has a middle name. Same thing with AptNumber.
Hopefully this sets you in the right direction.