Oracle 10 G

Only available on StudyMode
  • Download(s) : 95
  • Published : April 24, 2013
Open Document
Text Preview
ORACLE 10g Lab Guide
A supplement to: Database Systems: Design, Implementation and Management (International Edition) Rob, Coronel & Crockett (ISBN: 9781844807321)

ORACLE 10g Lab Guide

Table of Contents

Lab 1 2 3 4 5 6 7 8 9 10

Title Introduction to ORACLE 10g interfaces Creating a small database from a script file Data Manipulation Commands Basic SELECT statements Advanced SELECT statements Joining Tables SQL functions Set operators Subqueries Views

Page 4 21 37 48 64 77 94 114 121 133

Keeley Crockett

2

ORACLE 10g Lab Guide

Introduction to the ORACLE 10g Lab Guide
This lab guide is designed to provide examples and exercises in the fundamentals of SQL within the ORACLE 10g environment. The objective is not to develop full blown applications but to illustrate the concepts of SQL using simple examples. The lab guide has been divided up into 10 sessions. Each one comprises of examples, tasks and exercises about a particular concept in SQL and how it is implemented in ORACLE 10g. On completion of this 10 week lab guide you will be able to: • • • • • • • • Create a simple relational database in ORACLE 10g Insert, update and delete data the tables Create queries using basic and advanced SELECT statements Perform join operations on relational tables Apply set operators Use aggregate functions in SQL Write subqueries Create views of the database

This lab guide assumes that you know how to perform basic operations in the Microsoft Windows environment. Therefore, you should know what a folder is, how to maximize or minimize a folder, how to create a folder, how to select a file, how you maximize and minimize windows, what clicking and double-clicking indicate, how you create a folder, how you drag, how to use drag and drop, how you save a file, and so on.

Keeley Crockett

3

ORACLE 10g Lab Guide The lab guide has been designed on ORACLE 10g version 10.2.0.1.0 running on Windows XP Professional. Before starting this guide, you must log on to your ORACLE RDBMS, using a user ID and a password created by your database administrator. How you connect to the ORACLE database depends on how the ORACLE software was installed on your server and on the access paths and methods defined and managed by the database administrator. Follow the instructions provided by your instructor, College or University.

Keeley Crockett

4

ORACLE 10g Lab Guide

Lab 1: The ORACLE 10g DBMS interfaces

The learning objectives of this lab are to: • • • Learn how to use two standard ORACLE 10g interfaces to SQL Learn the basic command line SQL editing commands Load and run database scripts in the two interfaces

1.1 Introduction The ORACLE 10g DBMS has a number of interfaces for executing SQL queries. The most basic interface, known as the ORACLE SQL *Plus interface, is used to directly execute SQL commands such as those you will have learnt about in Chapter 8, Introduction to Structured Query Language. An example of the ORACLE SQL *Plus interface can be seen in Figure 1.

Keeley Crockett

5

ORACLE 10g Lab Guide

Figure 1: The ORACLE SQL *Plus interface In Figure 1, the following SQL query has been entered at the command line: SELECT P_CODE, P_DESCRIPT, P_INDATE, P_SALECODE FROM PRODUCT; Notice that a semi-colon (;) is needed at the end of the SQL query. This ends the SQL statement and when the enter key is pressed the query is executed. The results are displayed immediately below the query.

ORACLE 10g also has a web based interface known as iSQL *Plus. This interface has its own command language in addition to being able to execute any SQL statement. The

Keeley Crockett

6

ORACLE 10g Lab Guide main benefit of this interface is that it allows online editing of SQL statements to take place easily. You can also do some simple formatting of the query output. Figure 2 shows the ORACLE 10g iSQL *Plus interface.

Figure 2: The ORACLE iSQL *Plus interface Which interface you use to do these lab...
tracking img