Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Gate Questions on SQL
3.
Frequently Asked Questions
4.
Conclusion
Last Updated: Mar 27, 2024

Gate Questions on SQL: Part 3

Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

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 ServerMySQLOracle, 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. Consider the relational schema below:
employee(empId, empName, empDept)
customer(custId, custName, salesRepId, rating)

 

salesRepId is a foreign key that refers to the employee relation's empId. Assume that each employee sells something to at least one customer. What is the result of the following query?

SELECT empName
       FROM employee E
       WHERE NOT EXISTS ( SELECT custId
                          FROM customer C
                          WHERE C.salesRepId = E.empId
                            AND C.rating <> `GOOD`);

 

  1. Names of all employees who have received a 'GOOD' rating from at most one of their clients.
  2. Names of all employees who have not received a 'GOOD' rating from any of their clients.
  3. Names of all employees with a 'GOOD' rating from all their clients.
  4. Names of all employees who have received a 'GOOD' rating from at least one of their clients.

 

Ans. c) Names of all employees with a 'GOOD' rating from all their clients

Explanation: The inner query will return some rows if any employee has gotten a rating other than 'GOOD' from any of their clients, and NOT EXISTS will return false. Therefore those employees will not be printed. Only staff who have received a 'GOOD' rating from all of their clients will be included.

 

2. The statement that is automatically run by the system as a result of the database modification is:

  1. recovery
  2. trigger
  3. backup
  4. assertion

 

Ans. b) trigger

Explanation: Triggers are SQL codes performed automatically in response to specific events on a table. These are employed to keep the data safe and secure. In SQL, a trigger functions similarly to a real-world trigger. When the gun trigger is pulled, a bullet is fired.

 

3. To remove a table in SQL, we use which of the following commands?

  1. delete
  2. truncate
  3. remove
  4. drop

 

Ans. d) drop

Explanation: drop is used to remove a table entirely.
 

4. The following table includes two attributes, A and C, with A being the primary key and C being a foreign key that references A with an on-delete cascade.

A

C

2 4
3 4
4 3
5 2
7 2
9 5
6 4

When the tuple (2,4) is destroyed, the following set of tuples must be deleted as well to maintain referential integrity:

  1. (3,4) and (6,4)
  2. (5,2) and (7,2)
  3. (5,2), (7,2) and (9,5)
  4. (3,4), (4,3), and (6,4)

 

Ans. c) (5,2), (7,2) and (9,5)

Explanation: When (2,4) is removed from the equation. All values with value 2 in C must be destroyed since C is a foreign key referring to A with delete-on cascade. As a result, (5, 2) and (7, 2) are removed. As a result, 5 and 7 are removed from A, resulting in the deletion of (9, 5).

 

5. Which of the following statements about normal forms is incorrect?

  1. The BCNF is more stringent than the 3NF.
  2. It is always feasible to decompose 3NF in a lossless, dependency-preserving manner.
  3. It is always possible to decompose BCNF in a lossless, dependency-preserving way.
  4. BCNF includes any two-attribute relationship.

 

Ans. c) It is always feasible to decompose BCNF in a lossless, dependency-preserving manner.

Explanation: In BCNF, it is not always possible to collapse a table while maintaining dependencies. A set of functional dependencies AB –> C, C –> B, for example, cannot be deconstructed in BCNF.

 

6. Consider the database table T, which has two integer-type columns, X and Y. One record (X=1, Y=1) is entered into the database when created. Let MX and My stand for the maximum X and Y values in all entries in the database at any given moment. New records are put into the table 128 times using MX and MY, with X and Y values of MX+1 and 2*MY+1, respectively. It should be noticed that the values of MX and MY vary each time they are inserted. What will the following SQL query outcome be once you've completed the steps above?

SELECT Y FROM T WHERE X=7;

 

  1. 127
  2. 255
  3. 129
  4. 257

 

Ans. a) 127

Explanation: 

X

Y

1 1
2 3
3 7
4 15
5 31
6 63
7 127

 

7.

Table A

Id

Name

Age

12 Arun 60
15 Shreya 24
99 Rohit 11

Table B

Id

Name

Age

15 Shreya 24
25 Hari 40
98 Rohit 20
99 Rohit 11

Table C

Id

Phone

Area

10 2200 02
99 2100 01

Take a look at A, B, and C in the preceding tables. The following SQL query returns how many tuples?

  1. 1
  2. 0
  3. 3
  4. 4

 

Ans. c) 3

Explanation: In table B, there is no entry with the name "Arun." As a result, the subquery returns NULL. If a subquery returns NULL, the condition is satisfied for all rows in A. As a result, all of table A's rows have been picked.

 

8. Consider the relational schema below:

EmployeeDetail (EmpId, FullName, ManagerID, DateOfJoining)
EmployeeSalary (EmpID, Project, Salary)

 

On the aforementioned database, consider the following relational query:

SELECT Salary
FROM EmployeeSalary Emp1
WHERE 2 = (
                SELECT COUNT( DISTINCT ( Emp2.Salary ) )
                FROM EmployeeSalary Emp2
                WHERE Emp2.Salary > Emp1.Salary
            )

 

Assume that the schema mentioned above's relations aren't empty. Which of the following interpretations of the given query is correct?

  1. find the highest salary from the table
  2. find the 2nd highest salary from the table
  3. find the 3rd highest salary from the table
  4. none of the above

 

Ans. c) find the 3rd highest salary from the table

Explanation: For the nth highest wage-

SELECT Salary
FROM EmployeeSalary Emp1
WHERE n-1 = (
                SELECT COUNT( DISTINCT ( Emp2.Salary ) )
                FROM EmployeeSalary Emp2
                WHERE Emp2.Salary > Emp1.Salary
            )

 

Therefore, option (C) is correct.

 

9. Take a look at the collection of relationships below,

Students: (Roll_number, Name, Date_of_birth)
  Courses: (Course number, Course_name, Instructor)
  Grades: (Roll_number, Course_number, Grade)

 

as well as the SQL query that follows.

select distinct Name
         from Students, Courses, Grades
         where Students. Roll_number = Grades.Roll_number
              and Courses.Instructor = Korth
              and Courses.Course_number = Grades.Course_number
              and Grades.grade = A

 

The above query computes which of the following sets?

  1. Names of students who have received an A in all of Korth's classes.
  2. Names of students who received an A in all of their classes
  3. Names of students who have received an A in at least one of Korth's courses.
  4. None of the above

 

Ans. c) Names of students who have received an A in at least one of Korth's courses.

Explanation: Since there is an ‘and’ on the query predicate, the query returns the names of all students who have received an "A" grade and attend Korth's courses, i.e., at least one of them. As a result, C is the correct answer.

 

10. Consider the relational schema below:

Suppliers(sid:integer, sname:string, city:string, street:string)

Parts(pid:integer, pname:string, color:string)

Catalog(sid:integer, pid:integer, cost:real)

On the database as mentioned above, consider the following relational query:

SELECT S.sname
    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'))

 

  1. Find out who has supplied a non-blue part.
  2. Look up the names of all the vendors who haven't delivered a non-blue part.
  3. Find all of the vendors who have supplied blue components exclusively.
  4. Find all of the suppliers who haven't supplied blue parts exclusively.
  5. None

 

Ans. d) Find all of the suppliers who haven't supplied blue parts exclusively.

Explanation: 

Because the specified query returns suppliers who have not supplied any blue components, the (D) option matched, which means it can have sections that aren't blue.

  1. False because this might include blue portions and "null" components.

(B) Untrue, as it returns something other than a blue portion.

(C) False, as this is not returned.

 

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

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Frequently Asked Questions

  1. What is the use of SQL?
    SQL is a language for interacting with databases. SQL statements are used to conduct operations like updating a database and retrieving data from one.
     
  2. Is SQL different from MySQL?
    SQL is a query programming language for managing relational databases. MySQL is a SQL-based relational database management system. SQL is a querying and operating language for database systems. MySQL allows you to manage, save, edit, and remove data in a structured manner.
     
  3. What do you mean by SQL index?
    An SQL index is an index that allows you to access data from a database quickly. Indexing a table or view is, without a doubt, one of the most effective techniques to increase query and application speed. A SQL index is a rapid lookup table for locating often searched records.
     
  4. What do you mean by the schema in SQL?
    A schema is a list of logical data structures in a SQL database. The schema, which has the same name as the database manager, is owned by a database user. As of SQL Server 2005, a schema is a separate entity (container of objects) from the person who creates the item.

Conclusion

In this article, we have extensively discussed the crucial questions from SQL and some previously asked gate questions on SQL.

We hope that this blog has helped you enhance your knowledge regarding various gate questions on SQL, and if you would like to learn more about SQLMySQLgate syllabus, or questions on stacks, and queues, check out our articles on  Coding Ninjas Studio. Do upvote our blog to help other ninjas grow. Happy Coding!

Previous article
Gate Questions on SQL: Part 2
Next article
Transactions and concurrency control
Live masterclass