Database miscellaneous

Database miscellaneous

Easy Questions
Moderate Questions

Database

Database miscellaneous
  1. Consider the following database table named water_schemes:

    The number of tuples returned by the following SQL query is ________.
    with total (name, capacity) as
        select district_name, sum (capacity)
        from water_schemes
        group by district_name
    with total_avg (capacity) as
        select avg (capacity)
        from total
    select name
        from total, total_avg
        where total.capacity ≠ total_avg.capacity








  1. View Hint View Answer Discuss in Forum

    First we should perform group by group. We get ajmer 1
    bikaner 3
    charu 2
    dungargarh 1
    Now we should perform total capacity we will get 100. Now perform avg. capacity on total 100 / 4 = 25. bikaner = 40 which is greater than 25 charu is 30 which is also greater than 25. Hence charu and bikaner will be printed therefore answer is 2 tuples.

    Correct Option: A

    First we should perform group by group. We get ajmer 1
    bikaner 3
    charu 2
    dungargarh 1
    Now we should perform total capacity we will get 100. Now perform avg. capacity on total 100 / 4 = 25. bikaner = 40 which is greater than 25 charu is 30 which is also greater than 25. Hence charu and bikaner will be printed therefore answer is 2 tuples.

  1. Consider a database that has the relation schema CR(StudentName. CourseName). An instance of the schema CR is as given below.

    The following query is made on the database.
    T1 ← πCourseName (σStudentName= ‘SA’(CR))
    T2 ← CR ÷ T1
    The number of rows in T2 is _______.








  1. View Hint View Answer Discuss in Forum

    The given query is made on the database are:
    1.
    For T1 Result: find out the course name for the student name is ‘SA’ i.e.

    So, T1 = {CA, CB, CC} The number of rows in T1 is 3.
    2. T1 ← (CR ÷ T1)

    For T2 Result: find out the student name for which every course name of T1 are {CA, CB, CC} is:
    So, T2 = {SA, SC, SD, SF} Hence, the number of rows in T2 is 4.

    Correct Option: C

    The given query is made on the database are:
    1.
    For T1 Result: find out the course name for the student name is ‘SA’ i.e.

    So, T1 = {CA, CB, CC} The number of rows in T1 is 3.
    2. T1 ← (CR ÷ T1)

    For T2 Result: find out the student name for which every course name of T1 are {CA, CB, CC} is:
    So, T2 = {SA, SC, SD, SF} Hence, the number of rows in T2 is 4.

  1. Consider a database that has the relation schema EMP (EmpId. EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below.

    The output of executing the SQL query is_______.








  1. View Hint View Answer Discuss in Forum


    Result of outer query or Avg (NUM) =
    13
    		 = 2.6
    5

    Correct Option: B


    Result of outer query or Avg (NUM) =
    13
    		 = 2.6
    5

  1. Given relations r(w, x) and s(y, z), the result of select distinct w, x from r, s is guaranteed to be same as r, provided








  1. View Hint View Answer Discuss in Forum

    Distinct keyword tells that it will give duplicate entries only once and if r has duplicate entries than it will not show them more than once which tells us that if answer is to be same as r then r should not have any duplicate entries.
    Options (c) and (d) are completely wrong but in option (b) its also not necessary that s must also have no duplicate entries thus the answer is option (a).

    Correct Option: A

    Distinct keyword tells that it will give duplicate entries only once and if r has duplicate entries than it will not show them more than once which tells us that if answer is to be same as r then r should not have any duplicate entries.
    Options (c) and (d) are completely wrong but in option (b) its also not necessary that s must also have no duplicate entries thus the answer is option (a).

  1. Let r and s be two relations over the relational schemas R and S respectively, and let A be an attribute in R. Then the relational algebra expression A a (r s) s = >< is always equal to








  1. View Hint View Answer Discuss in Forum

    Relational schemas R and S A be an attribute in R.
    SA=a (r) ⋈ s
    The next step is to select A = in relation
    Now, since A is attribute of R and r is also a relation of R.
    Here write expression is to be performed.
    σA=a (r ⋈ s) = σA=a (r ) ⋈ s

    Correct Option: C

    Relational schemas R and S A be an attribute in R.
    SA=a (r) ⋈ s
    The next step is to select A = in relation
    Now, since A is attribute of R and r is also a relation of R.
    Here write expression is to be performed.
    σA=a (r ⋈ s) = σA=a (r ) ⋈ s