#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    2
    Rep Power
    0

    Need help with a few queries


    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.
    Here is the ER diagram:
    Customer
    customer_id
    first_name
    last_name
    address_1
    address_2
    city
    state
    country
    zip
    discount
    active

    Product
    product_id
    product_name
    desc
    manufacturer
    quantity
    sku_number
    unit_price
    vendor

    Order
    order_id
    date
    customer_id
    product_id
    billing_name
    billing_addr1
    billing_addr2
    billing_city
    billing_state
    billing_country
    billing_zip
    quantity_sold
    total_price





    Thanks.

    1) Look for all customers with a name that includes the string 'Johnson'.
    my answer:
    Code:
    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:

    Code:
    INSERT INTO Product (product_name,unit_price) VALUES ('Widget',5.00);
    Here is where I got confused.....
    3.Show all information related (order, customer and product data) using an explicit join for order_id 2477843.


    My Answer:
    Code:
    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.

    Code:
    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.
    Code:
    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.)

    Code:
    Select order_id, CASE WHEN total_price>=5000 THEN cast(1 as bit) ELSE cast(0 as bit) as totalgreater5k
    FROM Order;
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,740
    Rep Power
    1959
    Quesiton 1 to 3 looks ok. Question 6 is also out of my knowledge.

    When you make a JOIN you should keep using the explicit syntax, like you have in question 3.

    Regarding question 4, i would guess you should return the sum of all orders total price, using the aggregate function SUM and GROUP BY:
    sql Code:
     
    SELECT first_name,last_name, SUM(total_price) AS total_amount
    FROM Customer
    INNER JOIN ORDER ON Customer.customer_id=ORDER.customer_id  
    WHERE customer.customer_id=87162412;
    GROUP BY first_name, last_name


    Question 5. is then build further on the previous where you have to filter on the total instead of user id.
    When you have to filter on a value from aggregate function like SUM, you will have to use the HAVING clause:
    sql Code:
    SELECT first_name,last_name, SUM(total_price) AS total_amount
    FROM Customer
    INNER JOIN ORDER ON Customer.customer_id=ORDER.customer_id  
    GROUP BY first_name, last_name
    HAVING SUM(total_price) > 5000
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by MrFujin
    Quesiton 1 to 3 looks ok. Question 6 is also out of my knowledge.

    When you make a JOIN you should keep using the explicit syntax, like you have in question 3.

    Regarding question 4, i would guess you should return the sum of all orders total price, using the aggregate function SUM and GROUP BY:
    sql Code:
     
    SELECT first_name,last_name, SUM(total_price) AS total_amount
    FROM Customer
    INNER JOIN ORDER ON Customer.customer_id=ORDER.customer_id  
    WHERE customer.customer_id=87162412;
    GROUP BY first_name, last_name


    Question 5. is then build further on the previous where you have to filter on the total instead of user id.
    When you have to filter on a value from aggregate function like SUM, you will have to use the HAVING clause:
    sql Code:
    SELECT first_name,last_name, SUM(total_price) AS total_amount
    FROM Customer
    INNER JOIN ORDER ON Customer.customer_id=ORDER.customer_id  
    GROUP BY first_name, last_name
    HAVING SUM(total_price) > 5000
    Thank you for your reply. I certainly appreciate it. I also thought about using sum on 4 and 5 but couldn't figure out if that was what it was asking for. The questions really suck! horrifically vague.

IMN logo majestic logo threadwatch logo seochat tools logo