SQL for Database Construction Questions and Answers

Q: Common types of SQL CHECK constraints include range checks and limiting columns values.

Correct answer: TrueRange checks and lists of permitted values are two of the three common forms, the third being a comparison between two columns of the same row. All three are conditions the DBMS evaluates on every insert and update, so the rule holds however the data arrives - through an application, a script or a manual statement.

Q: SQL statements can be embedded in triggers, stored procedures, and program code.

Correct answer: TrueThe same SQL serves in all three settings, which is what makes the language useful across the whole application. Embedded in program code it is executed through an API such as JDBC or ODBC; inside a trigger it runs automatically on an event; inside a stored procedure it runs when the procedure is called.

Q: Three types of triggers are: BEFORE, AFTER and EXCEPTION.

Correct answer: FalseThe three types are BEFORE, AFTER and INSTEAD OF. There is no EXCEPTION trigger; exception handling is a feature of the procedural language a trigger is written in rather than a kind of trigger. Oracle supports all three types, while SQL Server has only AFTER and INSTEAD OF.

Q: A ________ is a stored program that is attached to a table or a view.

Correct answer: triggerA trigger is a stored program attached to a particular table or view and fired automatically when a specified event occurs on it - an insert, update or delete. The attachment is what distinguishes it from a stored procedure, which belongs to the database as a whole and must be called explicitly.

Q: The SQL statement to create a view is:

Correct answer: CREATE VIEW.CREATE VIEW names the view and gives the SELECT that defines it. Nothing is stored but the definition: the view holds no data of its own and is evaluated when it is queried, which is what makes it a virtual table and what keeps it automatically current.

Q: The SQL CHECK constraint is fully defined by the SQL-92 standard, and is consistently implemented by all DBMS vendors.

Correct answer: FalseCHECK is defined by the standard but implemented with real differences between products - in what expressions are permitted, whether subqueries may appear, and when the condition is evaluated. Portable code must therefore keep CHECK constraints simple, and complex rules are often placed in triggers instead.

31.

Common types of SQL CHECK constraints include range checks and limiting columns values.

A.True
B.False

Answer: A

Correct answer: True

Range checks and lists of permitted values are two of the three common forms, the third being a comparison between two columns of the same row. All three are conditions the DBMS evaluates on every insert and update, so the rule holds however the data arrives - through an application, a script or a manual statement.

Discuss in Forum

32.

SQL statements can be embedded in triggers, stored procedures, and program code.

A.True
B.False

Answer: A

Correct answer: True

The same SQL serves in all three settings, which is what makes the language useful across the whole application. Embedded in program code it is executed through an API such as JDBC or ODBC; inside a trigger it runs automatically on an event; inside a stored procedure it runs when the procedure is called.

Discuss in Forum

33.

When using the SQL INSERT statement:

A.rows can be modified according to criteria only.
B.rows cannot be copied in mass from one table to another only.
C.rows can be inserted into a table only one at a time only.
D.rows can either be inserted into a table one at a time or in groups.

Answer: D

Correct answer: rows can either be inserted into a table one at a time or in groups.

INSERT takes either a VALUES clause for a single row or a SELECT in place of it, in which case every row the query returns is inserted. The second form is how a table is populated in bulk from another, and it is why loading data does not require a row-by-row loop.

Discuss in Forum

34.

Three types of triggers are: BEFORE, AFTER and EXCEPTION.

A.True
B.False

Answer: B

Correct answer: False

The three types are BEFORE, AFTER and INSTEAD OF. There is no EXCEPTION trigger; exception handling is a feature of the procedural language a trigger is written in rather than a kind of trigger. Oracle supports all three types, while SQL Server has only AFTER and INSTEAD OF.

Discuss in Forum

35.

A ________ is a stored program that is attached to a table or a view.

A.pseudofile
B.embedded SELECT statement
C.trigger
D.None of the above is correct.

Answer: C

Correct answer: trigger

A trigger is a stored program attached to a particular table or view and fired automatically when a specified event occurs on it - an insert, update or delete. The attachment is what distinguishes it from a stored procedure, which belongs to the database as a whole and must be called explicitly.

Discuss in Forum

36.

The SQL statement to create a view is:

A.CREATE VIEW.
B.MAKE VIEW.
C.SELECT VIEW.
D.INSERT VIEW.

Answer: A

Correct answer: CREATE VIEW.

CREATE VIEW names the view and gives the SELECT that defines it. Nothing is stored but the definition: the view holds no data of its own and is evaluated when it is queried, which is what makes it a virtual table and what keeps it automatically current.

Discuss in Forum

37.

The SQL CHECK constraint is fully defined by the SQL-92 standard, and is consistently implemented by all DBMS vendors.

A.True
B.False

Answer: B

Correct answer: False

CHECK is defined by the standard but implemented with real differences between products - in what expressions are permitted, whether subqueries may appear, and when the condition is evaluated. Portable code must therefore keep CHECK constraints simple, and complex rules are often placed in triggers instead.

Discuss in Forum

38.

The DROP TABLE statement:

A.deletes the table structure only.
B.deletes the table structure along with the table data.
C.works whether or not referential integrity constraints would be violated.
D.is not an SQL statement.

Answer: B

Correct answer: deletes the table structure along with the table data.

DROP TABLE removes the table entirely: definition, data, indexes and constraints together. It is quite different from DELETE, which removes rows and leaves the table in place. It also fails if a foreign key elsewhere still references the table, since dropping it would break referential integrity.

Discuss in Forum

39.

To update an SQL view, the DBMS must be able to associate the column(s) to be updated with:

A.a particular column in a particular underlying table.
B.a particular column in a particular row.
C.a particular row in a particular underlying table.
D.None of the above is correct.

Answer: C

Correct answer: a particular row in a particular underlying table.

To apply an update the DBMS must know which row of which base table the change belongs to, and that is possible only if the view carries the key and the column maps to a single stored column. Where a view aggregates, joins ambiguously or computes a column, no such row exists and the update cannot be performed.

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