Transcript Oracle - University of Victoria

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
Creating tables
create table Movie(
title char(20),
year int,
length int,
inColor char(1),
studioName char(20),
producerC int,
primary key (title, year)
);
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)
);
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);
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('Melany Griffin', '34 Boston Blvd', 700, 3000000);
INSERT INTO MovieExec(name, address, cert, netWorth)
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('Melany Griffin', '34 Boston Blvd', 'F', '3-Jan-1950');
Inserting Tuples IV
INSERT INTO Studio (studioname, presc)
VALUES ('Disney', 1);
INSERT INTO Studio (studioname, presc)
VALUES ('Paramount', 2);
Getting all the tuples of a relation
E.g.
Select *
from movie;
Dropping Tables
drop table Movie;
drop table MovieExec;
drop table MovieStar;
Executing Operating System
Commands
E.g.
!ls
Executing SQL scripts
E.g.
@createmovie.sql
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 recall the attributes of a table once you know its name.
Issue the command:
– DESCRIBE <tableName>;to learn about the attributes of relation
<tableName>.
Quitting sqlplus
• To leave sqlplus, type quit; in response to the SQL> prompt.