Nead help answering sql questions

Blargh · 28-04-2013 8:33am #1

Hey guys i need help answering 2 questions on an assignment for college got all but 2 done. Ive ran out of steam now trying to wrap my head arround sql and the last 2 questions have me stumped.

Here are the query questions and the tables below.

9. What is the name of the customer who has the highest credit which is just below the average credit limit?

11. What would the total cost of order 450 be, if we gave a discount on product No 760 of 10%

[/URL]

srsly78 · 28-04-2013 9:22am

You have either mangled the question, or not pasted half of it.

Which bit are you stuck on? It's pretty simple, just find out how to calculate an average first (google for "sql average"). How do your notes say to do an average?

Blargh · 28-04-2013 10:10am

Ya soz im half brain dead at this stage no sleep trying to get a flash game working Here is the table data. Ive got q9 query working just there. q 11 has me confused how do i go about giving it a discount?

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);

srsly78 · 28-04-2013 10:13am

Construct two seperate queries.

A: Find the total cost of the order, this is the sum of all the stuff in it.
B: Find the size of the discount, which is equal to 0.1*cost of item in question*quantity in order.

Your answer is A-B.

Do them seperately first to make sure they return correct result, then combine.

Nead help answering sql questions

Comments