1
GATE CSE 2014 Set 3
MCQ (Single Correct Answer)
+2
-0.6

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');
A
Names of all the employees with at least one of their customers having a 'GOOD' rating.
B
Names of all the employees with at most one of their customers having a 'GOOD' rating.
C
Names of all the employees with none of their customers having a 'GOOD' rating.
D
Names of all the employees with all their customers having a 'GOOD' rating.
2
GATE CSE 2014 Set 1
MCQ (Single Correct Answer)
+2
-0.6

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?
A
It executes but does not give the correct result.
B
It executes and gives the correct result.
C
It generates an error because of pairwise comparison.
D
It generates an error because the GROUP BY clause cannot be used with table joins in a sub-query.
3
GATE CSE 2013
MCQ (Single Correct Answer)
+2
-0.6

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) ∏snamecourseno = 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) { < SN >| ∃SR∃RP ( < SR, SN > ∈ Students ∧ ∈ Registration ∧ RP > 90)}

A
I, II, III and IV
B
I, II and III only
C
I, II and IV only
D
II, III and IV only
4
GATE CSE 2011
MCQ (Single Correct Answer)
+2
-0.6

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 );
A
3
B
9
C
5
D
6
GATE CSE Subjects
Software Engineering
Web Technologies
EXAM MAP
Medical
NEET
Graduate Aptitude Test in Engineering
GATE CSEGATE ECEGATE EEGATE MEGATE CEGATE PIGATE IN
CBSE
Class 12