Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    1

    Unanswered: Help with a few querie questions

    Im going through my sql powerpoint for college and dont know how to do a few questions on them wondering can anyone help me with them.

    Here are the questions

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

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

    the tables
    Code:
    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);

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you got so far
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •