Database SQL questions: You can download the table from this link: https://drive

Question

Database SQL questions:

You can download the table from this link:

https://drive.google.com/file/d/0B0kVn308bqFuZkFULTBSU1lzdEk/view?usp=sharing

1) How long will it take to perform a linear search of a database table that is stored in 10 000 disk blocks? Each block is 4000 bytes, the disk access time is 10 ms, and the disk transfer time is 100 MB/sec. (Give you answer in milliseconds (ms).)

2) How many contributions were made from contributors with an occupation that contains the string “CEO”? Output a single number.

3) Show, for each zip that appears in the contributor table, the number of contributions greater than $ 2000. Every zip in the contributor table should appear in the query result, even if there are no contributions > $2000 for that zip.

Explanation / Answer

1)

       Given disk transfer time is 100 MB/sec

        In 1 sec we can transfer 100 MB data

        To transfer a block of 4000 bytes we need 4000 / 100 M secs = 0.04 msec

        For 1 block it will take 0.04 msec , for 10000 blocks it is = 10000 * 0.04 msec = 400 msec

        Here disk access time = 10 ms

        Hence total time is = 400 + 10 = 410 ms

2)

select count(contbr_id) from contributor where occupation ='CEO';

    This query will count all the contributors whose occupation is CEO.

3)

       If we need zip's having count > 2000 then use the below query

          select zip,count(contbr_id) from contributor group by zip having count(contbr_id) > 2000 ;

      If we need all zip's even if they dont have count > 2000 then use the below

          select zip,count(contbr_id) from contributor group by zip;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.