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

    Unanswered: If your good with SQL queries - please help

    Hi, ive got some work to do on SQL queries, the scenario is below and at the bottom is my attempt at answering in the questions:
    Could somebody simply tell me if the answer at the bottow are correct, if not what I have done wrong.

    A local company that produces machine parts has decided to develop an in-house database system. They have identified the following tables: -

    tblOrders OrderNo, CustomerNo, Date, OrderTotal

    tblCustomers CustomerNo, Name, Street, Town, County, Postcode

    tblParts PartNo, Description, UnitCost

    tblItems OrderNo, PartNo, Quantity, ItemTotal



    Create SQL queries to produce the following: -

    a) Details of all orders over £1000 sorted by customer
    number.

    b) A list of all part descriptions and their quantities appearing on order 39

    c) Delete all orders placed by customers in
    Wrexham.

    d) Archive all orders placed by customer Clarke into a new table called
    tblArchive.

    e) Increase the price of all parts whose description includes the
    word “washer” by 4%.

    These are my answer, which im not too sure if they are correct. If any1 could tell me if there correct or not that would be great, thanks.

    a)
    SELECT *
    FROM tblOrders
    ORDERBY CustomerNO
    WHERE OrderTotal > 1000

    b)
    SELECT tblParts.PartNo, tblParts.Description, tblItems.Quantity
    FROM tblItems INNER JOIN tblParts ON tblItems.PartNo = tblParts.PartNo;
    WHERE OrderNo = 39

    c)
    DELETE tblOrders.*
    FROM tblOrders INNER JOIN tblCustomers ON tblOrders.CustomerID = tblCustomers.CustomerID
    WHERE Town = “Wrexham”

    d)
    INSERT INTO tblArchive
    SELECT *
    FROM tblOrders INNER JOIN tblCustomers ON tblOrders.CustomerID = tblCustomers.CustomerID
    WHERE Name = “Clarke”

    e)
    UPDATE tblParts
    SET UnitCost = [UnitCost]*1.04
    WHERE Description LIKE “*washer” or Description LIKE “washer*” or
    Description LIKE “*washer*”


    Please help
    Last edited by andrewc266266; 04-09-08 at 17:42.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't use SELECT *. Very sloppy. List the columns instead.

    a) What are the "details"? Do details include subrecords, such as the items on the order? You should at least join the customer table and return that data as well, since those are surely "details" of the order. Simply returning the customerID from the orders table accomplishes nothing.

    b) Take the semicolon out of the statement.

    c) "DELETE tblOrders.* " should just be "DELETE tblOrders".

    d) Presumably tblArchive has the same structure as the orders table, which means your code will fail because the list of columns included in SELECT * from two tables will not match that of the destination table. So again, you should list the column individually, and in this case you should list them in both the INSERT and SELECT portions to be sure they have the same count and order.

    e) "*" is not the wildcard used by sql server. "%" represent any number of characters instead. And so you only need one of your LIKE statements, not all three. I'll let you guess which one to keep.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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