Changing the Password
ALTER USER <yourName> IDENTIFIED BY <newPassword>;
Creating a Table
CREATE TABLE test ( i int, s char(10) );
Creating a Table With a Primary Key
CREATE TABLE <tableName> (..., a <type> PRIMARY KEY, b, ...);
INSERT INTO test VALUES(10, 'foobar');
Getting the Value of a Relation
SELECT * FROM test;
Dropping a Table
DROP TABLE test;
Getting Information About the Specific Database
The system keeps information about your own database in certain system tables. The most important is USER_TABLES. You can recall the names of your tables by issuing the query:
SELECT TABLE_NAME FROM USER_TABLES;
More information about your 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:
to learn about the attributes of relation <tableName>.
Executing SQL From a File
Instead of executing SQL commands typed at a terminal, it is often more convenient to type the SQL command(s) into a file and cause the file to be executed.
To run the file foo.sql, type:
Editing Commands in the Buffer
If you end a command without a semicolon, but with an empty new line, the command goes into a buffer. You may execute the command in the buffer by either the command RUN or a single slash (/).
You may also edit the command in the buffer before you execute it.
|LIST||lists the command buffer, and makes the last line in the buffer the "current" line|
|LIST n||prints line n of the command buffer, and makes line n the current line|
|LIST m n||prints lines m through n, and makes line n the current line|
|INPUT||enters a mode that allows you to input text following the current line; you must terminate the sequence of new lines with a pair of "returns"|
|CHANGE /old/new||replaces the text "old" by "new" in the current line|
|APPEND text||appends "text" to the end of the current line|
|DEL||deletes the current line|
Recording a Session
There are several methods for creating a typescript to turn in for your programming assignments. The most primitive way is to cut and paste your terminal output and save it in a file (if you have windowing capabilities). Another method is to use the Unix command script to record the terminal interaction. The script command records everything printed on your screen. The syntax for the command is
script [ -a ] [ filename ]
The record is written to filename. If no file name is given, the record is saved in the file typescript. The -a option allows you to append the session record to filename, rather than overwrite it. To end the recording, type
sqlplus provides the command spool to save query results to a file. At the SQL> prompt, you say:
and a file called foo.lst will appear in your current directory and will record all user input and system output, until you exit sqlplus or type:
Note that if the file foo.lst existed previously, it will be overwritten, not appended.