Let

1
Lets try Oracle
2
Accessing Oracle

• The Oracle system, like the SQL Server system, is
  client / server.
• For SQL Server,
• the client is the Query Analyser application
  installed in all machines in the COMP domain
  labs.
• The server is on CIAN.STUDENT.COMP.DIT.IE
• For Oracle,
• The client is either
• The SQLPLUS application installed on all
  machines in the COMP domain labs.
• iSQLPlus, which can be accessed using a Web
  Browser while attached to the COMP domain.
• The Server is on FERDIA.STUDENT.COMP.DIT.IE

3
Starting with Oracle

• SQL Plus

4
Today in the lab

• Connect to SQL Plus your schema.
• Set up a table.
• Find the table in the catalog.
• Insert four rows into the table.
• Run various different select statements on the
  table.

5
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, and
  populate, update the table.

6
Find the application

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

7
This is an example

• Dont forget to get a username and password for
  your SCHEMA.
• The Host string defines the DATABASE.
• The host string is oracledb

8
An example from Oracle 10g
9
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!
• Create the same book table that you did on SQL
  Server.

10
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!!.

11
SQL differences you will see

• SQL or Structured 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
• Every query ends with a semi-colon
• The functions in Oracle differ from those in SQL
  Server.
• The datatype VARCHAR in SQL Server is replaced by
  the datatype VARCHAR2 in Oracle.
• Numeric data types are represented by NUMBER(L,P)
  where L is the full length, in digits, of the
  number and P is the number of those digits that
  are after the decimal point. E.g. NUMBER(6,2) can
  take up to 9999.99.

12
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.

13
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

14
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.

15
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.

16
To list columns

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

17
Format of the CREATE

• CREATE TABLE tablename
• (
• column_name datatype columnconstraint,
• tableconstraint
• )

18
Example

• CREATE TABLE book (
• ISBN VARCHAR2(13),
• Title VARCHAR2(40),
• Price NUMBER(7,2),
• NumberInStock NUMBER(3),
• Genre VARCHAR2(10),
• DatePublished DATE
• )

19
Dropping a table

• When you have created a table, you have set up a
  definition in the database of that table.
• You cannot create the table again, while the
  first definition is still there.
• To remove the table, use the DROP command.
• DROP table BOOK

20
A better CREATE

• To improve on our previous CREATE statement
• Define one of the fields as a key
• Allow null values to be entered against the
  number of books in stock.
• Dont allow null values to be entered against the
  Title or ISBN.
• Check that the Price is less that 100.
• Put in a default value of Drama for Genre.

21
New CREATE

• CREATE TABLE book (
• ISBN VARCHAR2(13) NOT NULL,
• Title VARCHAR2(40) NOT NULL,
• Price NUMBER(7,2) CHECK (price lt 100.00),
• NumberInStock NUMBER(3) NULL,
• Genre VARCHAR2(10) DEFAULT Drama,
• DatePublished DATE,
• PRIMARY KEY (ISBN)
• )

22
Inserting data into a table

• This is done using the INSERT statement.
• The INSERT has two formats
• Full insert
• INSERT into BOOK values(1-56592-744-3,SQL in a
  Nutshell,0,23.99,Technical,01/01/01)
• Or partial insert
• INSERT into BOOK(ISBN, Title,DatePublished)
  values(0-07-135953-2,Fundamentals of SQL
  Programming,01/01/00)

23
Exercise

• Create the book table, as you did in SQL Server.
• Note that some of the syntax differs.
• Insert four rows into the book table, ensuring
  that the data types and constraints are
  respected.
• Check the contents of the table
• Select from tablename
• Note that the semicolon ends the statement.

24
To look at the table contents..

• Use the SELECT instruction
• select from book
• Literally means show everything from the book
  table

25
Exercises

• Add the BOOK table to your database.
• Try to insert the following rows
• INSERT INTO BOOK values (
• '0-13-085033-0',
• 'Programming Oracle Triggers and Stored
  Procedures',
• 49.99,
• 40,
• 'Computing',
• '01/01/2004')
• INSERT INTO BOOK values (
• '0-201-11803-0',
• 'Visual Quickstart Guide SQL',
• 29.50,
• 10,
• 'Computing',
• '01/01/2002')
• Find out what the errors are, and why they
  happened.

26
To add some more records

• Oracle\ADDBOOKS.SQL