| 11. | 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’); |
| a. | Names of all the employees with at least one of their customers having a ‘GOOD’ rating. |
| b. | Names of all the employees with at most one of their customers having a ‘GOOD’ rating. |
| c. | Names of all the employees with none of their customers having a ‘GOOD’ rating. |
| d. | Names of all the employees with all their customers having a ‘GOOD’ rating. |
| View Answer Report Discuss Too Difficult! |
|
Answer: (d).Names of all the employees with all their customers having a ‘GOOD’ rating.
|
| 12. | The statement that is executed automatically by the system as a side effect of the modification of the database is |
| a. | backup |
| b. | assertion |
| c. | recovery |
| d. | trigger |
| View Answer Report Discuss Too Difficult! |
|
Answer: (d).trigger
|
| 13. | Which of the following command is used to delete a table in SQL? |
| a. | delete |
| b. | truncate |
| c. | remove |
| d. | drop |
| View Answer Report Discuss Too Difficult! |
|
Answer: (d).drop
|
| 14. | Consider the relation account (customer, balance) where customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. ties are not broke but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned Query1: select A.customer, count(B.customer) from account A, account B where A.balance <=B.balance group by A.customer Query2: select A.customer, 1+count(B.customer) from account A, account B where A.balance < B.balance group by A.customer Consider these statements about Query1 and Query2. 1. Query1 will produce the same row set as Query2 for some but not all databases. 2. Both Query1 and Query2 are correct implementation of the specification 3. Query1 is a correct implementation of the specification but Query2 is not 4. Neither Query1 nor Query2 is a correct implementation of the specification 5. Assigning rank with a pure relational query takes less time than scanning in decreasing balance order assigning ranks using ODBC. Which two of the above statements are correct? |
| a. | 2 and 5 |
| b. | 1 and 3 |
| c. | 1 and 4 |
| d. | 3 and 5 |
| View Answer Report Discuss Too Difficult! |
|
Answer: (c).1 and 4
|
| 15. | 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? |
| a. | All queries return identical row sets for any database |
| b. | Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets. |
| c. | There exist databases for which Query3 returns strictly fewer rows than Query2 |
| d. | There exist databases for which Query4 will encounter an integrity violation at runtime. |
| View Answer Report Discuss Too Difficult! |
|
Answer: (a).All queries return identical row sets for any database
|
| 16. | The following table has two attributes A and C where A is the primary key and C is the foreign key referencing A with on-delete cascade. A C ----- 2 4 3 4 4 3 5 2 7 2 9 5 6 4 The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2,4) is deleted is: |
| a. | (3,4) and (6,4) |
| b. | (5,2) and (7,2) |
| c. | (5,2), (7,2) and (9,5) |
| d. | (3,4), (4,3) and (6,4) |
| View Answer Report Discuss Too Difficult! |
|
Answer: (c).(5,2), (7,2) and (9,5)
|
| 17. | The relation book (title, price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list? select title from book as B where (select count(*) from book as T where T.price > B.price) < 5 |
| a. | Titles of the four most expensive books |
| b. | Title of the fifth most inexpensive book |
| c. | Title of the fifth most expensive bookTitles of the five most expensive books |
| d. | Titles of the five most expensive books |
| View Answer Report Discuss Too Difficult! |
|
Answer: (d).Titles of the five most expensive books
|
| 18. | Consider the following relation schema pertaining to a students database: Student (rollno, name, address) Enroll (rollno, courseno, coursename) where the primary keys are rollno,courseno. 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 ? |
| a. | 8, 0 |
| b. | 120, 8 |
| c. | 960, 8 |
| d. | 960, 120 |
| View Answer Report Discuss Too Difficult! |
|
Answer: (a).8, 0
|
| 19. | The employee information in a company is stored in the relation Employee (name, sex, salary, deptName) (name is primary key ) Consider the following SQL query select deptName from Employee where sex = 'M' group by deptName having avg (salary) > (select avg (salary) from Employee) It returns the names of the department in which |
| a. | the average salary is more than the average salary in the company |
| b. | the average salary of male employees is more than the average salary of all male employees in the company |
| c. | the average salary of male employees is more than the average salary of employees in the same department |
| d. | the average salary of male employees is more than the average salary in the company |
| View Answer Report Discuss Too Difficult! |
|
Answer: (d).the average salary of male employees is more than the average salary in the company
|
| 20. | Consider the set of relations shown below and the SQL query that follows. Students: (Roll_number, Name, Date_of_birth) Courses: (Course number, Course_name, Instructor) Grades: (Roll_number, Course_number, Grade) select distinct Name from Students, Courses, Grades where Students. Roll_number = Grades.Roll_number and Courses.Instructor = Korth and Courses.Course_number = Grades.Course_number and Grades.grade = A Which of the following sets is computed by the above query? |
| a. | Names of students who have got an A grade in all courses taught by Korth |
| b. | Names of students who have got an A grade in all courses |
| c. | Names of students who have got an A grade in at least one of the courses taught by Korth |
| d. | None of the above |
| View Answer Report Discuss Too Difficult! |
|
Answer: (c).Names of students who have got an A grade in at least one of the courses taught by Korth
|
