1 / 13

Oracle

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

zlata
Download Presentation

Oracle

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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) 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);

  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) VALUES('Melanie Griffith', '34 Boston Blvd', 700, 3000000); These two tuples won’t 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. @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 <tableName>;to learn about the attributes of relation <tableName>.

  13. Quitting sqlplus • To leave sqlplus, type quit; in response to the SQL> prompt.

More Related