SQL for Database Construction Questions and Answers

Q: Suppose you own a student table, the correct syntax for giving delete privileges to all users of the database with one SQL statement is :

The correct statement is Grant delete on student to public;.The SQL syntax is GRANT privilege ON object TO grantee. The privilege (DELETE) comes first, then ON and the table name, then TO and the receiver. PUBLIC is the special grantee meaning every user of the database, so a single statement grants the privilege to all users.'all' is not a grantee name (ALL is used for privileges, as in GRANT ALL), so the option ending 'to all' is wrong, and the two options that place TO before ON have the clauses in the wrong order.

Q: The SQL statement used to create a view is CREATE VIEW.

Correct answer: TrueCREATE VIEW gives the view a name and the SELECT that defines it, and nothing but that definition is stored. The rows are produced when the view is queried, which is what keeps it current and what makes it a virtual table rather than a copy.

Q: What is an advantage of placing computations in SQL views?

Correct answer: To accomplish both of the above.Both reasons matter and the second is the stronger. Putting the expression in the view saves every user from writing it, and more importantly ensures they all use the same one - so a margin or a total means the same thing wherever it is quoted, and a change to the definition takes effect everywhere at once.

Q: For what purposes are views used?

Correct answer: All of the above are uses for SQL views.All three are standard uses and they often coincide. Restricting columns hides sensitive data and simplifies the result; restricting rows limits what a user may see; and wrapping a complicated statement behind a name means a user can query it as a simple table without knowing the joins beneath.

Q: If any required (NOT NULL) columns are missing from the view, the view cannot be used for inserting new data.

Correct answer: TrueAn insert through a view supplies values only for the columns the view exposes, so a required column omitted from the view would be left null in the base table and the insert would fail. The view can therefore be used for reading and for updating existing rows, but not for inserting new ones.

Q: A benefit of using a stored procedure is that it can be distributed to client computers.

Correct answer: FalseA stored procedure is by definition stored and executed in the database; distributing it to clients would defeat both of its advantages, the reduction in network traffic and the single point of maintenance. What can be distributed is a call to it, which is all the client needs to know.

Q: SQL has a JOIN ON syntax that can be used to create inner joins and outer joins.

Correct answer: TrueThe JOIN ... ON syntax expresses the join condition explicitly and supports INNER, LEFT, RIGHT and FULL OUTER forms. It is preferred to putting the condition in the WHERE clause because the join and the filtering are then visibly separate, and because an outer join cannot be written the older way at all.

Q: A trigger is a stored program that is attached to a database.

Correct answer: FalseA trigger is attached to a particular table or view, not to the database as a whole, and it fires on an event affecting that object. A program belonging to the database and called explicitly is a stored procedure, and keeping the two apart matters because they are invoked in quite different ways.

Q: A updatable view has its own data.

Correct answer: FalseNo view has data of its own, updatable or not. A view stores only its defining SELECT, and an update through it is translated into an update on the underlying tables. That is precisely why updatability is restricted: the DBMS must be able to identify the row of the base table to which the change applies.

Suppose you own a student table, the correct syntax for giving delete privileges to all users of the database with one SQL statement is :

A.Grant delete to all on student;
B.Grant delete on student to all;
C.Grant delete to public on student;
D.Grant delete on student to public;

Exam Year(s)

MH SET - 2019, SET Exam - 2019,

Answer: D

The correct statement is Grant delete on student to public;.

The SQL syntax is GRANT privilege ON object TO grantee. The privilege (DELETE) comes first, then ON and the table name, then TO and the receiver. PUBLIC is the special grantee meaning every user of the database, so a single statement grants the privilege to all users.

'all' is not a grantee name (ALL is used for privileges, as in GRANT ALL), so the option ending 'to all' is wrong, and the two options that place TO before ON have the clauses in the wrong order.

Discuss in Forum

The SQL statement used to create a view is CREATE VIEW.

A.True
B.False

Answer: A

Correct answer: True

CREATE VIEW gives the view a name and the SELECT that defines it, and nothing but that definition is stored. The rows are produced when the view is queried, which is what keeps it current and what makes it a virtual table rather than a copy.

Discuss in Forum

SQL views can be used to hide:

A.columns and rows only.
B.complicated SQL syntax only.
C.both of the above can be hidden by an SQL view.
D.None of the above is correct.

Answer: C

Correct answer: both of the above can be hidden by an SQL view.

A view can restrict which columns and which rows a user sees, which serves security and simplicity at once; and because the view is defined by a SELECT, the joins, subqueries and computations in that statement are hidden behind a name. Users then query the view as though it were a simple table.

Discuss in Forum

What is an advantage of placing computations in SQL views?

A.To save users from having to write an expression.
B.To ensure that the results are consistent.
C.To accomplish both of the above.
D.None of the above is correct - computations cannot be placed in a view.

Answer: C

Correct answer: To accomplish both of the above.

Both reasons matter and the second is the stronger. Putting the expression in the view saves every user from writing it, and more importantly ensures they all use the same one - so a margin or a total means the same thing wherever it is quoted, and a change to the definition takes effect everywhere at once.

Discuss in Forum

For what purposes are views used?

A.To hide columns only
B.To hide rows only
C.To hide complicated SQL statements only
D.All of the above are uses for SQL views.

Answer: D

Correct answer: All of the above are uses for SQL views.

All three are standard uses and they often coincide. Restricting columns hides sensitive data and simplifies the result; restricting rows limits what a user may see; and wrapping a complicated statement behind a name means a user can query it as a simple table without knowing the joins beneath.

Discuss in Forum

If any required (NOT NULL) columns are missing from the view, the view cannot be used for inserting new data.

A.True
B.False

Answer: A

Correct answer: True

An insert through a view supplies values only for the columns the view exposes, so a required column omitted from the view would be left null in the base table and the insert would fail. The view can therefore be used for reading and for updating existing rows, but not for inserting new ones.

Discuss in Forum

A benefit of using a stored procedure is that it can be distributed to client computers.

A.True
B.False

Answer: B

Correct answer: False

A stored procedure is by definition stored and executed in the database; distributing it to clients would defeat both of its advantages, the reduction in network traffic and the single point of maintenance. What can be distributed is a call to it, which is all the client needs to know.

Discuss in Forum

SQL has a JOIN ON syntax that can be used to create inner joins and outer joins.

A.True
B.False

Answer: A

Correct answer: True

The JOIN ... ON syntax expresses the join condition explicitly and supports INNER, LEFT, RIGHT and FULL OUTER forms. It is preferred to putting the condition in the WHERE clause because the join and the filtering are then visibly separate, and because an outer join cannot be written the older way at all.

Discuss in Forum

A trigger is a stored program that is attached to a database.

A.True
B.False

Answer: B

Correct answer: False

A trigger is attached to a particular table or view, not to the database as a whole, and it fires on an event affecting that object. A program belonging to the database and called explicitly is a stored procedure, and keeping the two apart matters because they are invoked in quite different ways.

Discuss in Forum

10.

A updatable view has its own data.

A.True
B.False

Answer: B

Correct answer: False

No view has data of its own, updatable or not. A view stores only its defining SELECT, and an update through it is translated into an update on the underlying tables. That is precisely why updatability is restricted: the DBMS must be able to identify the row of the base table to which the change applies.

Discuss in Forum

Loading…

Topics

Other Subjects

Tags: SQL for Database Construction Questions and Answers || SQL for Database Construction MCQ Questions and Answers || SQL for Database Construction GK Questions and Answers || SQL for Database Construction GK MCQ Questions || SQL for Database Construction Multiple Choice Questions and Answers || SQL for Database Construction GK || GK on SQL for Database Construction || Database Questions and Answers || Database MCQ Questions and Answers || Database GK Questions and Answers || GK on Database

SQL for Database Construction Questions and Answers

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications