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

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?

Consider the following relational schema.

Students(rollno: integer, sname: string)

Courses(courseno: integer, cname: string)

Registration(rollno: integer, courseno: integer, percent: real)

Which of the following queries are equivalent to this query in English?

"Find the distinct names of all students who score more than 90% in the course numbered 107"

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

(II) ∏_sname(σ_courseno = 107 ∧ percent > 90 (Registration ⋈ Students))

(III) { T | ∃S ∈ Students, ∃R ∈ Registration ( S.rollno=R.rollno ∧ R.courseno=107 ∧ R.percent > 90 ∧ T.sname=S.sname)}

(iv) { < S_N >| ∃S_R∃R_P ( < S_R, S_N > ∈ Students ∧ ∈ Registration ∧ R_P > 90)}

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

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