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); 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); These two tuples won’t be allowed to get inserted. Why?
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.
