DBMS FINALS

SELECT STATEMENTS

DML STATEMENTS

TRANSACTION CONTROL STATEMENTS

SQL STATEMENTS in PL/SQL:

INSERT

UPDATE

DELETE

MERGE

DML Commands

COMMIT

ROLLBACK

SAVEPOINT

TRANSACTION CONTROL statements:

True

You cannot directly execute DDL and DCL statements because they are constructed and executed at run time— that is, they are dynamic.

Dynamic SQL with EXECUTE IMMEDIATE statement

The recommended way of working with DDL and DCL with PL/SQL.

INTO

The ____ clause is mandatory and occurs between the

SELECT and FROM clauses.

INTO clause

It is used to specify the names of PL/SQL variables that

hold the values that SQL returns from the SELECT clause

Cursor

Every time an SQL statement is about to be executed, the Oracle server
allocates a private memory area to store the SQL statement and the data that
it uses

Implicit cursor

Defined automatically by Oracle for all SQL data manipulation statements, and for queries that return only one row.

SQL

An implicit cursor is always automatically named ?

Explicit cursors

Defined by the PL/SQL programmer for queries that return more than one row.

Cursor attributes

Automatically declared variables that allow you to evaluate what happened when a cursor was last used.

SQL

Attributes for implicit cursors are prefaced with?

Cursor attributes

How can you test the outcome of your SQL statements?

SQL%FOUND

Boolean attribute that evaluates to TRUE if the most recent SQL statement returned at least

SQL%ROWCOUNT

An integer value that represents the number of rows affected by the most recent SQL statement.

Control structures

You can change logical flow of statements within PL/SQL block with what?

IF

CASE

LOOP

PL/SQL Control Structures:

CASE statements

Used to test for non-equality conditions, assigning a value.

LOOP statements

Repeats a statements multiple times

Basic Loops

Performs repetitive actions without overall conditions.

FOR loops

Performs iterative actions based on a counter.

WHILE loop

Performs repetitive actions based on a condition

Record

A composite data type consisting of a group of related data items stored as fields, each with its own name and data type.

True

You can refer to the whole record by its name and/or to individual fields by their names.

False

When declaring the cursor, include the INTO clause in the cursor declaration
because it appears later in the FETCH statement.

ORDER BY clause

This clause is used for processing rows in a specific sequence is required.

SELECT

The active set of a cursor is determined by the _______ statement in the cursor declaration

Parameter

A variable whose name is used in a cursor declaration

Predefined

Non-predefined

Two types of Oracle Server Errors:

Predefined Oracle server errors

Each of these errors has a predefined name, in addition to a standard Oracle error number (ORA- #####) and message.

Non-predefined Oracle server errors

Each of these errors has a standard Oracle error number (ORA-#####) and error message, but not a predefined name.

You declare your own names for these so that you can
reference these names in the exception section

Anonymous Blocks

Unnamed executable PL/SQL blocks that cannot be reused
nor stored in the database for later use.

Procedures and Functions

Also known as subprograms and are named blocks that are compiled and stored in the database.

PROCEDURE

A named PL/SQL block that can accept
parameters, used to perform an action.

Functions

A named PL/SQL block (subprogram) that can accept optional IN parameters and must return exactly one value

DML/DCL

COMMIT/ROLLBACK

Avoid the following within functions:

Function identifier

Acts like a variable whose value depends on the parameters passed
to it.

PROCEDURE

To store a series of actions for later execution

FUNCTION

To compute a value that must be returned to the calling environment

ONE

Functions return how many values through RETURN?

PACKAGES

Containers that enable you to group together related PL/SQL subprograms, variables, cursors, and exceptions

PACKAGE SPECIFICATION

Interface to applications.

Must be created first

Declares constructs visible to calling environment

PACKAGE BODY

Contains the executable code of the subprograms that were declared in the package specification.

Can also contain its own variable declarations

Trigger

A PL/SQL block associated with a specific action (an event) such as a successful logon by a user, or an action taken on a database object such as a table or view

Trigger

Executes automatically whenever the associated action occurs

Row-level trigger

A trigger fires once for each row affected by the
triggering statement

Statement-level trigger

A trigger fires once for the whole statement

DML trigger

A trigger that is automatically fired (executed) whenever an
SQL DML statement (INSERT, UPDATE, or DELETE) is executed

DDL triggers

triggers are fired by statements: CREATE, ALTER, or
DROP.

Database Event triggers

Event triggers are fired by non-SQL events in the database

ON DATABASE

 fires the trigger for DDL on all schemas in the
database

ON SCHEMA

fires the trigger only for DDL on objects in your
own schema

ON DATABASE

fires the trigger for events on all sessions in the
database.

ON SCHEMA

fires the trigger only for your own sessions

False

You can use INSTEAD OF with Database Event
triggers.

LOGON

SHUTDOWN

SERVERERROR

You can define triggers to respond to such system events as:

Data warehousing

An electronic storage of a large amount of information by a business or organization.

Data warehousing

A type of data management system that is designed to enable and support business intelligence (BI) activities, especially analytics.

Database

A transactional system that is set to monitor and update real-time
data in order to have only the most recent data available.

Data warehouse

A programmed to aggregate structured data over a period of
time.

Data Warehouse Architecture

All data warehouses share a basic design in which metadata, summary data,
and raw data are stored within the central repository of the warehouse

Operational data

Must be cleaned and processed before being put in the warehouse

Hub and spoke

Adding data marts between the central repository and end users
allows an organization to customize its data warehouse to serve various lines of
business. When the data is ready for use, it is moved to the appropriate data mart.

Sandboxes

Private, secure, safe areas that allow companies to quickly
and informally explore new datasets or ways of analyzing data without having to
conform to or comply with the formal rules and protocol of the data warehouse

Top tier

Consists of the Client-side front end of the architecture

Middle tier

Consists of the OLAP Servers

Bottom tier

Mainly consists of the Data Sources, ETL Tool, and Data Warehouse.

OLAP

Used to provide information to business analysts and managers

Load Manager

It performs with all the operations associated with the extraction and
load of data into the warehouse.

Also known as the FRONT COMPONENT

Load Manager

These operations include transformations to prepare the data for
entering the Data warehouse

Warehouse Manager

Performs operations associated with the management of the data in the warehouse.

Warehouse Manager

It performs operations like analysis of data to ensure consistency,
creation of indexes and views, generation of denormalization and
aggregations, transformation and merging of source data and
archiving and baking-up data.

Query Manager

It performs all the operation operations related to the management of
user queries

Also known as BACKEND Component

Query Manager

The operations of this Data warehouse components are direct queries
to the appropriate tables for scheduling the execution of queries.

Data Reporting
Query Tools
Application development tools

EIS tools
OLAP tools and data mining tools.

END-USER TOOLS

Enterprise Data Warehouse
Operational Data Store
Data Mart

Three types of data warehouse:

Enterprise Data Warehouse (EDW)

A centralized warehouse providingEnterprise Data Warehouse (EDW) decision support service across the enterprise

Enterprise Data Warehouse (EDW)

It offers a unified approach for organizing and representing data.

Enterprise Data Warehouse (EDW)

It also provide the ability to classify data according to the subject and give
access according to those divisions.

One-tier architecture

Architecture for EDW that has a database directly connected
with the analytical interfaces where the end user can make queries

Data Mart

A low-level repository that contains domain-specific
information

Data mart level

Added between the user interface and EDW

Two-tier architecture

Another, smaller-sized database that extends EDW
with dedicated information for your sales/operational departments, marketing, etc.

Three-tier architecture

On top of the data mart layer, enterprises also use online analytical processing (OLAP) cubes.

OLAP cube

a specific type of database that represents data from multiple dimensions.

Data Mart

A subset of the data warehouse. It is specially designed for a particular line of business, such as sales, finance, sales or finance

Data Mart

A scaled-down version of a data warehouse aimed at
meeting the information needs of a homogeneous small group of end
users such as a department or business unit (marketing, finance,
logistics, or human resources)

Data mart

It typically contains some form of aggregated data and is used as the primary source for report generation and analysis by this end user group

Dependent data mart

A type of data mart that allows sourcing organization's data from a
single Data Warehouse

Independent data mart

A type of data mart created without the use of a central data
warehouse.

Hybrid data mart

A type of data mart that can take data from data warehouses or operational

systems.

Schema

A logical description that describes the entire database.

It has all data items and also different aggregates associated with the data.

In the data warehouse there includes the name and description of records.

Star Schema

Simplest type of Data Warehouse schema

Optimized for querying large data sets

One fact table

The center of the star can have ______ and a number of associated dimension tables.

Fact table

Table that contains all the facts or the business information, which can be subjected to analysis and reporting activities when required.

Dimension

A collection of reference information about a measurable in the
fact table.

Primary Key column of the dimension table

Has uniquely identifies each dimension record or row.

Snowflake schema

An extension of star schema where the dimension tables are connected to one or more dimensions.

Keeps same fact table structure as star schema.

Snowflake schema

In this schema, the dimension tables are normalized which splits data into additional tables.