Kuwait University

Information Technology
College of Computing Solutions
Sciences and Engineering
Department of Information Science

Lab Manual

ISC 321
Database System I

Prepared by:
Dr. Hanady Abdulsalam
Eng. Eman Al Roumi

Revised Spring 2015-2016

 Contents
Laboratory Hardware, Software / Tools Requirements: ................................................................................................ 3
Laboratory Schedule ......................................................................................................................................................5
Laboratory Policy ..........................................................................................................................................................6
Laboratory Grading Policy ............................................................................................................................................7
Introduction ...................................................................................................................................................................8
Lab #1 – Introduction to Oracle DBMS ........................................................................................................................9
Lab #2 - Creating a Database ....................................................................................................................................... 12
Lab #3 – Retrieving Information from Database Tables I ........................................................................................... 20
Lab #4 – Retrieving Information from Database Tables II .......................................................................................... 24
Lab #5 – Retrieving Information from Database Tables III ........................................................................................ 29
Lab #6 – Creating Sequences and Views ..................................................................................................................... 33
Lab #7 – Report Builder .............................................................................................................................................. 36
Lab #8 – Oracle Forms Builder I ................................................................................................................................. 49
Lab #9 – Oracle Form Builder II ................................................................................................................................. 58
Lab #10 – Oracle Forms Builder III ............................................................................................................................ 68
Lab #11 – Introduction to PL/SQL Programming ....................................................................................................... 74
Lab #12 – Applying PL/SQL On Database ................................................................................................................. 80
Appendix A: Rules to fallow by Computer Lab Users ................................................................................................ 82
Appendix B: Endorsement ........................................................................................................................................... 83

ISC 321 – Database Systems I Page |2

 Laboratory Hardware, Software / Tools Requirements:
In this lab the students will be using SQL Plus, Oracle Forms Builder and Oracle Reports
Builder.

Connecting to Oracle server

1. Select Local Net Service Name Configuration, the click Next

2. Select Add, then click Next

3. Write a server name, then click Next.

4. Select the network protocol.

ISC 321 – Database Systems I Page |3

 5. Type host name IP address

6. Test service to insure information

7. You will be asked for user name and password
8. If all information are correct the test will succeed, click next then finish

ISC 321 – Database Systems I Page |4

 Laboratory Schedule
# Lab Title Lab activity
1 Introduction to Oracle DBMS
2 Creating a Database
3 Retrieving Information from Database Tables I
4 Retrieving Information from Database Tables II
5 Retrieving Information from Database Tables III
6 Creating Sequences and Views
7 Report Builder
8 Oracle Forms Builder I
9 Oracle Forms Builder II
10 Oracle Forms Builder III
11 Introduction to PL/SQL Programming
12 Applying PL/SQL On Database

ISC 321 – Database Systems I Page |5

 Laboratory Policy
 Fallow the laboratory rules listed in appendix “A”
 To pass this course, the student must pass the lab-component of the course.
 Cheating in whatever form will result in F grade.
 Attendance will be checked at the beginning of each Lab.
 Absence for three (03) or more unexcused labs will result in a F grade in the Course. An official
excuse must be shown in one week following return to classes.
 Every unexcused absence leads to a loss of 0.5 % marks.
 Cheating in Lab Work or Lab Project will result F grade in Lab.
 Late Submission of Home Works & Projects will not be accepted.
 There will be no make-up for any Quiz/Exam/Lab.
 Hard work and dedication are necessary ingredients for success in this course.

ISC 321 – Database Systems I Page |6

 Laboratory Grading Policy

Activity Weight

Lab Work 4%

Lab Quizzes 2%

Lab Final Exam 4%

Total 10%

ISC 321 – Database Systems I Page |7

 Introduction

This lab is an integral part of the course ISC 321 Database Systems I. The main objectives of the
lab are to introduce the students with SQL programming and the fundamental concepts of Oracle
Reports Builder and Oracle Forms Builder.

ISC 321 – Database Systems I Page |8

Lab #1 – Introduction to Oracle DBMS

1. Laboratory Objective:
In this lab introduces a breif introduction to DBMS and Oracle 11g

2. Laboratory Learning Outcomes:

 In this lab, you will learn the following:
o The meaning of DBMS.
o Oracle DBMS and Oracle 11 g.
o Relational database.
o The different between oracle database and oracle instance.
o The meaning of tables, SQL and schemas.
o The types of privileges: system privileges and object privileges.
o The types of SQL Statements.
o Create user account in Oracle.

3. Introductory Concepts
Database Management System:
 Database management system (DBMS) is computer software that manages access to
databases
Oracle DBMS
 produced and marketed by Oracle Corporation
 A Relational database management system (RDBMS)
 collections of related information are organized into structures called tables
Oracle Fundamentals
 Oracle Database: collection of related OS files that oracle uses to store and manage a set
of related information. (physical storage of information)
 Oracle instance (Oracle Server): the oracle software that manage the physical db access

ISC 321 – Database Systems I Page |9

  Tables: Organized collection of rows (records) columns (attributes).
 Structured Query Language (SQL): command language designed to access relational
databases.

 Schemas: collection of objects such as tables, views and sequences and owned by a
database user.
 Privileges: the right to execute particular SQL statements.
 System privileges: Gaining access to database
 Object privileges: Manipulating the content of the database objects

Types of SQL Statements

 Data Definition Language (DDL) statements: Allow you to define the data structures,
such as tables using CREATE, ALTER, DROP, RENAME and TRUNCATE statements.
 Data Manipulation Language (DML) statements: to modify the contents of tables
using INSERT, UPDATE and DELETE statements.
 Query statements: to retrieve rows stored in database tables using the SELECT
statement.

Create Account
 You should have system privilege to create user
 The database administrator creates the users
 Syntax
CREATE USER user_name
IDENTIFIED BY password;
 Where
 user_name: The name of the user to be created
 password: the password for the user
 The user must have the necessary permissions to work in the database
 Permissions are granted by a privileged user using the GRANT statement
 Syntax:
GRANT privilege [, privilege …]
TO user_name;

ISC 321 – Database Systems I P a g e | 10

Changing Your Password
 You can ONLY change your password, only administrators are allowed to change others
passwords
 Syntax:
ALTER USER username
IDENTIFIED BY new_password;

ISC 321 – Database Systems I P a g e | 11

Lab #2 - Creating a Database

1. Laboratory Objective:
Ihis lab introduces the SQL statements to

2. Laboratory Learning Outcomes:

 After completing this lesson, you should be able to do the following:
o Learn Oracle basic datatypes.
o Explore how to create tables, constraints.
o Editing the tables, constraints.
o Adding, modifying and removing rows from a table.

3. Introductory Concepts (for this laboratory)

Basic Data Types

 Character Datatypes
 CHAR: stores character values with a fixed length
CHAR(10) = "Rick ", "Jon ", "Stackowiak"
 VARCHAR2: stores variable-length character strings
VARCHAR2(10) = "Rick", "Jon", "Stackowiak"
 Numeric Datatypes
 INTEGER: Stores integer numbers.
 NUMBER: provide a precision of 38 digits and can accept two qualifiers,
precision and scale
 Precision: total number of significant digits in the number
 scale represents the number of digits to the right of the decimal point. If no
scale is specified, Oracle will use a scale of 0
NUMBER(3)= 124
NUMBER(3,2)= 124.26
 Date Datatype
 DATE: stores the date in the form of DD- MON-YY

 Other Datatypes
 RAW and LONG RAW: store objects with their own internal format, such as
bitmaps.
 RAW can hold 2 KB,
 LONG RAW can hold 2 GB
 ROWID: represents the specific address of a particular row

Create Table
CREATE TABLE table_name (
column datatype [CONSTRAINT constraint_def DEFAULT default_exp]
[,...]
);

ISC 321 – Database Systems I P a g e | 12

  Where
 table_name specifies the name you assign to the table.
 column specifies the name you assign to a column.
 datatype specifies the type of a column.
 constraint_def specifies the definition of a constraint on a column.
 default_exp specifies the expression used to assign a default value to a column.

Naming Rules and Guidelines

 Table names and column names:
 Must begin with a letter.
 Must be 1 to 30 characters long.
 Must contain only A-Z, a-z, 0-9, _ ,$, and #.
 Must not duplicate the name of another object owned by the same user.
 Must not be an Oracle server reserved word.
 Use descriptive names for tables and columns
 Names are case-insensitive.

Constraints
 ensures that data violating that constraint is never accepted
 Types
 NOT NULL
 Unique: cannot enter values that already exist in another row in the table
 Primary key:
 enforces both the unique and the NOT NULL constraints.
 will create a unique index the specified column(s).
 Foreign key: is defined for a table (known as the child) that has a relationship
with another table in the database (known as the parent).
 Check: is a Boolean expression that evaluates to either TRUE or FALSE.

Constraint Guidelines
 You can name the constraint, or Oracle server generate a name
 Create a constraint at either of the following times:
 At the same time as the table is created
 After the table has been created
 View a constraint in the data dictionary

Create Table Example

Create the student table with the following:
Attributes Data Type Length Constraint
SID Number 8 Primary Key
f_name Character 20 Not null
major Character 20 Not null
current_credits Number 3

ISC 321 – Database Systems I P a g e | 13

 CREATE TABLE Student
(SID NUMBER(8)
CONSTRAINT std_SID PRIMARY KEY,
f_name VARCHAR2(20)
CONSTRAINT std_fname NOT NULL,
major VARCHAR2(20)
CONSTRAINT std_major NOT NULL,
current_credits NUMBER(3)
);

Getting Information on Tables

 Performing a DESC[RIBE] command on the table
 Example:
DESCRIBE student
DESC student

Getting Information on Constraints

SELECT constraint_name, constraint_type, status, deferrable, deferred
FROM user_constraints
WHERE table_name = „STUDENT‟;

Changing a Table
 You change/alter a table using the ALTER TABLE statement.
 You can use ALTER TABLE to perform tasks such as:
 Add, modify, or drop a column
 Add or drop a constraint
 Enable or disable a constraint

Adding a Column
 Use ADD clause to add column
ALTER TABLE table_name
ADD (column datatype [CONSTRAINT constraint_def DEFAULT default_exp] );
 Example:
ALTER TABLE Student
ADD (l_name VARCHAR2(15));

Modifying a Column
 Change the size of a column
 Change the precision of a numeric column
 Change the data type of a column
 Change the default value of a column
 Affects only subsequent insertion to the table
 Syntax:
ALTER TABLE table_name
MODIFY (column new(data type) );

ISC 321 – Database Systems I P a g e | 14

 ALTER TABLE Student
MODIFY (f_name VARCHAR2(15));

ALTER TABLE Student

MODIFY (major CHAR(3) );

Add a primary key constraint

ALTER TABLE Classes
ADD CONSTRAINT cls_pk
PRIMARY KEY (dep,course);

Add a foreign key constraint

ALTER TABLE personal_info
ADD CONSTRAINT personal_info_fkey
FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
ON DELETE CASCADE;

Dropping a Column
 Use DROP COLUMN clause to drop columns you no longer need from the table
 The column may or may not contain data
 Only one column can be dropped at a time
 The table must have at least one column remaining in it after it is altered.
 Syntax:
ALTER TABLE table_name
DROP COLUMN name;
 Example:
ALTER TABLE student
DROP COLUMN f_name;

Adding a CHECK Constraint

 Example:
ALTER TABLE Student
ADD CONSTRAINT student_major
CHECK ( major IN („CSC‟,‟HIS‟,‟ECN‟,‟MUS‟,NUT‟));

ALTER TABLE Student

ADD CONSTRAINT student_credits
CHECK (current_credits > 0);

Dropping a Constraint
 Syntax:
ALTER TABLE table_name
DROP CONSTRAINT constraint;
 Example:
ALTER TABLE student
DROP CONSTRAINT std_major;

ISC 321 – Database Systems I P a g e | 15

 Truncating a Table
 To delete the entire contents of a table
TRUNCATE TABLE table_name;

Drop a Table
 Remove rows and data structure
 Can NOT be rolled back
 Syntax:
DROP TABLE table_name;

Rename Table
 You rename a table using the RENAME statement
 Syntax:
RENAME table_name TO new_table_name;

Adding Rows
 Use INSERT statement to add rows to a table.
 Supply a value for primary key and all columns that are defined as NOT NULL.
 Enclose character and date values within single quotation marks.
 Optionally list the columns in the INSERT clause.
 If columns list is omitted, all the values must be listed in the default order of the column
in the table.
 Syntax:
INSERT INTO table_name [(column1, column2, …)]
VALUES (value1, value2, …);

INSERT INTO student

(sid, f_name, l_name ,major ,current_credits)
VALUES (1000,'Sara','Jassem',„CSC',120);

INSERT INTO student

(sid, f_name, l_name, major, current_credits)
VALUES (1001,„Maha','Naser',„ttt',60);

Modifying Rows
 Use UPDATE statement to modifying existing rows in a table.
 Syntax:
UPDATE table_name
SET column = value [,column=value,…]
[WHERE condition];
 Example:
UPDATE student
SET current_credits=66
WHERE sid=1001;

ISC 321 – Database Systems I P a g e | 16

 Removing Rows
 Remove existing rows from table by using the DELETE statement.
 Specific rows are deleted when you specify the WHERE clause.
 All rows in the table are deleted if you omit the WHERE clause.
 Syntax:
DELETE [from] table_name
[WHERE condition];

4. Laboratory Instructions
How to use SQL Plus?

1. Click on the SQL Plus icon in your desktop as shown in the picture below

2. In log on window type your user name and password as given to you by instructor. In the
host string type orcl

3. The following figure shows the results of logging into Oracle using SQL*Plus

ISC 321 – Database Systems I P a g e | 17

SQL*Plus commands

SQL> CLEAR SCREEN

SQL> SHOW USER
SQL> SET PAGESIZE 100

5. Laboratory Exercises
Consider the following ER diagram
ID ID
Name Ttile

1 N
Address Author Write Book Type

Phone Price

When reducing this ERD into tables you will get:

Author
ID Name Address Phone

ID Title Price Author_ID

ISC 321 – Database Systems I P a g e | 18

 1. Write SQL statements to create the previous tables according to the following constraints:
Author Table
Attributes Data Type Length Description
ID Integer 5 Primary Key
Name Character 25 Not Null
Address Character 35
Phone Number 8

Book Table
Attributes Data Type Length Description
ID Integer 9 Primary Key 1
Title Character 35 Primary Key 2
Scale = 2
Price Number 5
Precision = 3
Author_ID Integer 5 Foreign Key

2. Insert the following Data into Author Table

ID Name Address Phone
12121 John Smith 123, 4th Street 52525252

3. Insert the following Data into Book Table

ID Title Author_ID
123456789 The Secret 12121

4. Modify the book table to add the following:

Attribute Data Type Length
Type Character 20

ISC 321 – Database Systems I P a g e | 19

Lab #3 – Retrieving Information from Database Tables I

1. Laboratory Objective:
The objective of this laboratory is to introduce students to retrive information from one
database table using select statements.

2. Laboratory Learning Outcomes:

 After completing this lesson, you should be able to do the following:
o Perform single table query
o Use aliases to reference tables and columns
o Merge column output using concatenation operator
o Limit the rows that are retrieved by a query

3. Introductory Concepts
Capabilities of SQL Select Statements

Using SELECT statement , you can do the following:

Projection: choosing All/ particular columns in table that you want to returned by your query.
Selection: choosing particular rows in a table.
Joining: bring together data that stored in different table.

Basic SELECT Statement

 Used to retrieve information from database tables
 Syntax:
SELECT [DISTINCT] *, or column [alias],…,
FROM table1,table2,…,tablen,
[WHERE condition(s)];

ISC 321 – Database Systems I P a g e | 20

 Select is a list of one or more cloumns
“*” Selects all columns
Distinct suppresses duplicates
Column/expression name of column or expression
Alias gives selected column different heading
From table specifies the table containing the columns

 List all student information

select * from student;

 Display the building and room numbers in every building

select building, room_number
from rooms;

Performing Arithmetic
 You can create expressions with number and date data by using arithmetic operators
Operator Description
+ Addition
- Subtraction
* Multiplications
/ Division
 You can use arithmetic operators in any clause of a SQL statement except the FROM
clause
select building, room_number, 0.1*(number_seats + 20)
from rooms;

Using Column Aliases

 Renames a column heading
 Useful with calculations
 Immediately follows the column name
 Required double quotation marks if it contains spaces or special characters or if it is case
sensitive

select dep "Department Name", course, current_student

from classes;

select dep, course, 2*current_student Double_Student

from classes;

Combining Column Output Using Concatenation

 Links columns or character strings to other columns
 Represented by two vertical bars (||)
 Creates a resultant column that is a character expression

ISC 321 – Database Systems I P a g e | 21

 select dep|| ' ' || course "course Name"
from classes;

The WHERE Clause

 Used to specify the rows you want to retrieve
 The WHERE clause follows the FROM clause
 In a WHERE clause simple conditions based on comparison operator is used to identify
specific row.
 The WHERE clause can compare values in columns, literal values, arithmetic
expressions, or functions.
 Example:
 Display all rooms numbers and buildings for rooms located in Building 7
select Room_number, Building
from rooms
where Building='Building 7';

4. Laboratory Instructions
Consider the following ER Diagram
Course Num_Credits
SID ID
f_name DEP Current_student
f_name
M N M 1
l_name Student Registered Class Teach Professor

Major M l_name
Current_Credits Grade emp_date Salary
In

Room_id Room Number_seats

Building
Room_number

Student
SID f_name L_name Major Current_Credits

Professor
ID f_name l_name Salary emp_date

ISC 321 – Database Systems I P a g e | 22

 Rooms
Room_id Building Room_number Number_seats

Classes
DEP Course Current_student Num_Credits room_id prof_id

Registered_Students
Student_ID DEP Course Grade

5. Laboratory Problem Description

1) List the course and room id in History classes
select course, room_id
from classes
where dep='HIS';

2) Display all students IDs, first names, and last names whose major is computer science
select sid,f_name,l_name
from student
where major='CSC';

3) Display all grades for student 1009

select dep,course,grade
from REGISTERED_STUDENTS
where student_id = 1009;

6. Laboratory Exercises

1. Display the full name (first name and last name) and major of all students
2. Display the buildings and the room numbers of all rooms which have 50 seats
3. Display the full name (first name and last name) and passed credits plus 4 of students
whose major is music

ISC 321 – Database Systems I P a g e | 23

Lab #4 – Retrieving Information from Database Tables II

1. Laboratory Objective:
The objective of this laboratory is to introduce students to the use of different Operators in select
statements

2. Laboratory Learning Outcomes:

After completing this exercise, you should be able to do the following:
 Using SQL operators
 Using comparison operators
 Using Logical operators
 Sorting the result
 Using two tables in select statement

3. Introductory Concepts
Displaying Distinct Rows
Eliminating Duplicate Rows by using DISTINCT keywords in the SELECT clause
select major
from student;

select distinct major

from student;

Comparing Values

Operator Description
= Equal
<> Or != Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
ANY Compares one value with any value in a list
ALL Compare one value with all values in a list

ISC 321 – Database Systems I P a g e | 24

  Retrieve all student information whose major is not computer science
Select *
from student
where major != 'CSC';

 Write a query to display room number and building name of rooms having capacity (max
seats) more than 50
Select building, room_number
from rooms
where number_seats > 50;

 Display all classes information where course number is either 101 or 102
Select *
from classes
where course = any (101, 102);

 Retrieve the student id and first name of all students where student ids is greater than
either 1004 or 1006
select sid, f_name
from student
where sid >= any (1004, 1006);

 Retrieve the student id and first name of all students where student ids is greater than
both 1004 or 1006
select sid, f_name
from student
where sid >= all (1004, 1006);

Using the SQL Operators

Operator Description
LIKE Matches patterns in strings
IN Matching lists of values
BETWEEN Matched range of values
IS NULL Matches null values

Reverse the meaning of an operator

 NOT LIKE
NOT  NOT IN
 NOT BETWEEN
 IS NOT NULL

ISC 321 – Database Systems I P a g e | 25

 Using The Like Operator
 allows comparisons on parts of character string
 ( _ ) replaces single character
 ( %) replaces an arbitrary number of characters
 Examples:
select sid, f_name
from student
where f_name like '%al%';

select *
from classes
where dep not like '%S';

select *
from classes
where dep like '%S_' ;

Using The IN Operator

 Retrieve the rows whose column value is in a list
 Can be used with any data type
 Example:
Select *
from classes
where course in (101, 102);

Using The Between Operator

 To retrieve the rows whose column value is in a specific range
 The range is inclusive
 Example:
select *
from registered_students
where grade between 'A' and 'B';

select *
from student
where sid between 1001 and 1005;

Using The Logical Operators

 Allow to limit rows based on logical conditions
Operator Description
x AND y Returns true when both x and y are true
x OR y Returns true when either x or y is true
NOT x Returns true if x is false

ISC 321 – Database Systems I P a g e | 26

 Logical Operators Example
select *
from registered_students
where student_id > 1006 and grade between 'A' and 'B';

select *
from registered_students
where student_id > 1006 or grade between 'A' and 'B';

Sorting Rows
 Use ORDER BY clause to sort the rows retrieved by a query
 ASC: ascending order, default
 DESC: descending order
 Example:
Select *
from student
order by l_name;

Select *
from student
order by l_name ASC, f_name DESC;

Using Two Tables

SELECT table1.column
FROM table1, table2
WHERE table1.column = table2.column AND table2.column = value;

4. Laboratory Problem Description

1. Retrieve the full names and the major of students registered in class CSC102
Select f_name || ' ' || l_name "Full Name", major
from student, registered_students
where student.sid = registered_students.student_id and
registered_students.dep='CSC' and registered_students.course =
102;

2. Display the full name and the salary of the professor who teaches class HIS101
select f_name || ' ' || l_name "Full Name", salary
from professor,classes
where classes.prof_id = professor.id and classes.dep = 'HIS' and
classes.course = 101;

3. Get room number and the building for the classes 300 level classes
select classes.dep,course,building,room_number
from rooms,classes
where classes.room_id = rooms.room_id and classes.course between
300 and 399;

ISC 321 – Database Systems I P a g e | 27

5. Laboratory Exercises
1. Display room number and building of rooms having seats less than 100 or more than

350
2. List the professor full name who was hired in 2008
3. Display the id, the full names and the grades of students registered in class NUT307
order by student id

ISC 321 – Database Systems I P a g e | 28

Lab #5 – Retrieving Information from Database Tables III

1. Laboratory Objective:
The objective of this laboratory is to introduce students to more complicated select
statement

2. Laboratory Learning Outcomes:

 After completing this lesson, you should be able to do the following:
 Using table alias
 Identifying available group functions
 Group data by using the group by clause
 Define subqueries
 Write single-row and multiple-row subqueries

3. Introductory Concepts (for this laboratory)

Group Functions
Example of Group Function

Functions Purpose

AVG ([DISTINCT/ALL] n) Average the value of n, ignoring null values

COUNT(*/[DISTINCT/ALL] Number of rows, where expr evaluates to something other

expr) than null (count all select rows using *, including duplicates
rows and nulls)

MAX([DISTINCT/ALL] expr) Maximum value of expr, ignoring null values.

MIN([DISTINCT/ALL] expr) Minimum value of expr, ignoring null values.

STDDEV([DISTINCT/ALL] n) Standard deviation of n, ignoring null values.

SUM([DISTINCT/ALL] n) sum value of n, ignoring null values.

VARIANCE([DISTINCT/ALL] n) variance of n, ignoring null values.

 select max(salary), min(salary), avg(salary), sum(salary)

from professor;

 select count(distinct prof_id)

from classes;

ISC 321 – Database Systems I P a g e | 29

  select count(*)
from registered_students
where student_id = 1002;

Using the GROUP BY

 To group rows into blocks with a common column value
 Example:
select prof_id
from classes
group by prof_id
order by prof_id;

select prof_id, count(*)

from classes
group by prof_id
order by prof_id;

Using Multiple Columns in a Group

 select student_id,dep,course,grade
from registered_students
group by student_id,dep,course,grade
order by student_id,course;

Subqueries
 Some problems can be solved by combining two queries, placing one query inside the
other query
 The inner query (or subquery) reurns a value that is used by the outer query (or main
query).
 Syntax:

Guidelines for Using Subqueries

 Enclose the subqueries in parentheses.
 Place subqueries on the right side of the comparison condition.
 Use Single-row comparison operators(=,> ,>=,<,<=,<>) with single-row subqueries and
use multiple-row operators (IN, ANY, ALL) with multiple-row subqueries.
 The ORDER BY cluase in the subquery is not needed

ISC 321 – Database Systems I P a g e | 30

EXISTS and NOT EXISTS Functions
 Syntax:
SELECT column
FROM table
WHERE [EXITS | NOT EXISTS]
(SELECT column
FROM table
WHERE condition);

4. Laboratory Problem Description

1. Display the total number of students in 101 courses
select sum(current_student)
from classes
where course=101;

2. Get the names of professors who do not teach any class

Select f_name || ' ' || l_name Name
from professor
where id not in
(select prof_id
from classes);

3. Without using MAX function, get the name of the professor that has the maximum salary
select f_name || ' ' || l_name Name
from professor
where salary >= all
(select salary
from professor);

4. Find all room numbers and buildings for rooms in which professor John Smith teach.
Order the result according to the room number in ascending order.
select room_number,building
from rooms
where room_id in
(select room_id
from classes
where prof_id in
(select id
from professor
where f_name = 'John' and l_name = 'Smith')
)
order by room_number;

ISC 321 – Database Systems I P a g e | 31

 5. Retrieve the name of students who took A in all their registered classes.
select f_name || ' ' || l_name Name
from student
where not exists
(select student_id
from registered_students
where grade != 'A' and sid = student_id);

5. Laboratory Exercises

1. Display the maximum, minimum, and average number of seats for all rooms having seats
between 10 and 100
2. Display the name and salary of professors having salary greater than Smith‟s Salary
3. Retrieve the classes (department and course) that have teachers having salary more than
1600 using subquery and without subquery

ISC 321 – Database Systems I P a g e | 32

Lab #6 – Creating Sequences and Views

1. Laboratory Objective:
The objective of this laboratory is to introduce students to sequences and views

2. Laboratory Learning Outcomes:

 After completing this lesson, you should be able to do the following:
 How to create and use sequence
 How to create and use views

3. Introductory Concepts (for this laboratory)

Sequence
 It is a database item that generates a sequence of integer.
 Used to create primary key values.
 Syntax:

 INCREMENT BY indicates how the sequence numbers should change. The default
increment is 1. A negative number will cause decrement the sequence.
 START WITH indicates the initial value of the sequence. The default start value for
ascending sequences is MINVALUE and MAXVALUE for descending. Note: START
WITH cannot be more than MAXVALUE.
 MINVALUE is the lowest number the sequence will generate. The default is 1 for
ascending sequences.
 MAXVALUE is the highest number the sequence will generate. The default is -1 for
ascending sequences.
 CYCLE option makes the sequence to restart either type of sequence where its max
value or min value is reached.
 CACHE allows a preallocated set of sequence numbers to be kept in memory. The
default is 20. The value set must be less than max value minus min value.

ISC 321 – Database Systems I P a g e | 33

  NOCACHE forces the data dictionary to be updated for each sequence number
generated, guaranteeing no gaps in the generated numbers, but decreasing performance of
the sequence.
 ORDER guarantees that the sequence numbers will be assigned to instances requesting
them in the order the request are received. This is useful in applications requiring a
history of the sequence in with the transactions took place.

Sequence Example
 CREATE SEQUENCE MyFirstSequence;
 CREATE SEQUENCE MySecondSequence
INCREMENT BY 3
START WITH 100;
 CREATE SEQUENCE MyThirdSequence
MAXVALUE 40
MINVALUE 30
INCREMENT BY 3
CACHE 3 CYCLE;

To Use Sequence
 select mysecondsequence.currval from dual;
 select mysecondsequence.nextval from dual;
 select * from user_sequences

Populate Primary Key Using a Sequence

 create table testing
( id number(5) constraint testing_pk primary key);
 INSERT INTO testing values (mysecondsequence.nextval);
 Run the insert statement 4 times
 select * from testing

Views
 Views are customized presentations of data in one or more tables or other views.
 You can think of them as stored queries.
 As with tables, views can be queried, updated, inserted into, and deleted from, with some
restrictions.
 All operations performed on a view affect the base tables of the view.
 View used to:
 Restrict data access
 Make complex queries easy
 Provide data independence.
 Present different views of the same data.

ISC 321 – Database Systems I P a g e | 34

Creating View
CREATE [OR REPLACE] [FORCE/NOFORCE] VIEW view [{alias[, alias] ….]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]];
[WITH READ ONLY [CONSTRAINT constraint]];

 OR REPLACE: re-create the view if it already exist.

 FORCE: creates the view whether or not the base table exist.
 NOFORCE: create the view only if the base tables exist (This is the default)
 view: is the name of the view.
 alias: specifies names for the expressions selected by the view query. (The number of alias
must match the number of expressions selected by the view)
 subquery: select statement, you can use alias for the columns selected.
 WITH CHECK OPTION: specifies that only rows accessible to view can be inserted or
updated.
 constraint: is the name assigned to CHECK OPTION constrain

Commit and Rollback

 Changes happened because of insert, update, and delete statements are temporarily stored
in the database system. They become permanent only after the statement commit; has
been issued.
 You can use the SQL ROLLBACK statement to rollback (undo) any changes you made to
the database before a COMMIT was issued.
 The SQL COMMIT statement saves any changes made to the database. When a
COMMIT has been issued, all the changes since the last COMMIT, or since you logged
on as the current user, are saved.

ISC 321 – Database Systems I P a g e | 35

Lab #7 – Report Builder

1. Laboratory Objective:
The objective of this laboratory is to introduce students to the basics of Oracle Report
Builder

2. Laboratory Learning Outcomes:

 After completing this lesson, you should be able to do the following:
 Create report using report builder

3. Introductory Concepts (for this laboratory)

What is Report Builder?

a powerful enterprise reporting tool that enables you to rapidly develop and deploy
sophisticated Web and paper reports against any data source

What is Report?
A report consists of objects that collectively define the report:
 data model objects (queries, groups, columns, links, user parameters)
 layout objects (repeating frames, frames, fields, boilerplate, anchors)
 parameter form objects (parameters, fields, boilerplate)
 PL/SQL objects (program units, triggers)
 references to external PL/SQL libraries, if any
 code shown in the Web Source view (for JSP-based Web reports)

Report Styles
 tabular reports
A tabular report is the most basic type of report. Each column corresponds to a column
selected from the database

 group above reports

A group above report contains multiple groups in its data model. It is a "master/detail"
report, where there may be a lot of information in the master group. For every master

ISC 321 – Database Systems I P a g e | 36

 group, the related values of the detail group(s) are fetched from the database and are
displayed below the master information.

 Group left report

 A group left report also contains multiple groups in its data model, dividing the
rows of a table based on a common value in one of the columns.
 Use this type of report to restrict a column from repeating the same value several
times while values of related columns change.
 The data model for group above and group left reports is the same, but the layouts
differ; group above reports display the master information at the top while group
left reports display break columns to the side.

 Form like report

A form-like report displays one record per page, displaying field values to the
right of field labels.

 Form letter report

A form letter report contains database values embedded in boilerplate text

ISC 321 – Database Systems I P a g e | 37

  Mailing label report
A mailing label report prints mailing labels in multiple columns on each page. Using
the Report Wizard, you can specify the format for your mailing labels

 Matrix report
A matrix (cross-product) report is a cross-tabulation of four groups of data:
 One group of data is displayed across the page.
 One group of data is displayed down the page.
 One group of data is the cross-product, which determines all possible
locations where the across and down data relate and places a cell in those
locations.
 One group of data is displayed as the "filler" of the cells.

Example
Suppose we want a report that displays professors names and salary and all courses they
teach
In addition to the total number of courses each professor teach

How To Create Report Using The Wizard?

1. Launch Reports Builder

ISC 321 – Database Systems I P a g e | 38

 2. Select “Use the Report Wizard” and click OK
3. If the Welcome page displays, click Next
4. On the Report Type page, select Create Paper Layout Only, then click Next.
5. Type the title of the report you want in the title bar.
6. Choose the format you want your repot to be showed with from the formats listed below
the title bar.

7. Click on next
8. On the next window choose SQL-Statement as the query type and click on next.

ISC 321 – Database Systems I P a g e | 39

 9. Click on connect to get the connect pop on window. Type the user name, password and
database and click on connect

10. The program will verify the user name and password and connect to the specified
database.
If you have the query you want to run the report from, stored in an .sql file, then click on
Import SQL query button and brows for your file.
If you want to build up a new query, then click on the Query builder.
Note: remember that you can always write you own code in the Query Statement Text
Area
11. Once you click on query builder, a pop up window showing all your tables and views will
be shown, include the tables (and views) that are related in your query.

12. Check all fields you want them to be displayed in the report and click OK.

ISC 321 – Database Systems I P a g e | 40

 13. The query that relate all tables and fields will appear in the query statement box

14. Click next, then choose the group fields by which output data is grouped and then click
next

15. Now include the fields you want them to be displayed and click Next

ISC 321 – Database Systems I P a g e | 41

 16. This screen is to include any aggregate function needed in your report,
17. Click on the field you want to have the function on, and then choose the function

18. This screen enables you to modify either the labels or the size of the displayed fields. If
there are any modifications, then do them and click Next
19. Choose a template for your report, then click Finish.

ISC 321 – Database Systems I P a g e | 42

The resultant report is the following:

Controlling Report
 Shows all related queries,
parameters, groups, etc…
 To control the layout of the report

ISC 321 – Database Systems I P a g e | 43

Data Model
To view or update any object property, double click on it

User parameters: to take an input parameter from the user and return the related output.
How to create new user parameter?
 double click on user parameters, a new parameter will be listed under it.
 double click on this parameter
 You can change the name, the type or the size of it.
 To complete the process, you have to update your query to include the condition
including this parameter

How to use the parameter?

 Change the name of the parameter (optional) … say you named it “ENTER_SALARY”
 On the data model right-click on Q_1 and select edit query
 Add the following to the query
 and salary > :ENTER_SALARY

ISC 321 – Database Systems I P a g e | 44

  Run the report by clicking on

Output

Layout Model
 Control the layout of the report

ISC 321 – Database Systems I P a g e | 45

Graph Wizard
 provides an easy way for you to add a wide variety of graphs to report
 Usage notes
 When you specify a graph title, subtitle, footnote, or axis title in the Graph
Wizard, you can insert lexical references (to user parameters, system parameters,
and columns) in the text that will display their value at runtime
 When you specify dates in your graphs, the date values may display in a different
format in a graph than in other fields in your report output
 Primary Graph Types
 Bar graph, line graph, area graph, pie graph, funnel graph, combination graph

Adding Graph to Report

 In the Paper Layout view, click the Graph tool in the tool palette.
 Drag a square in the area where the graph should appear to display the Graph Wizard.
 In the Graph Wizard, specify information for the graph. Click Help on any wizard page
for assistance
 Click Next
 Select the graph type and click Next
 Include the horizontal attributes and click Nest

ISC 321 – Database Systems I P a g e | 46

  Specify the vertical attributes and click Next

 You can change the layout of your graph, Click Next

ISC 321 – Database Systems I P a g e | 47

  Include the title, subtitle and footnote then click Finish

 Graph output:

ISC 321 – Database Systems I P a g e | 48

Lab #8 – Oracle Forms Builder I

1. Laboratory Objective:
The objective of this laboratory is to introduce students to Oracle Forms Builder.

2. Laboratory Learning Outcomes:

 After completing this lesson, you should be able to do the following:
 Create simple form

3. Introductory Concepts (for this laboratory)

What is Form Builder?
 Used to develop form-based applications for presenting and manipulating data in a
variety of ways.
 With Form Builder you can:
 Provide an interface for users to insert, delete, update and query data
 Present data as text, image and custom controls
 Control forms across several windows and database transactions
 Use integrated menus
 Send data to Oracle Reportsy

Form Builder Components

 Canvases are background objects on which you place the interface objects and graphic
elements that end users interact with when they use a Form Builder application.
 Blocks are logical containers for Form Builder items, and are the basic unit of
information in a form module. A form module typically contains multiple blocks.
 Items are interface objects that display information to end users and allow them to
interact with the application.

ISC 321 – Database Systems I P a g e | 49

Canvases

 Content Canvas: (light green)

 Most common canvas type
 Contains the content pane of the window
 Must define at least one content canvas for each window you create.
 Stacked Canvas: (white)
 Displayed on the top of the content canvas assigned to the current window.
 You can display more than one stacked canvas in a window at the same time.
 Tab Canvas: (dark green)
 Made up of one or more tab pages
 Like stacked canvases, tab canvases are displayed on top of a content canvas

Getting Started
 Execute the patch file
 The batch file executed in a separate window, you can minimize it if desired

ISC 321 – Database Systems I P a g e | 50

Creating a New Form Module
 Invoke the Form Builder. This will take you to the Form Builder Welcome page
 Select the “Use the Data Block Wizard” option

 Click Next
 Creating a New Form Module
 The Type page asks you if you want the form to be built on table or procedure, choose
table or view, and click next.

 To choose the table of view you want, click on Browse, and the connection window will
show up. After connecting to the database, you will have the list of tables and views that
are available in your account.

ISC 321 – Database Systems I P a g e | 51

  Select the table for the data source name, then click Ok
 A list of columns in the selected table is displayed

 Select the columns you want to include in the data block.

 Select the “enforce Data integrity” check box if you want the wizard to enforce the
database integrity constraint

 Accept the default name for the data block and click Next.
 Select the “Create the data block, then call the Layout Wizard”

ISC 321 – Database Systems I P a g e | 52

  Select the canvas which you want and select Next

 Choose the data base fields needed to be displayed, and select the type for each

 Change the width and name displayed fields if needed

 Select a layout style for your frame. The options are:

 Form (usually used to create single-record data blocks)
 Tabular (usually used to create multirecord data blocks)

ISC 321 – Database Systems I P a g e | 53

  Enter a title in the Frame Title field
 Enter the number of records that you want to display at run time in the Records Displayed
field
 You can select the Display Scroll check box to display a scroll bar next to the frame
(common for multi data blocks)

 Finish page
Click Finish to create a new frame and lay out the selected items for the new data block.

 You can change the colors of the items you have in the canvas by right click on Canvass
and select Property Palette

ISC 321 – Database Systems I P a g e | 54

  Add values to the major pop list
 right click on it and choose property palette
 Change the Item Type to be List Item
 Click on elements on list and choose more

 Write the values that will appear in the list and its corresponding values you want to deal
with in your database

ISC 321 – Database Systems I P a g e | 55

 List Item Value List Elements
CSC Computer Science
HIS History
ECN Economics
MUS Music

Compile and Running

Click on the compile icon or program > Compile Module

Click on the run icon or program > Run Form

The output

Built-in Procedures
 COMMIT_FORM: used to save the changes (insert)
 CLEAR_FORM(parameter1, parameter2): used to clear contents in form
 parameter1 can be: ASK_COMMIT, DO_COMMIT, OR NO_COMMIT
 parameter2 can be: TO_SAVEPOINT, FULL_ROLLBACK , OR
NO_ROLLBACK
 EXIT_FORM(parameter1, parameter2): used to exit the form
 Check the help for built-in procedures

Dealing With Buttons

 Click on the buttons icon
 Create a new button

ISC 321 – Database Systems I P a g e | 56

  Change the name and label using property palette.
 Right click the button
 Choose smart triggers then choose WHEN-BUTTON-PRESSED
 Write “commit_form;” built in procedure

4. Laboratory Exercises
 Add two more buttons, one is to clear the form and the other to exit.
 You should have the resultant form like the one shown in the following figure

ISC 321 – Database Systems I P a g e | 57

Lab #9 – Oracle Form Builder II

1. Laboratory Objective:
The objective of this laboratory is to introduce students to Oracle Forms Builder.

2. Laboratory Learning Outcomes:

After completing this lesson, you should be able to do the following:
 Understanding form run time window
 Creating List of Values (LOV)
 Create check item
 Creating radio group
 Creating non-input item (read-only text box)

3. Introductory Concepts (for this laboratory)

Blocks
Master and Detail Blocks:
 You can define a data block as detail (child) of a master (parent) data block.
 In the Create relationships screen, click on create relationship  then choose the
master data block
 The join condition will be created automatically (note that if you have the check
box cleared you should choose the joined attributes manually)

Creating Another Data Block

 Go to tools  Data Block wizard
 Create a new data block for registered students table
 Name the block as “Student_Classes”
 Locate the new block in the same canvas
 When you reach the relationship window, click on create relationship then click OK.
 You should have the form as shown in the next slide
 Now, press F7, then go to execute query and you will see the students with his classes
shown.

ISC 321 – Database Systems I P a g e | 58

Form Runtime Window
Mode of operation
 Enter Query Mode
 Normal Mode

Enter Query Mode

 Used to enter search criteria
 It allows:
 Retrieve all records
 Retrieve records by using selection criteria
 Retrieve records by using Query/Where Dialog Box.
 Obtain the number of records from database
 It does not allow:
 navigate out the current block
 exit from the run-time session
 go to next record
 Insert new records
 update existing records
 delete records

Normal Mode
 Used to insert and alter records in the database
 It allows:
 retrieve all records
 insert new records
 update records
 delete records
 commit and rollback
 navigate outside the current block

ISC 321 – Database Systems I P a g e | 59

  exit from run-time session
 It does not allow:
 retrieve a restricted set of records
 Invoke the query/where dialog box

 To run a query:

 Click the Enter Query button

 Enter the condition you want in the related field

 Click Execute query

 To Cancel a query in an enter query mode:

 Click on Cancel Query button

 To insert a record:
 Enter the data you want (make sure you are not in enter query mode)

 Click insert button

 To update a record:
 Search for the record you want using query enter mode
 Update the record
 To delete a record:

 Search for the record you want using query enter mode
 Click delete button
 Discard changes:
 Go to action  clear all

ISC 321 – Database Systems I P a g e | 60

  Navigate between records:
 previous block, previous record, next record and next block respectively

Blocks
 Data Blocks:
 Associated with a database table, view or a stored procedure.
 Its items related to database columns
 Control Blocks:
 Not associated with a database object
 Its items called control items;
 They control the functionalities of the form
 You have to create it manually NOT using the data block wizard

Including Helpful Messages

 In the item properties window, go to help property that has the following features:
 Hint: write your help message
 Display Hint automatically: set it to YES.

Creating LOVs
 LOVs (List Of Values) are objects in form modules that each open their own window
when activated at run time.
 Defined at form level  Used to support text items in any block in form module
 Include data from database
 LOVs have the following qualities:
 Dynamic: takes data from database
 Flexible: used same LOV for many text items
 Independent: Not dependent on specific text item
 Efficient: reuse data loaded to form

Creating an LOV with the LOV Wizard

 Lunch the LOV Wizard
 If the welcome screen appears. Click Next
 In the LOV source page, choose New Record Group based on query. Click Next

ISC 321 – Database Systems I P a g e | 61

  Click Build SQL Query to use Query Builder

 Choose the columns you want to display in the LOV

ISC 321 – Database Systems I P a g e | 62

  Change the size and label of LOV columns if needed

 Specify the title, the width, and the height of the LOV window.
 You can choose to display it at a set position that manually define, or let Forms position it
automatically. Click Next

ISC 321 – Database Systems I P a g e | 63

  Specify the number of records at a time to be fetched from the database

 Assign the LOV with form items

 Click Finish to complete the LOV creation process

ISC 321 – Database Systems I P a g e | 64

How To Use LOV At Rum Time
 Place your mouse cursor in the text item associated with the LOV
 Select Edit Display list of values
 Select an entry from the displayed list, you can type a character to filter the list or you
can search the list by typing a string in the find box.
 Click OK to retrieve the value

Associating LOV With A More Text Items

 Select the text item from which the LOV is to be accessible
 Set the list of values property in the property platter to the required LOV

Input Items
 Items types that accept user input. Include:
 Check boxes
 List items
 Radio groups
 Enable insert, update, delete and query

Check Boxes
 Is a two state interface object that indicates whether a certain value is ON or OFF.
 The display state of check box is always either checked or unchecked
 Although it is limited to two states, it is not limited to just two values. You can specify
the value to represent Checked, the value to represent Unchecked, and how other values
are processed
 Check box can be created in three ways:
 Converting an existing item
 Using the Check Box tool in the Layout Editor
 Using the Create icon in the Object navigator
 Important properties to be set:
 Data Type
 Label
 Access Key
 Initial value
 Value when checked
 Value when unchecked
 Synchronize with item

Radio Groups
 Set of mutually exclusive radio buttons, each representing a value
 Use

ISC 321 – Database Systems I P a g e | 65

  To display two or more static choices
 As an alternative to a list item
 As an alternative to a check box

Creating a Radio Group

 Radio Group can be created in three ways:
 Converting an existing item
 Using the radio group item in the Layout Editor
 Using the Create icon in the Object navigator
 Important properties to be set (for Radio group Property):
 Data Type
 Mapping of Other Values
 Important properties to be set (for Radio button Property):
 Label
 Radio Button Value

How To Convert An Existing Item Into A Radio Group

1. Invoke the property palette for the item that you want to convert.
2. Set the Item Type property to Radio Group.
3. Set the Mapping of Other Values property to specify how the Radio Group should handle
the other values
4. Expand the item node in the Object Navigator. The Radio Buttons node appears
5. Select the Radio Buttons node and click the Create icon.
6. Enter a value, and a label for the radio button
7. Create additional radio buttons by repeating steps 4 to 6

4. Laboratory Exercises
Create a form as it shown in the figure below

ISC 321 – Database Systems I P a g e | 66

 The form should contain one data blocks that is used to display the information about a class.
You should create the following:
- a LOV for professors Ids, first name, and last name and assign the ID text item with it.
- A radio button group to represent the department
- List of buttons:
1. New: create a new empty record
2. Save: Save the inserted data
3. Search: enables user to enter into enter_query mode
4. Show Results: Show the result of the entered query
5. Exit: To exit the form

ISC 321 – Database Systems I P a g e | 67

Lab #10 – Oracle Forms Builder III

1. Laboratory Objective:
The objective of this laboratory is to introduce students to Oracle Forms Builder.

2. Laboratory Learning Outcomes:

After completing this lesson, you should be able to do the following:
 Creating non-input item (read-only text box)
 Creating non-input item (image item, sound item)
 Create calculated items
 Creating a canvas (Stacked Canvas, Tool bar Canvas, Tab Canvas)
 Creating Alerts
 Navigate between forms

3. Introductory Concepts (for this laboratory)

Create Noninput Items

 Used to enhance application by displaying additional data, often from a database table
 Item types that do not accept direct user input include:
 Display item
 Image item
 Calculated items

Display Items
 Are similar to a text item, except that they cannot:
 Be edited
 Be queried
 Be navigated to
 Accept user input
 Can display:
 Display additional, nonbase table information
 Display derived data values

Creating a Display Item

 Using the Item Type property to convert an existing item into a display item
1. Double click on the item to display the Property Palette
2. Change the Item Type to be Display Item

ISC 321 – Database Systems I P a g e | 68

Image Item
 Is a special interface control that can store and display vector or scanned images.
 Store and display images
 You can store images in
 Database – long raw
 File system – any supported file format

Calculated Items
 They accept item values that are based on calculations
 They are read-only
 They can be expressed as:
 Formula: calculated item is a result of horizontal calculation
 Summary: calculated item is a vertical calculation involving items of single item
over all the rows within a single block
 Generally use display items as calculated items

Setting Item Properties for the Calculated Item

 Formula
 Calculation Mode
 Formula
 Summary
 Calculation Mode
 Summary Function
 Summarized Block
 Summarized Item

ISC 321 – Database Systems I P a g e | 69

Rules for Summary Items
 Summary item must be reside in:
 The same block as the summarized item
 A control block with Single Record property set to Yes
 Summarized item must be reside in:
 A data block with Query All Records property or Precompute Summaries
property set to Yes
 A control block
 Datatype of summary items must be Number, unless using MAX or MIN

Creating Calculated Item Based on a Summary

1. Create a new item in the Object Navigator
2. Open property Palette of an item.
3. Set the following properties:
1. Canvas: Select the canvas in which the item reside
2. Item Type: Display Item
3. Data Type: Number (* only if it is not min/max)
4. Calculation Mode: Summary
5. Summary Function: select the required function
6. Summarized Block: Select a block over which all rows will be summarized
7. Summarized Item: Select item to be summarized
4. Open the property Palette for the data block and set Query All Record property to Yes

Calculated Item: Example

Create a calculated item and count the number of courses for a student
1. Set the required property as shown

ISC 321 – Database Systems I P a g e | 70

What Is a Canvas?
 Is a surface inside a window container on which you place visual objects such as
interface items and graphics
 Each item in a form must refer to no more than one canvas
 Canvas Type:
 Content Canvas
 Stacked Canvas
 Toolbar Canvas
 Tab Canvas

The Stacked Canvas

 Display on top of a content canvas
 Shares a window with a content canvas
 Size:
 Usually smaller than the content canvas
 Determined by viewport size
 Created in:
 Layout Editor
 Object Navigator
 With stacked canvases you can achieve the following:
 Scrolling views as generated by Oracle Designer
 Creating an overlay effect within a single window
 Displaying headers with constant information (ex. Company name)
 Creating a cascading or a revealing effects within a single window
 Displaying additional information
 Displaying information conditionally
 Displaying context-sensitive help
 Hiding information

Creating a Stacked Canvas in the Layout Editor

1. Display the Layout Editor for the content canvas on which you wish to create a stacked
canvas
2. Click the Stacked Canvas tool in the toolbar
3. Click and drag the mouse in the canvas where you want to position the stacked canvas
4. Open the Property Palette of the stacked canvas. Set the following properties:
1. Raise on Entry
2. Viewport X/Y
3. Viewport width/height
4. Visible
5. Window
6. Show horizontal/vertical scroll bar

 To show/hide the stack canvas at run time, use SHOW_VIEW(„canvas_name‟)/

HIDE_VIEW(„canvas_name‟) built in procedures.
 Make sure to set the visible property for the canvas to NO.

ISC 321 – Database Systems I P a g e | 71

  If you want to view stacked canvas in layout editor, go to View  Stacked View, and
select/unselect the canvases you want them to be shown/hidden.
 NOTE: [Control]+ Click to clear the selection in menu.

Stacked Canvas Example

 create a help message stack canvas and two buttons; one is for showing the canvas
(located on content canvas) and the other is to hide it (located also on the content canvas)

The Toolbar Canvas

 Special type of canvas for tool items
 Two types:
 Vertical toolbar
 Horizontal toolbar
 Provide:
 Standard look and feel
 Alternative to menu or function key operation
 Creating Tool bar Canvas:
 In the object navigator select the canvases icon then click on create button
 Change the type to your suggested type.
 Change the attributes you want from the property platter

The Tab Canvas

 Enables you to organize and display related information on separate tabs
 Creating Tab Canvas:
 In layout editor, click on the Tab canvas button then drag the mouse to place the
tab canvas in the place you want
 If you want to view tab canvas in layout editor, go to View  Stacked View, and
select/unselect the canvases you want them to be shown/hidden.
 NOTE: [Control]+ Click to clear the selection in menu.

Run-Time Messages and Alert Overview

 Forms display messages at run time to inform the operator of events that occur in the
session

ISC 321 – Database Systems I P a g e | 72

Creating Alerts
 Click on the alters icon in the object navigator, and then click on the create button
 In the Property Platter you can define the following properties
 Name
 Title
 Alert style
 Button 1, Button 2, Button3 Labels
 Default Alert Button
 Message

Controlling Alerts at Run Time

 Use Function Show_alert to call your alert
 Example: create an exit_form button and an alert that asks the user if he really wants to
exit the form or not, define three buttons, and add the calling of the alert to the exit_form
button
Declare
num number;
Begin
num := show_alert('ALERT56');
if num = alert_button1 then exit_form; end if;
End;

Navigate Between Forms

 Use forms built in procedures to navigate between forms
 CALL_FORM(„form_path\form_name‟);
 OPEN_FORM(„form_path\form_name);

ISC 321 – Database Systems I P a g e | 73

Lab #11 – Introduction to PL/SQL Programming

1. Laboratory Objective:
The objective of this laboratory is to introduce students to the basic of PL/SQL programming

2. Laboratory Learning Outcomes:

 After completing this lesson, you should be able to do the following:
 PL/SQL blocks
 PL/SQL procedures and functions
 Triggers

3. Introductory Concepts (for this laboratory)

PL/SQL
 PL/SQL is an Oracle Procedural Language extension to SQL
 The following can be constructed with the PL/SQL language:
 Anonymous block
 Stored or standalone procedure and function
 Package
 Trigger
 Use PL/SQL to add business logic to a database application

PL/SQL Block Structure

DECLARE
/* Declarative section – PL/SQL variables */
BEGINE
/* Execution section – SQL statements go here … only section that is REQUIRED */
EXEPTION
/* Exception handling section – error-handling statements go here */
END;

PL/SQL Declaring Variables

 Syntax:
DECLAE
Variable_name type [CONSTANT] [NOT NULL] [:=value];
 Variables can have any SQL datatype, such as VARCHAR2, DATE, or NUMBER, or a
PL/SQL-only datatype, such as a BOOLEAN or PLS_INTEGER.
 Declaring a constant is similar to declaring a variable except that you must add the
CONSTANT keyword and immediately assign a value to the constant.
 Variable_name consists of a letter optionally followed by more letters, numerals, dollar
signs, underscores, and number signs
Variable_name table.column%TYPE;
 Creates a variable that has the same type as the column type

ISC 321 – Database Systems I P a g e | 74

Example in Declaring Variables in PL/SQL
DECLARE -- declare the variables in this section
v_last_name VARCHAR2(30);
v_first_name VARCHAR2(25);
n_employee_id NUMBER(6);
b_active_employee BOOLEAN;
n_monthly_salary NUMBER(6);
number_of_days_worked NUMBER(2);
pay_per_day NUMBER(6,2);
-- a constant variable
avg_days_worked_month CONSTANT NUMBER(2) := 21;
BEGIN
NULL; -- NULL statement does nothing, allows this block to executed and tested
END;

To Show Output
 Set the serverout variable on:
Syntax:
SQL> SET serveroutput ON;
 Use DBMS_OUT.PUT_LINE command:
Syntax:
SQL> DBMS_OUTPUT.PUT_LINE (variable_name);

Example in PL/SQL With Output

DECLARE
text1 varchar2(50);
text2 varchar2(50) := 'var2 initially initialized --> ';
var1 number;
var2 number := 5;
BEGIN
text1 := 'var1 just initialized --> ';
var1 := 10;
DBMS_OUTPUT.PUT_LINE ( '... ' || text1 || var1);
DBMS_OUTPUT.PUT_LINE ( '... ' || text2 || var2);
END;

Interacting With Tables

DECLARE
var1 number;
BEGIN
SELECT number_seats
INTO var1
FROM rooms
WHERE room_id = 99999;
DBMS_OUTPUT.PUT_LINE ( ' Number of seats for room number 99999 is ' || var1);
END;

ISC 321 – Database Systems I P a g e | 75

PL/SQL Control Structures
 Conditional Control With IF-THEN
 The forms of the statement can be IF-THEN, IF-THEN-ELSE, or IF-THEN-
ELSEIF-ELSE.
 The IF clause checks a condition
 The THEN clause defines what to do if the condition is true
 The ELSE clause defines what to do if the condition is false or null.
 Iterative Control With LOOPs.

IF-THEN-ELSE: Syntax
IF boolean_expresion1 THEN
sequence_of_statemnts1;
[ELSEIF boolean_expreasion2 THEN
sequence_of_statemnets2;]
…
[ELSE
sequence_of_statements3;]
END IF;

IF-THEN-ELSE: Example
DECLARE
v_NumberSeats rooms.number_seats%TYPE;
v_Comment VARCHAR2(35);
BEGIN
SELECT number_seats
INTO v_NumberSeats
FROM rooms
WHERE room_id = &input;
IF v_NumberSeats <= 50 THEN
v_Comment := 'Fairly small';
ELSIF v_NumberSeats <= 100 THEN
v_Comment := 'A little bigger';
ELSE
v_Comment := 'Lots of room';
END IF;
DBMS_OUTPUT.PUT_LINE (v_Comment);
END;
/

While Loop
 Syntax:
WHILE condition LOOP
sequence_of_statements;
END LOOP;
 Example:
V_counter := 0;

ISC 321 – Database Systems I P a g e | 76

 WHILE v_counter < 6 LOOP
v_counter := v_counter +1;
END LOOP;

For Loop
 Syntax:
FOR loop_counter IN [REVERSE] low_bound .. High_bound LOOP
sequence_of_statements;
END LOOP;
 Example 1:
FOR v_counter2 IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(v_counter2);
END LOOP;
 Example 2:
FOR v_counter2 IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(v_counter2);
END LOOP;

Procedures
CREATE [OR REPLACE] PROCEDURE procedure_name (parameter1 mode type,
parameter2 mode type, ….) AS
/*Declarative section is here*/
BEGIN
/*Executable section is here*/
EXCEPTION
/* Exception section is here*/
END [procedure name];

Procedures: Example
create or replace procedure update_room_capacity(
r_room_id in rooms.room_id%TYPE,
r_factor in NUMBER)
AS
r_room_count INTEGER;
BEGIN
select count(*)
into r_room_count
from rooms
where room_id=r_room_id;

Calling a Procedure
 insert into rooms
values(111,'Building 7',100,100);
 call update_room_capacity(111,1.5);
Call completed.
 select * from rooms;

ISC 321 – Database Systems I P a g e | 77

Procedure
 Dropping a procedure
DROP procedure update_room_capacity;
 Show errors in a procedure
Show errors

Functions
CREATE OR REPLACE FUNCTION function_name (parameter1 mode type,
parameter2 mode type, ….)
RETURN return_type AS
/*Declarative section is here*/
BEGIN
/*Executable section is here*/
EXCEPTION
/* Exception section is here*/
END [function name];

Functions: Example
create function circle_area (
radius in number
) return number as
v_pi number := 3.1416;
v_area number;
begin
v_area := v_pi * radius * radius;
return v_area;
end circle_area;
/

Functions
 Calling a function
SQL> select circle_area(2)
2 from dual;
CIRCLE_AREA(2)
--------------
12.5664
 Dropping a function
SQL> drop function circle_area;
Function dropped.

Trigger
 Is a procedure that is run (or fired) automatically by the database when a specified DML
statement is run against a certain database table.
 Is useful for doing things like advanced auditing of changes made to column values in a
table.

ISC 321 – Database Systems I P a g e | 78

Trigger: Syntax
CREATE OR REPLACE TRIGGER trigger_name
{BEFORE|AFTER} trigger_event {INSERT | DELETE | UPDATE}ON table_reference
[FOR EACH ROW[WHEN trigger_condition]]
DECLARE
/*Declarative section is here*/
BEGIN
/*Executable section is here*/
EXCEPTION
/* Exception section is here*/
END;

ISC 321 – Database Systems I P a g e | 79

Lab #12 – Applying PL/SQL On Database

1. Laboratory Objective:
The objective of this laboratory is to introduce students to how to apply PL/SQL on Database

2. Laboratory Learning Outcomes:

After completing this lesson, you should be able to do the following:
 Applying PL/SQL On Database

3. Introductory Concepts (for this laboratory)

Passing Parameters – Between Forms

 In the Caller Form:
1. Declare parameter list ( variable paramlist)
2. Create Parameter list using built in function (create_parameter_list(„name‟);)
3. Add parameter value(s) using build in procedure (add_parameter(parameter
list,„name',text_parameter;value); )
4. Call the form using call_form built in and pass the parameter list to it.
5. Destroy the parameter list using built in procedure (destroy_parameter_list(„name‟); )
 In the Called Form:
1. Create a parameter that has the same name as used in add_parameter
2. Assign appropriate variable to this parameter using the prefix (:PARAMETER.name)

 Example:
Pass the student ID as a parameter from the main form you have to the other and display
its information in the next form
 In the main form (Caller Form):
declare
param_test paramlist;
begin
param_test:= create_parameter_list('test');
add_parameter(param_test,'val1',text_parameter,:student.sid);
call_form(C:\Review2',no_hide,no_replace,no_query_only, 'test');
destroy_parameter_list('test');
end;

 In the called form:

 Create a parameter that has name: “val1”
 Create a two triggers on the form level:
 pre-query that has the following code:
:REGISTERED_STUDENTS.STUDENT_ID := :PARAMETER.VAL1;
 when new-form-instance that has the following code: (
execute_query;

ISC 321 – Database Systems I P a g e | 80

IMPORTANT HINTS:
- If you want to click on a button to show a LOV, you can use show_LOV built in
function, it returns a Boolean.
- Some useful procedure:
- Delete_record
- Next_record
- Previous_record

ISC 321 – Database Systems I P a g e | 81

Appendix A: Rules to fallow by Computer Lab Users

 The loud conversations / discussion that disturbing the other users is prohibited.
 Audio CDs or applications with audio output may only be used with headphones with minimum
volume that it should not be disturb other users.
 All cell phones are to be turned off or set to silent while in the lab. If you receive a phone call, you
should exit the lab before answering your cell phone.
 Do not bring food or beverages inside the lab.
 Any file saved on the computer hard drive will be deleted without notice. Students should save their
work onto an external storage device such as USB drive or CD.
 Changing hardware and software configurations in the computer labs is prohibited. This includes
modifications of the settings, modification of system software, unplugging equipment, etc.
 Open labs are reserved for academic use only. Use of lab computers for other purposes, such as
personal email, non-academic printing, instant messaging, playing games, and listening to music is
not permitted.
 Please leave the computer bench ready for the next patron. Leave the monitor on the login screen, and
do not forget to take goods related to you. While leaving computer bench please push the chair inside
the computer bench.
 Users are responsible for their own personal belongings and equipment. Do not leave anything in the
Computer Lab unattended for any length of time. The Computer Labs staffs are not responsible for
lost or stolen items.
 Users are not allowed to clear paper jams in the printer by themselves.
 Operate the lab equipments with care.
 After using white-board the user must clean for other user.

Thanks for your cooperation.

Information Science Department

ISC 321 – Database Systems I P a g e | 82

Appendix B: Endorsement

LABORARTORY MANUAL FOR COURSE

ISC 321 (Database Systems I)

# Instructor name Remarks Signature Date

1

ISC 321 – Database Systems I P a g e | 83