Oracle Database Basic Command

** Credit Web

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)
Commit
Application:	Commit data to a database table.
Save inserted data	Commit
Constraints
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 REFERENCES tablename(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 REFERENCES table(column name)
Example	ALTER TABLE cd
	ADD CONSTRAINT cd_cd_id_fk REFERENCES cd(cd_id);
Create
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
Delete
Application:	Delete a record from a table.
Delete record	DELETE FROM tablename WHERE search condition
Example:	DELETE FROM project WHERE projid = 21
Drop
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
Insert
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)
Modify
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))
Rename
Application:	Renames a table.
Rename table	RENAME oldtablename TO new tablename
Example:	RENAME projects99 TO projects2000
Various Select Statements
SELECT Statement	SELECT column1, column2... FROM table
Example	SELECT project_name FROM project
Column Alias	Creates an alias for the database column.
	SELECT column name "alias name" FROM table
Example	SELECT name "Our Team" FROM project
Order By	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
COUNT	Returns an integer representing the number of counted rows.
Example	SELECT count(*) FROM projects
AVG	Returns the average value of a numberic column's returned values.
Example	SELECT avg(project_cost) FROM project
SUM	Sums a numeric column;'s returned values.
Example	SELECT sum(project_count) FROM project
MIN	Returns the minimum value of a numeric column's returned values.
Example	SELECT min(project_cost) FROM project
MAX	Returns the maximum value of a numeric column's return values.
Example	SELECT max(project_cost) FROM project
Distinct	Supresses duplicate values.
Example	SELECT DISTINCT project_date FROM project
Math Operators (+,-,*,/)	Performs basic calculations in a select statement
Example	SELECT total+5 FROM project
Logical Operators (AND, OR, NOT)	Allows you to apply multiple search criteria.
Example	SELECT project_name FROM projects WHERE project_date > '02-FEB-00' AND project_cost < 150.00
LIKE or =	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
Commands	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
Formatting Columns	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
Describe	Diplays table column names and datatypes.
	DESCR tablename
Example	DESCR project