GATE || DBMS || SQL & Relational Algebra || Pyq ( 2010 to 2025 )

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. 

emp (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:

[GATE 2021 || SET- 2 MCQ || 2-mark]

SELECT operation in SQL is equivalent to
[GATE 2015 || SET-1 MCQ || 1-mark]

Consider the following relations:

            Student

                 Performances

SELECT S. student_Name, sum(P.Marks) 

FROM Student S, Performance P 

WHERE S.Roll_No = P.Roll_no

GROUP BY S.Student_Name

The number of rows that will be returned by the SQL query is _______?
[GATE 2015|| SET-1 NAT || 2-mark]

Consider the following database table named water_schemes:

The number of tuples returned by the following SQL query is _______.

with total(name, capacity) as	
    select district_name, sum(capacity)
	from water_schemes	
group by district_name	
with total_avg(capacity) as	
select avg(capacity)	
from total 	
select name	
from total, total_avg	
where total.capacity>total_avg.capacity;     [GATE 2016|| SET-2 NAT || 2-mark]

Consider a database that has the relation schema EMP (EmpId, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below.

SELECT AVG (EC.Num)

FROM EC

WHERE (DeptName, Num) IN

(SELECT DeptName, COUNT(EmpId) AS EC(DeptName, Num)

FROM EMP

GROUP BY DeptName);

The output of executing the SQL query is ______? [GATE 2017|| SET-1 NAT || 1-mark ]

Consider a database that has the relation schemas EMP(Empld, EmpName, Deptld) and DEPT(DeptName, Deptld). Note that the Deptld can  be permitted to a NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus.

(I) {t|∃u ϵ EMP(t[EmpName) = u[EmpName] Λ ∀ ϵ DEPT(t[Deptld] ≠ V[Deptld]))}

(II) {t|∃u ϵ EMP(t[EmpName) = u[EmpName] Λ ∃∨ ϵ DEPT(t[Deptld] ≠V[Deptld]))}

(III) {t|∃u ϵ EMP(t[EmpNmae) = u[EmpName] Λ ∃∨ ϵ DEPT(t[Deptld] =V[Deptld]))}

Which of the above queries are safe? [GATE 2017|| SET-1 MCQ || 1-mark ]

Consider the following two tables and four queries in SQL .

Book (isbn, bname), Stock (isbn, copies)

Query 1:      SELECT B.isbn, S.copies

FROM BOOK B INNER JOIN Stock S

ON B.isbn = S.isbn; 

Query 2:      SELECT B.isbn, S.copies

FROM BOOK B LEFT OUTER JOIN Stock S 

ON B.isbn=S.isbn;

Query 3:      SELECT B.isbn, S.copies

FROM BOOk B RIGHT OUTER JOIN Stock S 

ON B.isbn = S.isbn;

Query 4:   SELECT B.isbn, S.copies

FROM BOOk B FULL OUTER JOIN Stock S

ON B.isbn = S.isbn;

Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries? [GATE 2018 MCQ || 1-mark]

Consider the following relations P(X, Y, Z), Q(X, Y, T) and R(Y, V).  [GATE 2019 || NAT || 2-mark]

Table : P

Table : Q

Table : R

How many tuples will be returned by the following relational algebra query?

πx(σ(P.Y = R.Y ∧ R.V = V(P X R))) - πx(σ(Q.Y = R.Y ∧ Q.T > 2(Q X R))) 

A relational database contains two tables Student and Performance as shown below

Student

Performance

The primary key of the Student table is Roll_no. For the Performance table, the columns Roll_no. and Subject_code together from the primary Key. Consider the SQL query given below.

SELECT S.Student.name, sum (P.Marks) 

FROM Student S, Performance P 

WHERE P.Marks> 84

GROUP BY S.Student_name;

The number of rows returned by the above SQL query is _________? [GATE 2019 || NAT || 2-mark]

Consider a relational database containing the following schemas.

Catalogue

Suppliers 

Parts

The primary key of each table is indicated by underlying 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 __________. [GATE 2020 || NAT || 2-mark]