Basic SQL commands

ORACLE COMMANDS:

CREATING USERS AND GIVING PERMISSIONS/PRIVILEGES:

•       Create user <username> identified by <password>;
•       Grant dba to <username> identified by <password>;
•       Select * from dba_users;

CREATING, INSERTING, DELETING, UPDATE AND DROPPING A TABLES:

•         Create table <tablename>(att/column_name datatypes);
•         Inserting into <tablename> values(‘&attname’);//& is mandatory
•         Update <tablename> set <column name>=<value>;
•         Alter table <tablename> add/drop(primary key(column name));
•         Delete from <table-name> <where condition>;
•         Drop table <tablename>;

Aggregate Function:

•         Avg
•         Max
•         Min
•         Abs
•         Pow
•         Sqrt
•         Ceil
•         Floor
•         Round
•         sum

Column/Domain CONSTRAINTS:

•         Not Null
•         Unique
•         Primary key
•         Check
•         Foreign key

Key Constraints:

•         Composite key
•         Super key
•         Candidate key
•         Foreign key
•         Primary key

Rename to table:

•         Rename <old-table> to <new-table>;

To Save:

•         Commit

To undo/unchanges:

•         Roll back

Delete complete/entire Rows in the table:

•         Truncate  table <table name>

CLAUSE’s:

    ORDER BY:

•    Ascending Order
•    Descending Order

Syntax/Usage in SQL Prompt:

       Select <column-name/attribute> from <table-name> order by <column-name>=asc/des;

  GROUP BY:

                  It will group the duplicate row in the table and display it in the screen as single row

Syntax/Usage in SQL Prompt:

       Select <column-name> from <table-name> group by <column-name>;

 HAVING :

Syntax/Usage in SQL Prompt:

    Select <column-name> from <table-name> group by <column-name> having <arithmetic-condition>

Joins:

•         Condition joins
•         Equi-join
•         Natural-join

Creating Views:

    Create view <view-name> as select <column-name> from <table-name>;