Quiz 2 (week 2 cursors, week 3, week 4)

Question 1

A cursor is a _____ to this context area

A cursor can hold ___ __ ___rows of data

The set of rows the cursor holds is referred to as the _____ ____

Answer 1

pointer
zero or more
active set

Question 2

____ ___ Are automatically created by Oracle whenever a ___ statement is executed

Answer 2

DML (INSERT, UPDATE,
DELETE)

Question 3

%_____ - true if 1 or more rows are affected

%____ - the opposite of %____

%_____ - the number of rows processed

Answer 3

SQL%FOUND (true if 1 or more rows are affected)
SQL%NOTFOUND (the opposite of SQL%FOUND)
SQL%ROWCOUNT (the number of rows processed)

Question 4

Explicit cursors are programmer defined cursors for gaining more control over the ___ ___

Once opened, the data must be moved from the ___ ____ to a ___ ___ to be used by the program.

Variables must be populated ___ __ __ __ of the data in the cursor.

Answer 4

context area
temp area
local variable
in the same order

Question 5

Declare cursor syntax

Answer 5

DECLARE
l_id sh.Customers.Cust_ID%TYPE
l_fname sh.Customers.Cust_First_Name%TYPE
l_lname sh.Customers.Cust_Last_Name%TYPE

CURSOR cr IS SELECT Cust_ID, Cust_First_Name, Cust_Last_Name
FROM sh.Customers
WHERE Cust_ID < 100;

CURSOR name IS [select statement]

Question 6

fetch from cursor into variables without bulk collect syntax

Answer 6

BEGIN
OPEN cr;
LOOP
FETCH cr INTO l_id, l_fname, l_lname;
DBMS_OUTPUT>PUT_LINE(rowNames);
EXIT WHEN cr%NOTFOUND;
END LOOP;
CLOSE cr;
END;

Question 7

cursor attributes

Answer 7

cr%ISOPEN
cr%NOTFOUND
cr%FOUND
cr%ROWCOUNT

Question 7

bulk collect rows syntax

Answer 7

DECLARE
TYPE Rec_Cust IS RECORD
(l_id NUMBER,
name VARCHAR2(20));

TYPE Arr_Cust IS TABLE OF Rec_Cust;
l_Cust Arr_Cust

define cursor

BEGIN
OPEN cr;
FECTH cr BULK COLLECT INTO l_cust;
CLOSE cr;

FOR i IN 1 .. l_Cust.LAST LOOP
    DBMS_OUTPUT>PUT_LINE(rows);
END LOOP; END;

Make a record that has the data types of the table

make a table of the record

define cursor

open cr, bulk collect, close cr

for loop

Question 8

how to access rows of a table with an index

Answer 8

tableName(1)

Question 9

forall loop
when to use
syntax

Answer 9

can only include one DML

FORALL i IN 1..l_Cust.LAST
INSERT INTO table Values l_cust(i)

Question 10

___ ___ - Is a named block of PL\SQL
code that is saved in in the
DBMS

Procedures can be executed
using ____

Stored Procedures can
___ ____
perform ___ or ___

Answer 10

Stored procedures

EXECUTE

return values
perform DDL or DMLs

Question 11

Procedure syntax

Answer 11

CREATE OR REPLACE PROCEDURE hello (parameterName IN VARCHAR2)
IS
returnedVar VARCHAR2(24);

BEGIN
END;

Question 12

ways to call a procedure

Answer 12

procedureName(val1, val2)

CALL procedureName(val1, val2)

procedure_name(param2 => val2, param1 => val1)

Question 13

assignment operator

Answer 13

:=

Question 14

what does IN mean in procedures

where to use

Answer 14

read only, you can use the data, but you cannot change it

use in the parameter section

Question 15

what does OUT mean in procedures

where to use

Answer 15

writable variable

use in the parameter section

Question 16

what does INOUT mean in procedures

Answer 16

readable and writable

Question 17

how to get an empty table with the table structure of another table

Answer 17

CREATE TABLE tableName AS
SELECT * FROM otherTable
WHERE 1=2;

1 is never equal to 2, so no rows will be selected

Question 18

a __ always returns a value

a ___ cant make charges to the database, like DML statements

Answer 18

function
function

Question 19

the return keyword is optional for functions t/f

Answer 19

f

Question 20

function syntax

Answer 20

CREATE OR REPLACE FUNCTION functionName (param1 dataType)

RETURN dataType
IS
PL/SQL block

Question 21

functions can return multiple values t/f

Answer 21

f
only one

Question 22

you can use functions in select statements t/f

Answer 22

t

Question 23

functions, procedures and variables can be put together in a package

syntax

Answer 23

CREATE OR REPLACE PACKAGE name IS
procedure/function block;
procedure/function block;
END name;

Question 24

EXCEPTION
syntax
where to use

Answer 24

EXCEPTION
WHEN ZERO_DIVIDE THE
statements;
after BEGIN, before END

Question 25

exceptions

Answer 25

zero_divide
case_not_found
no_data_found - for select into or cursor fetch

rowtype_mismatch
too_many_rows
value_error

Question 26

how to define an exception
syntax

Answer 26

DECLARE
exceptionName EXCEPTION;
price := 10;
BEGIN
IF price < 0 THEN
RAISE exceptionName;
EXCEPTION
WHEN exceptionName THEN
statements;
END;

Question 27

A ____ is a named block of PL/SQL code that is executed or fired automatically when a particular type of SQL is executed.

used with ___ and ___ statements

Answer 27

trigger
DML and DDL

Question 28

Trigger syntax

Answer 28

CREATE OR REPLACE TRIGGER triggerName
BEFORE INSERT ON tableName
FOR EACH ROW
BEGIN
IF :NEW.rcolName != UPPER(:NEW.colName) THEN
:NEW.colName := UPPER(:NEW.colName);
END IF;
END;

Question 29

Dynamic SQL composes a DML or DDL statement at __ __

it is useful when
____
____

Answer 29

run time

need to run a DDL
SQL can only be formed at run time

Question 30

Dynamic SQL syntax

Answer 30

CREATE OR REPLACE PROCEDURE name (varName IN VARCHAR2,
varName2 IN VARCHAR2)
IS
l_string VARCHAR(240);
BEGIN
l_string := ‘ALTER TABLE ‘ || varName|| ‘ ADD CONSTRAINT PK_’|| varName1||’ Primary key (‘||varName2||’)’;

EXECUTE IMMEDIATE l_string;
END;
exec name(val1, val2);

Question 31

DML with Dynamic SQL
syntax

Answer 31

CREATE OR REPLACE PROCEDURE name (varName IN VARCHAR2,
varName2 IN VARCHAR2,
l_oldVal IN VARCHAR2,
l_newVal IN VARCHAR2)
IS
l_string VARCHAR(240);
BEGIN
l_string := ‘UPDATE ‘||varName1||’ SET ‘|| varName2||’ = :a WHERE ‘ || varName2 || ‘ = :b’;

EXECUTE IMMEDIATE l_string USNG l_newVal, l_oldVal;

END;
exec name(val1, val2, val3, val4);

Question 32

_____ is the process of consolidating data from different data sources to support business needs

Answer 32

integration

Question 33

Main data resources
___
___
___
___

Answer 33

OLTP
application specific databases
event logs
social media

Question 34

data quality

Answer 34

redundancy
accuracy
consistency
currency
timeliness
conformance
referential integrity

Question 35

There are three techniques that form the building blocks of
any integration approach:
* Data ___
* Data ____
* Data ____

Answer 35

Consolidation
Federation
Propagation

Question 36

data ____
Usually done for Data Warehousing using Extract Transform Load (ETL) techniques

___ only databases

has a ___ schema

Answer 36

consolidation

read only

fixed schema

Question 37

data ___
is the creation of a virtual database that aggregates data from distributed sources giving them a common data model. It is an approach to data integration that provides a single source of data for front end applications

___ on demand
provides ___ ___ to consolidated data

____ (no ___ defined in advanced)

Answer 37

federation

consolidation

virtual view

flexible, no schema

Question 38

Data _____
is the mechanism of copying data within the case hierarchy. By sharing
data among cases, you save time and provide relevant information to caseworkers. Data propagation is not limited to subcases. Data can also be propagated when creating spin- off cases. Data propagation happens on case creation

Data is ___ across the databases

can be ___ (data is consistent) or __

Answer 38

propagation

replicated

synchronous or asynchronous

Question 39

___
meaning
is a process that is used to integrate data or move data from
one source to another

Answer 39

ETL
extract transform load

Question 40

ETl - Extract

It is a common practice to first load the extracted data into an ____ storage area. this is often referred to as a “staging database” or “temporary repository

___ extract is when the whole data is copied

___ extract is when the deltas(mean new or modified records)- the changes are copied

Answer 40

intermediate

Static
Incremental

Question 41

ETL - cleanse extracted data

Integrating different sources of data exposes ___ issues in the original data sets

Extract process should have processes that expose and reject
____ data

Extract process must __ __ __ __. Instead it will send an error report to the owners of the data source to fix the data at the source

Answer 41

quality

erroneous

not fix the errors

Question 42

ETL - transform - record level transformation

Is a process of changing the ___ of the source tables through

• __: Restricting rows using the WHERE clause in the select statement)
• ___: Projecting columns from more than one table)
• ___: think of it as the opposite of joining. Convert tables that are not in the third normal form to smaller tables that do not have redundancies
• ____: converting data from more detailed to summary statistics on the
  data

Answer 42

structure

Selection

Joining

Normalization

Aggregation

Question 43

ETL - TRANSFORM – FIELD LEVEL TRANSFORMATION

Is concerned with changing the data in the fields (columns) that include
* ___ __change
* Use functions and algorithms to change data from source to
destination (for example change US dollars to Canadian
dollars)

Answer 43

Data type

Question 44

ETL - load
insert the ___ ___ to tis final destination (usually data warehouse or data mart)

Answer 44

transformed data

Question 45

____ ___ - Databases that are used to analyze and summarize data, and report on the business entities

Answer 45

data warehouse

Question 46

data ___ is a subset of a data ___

Answer 46

mart, warehouse

Question 47

data marts uses ___ models

centered around __ __

Answer 47

dimensional, not the same as relational

one subject

Question 48

data warehouses
____ change over time
___ only
___ ___: focused on business entities
___: Comes from multiple systems and need to fit one structure

Answer 48

slow
read
Subject Oriented
Integrated

Question 49

data marts have less data sources t/f

Answer 49

t

Question 50

____ ___ - is a model that organizes
data into two main types of tables: fact tables and dimension tables.

the fact table is located at the center, surrounded by dimension tables

___ Table: has quantitative values

___ tables: has categories and their descriptions

All the primary keys of the dimension tables should be ___ keys (system generated keys).Most of the time it is a sequential integer

Answer 50

star schema

Facts
Dimensions

surrogate

Question 51

GRAIN OF THE FACT TABLE

The minimum unit of reporting should be an ___ of all the dimensions

The grain is a crucial concept in data warehousing, influencing how data is aggregated and analyzed in conjunction with dimension
attributes

Think of the fact table as an associative entity. This means that its primary key is a composite key that contains all the foreign keys of the entities that associates it which is the dimension tables.

Answer 51

intersection

Question 52

data mart
Longer duration means that the
fact table becomes much bigger
t/f

Answer 52

t

Question 53

create star schema

Answer 53

create tables

CREATE SEQUENCE Dim_ID;

for each dimension table
INSERT INTO DIM_name
SELECT Dim_ID.NEXTVAL,col1, col2
FROM table

create table with %rowtype

create cursor with select statement that joins the dimension tables
group by dimension table ids

bulk collect into created table

—-cleanse
for i IN 1..table.last loop
if table(i).col is null then
table(i).col := 0;
end if;
end loop;

use forall to insert from table to fact table