Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Mar 27, 2024

Gate Questions on SQL: Part 2

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

Structured Query Language (SQL) is an acronym 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 a programming 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. Which of the following statements about 'view' in DBMS is/are correct? 

(a) When a particular event happens, a 'view' is a unique stored process. 
(b) A 'view' is a virtual table that appears after a pre-compiled query is executed:

  1. Only (a) is true
  2. Only (b) is true
  3. Both (a) and (b) are true
  4. Neither (a) nor (b) are true

 

Ans. b) Only (b) is true

Explanation: A 'view' is a virtual table, not separate storage. The view is only available when a pre-computed query has been executed. As a result, option (B) is the correct answer.

 

2.  The table below displays the employee name, department, and OT_allowance.

Employee

Department

OT_allowance

RAMA Mechanical 5000
GOPI Electrical 2000
SINDHU Computer 4000
MAHESH Civil 1500

What is the result of the SQL query below?

select count(*) from ((select Employee, Department from Overtime_allowance) as S
natural join (select Department, OT_allowance from Overtime_allowance) as T);

 

  1. 16
  2. 4
  3. 8
  4. None of the above

 

Ans. b) 4

Explanation: When we use a natural join on S and R, the common attribute Department in both tables is matched, and the common tuples are produced. As a result, the output will be four tuples. 

Table S Table R
Employee Department Department OT_allowance
Rama Mechanical Mechanical 5000
Gopi Electrical Electrical 2000
Sindhu Computer Computer 4000
Mahesh Civil Civil 1500

Option (B) is correct 
 

3. The book (title, price) relation comprises the titles and prices of various books. Assuming that no two books have the same price, what does the following SQL query list?  

select title from book as B where (select count(*) from book as T where T.price>B.price) < 5 

 

  1. Titles of the five most expensive books
  2. Titles of the four most expensive books
  3. Title of the fifth most inexpensive book
  4. Title of the fifth most expensive book
     

Ans. a) Titles of the five most expensive books

Explanation: The outer query selects all titles from the book table. The subquery counts all books that are more costly than the selected book for each selected book. For the five most costly books, the where clause of the outer query will be valid. For example, for the most costly book, count (*) will be 0, and for the second most expensive book, count (*) will be 1.
 

4. Look at the two SQL tables and four SQL queries below.

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 B 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 B B FULL OUTER JOIN Stock S
ON B.isbn = S.isbn;

 

Which of the above questions is assured to yield an output that supersets the other three queries' results?

  1. Query 1
  2. Query 2
  3. Query 3
  4. Query 4

 

Ans. d) Query 4

Explanation: The FULL OUTER JOIN in SQL combines the results of both left and right outer joins and returns all (matched or unmatched) rows from both sides of the join clause. As a result, option (D) is the correct answer.

 

5. Consider the primary keys P and R in r1(P, Q, R) and r2(R, S, T). The relation r1 has 2000 tuples and the relation r2 has 2500 tuples. The join r1⋈ r2 can be as large as:

  1. 2000
  2. 2500
  3. 4500
  4. 5000

 

Ans. a) 2000

Explanation: The join operation r1⋈ r2 is performed on the shared attribute R. R is also the primary key in R2. When we take a, we should ensure that the value of the common attribute (in this example, R2) is the same. The value of R in r2 corresponds to the value of R in r1. As a result, there will be 2000 tuples. As a result, the correct answer is (A).
 

6. Consider the given Student, School, Enrolment, and ExamResult schema:

Student (school-id, sch-roll-no, sname, saddress)
School (school-id, sch-name, sch-address, sch-phone)
Enrolment(school-id sch-roll-no, erollno, examname)
ExamResult(erollno, examname, marks)

 

What is the result of the SQL query below?

SELECT sch-name, COUNT (*)
FROM School C, Enrolment E, ExamResult R
WHERE E.school-id = C.school-id
AND
E.examname = R.examname AND E.erollno = R.erollno
AND
R.marks = 100 AND S.school-id IN (SELECT school-id
                           FROM student
                           GROUP BY school-id
                            HAVING COUNT (*) > 200)
GROUP By school-id

 

  1. The name of the school and the number of 100s obtained by its students for each school with more than 200 students taking examinations
  2. The name of each school with more than 200 students, as well as the number of 100s scored by its students
  3. The name of schools with more than 200 students, as well as the number of students who scored 100 on at least one exam
  4. nothing; there is a syntax problem in the query

 

Ans. c) nothing; the query has a syntax error

Explanation: In the SELECT clause of the outer SQL query, sch-name is used; however, in the GROUP BY clause, school-id is used, which should be the same as in the SELECT clause.

 

7. Which of the following statements is/are true? 

  1. An SQL query automatically eliminates the duplicates
  2. An SQL query will fail if there are no indexes on the relations
  3. SQL permits attribute names to be repeated in the same relation
  4. None of the above

 

Ans. d) None of the above

Explanation: We must use distinct to remove duplicates because SQL does not delete them as relational algebra projection does. If there are no indexes, SQL will work slowly but steadily. In SQL, there can't be two attributes with the same name in the same relation. Therefore none of the choices (a), (b), or (c) are accurate. As a result, option (d) is the correct answer.
 

8. Null values are viewed as unknown in SQL, and comparisons with null values are treated as unknown. Assume that all comparisons with a null value are false. Which of the following pairings does not have the same meaning?

  1. x = 5 AND not(not(x = 5))
  2. x = 5 AND x> 4 and x < 6, where x is an integer
  3. x ≠ 5 AND not (x = 5)
  4. None of the above

 

Ans. c) x ≠ 5 AND not (x = 5)

Explanation: Because a comparison with a NULL value is always False in the provided query, "x ≠ 5" will be false. "x = 5" will also return false, and not (x = 5) = not(false) = true will also return true. As a result, these aren't equal pairs. Option (C) is the correct answer.

 

9. In SQL, the select operation is identical to

  1. the selection operation in relational algebra
  2. the selection operation in relational algebra, except that select in SQL retains duplicates
  3. the projection operation in relational algebra
  4. the projection operation in relational algebra, except that select in SQL keeps duplicates

 

Ans. d) the projection operation in relational algebra, except that select in SQL retains duplicates

Explanation: The select operation in SQL is similar to the projection process in relational algebra, except that select keeps duplicates whereas projection eliminates them.

 

10.
Table: Students

Roll_No Student_Name
1 Raj
2 Rohit
3 Raj

Table: Performance

Roll_No Course MArks
1 Math 80
1 English 70
2 Math 75
3 English 80
2 Physics 65
3 Math 80

 Consider the following relations:

SELECT S.Student_Name, sum(P.Marks)
     FROM Student S, Performance P
     WHERE S.Roll_No = P.Roll_No
     GROUP BY S.Student_Name

 

________ is the number of rows that the SQL query will return.

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

 

Ans. c) 2

Explanation: The output of the provided query is shown below. There are just two student names listed, and for each student, the query outputs sum(P.Marks).

Student_Name Marks
Raj 310
Rohit 140

 

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

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 used to interact 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 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.

Recommended Reading:

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!

Topics covered
1.
Introduction
2.
Gate Questions on SQL
3.
Frequently Asked Questions
4.
Conclusion