5 Replies - 1365 Views - Last Post: 10 April 2012 - 06:51 PM Rate Topic: -----

#1 worldindus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 10-April 12

need clarification on a few of my queries

Posted 10 April 2012 - 02:40 PM

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:
Posted Image



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;


Is This A Good Question/Topic? 0
  • +

Replies To: need clarification on a few of my queries

#2 blackcompe  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1150
  • View blog
  • Posts: 2,528
  • Joined: 05-May 05

Re: need clarification on a few of my queries

Posted 10 April 2012 - 04:41 PM

Testing these queries would tell you what's wrong and what's right. I suggest you do that.

Quote

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


Your database should throw an error for that update. You need to insert values for all columns. I may be wrong since it's hypothetically possible for a SQL interpreter to insert NULLs for you; that's if the column allows NULLs.


Quote

Here is where I got confused.....
3.Show all information related (order, customer and product data) using an explicit join for order_id 2477843.


Your query is right, but you should qualify each column with a table name to make things a little clearer and avoid any ambiguity if two tables have the same column name. E.g.

SELECT C.last_name, P.product_id, O.order_id
FROM Customer AS C
     INNER JOIN Order AS O
          ON C.customer_id = O.customer_id
     INNER JOIN Product AS P
          ON P.product_id = O.product_id
WHERE O.order_id = '2477843'



Quote

4.Show the first_name, last_name, and the total amount of all orders for customer_id 87162412.


See the SUM function.

Quote

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


I'd try something like this, although I'm not sure it's correct since I don't have a test environment set up.

SELECT COUNT(*)
FROM Order AS O
WHERE O.order_id = '<order_id>'
AND O.total_price >= '5000'


Was This Post Helpful? 0
  • +
  • -

#3 worldindus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 10-April 12

Re: need clarification on a few of my queries

Posted 10 April 2012 - 04:56 PM

Thanks. I can't test it. All I have is the ER diagram to go off of. No actual database.
Was This Post Helpful? 0
  • +
  • -

#4 blackcompe  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1150
  • View blog
  • Posts: 2,528
  • Joined: 05-May 05

Re: need clarification on a few of my queries

Posted 10 April 2012 - 05:26 PM

Quote

Thanks. I can't test it. All I have is the ER diagram to go off of. No actual database.


It's not like you couldn't set up a small database. If you can write these queries, you can set up a database. Oh well.

This post has been edited by blackcompe: 10 April 2012 - 05:27 PM

Was This Post Helpful? 0
  • +
  • -

#5 worldindus  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 10-April 12

Re: need clarification on a few of my queries

Posted 10 April 2012 - 06:06 PM

I am more worried that I interpreted the question correctly than the actual code. Except for number 6, number 6 is really bugging me because quite frankly it is impossible to have a "boolean" in ms sql server so I don't know what the hell it is asking for, let alone what a flag is.
Was This Post Helpful? 0
  • +
  • -

#6 blackcompe  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1150
  • View blog
  • Posts: 2,528
  • Joined: 05-May 05

Re: need clarification on a few of my queries

Posted 10 April 2012 - 06:51 PM

Right, there's no boolean return value. I think the question is just asking that there be one column and row with either a 0 or 1, and I'm pretty sure that query should do it. As a matter of fact, that query should be:

SELECT COUNT(*) AS BOOLEAN_FLAG
FROM Order AS O
WHERE O.order_id = '<order_id>'
AND O.total_price >= '5000'



Remember that you're querying for a single order.

This post has been edited by blackcompe: 10 April 2012 - 06:52 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1