SQL (original) (raw)

**Table A:

**Table B:

Table C:

Consider the above tables A, B and C. How many tuples does the result of the following SQL query contains?

SELECT A.id FROM A WHERE A.age > ALL (SELECT B.age FROM B WHERE B. name = "arun")

A table T(X, Y) initially has one record (1, 1). New records are inserted 128 times with:

X = MX + 1, Y = 2*MY + 1

where MX and MY are the current maximums of X and Y.What will MAX(X) and MAX(Y) be after all insertions?

SELECT Y FROM T WHERE X=7;

Database table by name Loan_Records is given below.

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 );

A relational schema for a train reservation database is given below. Passenger (pid, pname, age) Reservation (pid, class, tid)

**Table: Passenger

**Table : Reservation

What pids are returned by the following SQL query for the above instance of the tables?

SLECT pid FROM Reservation , WHERE class ‘AC’ AND EXISTS (SELECT * FROM Passenger WHERE age > 65 AND Passenger. pid = Reservation.pid)

Let R and S be relational schemes such that R={a,b,c} and S={c}. Now consider the following queries on the database:

IV) SELECT R.a, R.b
FROM R,S
WHERE R.c=S.c

Which of the above queries are equivalent?

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.

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

• 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.

Consider the set of relations given 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 =Sriram
AND Courses.Course_number = Grades.Course_number
AND Grades.Grade = A

Which of the following sets is computed by the above query?

• Names of Students who have got an A grade in all courses taught by Sriram
• Names of Students who have got an A grade in all courses
• Names of Students who have got an A grade in at least one of the courses taught by Sriram

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 non blue parts.
• Find the names of all suppliers who have not supplied only non-blue parts.

Which of the following is true ? I. Implementation of self-join is possible in SQL with table alias. II. Outer-join operation is basic operation in relational algebra. III. Natural join and outer join operations are equivalent.

There are 66 questions to complete.

Take a part in the ongoing discussion