need urgent help with sql queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need urgent help with sql queries

Author

Topic

worldindus
Starting Member

2 Posts

Posted - 2012-04-10 : 16:44:22

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:

[URL=http://imageshack.us/photo/my-images/21/erdiagram.jpg/]

[/URL]

Thanks.

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);

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

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.
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;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-10 : 17:42:58

We don't usually do homework here, but since you gave it a good shot here's some help.
Your answers for 1,2 and 3 look fine.
For 4, you may need to aggregate the data, i.e., sum up the total_price

SELECT first_name,last_name, SUM(total_price) as total_amount
FROM Customer, Order
WHERE Customer. customer_id=Order.customer_id AND customer.customer_id=87162412;
GROUP BY first_name,last_name

5.Show the customer_id, first_name, and last_name of any customers having orders totaling more than $5000 to date.
SELECT customer_id, first_name, last_name
FROM Customer,Order
WHERE Customer.customer_id=Order.customer_id
GROUP BY customer_id, first_name, last_name
HAVING SUM(total_price) > 5000 -- why is this different?

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;

This looks ok, but you don't really need to convert it to a bit, unless you thing that's what the prof is looking for

Jim

Everyday I learn something that somebody else already knew

worldindus
Starting Member

2 Posts

Posted - 2012-04-10 : 18:24:08

It's actually not homework.
for 4 and 5, I think you are right, I was on the fence about using SUM() but I really didn't know what the question was asking. Also, I don't know if total_price is for the entire order or if it is essentially price_per_unit*q

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources