GATE PYQ

SQL

Q11.
Consider a relational database containing the following schemas. The primary key of each table is indicated by underlining the constituent fields. 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) ;The number of rows returned by the above SQL query is

Q12.
Consider the set of relations given below and the SQL query that follows: Students: (Roll_number, Name, Date_of_birth ) Coursed: (Course_number, Course_name, Instructor ) Grades: (Roll_number, Course_number, Grade) SELECT DISTINCT Name FROM Students, Courses, Grades WHERE Students.Roll_number = Grades.Roll_number AND Courses.Intructor =Sriram AND Courses.Course_number = Grades.Course_number AND Grades.Grade = AWhich of the following sets is computed by the above query?

Q13.
Consider the following relational query on the above database: SELECT S.name FROM Suppliers S Where S.sid NOT IN (SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P WHERE P.color <>'blue'))

Q14.
Consider the following relation Cinema(theater, address, capacity) Which of the following options will be needed at the end of the SQL query SELECT P1.address FROM Cinema P1 such that it always finds the addresses of theaters with maximum capacity?

Q15.
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?

Q16.
Given the following statements: S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition. CREATE TABLE S ( a INTEGER, d INTEGER, e INTEGER, PRIMARY KEY (d), FOREIGN KEY (a) references R) Which one of the following statements is CORRECT?

Q17.
The relation scheme given below is used to store information about the employees of a company, where empId is the key and deptId indicates the department to which the employee is assigned. Each employee is assigned to exactly one department. \textsf{emp($\underline{\textsf{empId}}$, name, gender, salary, deptId)} Consider the following SQL query: select deptId, count(*) from emp where gender = "female" and salary > (select avg(salary)from emp) group by deptId;The above query gives, for each department in the company, the number of female employees whose salary is greater than the average salary of

Q18.
SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below: Select * from R where a in (select S. a from S)

Q19.
The SQL query SELECT columns FROM TableA RIGHT OUTER JOIN TableB ON A.columnName = B.columnName WHERE A.columnName IS NULLreturns the following:

Q20.
SELECT operation in SQL is equivalent to