Free Printable AIT 524 SQL Chapter 13

Worksheets

AIT 524 SQL Chapter 13

Total questions: 105

Worksheet time: 52mins

Name

Class

Date

Views can be used to restrict a user's access to sensitive data.

true

false

A view can be created to simplify issuing complex SQL queries.

true

false

Views are database objects that store data.

true

false

Views are not database objects.

true

false

A view name must be enclosed in single quotation marks if it is referenced in the FROM clause of a SELECT statement.

true

false

A simple view is based upon a subquery that references only one table and doesn't contain any group functions, expressions, or a GROUP BY clause.

true

false

A complex view cannot contain any group functions.

true

false

A complex view can retrieve data from more than one table.

true

false

An inline view is a temporary table that only lasts for the duration of the SELECT statement used to create it.

true

false

10.

A view can be modified using the MODIFY clause of the ALTER VIEW command.

true

false

11.

You cannot create a view if the underlying table does not exist at the time the view is created.

true

false

12.

A view cannot be given the same name as another database object.

true

false

13.

The column names used in the view must be the same names as the column names in the underlying table(s).

true

false

14.

Column aliases can be used in the subquery to assign new column names to the columns contained in a view.

true

false

15.

The WITH READ ONLY option can be used to prevent changes from being made to the data displayed by the view.

true

false

16.

A simple view cannot contain a simple arithmetic operation.

true

false

17.

Any DML operation can be performed on a simple view that was not created with the WITH READ ONLY option, unless it violates an existing constraint.

true

false

18.

Rows cannot be added to a simple view.

true

false

19.

Rows can be updated through a simple view as long as the operation does not violate existing constraints and the view was created with the WITH READ ONLY option.

true

false

20.

DML operations can be performed through a simple view even if the operation will violate constraints on the underlying table.

true

false

21.

Rows can be deleted through a simple view as long as the operation does not violate existing constraints and the view was created with the WITH READ ONLY option.

true

false

22.

Update operations cannot be performed through a complex view that contains an arithmetic expression.

true

false

23.

Rows in an underlying table cannot be deleted through a complex view that contains an arithmetic operation.

true

false

24.

Values cannot be inserted through a view into columns that are based on arithmetic expressions.

true

false

25.

When a view includes columns from more than one table, updates can only be applied to the table that includes the primary key for the view.

true

false

26.

DML operations cannot be performed on a key-preserved table.

true

false

27.

Rows can be added through a complex view that is based upon grouped data.

true

false

28.

DML operations can be performed on a view created with the DISTINCT keyword.

true

false

29.

An ORDER BY clause can be used to perform "TOP-N" analysis.

true

false

30.

"TOP-N" analysis is performed by sorting values in ascending or descending order in an inline view.

true

false

31.

A(n) view is a database object, but it does not actually store data.

true

false

32.

A view can be thought of as the result of a(n) stored query.

true

false

33.

A(n) simple view can contain a GROUP BY clause.

true

false

34.

An inline view is used to create a(n) permanent table.

true

false

35.

A(n) existing view cannot be modified.

true

false

36.

The NOFORCE keyword can be used to create a view based upon a table that has not yet been created.

true

false

37.

FORCE is the default mode for the CREATE VIEW command.

true

false

38.

The WITH CHECK OPTION can be used when a view is created to ensure that any DML operations performed on the view will not prevent the row from being accessible by the view at a later time.

true

false

39.

The WITHOUT UPDATE keywords prevent DML operations from being performed through a view.

true

false

40.

When a query references a view, the query in the view is processed, and the results are treated as a(n) permanent table.

true

false

41.

The OR REPLACE clause is only necessary if another view may already exist with the same name.

true

false

42.

The UPDATE command cannot be used on a view created with the WITH CHECK OPTION.

true

false

43.

Any DDL operation can be performed on a simple view that was not created with the WITH READ ONLY option, unless it violates an existing constraint.

true

false

44.

A(n) complex view can contain data from more than one table.

true

false

45.

A(n) UPDATE operation can be performed on a complex view that contains an arithmetic operation.

true

false

46.

The DELETE operation can be performed through a complex view that contains an arithmetic operation.

true

false

47.

DML operations cannot be performed on non key-preserved tables through a complex view.

true

false

48.

Rows cannot be added to a table through a complex view that was created with the ORDER BY clause.

true

false

49.

Rows cannot be deleted from a table through a complex view that is based on a group function.

true

false

50.

Rows cannot be added to a table through a complex view that is based on a group function.

true

false

51.

Rows cannot be deleted if the complex view was created with a(n) GROUP BY clause.

true

false

52.

A view can be dropped or deleted using the DELETE VIEW command.

true

false

53.

An inline analysis can be used to find the “Top-N” values.

true

false

54.

The pseudo column ROW can be used to perform a "TOP-N" analysis.

true

false

55.

"TOP-N" analysis can be used to find the highest values in a column by sorting the data in ascending order.

true

false

56.

A(n) ____ stores a query and is used to access data in the underlying tables.

view

constraint

function

argument

57.

Which of the following statements about views is incorrect?

Views assist users who do not have the training to issue complex SQL inquiries.

Views restrict users' access to sensitive data.

Views are database objects that actually store data.

A view can be referenced in a SELECT...FROM statement, just like any table.

58.

Which of the following describes a subquery used in a FROM clause to create a "temporary" table that can be referenced by the SELECT and WHERE clauses of the outer query?

simple view

complex view

inline view

"TOP-N" analysis

59.

Which of the following describes a type of view that is based on a subquery that retrieves or derives data from one or more tables, and may also contain functions or grouped data?

simple view

complex view

inline view

"TOP-N" analysis

60.

Which of the following describes a type of view that is based upon a subquery that only references one table and does not include any group functions, expressions, or GROUP BY clauses?

simple view

complex view

inline view

"TOP-N" analysis

61.

Which of the following types of views cannot include a group function?

simple view

inline view

complex view

all apply

62.

Which of the following types of views cannot include a GROUP BY clause?

simple view

inline view

complex view

all apply

63.

Which of the following types of views cannot include an arithmetic expression?

simple view

inline view

complex view

all apply

64.

Which of the following statements is incorrect?

A view can be created with the CREATE VIEW command.

Views can be modified by using the ALTER VIEW...

A view cannot be modified; if you need to change it, you must use the CREATE OR REPLACE VIEW keywords.

A view cannot be given the same name as another database object in the same schema.

65.

If you want to create a view based upon a table or tables that do not yet exist, or are currently unavailable (e.g.,off-line), what keyword can you use to avoid receiving an error message?

FORCE

NOERROR

OVERRIDE

none apply

66.

What is the default mode for the CREATE VIEW command?

COMPILE

NOCOMPILE

FORCE

NOFORCE

67.

Which type of view is created with the following command?

CREATE VIEW inventory

AS SELECT isbn, title, retail price

FROM books

WITH READ ONLY;

simple

complex

derived

inline

68.

What is the procedure for assigning new names for the columns that are displayed by a view?

The new column names can be listed after the VIEW keyword, enclosed in parentheses.

Column aliases can be used in the subquery, and Oracle12c will use the aliases as column names in the view that is created.

both apply

neither apply

69.

Which of the following types of views cannot contain grouped data?

simple

complex

inline view

all apply

70.

____ aren’t allowed in the CREATE VIEW command.

Arithmetic expressions

ORDER BY clauses

Group functions

GROUP BY clauses

71.

The ____ constraint ensures that any DML operations performed on the view (e.g., adding rows, changing data) will not prevent the row from being accessed by the view because it no longer meets the condition in the WHERE clause.

ON UPDATE CHECK

WITH CHECK OPTION

WITH READ ONLY

READ-WRITE ONLY

72.

Which constraint ensures that the data in a view cannot be changed?

WITH CHECK OPTION

WITH READ OPTION

WITH READ ONLY

NO WRITE OPTION

73.

Which statement is true about the view created with the following command?

CREATE VIEW inventory AS SELECT isbn, title, retail price

FROM books

WITH READ ONLY;

The command creates a simple view.

DML operations are NOT allowed on the data displayed by the view.

A view named INVENTORY did not previously exist.

all apply

74.

Which statement is not true about the view created with the following command?

CREATE VIEW inventory

AS SELECT isbn, title, retail price

FROM books;

The command creates a simple view.

DML operations are not allowed on the data displayed by the view.

A view named INVENTORY did not previously exist.

all apply

75.

The OR REPLACE clause is not required if ____.

another view does not exist with the same name

the view is not based on a group function

the view does not contain data from more than one table

the CREATE command does not specify the WITH CHECK OPTION option

76.

Which type of view is created with the following command?

CREATE VIEW OR REPLACE outstanding

AS SELECT customer#, order#, orderdate, shipdate

FROM orders

WHERE shipdate IS NULL

WITH READ ONLY;

simple

complex

derived

inline

77.

Which statement is true about the view created with the following command?

CREATE OR REPLACE VIEW outstanding

AS SELECT customer#, order#, orderdate, shipdate

FROM orders

WHERE shipdate IS NULL

WITH READ ONLY;

The order# of an order cannot be changed through the view.

The shipping date of an order cannot be changed through the view.

No DML operations are permitted through the view.

all apply

78.

Which of the following options will prevent any DML operations from being performed on the underlying table of a view?

WITH READ ONLY

WITH CHECK ONLY

WITH CHECK OPTION

NO WRITE OPTION

79.

Which statement is true about the view created with the following command?

CREATE OR REPLACE VIEW inventory

AS SELECT isbn, title, retail price

FROM books;

The command creates a complex view.

DML operations are not allowed on the data displayed by the view.

A database object named INVENTORY may already exist.

all apply

80.

A user can perform a DML operation (add, modify, delete) on a simple view if it does not violate which type of existing constraint on the underlying base table?

PRIMARY KEY

WITH CHECK OPTION

UNIQUE

all apply

81.

If a view was created with the WITH READ ONLY constraint, to remove the constraint you will need to ____.

use the ALTER VIEW command

re-create the view without the option

drop the option

82.

Which statement is true about the view created with the following command?

CREATE VIEW outstanding

AS SELECT customer#, order#, orderdate, shipdate

FROM orders

WHERE shipdate IS NULL

WITH CHECK OPTION;

The order# of an order cannot be changed through the view.

The shipping date of an order cannot be changed through the view.

No DML operations are permitted through the view.

none apply

83.

Which type of view is created with the following command?

CREATE VIEW outstanding

AS SELECT customer#, order#, orderdate, shipdate

FROM orders

WHERE shipdate IS NULL

WITH CHECK OPTION;

complex

partial

simple

none apply

84.

If a view was created with the WITH CHECK OPTION constraint, to remove the constraint you will need to ____.

use the ALTER VIEW command

re-create the view without the option

rename the view

drop the option

85.

Which of the following statements about complex views is incorrect?

It is created with the same CREATE VIEW command as a simple view.

It retrieves or derives data from one or more tables.

All DML operations can be performed on complex views, just like simple views.

It may contain functions or grouped data.

86.

A view based on the contents of one table that uses an expression for one of the columns is considered a(n) ____ view.

simple

complex

inline

outer

87.

Which statement is true about the view created with the following command?

CREATE VIEW OR REPLACE outstanding

AS SELECT customer#, order#, orderdate, shipdate

FROM orders

WHERE shipdate IS NULL;

The order# of an order can be changed through the view.

The shipping date of an order can be changed through the view.

A new order can be added through the view as long as no constraints on the underlying table are violated.

all apply

88.

Which type of view is created by the following command?

CREATE VIEW OR REPLACE outstanding

AS SELECT customer#, order#, orderdate, shipdate

FROM orders

WHERE shipdate IS NULL;

simple

complex

inline

none apply

89.

Which statement about the view created from the following command is correct, assuming no constraints exist on the underlying table?

CREATE VIEW prices

AS SELECT isbn, title, cost, retail, retail-cost profit

FROM books;

The retail price of a book can be changed.

The profit generated by a book can be changed.

The profit generated by a book can be added.

Values for a new book can be added for all five columns displayed by the view.

90.

Which type of view is created from the following command?

CREATE VIEW prices

AS SELECT isbn, title, cost, retail, retail-cost profit

FROM books;

simple

inline

complex

derived

91.

Which of the following statements about performing DML operations on complex views is correct?

Values cannot be inserted into columns containing date data.

Values cannot be inserted into columns that are based on arithmetic expressions.

Values cannot be inserted into columns containing an ORDER BY clause.

92.

Which SQL statement can be executed based upon the view created from the following command, assuming no constraints exist on the underlying table?

CREATE VIEW prices

AS SELECT isbn, title, cost, retail, retail-cost profit

FROM books;

INSERT INTO prices (title, cost, retail, profit)

VALUES ('A NEW BOOK', 49.99, 69.99, 20);

INSERT INTO prices (title, cost, retail, profit)

VALUES ('A SECOND BOOK', 49.99, 39.99, -10);

INSERT INTO prices (title, cost, retail)

VALUES ('A NEW BOOK', 49.99, 69.99);

none apply

93.

Which type of view is created from the following SQL command?

CREATE OR REPLACE VIEW prices

AS SELECT isbn, title, cost, retail, retail-cost profit, name

FROM books NATURAL JOIN publisher;

complex

simple

inline

none apply

94.

Which statement about the view created from the following SQL command is correct, assuming ISBN from the BOOKS table is the primary key used by the view?

CREATE OR REPLACE VIEW prices

AS SELECT isbn, title, cost, retail, retail-cost profit, name

FROM books NATURAL JOIN publisher;

DML operations can only be performed on the columns belonging to the PUBLISHER table.

No DML operations are allowed.

DML operations are allowed on both the BOOKS and PUBLISHER tables.

DML operations can only be performed on the columns belonging to the BOOKS table.

95.

A(n) ____ table is the table that contains the primary key the view uses to uniquely identify each record being displayed by the view.

non key-preserved

key-preserved

primary

non primary-keyed

96.

A(n) ____ table is a table that does not contain the primary key that a view uses to uniquely identify each record being displayed by the view.

non key-preserved

key-preserved

primary

non primary-keyed

97.

Which of the following statements about performing DML operations on complex views is correct?

DML operations cannot be performed on a non primary key-locked table

DML operations cannot be performed on a non key-preserved table.

DML operations can be performed if a view contains a group function or a GROUP BY clause.

none apply

98.

Which statement about the view created from the following SQL statement is correct?

CREATE VIEW balancedue AS SELECT customer#, order#, SUM(quantity*retail) amtdue

FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#, order#;

No DML operations can be performed through the view.

DML operations can only be performed on the CUSTOMERS table since it is the primary key for the view.

Only rows can be added through the view — no other DML operations are allowed.

none apply

99.

Which type of view is created from the following SQL statement?

CREATE VIEW balancedue

AS SELECT customer#, order#, SUM(quantity*retail) amtdue

FROM customers NATURAL JOIN orders NATURAL JOIN

orderitems NATURAL JOIN books

GROUP BY customer#, order#;

simple

complex

inline

partial

100.

DML operations are not allowed on a view that is created with the ____ keyword.

JOIN

NATURAL JOIN

DISTINCT

REPLACE

101.

DML operations are not allowed on a view that includes the pseudo column ____.

ROWNUMBER

DISTINCTROW

ROWNUM

NUMROW

102.

Which command will delete a view?

ALTER TABLE...DROP VIEW

DROP VIEW

ALTER TABLE...DELETE VIEW

DELETE VIEW

103.

What is the definition of an inline view?

It is a permanent database object that can be referenced by subsequent queries.

It is a view that retrieves data from one or more tables, and can contain functions and grouped data.

It is a temporary data source that exists only while a command is being executed.

It is a temporary pseudo column.

104.

The type of view that actually replicates data is called a(n) ____ view.

simple

complex

inline

materialized

105.

The subquery used to create a(n) ____ view can contain an ORDER BY clause.

simple

complex

derived

inline

Reset

AIT 524 SQL Chapter 13

Total questions: 105

Worksheet time: 52mins

Similar Resources on Wayground