Sunday 31 March 2013

Most Useful Commands in DATABASE


Most Useful Commands in DATABASE

SQL is divided into the following:
Ø  Data Definition Language (DDL)
Ø  Data Manipulation Language (DML)
Ø  Data Retrieval Language (DRL)
Ø  Transaction Control Language (TCL)
Ø  Data Control Language (DCL)

DDL -- create, alter, drop, truncate, rename
DML -- insert, update, delete
DRL -- select
TCL -- commit, rollback, savepoint
DCL -- grant, revoke

1) Create Table
SyntaxCreate table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen);
Example: CREATE TABLE SAILOR (SID NUMBER (10), SNAME VARCHAR2 (10));

 2) INSERT
This will be used to insert the records into table.
We have two methods to insert.
Ø  By value method
Ø  By address method

a) USING VALUE METHOD
     Syntax:
          Insert into <table_name) values (value1, value2, value3 …. Valuen);
     Ex:
            SQL> insert into student values (1, ’sudha’, 100);
            SQL> insert into student values (2, ’saketh’, 200);

b) USING ADDRESS METHOD
     Syntax:
          insert into <table_name) values (&col1, &col2, &col3 …. &coln);
      This will prompt you for the values but for every insert you have to use forward slash.
      Ex:
            SQL> insert into student values (&no, '&name', &marks);

c) INSERTING DATA INTO SPECIFIED COLUMNS USING VALUE METHOD
     Syntax:
           insert into <table_name)(col1, col2, col3 … Coln) values (value1, value2, value3 ….);
     Ex:
            SQL> insert into student (no, name) values (3, ’Ramesh’);
            SQL> insert into student (no, name) values (4, ’Madhu’);
d) INSERTING DATA INTO SPECIFIED COLUMNS USING ADDRESS METHOD
     Syntax:
          insert into <table_name)(col1, col2, col3 … coln) values (&col1, &col2 ….&coln);
Ex:
            SQL> insert into student (no, name) values (&no, '&name');

3) SELECTING DATA:
Syntax:
    Select * from <table_name>;                     -- here * indicates all columns
or
    Select col1, col2, … coln from <table_name>;
Ex:
    SQL> select * from student;

4) USING WHERE:
Syntax:
     Select * from <table_name> where <condition>;
Ex: SQL> select * from student where no < 2; (display the list of students)

5) USING AND:
     This will give the output when all the conditions become true.
    Syntax:
            Select * from <table_name> where <condition1> and <condition2> ;
    Ex:
         SQL> select * from student where no = 2 and marks >= 200;
                               NO NAME            MARKS
         ---  -------           --------
         2   Gates            200
         2   Buffet            400                           
  
6) USING OR:
 This will gives the output when either of the conditions become true.
Syntax:
         select * from <table_name> where <condition1> or<condition2>;
   Ex:
         SQL> select * from student where no = 2 or marks >= 200;

         NO NAME            MARKS
         ---  -------           ---------
         2   Saketh            200
         1   Jagan             300
         2   Naren             400

7) USING BETWEEN:
              This will gives the output based on the column and its lower bound, upper bound.
      Syntax:
         select * from <table_name> where <col> between <lower bound> and <upper bound>;
       Ex:
            SQL> select * from student where marks between 200 and 400;
NO NAME            MARKS
---  -------           ---------
2   Saketh            200
1   Jagan              300
2   Naren              400

8) USING NOT BETWEEN:
     This will gives the output based on the column which values are not in its lower bound,
     Upper bound.
    Syntax:
          select * from <table_name> where <col> not between <lower bound> and <upper bound>;

9) USING IN:
This will gives the output based on the column and its list of values specified.
Syntax:
    select * from <table_name> where <col> in ( value1, value2, value3 … valuen);

10) USING NOT IN:
 This will gives the output  based on the column which values are not in the list of
      values  specified.
  Syntax:
         select * from <table_name> where <col> not in ( value1, value2, value3 … valuen);

     Ex:
         SQL> select * from student where no not in (1, 2, 3);

11) USING NULL:
  This will gives the output based on the null values in the specified column.
     Syntax:
         select * from <table_name> where <col> is null;
     Ex:
         SQL> select * from student where marks is null;

12) USING NOT NULL:
    This will gives the output based on the not null values in the specified column.
     Syntax:
         select * from <table_name> where <col> is not null;
     Ex:          
         SQL> select * from student where marks is not null;
13) USING LIKE:
   This will be used to search through the rows of database column based on the pattern 
    You specify.
     Syntax:
        select * from <table_name> where <col> like <pattern>;
      Ex:         
       i) This will give the rows whose marks are 100.
            SQL> select * from student where marks like 100;

14) USING ORDER BY:
This will be used to ordering the columns data (ascending or descending).
Syntax:
        Select * from <table_name> order by <col> desc;
By default oracle will use ascending order.
Ex:
        SQL> select * from student order by no; (If you want output in descending order you have to use desc keyword after the column.)

15) USING UPDATE:
This can be used to modify the table data.
Syntax:
     Update <table_name> set <col1> = value1, <col2> = value2 where <condition>;
Ex:
     SQL> update student set marks = 500;
     If you are not specified any condition this will update entire table.

16) USING DELETE:
This can be used to delete the table data temporarily.
Syntax:
    Delete <table_name> where <condition>;
Ex:
     SQL> delete student; (If you are not specifying any condition this will delete entire table)

17) USING ALTER:
This can be used to add or remove columns and to modify the precision of the datatype.
Syntax:
        alter table <table_name> add <col datatype>;
    Ex:
        SQL> alter table student add sdob date;

18) USING TRUNCATE:
This can be used to delete the entire table data permanently.
Syntax:
      truncate table <table_name>;
Ex:
     SQL> truncate table student;

19) USING DROP:
This will be used to drop the database object;
Syntax:
     Drop table <table_name>;
Ex:
     SQL> drop table student;

20) USING RENAME:
This will be used to rename the database object;
Syntax:
     rename <old_table_name> to <new_table_name>;
Ex:
     SQL> rename student to stud;

21) USING GRANT:
This is used to grant the privileges to other users.
Syntax:
     Grant <privileges> on <object_name> to <user_name> [with grant option];
Ex:
     SQL> grant select on student to sudha;     -- you can give individual privilege

22) USING REVOKE:
This is used to revoke the privileges from the users to which you granted the privileges.
Syntax:
     Revoke <privileges> on <object_name> from <user_name>;
23) COLUMN ALIASES:
Syntax:
     Select <orginal_col> <alias_name> from <table_name>;
Ex:
     SQL> select no sno from student;

 24) USING CHECK:
This is used to insert the values based on specified condition.
We can add this constraint in all three levels.
Ex:
     COLUMN LEVEL
     SQL> create table student(no number(2) , name varchar(10), marks number(3) check (marks > 300));

25) UNIQUE:
This is used to avoid duplicates but it allows null value.
Ex:
      SQL> create table student(no number(2) unique, name varchar(10), marks
             number(3));
26) PRIMARY KEY:
Ø  This is used to avoid duplicates and nulls. This will work as combination of unique and not null.
Ø  Primary key always attached to the parent table   
Ex:
      SQL> create table student(no number(2) primary key, name varchar(10), marks
             number(3));

27) FOREIGN KEY:
Ø  This is used to reference the parent table primary key column which allows duplicates.
Ø  Foreign key always attached to the child table.
Ex:
     SQL> create table emp(empno number(2), ename varchar(10), deptno number(2), primary key(empno), foreign key(deptno) references dept(deptno));

28) USING ON DELTE CASCADE:
By using this clause you can remove the parent record even it child’s exists.
Because whenever you remove parent record oracle automatically removes all its dependent records from child table, if this clause is present while creating foreign key constraint.
Ex:
     SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),primary key(empno), foreign key(deptno) references dept(deptno) on delete cascade);

29) GROUP BY:
Using group by, we can create groups of related information.
Columns used in select must be used with group by; otherwise it was not a group by expression.
Ex:
     SQL> select deptno, sum(sal) from emp group by deptno;

30) HAVING:
This will work as where clause which can be used only with group by because of absence of where clause in group by.
Ex:
     SQL> select deptno,job,sum(sal) tsal from emp group by deptno,job having sum(sal) > 3000;

31) UNION:
This will combine the records of multiple tables having the same structure.
Ex:
     SQL> select * from student1 union select * from student2;

32) UNION ALL:
This will combine the records of multiple tables having the same structure but including duplicates.
Ex:
     SQL> select * from student1 union all select * from student2;

33) INTERSECT:
This will give the common records of multiple tables having the same structure.
Ex:
     SQL> select * from student1 intersect select * from student2;

34) MINUS:
This will give the records of a table whose records are not in other tables having the same structure.
Ex:
     SQL> select * from student1 minus select * from student2;

35) EQUI JOIN:
A join which contains an ‘=’ operator in the joins condition.
Ex:
     SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;

36) USING CLAUSE:
SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno);

37) NON-EQUI JOIN :
A join which contains an operator other than ‘=’ in the joins condition.
Ex:
     SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno >
             d.deptno;
38) SELF JOIN:
Joining the table itself is called self-join.
Ex:
     SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where
             e1.empno=e2.mgr;

39) NATURAL JOIN:
Natural join compares all the common columns.
Ex:
     SQL> select empno,ename,job,dname,loc from emp natural join dept;

40) EXISTS:
Exists function is a test for existence. This is a logical test for the return of rows from a query.
Ex:
     Suppose we want to display the department numbers which has more than 4 
     Employees.
     SQL> select deptno,count(*) from emp group by deptno having count(*) > 4


1 comment:

  1. Very greаt р&X6f;st. I simply s&X74;umbled &X75;pon your web&X6C;οg аnd wan&X74;ed to say thаt I have rеally enјoyеԁ
    ѕurfing around your weblo&X67; pos&X74;s.
    Ιn any &X63;ase I'&X6c;l bе
    &X73;ubscrіbing to youг rss fee&X64; a&X6E;d I'm hopіng you write again ver&X79; soοn!


    Review my blog :: online wholesale clothing

    ReplyDelete