Database Management Quiz: Lesson 7

Based on the table below, a function to compute a 10% late penalty on a customer's balance would have how many input parameters? CUSTOMER CustNo CustName Balance SalesRepNo 9870 Winston 500 345 8590 Gonzales 350 434 7840 Harris 800 654 4870 Miles 100 345 0 1 2 it doesn't matter

1

If the table PRODUCT has a column PRICE that has the data type Numeric (8,2), the value 12345 will be displayed by the DBMS as ________. 123450.00 12345 12345.00 123.45

123.45

Which of the following illustrates the authors' preferred style of defining a primary key?

2nd Option

Which SQL keyword is used to change the structure, properties or constraints of a table? ALTER CREATE SET SELECT

ALTER

Which of the following is an SQL trigger Microsoft SQL Server supports? BEFORE INSTEAD OF AFTER Both INSTEAD OF and AFTER are correct

Both INSTEAD OF and AFTER are correct

SQL views are always updatable when ________. the view is based on a single table with no computed columns, and all non-null columns are present in the view the view is based on any number of tables, with or without computed columns, and the INSTEAD OF trigger is defined for the view the view is based on multiple tables, the update is being done on the most subordinate table, and the rows of that table can be uniquely identified Both the view is based on a single table with no computed columns, and all non-null columns are present in the view and the view is based on any number of tables, with or without computed columns, and the INSTEAD OF trigger is defined for the view are correct

Both the view is based on a single table with no computed columns, and all non-null columns are present in the view and the view is based on any number of tables, with or without computed columns, and the INSTEAD OF trigger is defined for the view are correct

The SQL keyword used to limit column values to specific values is ________. CHECK CONSTRAINT UNIQUE NOT NULL

CHECK

Which SQL keyword is used to impose restrictions on a table, data or relationship? CREATE CONSTRAINT SET SELECT

CONSTRAINT

Which SQL keyword is used to name a new table and describe the table's columns? ALTER CREATE SET SELECT

CREATE

The SQL command used to create a virtual table is ________. VTABLE CREATE VTABLE CREATE VIEW VIEW

CREATE VIEW

Which keyword is used to remove one or more rows from a table? ERASE INSERT SET DELETE

DELETE

Which SQL keyword is used to delete a table's structure? DELETE DROP ALTER DISPOSE

DROP

Which of the following is not an SQL trigger Oracle supports? INSTEAD OF AFTER DURING BEFORE

DURING

Referential integrity constraints are implemented in SQL using the ________ syntax. PRIMARY KEY UNIQUE FOREIGN KEY DEFAULT

FOREIGN KEY

SQL triggers can be used when the DBMS receives a(n) ________ request. ALTER INSERT SELECT CREATE

INSERT

Which SQL keyword is used to add one or more rows of data to a table? DELETE SELECT UPDATE INSERT

INSERT

Which of the following is not an ANSI SQL trigger? BEFORE UPDATE INSTEAD OF UPDATE INSTEAD OF CONSTRAINT AFTER INSERT

INSTEAD OF CONSTRAINT

Which of the following does not describe a user-defined function? It computes a numeric value It may have input parameters passed to it It can be called by name from within an SQL statement It returns an output value to the calling statement

It computes a numeric value

Given the SQL statement CREATE TABLE SALESREP( SalesRepNo int NOT NULL, RepName char(35) NOT NULL, HireDate date NOT NULL, CONSTRAINT SalesRepPK PRIMARY KEY(SalesRepNo), CONSTRAINT SalesRepAK1 UNIQUE(RepName) ); we know that ________. RepName is a surrogate key RepName is the primary key RepName is a foreign key RepName is a candidate key

RepName is a candidate key

Which SQL statement is used to retrieve view instances? DELETE SELECT INSERT CREATE

SELECT

SQL views are constructed from ________. UPDATE statements INSERT statements SELECT statements CREATE statements

SELECT statements

Which SQL keyword is used to change the values of an entire column? CHANGE INSERT SELECT SET

SET

When the correct SQL command is used to delete a table's structure, what happens to the data in the table? The data in the table is also deleted. If the deleted table was a parent table, the data is added to the appropriate rows of the child table. Nothing because there was no data in the table since only an empty table can be deleted. If the deleted table was a child table, the data is added to the appropriate rows of the parent table.

The data in the table is also deleted.

If the values in an SQL view are changeable through the view itself, which SQL statement is used to change the values? DELETE CREATE INSERT UPDATE

UPDATE

Which SQL keyword is used to change one or more rows in a table? UPDATE SELECT INSERT MODIFY

UPDATE

Based on the tables below, which of the following SQL statements would increase the balance of the Gonzales account by $100 to a total of $450? GENERAL SALES DATABASE: SALESREP SalesRepNo RepName HireDate 654 Jones 01/02/2005 734 Smith 02/03/2007 345 Chen 01/25/2004 434 Johnson 11/23/2004 CUSTOMER CustNo CustName Balance SalesRepNo 9870 Winston 500 345 8590 Gonzales 350 434 7840 Harris 800 654 4870 Miles 100 345 SELECT Gonzales FROM CUSTOMER INSERT VALUES PLUS (100) INTO Balance; INSERT INTO CUSTOMER VALUES (450) SELECT Balance WHERE CustName = 'Gonzales'; SELECT Gonzales FROM CUSTOMER INSERT VALUES (450) INTO Balance; UPDATE CUSTOMER SET Balance = 450 WHERE CustName = 'Gonzales';

UPDATE CUSTOMER SET Balance = 450 WHERE CustName = 'Gonzales';

A stored program that is attached to the database is called ________. a trigger a view a stored procedure embedded SQL

a stored procedure

A stored program that is attached to a table or view is called ________. a trigger embedded SQL a CHECK constraint a view

a trigger

An SQL virtual table is called ________. embedded SQL a view a CHECK constraint a trigger

a view

If the values in an SQL view are not changeable through the view itself, you may still be able to update the view by using unique application logic. In this case, the specific logic is placed in ________. a BEFORE trigger an INSTEAD OF trigger an AFTER trigger Depending on the specific logic, either a BEFORE trigger or an INSTEAD OF trigger can be used.

an INSTEAD OF trigger

Because SQL statements are table-oriented, whereas programs are element-oriented, the results of SQL statements used in programs are accessed using ________. standard programming tools an SQL trigger an SQL cursor custom written programming tools

an SQL cursor

To set a column value to an initial value that is selected according to some business logic, you would use ________. an SQL view the SQL DEFAULT constraint with the CREATE TABLE command embedded SQL an SQL trigger

an SQL trigger

SQL triggers are not used for ________. creating tables updating views validity checking providing default values

creating tables

A set of SQL statements stored in an application written in a standard programming language is called ________. a view embedded SQL a stored procedure a trigger

embedded SQL

Stored procedures have all these advantages except ________. code sharing faster development SQL optimized by the DBMS compiler greater security

faster development

Because SQL stored procedures allow and encourage code sharing among developers, stored procedures give database application developers all these advantages except ________. standardized processing faster query response times specialization among developers less work

faster query response times

One advantage of using the CONSTRAINT phrase to define a primary key is that the database designer controls the ________. name of the constraint name of the table name of the primary key field name of the foreign key field

name of the constraint

Because SQL statements are set-oriented, whereas programs are element-oriented, the results of SQL statements used in programs are treated as ________. pseudofiles tables files rows

pseudofiles

According to the SQL-92 standard, statements used to construct views cannot contain ________. the FROM clause the ORDER BY clause the SELECT clause the WHERE clause

the ORDER BY clause

SQL triggers are created using ________. the SQL ADD CONSTRAINT TRIGGER statement the SQL ADD TRIGGER statement the SQL TRIGGER statement the SQL CREATE TRIGGER statement

the SQL CREATE TRIGGER statement

When a trigger is fired, the DBMS makes the appropriate data available to ________. the trigger code the embedded SQL code the application code the SQL interpreter

the trigger code

SQL views are not used ________. to enforce primary key constraints to show results of computed columns to hide complicated SQL statements to hide columns

to enforce primary key constraints

If you have a foreign key in a CUSTOMER table that references the primary key in a SALESREP table, the ON DELETE CASCADE syntax means that ________. when a SALESREP is deleted, all CUSTOMERs of that SALESREP are also deleted when the SALESREP table is dropped, all CUSTOMERs must be deleted when a CUSTOMER is deleted, that CUSTOMER's SALESREP is also deleted when a CUSTOMER is deleted, all SALESREPS are also deleted

when a SALESREP is deleted, all CUSTOMERs of that SALESREP are also deleted