Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Interview Prep - SQL > Views > Flashcards

Views Flashcards

1
Q

How do you reference an existing view from another query?

A

Use the name of the view. View name is sales.USAcusts
~~~
SELECT custid, companyname FROM sales.USAcusts
~~~

SQL Server 70-461 04-02b to 04-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Is the result set from the view or the view definition stored in the database?

A

The definition of the view.

SQL Server 70-461 04-02b to 04-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is the general structure used to create a view?

A 
<statement to drop view if it exists>;
GO
CREATE VIEW view_name
AS
<select statement that defines the view>;
GO

Just making a sentence long enough so the code will left justify for this flash card.

SQL Server 70-461 04-02b to 04-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Example of a view statement for reference

A 
If OBJECT_ID('Sales.USACusts') IS NOT NULL
DROP VIEW Sales.USACusts;
GO

CREATE VIEW Sales.USACusts
AS
SELECT 
custid, 
companyname, 
contactname, 
contacttitle, 
address, 
city, 
region, 
postal code, 
country, 
phone, fax

FROM Sales.Customers
WHERE country=N'USA';
GO

SQL Server 70-461 04-02b to 04-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

General form of statement to create a view

A

USE TSQL2012
GO

CREATE VIEW Sales.OrderTotalsByYear
WITH SCHEMABINDING
AS

SELECT YEAR(O.orderdate) AS orderyear,
SUM(OD.qty) AS qty

FROM Sales.Orders AS O

JOIN Sales.OrderDetails AS OD
ON OD.orderid=O.orderid

GROUP BY YEAR(orderdate);
GO

SQL Server 70-461 09-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Can you select a view just like you select a table in a SELECT statement?

A

Yes

Example:
SELECT orderyear.qty
FROM Sales.OrderTotalsByYear

Just writing enough words to get code to left justify

SQL Server 70-461 09-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

How should a view be named with respect to a schema? What is best practice?

A

You should always include the schema name the view will belong to when naming it.

Example:
Sales.OrderTotalsByYear

Sales is the schema
OrderTotalsByYear is the view

SQL Server 70-461 09-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What does including the SCHEMABINDING statement when creating a view do?

A

SCHEMABINDING guarantees that the underlying table structures that the view includes cannot be altered without dropping the view

SQL Server 70-461 09-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

General form of create view statement that includes SCHEMABINDING

A

CREATE VIEW Sales.OrderTotalsByYear WITH SCHEMABINDING

SQL Server 70-461 09-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Since the body of a view is just a SELECT statement, what rules for SELECT statements does it follow?

A

All of the usual rules when putting together a SELECT statement

SQL Server 70-461 09-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Where must the CREATE VIEW statement be in a batch?

A
  • It must be the first statement
  • It cannot be inside an IF statement

SQL Server 70-461 09-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Are there any considrations that must be made when naming a view? With respect to other objects in the database.

A

Yes

Because it is an object in the database it must have a name that is unique from all other objects

SQL Server 70-461 09-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

You can name the columns right after the new view name or you can name them in the SELECT statement by aliasing the column. Which is better?

A
  1. CREATE VIEW Sales.OrderTotalsByYear(orderyear, qty)
  2. SELECT YEAR(O.orderdate) AS orderyear, SUM(OD.qty) AS qty

As you can see, it would be better to put them in the SELECT statement so they are right next to the column they are naming instead of having to look back and forth between CREATE statement and SELECT statement

SQL Server 70-461 09-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are the three view options and which of them can be used together?

A

WITH ENCRYPTION
WITH SCHEMABINDING
WITH VIEW_METADATA

You can use any combination of the three. Meaning you could also use just one or two.

SQL Server 70-461 09-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What does WITH ENCRYPTION do?

A

It makes it difficult for users to discover the SELECT text of the view. Note it is not strong encryption.

SQL Server 70-461 09-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

How does WITH VIEW_METADATA work?

A

It returns the metadata of the view instead of the base table.

SQL Server 70-461 09-01

17
Q

How many SELECT statements can you use when creating a view?

A
  • Only one. Because a requirement of a view is that only one result set is returned.
  • However, you can use UNION ALL or UNION to combine result sets in one view thereby having more than one SELECT statement

SQL Server 70-461 09-01

18
Q

How does the WITH CHECK OPTION work on a view?

A

When modifying data through a view, it is possible to modify a row such that it would no longer be included in the view due to the WHERE clause filter on the view. WITH CHECK OPTION restricts changes to the data if it would no longer be included because the new data isn’t included in the WHERE clause.

SQL Server 70-461 09-01

19
Q

What are the 4 restricitons of views?

A
  1. They cannot have an ORDER BY clause. They must return a relational result.
  2. You cannot pass parameters to a view
  3. you cannot create a table using a view. For example, using SELECT/INTO syntax.
  4. A view can only reference permanent tables, not temporary ones.

SQL Server 70-461 09-01

20
Q

How can you order a view if they can’t include an ORDER BY clause?

A

SELECT the already created view and use an ORDER BY clause.

SQL Server 70-461 09-01

21
Q

What is the one scenario in which an ORDER BY clause is included in a CREATE VIEW statement and does it order the data?

A

When creating a view, you can use ORDER BY in conjuction with TOP or OFFSET-FETCH. However, this will not order the final result set that is returned by the view so it is still considered relational.

SQL Server 70-461 09-01

22
Q

Does a view store data or just a definition by a SELECT statement of how the results should be built?

A

Just the definition

SQL Server 70-461 09-01

23
Q

In what situation would data in a view actually be stored to disk?

A
  • If you created a unique clustered index on the views
  • There are a number of restrictions that must be satisfied to do this and they are discussed later

SQL Server 70-461 09-01

24
Q

What will a query plan show if you select a view?

A

The underlying tables of the view, the view itself will not be an object in the plan.

SQL Server 70-461 09-01

25
Q

WHAT does ALTER VIEW do?

A
  • Allows you to change the views structure and add or remove columns.
  • ALTER VIEW simply redefines how the view works by re-issuing the entire view definition

SQL Server 70-461 09-01

26
Q

Sample ALTER VIEW statement

A

ALTER VIEW view_name
WITH SCHEMABINDING
AS
SELECT statement

WITH SCHEMABINDING is optional. Include so the underlying tables of the view cannot have their definitions changed unless the view is dropped.

SQL Server 70-461 09-01

27
Q

How do you drop a view?

A

DROP VIEW view_name

SQL Server 70-461 09-01

28
Q

How do you check to see if a view exists, drop it if it does and replace it with the new view definition you want?

A 
IF OBJECT_ID(N'Sales.OrderTotalsByYear',N'V') IS NOT NULL
DROP VIEW Sales.OrderTotalsByYear;
GO
CREATE VIEW Sales.OrderTotalsByYear
...

Note: The V stands for view objects

SQL Server 70-461 09-01

29
Q

What 5 restrictions are there to updating data through a view?

A
  1. Insert, update and delete statements must reference only on underlying table of the view at a time no matter how many tables the view references
  2. You cannot change data in column of the view that were created using expressions, only columns off the underlying tables
  3. You cannot modify data in a view column that is computed from a UNION, UNION ALL, CROSS JOIN, EXCEPT or INTERSECT
  4. You cannot modify data in a view where values result from grouping such as when using DISTINCT, GROUP BY, HAVING.
  5. You cannot modify data in a view that has TOP or OFFSET-FETCH accompanied by the WITH CHECK OPTION clause.

SQL Server 70-461 09-01

30
Q

What permission does a user need to view metadata?

A

VIEW DEFINITION

SQL Server 70-461 09-01

31
Q

How can you explore view metadata?

A

Query the sys.views catalog view
~~~
USE TSQL2012;
GO
SELECT name, object_id, principle_id, schema_id, type
FROM sys.views
~~~

SQL Server 70-461 09-01

32
Q

Since you can’t pass parameters to a view, what can you use that simulates passing a parameter to a view?

A

An Inline Table-Valued Function

SQL Server 70-461 09-01

33
Q

What two options can you apply to Inline Table-Valued functions that you can also apply to views?

A
  • WITH ENCRYPTION
  • WITH SCHEMABINDING

SQL Server 70-461 09-01

34
Q

What is partitioned view or local partitioned view?

A
  • A view that uses union statements to combine data from partitioned tables.
  • The tables are in one database or at least one instance of SQL Server

SQL Server 70-461 09-01

35
Q

What is distributed partition view?

A
  • A view that uses union statements to combine data from partitioned tables
  • The tables are spread across multiple SQL Server Instances

SQL Server 70-461 09-01

Interview Prep - SQL (26 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions