Alter
| |
Application:
|
Altering a users password, making changes to a table or column.
| |
Change Password:
|
ALTER user username IDENTIFIED BY newpassword
| |
Example
|
ALTER USER tony IDENTIFIED BY cereal
| |
|
| |
Adding a column:
|
ALTER TABLE tablename ADD column name datatype
| |
Example:
|
ALTER TABLE project ADD names varchar2(25)
| |
|
| |
|
| |
Application:
|
Commit data to a database table.
| |
Save inserted data
|
Commit
| |
|
| |
|
| |
Application:
|
Allows for the creation of column constraints such as Primary and Foreign keys.
| |
Create Primary Key
|
CONSTRAINT constraint name_pk PRIMARY KEY
| |
Example
|
CONSTRAINT cd_cd_id_pk PRIMARY KEY (cd_id)
| |
|
{NOTE: this is the format when using at the end of you column declarations in a CREATE Table statement)
| |
|
| |
Create a Foreign Key
|
CONSTRAINT constraint name_fk REFERENCEStablename(primary key)
| |
Example
|
CONSTRAINT cd_cd_id_fk REFERENCES cd(cd_id)
| |
|
{NOTE: this is the format when using at the end of you column declarations in a CREATE Table statement)
| |
|
| |
ADD a PRIMARY Key Constraint (after table created)
|
ALTER TABLE tablename
| |
|
ADD CONSTRAINT constraint name PRIMARY KEY (column name)
| |
Example
|
ALTER TABLE cd
| |
|
ADD CONSTRAINT cd_cd_id_pk PRIMARY KEY(cd_id);
| |
|
| |
Add a FOREIGN KEY Constraint (after table created)
|
ALTER TABLE tablename
| |
|
ADD CONSTRAINT constraint name REFERENCEStable(column name)
| |
Example
|
ALTER TABLE cd
| |
|
ADD CONSTRAINT cd_cd_id_fk REFERENCES cd(cd_id);
| |
|
| |
|
| |
|
| |
Application:
|
Creates a table,view, index, sequence, user etc. in the database.
| |
Create Table
|
CREATE TABLE tablename (data definition)
| |
Example:
|
CREATE TABLE project ( name varchar2(25), activity varchar2(30))
| |
|
| |
Create View
|
CREATE VIEW viewname (column names)AS SELECT select statement
| |
Example:
|
CREATE VIEW report (Names, Addresses) AS SELECT Select name, address FROM book
| |
| |
|
| |
Application:
|
Delete a record from a table.
| |
Delete record
|
DELETE FROM tablename WHERE search condition
| |
Example:
|
DELETE FROM project WHERE projid = 21
| |
| |
|
| |
Application:
|
Drop a table or view from a database.
| |
Drop Table
|
DROP TABLE tablename
| |
Example
|
DROP TABLE projects
| |
|
| |
Drop View
|
DROP VIEW view name
| |
Example
|
DROP VIEW lowscores
| |
|
| |
|
| |
Application:
|
Insert records into a table.
| |
Insert record
|
INSERT INTO table (columns1, column2....) VALUES (value1, value2...)
| |
Example
|
INSERT INTO project (name, activity) VALUES (building, construction)
| |
| |
|
| |
Application:
|
Modify a column in a table.
| |
Modify a column in a table
|
ALTER TABLE tablename MODIFY column name new data definition
| |
Example:
|
ALTER project MODIFY (name varchar2(50))
| |
| |
|
| |
Application:
|
Renames a table.
| |
Rename table
|
RENAME oldtablename TO new tablename
| |
Example:
|
RENAME projects99 TO projects2000
| |
| |
Various Select Statements
| |
|
|
SELECT column1, column2... FROM table
| |
Example
|
SELECT project_name FROM project
| |
| |
|
|
|
Creates an alias for the database column.
| |
|
SELECT column name "alias name" FROM table
| |
Example
|
SELECT name "Our Team" FROM project
| |
|
| |
| |
|
|
|
SELECT column FROM table ORDER BY column name
| |
Example
|
SELECT name FROM project ORDER BY project_num
| |
Order By (Ascending order)
|
SELECT name FROM project ORDER BY project_num ASC
| |
Order By (Descending order)
|
SELECT name FROM project ORDER BY project_num DESC
| |
| |
|
|
|
Returns an integer representing the number of counted rows.
| |
Example
|
SELECT count(*) FROM projects
| |
| |
|
|
|
Returns the average value of a numberic column's returned values.
| |
Example
|
SELECT avg(project_cost) FROM project
| |
| |
|
|
|
Sums a numeric column;'s returned values.
| |
Example
|
SELECT sum(project_count) FROM project
| |
| |
|
|
|
Returns the minimum value of a numeric column's returned values.
| |
Example
|
SELECT min(project_cost) FROM project
| |
| |
|
|
|
Returns the maximum value of a numeric column's return values.
| |
Example
|
SELECT max(project_cost) FROM project
| |
| |
|
|
|
Supresses duplicate values.
| |
Example
|
SELECT DISTINCT project_date FROM project
| |
| |
|
|
|
Performs basic calculations in a select statement
| |
Example
|
SELECT total+5 FROM project
| |
| |
|
|
|
Allows you to apply multiple search criteria.
| |
Example
|
SELECT project_name FROM projects WHERE project_date > '02-FEB-00' AND project_cost < 150.00
| |
| |
|
|
|
These are applied to your search condition in the WHERE clause.
| |
Example LIKE
|
Use when applying wildcards on VARCHAR2 datatypes.
| |
|
SELECT name FROM project WHERE name LIKE 'JONES%'
| |
| |
|
Example =
|
Use when knowing the exact search parameter in your WHERE cluase.
| |
|
SELECT cost FROM project WHERE cost = 150.00
| |
| |
SQL Plus
| |
|
|
Most of the commands listed here also require a FULL path name to carry out the desired task.
| |
Save FILENAME
|
Saves a file.
| |
Get FILENAME
|
Opens a file.
| |
Start FILENAME
|
Executes a file.
| |
@FILENAME
|
Executes a file.
| |
Edit FILENAME
|
Opens a file for editing in Notepad.
| |
Spool FILENAME
|
Spools out to a file.
| |
Exit
|
Exits out of SQL Plus
| |
Disc
|
Disconnects from the Database.
| |
Connect
|
Connects to the database: CONNECT username/password@instance
| |
| |
|
|
|
Apply these techniques to alter the way your data is displayed in a columnar format.
| |
Character and Date Columns
|
An, sets the display width of n
| |
Examples
|
| |
Create column headings
|
COLUMN name HEADING ‘Customer|Name’ FORMAT A70
| |
|
COLUMN bact_balance JUSTIFY LEFT FORMAT $999,999,990.00
| |
|
COLUMN inv_date FORMAT A9 NULL ‘No Invoice’
| |
Display the current settings
|
COLUMN name
| |
Clear Settings
|
COLUMN name CLEAR
| |
| |
|
|
|
Diplays table column names and datatypes.
| |
|
DESCR tablename
| |
Example
|
DESCR project
| |