8.1 Write SQL*Plus commands to do the following:
Make sure that the Autocommit is off;
List the names of all tables in this schema;
8.2 Assume that the following table is created and stored in your database:
Staff (staffNo, name, post, salary, sex, DOB)
Write SQL statements to do the following:
(a) Increment the salary of managers by 5%;
(b) Remove the records of all ‘salesmen’ from the Staff table;
(c) List the Staff table tablespace name, pctfree, and pctused.
Guide IV
8.3 What is a sequence? Write an SQL statement to create a sequence that starts from 10 and is
incremented by 10 up to a maximum value of 10000. The sequence should continue to
generate values after reaching its maximum value.
8.4 (a)
create a tablespace, say MyTableSpace, with default storage. You can use a datafile
(b) Explain what is a DUAL table, where is it stored, and what is it useful for? Give an
example of its use.
8.5 (a) What is a named block in PL/SQL and how many types does PL/SQL support?
(b) Specify the general structure of an anonymous block.
(c) Assume that the following tables are part of a Company database schema
Guide IV
(d) Invoke the procedure from SQL*Plus.
8.6 Assuming that a single-row form is created on the following Country table:
State the steps you would take to execute the following query using the form: List all the
African countries whose currency name is the pound and population greater than 20 millions.
Order the result by country name.
Run the form. Press the icon Enter Query or select Enter from the Query menu;
(a) Press OK button. Scroll through the result using the arrow key.
8.7 Oracle database consists of logical and physical database structures. Describe each of the
following concepts and state to which structure they belong:
(a) Schema;
(b) Data block;
(c) Redo log file.
8.8 State the three primary uses of Oracle Data Dictionary.
The primary use of Oracle Data Dictionary are:
8.9 (a) SQL*Plus environment variables are set to default values when SQL*Plus is started.
State three ways by which users can change the default setting.
Users can change the default setting in three ways (use SET command):
(b) Write SQL*Plus commands to do the following:
Guide IV
(c) Write SQL statements to do the following:
(1) List the name and granted roles of the current user;
(2) List name and type of all objects owned by the current user;
(3) List table name and the tablespace name to which the table is assigned of all
tables owned by the current user;
(4) List the next value of the sequence Emp_Seq.
(d) Given the following schema of a database table:
Guide IV
Orders Total Values
8.10 (a) In PL/SQL what is a Cursor? When do we use an explicit Cursor? What do you do
when you declare a Cursor?
(b) Specify the general structure of a named function block.
(c) Assume that the following tables are part of a Library database system
Guide IV
CREATE OR REPLACE PROCEDURE Glasgow_Members
IS
(d) Invoke the procedure from SQL*Plus.
(e) Assume that a single-row form is created on the Member table and you are using it to
enter data in the table. State the steps you would take to create a trigger that will fire
and insert the next card number in the member record when the record is saved.
Specify the type of trigger you will use and write the trigger code. Assume that a
sequence generator already exists.
8.11 (a) What is a SQL*Plus script? Why is it a good practice to create a log file while a
SQL*Plus script is executed, and how can the log file be created?
Guide IV
(b) below
does.
(c) Assume the following tables:
Order(orderNo
Guide IV
statusCode_int order.statusCode%TYPE:= UPPER(statusCode_in);