Deck 7: SQL for Database Construction and Application Processing

Question 1

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.

Answer 1

False

Question 2

The SQL keyword PRIMARY KEY is used to designate the column(s)that are the primary key for the table.

Answer 2

True

Question 3

The SQL SET keyword is used to specify a new value when changing a column value.

Answer 3

True

Question 4

The SQL keyword UNIQUE is used to define alternative keys.

Question 5

The SQL keyword MODIFY is used to change the structure,properties or constraints of a table.

Question 6

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.

Question 7

The SQL CREATE statement is used to name a new table and describe the table's columns.

Question 8

If the table ITEM has a column WEIGHT,and WEIGHT has the data type Numeric (7,2),the value 4321 will be displayed by the DBMS as 43.21.

Question 9

The SQL keyword CONSTRAINT is used in conjunction with the SQL keywords PRIMARY KEY and FOREIGN KEY.

Question 10

Rows can be removed from a table by using the SQL DELETE statement.

Question 11

One advantage of using the CONSTRAINT command to define a primary key is that the database designer controls the name of the constraint.

Question 12

Rows in a table can be changed by using the SQL UPDATE statement.

Question 13

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.

Question 14

The SQL keyword CHECK is used to limit column values to specific values.

Question 15

The SQL keyword CONSTRAINT is used to limit column values to specific values.

Question 16

Data values to be added to a table are specified by using the SQL VALUES clause.

Question 17

The SQL keyword CONSTRAINT is used to define one of five types of constraints.

Question 18

One or more rows can be added to a table by using the SQL INSERT statement.

Question 19

The SQL keyword MODIFY is used to change a column value.

Question 20

The SQL keyword DELETE is used to delete a table's structure.

Question 21

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

Question 22

The values in an SQL view are always changeable through the view itself.

Question 23

The SQL syntax JOIN ...ON can be used as an alternate way of writing an SQL join statement.

Question 24

Joins that show only matching rows from the joined tables in their results are called inner joins.

Question 25

According to the SQL-92,statements used to construct views cannot contain the WHERE clause.

Question 26

SQL views are constructed from SELECT statements.

Question 27

The SQL command SELECT is used to retrieve view instances.

Question 28

Because SQL statements are table-oriented,whereas programs are variable-oriented,the results of SQL statements used in programs are treated as pseudofiles.

Question 29

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

Question 30

If the values in an SQL view are changeable through the view itself,the SQL command UPDATE is used to change the values.

Question 31

SQL views can be used to hide columns.

Question 32

Joins that show the matching rows from the joined tables plus unmatched rows from one other table in their results are called outer joins.

Question 33

The values in an SQL view are not always changeable through the view itself.

Question 34

An SQL virtual table is called a view.

Question 35

SQL views are updatable when the view is based on a single table with no computed columns,and all non-null columns are present in the view.

Question 36

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

Question 37

Outer joins can be either up joins or down joins.

Question 38

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.

Question 39

SQL views can be use used to provide a level of insolation between data processed by applications and the data actually stored in the database tables.

Question 40

SQL triggers use the ANSI SQL keywords BEFORE,INSTEAD OF,and AFTER.

Question 41

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

A)SET
B)CREATE
C)SELECT
D)ALTER
E)CONSTRAINT

Question 42

SQL triggers can be used when the DBMS receives an INSERT request.

Question 43

A stored program that is stored within the database and compiled when used is called a trigger.

Question 44

SQL triggers can be used when the DBMS receives an insert request.

Question 45

SQL triggers are created using the SQL ADD TRIGGER statement.

Question 46

The Oracle DBMS supports the SQL BEFORE trigger.

Question 47

Stored procedures have the advantage of greater security,decreased network traffic,SQL optimized by the DBMS compiler,and code sharing.

Question 48

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.

Question 49

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

A)SET
B)CREATE
C)SELECT
D)ALTER
E)CONSTRAINT

Question 50

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

Question 51

SQL triggers are used for providing default values,validity checking,updating views,and performing referential integrity actions.

Question 52

If a trigger is being written to enforce referential integrity actions,you cannot use an INSTEAD OF trigger.

Question 53

Unlike application code,stored procedures are never distributed to the client computers.

Question 54

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
D)either B or C
E)The author does not demonstrate a preference for how to define a primary key.

Question 55

The SQL Server DBMS supports the SQL BEFORE trigger.

Question 56

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.

Question 57

When a trigger is fired,the DBMS makes the appropriate data available to the trigger code.

Question 58

One advantage of using the CONSTRAINT phrase to define a primary is that the database designer controls the ________.

A)name of the primary key
B)name of the foreign key
C)name of the constraint
D)A and B
E)A,B and C

Question 59

SQL triggers can be used with SQL operations INSERT UPDATE,and DELETE.

Question 60

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
E)00012345

Question 61

SQL views are always updateable 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)A and B
E)A,B and C

Question 62

Based on the tables below,which of the following SQL statements would increase the balance of the Gonzales by $100 to a total of $450? GENERAL SALES DATABASE:
SALESREP

CUSTOMER

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 PLUS (100)
SELECT Balance
WHERE CustName = 'Gonzales';
D)INSERT INTO CUSTOMER VALUES (450)
SELECT Balance
WHERE CustName = 'Gonzales';
E)UPDATE CUSTOMER
SET Balance = 450
WHERE CustName = 'Gonzales';

Question 63

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

A)DELETE
B)INSERT
C)SELECT
D)SET
E)UPDATE

Question 64

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.
E)A and B

Question 65

Which SQL keyword is used to change the values of an entire column?

A)CHANGE
B)INSERT
C)SELECT
D)SET
E)MODIFY

Question 66

The SQL keyword used to limit column values to specific values is ________.

A)CONSTRAINT
B)CHECK
C)NOT NULL
D)UNIQUE
E)UPDATE

Question 67

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

A)SET
B)CREATE
C)SELECT
D)ALTER
E)CONSTRAINT

Question 68

Which SQL keyword is used to change one or more rows in a table?

A)MODIFY
B)INSERT
C)SELECT
D)CHANGE
E)UPDATE

Question 69

In an SQL query of two tables,which SQL keyword indicates that we want data from all the rows of one table to be included in the result,even if the row does not correspond to any data in the other table?

A)LEFT JOIN
B)RIGHT JOIN
C)INCLUDE
D)A and B
E)A,B and C

Question 70

According to the SQL-92,statements used to construct views cannot contain ________.

A)the SELECT clause
B)the FROM clause
C)the WHERE clause
D)the ORDER BY clause
E)SQL view statements can use all of the listed clauses.

Question 71

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

A)a CHECK constraint
B)a view
C)embedded SQL
D)a trigger
E)a stored procedure

Question 72

Which keyword is used to remove one or more rows from a table?

A)DELETE
B)INSERT
C)ERASE
D)SET
E)UPDATE

Question 73

An SQL virtual table is called ________.

A)a CHECK constraint
B)a view
C)embedded SQL
D)a trigger
E)a stored procedure

Question 74

SQL views are constructed from ________.

A)CREATE statements
B)INSERT statements
C)UPDATE statements
D)SELECT statements
E)VIEW statements

Question 75

SQL views are used ________.

A)to hide columns
B)to show results of computed columns
C)to hide complicated SQL statements
D)to provide a level of indirection between data processed by applications and the data actually stored in the database tables
E)SQL views are used for all of the above.

Question 76

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

A)CREATE VTABLE
B)CREATE VIEW
C)VTABLE
D)VIEW
E)NEWLOOK

Question 77

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)SELECT
E)UPDATE

Question 78

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
E)None of the above is true.

Question 79

Which SQL keyword is used to delete a table's structure?

A)DELETE
B)DROP
C)DISPOSE
D)ALTER
E)MODIFY

Question 80

Which SQL statement is used to retrieve view instances?

A)CREATE
B)DELETE
C)INSERT
D)SELECT
E)UPDATE