Introduction to SQL Questions and Answers

Q: Consider the following two tables and four queries in SQL.Book (isbn, bname), Stock (isbn, copies)Query 1: SELECT B.isbn, S.copiesFROM Book B INNER JOIN Stock SON B.isbn = S.isbn;Query 2: SELECT B.isbn, S.copiesFROM Book B LEFT OUTER JOIN Stock SON B.isbn = S.isbn;Query 3: SELECT B.isbn, S.copiesFROM Book B RIGHT OUTER JOIN Stock SON B.isbn = S.isbn;Query 4: SELECT B.isbn, S.copiesFROM Book B FULL OUTER JOIN Stock SON B.isbn = S.isbn;Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?

The query certain to give a superset is Query 4, the full outer join.The inner join returns only books with a matching stock row. The left outer join adds books with no stock row (with copies null), and the right outer join adds stock rows with no matching book (with B.isbn null). The full outer join returns the matched rows plus both kinds of unmatched rows, so every row of the other three appears in it.The left join misses the unmatched stock rows, and the right join misses the unmatched books, so neither is certain to contain the other. The inner join is contained in all the others, which makes it the smallest result, not the largest.

Q: Database contains the relation Employee (id, name, salary), which of the following is an incorrect SQL query ?

The incorrect query is Select * from Employee order by salary Desc, name Asce;.In SQL, the sort direction keywords are ASC (or ASCENDING in some systems) and DESC. 'Asce' is not a keyword, so the statement fails with a syntax error.The other three are valid. A WHERE comparison on salary is ordinary filtering, SELECT DISTINCT name removes duplicate names, and LIKE '%Joshi' matches names ending in Joshi, since % stands for any sequence of characters.

Q: Database contains the relation course (Id, name, semester, year), which of the following is an incorrect SQL query ?

The incorrect query is Select semester from course group by semester where count (semester) \(\gt\) z;.In SQL the WHERE clause must come before GROUP BY, and it cannot contain an aggregate function such as count(). A condition on a group's count belongs in a HAVING clause: select semester from course group by semester having count(semester) \(\gt\) 2. The query also compares the count with z, which is not a number or a column of course.The other three queries are valid: ordering all rows by year, counting per semester with GROUP BY, and ordering the grouped result by the count.

Q: Which connective is used to test the absence of set membership in SQL ?

Absence of set membership is tested with not in.In SQL, the in connective tests whether a value belongs to a set (a list or a subquery result), and not in tests that it does not, for example where id not in (select id from enrolled).minus (except) and intersection are set operations between two whole query results, not membership tests on a value, and or is a logical connective between conditions.

Q: Which command is used to remove a relation from an SQL database ?

A relation is removed with Drop.DROP TABLE student; deletes the relation itself: its schema, all its tuples, and its indexes and constraints. Afterwards the table no longer exists in the database.DELETE FROM student; removes tuples but leaves the empty relation in place. PURGE is an Oracle-specific command for clearing dropped objects from the recycle bin, and REMOVE is not an SQL command.

Q: Which of the following is valid SQL for an Index?

Correct answer: CREATE INDEX ID;CREATE INDEX is the statement that builds an index, following the same CREATE pattern as tables and views - in full, CREATE INDEX name ON table (column).An index is a separate structure that lets the engine find rows by a column's value without scanning the whole table, at the cost of space and of slower writes, since every insert and update must maintain it. The counterpart is DROP INDEX. CHANGE, ADD and REMOVE are not SQL statements in their own right.

Q: A subquery in an SQL SELECT statement is enclosed in:

Correct answer: parenthesis -- (...) .A subquery is written inside ordinary parentheses, as in WHERE id IN (SELECT id FROM other).Braces and square brackets have other roles: square brackets delimit identifiers in some dialects, such as SQL Server's [Order Details], and braces appear in ODBC escape sequences - neither encloses a subquery in standard SQL. The parentheses are not decoration; they mark where the inner query ends, which is what lets subqueries nest inside one another.

Q: ________ was adopted as a national standard by ANSI in 1992.

Correct answer: SQLSQL was adopted as an ANSI standard in 1986 and substantially revised as SQL-92, which ANSI adopted in 1992 and which remains the reference point for the language's core.SQL-92 fixed much of what is now taken for granted: the JOIN syntax in the FROM clause, the % and _ wildcards, and standard data types. The other options are products rather than standards - Oracle, Access and dBase are implementations, and it is precisely the standard that lets skills move between them.

Which of the following languages is referred to as a database language?

A.SQL
B.VJ++
C.HTML
D.Python

Exam Year(s)

DSSSB TGT - 2023,

Answer: A

SQL (Structured Query Language) is the database language, used to define, query and manage data in relational databases. HTML is a markup language for web pages, Python is a general-purpose programming language, and VJ++ (Visual J++) is a language for Java development on Microsoft platforms. None of those three is a database language. The language has commands such as SELECT, INSERT, UPDATE and DELETE for working with the data in tables.

Discuss in Forum

Consider the following two tables and four queries in SQL.

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

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

Exam Year(s)

GATE CS - 2018,

Answer: D

The query certain to give a superset is Query 4, the full outer join.

The inner join returns only books with a matching stock row. The left outer join adds books with no stock row (with copies null), and the right outer join adds stock rows with no matching book (with B.isbn null). The full outer join returns the matched rows plus both kinds of unmatched rows, so every row of the other three appears in it.

The left join misses the unmatched stock rows, and the right join misses the unmatched books, so neither is certain to contain the other. The inner join is contained in all the others, which makes it the smallest result, not the largest.

Discuss in Forum

Database contains the relation Employee (id, name, salary), which of the following is an incorrect SQL query ?

A.Select * from Employee where salary \(\gt\) 10000;
B.Select distinct name from Employee;
C.Select * from Employee order by salary Desc, name Asce;
D.Select * from Employee where name like ‘%Joshi’;

Exam Year(s)

MH SET - 2019, SET Exam - 2019,

Answer: C

The incorrect query is Select * from Employee order by salary Desc, name Asce;.

In SQL, the sort direction keywords are ASC (or ASCENDING in some systems) and DESC. 'Asce' is not a keyword, so the statement fails with a syntax error.

The other three are valid. A WHERE comparison on salary is ordinary filtering, SELECT DISTINCT name removes duplicate names, and LIKE '%Joshi' matches names ending in Joshi, since % stands for any sequence of characters.

Discuss in Forum

Database contains the relation course (Id, name, semester, year), which of the following is an incorrect SQL query ?

A.Select * from course order by year;
B.Select count(semester), semester from course group by semester;
C.Select semester from course group by semester where count (semester) \(\gt\) z;
D.Select count (semester), semester from course group by semester order by count (semester);

Exam Year(s)

MH SET - 2020, SET Exam - 2020,

Answer: C

The incorrect query is Select semester from course group by semester where count (semester) \(\gt\) z;.

In SQL the WHERE clause must come before GROUP BY, and it cannot contain an aggregate function such as count(). A condition on a group's count belongs in a HAVING clause: select semester from course group by semester having count(semester) \(\gt\) 2. The query also compares the count with z, which is not a number or a column of course.

The other three queries are valid: ordering all rows by year, counting per semester with GROUP BY, and ordering the grouped result by the count.

Discuss in Forum

Which connective is used to test the absence of set membership in SQL ?

A.minus
B.not in
C.intersection
D.or

Exam Year(s)

MH SET - 2020, SET Exam - 2020,

Answer: B

Absence of set membership is tested with not in.

In SQL, the in connective tests whether a value belongs to a set (a list or a subquery result), and not in tests that it does not, for example where id not in (select id from enrolled).

minus (except) and intersection are set operations between two whole query results, not membership tests on a value, and or is a logical connective between conditions.

Discuss in Forum

Which command is used to remove a relation from an SQL database ?

A.Drop
B.Delete
C.Purge
D.Remove

Exam Year(s)

MH SET - 2021, SET Exam - 2021,

Answer: A

A relation is removed with Drop.

DROP TABLE student; deletes the relation itself: its schema, all its tuples, and its indexes and constraints. Afterwards the table no longer exists in the database.

DELETE FROM student; removes tuples but leaves the empty relation in place. PURGE is an Oracle-specific command for clearing dropped objects from the recycle bin, and REMOVE is not an SQL command.

Discuss in Forum

Which of the following is valid SQL for an Index?

A.CREATE INDEX ID;
B.CHANGE INDEX ID;
C.ADD INDEX ID;
D.REMOVE INDEX ID;

Answer: A

Correct answer: CREATE INDEX ID;

CREATE INDEX is the statement that builds an index, following the same CREATE pattern as tables and views - in full, CREATE INDEX name ON table (column).

An index is a separate structure that lets the engine find rows by a column's value without scanning the whole table, at the cost of space and of slower writes, since every insert and update must maintain it. The counterpart is DROP INDEX. CHANGE, ADD and REMOVE are not SQL statements in their own right.

Discuss in Forum

A subquery in an SQL SELECT statement:

A.can only be used with two tables.
B.can always be duplicated by a join.
C.has a distinct form that cannot be duplicated by a join.
D.cannot have its results sorted using ORDER BY.

Answer: C

Correct answer: has a distinct form that cannot be duplicated by a join.

Many subqueries can be rewritten as joins, but not all. A correlated EXISTS or NOT EXISTS, and a comparison against an aggregate of a related set - WHERE salary > (SELECT AVG(salary) FROM emp) - have no direct join equivalent.

The reason is that a join combines rows while a subquery can compute a value or test existence without producing rows. Subqueries are also not limited to two tables, and their outer query can certainly be sorted with ORDER BY.

Discuss in Forum

A subquery in an SQL SELECT statement is enclosed in:

A.braces -- {...}.
B.CAPITAL LETTERS.
C.parenthesis -- (...) .
D.brackets -- [...].

Answer: C

Correct answer: parenthesis -- (...) .

A subquery is written inside ordinary parentheses, as in WHERE id IN (SELECT id FROM other).

Braces and square brackets have other roles: square brackets delimit identifiers in some dialects, such as SQL Server's [Order Details], and braces appear in ODBC escape sequences - neither encloses a subquery in standard SQL. The parentheses are not decoration; they mark where the inner query ends, which is what lets subqueries nest inside one another.

Discuss in Forum

10.

________ was adopted as a national standard by ANSI in 1992.

A.Oracle
B.SQL
C.Microsoft Access
D.DBase

Answer: B

Correct answer: SQL

SQL was adopted as an ANSI standard in 1986 and substantially revised as SQL-92, which ANSI adopted in 1992 and which remains the reference point for the language's core.

SQL-92 fixed much of what is now taken for granted: the JOIN syntax in the FROM clause, the % and _ wildcards, and standard data types. The other options are products rather than standards - Oracle, Access and dBase are implementations, and it is precisely the standard that lets skills move between them.

Discuss in Forum

Loading…

Topics

Other Subjects

Tags: Introduction to SQL Questions and Answers || Introduction to SQL MCQ Questions and Answers || Introduction to SQL GK Questions and Answers || Introduction to SQL GK MCQ Questions || Introduction to SQL Multiple Choice Questions and Answers || Introduction to SQL GK || GK on Introduction to SQL || Database Questions and Answers || Database MCQ Questions and Answers || Database GK Questions and Answers || GK on Database

Introduction to SQL Questions and Answers

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications