Oracle

1
Oracle

• Labs ECS 242, 342, 360
• You can connect from home to the machines in the
  lab.
• E.g. ssh u-knoppix.csc.uvic.ca
• Execute sh to use the proper shell.
• source /opt/oracle/etc/oraenv
• sqlplus
• Provide user name and password
• oracle username is identical to the UNIX
  username.initial oracle password is the student
  number prefixed by st
• To change the password use passw

2
Creating tables

• create table Movie(
• title char(20),
• year int,
• length int,
• inColor char(1),
• studioName char(20),
• producerC int,
• primary key (title, year)
• )

3
Creating tables II

• create table MovieExec(
• name char(20),
• address char(30),
• cert int primary key,
• netWorth int
• )
• create table MovieStar(
• name char(20),
• address char(30),
• gender char(1),
• birthdate char(20)
• )

4
Inserting Tuples

• INSERT INTO Movie(title, year, length, inColor,
  studioName, producerC)
• VALUES('Godzilla', 1998, 120, 'C', 'Paramount',
  123)
• INSERT INTO Movie(title, year, length, inColor,
  studioName, producerC)
• VALUES('Pretty Woman', 1990, 120, 'C', 'Disney',
  234)
• INSERT INTO Movie(title, year, length, inColor,
  studioName, producerC)
• VALUES('Titanic', 1998, 340, 'C', 'Paramount',
  123)
• INSERT INTO Movie(title, year, length, inColor,
  studioName, producerC)
• VALUES('Star Wars', 1977, 124, 'C', 'Fox', 500)
• INSERT INTO Movie(title, year, length, inColor,
  studioName, producerC)
• VALUES('Mighty Ducks', 1991, 104, 'C',
  'Paramount', 123)
• INSERT INTO Movie(title, year, length, inColor,
  studioName, producerC)
• VALUES('Gone with the wind', 1972, 104, 'B',
  'Paramount', 300)
• INSERT INTO Movie(title, year, length, inColor,
  studioName, producerC)

5
Inserting Tuples II

• INSERT INTO MovieExec(name, address, cert,
  netWorth)
• VALUES('Smith', '123 Billings Rd.', 500, 100000)
• INSERT INTO MovieExec(name, address, cert,
  netWorth)
• VALUES('Chris', '13 St. Marc Street', 123,
  200000)
• INSERT INTO MovieExec(name, address, cert,
  netWorth)
• VALUES('Bill', '300 Broadway Rd.', 600, 100000)
• INSERT INTO MovieExec(name, address, cert,
  netWorth)
• VALUES('Brown', '123 Billings Rd.', 234, 300000)
• INSERT INTO MovieExec(name, address, cert,
  netWorth)
• VALUES('Melanie Griffith', '34 Boston Blvd', 700,
  3000000)
• INSERT INTO MovieExec(name, address, cert,
  netWorth)
• VALUES('Melanie Griffith', '34 Boston Blvd', 700,
  3000000)
• INSERT INTO MovieExec(name, address, cert,
  netWorth)

These two tuples wont be allowed to get
inserted. Why?
6
Inserting Tuples III

• INSERT INTO MovieStar(name, address, gender,
  birthdate)
• VALUES('Julia Roberts', '123 Billings Rd.', 'F',
  '23-Feb-1963')
• INSERT INTO MovieStar(name, address, gender,
  birthdate)
• VALUES('Alec Baldwin', '12 Temple Street', 'M',
  '2-Aug-1960')
• INSERT INTO MovieStar(name, address, gender,
  birthdate)
• VALUES('Kim Basinger', '12 Temple Street', 'F',
  '12-Jul-1970')
• INSERT INTO MovieStar(name, address, gender,
  birthdate)
• VALUES('Robert de Niro', '34 Cambridge Blvd',
  'M', '3-Jan-1950')
• INSERT INTO MovieStar(name, address, gender,
  birthdate)
• VALUES('Melanie Griffith', '34 Boston Blvd', 'F',
  '3-Jan-1950')

7
Inserting Tuples IV

• INSERT INTO Studio (studioname, presc)
• VALUES ('Disney', 1)
• INSERT INTO Studio (studioname, presc)
• VALUES ('Paramount', 2)

8
Getting all the tuples of a relation

• E.g.
• SELECT
• FROM movie

9
Dropping Tables

• DROP TABLE Movie
• DROP TABLE MovieExec
• DROP TABLE MovieStar

10
Executing Operating System Commands

• E.g.
• !ls

11
Executing SQL scripts

• E.g.
• _at_createmovie.sql

12
Getting Information About Your Database

• The system keeps information about your own
  database in certain system tables. The most
  important for now is USER_TABLES.
• You can recall the names of your tables by
  issuing the query
• SELECT TABLE_NAME FROM USER_TABLES
• More information about tables is available from
  USER_TABLES. To see all the attributes of
  USER_TABLES, try
• SELECT FROM USER_TABLES
• It is also possible to list the attributes of a
  table once you know its name. Issue the command
• DESCRIBE to learn about the
  attributes of relation .

13
Quitting sqlplus

• To leave sqlplus, type quit in response to the
  SQL prompt.