midterm

Question 1

math operators

Answer 1

*
/
**

Question 2

logical operators

Answer 2

AND
OR
NOT

Question 3

between

Answer 3

min and max value

WHERE colName BETWEEN val1 AND val2

Question 4

in clause

Answer 4

compare a value to a list of literal values that have been specified

WHERE colName IN (val1, val2, val3, val4)

Question 5

LIKE clause

wildcards

Answer 5

% represents zero or more characters

_ represents a single character

WHERE FIrst_Name LIKE ‘Herma%’

Question 6

DISTINCT clause
eliminates _____ records, fetching only ___ unique records when using a select statement

Answer 6

duplicate, unique

Question 7

subquery syntax

Answer 7

SELECT emp_ID, name
FROM employees
WHERE salary =
(SELECT MAX(salary) FROM employees);

let you use an aggregate function in the where clause

Question 8

types of joins (6)

Answer 8

inner join
left join
right join
full join
cross join
self join

Question 9

input

Answer 9

V_name := ‘&name’;

Question 10

NCHAR vs VARCHAR

Answer 10

when you insert a string shorter than the defined length into an NCHAR column, the database pads the string with spaces to math the defined length

varchar does not do this

Question 11

NUMBER vs pls_int

Answer 11

number stores more than int

Question 12

if statements syntax

Answer 12

IF condition THEN
statements;

ELSIF condition THEN
statements;

ELSE
statements;
END IF;

Question 13

case statement syntax

Answer 13

CASE l_num

WHEN 11 THEN
statements;

WHEN 10 THEN
statements;

ELSE
statements

END CASE;
_________________________-
CASE
WHEN l_num > 11 THEN
statements;

WHEN l_num =10 THEN
statements;

ELSE
statements;

END CASE

Question 14

pls_integer vs simple_integer

Answer 14

pls_intger can store null values, simple_integer cannot store null values, it does not raise an overflow exception and it wraps around to zero

Question 15

while loop syntax

Answer 15

WHILE condition LOOP
statements
END LOOP;

Question 16

IN vs OUT parameters

Answer 16

IN - read only
OUT - can change the value (write)

Question 17

EXCEPTIONS

syntax

Answer 17

zero_divide
case_not_found
no_data_found
rowtype_mismatch
too_many_rows
value_error

EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT>PUT(‘error message’);
END:

Question 18

how to define an exception

Answer 18

DECLARE
varName EXCEPTION;

EXCEPTION
WHEN varName THEN
print statement
END;

Question 19

for loop syntax

Answer 19

FOR i IN 1 .. 20 LOOP
statments;
END LOOP;

Question 20

cursor declare syntax

Answer 20

declare
CURSOR cr IS SELECT cust_ID, cust_first, cust_last FROM SH.customers

Question 21

cursor attributes

Answer 21

%ISOPEN
%NOTFOUND
%FOUND
%ROWCOUNT

Question 22

4 steps in using an explicit cursor

Answer 22

declare
open
fetch
close

Question 23

when fetching to a record or list of variables, the variables should be ___________

Answer 23

in the same order as the variables in the cursor

Question 24

declare record

Answer 24

TYPE rec_cust IS RECORD(l_id NUMBER, l_first VARCHAR(20));

– instance of record
Cust rec_cust

Question 25

create a table of records

create table using existing table structure

Answer 25

TYPE arr_cust IS TABLE OF rec_cust;

–instance of arr_cust
l_cust arr_cust

TYPE arr_cust IS TABLE OF sh.customers%ROWTYPE;

l_cust arr_cust;

Question 26

bulk update syntax

Answer 26

OPEN cr;
FETCH cr BULK COLLECT INTO l_cust;
CLOSE cr;

FORALL i IN 1 .. l_cust.LAST
UPDATE MyCustomer
SET Cust_city = ‘Toronto’
WHERE cust_ID = l_cust(i).cust_id;

Question 27

stored procedures, functions and triggers ___ ___ more efficiently and effectively than SQL

Answer 27

manipulate data more efficiently and effectively than SQL

Question 28

functions and procedures do not remain in the database after it is created (t/f)

Answer 28

f, remains in the database until it is dropped

Question 29

stored procedures syntax

Answer 29

CREATE OR REPLACE PROCEDURE hello(l_name IN VARCHAR2)
IS
l_greeting VARCHAR2(24);
BEGIN
statement
END;

Question 30

how to execute a procedure

Answer 30

EXECUTE hello(‘myName)

Question 31

how to call a procedure (3)

Answer 31

CALL procedureName(params);

call from another script
BEGIN
procedureName(params);
END;

association operator
BEGIn
procedureName(param2 => val2, param1 => val1);

Question 32

procedure notations

Answer 32

positional notation(the same order)

named notation
greeting(name => ‘John’, text => ‘dear’)

mixed notation
positional first, named second

Question 33

INOUT meaning

Answer 33

both readable and writable

Question 34

select and bulk collect syntax

Answer 34

SELECT * BULK COLLECT INTO l_tbl
FROM sh.customers;

Question 35

why do we use cursors

steps

Answer 35

lets you manipulate the data before inserting into a table

use cursor to collect data
put data into created table
put data into database table from created table

Question 36

function vs procedure

Answer 36

A function ALWAYS returns 1 value
A function cannot make changes to the database (INSERT, UPDATE, DELETE)

a procedure does not have to return a value

Question 37

It rarely makes sense to use ____ ____ for a function

why

Answer 37

output parameters

cannot edit the data

Question 38

function syntax

Answer 38

CREATE OR REPLACE FUNCTION functionName(varName IN NUMBER)
RETURN NUMBER
IS
varName2 NUMBER(12,2)
BEGIN
statements
END;

Question 39

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

mostly used with ____, ___, ___

can be used with DDL statements such as ____, ___, ___

Answer 39

trigger

insert, update, delete

cerate, alter, drop

Question 40

trigger syntax

Answer 40

CREATE OR REPLACE TRIGGER triggerName
BEFORE/AFTER/INSTEAD OF
INSERT/UPDATE/DELETE
OF colName ON tableName
FOR EACH ROW
BEGIN
statements
END;
:NEW.colName to reference new value

Question 41

Dynamic SQL composes a DML or DDL statement at ___ ___

useful when:
you need to run a ___
your sql can only be formed at __ __

Answer 41

run time

ddl
run time

Question 42

dynamic sql

Answer 42

CREATE OR REPLACE PROCEDURE example(employee_id NUMBER) IS
sqlStatement VARCHAR2(100);
empName VARCHAR(20);

BEGIN
sqlStatement := ‘SELECT first_name FROM EMPLOYEES WHERE emp_ID = :id’;

EXECUTE IMEEDIATE sqlStatement INTO empName USING employee_id

END;

Question 43

dynamic sql
alter table, add constraint syntax

Answer 43

sqlStatement := ‘ALTER TABLE ‘ || tableName || ‘ ADD CONSTRAINT PK_’ || tableName ||
‘ PRIMARY KEY (‘ || colName || ‘)’;

EXECUTE IMEEDIATE sqlStatement;

make sure to add appropriate spaces to the string

Question 44

package syntax

Answer 44

CREATE OR REPLACE PACKAGE pkg AS
PROCEDURE procedureName1(params);

PROCEDURE procedureName2(params);

END pkg;

CREATE OR REPLACE PACKAGE BODY pkg AS
PROCEDURE procedureName1(params) IS
BEGIN
statements
END procedureName1);

PROCEDURE procedureName2(params) IS
BEGIN
statements
END procedureName2);

END pkg;

Question 45

how to use a procedure in a package

Answer 45

pkgName.procedureName(params)

Question 46

Integration is the process of ____ data from different __ __ to support business needs

Answer 46

consolidating
data sources

Question 47

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

Answer 47

Consolidation
Federation
Propagation

Question 48

data consolidation
Usually done for _____ ___ using Extract Transform Load (ETL) techniques

___-___ databases

flexibility: ____

requires constant __ ___ to refresh the warehouse

Answer 48

Data Warehousing

read only databases

not flexible (has a fixed schema)

batch processes

Question 49

Data warehousing is the process of constructing and using a data warehouse.

A data warehouse is constructed by ___ data from multiple ___ __that support analytical reporting, structured and/or ad hoc queries, and decision making

Warehousing is to ___ data in one database that is optimized
for __

Answer 49

integrating

heterogeneous sources

consolidate, reads

Question 50

Data federation is the creation of a ___ database that ___ data from distributed sources giving them a ___ ___ ___.

It is an approach to data integration that provides a single source of data for front end applications

flexibility: __

___ on demand

provides ___ views to consolidated data

only copies or transforms data when need

can affect the performance of the transactional ___ databases

Answer 50

virtual, aggregates, common data model

flexible

consolidation

virtual

OLTP

Question 51

ETL
what is it

Answer 51

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

Question 51

Data propagation

Is the mechanism of copying data within the ___ __. 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 __ __

data is ___ across the database

can be ___ (data is consistent) or ___

too many ___

Answer 51

case hierarchy

case creation

replicated

synchronous or asynchronous

duplicates

Question 52

ETL extract

Process of getting data from the data sources

Usually the data will be extracted to a _____ repository (staging
database). It is a common practice to first load the extracted data into an intermediate storage area. This intermediate storage is often referred to as a “staging database” or “temporary repository

___ extract is when the whole data is copied

Answer 52

temporary

Static

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

Question 53

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
___ ___

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

Answer 53

quality

erroneous data

fix

Question 54

ETL - Transform - record level

• ____: 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 54

Selection

Joining

Normalization

Aggregation

Question 55

ETL - TRANSFORM – FIELD LEVEL

___ ___ change

Use functions and algorithms to ___ data from source to
destination

Answer 55

data type change

change

(for example change US dollars to Canadian dollars)

Question 56

ETL - load
___ the transformed data to its final destination (usually data warehouse or a data mart)

build ___ if required

Answer 56

Insert

Question 57

Data mart is a subset of __ __

uses ___ ___

centered around ___ ___

Answer 57

data warehouse

use dimensional models

one subject (sale, inventory)

Question 58

data warehouse

___ Oriented: is focused on business entities

___: Comes from multiple systems and need to fit one structure

____-___: show change over time

____ Only: can not be updated. Has to be reloaded or refreshed

Answer 58

Subject

Integrated

Time-Variant

Read

Question 59

need for data warehouse

Answer 59

when we need to integrate data from multiple systems

Separating reporting databases from operational databases
(OLTP) enhances the performance
NEED FOR DATA WAREHOUSE

Question 60

star schema

facts table: has ___ values, contains ___ ___ of dimension tables

dimension tables: has ___ and their descriptions

all primary keys of the dimension tables should be ___ keys (system generated
keys)

Answer 60

quantitative, foreign keys
categories

surrogate

Question 61

star schema
grain of the fact table

The minimum unit of reporting should be an ________________

The grain is a crucial concept in data warehousing, influencing how data is ___ and ____ 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 61

intersection of all the dimensions

aggregated and analyzed

Question 62

how to create and use a star schema syntax

Answer 62

–create tables
CREATE TABLE Fact_Emp(
J_ID NUMBER,
P_ID NUMBER,
D_ID NUMBER,
SALARY NUMBER(10,2),
Commission_PCT(4,2)):

CREATE TABLE Dim_Job(
J_ID NUMBER,
Job_ID VARCHAR2(30),
Job_title VARCHAR2(40));

CREATE TABLE Dim_Period(
P_ID NUMBER,
P_Year NUMBER);

CREATE TABLE Dim_Department(
D_ID NUMBER
Department_ID NUMBER,
Department_name (VARCHAR2(40));

CREATE SEQUENCE Dim_ID;

INSERT INTO Dim_Job
SELECT Dim_ID.NEXTVAL, Job_ID, Job_title
FROM hr.jobs;
– in the select, it is all the fields other than the first ID

create table of fact_table using %rowtype, create instance

create a cursor than selects all the fields that the fact table has, inner join all tables, group by the dimension ids
FROM hr.employees e INNER JOIN Dim_job j
ON e.jobID = j.JobID
INNER JOIN Dim_department d ON e.departmentID = d.DepartmentID
group by J-ID, P_ID, D_ID;

open cr, bulk collect into created table, clsoe cr.

use forall to insert the data in the created table into the fact table