1
GATE CSE 2014 Set 1
+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.
2
GATE CSE 2013
+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
3
GATE CSE 2011
+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
4
GATE CSE 2011
+2
-0.6

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
127
B
255
C
129
D
257
GATE CSE Subjects
EXAM MAP
Medical
NEET