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