Docstoc

oracle

Document Sample
oracle Powered By Docstoc
					                          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);

INSERT INTO Movie(title, year, length, inColor, studioName, producerC)
VALUES('Wyane', 1973, 134, 'B', 'Disney', 234);

INSERT INTO Movie(title, year, length, inColor, studioName, producerC)
VALUES('%AAAA%', 1973, 134, 'B', 'Disney', 234);

INSERT INTO Movie(title, year, length, inColor, studioName, producerC)
VALUES('Wayne''s world', 1972, 134, 'B', 'Disney', 234);
                     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);   These two tuples
                                                              won’t be allowed
INSERT INTO MovieExec(name, address, cert, netWorth)
VALUES('Melanie Griffith', '34 Boston Blvd', 700, 3000000);
                                                               to get inserted.
                                                                    Why?
INSERT INTO MovieExec(name, address, cert, netWorth)
VALUES('Melanie Griffith', '34 Boston Blvd', 700, 3000000);
                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');
            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 list 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.