Gate Questions on SQL: Part 1

Introduction

Structured Query Language (SQL) is an abbreviation for Structured Query Language. SQL is a database programming language for retrieving and managing data in relational databases. 

The condition of declarative SQL statements is referred to as SQL Query. When writing queries, you may use a variety of SQL clauses to define the action you want to do. IBM was the first company to develop SQL.

SQL may be used with SQL Server, MySQL, Oracle, and MS Access, among other relational database management systems. You may build, change, and create schemas for any database by creating SQL codes and queries.

SQL is made up of several distinct sorts of statements, known as SQL sub-languages:

1. Data Definition Language (DDL) is a programming language that allows you to define data (Contains commands for creating and editing tasks).
2. DCL stands for Data Control Language (Contains instructions relating to access control).
3. DQL stands for Data Query Language.
4. DML (Data manipulation language ) is the database language for manipulating data (Contains instructions for inserting, updating, and deleting data).

 

Let us go through some of the crucial gate questions on SQL.

Gate Questions on SQL

Below are some commonly asked gate questions on SQL in the GATE examination.

1. In SQL, which of the following is an aggregate function?

1. Avg
2. Select
3. Ordered by
4. Distinct

 

Ans. a) Avg
Explanation: One of the aggregate functions is avg. After calculating values in a numeric column, it returns the average value. Syntax:

SELECT AVG(column_name) FROM table_name;

 

2. Which of the following statements is correct? 

I. A self-join may be implemented in SQL using table aliases.
II. The outer-join operation is a fundamental relational algebra procedure.
III. The procedures of natural join and outside join are the same.

 

1. Only III is correct.
2. Only I is correct.
3. I and II are correct.
4. II and III are correct.

 

Ans. b) Only I is correct.

Explanation: 

A self-join may be implemented in SQL using table aliases. (Correct)

The outer-join operation is a fundamental relational algebra procedure. (Incorrect)

The functions of natural join and outside join are the same. (Incorrect)

 

3. The outcome of the relations R(w,x) and S(y,z) is

SELECT DISTINCT w, x
FROM R, S

 

1. S is non-empty, and R has no duplicates
2. R and S have no duplicates
3. S has no copies, and R is non-empty
4. S and R have the same number of tuples

 

Ans. a) S is non-empty, and R has no duplicates
Explanation: The query returns all of R's characteristics. Because distinct is in the query, the result can only be equal to R if R has no duplicates. If we don't specify an attribute on which to link two tables, queries like those above become cartesian products. If one of the two sets is empty, the cartesian product of the two sets will be blank. To get all rows of R, S must have at least one record. 

 

4. The relation scheme below stores information on a company's workers, where empId is the key and deptId is the employee's assigned department. Each employee has a specific department allocated to them.

emp(empId, name, gender, salary, deptId)

 

Consider the SQL query below:

 

select deptId, count(*)
from emp
where gender = “female” and salary > (select avg(salary)from emp)
group by deptId;

 

The above query returns, for each department, the number of female workers whose salary is higher than the company's average salary of

1. All employees in the department
2. All employees in the company
3. All female employees in the department
4. All female employees in the company

 

Ans. b) All employees in the company

Explanation: The inner query (select avg(salary) from emp) runs on the whole emp table, i.e., all employees. As a result, option (B) is the correct answer.
 

5. Location transparency in distributed databases allows database users, programmers, and administrators to handle data as if it were stored in a single location. A SQL query with location transparency must provide the following information:

1. Fragments
2. Locations
3. Local formats
4. Inheritances

 

Ans. a) Fragments

Explanation: The end user must supply the database fragment name but not the location of those pieces in a SQL query with location transparency.

The ultimate level of transparency is fragmentation transparency. The fact that a database is partitioned does not need to be known by the end-user or programmer. As a result, neither fragment names nor fragment locations are provided before data access.

6. Assume an ORACLE relation R(A, B) contains tuples (1, 2), (1, 3), (3, 4) and relation S(B, C) has tuples (2, 5), (4, 6), (7, 8).  Consider the following SQL queries, SQ1 and SQ2, respectively: SQ1: Select * From R Full Join S On R.B = S.B; SQ2: Select * From R Inner Join S On R.B = S.B; The numbers of tuples in the results of SQL queries SQ1 and SQ2 are calculated as follows:

1. 2 and 6, respectively
2. 6 and 2, respectively
3. 2 and 4, respectively
4. 4 and 2, respectively

 

Ans. d) 4 and 2, respectively

Explanation: A full join will provide two matches and two inconsistencies for B = 2 and B = 4. As a result, there will be four outputs. Only the conditions that match will be output by the inner join. B = 2 and B = 4 are two different types of B. Inner join produces two outputs. As a result, option (D) is the correct answer.

 

7. Consider the following three SQL queries (assuming the 'persons' table has the data): (a)Select a name from people who are over the age of 21; (b)Select a name from people who are over the height of 180; (c)Select a name from people who are (over the age of 21) or (over the height of 180);. What is one probable number of rows returned by SQL query (c) if SQL queries (a) and (b) above produce 10 and 7 rows in the result set, respectively?

1. 7
2. 10
3. 3
4. 21

 

Ans. b) 10

Explanation: 10 rows are returned if you select a name from those over the age of 21. 7 rows are returned if you choose Name among persons with a height of more than 180. Select Name from People where (Age > 21) or (Height > 180) will return 10 rows since there is a choice between the two conditions.

 

8. STUDENT (Name, Gender, Marks, DEPT Name) is a relation that stores STUDENT information at a university. Consider the SQL Query below. SELECT DEPT Name from STUDENT where Gender = 'M'> GROUP BY DEPT_Name having avg(Marks)>SELECT avg (Marks) from STUDENT. It returns the Department Name for which:

1. The Average marks of Male students are more than the average marks of students in the same department
2. The average marks of male students are more than the average marks of students in the University
3. The average marks of male students are more than the average marks of male students in the University
4. The average marks of students are more than the average marks of male students in the University

 

Ans. b)  The average marks of male students are more than the average marks of students in the University

Explanation: SELECT DEPT Name from STUDENT where Gender = 'M' group by DEPT Name having avg (Marks)>SELECT average (Marks) from STUDENT returns the results. Male students have higher average grades than female students at the University. As a result, option (B) is the correct answer.
 

9.  Consider the ORACLE relationships below:One (x, y) = {<2, 5>, <1, 6>, <1, 6>, <1, 6>, <4, 8>, <4, 8>} Two (x, y) = {<2, 55>, <1, 1>, <4, 4>, <1, 6>, <4, 8>, <4, 8>, <9, 9>, <1, 6>}. Consider the following SQL queries, SQ1 and SQ2, respectively:

SQ1 : SELECT * FROM One)
           EXCEPT
      (SELECT * FROM Two);
SQ2 : SELECT * FROM One)
        EXCEPT ALL
      (SELECT * FROM Two);

 

What is the cardinality of the result generated on the execution of each SQL query on the instances above?

1. 2 and 1, respectively
2. 1 and 2, respectively
3. 2 and 2, respectively
4. 1 and 1, respectively

 

Ans. b) 1 and 2, respectively

Explanation:

SQ1: EXCEPT (SELECT * FROM Two) EXCEPT (SELECT * FROM Two); This will result in a single tuple (2, 5) since all duplicate tuples will be eliminated.
SQ2: SELECT * FROM ONE) EXCEPT ALL (SELECT * FROM TWO); This will result in two tuples <(2,5) and (1,6)> since duplicates will not be removed. 
As a result, option (B) is the correct answer.

10. An assertion is a predicate that expresses a requirement that the database must constantly meet. The assertion should be written in the following format:

1. CREATE ASSERTION ‘ASSERTION Name’ CHECK ‘Predicate’
2. CREATE ASSERTION ‘ASSERTION Name’
3. CREATE ASSERTION, CHECK Predicate
4. SELECT ASSERTION

 

Ans. a) CREATE ASSERTION 'ASSERTION Name' CHECK 'Predicate'

Explanation: This is the format of writing assertion: 

CREATE ASSERTION <assertion_name> CHECK <predicate/constraint>;

 

11. Consider the following schema:

Emp (Empcode, Name, Gender, Salary, Deptt)

 

What will be the output of the basic SQL query is given below:

SELECT Deptt FROM Emp
WHERE Gender = 'M'
GROUP by Dept
Having avg (Salary) > {select avg (Salary) from Emp}

 

1. The average salary of male employees is the average salary of the organization.
2. The average salary of male employees is less than the average salary of the organization.
3. The average salary of male employees is equal to the average salary of the organization.
4. The average salary of male employees is more than the average salary of the organization.

 

Ans. d) The average salary of male employees is more than the average salary of the organization

Explanation: The subquery will return the average salary of all the employees. The parent query will then group the records department-wise and display only those employees whose salary is more than the average salary.
 

12. A SQL command changes one or more fields of a table:  

1. LOOK-UP
2. INSERT
3. CHANGE
4. MODIFY

 

Ans. d) MODIFY

Explanation:  MODIFY is a SQL function that modifies records in a database table. As a result, option (d) is the correct answer.

 

For more GATE questions related to SQL, refer to our Gate Questions on SQL: Part 2 and Gate Questions on SQL: Part 3.