Mysql Quections Help Needed?

I need some help answering a few questions in mysql here are the tables.
and the questions Any help would be greatly appreciated. Im lost when it cones to mysql.
1. What is the average number of orderitems per order?
2. How many orders came from customers with a Tipperary address?
3. What would the total cost of order 450 be, if we gave a discount on product No 760 of 10%.
4. Display the total quantity of capacitors ordered to date.
5. What part is ordered the most?
6. What is the total value of all orders for each customer?
7. All customers whose name starts with B.
create table customers (
CustNo Char(5) Primary key,
Name varchar(25) not null,
Address varchar(50) not null,
CreditLimit Decimal(5,2));
Create table parts(
PartNo Char(4) Primary key,
Description varchar(25) not null,
Price Decimal(10,2) not null,
Colour varchar(10) not null);
create table orders(
orderno char(4) primary key,
OrdDate Date not null,
CustNo char(5) not null,
foreign key(custNo) references customers(custNo));
Create table orderItem(
OrderNo Char(4),
PartNo Char(4),
Qty integer not null,
foreign key(orderNo) references orders(orderNo),
foreign key(partNo) references parts(partNo));
insert into customers
values (‘3340’, ‘jm Electric’, ‘Tipperary Rd, Limerick’, 1500) ;
insert into customers
values (‘3346’, ‘Bridge Circuits’, ‘Oola, Tipperary’, 1000);
insert into customers
values (‘3400’, ‘Byrne Bros’, ‘Nenagh, Tipperary’, 5200);
insert into customers
values (‘3560’, ‘Mercury Eng’, ‘Ennis Rd., Limerick’, 2300);
insert into Parts
values (‘760’, ‘Resistor’, 5, ‘Green’);
insert into Parts
values (‘775’, ‘Capacitor’, 2, ‘Blue’);
insert into Parts
values (‘780’, ‘Diode’, 1, ‘Red’);
insert into Parts
values (‘950’, ‘Switch’, 4, ‘Green’);
insert into orders
values (‘450’, ‘2011-8-20’, ‘3340’);
insert into orders
values (‘453’, ‘2011-8-24’, ‘3560’);
insert into orders
values (‘455’, ‘2011-9-1’, ‘3340’);
insert into orderitem
values (‘450’, ‘760’, 30);
insert into orderitem
values (‘450’, ‘780’, 45);
insert into orderitem
values (‘453’, ‘775’, 25);
insert into orderitem
values (‘455’, ‘760’, 30);
insert into orderitem
values (‘455’, ‘775’, 10);

Both comments and pings are currently closed.

Comments are closed.

Powered by WordPress | Designed by: free css template | Thanks to hostgator coupon and web hosting reviews