Jump to content
Sadikhov IT Certification forums
Sign in to follow this  

urgent help on SQL

Recommended Posts

anyone plz can help me with those Qs.


Q1: For the Supplier (S), Part(P), and Supplier-Part (SP) tables shown below, assume the

following SQL statement and the given query tree.

S (S#, NAME) /* Suppliers Table

SP (S#, P# , QTY) /* Every suppliers supplying every part

P (P#, NAME) /* Parts Table

Select S.NAME

From S, SP, P

Where S.S# = SP.S#

AND SP.P# = P.P#

AND P.P# = "P3";

Assume there are 4000 usable bytes in a page after the overhead. We have 1000 suppliers and

50 parts in the database. Every supplier supplies every one the 50 parts. Further, assume that we

have 22 buffers and the BTree index height is 3. What is the most efficient way of performing

this plan in terms of number disk I/O?


where : S 1000row(10pages),SP 50000rows(100pages) Cluster Btree index on S#, p 50rows(20pages) un-clustered Btree index on P#

the condition is: you should first join S &SP ,and then join them to P and then PJ where P#=P3




Q2: Assume our Bank has 1000 accounts that are uniformly distributed over 20 branches. In the

following query tree, assume that the selectivity of the first SL is 20% and of the second SL is

40%. How many rows does this plan process? Show the cost of each step and the total cost.


Select cname, balance

from account, branch

Where balance>1000 and bcity=’york’.


And the first selection of balance>1000 should be bushed ahead of the join condition

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this