SQL

Question 21

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
• 2
• 3
• 4

Question 22

Student (school-id, sch-roll-no, sname, saddress)
School (school-id, sch-name, sch-address, sch-phone)
Enrolment(school-id sch-roll-no, erollno, examname)
ExamResult(erollno, examname, marks)
What does the following SQL query output?

C

SELECT	sch-name, COUNT (*)
FROM	School C, Enrolment E, ExamResult R
WHERE	E.school-id = C.school-id
AND
E.examname = R.examname AND E.erollno = R.erollno
AND
R.marks = 100 AND S.school-id IN (SELECT school-id
                                FROM student
                                GROUP BY school-id
                                 HAVING COUNT (*) > 200)
GROUP By school-id
 /* Add code here. Remove these lines if not writing code */ 

• for each school with more than 200 students appearing in exams, the name of the school and the number of 100s scored by its students
• for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students
• for each school with more than 200 students in it, the name of the school and the number of its students scoring 100 in at least one exam
• nothing; the query has a syntax error

Question 23

Which of the following is/are correct?

• An SQL query automatically eliminates the duplicates
• An SQL query will not work if there are no indexes on the relations
• SQL permits attribute names to be repeated in the same relation
• None of the above

Question 24

Suppose we have a database consisting of the following three relations.

FREQUENTS(student, parlor) giving the parlors each student visits.
SERVES(parlor, ice-cream) indicating what kind of ice-creams each parlor serves.
LIKES(student, ice-cream) indicating what ice-creams each parlor serves.

(Assuming that each student likes at least one ice-cream and frequents at least one parlor)

Express the following in SQL: Print the students that frequent at least one parlor that serves some ice-cream that they like.

Question 25

Consider the following relational database schemes:

COURSES(Cno, name)
PRE-REQ(Cno, pre_Cno)
COMPLETED(student_no, Cno)

COURSES give the number and the name of all the available courses. PRE-REQ gives the information about which course are pre-requisites for a given course. COMPLETED indicates what courses have been completed by students. Express the following using relational algebra:

List all the courses for which a student with student_no 2310 has completed all the 
pre-requisites.

Question 26

Consider the following relational database schema:

EMP (eno name, age)
PROJ (pno name)
INVOLVED (eno, pno)

EMP contains information about employees. PROJ about projects and INVOLVED about which employees involved in which projects. The underlined attributes are the primary keys for the respective relations. a). What is the relational algebra expression containing one or more of {σ,π,x,u,−} which is equivalent to SQL query.

select eno
from EMP, INVOLVED 
where EMP.eno=INVOLVED.eno  
and INVOLVED.pno=3

b). State in English (in not more than 15 words). What the following relational algebra expressions are designed to determine

(i) πeno(INVOLVED) − πeno((πeno(INVOLVED) X πpno(PROJ))−INVOLVED)
(ii) πage(EMP) − πEage<EMP.age(ρE(EMP) x EMP))

(Note: ρ_E(EMP) conceptually makes a copy of EMP and names it K (ρ is called the rename operator))

Question 27

Consider the following relational schema:

EmployeeDetail (EmpId, FullName, ManagerID, DateOfJoining)
EmployeeSalary (EmpID, Project, Salary)

Consider the following relational query on the above database:

SELECT Salary
FROM EmployeeSalary Emp1
WHERE 2 = (
                SELECT COUNT( DISTINCT ( Emp2.Salary ) )
                FROM EmployeeSalary Emp2
                WHERE Emp2.Salary > Emp1.Salary
            )

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 highest salary from table
• find the 2nd highest salary from table
• find the 3rd highest salary from table
• None of above

Question 28

Consider the following two tables and four queries in SQL. Book (isbn, bname), Stock (isbn, copies) Query 1:

SELECT B.isbn, S.copies
FROM Book B INNER JOIN Stock S
ON B.isbn = S.isbn;

Query 2:

SELECT B.isbn, S.copies
FROM B B LEFT OUTER JOIN Stock S
ON B.isbn = S.isbn;

Query 3:

SELECT B.isbn, S.copies
FROM Book B RIGHT OUTER JOIN Stock S
ON B.isbn = S.isbn;

Query 4:

SELECT B.isbn, S.copies
FROM B B FULL OUTER JOIN Stock S
ON B.isbn = S.isbn;

Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?

• Query 1
• Query 2
• Query 3
• Query 4

Question 29

Which of the following is/are true with reference to ‘view’ in DBMS ? (a) A ‘view’ is a special stored procedure executed when certain event occurs. (b) A ‘view’ is a virtual table, which occurs after executing a pre-compiled query. code:

• Only (a) is true
• Only (b) is true
• Both (a) and (b) are true
• Neither (a) nor (b) are true

Question 30

In SQL, __________ is an Aggregate function.

• SELECT
• CREATE
• AVG
• MODIFY