Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    33

    Unanswered: 5 Database Queries to go with an online purchasing system :-)

    Hi Guys,

    I'm having trouble thinking of ideas for queries that would go with an online purchasing system. The queries must be partially complex (i.e. not just a simple insert query ).

    I can easily write the queries myself but i just need a little help thinking of useful queries.

    A query for an 'Invoice' for example is a perfect idea as it implements table joins etc.

    Possible other queries are:
    Find all customers that have spent a total of more than X amount
    Find all delivery address's that X customer has sent items to
    Track an order to see if items have been sent yet or not.
    etc....

    If anyone else can think of a few other ideas i'd be very grateful :-)

    thx for reading :-)

    --Philkills

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    online purchasing sytem? or homework assignment?

    total sales by category for the previous month
    average price of backlogged orders (product not in stock)
    most popular item (by quantity sold, not total price)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    easiest technique...
    open a copy of Access, create yuour tables
    create the realtionships
    open up the query designer
    add the required constraints....
    run the query, prove it works
    open up the query in SQL mode, copy and paste the SQL to your homework assignment paper
    ....jobsagoodun



    ... requires minimum thought and creativity, and absolutely no proof that you have understood the concepts behid joins, where clauses etc...
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2006
    Posts
    33
    Quote Originally Posted by r937
    online purchasing sytem? or homework assignment?

    total sales by category for the previous month
    average price of backlogged orders (product not in stock)
    most popular item (by quantity sold, not total price)
    Some nice ideas there thx ^^


    Quote Originally Posted by healdem
    easiest technique...
    open a copy of Access, create yuour tables
    create the realtionships
    open up the query designer
    add the required constraints....
    run the query, prove it works
    open up the query in SQL mode, copy and paste the SQL to your homework assignment paper
    ....jobsagoodun



    ... requires minimum thought and creativity, and absolutely no proof that you have understood the concepts behid joins, where clauses etc...
    and lol....

    a nice way to cheat i suppose... but i do actually understand joins etc ;p

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by r937
    total sales by category for the previous month
    average price of backlogged orders (product not in stock)
    most popular item (by quantity sold, not total price)
    Variations on these:
    - Total sales for all foregoing months
    - Top ten most popular items
    - Most popular category per client (useful for better direct advertising ;-)
    - Top ten clients in terms of the amount spent
    - Same question, but now in 12 columns, for each of the last 12 months
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb



    - Customer's year to date sales (purchases) compared with last year's year to date sales (purchases).

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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