A really simple SQL query

Discussion in 'Oracle' started by Lokantis, Oct 7, 2008.

  1. Lokantis

    Lokantis New Member

    Oct 7, 2008
    Likes Received:
    Trophy Points:
    Hello, I am a total noobie at SQL and I still don't understand all the concepts. I want to see how to solve this first query and hopefully I'll be able to go from there.
    I have this database:
    Customer (CustNo, CustFirstName, CustLastName, CustStreet, CustCity, CustState, CustZip, CustBal)

    Employee (EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEmail, SupEmpNo, EmpCommRate)

    Product (ProdNo, ProdName, ProdMfg, ProdQOH, ProdPrice, ProdNextShipDate)

    OrderTbl (OrdNo, OrdDate, CustNo, EmpNo, ShpName, ShpStreet, ShpCity, ShpState, ShpZip)

    OrdProd (OrdNo, ProdNo, Qty)

    2 answers: first one using 3 simple subqueries nested, second one with JOIN (using no subqueries).
    List the product number, name, and price of those products with a price greater than $150 and were ordered in Jan 2008 by customers with balances greater than $400.

    Thank you in advance.
  2. xpi0t0s

    xpi0t0s Mentor

    Aug 6, 2004
    Likes Received:
    Trophy Points:
    Senior Support Engineer
    It's not that simple...
    Try building the query bit by bit rather than trying to do the whole lot at once.
    So for example you could start with "List the product number, name, and price", which translates fairly easily into SELECT ProdNo, ProdName, ProdPrice FROM Product;
    "Price greater than $150" leads to a fairly obvious WHERE clause.
    Then just continue adding bit by bit, checking as you go along against your test data that correct data is returned and incorrect data is not (both are important) until you have the full query.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice