A relational database contains two tables Student and Performance as shown below:

The primary key of the Student table is Roll_no. For the Performance table, the columns Roll_no. and Subject_code together from the primary key. Consider the SQL query given below:

SELECT S.Student_name, sum (P.Marks)
       FROM Student S, Performance P
       WHERE P.Marks > 84
       GROUP BY S.Student_name;

The number of rows returned by the above SQL query is _____.

Consider the following two tables and four queries in SQL.

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 Book 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 Book 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?

Consider a database that has the relation schema EMP (EmpId, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below.

The output of executing the SQL query is ___________.

Consider the following database table named top_scorer.

SELECT ta.player FROM top_scorer AS ta
WHERE ta.goals > ALL (SELECT tb.goals
                  FROM top_scorer AS tb
                  WHERE tb.country = 'Spain')
AND ta.goals > ANY (SELECT tc.goals
                  FROM top_scorer AS tc
                  WHERE tc.country = 'Germany')

The number of tuples returned by the above SQL query is _____.

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

SELECT operation in SQL is equivalent to

Consider the following relations:

Consider the following SQL query.

SELECT S. Student_Name, sum(P.Marks)
     FROM Student S, Performance P
     WHERE S.Roll_No = P.Roll_No
     GROUP BY S.Student_Name

The number of rows that will be returned by the SQL query is _________.

Consider the following relation

  Cinema (theater, address, capacity)

Which of the following options will be needed at the end of the SQL query

SELECT P1. address
FROM Cinema P1

Such that it always finds the addresses of theaters with maximum capacity?

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?

Given the following schema:

     employees(emp-id, first-name, last-name, hire-date, dept-id, 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?

SQL allows tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:

    select * from R where a in (select S.a from S)

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

Which of the  following statements are TRUE about an SQL query?

P:An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause.
Q:An SQL query can contain a HAVING clause even if it has a GROUP BY clause.
R: All attributes used in the GROUP BY clause must appear in the SELECT clause.
S: Not all attributes used in the GROUP BY clause need to appear in the SELECT clause

Consider the following relations A, B, C.

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

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

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

Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table. Let MX and My denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out?

SELECT Y FROM T WHERE X=7;

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

Table: Passenger
pid   pname   age
-----------------
 0    Sachin   65
 1    Rahul    66
 2    Sourav   67
 3    Anil     69

Table : Reservation
pid  class  tid
---------------
 0    AC   8200
 1    AC   8201
 2    SC   8201
 5    AC   8203
 1    SC   8204
 3    AC   8202

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

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

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?

Consider the table employee(empId, name, department, salary) and the two queries Q₁,Q₂ below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements is TRUE for any arbitrary employee table?

Q1 : Select e.empId
     From employee e
     Where not exists
        (Select * From employee s where s.department = “5” and s.salary >= e.salary)
Q2 : Select e.empId
     From employee e
     Where e.salary > Any
       (Select distinct salary From employee s Where s.department = “5”)

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?

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?

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. Assume that amounts 6000, 7000, 8000, 9000 and 10000 were each paid by 20% of the students. Consider these query plans (Plan 1 on left, Plan 2 on right) to "list all courses taken by students who have paid more than x".

A disk seek takes 4ms, disk data transfer bandwidth is 300 MB/s and checking a tuple to see if amount is greater than x takes 10 micro-seconds. Which of the following statements is correct?

     select title
     from book as B
     where (select count(*)
        from book as T
        where T.price > B.price) < 5 

The employee information in a company is stored in the relation

Employee (name, sex, salary, deptName)
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

Consider the following SQL query

   select distinct al, a2,........., an
   from rl, r2,........, rm
   where P 

For an arbitrary predicate P, this query is equivalent to which of the following relational algebra expressions?

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?

Consider a relation geq which represents “greater than or equal to”, that is, (x,y) ∈ geq only if y >= x.

create table geq
(  Ib integer not null
   ub integer not null
   primary key 1b
   foreign key (ub) references geq on delete cascade ) 

Which of the following is possible if a tuple (x,y) is deleted?

Given relations r(w, x) and s(y, z), the result of

    select distinct w, x
    from r, s  

is guaranteed to be same as r, provided

In SQL, relations can contain null values, and comparisons with null values are treated as unknown. Suppose all comparisons with a null value are treated as false. Which of the following pairs is not equivalent?

Consider a bank database with only one relation
transaction (transno, acctno, date, amount)
The amount attribute value is positive for deposits and negative for withdrawals.

(a) Define an SQL view TP containing the information.
(acctno, T1.date, T2.amount)
for every pair of transactions T1, T2 such that T1 and T2 are transaction on the same account and the date of T2 is ≤ the date of T1.

(b) Using only the above view TP, write a query to find for each account the minimum balance it ever reached (not including the 0 balance when the account is created). Assume there is at most one transaction per day on each account, and each account has had atleast one transaction since it was created. To simply your query, break it up into 2 steps by defining an intermediate view V.

Which of the following is/are correct?

Consider the set of relations

EMP(Employee-no, Dept-no, Employee-name, Salary)
DEPT(Dept-no, Dept-name, Location) 

Write an SQL query to:
(a) Find all employee names who work in departments located at "Calcutta" and whose salary is greater than Rs. 50,000.
(b) Calculate, for each department number, the number of employees with a salary greater than Rs. 100,000.

(a) 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.

(b) In a computer system where the 'best-fit' algorithm is used for allocating 'jobs' to 'memory partitions', the following situation was encountered:

When will the 20K job complete? Note - This question was subjective type.

Consider the following database relations containing the attributes

Book_id 
Subject_Category_of_book
Name_of_Author
Nationality_of_Author 
with Book_id as the Primary Key. 

(a) What is the highest normal form satisfied by this relation?

(b) Suppose the attributes Book_title and Author_address are added to the relation, and the primary key is changed to (Name_of_Author, Book_Title), what will be the highest normal form satisfied by the relation?

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.

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

(a) σ User #=6 (11 User #, Book Title ((USERS ISSUED) BOOKS))
(b) σ Author Name (BOOKS (σ Home Town) = Delhi (USERS ISSUED))) 

Consider the relation scheme.

 AUTHOR      (ANAME, INSTITUTION, ACITY, AGE)
 PUBLISHER   (PNAME, PCITY)
 BOOK        (TITLE, ANAME, PNAME) 

Express the following queries using (one or more of )SELECT, PROJECT, JOIN and DIVIDE operations.
(a) Get the names of all publishers.
(b) Get values of all attributes of all authors who have published a book for the publisher with PNAME = ‘TECHNICAL PUBLISHERS’.
(c) Get the names of all authors who have published a book for any publisher located in Madras.

Consider the following relational schema:

 COURSES (cno, cname)
 STUDENTS (rollno, sname, age, year)
 REGISTERED FOR (cno, rollno) 

(a) Write a relational algebra query to
Print the roll number of students who have registered for cno 322.
(b) Write a SQL query to
Print the age and year of the youngest student in each year.

Consider a relational database containing the following schemas.

The primary key of each table is indicated by underlying the constituent fields.

SELECT s.sno, s.sname
FROM Suppliers s, Catalogue c
WHERE s.sno = c.sno AND
              Cost > (SELECT AVG (cost)
                      FROM Catalogue
                      WHERE pno = ‘P4’
                      GROUP BY pno);

The number of rows returned by the above SQL query is

The following relations are used to store data about students, courses, enrollment of students in courses and teachers of courses. Attributes for primary key in each relation are marked by ‘*’.

 Students (rollno*, sname, saddr)
 courses (cno*, cname)
 enroll(rollno*, cno*,grade)
 teach(tno*,tname,cao*) 

(cno is course number, cname is course name, tno is teacher number, tname is teacher name, sname is student name, etc.)

Write a SQL query for retrieving roll number and name of students who got A grade in at least one course taught by teacher named Ramesh for the above relational database.