CIS407 Exam 2

Which statement do you use to delete an existing view?

DROP VIEW

A table that's used to create a view is called

a base table

The WITH ENCRYPTION clause of the CREATE VIEW statement

prevents users from seeing the code that defines the view

Each of the following is a benefit provided by using views except for one. Which one is it?

You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view.

You can code views that

join tables, summarize data, and use subqueries and functions

Which of the following should you use to view the code that's generated for a view in the View Designer?

SQL Pane

Which statement do you use to modify an existing view?

ALTER VIEW

By default,

columns in a view are given the same names as the columns in the base tables

A view

is like a virtual table, consists only of the rows and columns specified in its CREATE VIEW statement, doesn't store any data itself

One way to examine the system objects that define a database is to use

catalog views

You use data definition language (DDL) to create, modify, and delete the \________________ of a database.

objects, sequences, table

Which of the following statements about the SPARSE attribute is true?

It optimizes the storage of null values for a column, It requires more overhead to retrieve non-null values, You should only use it when a column contains a high percentage of null values

What attribute can you use to optimize storage when coding the definition for a column that will contain a high percentage of null values?

SPARSE

A regular identifier

can contain a number sign (\#)

When you define a foreign key constraint, you can specify all but one of the following. Which one is it?

that the insertion of a row in a foreign key table that has a foreign key that isn't matched in the primary key table should be cascaded up to the primary key table

Each of the following column attributes is a column constraint, except

DEFAULT

When you use the CREATE TABLE statement to create a table, you can also define the attributes and \______________ for the columns.

constraints

What kind of constraint limits the values that can be stored in a column?

check constraint

When you code a table-level check constraint, the constraint can refer to data in more than one

column

Which statement is used to modify the structure of an existing table?

ALTER TABLE

If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery)

a single value

If introduced as follows, the subquery can return which of the values listed below?WHERE VendorID NOT IN (subquery)

a column of one or more rows

A subquery is a/an \______________ statement that's coded within another SQL statement.

SELECT

You can't update

an identity column

If you code a column list in an INSERT statement that includes a column that has a default value, which keyword can you code in the VALUES clause to use the default value?

DEFAULT

To insert several rows selected from another table into a table, you can code an INSERT statement with a/an \______________ in place of the VALUES clause.

subquery

You use the UPDATE statement to modify one or more rows in the table named in the \________________ clause.

UPDATE

When you code a column list in an INSERT statement, you can omit identity columns, columns that have default values, and columns that allow \__________ values.

null

When you use the SELECT INTO statement, the result set that's defined by the SELECT statement is \________________ a new table.

copied into

The integer and decimal data types are considered \________________ because their precision is exact.

exact numeric data types

Write the code for an ORDER BY clause that sorts a table into numeric sequence by the data in the PartCode column if that column contains whole numbers that are stored with the varchar data type.

ORDER BY CAST (PartCode AS int)

Which data types are used to store whole numbers?

integer

The difference between the CAST function and the CONVERT function is that the \________________ function accepts an optional style argument that lets you specify a format for the result

CONVERT

Which function is typically used to insert control characters into a character string?

CHAR

If you omit both NULL and NOT NULL from the list of column attributes in a CREATE TABLE statement, which is the default setting?

NULL

A collation can affect all of the following except one. Which one?

how character data is concatenated

When you code a script for creating a database,

you need to create the referred to tables before you create the tables that refer to them

The CREATE TABLE statement

creates a new table in the current database

If introduced as follows, the subquery can return which of the values listed below?WHERE InvoiceTotal \> ALL (subquery)

a column of one or more rows

A subquery can be coded in a WHERE, FROM, SELECT, or \______________ clause.

HAVING

A common table expression (CTE) creates a temporary \_____________ that can be used by a query that follows.

table

If introduced as follows, the subquery can return which of the values listed below?SELECT (subquery)

a single value

Code example 6-1SELECT VendorName, COUNT(*) AS NumberOfInvoices,MAX(InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDueFROM Vendors JOIN Invoices ON Vendors.VendorID \= Invoices.VendorIDWHERE InvoiceTotal - PaymentTotal - CreditTotal \>(SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal)FROM Invoices)GROUP BY VendorNameORDER BY BalanceDue DESC;
(Please refer to code example 6-1.) When this query is executed, the NumberOfInvoices column for each row will show the number

of invoices for each vendor that have a larger balance due than the average balance due for all invoices

Write the code for a DELETE statement that deletes every row in the Vendors table

DELETE Vendors;

When you code an UPDATE statement for one or more rows, the SET clause specifies the new data for the specified columns and the \_________________ clause specifies which row or rows are to be updated.

WHERE

In an UPDATE statement, the WHERE clause will

specifiy the condition a row must meet to be updated

You specify the conditions that must be met for a row to be deleted in the which clause?

WHERE

In the INSERT statement that follows, assume that all of the table and column names are spelled correctly, that none of the columns are identity columns, and that none of them have default values or accept null values. What's wrong with the statement?INSERT INTO InvoiceCopy(VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, CreditTotal,TermsID, InvoiceDate, InvoiceDueDate)VALUES(97, '456789', 8344.50, 0, 0, 1, '2016-08-01');

The number of items in the column list doesn't match the number in the VALUES list.