Tech Tips and Tricks: Oracle on Windows 10

Wednesday, July 6, 2016
Some Basic Oracle DDL and DCL Commands

DDL COMMANDS:
    ALTER:
    This can be used to add (or) remove columns and to modify the precision of data type.

    syntax:
    alter table <table name> add <col... datatype>

    eg.
    alter table student add sdob date;
    alter table student drop sdob date;

    To change the precision:
    alter table student modify id number(5);

    Make a column unused:
        alter table student set unused column marks 

    Rename column:
        Alter table student rename column name to sname;

    Using Truncate:
            This can be used to delete table data permanently.
            Syntax: truncate table <table name>
                    truncate table student;
        
    Drop:
        this will be used to drop the database object.
        syntax:
            drop table <table name>
            drop table student;

    Rename:
        it will be used to rename the database object.
        syntax:
            rename table <table name> to <table name>
            rename table student to stud;


    Commit:
        this will be used to save th work.
        Two types of commit:
            a. implicit commit
            b. explicit commit
        
        
        Explicit commit:
            this will be issued by the user.
            syntax: commit or commit work;
            
    Rollback:
        this will undo the operation
        it will be issued in two methods
        1. upto previous commit 
        2. upto previous rollback
        
        syntax:
            roll or roll work 
            or 
            rollback or rollback work 
            
    Savepoint:
                you can use savepoint to rollback portions of your current set of transactions.
                syntax:
                    savepoint <save point name>
                    eg. savepoint s1;
                        insert into student values (1, 'brook', 90);
                        savepoint s2;
                        insert into student values (2, 'DJ', 80);
                        savepoint s3;
                        rollback s2;
USING DCL:
    DCL commands are used to grant and revoke the permissions.
    
    Using Grant:
        This will be used to grant the privileges  to users.
        Syntax:
        Grant <privileges> on <object name> to <user name>;
        grant select on student to seema;     
        [most of the time grant and revoke will be done by DBA.]
        
        or, 
        grant all on student to seema; [we have given all the previliges to seema]
        
        grant all on student to seema with grant option;
            if you want to allow  seema to give grant permissions to other;
            
    
    revoke select on student from seema;
    
    revoke all on student from seema;
    
    
    
Multiinsert with all fields:
        insert all 
            into student values (3, 'Rabi', 93)
            into student values (4, 'Hari', 96)
            into student values (5, 'Anup', 90)
            
            select * from dept where deptno = 10;
            
            
select greatest (1, 2, 3), greatest(-1, -2, -3) from dual; [dual is a table inside the system, and there is nothing in the table]


initcap: make the first letter capitalized.

CONSTRAINTS (RESTRICTIONS):
    Constraints are categorized as follows:
    1. domain integrity constraint
        a. Notnull constraint 
        b. check 
        
    2. Entity integrity constraint
        a. unique
        b. primary key 
        
    3. Referencial integrity constraint
        a. foreign key constraint
    
    
    - Constraints are always attached to the column not to the table.
    - We can add constraints in three ways
            a. column level: always with the column definition
            b. table level: after the table definition
            c. alter level: using alter command 