SQL

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.