GATE PYQ

SQL

Q21.
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

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

Q23.
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

Q24.
Consider the following relations: Consider the following SQL query. The number of rows that will be returned by the SQL query is _____________.

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

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

Q27.
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'))

Q28.
SELECT operation in SQL is equivalent to

Q29.
Consider the relational database with the following four schemas and their respective instances.The number of rows returned by the above SQL query is ___

Q30.
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')