QUESTION 1 A table thats used to create a view is called a w

  

QUESTION 1

A table that’s used to create a view is called a what?

base

OFFSET

temporary

view

1 points   

QUESTION 2

A view

doesn’t store any data itself

consists only of the rows and columns specified in its CREATE VIEW statement

is like a virtual table

all of the above

1 points   

QUESTION 3

A view is a/an ________________ statement that’s stored as an object in the database.

SELECT

INSERT

DELETE

UPDATE

1 points   

QUESTION 4

By default,

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

calculated columns do not need to be named in the SELECT statement

columns from different tables with the same name do not have to be renamed

all of the above

1 points   

QUESTION 5

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

You can create custom views to accommodate different needs.

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

You can simplify data retrieval by hiding multiple join conditions.

You can provide secure access to data by creating views that provide access only to certain columns or rows.

1 points   

QUESTION 6

In the View Designer, you can

specify the selection criteria and sort order for a view

display the results of a view

all of the above

edit the design of an existing view

1 points   

QUESTION 7

The SELECT statement for a view

can include the INTO keyword

cannot use the ORDER BY clause with the OFFSET and FETCH clauses

can use the ORDER BY clause if it also uses the TOP clause

can’t use an ORDER BY clause

1 points   

QUESTION 8

To delete an existing view, you use which statement?

ALTER VIEW

CREATE VIEW

DELETE VIEW

DROP VIEW

1 points   

QUESTION 9

To modify an existing view, you use which statement?

ALTER VIEW

WITH SCHEMABINDING

CREATE VIEW

UPDATE

1 points   

QUESTION 10

The statement
CREATE VIEW Example3
AS
SELECT *
FROM Invoices;

will create an updatable view

will create a view through which you can delete rows, but not insert or update rows

will create a read-only view

d. will fail because the * operator isn’t allowed

1

A series of SQL statements that you can store in a file is called a

script

catalog view

subquery

view

1 points   

QUESTION 2

Code a statement that assigns the value “Test” to a scalar variable named @Name that’s declared with the varchar data type.

DECLARE ‘Test’ @varchar;

DECLARE varchar @Name = ‘Test’;

SET @Name = ‘Test’;

SET Test @Name;

1 points   

QUESTION 3

Code a statement that changes the database context to a database named TestDB.

USE TestDB;

none of the above

GO TestDB;

EXEC TestDB;

1 points   

QUESTION 4

Code a statement that creates a table variable named @TestTable.

CREATE @TestTable;

SET @TestTable = table;

DECLARE @table TestTable;

DECLARE @TestTable table;

1 points   

QUESTION 5

Code a statement that tests whether the database named TestDB exists.

IF DB_ID (\'TestDB\') EXISTS

IF TestDB IS NOT NULL

IF DB_ID (\'TestDB\') IS NOT NULL

IF TestDB EXISTS

1 points   

QUESTION 6

Code example 14-1
USE AP;
DECLARE @Date1 smalldatetime;
DECLARE @Date2 smalldatetime;
SELECT @Date1 = MIN(InvoiceDueDate), @Date2 = MAX(InvoiceDueDate)
    FROM Invoices
    WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;
IF @Date1 < GETDATE()
    IF @Date2 < GETDATE()
        BEGIN
            PRINT \'Earliest past due date: \' + CONVERT(varchar, @Date1, 1);
            PRINT \'Latest past due date: \' + CONVERT(varchar, @Date2, 1);
        END;
    ELSE
        PRINT \'Earliest past due date: \' + CONVERT(varchar, @Date1, 1);
ELSE
    PRINT \'No invoices past due\';

(Refer to code example 14-1.) If the current date is 04/04/16, the earliest invoice due date for invoices with unpaid balances is 02/09/16, and the latest invoice due date for invoices with unpaid balances is 03/20/16, what will be printed by this script?

Earliest past due date: 02/09/16
Latest past due date: 03/20/16

Earliest past due date: 02/09/16

Nothing

No invoices past due

1 points   

QUESTION 7

Given the following statements that declare a local variable and set its value, which of the following will cause an error?
DECLARE @Example1 varchar(128);
SET @Example1 = \'Invoices\';

SELECT *
FROM @Example1;

SELECT *
FROM sys.tables
WHERE name = @Example1;

PRINT \'Table name is: \' + @Example1;

IF @Example1 = \'Invoices\'
SELECT * FROM Invoices;

1 points   

QUESTION 8

The scope of a derived table is limited to what?

the batch in which it’s defined

the script in which it’s defined

the statement in which it’s defined

the database session in which it’s defined

1 points   

QUESTION 9

The scope of a local variable is limited to what?

the statement in which it’s defined

the batch in which it’s defined

the script in which it’s defined

the database session in which it’s defined

1 points   

QUESTION 10

The scope of a temporary table is limited to what?

the script in which it’s defined

the batch in which it’s defined

the statement in which it’s defined

the database session in which it’s defined

1 points   

QUESTION 11

What do you call a local variable that can store a single value?

scalar

single-local

temporary

global

1 points   

QUESTION 12

What statement can you use to divide a script into multiple batches?

EXEC

DECLARE

GO

SET

1 points   

QUESTION 13

What statement do you use to execute a dynamic SQL statement?

CONTINUE

EXEC

GO

SET

1 points   

QUESTION 14

Which of the following statements can be coded in a batch with other statements?

CREATE FUNCTION

CREATE TABLE

CREATE PROCEDURE

CREATE VIEW

1 points   

QUESTION 15

Which statement can you use to control the flow of execution based on a true/false condition?

IF...ELSE

TRY...CATCH

BEGIN...END

EXEC

d. will fail because the * operator isn’t allowed

	a.	base
	b.	OFFSET
	c.	temporary
	d.	view

Solution

1. A table that’s used to create a view is called a what?
   Base table. So, the answer is: a. base.
2. A view
   -> doesn’t store any data itself  
   -> consists only of the rows and columns specified in its CREATE VIEW statement
   -> is like a virtual table
   So, the answer is: d. all the above.
3. A view is a/an ________________ statement that’s stored as an object in the database.
   a. SELECT statement.  
4. By default,
   -> columns in a view are given the same names as the columns in the base tables
   -> calculated columns do not need to be named in the SELECT statement
   -> columns from different tables with the same name do not have to be renamed
   So, the answer is d. all the above.
5. Each of the following is a benefit provided by using views except for one. Which one?
   b. You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view.      
6. In the View Designer, you can
   -> specify the selection criteria and sort order for a view
   -> display the results of a view
   -> edit the design of an existing view
   So, the answer is, c. all the above.
7. The SELECT statement for a view
  
8. To delete an existing view, you use which statement?
   d. DROP VIEW is the statement which drops the view. Remember this is a DDL statement.
9. To modify an existing view, you use which statement?
   c. CREATE VIEW is the one which is used to modify the existing view. Infact, we should use it as CREATE OR REPLACE.