SQL*Plus hints

---

Online Help

• help command name
• help commands - list of commands

Creating a table

create table tableName(
  field type (length) constraints,
  field2 type (length) constraints,
);

Basic Field Types

• char (n) Character fixed length at n
• varchar (n) Character max length n
• date
• number integer or real number up to 40 spaces
• number (n) number up to n spaces
• number (n, d) number, n spaces and d decimal points
• integer number with no decimal points
• integer (n) fixed length integer

Column restraints

• primary key - the field is required and not null
• not null - the field must be filled in
• check (condition) require condition to be true
• references tableName Foreign key reference to table

Viewing a table

describe tableName;

select * from tableName;

Adding records

INSERT INTO tableName VALUES(1, 'text', 45);

• Values must be of correct types and in correct order
• Varchar data goes in single quotes

Changing records

UPDATE tableName
SET column = value
WHERE condition;

Importing a script

Deleting a table definition

DROP tableName;

Selecting data

• SELECT * FROM table;
• SELECT column1, column2 FROM table;
• SELECT * FROM table WHERE condition;
• SELECT * FROM table WHERE condition ORDER BY Column;

Conditions

• String literals in 'single quotes'
• =, <, >, <=, >= for numeric comparison
• fieldname LIKE 'A%' - fieldname begins with "A"
• fieldname LIKE '%A%' - fieldname contains an "A"
• fieldname BETWEEN 1 and 10
• fieldname IN ('Apples', 'Oranges')
• IN subquery
• EXISTS subquery

Operators

• +, -, *, /
• || (string concatenation)
• to_char, to_number, to_date

Date Functions

• sysDate returns current date and time
• next_day (date, day_of_week) returns next date with same day of week
• add_months (date, count) returns a date count months after date
• months_between(date1, date2) returns monthgs
• Adding and subtracting result is in days
• to_date (string, format) converts a string to a date
• to_string (date, format) converts a date to a string

Aggregate functions

• sum
• count
• avg
• max
• min

Views

  CREATE VIEW viewName AS select statement

Report formatting

• COLUMN colName heading heading format format
  EG

      Column ID heading "Instructor | ID" format 999
      column LName heading "Last Name" format A10
      

• ttitle
  Presents a top title for a report
  
• spool fileName
  copies all results to the file
• spool off
  turns off spooling