ISE 224 Chapter 7
c
A set of SQL statements stored in an application written in a standard programming language is called ________. A) a stored procedure B) a view C) embedded SQL D) a trigger
d
A stored program that is attached to a table or view is called ________. A) a CHECK constraint B) a view C) embedded SQL D) a trigger
d
A stored program that is attached to the database is called ________. A) a view B) embedded SQL C) a trigger D) a stored procedure
d
According to the SQL-92 standard, statements used to construct views cannot contain ________. A) the SELECT clause B) the FROM clause C) the WHERE clause D) the ORDER BY clause
b
An SQL virtual table is called ________. A) a CHECK constraint B) a view C) embedded SQL D) a trigger
b
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 A) 0 B) 1 C) 2 D) it doesn't matter
d
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 A) SELECT Gonzales FROM CUSTOMER INSERT VALUES PLUS (100) INTO Balance; B) SELECT Gonzales FROM CUSTOMER INSERT VALUES (450) INTO Balance; C) INSERT INTO CUSTOMER VALUES (450) SELECT Balance WHERE CustName = 'Gonzales'; D) UPDATE CUSTOMER SET Balance = 450 WHERE CustName = 'Gonzales';
d
Because SQL statements are set-oriented, whereas programs are element-oriented, the results of SQL statements used in programs are treated as ________. A) tables B) rows C) files D) pseudofiles
c
Because SQL statements are table-oriented, whereas programs are element-oriented, the results of SQL statements used in programs are accessed using ________. A) standard programming tools B) custom written programming tools C) an SQL cursor D) an SQL trigger
d
Because SQL stored procedures allow and encourage code sharing among developers, stored procedures give database application developers all these advantages except ________. A) less work B) standardized processing C) specialization among developers D) faster query response times
c
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 ________. A) RepName is the primary key B) RepName is a foreign key C) RepName is a candidate key D) RepName is a surrogate key
a
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 ________. A) 123.45 B) 12345 C) 12345.00 D) 123450.00
d
If the values in an SQL view are changeable through the view itself, which SQL statement is used to change the values? A) CREATE B) DELETE C) INSERT D) UPDATE
b
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) a BEFORE trigger B) an INSTEAD OF trigger C) an AFTER trigger D) Depending on the specific logic, either A or B can be used.
c
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 ________. A) when a CUSTOMER is deleted, that CUSTOMER's SALESREP is also deleted B) when a CUSTOMER is deleted, all SALESREPS are also deleted C) when a SALESREP is deleted, all CUSTOMERs of that SALESREP are also deleted D) when the SALESREP table is dropped, all CUSTOMERs must be deleted
c
One advantage of using the CONSTRAINT phrase to define a primary key is that the database designer controls the ________. A) name of the table B) name of the foreign key field C) name of the constraint D) name of the primary key field
b
Referential integrity constraints are implemented in SQL using the ________ syntax. A) PRIMARY KEY B) FOREIGN KEY C) DEFAULT D) UNIQUE
a
SQL triggers are created using ________. A) the SQL CREATE TRIGGER statement B) the SQL ADD TRIGGER statement C) the SQL TRIGGER statement D) the SQL ADD CONSTRAINT TRIGGER statement
d
SQL triggers are not used for ________. A) validity checking B) providing default values C) updating views D) creating tables
a
SQL triggers can be used when the DBMS receives a(n) ________ request. A) INSERT B) SELECT C) ALTER D) CREATE
d
SQL views are always updatable when ________. A) the view is based on a single table with no computed columns, and all non-null columns are present in the view B) the view is based on any number of tables, with or without computed columns, and the INSTEAD OF trigger is defined for the view C) 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 D) Both A and B are correct
d
SQL views are constructed from ________. A) CREATE statements B) INSERT statements C) UPDATE statements D) SELECT statements
d
SQL views are not used ________. A) to hide columns B) to show results of computed columns C) to hide complicated SQL statements D) to enforce primary key constraints
b
Stored procedures have all these advantages except ________. A) greater security B) faster development C) SQL optimized by the DBMS compiler D) code sharing
b
The SQL command used to create a virtual table is ________. A) CREATE VTABLE B) CREATE VIEW C) VTABLE D) VIEW
b
The SQL keyword used to limit column values to specific values is ________. A) CONSTRAINT B) CHECK C) NOT NULL D) UNIQUE
d
To set a column value to an initial value that is selected according to some business logic, you would use ________. A) the SQL DEFAULT constraint with the CREATE TABLE command B) an SQL view C) embedded SQL D) an SQL trigger
false
True or false, SQL triggers are created using the SQL ADD TRIGGER statement
true
True or false, SQL triggers are used for providing default values, validity checking, updating views, and performing referential integrity actions
true
True or false, SQL triggers can be used when the DBMS receives an INSERT request
true
True or false, SQL triggers can be used when the DBMS receives an update request
true
True or false, SQL triggers can be used with SQL operations INSERT, UPDATE, and DELETE
true
True or false, SQL triggers use the ANSI SQL keywords BEFORE, INSTEAD OF, and AFTER
true
True or false, SQL views are constructed from SELECT statements
true
True or false, SQL views are updatable when the view is based on a single table with no computed columns, and all non-null columns are presented in the view
true
True or false, SQL views can be used to hide columns
true
True or false, a set of SQL statements stored in an application written in a standard programming language is called embedded SQL
false
True or false, a stored program that is attached to a table or view is called a stored procedure
false
True or false, a stored program that is stored within the database and compiled when used is called a trigger
false
True or false, according to the SQL-92 standard, statements used to construct views cannot contain the WHERE clause
true
True or false, an SQL virtual table is called a view
true
True or false, because SQL statements are table-oriented, whereas programs are variable-oriented, the results of SQL statements are used in programs are treated as pseudofiles
true
True or false, because SQL statements are table-oriented, whereas programs are variable-oriented, the results of SQL statements used in programs are accessed using an SQL cursor
true
True or false, because SQL stored procedures allow and encourage code sharing among developers, stored procedures give database application developers the advantages of less work, standardized processing, and specialization among developers
true
True or false, data values to be added to a table are specified by using the the SQL VALUES clause
false
True or false, if a trigger is being written to enforce referential integrity actions, you cannot use an INSTEAD OF trigger
true
True or false, if the table ITEM has a column WEIGHT, and WEIGHT has the data type Numeric (4,2), the value 4321 will be displayed by the DBMS as 43.21
false
True or false, if the table PRODUCT has a column PRICE, and PRICE has the data type Numeric (8,2), the value 98765 stored in that field will be displayed by the DBMS as 98765.00
true
True or false, if the values in an SQL view are changeable through the view itself, the SQL command UPDATE is used to change the values
true
True or false, 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 an INSTEAD OF trigger
true
True or false, one advantage of using the CONSTRAINT command to define a primary key is that the database designer controls the name of the constraint
true
True or false, one or more rows can be added to a table by using the SQL INSERT statement
true
True or false, rows can be removed from a table by using the SQL DELETE statement
true
True or false, rows in a table can be changed by using the SQL UPDAT statement
true
True or false, stored procedures have the advance of greater security, decreased network traffic, SQL optimized by the DBMS compiler, and code sharing
true
True or false, the Oracle DBMS supports the SQL BEFORE trigger
true
True or false, the SQL CREATE TABLE statement is used to name a new table and describe the table's columns
true
True or false, the SQL SET keyword is used to specify a new value when changing a column value
false
True or false, the SQL Server DBMS supports the SQL BEFORE trigger
false
True or false, the SQL command CREATE USER VIEW is used to create a virtual table
true
True or false, the SQL command SELECT is used to retrieve view instances
true
True or false, the SQL keyword CHECK is used to limit column values to specific values
true
True or false, the SQL keyword CONSTRAINT can be used in conjunction with the SQL keywords PRIMARY KEY and FOREIGN KEY
true
True or false, the SQL keyword CONSTRAINT is used to define one of several types of constraints
true
True or false, the SQL keyword CONSTRAINT is used to limit column values to specific values
false
True or false, the SQL keyword DELETE is used to delete a table's structure
false
True or false, the SQL keyword MODIFY is used to change a column value
false
True or false, the SQL keyword MODIFY is used to change the structure, properties, or constraints of a table
true
True or false, the SQL keyword PRIMARY KEY is used to designate the column(s) that are the primary key for the table
true
True or false, the SQL keyword UNIQUE is used to define alternate keys
true
True or false, the SQL views can be used to provide a level of insulation between data processed by applications and the data actually stored in the database tables
false
True or false, the values in an SQL view are always changeable though the view itself
true
True or false, the values in an SQL view are not always changeable through the view itself
false
True or false, to set a column value to an initial value that is selected according to some complicated business logic, you would use the SQL DEFAULT constraint with the CREATE TABLE command
true
True or false, unless it is being used to copy data from one table to another, the SQL INSERT statement can be used to insert only a single row into a table
true
True or false, unlike application code, stored procedures are never distributed to the client computers
true
True or false, when a trigger is fired, the DBMS makes the appropriate data available to the trigger code
false
True or false, when the correct SQL command is used to delete a table's structure, the command can only be used with a table that has already had its data removed
d
When a trigger is fired, the DBMS makes the appropriate data available to ________. A) the SQL interpreter B) the application code C) the embedded SQL code D) the trigger code
c
When the correct SQL command is used to delete a table's structure, what happens to the data in the table? A) If the deleted table was a parent table, the data is added to the appropriate rows of the child table. B) If the deleted table was a child table, the data is added to the appropriate rows of the parent table. C) The data in the table is also deleted. D) Nothing because there was no data in the table since only an empty table can be deleted.
b
Which SQL keyword is used to add one or more rows of data to a table? A) DELETE B) INSERT C) SELECT D) UPDATE
d
Which SQL keyword is used to change one or more rows in a table? A) MODIFY B) INSERT C) SELECT D) UPDATE
d
Which SQL keyword is used to change the structure, properties or constraints of a table? A) SET B) CREATE C) SELECT D) ALTER
d
Which SQL keyword is used to change the values of an entire column? A) CHANGE B) INSERT C) SELECT D) SET
b
Which SQL keyword is used to delete a table's structure? A) DELETE B) DROP C) DISPOSE D) ALTER
d
Which SQL keyword is used to impose restrictions on a table, data or relationship? A) SET B) CREATE C) SELECT D) CONSTRAINT
b
Which SQL keyword is used to name a new table and describe the table's columns? A) SET B) CREATE C) SELECT D) ALTER
d
Which SQL statement is used to retrieve view instances? A) CREATE B) DELETE C) INSERT D) SELECT
a
Which keyword is used to remove one or more rows from a table? A) DELETE B) INSERT C) ERASE D) SET
a
Which of the following does not describe a user-defined function? A) It computes a numeric value B) It can be called by name from within an SQL statement C) It may have input parameters passed to it D) It returns an output value to the calling statement
b
Which of the following illustrates the authors' preferred style of defining a primary key? A) CREATE TABLE CUSTOMER ( CustomerID Integer Primary Key LastName Char(35) Not Null First Name Char(25) Null ); B) CREATE TABLE CUSTOMER ( CustomerID Integer Not Null LastName Char(35) Not Null First Name Char(25) Null CONSTRAINT CustomerPK PRIMARY KEY (CustomerID) ); C) CREATE TABLE CUSTOMER ( CustomerID Integer Not Null LastName Char(35) Not Null First Name Char(25) Null ); ALTER TABLE CUSTOMER ADD CONSTRAINT CustomerPK PRIMARY KEY (CustomerID); D) Both B and C are correct
d
Which of the following is an SQL trigger Microsoft SQL Server supports? A) BEFORE B) INSTEAD OF C) AFTER D) Both B and C are correct
d
Which of the following is not an ANSI SQL trigger? A) BEFORE UPDATE B) INSTEAD OF UPDATE C) AFTER INSERT D) INSTEAD OF CONSTRAINT
d
Which of the following is not an SQL trigger Oracle supports? A) BEFORE B) INSTEAD OF C) AFTER D) DURING
