Starting with Oracle

1
Starting with Oracle

• SQL Plus

2
Today in the lab

• Connect to SQL Plus your schema.
• Set up two tables.
• Find the tables in the catalog.
• Insert four rows into the base table.
• Insert two rows into the detail table.
• Alter the structure of the detail table, to
  constrain its contents.
• If this doesnt work, why?
• What can you do to make it work?

3
Find your feet

• Oracle SQL Plus
• Are you in the correct lab?
• Introduce yourselves to Oracle
• Each person has an individual schema (R,W,U,D)
• In your INDIVIDUAL schema, use the Data
  Definition Language commands to create, populate,
  update and alter 2 joined tables.

4
Find the application

• Through the Start menu, find
• Programs
• Oracle client
• Application Development
• SQL Plus (icon opposite)

5
This is an example

• Dont forget to get a username and password for
  your SCHEMA.
• The Host string defines the DATABASE. This one
  wont work for you!

6
An example from Oracle 10g
7
About the interface

• The interface is VERY basic. It is similar to a
  very old model of command line interface.
• Each line is transacted immediately.
• It takes a bit of getting used to!
• Using the examples on the following slides,
  create two tables.

8
Example
9
About the interface

• To exit the application, type in EXIT
• To save your work
• It is messy trying to save in this environment.
• The best thing to do is to open Notepad as well
  as SQL Plus and copy from one to the other.
• Note that the full path name needs to be entered,
  so keep path names SHORT!!.

10
SQL

• SQL or Standard Query Language is used widely to
  access databases.
• Implemented SQL (Oracle, MS SQL Server, Access)
  varies a little from the standard, but is
  reasonably standard
• It consists of a data definition language - DDL
  (Create, Alter, Drop)
• And a Data Manipulation Language - DML (Select,
  Union, Update, Delete, Insert Into, Select Into,
  Transform, Parameter)

11
The CREATE command

• Creates tables in your schema.
• The basic Create statement
• CREATE TABLE table_name
• (
• column_name data_type
• )
• optional, one or more, lower case user
  supplied names, upper case reserved words,
  either or, () are part of the syntax.
• See www.ss64.com/orasyntax/datatypes.html for a
  comprehensive list and description of data types.

12
Adding constraints

• To make one the attributes a primary key, put a
  CONSTRAINT on the table, by adding
• PRIMARY KEY (column_name).

13
Constraints

• Constraints can be imposed when the table is
  being created, in the create statement.
• Constraints can be on the column or the table.
• Column constraints can be listed after the column
  data type e.g. NOT NULL, UNIQUE, PRIMARY KEY.
• Note Primary Key doesnt need unique.
• Other constraints can be added after all columns
  have been declared, by ALTERing the table.
• These can include
• primary keys, including compound primary keys, or
  
• foreign keys.

14
Exercise

• Set up two tables.
• Product with columns ProductCode 4 digits,
  ProductName 15 characters, ProductPrice Decimal
  4 digits with two after the decimal place. The
  product code is the primary key.
• Supplier with columns SupplierId 4 digits, SName
  15 characters. SupplierId is the primary key.
• Find the tables in the catalog.
• To find tables in the catalog, you manipulate the
  SQLPlus buffer.
• Select from cat
• See end of previous example.

15
Data Manipulation Language

• Data Manipulation language (DML) allows you to
  manipulate the contents of the tables in you
  schema, rather than the definition.
• DML includes
• Insert
• Delete
• Update
• Select

16
Insert statement

• This adds data to the table.
• Either a full row can be added, or values can be
  put in individual columns.
• NOTE If only some of the columns are given
  values, the remainder of the columns have an
  undefined status of NULL.
• If a column is constrained by the NOT NULL
  constraint, every insert to the owning table must
  provide a value for that column, unless a DEFAULT
  value is provided for the table.

17
Example insert (full version)
18
Exercise

• Insert four rows into the supplier table,
  ensuring that the data types and constraints are
  respected.
• Insert two rows into the product table, as above.
• Check the contents of both tables by
• Select from tablename
• Note that the semicolon ends the statement.

19
Altering tables

• Alter is part of the data definition language.
• Alter allows a column to be added or dropped from
  the table.
• ALTER TABLE customer ADD COLUMN server NUMBER(7)
  DEFAULT 1234567
• Alter allows a constraint to be added or removed
  from a table.
• ALTER TABLE customer ADD CONSTRAINT servedby
  FOREIGN KEY server REFERENCES staff(staffId)

20
Exercise

• Use the Alter command to
• Add a new column called supplier of type 4
  digits, to the product table, with a default
  value of 1234.
• Add a constraint to the product table, making the
  new supplier field a foreign key, referencing
  the SupplierId in the Supplier table.

21
Lessons?

• If this doesnt work, why not?
• What can you do to make it work?

22
(No Transcript)
23
Manipulating the buffer

• To terminate an entry, use or, on a new line,
  use /
• Append text or a text adds text to the end of a
  line.
• Change /old/new or C /old/new changes old to
  new in a line.
• Change /text or C /text deletes text from a
  line.
• Clear buffer or cl buff deletes all lines.
• Del deletes a line
• Get file loads the contents of a fine named file
  into the buffer.
• Input or i add one or more lines
• Input text adds a line consisting of text.
• List or l lists all lines in buffer
• List n or l n or n lists one line and makes it
  the current line.
• List or l lists the current line
• List last or l last lists the last line
• List m n or l m n lists lines m through n
• Save file or sav file saves the contents of the
  buffer to a file named file.

24
To list columns

• select table_name, column_name from
  user_tab_columns
• To describe a table
• Desc lttablenamegt

25
Errors

• ERROR at line 1
• ORA-02291 integrity constraint (BUILDER.REL658)
  violated - parent key not
• found
• What does this mean?