Week 7

Question 1

What is a view?

Answer 1

a view is a virtual table!!! basically making it virtually

CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition

you can do all the things!!! inner joins and all the others week 5 studd

Question 2

What does isnull(__,0) function do?

Answer 2

replaces all null values with 0

Question 3

wHAT IS a dynamic view

Answer 3

virtual table created dynamically base don user request

no data actuall stroed

instead data from base table is aavialbel for user

based on sql select statmeemtns on basee tabls or other views

Question 4

Materialized view

Answer 4

Copy or replication of data

DATA IS ACTUALLY STORED

Must be regreshed periodically to match correpsonding base table

Question 5

pros of dynamic views

Answer 5

simplify query commands

assist with data security

enhance programming productivity

contain most current base table data

use little stroage space

provide custom view for user

establsih physical data independance

Question 6

cons of dynamic views

Answer 6

use processing time each time view is references

may/may not be directly updatebale

as with al sql constructs, you should use views with discretion

Question 7

what is a routine

Answer 7

program modules that execute on demand

Question 8

3 compeonents of a tourine

Answer 8

function: routines that return values and take input paramaeters

stored procedure: routines that do not return values and can take input or output parameters

triggers: routines that executive in response to a db event (insert, update, deletE)

Question 9

what is a stored procedure

Answer 9

if you are repeating an excel query constantlty, you can save it as a stored proceudre and then call it or execute it

Question 10

example of stored proceudre

Answer 10

CREATE PROCEDURE (name)
@parameter1 datatyp
@parameter2 datatype
AS —-
BEGIN
SQL statmeents
END

LIKE,,,,,,,,,,,,,,,,,,,,,,,

CREATE PROCEDURE SelectProduct AS
begin
SELECT * FROM Product_T;
end

Question 11

how to execute a stroed proceduree

Answer 11

EXEC procedure naem

Question 12

What is an index?

Question 13

what is a clustered index

Answer 13

sort and store the data riws in the table or view based in their key values

CREATE INDEX index1 ON table1 (column1)

Question 14

Nonclustered index

Answer 14

having a structure separate from the data rows

containing key values and each key value entry has a pointer to the data row that contains the key value

CREATE CLUSTERED INDEX index1 ON table1 (column1)

Question 15

How do you access data dictionary in sql server

Answer 15

metadata in sql server cannot be queried directly, it is exposed in

CATALOG VIEWS
INFO SCHEMA VIEWS

Question 16

where are the catalaog views?

Answer 16

in the sys schema

SELECT * FROM sys.objects
WHERE type_desc=’USER_TABLE

Question 17

Info schema view

Answer 17

• very similar to catalog views
• Provide a standard method for accessing metadata
  across different relational databases

-Provided more limited information than Catalog views

Question 18

example for info schema views

Answer 18

SELECT * FROM
INFORMATION_SCHEMA.TABLES

Question 19

tips for dev queries

Answer 19

Be familiar with the data model (entities and relationships)

Understand the desired results

Know the attributes desired in results

Identify the entities that contain desired attributes

Review ERD

Construct a WHERE equality for each link

Fine tune with GROUP BY and HAVING clauses if needed

Consider the effect on unusual data

Question 20

the less subqueries the better

Answer 20

!!

Question 21

If data is to be used many times, make a separate query and
store it as a view