Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    2

    Red face Unanswered: Write a query to list ... (was "Help, trouble with database query!")

    Hi guys, I am busy doing an database assignment for a hardware supplies company and i am having trouble with two queries. Could anyone help?

    The queries are what i am trying to do -

    a )
    Write a query to list the customer name and the number of orders they have placed. Include any customers that have not placed orders. Sequence the list by the customer number.

    b)
    Write a query to list parts (identity number and first 20 characters of the description) and the total quantity ordered for each part and total value relating to those quantities. Include any parts that have not been ordered. Sequence the list in alphabetical order of description.

    If it helps the tables structure is included below!

    Thanks in advance!


    create table customer
    (
    Cust_No number primary key,
    name varchar2(30) not null,
    address1 varchar2(40),
    address2 varchar2(40),
    area varchar2(40),
    postcode varchar2(8),
    phone varchar2(14),
    credit_limit number default 100
    ) pctfree 0
    storage (
    initial 0K
    next 2k
    pctincrease 0);

    create table part
    (
    part_number varchar2(13) primary key,
    description varchar2(40),
    UOM varchar2(10) default 'EACH' not null,
    PricePerUnit number
    ) pctfree 0
    storage (
    initial 0K
    next 2k
    pctincrease 0);


    create table SalesOrder
    (
    Order_No number primary key,
    cust_no number not null references customer,
    date_placed date default sysdate not null,
    date_completed date
    ) pctfree 0
    storage (
    initial 0K
    next 2k
    pctincrease 0);

    create table SalesOrderLine
    (
    order_no number,
    line_no number,
    part_no varchar2(13) not null references part,
    Qty_ordered number default 1 not null,
    Qty_shipped number default 0 not null,
    date_shipped date,
    primary key(order_no,line_no)
    ) pctfree 0
    storage (
    initial 0K
    next 2k
    pctincrease 0);
    Last edited by noisyassassin; 02-23-06 at 17:38.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    HOMEWORK!!!

    we are just flat out not going to write your queries for you

    show what you've been able to do so far in writing the queries

    show your test data

    show your error messages if any


    oh, and by the way, you are in the wrong forum, mysql has no such datatype as varchar2

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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