Week 10/11

Question 1

Physical vs Logical objects

Do physical objects grow in size/occupy disk space? How about logical objects? What are some examples of both?

Answer 1

Physical objects occupy diskspace and grow in size. Think tables, indexes, materialized views

Logical objects do not occupy disk space and do not grow in size. Think views, sequences, synonyms, procedures, functions, triggers.

Question 2

How do indexes optimize the retrieval process?

Answer 2

They speed up select statements

Question 3

Every primary key and unique key column constraint automatically has an

Answer 3

index

Question 4

How can users create an index?

Answer 4

CREATE INDEX

Question 5

What is the drawback of Indexes?

Answer 5

They slow down DML in order to maintain the index (Insert something, Index must be updated)

Question 6

What are sequence generated values generally used for?

Answer 6

Primary key values in tables

Question 7

Are sequences linked to tables?

Answer 7

No, but it’s good practice to create a dedicated sequence for a tables column

Question 8

What sort of pseudo columns do sequences use to return values?

Answer 8

<sequenceName>.currvalue and <sequenceName>.nextval
</sequenceName></sequenceName>

Question 9

What are synonyms used for?

Answer 9

To shorten complex table/view names. They do not replace the name.

Question 10

PL/SQL can do programming other than DB programming

Answer 10

False, it’s restricted

Question 11

PL/SQL can have any number of statements, which reduces network traffic. This means that DB operations can be sent to the server in one large unit, rather than many separate statements

Answer 11

True. This minimizes network traffic.

Question 12

T/F – With PL/SQL, the program must reside at the front end

Answer 12

False, it can reside on the front end or within the Oracle DB server as a stored subprogram

Question 13

What sort of PL/SQL subprograms can be stored in the database?

Answer 13

Procedures, Functions, Triggers, Packages

Question 14

PL/SQL Sub-programs can be

Answer 14

Anonymous Blocks or Named Sub Programs

Question 15

What do stored sub programs do?

Answer 15

They promote reusability, better performance, programmability

Question 16

What types of SQL statements are valid in PL/SQL, what types are not?

Answer 16

Valid: DML(insert), TCL (Commit, Rollback), SQL functions
Invalid: DDL(Create), DCL(Grant, Revoke)

Question 17

For anonymous block PL/SQL syntax, which are optional and which are mandatory?
Declare, Begin, Exception

Answer 17

Declare and Exception and optional, Begin is mandatory

Question 18

Scalar vs Composite variable types: what’s the simple difference

Answer 18

Scaler = 1 value
Composite = 2 values

Question 19

Procedures are generally used for ___ or SELECT, functions are used for ____________

Answer 19

DML, calculations

Question 20

Functions must return…

Answer 20

a single value

Question 21

What are Data Dictionary Views? (DDVs)

Answer 21

Views that can show you information on procedures, functions, packages

Question 22

2 Types of triggers in PL/SQL

Answer 22

Schema level, System

Question 23

Schema level vs System triggers go

Answer 23

Schema - based on a particular table in a schema
System - Database-wide triggers

Question 24

What are the 3 things we must decide when creating a trigger

Answer 24

The time, event, and type

Question 25

What do the time, event and type mean in reference to triggers

Answer 25

Time = before or after event
Event = The event that will trigger the function (insert, update)
Type = row level or statement level

Question 26

What is row level and statement level in reference to trigger type?

Answer 26

Row executes once for each record affected by the event (could be a lot)
Statement executes once regardless of records affected (only once)

Question 27

Packages have 2 parts:

Answer 27

specification and body

Question 28

I have a subprogram that is mentioned in the package body but not the specification, what does that mean?

Answer 28

It means the subprogram is PRIVATE

Question 29

What are the 4 advantages of packages?

Answer 29

Modularity, Encapsulation, Overloading, Performance