Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012

    Unanswered: A few queries that I need help with

    hello all!

    I have this SQL code that I am supposed to write and am worried that my code may be wrong. I am fairly confident that the first 2 questions are correct but after that......who knows! I have attached an ER diagram( which is acutally really messed up...but whatever) Below are the queries that I wrote based on what I thought that the questions was asking (the directions are not clear at all).

    I am just looking to see if any SQL experts out there can help me out with the queries and let me know if any of them are wrong.
    What I am really worried about is that I understood what is really being asked in the question ( specifically 4,5,6).

    Here is the ER diagram:

    Also, I cannot test my queries on a database. All I have is the ER Diagram to go off of. And considering that I do not understand some of the column headings, I cannot make a database to test off of.

    1) Look for all customers with a name that includes the string 'Johnson'.

    my answer:
        SELECT first_name, last_name
        FROM Customer
        WHERE first_name LIKE '%Johnson%' OR last_name LIKE '%Johnson%';
    2) The 'Product' table's primary key is product_id. A default sequence has been assigned to the column product_id. Add a product called 'Widget' with a unit_price of $5.00 to the Product table.

    My Answer:

    INSERT INTO Product (product_name,unit_price)  VALUES ('Widget',5.00);
    3.Show all information related (order, customer and product data) using an explicit join for order_id 2477843.

    My Answer:

        SELECT order_id, date,billing_name, billing_addr1, billing_addr2, billing_city, billing_state, billing_country, billing_zip,quantity_sold, total_price,
        customer.customer_id,first_name,last_name,address_1, address_2, city, state, country, zip, discount, active,
        product.product_id,product_name, desc, manufacturer, sku_number, unit_price,vendor
        FROM Order JOIN Product ON Order.Product_id=Product.product_id
        JOIN Customer ON Order.customer_id=Customer.customer_id
        WHERE order_id=2477843;
    4.Show the first_name, last_name, and the total amount of all orders for customer_id 87162412.

    My Answer:
        SELECT first_name,last_name, total_price as total_amount
        FROM Customer, Order
        WHERE Customer. customer_id=Order.customer_id AND customer.customer_id=87162412;
    5.Show the customer_id, first_name, and last_name of any customers having orders totaling more than $5000 to date.
    My Answer:

    SELECT customer_id, first_name, last_name
        FROM Customer,Order
        WHERE Customer.customer_id=Order.customer_id AND total_price>5000;
    6.Write a query that returns a Boolean flag if the total_price of an order is greater than or equal to $5,000.
    (I honestly have no clue what this question is asking but here is what I came up with.)

    Select order_id, CASE WHEN total_price>=5000 THEN cast(1 as bit) ELSE cast(0 as bit) as totalgreater5k
    FROM Order;

    And finally.....This is not homework. fwiw.
    Last edited by worldindus; 04-10-12 at 23:14. Reason: added something

  2. #2
    Join Date
    Nov 2004
    Provided Answers: 4
    Also, I cannot test my queries on a database. All I have is the ER Diagram to go off of. And considering that I do not understand some of the column headings, I cannot make a database to test off of.
    That's a weak argument. The column names are descriptive, and you could leave those that may not be clear out of the test tables, as they are not needed in any of the questions. Create those tables, put some sample data in it and test your queries on them. It is the only way to find syntax errors and logical errors.

    And finally.....This is not homework. fwiw.
    I could have sworn these are not typically the kind of questions I get from my users.

    Queries 1, 2 and 3 look valid to me.

    Query 4: "and the total amount of all orders for customer_id 87162412."
    You don't calculate the total amount of all orders for that customer. Create those test tables and you'll see what I mean.

    And why do you go back to old school SQL with
    "FROM Customer, Order
    WHERE Customer. customer_id=Order.customer_id"
    Change it to
    "FROM Customer
    Customer. customer_id=Order.customer_id"

    Query 5: You are not calculating the total of all a customer's orders.
    Old school SQL.

    Query 6: looks good to me
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2012
    I am less concerned about syntax and more concerned about whether I am interpreting the question correctly. In fact that is ALL I am concerned with. And no it is not homework. but whatever.

Posting Permissions

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