Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Nead help answering sql questions

Options
  • 28-04-2013 8:33am
    #1
    Blargh
    Registered Users Posts: 63 ✭✭


    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%



    61855528.png[/URL]


Comments

  • Options
    #2
    srsly78
    Registered Users Posts: 7,157 ✭✭✭srsly78


    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?


  • Options
    #3
    Blargh
    Registered Users Posts: 63 ✭✭Blargh


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


  • Options
    #4
    srsly78
    Registered Users Posts: 7,157 ✭✭✭srsly78


    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.


Advertisement