Match the following statements in Group 1 with database objects in Group 2 :Group 1Group 2P. ViewI. that which contains dataQ. TableII. that which returns a valueR. RoleIII. that helps in managing groups of privilegesS. FunctionIV. that which contains only a stored SQL statement

The correct matching is P→IV, Q→I, R→III, S→II.A view stores only a SQL query, not data; its rows are produced when it is queried (IV).A table is the object that actually contains the data (I).A role is a named group of privileges that can be granted to users together (III).A function is a stored routine that returns a value (II).Option A gives the data to the view and the stored SQL to the function, B swaps role and function, and D gives the table the privileges and the role the data; only C matches all four.

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: Database contains two tables : Employee (id, name, salary, did) and Dept. (did, dname).Which of the following is an incorrect SQL query ?

The incorrect query is Insert values into employee (1, 'vijay', 100000, 12);.The SQL syntax is INSERT INTO table VALUES (...): the keyword INTO and the table name come before VALUES. The statement as written puts VALUES first and omits INTO, so it is a syntax error. The correct form is Insert into employee values (1, 'vijay', 100000, 12);.The others are valid. The natural join matches the two tables on their common column did. The delete uses a subquery with IN. The update raises the salary by 3% for rows where salary exceeds 100000.

Q: Which keyword is used to rename a field name in SQL query ?

The keyword is AS.AS gives a column (or table) an alias in the result of a query, for example SELECT name AS employee_name FROM employee; displays the column under the new heading without changing the table.ALTER changes the structure of a table (and with RENAME COLUMN can rename a column permanently, but that is a schema change, not part of a query). LIKE is for pattern matching in conditions, and FROM names the tables a query reads.

Q: Which of the following commands is used to save any transaction permanently into the database ?

The command is Commit.COMMIT ends a transaction and makes all its changes permanent in the database; after it, the changes survive failures and are visible to other transactions.ROLLBACK does the opposite, undoing the changes of the current transaction. Redo and undo are recovery operations the DBMS performs from its log, not commands for saving a transaction.

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

Match the following statements in Group 1 with database objects in Group 2 :

Group 1	Group 2
P. View	I. that which contains data
Q. Table	II. that which returns a value
R. Role	III. that helps in managing groups of privileges
S. Function	IV. that which contains only a stored SQL statement

A.P→I, Q→II, R→III, S→IV
B.P→II, Q→I, R→IV, S→III
C.P→IV, Q→I, R→III, S→II
D.P→IV, Q→III, R→I, S→II

Exam Year(s)

MH SET - 2023, SET Exam - 2023,

Answer: C

The correct matching is P→IV, Q→I, R→III, S→II.

A view stores only a SQL query, not data; its rows are produced when it is queried (IV).
A table is the object that actually contains the data (I).
A role is a named group of privileges that can be granted to users together (III).
A function is a stored routine that returns a value (II).

Option A gives the data to the view and the stored SQL to the function, B swaps role and function, and D gives the table the privileges and the role the data; only C matches all four.

Discuss in Forum

Database contains two tables : Employee (id, name, salary, did) and Dept. (did, dname).

Which of the following is an incorrect SQL query ?

A.Select name, dname from employee natural join Dept;
B.Delete from employee where did in (Select did from Dept);
C.Update employee set salary = salary * 1.03 where salary \(\gt\) 100000;
D.Insert values into employee (1, 'vijay', 100000, 12);

Exam Year(s)

MH SET - 2023, SET Exam - 2023,

Answer: D

The incorrect query is Insert values into employee (1, 'vijay', 100000, 12);.

The SQL syntax is INSERT INTO table VALUES (...): the keyword INTO and the table name come before VALUES. The statement as written puts VALUES first and omits INTO, so it is a syntax error. The correct form is Insert into employee values (1, 'vijay', 100000, 12);.

The others are valid. The natural join matches the two tables on their common column did. The delete uses a subquery with IN. The update raises the salary by 3% for rows where salary exceeds 100000.

Discuss in Forum

Which keyword is used to rename a field name in SQL query ?

A.AS
B.ALTER
C.LIKE
D.FROM

Exam Year(s)

MH SET - 2023, SET Exam - 2023,

Answer: B

The keyword is AS.

AS gives a column (or table) an alias in the result of a query, for example SELECT name AS employee_name FROM employee; displays the column under the new heading without changing the table.

ALTER changes the structure of a table (and with RENAME COLUMN can rename a column permanently, but that is a schema change, not part of a query). LIKE is for pattern matching in conditions, and FROM names the tables a query reads.

Discuss in Forum

10.

Which of the following commands is used to save any transaction permanently into the database ?

A.Redo
B.Commit
C.Rollback
D.Undo

Exam Year(s)

MH SET - 2024, SET Exam - 2024,

Answer: B

The command is Commit.

COMMIT ends a transaction and makes all its changes permanent in the database; after it, the changes survive failures and are visible to other transactions.

ROLLBACK does the opposite, undoing the changes of the current transaction. Redo and undo are recovery operations the DBMS performs from its log, not commands for saving a transaction.

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