SQL

Question 51

The relation scheme given below is used to store information about the employees of a company, where empId is the key and deptId indicates the department to which the employee is assigned. Each employee is assigned to exactly one department.

emp(empId, name, gender, salary, deptId) 

Consider the following SQL query:

select deptId, count(*)
from emp
where gender = “female” and salary > (select avg(salary)from emp)
group by deptId; 

The above query gives, for each department in the company, the number of female employees whose salary is greater than the average salary of

• employees in the department
• employees in the company
• female employees in the department
• female employees in the company

Question 52

Consider the relations r₁(P, Q, R) and r₂(R, S, T) with primary keys P and R respectively. The relation r₁ contains 2000 tuples and r₂ contains 2500 tuples. The maximum size of the join r₁⋈ r₂ is :  

• 2000
• 2500
• 4500
• 5000

Question 53

A library relational database system uses the following schema

USERS (User#, UserName, HomeTown)
BOOKS (Book#, BookTitle, AuthorName)
ISSUED (Book#, User#, Date)

Explain in one English sentence, what each of the following relational algebra queries is designed to determine  

[caption width="800"] [/caption]

•  Show all the Book Title which have been issued by User# 6.

•  The user who’s home town is Delhi and issued a book, Show all the name authors of the book he/she have issued.

Question 54

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?

• S1 is TRUE and S2 is FALSE.
• Both S1 and S2 are TRUE.
• S1 is FALSE and S2 is TRUE.
• Both S1 and S2 are FALSE.

Question 55

Consider the following relational schema:

  employee(empId, empName, empDept)
  customer(custId, custName, salesRepId, rating)

salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

SELECT empName
       FROM employee E
       WHERE NOT EXISTS ( SELECT custId
                          FROM customer C
                          WHERE C.salesRepId = E.empId
                            AND C.rating <> `GOOD`);

• Names of all the employees with at least one of their customers having a ‘GOOD’ rating.
• Names of all the employees with at most one of their customers having a ‘GOOD’ rating.
• Names of all the employees with none of their customers having a ‘GOOD’ rating.
• Names of all the employees with all their customers having a ‘GOOD’ rating.

Question 56

Consider the relation "enrolled(student, course)" in which (student, course) is the primary key, and the relation "paid(student, amount)" where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the following four queries:

Query1: select student from enrolled where 
        student in (select student from paid)
Query2: select student from paid where 
        student in (select student from enrolled)
Query3: select E.student from enrolled E, paid P 
         where E.student = P.student
Query4:  select student from paid where exists
        (select * from enrolled where enrolled.student
         = paid.student) 

Which one of the following statements is correct?

• All queries return identical row sets for any database
• Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets.
• There exist databases for which Query3 returns strictly fewer rows than Query2
• There exist databases for which Query4 will encounter an integrity violation at runtime.

Question 57

Database table by name Loan_Records is given below.

Borrower    Bank_Manager   Loan_Amount
 Ramesh      Sunderajan     10000.00
 Suresh      Ramgopal       5000.00
 Mahesh      Sunderajan     7000.00

What is the output of the following SQL query?

SELECT Count(*) 
FROM  ( ( SELECT Borrower, Bank_Manager 
          FROM Loan_Records) AS S 
          NATURAL JOIN ( SELECT Bank_Manager, Loan_Amount 
                         FROM Loan_Records) AS T );

• 3
• 9
• 5
• 6

Question 58

The statement that is executed automatically by the system as a side effect of the modification of the database is

• backup
• assertion
• recovery
• trigger

Question 59

Consider the following relation schema pertaining to a students database:

Student (rollno, name, address)
Enroll (rollno, courseno, coursename)

where the primary keys are shown underlined. The number of tuples in the Student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student * Enroll), where \'*\' denotes natural join ?

• 8, 8
• 120, 8
• 960, 8
• 960, 120

Question 60

Consider the following relational schema: 

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)

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?

• Find the names of all suppliers who have supplied a non-blue part.
  

• Find the names of all suppliers who have not supplied a non-blue part.
  

• Find the names of all suppliers who have supplied only blue parts.
  

• Find the names of all suppliers who have not supplied only blue parts.
  

• None