SQL

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

Single correct

• Aemployees in the department.
• Bemployees in the company.
• Cfemale employees in the department.
• Dfemale employees in the company.

Answer key coming soon

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

Single correct

• A4
• B5
• C0
• D2

Answer key coming soon

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;

Single correct

• AQuery 1
• BQuery 2
• CQuery 3
• DQuery 4

Single correct

• Athe selection operation in relational algebra
• Bthe selection operation in relational algebra, except that SELECT in SQL retains duplicates
• Cthe projection operation in relational algebra
• Dthe projection operation in relational algebra, except that SELECT in SQL retains duplicates

SELECT P1.address
FROM Cinema P1

Single correct

• AWHERE P1.capacity >= All (select P2.capacity from Cinema P2)
• BWHERE P1.capacity >= Any (select P2.capacity from Cinema P2)
• CWHERE P1.capacity > All (select max(P2.capacity) from Cinema P2)
• DWHERE P1.capacity > Any (select max(P2.capacity) from Cinema P2)

CREATE TABLE S (
    a INTEGER,
    d INTEGER,
    e INTEGER,
    PRIMARY KEY (d),
    FOREIGN KEY (a) references R)

Single correct

• AS1 is TRUE and S2 is FALSE.
• BBoth S1 and S2 are TRUE.
• CS1 is FALSE and S2 is TRUE.
• DBoth S1 and S2 are FALSE.

employees(emp-id, first-name, last-name, hire-date, dept-id, salary)
departments(dept-id, dept-name, manager-id, location-id)

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

Single correct

• AIt executes but does not give the correct result.
• BIt executes and gives the correct result.
• CIt generates an error because of pairwise comparison.
• DIt generates an error because the GROUP BY clause cannot be used with table joins in a sub-query.

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

Single correct

• Aselect R.* from R, S where R.a=S.a
• Bselect distinct R.* from R,S where R.a=S.a
• Cselect R.* from R,(select distinct a from S) as S1 where R.a=S1.a
• Dselect R.* from R,S where R.a=S.a and is unique R

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

Single correct

• ANames of all the employees with at least one of their customers having a 'GOOD' rating.
• BNames of all the employees with at most one of their customers having a 'GOOD' rating.
• CNames of all the employees with none of their customers having a 'GOOD' rating.
• DNames of all the employees with all their customers having a 'GOOD' rating.

SELECT DISTINCT S.sname
FROM Students as S, Registration as R
WHERE R.rollno=S.rollno AND R.courseno=107 AND R.percent >90

Single correct

• AI, II, III and IV
• BI, II and III only
• CI, II and IV only
• DII, III and IV only

Answer key coming soon

Single correct

• AP and R
• BP and S
• CQ and R
• DQ and S

Answer key coming soon

SELECT A.Id
FROM A
WHERE A.Age > ALL (SELECT B.Age
                   FROM B
                   WHERE B.Name = 'Arun')

Single correct

• A4
• B3
• C0
• D1

Answer key coming soon

SELECT Y FROM T WHERE X=7;

Single correct

• A127
• B255
• C129
• D257

Answer key coming soon

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

Single correct

• A3
• B9
• C5
• D6

Answer key coming soon