SQL

Question 1
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
A
employees in the department
B
employees in the company
C
female employees in the department
D
female employees in the company
GATE CSE 2021 SET-2   Database Management System
Question 2
The SQL query
 SELECT columns
FROM TableA
RIGHT OUTER JOIN TableB
ON A.columnName = B.columnName
WHERE A.columnName IS NULL

returns the following:
A
All rows in Table B, which meets equality condition above and, none from Table A which meets the condition.
B
All rows in Table A, which meets equality condition above and none from Table B, which meets the condition.
C
All rows in Table B, which meets the equality condition
D
All rows in Table A, which meets the equality condition
ISRO CSE 2020   Database Management System
Question 2 Explanation: 
NOTE: Question is excluded from the evaluation due to ambiguity.
Click here for detail solution by gateoverflow
Question 3
Properties of 'DELETE' and 'TRUNCATE' commands indicate that
A
After the execution of 'TRUNCATE' operation, COMMIT, and ROLLBACK statements cannot be performed to retrieve the lost data, while 'DELETE' allow it
B
After the execution of 'DELETE' and 'TRUNCATE' operation retrieval is easily possible for the lost data
C
After the execution of 'DELETE' operation, COMMIT and ROLLBACK statements can be performed to retrieve the lost data, while TRUNCATE do not allow it
D
After the execution of 'DELETE' and 'TRUNCATE' operation no retrieval is possible for the lost data
ISRO CSE 2020   Database Management System
Question 4
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
A
4
B
5
C
0
D
2
GATE CSE 2020   Database Management System
Question 5
A relational database contains two tables Student and Performance as shown below:

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 _________ .
A
3
B
6
C
5
D
4
GATE CSE 2019   Database Management System
Question 6
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 = A
Which of the following sets is computed by the above query?
A
Names of Students who have got an A grade in all courses taught by Sriram
B
Names of Students who have got an A grade in all courses
C
Names of Students who have got an A grade in at least one of the courses taught by Sriram
D
None of the above
ISRO CSE 2018   Database Management System
Question 7
Given relations R(w,x) and S(y,z), the result of
SELECT DISTINCT w,x
FROM R,S
is guaranteed to be same as R, if
A
R has no duplicates and S is non-empty
B
R and S have no duplicates
C
S has no duplicates and R is non-empty
D
R and S have the same number of tuples
ISRO CSE 2018   Database Management System
Question 8
Consider the following two tables and four queries in SQL.

Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?
A
Query 1
B
Query 2
C
Query 3
D
Query 4
GATE CSE 2018   Database Management System
Question 9
\begin{array}{|l|l|l|l|l|} \hline \textbf{Employee} & \textbf{Department} & \textbf{OT allowance} \\ \hline \text{RAMA} & \text{Mechanical} & \text{5000} \\\hline \text{GOPI} & \text{Electrical} & \text{2000} \\\hline \text{SINDHU} & \text{Computer} & \text{4000} \\\hline \text{MAHESH} & \text{Civil} & \text{1500} \\\hline \end{array}
What is the output of the following SQL query?
 select count(*) from 
    ((select Employee, Department from Overtime_allowance) 
    natural join 
    (select Department, OT_allowance from Overtime_allowance) 
    as T);
A
16
B
4
C
8
D
None of the above
ISRO CSE 2017   Database Management System
Question 10
Consider the following database table named top_scorer.

Consider the following SQL query:

The number of tuples returned by the above SQL query is ___________.
A
5
B
6
C
7
D
8
GATE CSE 2017 SET-2   Database Management System
There are 10 questions to complete.

2 thoughts on “SQL”

Leave a Comment

Like this FREE website? Please share it among all your friends and join the campaign of FREE Education to ALL.