Basic SQLPlus edit and execute commands SQLPlus buffer

Basic SQL*Plus edit and execute commands • SQL*Plus buffer and built-in editor • holds the last SQL statement • Statements are created in free-flow style and are numbered • Example: select Custnumb, Balance from CUSTOMER • The semicolon indicates the end of the SQL statement and causes execution • A builtin editor is used to change the statements in the buffer after it has been created • Append Credlim to the current listing • Change the SELECT statement to return Custname rather than Custnumb

Basic SQL*Plus edit and execute commands contd. • Insert a new line ORDER BY SLSRNUMB after the current line • List the current statement in the buffer • Formatting columns

Basic SQL*Plus edit and execute commands contd. • The SQL statement in the buffer can be saved to a file • A file may contain any number of SQL statements • Comments: • --for a single line of text • /* for multi-line texts */ • Editing and running a file

Database tables • Table: columns, rows • Valid Oracle object names • must be unique • <= 30 characters • not a reserved word • must start with a letter • can contain letters, digits, some special characters (_, $, #) • Data types for columns: • VARCAHR 2 • variable length character string up to 2000 characters • must specify length-- Example: slname VARCHAR 2 (30) • CHAR • fixed length character string up to 255 characters • should not be used if exact length is not known • NUMBER: integer, fixed point, floating point numbers • INTEGER: whole numbers • Example: sid number(5) • fixed-point numbers: price NUMBER (5, 2) • floating point numbers: gpa NUMBER

Database tables cont’d • Data types cont’d • DATE: no length specification needed. Various format masks can be used • LONG: used for very large character data. Do not use • RAW/LONG RAW: used for binary data--digitized image. • Integrity constraints • Primary key: sid NUMBER (5) CONSTRAINT student_pk PRIMARY KEY • Foreign key: locid NUMBER (5) CONSTRAINT faculty_fk REFERENCES location(locid) • Value constraints • check that gpa is between 0 and 4 • gpa NUMBER (5, 2) CONSTRAINT gpa_cc CHECK gpa between 0 and 4 • Creating/modifying/dropping tables: • CREATE TABLE <tablename> (<fieldname> <data type> [CONSTRAINT <constraint definition>], . . ) • Example: CREATE TABLE location (locid number (5) CONSTRAINT location_pk PRIMARY KEY. . . ); • DROP TABLE <tablename> • Example: DROP TABLE location;

Database tables cont’d • Viewing table structure • field definition: describe <tablename> • Example: • Constraint definition • user_constraints table • Modifying tables • changing a columns size, data type, default value • adding/deleting PK, FK constraints • deleting column/changing column name--not allowed • adding a new column

Database tables cont’d • Modifying table definitions • Adding column/constraints • ALTER TABLE <tablename> ADD (<column definition>/ <constraint definition>) • Modifying existing table • ALTER TABLE <tablename> MODIFY (<new column def>/ <new constraint def>) • Dropping constraints • ALTER TABLE <tablename> DROP CONSTRINT <constraint name>