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 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:
- Data Definition Language (DDL) is a programming language that allows you to define data (Contains commands for creating and editing tasks).
- DCL stands for Data Control Language (Contains instructions relating to access control).
- DQL stands for Data Query Language.
- 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:
- Only (a) is true
- Only (b) is true
- Both (a) and (b) are true
- 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);
- 16
- 4
- 8
- 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
- Titles of the five most expensive books
- Titles of the four most expensive books
- Title of the fifth most inexpensive book
-
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?
- Query 1
- Query 2
- Query 3
- 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:
- 2000
- 2500
- 4500
- 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
- The name of the school and the number of 100s obtained by its students for each school with more than 200 students taking examinations
- The name of each school with more than 200 students, as well as the number of 100s scored by its students
- The name of schools with more than 200 students, as well as the number of students who scored 100 on at least one exam
- 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?
- An SQL query automatically eliminates the duplicates
- An SQL query will fail if there are no indexes on the relations
- SQL permits attribute names to be repeated in the same relation
- 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?
- x = 5 AND not(not(x = 5))
- x = 5 AND x> 4 and x < 6, where x is an integer
- x ≠ 5 AND not (x = 5)
- 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
- the selection operation in relational algebra
- the selection operation in relational algebra, except that select in SQL retains duplicates
- the projection operation in relational algebra
- 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.
- 0
- 1
- 2
- 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.