Database miscellaneous

Database miscellaneous

Easy Questions
Moderate Questions

Database

Database miscellaneous

Direction: Consider the following relational schemas :
Suppliers (sid : integer, sname : string, city : string, street : string)
Parts (pid : integer, pname : string, color : string)
Catalog (sid : integer, pid : integer, cost : real)

  1. Consider the following relational query on the above database :
    SELECT        S.sname
    FROM      Suppliers S
    WHERE S.sid NOT IN (SELECT C. sid
        FROM Catalog C
        WHERE C.pid NOT IN (SELECT P.pid
           FROM Parts P
           WHERE P.color < > ‘blue’))
    Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?








  1. View Hint View Answer Discuss in Forum

    S name is selected form Supplier S. Now this S.sid should not be in C.sid of Catalog C.
    Therefore, it will find the names of all suppliers who have supplied a non-blue part.

    Correct Option: A

    S name is selected form Supplier S. Now this S.sid should not be in C.sid of Catalog C.
    Therefore, it will find the names of all suppliers who have supplied a non-blue part.

  1. Given the following statements: S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL. S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition.
    CREATE TABLE S (
        a INTEGER,
        d INTEGER,
        e INTEGER,
        PRIMARY KEY (d),
        FOREIGN KEY (a) references R)
    Which one of the following statements is CORRECT ?








  1. View Hint View Answer Discuss in Forum

    Both S1 and S2 are false.

    Correct Option: D

    Both S1 and S2 are false.

Direction: Consider the following relations A, B and C :

  1. How many tuples does the result of the following relational algebra expression contain? Assume that the schema of A ∪ B is the same as that of A.
    (A ∪ B)A.ld>40∨C.ld<15C








  1. View Hint View Answer Discuss in Forum

    A ∪ B will have 5 rows because two rows in B are already in A. When cross product of A ∪ B and C is done, the result is:

    The condition is: either A.Id > 40 or C.Id < 15. For first 5 rows, C. Id < 15. For 8th and 10th rows, A. Id > 40. So total rows in result will be 7. So correct option is (A)

    Correct Option: A

    A ∪ B will have 5 rows because two rows in B are already in A. When cross product of A ∪ B and C is done, the result is:

    The condition is: either A.Id > 40 or C.Id < 15. For first 5 rows, C. Id < 15. For 8th and 10th rows, A. Id > 40. So total rows in result will be 7. So correct option is (A)

  1. How many tuples does the result of the following SQL query contain?
    SELECT A. Id
    FROM A
    WHERE A. Age > ALL (SELECT B. Age
    FROM B
    WHERE B. Name = ‘Arun’)








  1. View Hint View Answer Discuss in Forum

    The meaning of "ALL" is the A. Age should be greater than all the values returned by the sub-query. There is no entry with name "arun" in table B. So the subquery will return NULL. If a sub-query returns an empty table or NULL, then the condition becomes true. Therefore, all the three row id's of A will be selected from table A.

    Correct Option: B

    The meaning of "ALL" is the A. Age should be greater than all the values returned by the sub-query. There is no entry with name "arun" in table B. So the subquery will return NULL. If a sub-query returns an empty table or NULL, then the condition becomes true. Therefore, all the three row id's of A will be selected from table A.

  1. Given the following schema: employees (emp-id, first-name, last-name, hire-date, deptid, salary)
    departments (dept-id, dept-name, manager-id, location-id)
    You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:
    SQL > SELECT last-name, hire-date
            FROM employees
            WHERE (dept-id, hire-date) IN
            (SELECT dept-id, MAX (hire-date)
            FROM employees JOIN departments USING (dept-id)
            WHERE location-id = 1700
            GROUP BY dept-id),
    What is the outcome?








  1. View Hint View Answer Discuss in Forum

    It executes and gives the correct result. As we see there is nothing wrong with the Given query so it displays the expected result.

    Correct Option: B

    It executes and gives the correct result. As we see there is nothing wrong with the Given query so it displays the expected result.