2 Replies - 887 Views - Last Post: 09 April 2011 - 08:06 PM Rate Topic: -----

#1 mon mon   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 128
  • Joined: 31-December 09

Question in query optimization

Posted 09 April 2011 - 06:20 PM

Quetion is :
the schema:
Product (ProductId, ProductName, Unit price,InStockQuantity, SupplierId)
Supplier(SupplierId, SupplierName, Country, City, Contact#)
OrderDetail(ProductId,OrderId, Quantity,TotalPrice)
Order(OrderID, OrderDate, RequiredDate)
/////////////////////////////////////////////////////////

Select Product.ProductName, OrderDetail. Quantity
From product, supplier, OrderDetail , order
Where (OrderDetail. Quantity> 400
Or ((Supplier.City = 'Berlin' And order.OrderDate > 26- 3-2007) Or OrderDetail. Quantity<= 400))
Or( Product.SupplierId = Supplier.SupplierId
And OrderDetail.ProductId =  Product.ProductId
And OrderDetail.OrderID =  Order. OrderID)


/////////////////////////////////////////////// "Question"
If you know that Product table has 100 tuples, Order table has 200 tuples, OrderDetail table has 300 tuples, Supplier table has 450 tuples, and no tuples satisfy the condition OrderDetail.ProductId = Product.ProductId. how many tuples will returned ?
////////////////////////////////////////////////////////////////////////////////
#my solution is :(will not return any thing ) because no tuples satisfy the condition OrderDetail.ProductId = Product.ProductId and there are projection for (Product.ProductName,OrderDetail.Quantity)

Is it correct?

Is This A Good Question/Topic? 0
  • +

Replies To: Question in query optimization

#2 macosxnerd101   User is offline

  • Games, Graphs, and Auctions
  • member icon




Reputation: 12317
  • View blog
  • Posts: 45,417
  • Joined: 27-December 08

Re: Question in query optimization

Posted 09 April 2011 - 06:22 PM

Moved to Databases.

That is correct. :)
Was This Post Helpful? 0
  • +
  • -

#3 baavgai   User is online

  • Dreaming Coder
  • member icon


Reputation: 7183
  • View blog
  • Posts: 14,971
  • Joined: 16-October 07

Re: Question in query optimization

Posted 09 April 2011 - 08:06 PM

Tuples? What are tuples? If this is a SQL RDBMS, then there are Tables with Rows.

Your query has issues, since it simply doesn't join properly. Let's format it a little:
Select Product.ProductName, OrderDetail. Quantity
From product, supplier, OrderDetail , order
Where 
	(OrderDetail. Quantity> 400
		Or (
			(Supplier.City = 'Berlin' And order.OrderDate > '26- 3-2007') 
				Or OrderDetail. Quantity<= 400
		)
	)
	Or ( 
		Product.SupplierId = Supplier.SupplierId 
			And OrderDetail.ProductId =  Product.ProductId 
			And OrderDetail.OrderID =  Order. OrderID
		)



So, if "OrderDetail. Quantity> 400" I ignore the joins? I don't know that a decent database would actually let you get away with your logic. If it did, you're looking at some unexpected results.

Written correctly, the basic table joins would look like:
Select *
	From OrderDetail
		inner join Product 
			on OrderDetail.ProductId =  Product.ProductId
		inner join Order 
			on OrderDetail.OrderID =  Order.OrderID
		inner join Supplier 
			on Product.SupplierId = Supplier.SupplierId 



Now that you have a clean join, you can worry out your wheres.
(OrderDetail. Quantity> 400
	Or (
		(Supplier.City = 'Berlin' And order.OrderDate > '26- 3-2007') 
			Or OrderDetail. Quantity<= 400
	)



Let's contemplate that for a moment:
OrderDetail. Quantity> 400
Or OrderDetail. Quantity<= 400



You can stop now, you've already asked for everything, the Supplier.City bit is ignored.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1