Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Tuesday, 11 April 2017

SQL Table Drop

0 comments

It is used to remove the table permanently from database along with data


Syntax:

Drop table <Table_Name>;


Example:

        DROP TABLE Student;
        DROP TABLE dept;
        DROP TABLE emp;
Continue reading >>

SQL Table Describe/Desc

0 comments

Used to retrieve structure of table
It is an NON-SQL statement valid in oracle environment


Syntax:

Describe  <Table_Name>;
Desc  <Table_Name>;


Example:

        Describe student;

        Desc emp;
Continue reading >>

SQL Table Insert

0 comments

This will be used to insert the records into table.We have two methods to insert.
  • Insert Values
  • Insert values by using address

INSERT VALUES


Syntax:
          insert into <table_name) values (value1, value2, value3 …. Valuen);

   
Example:

            SQL> INSERT INTO student VALUES (1, ’sudha’, 100);
            SQL> INSERT INTO student VALUES (2, ’saketh’, 200);
         
To insert a new record again you have to type entire insert command, if there are lot  of  records this will be difficult.

This will be avoided by using address method.

INSERT VALUES BY USING ADDRESS 

   
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.
   
Example:
            SQL>  INSERT INTO student VALUES (&no, '&name', &marks);


Enter value for no: 1
Enter value for name: Jagan
Enter value for marks: 300
old   1: insert into student values(&no, '&name', &marks)
new   1: insert into student values(1, 'Jagan', 300)

SQL> /
Enter value for no: 2
Enter value for name: Naren
Enter value for marks: 400
old   1: insert into student values(&no, '&name', &marks)
new   1: insert into student values(2, 'Naren', 400)


INSERT VALUES INTO SPECIFIED COLUMNS

   Syntax:

                insert into <table_name)(col1, col2, col3 … Coln) values (value1, value2, value3 ….Valuen);


Example:
            SQL> INSERT INTO student (no, name) VALUES (3, ’Ramesh’);
            SQL> INSERT INTO student (no, name) VALUES (4, ’Madhu’);

INSERT VALUES INTO SPECIFIED COLUMNS BY USING ADDRESS


Syntax:
          insert into <table_name)(col1, col2, col3 … coln) values (&col1, &col2, &col3 …. &coln);

     This will prompt you for the values but for every insert you have to use forward
slash.


Example:
            SQL> INSERT INTO student (no, name) VALUES (&no, '&name');

Enter value for no: 5
Enter value for name: Visu
old   1:  insert into student (no, name) values(&no, '&name')
new   1:  insert into student (no, name) values(5, 'Visu')

SQL> /
Enter value for no: 6
Enter value for name: Rattu
old   1:  insert into student (no, name) values(&no, '&name')
new   1:  insert into student (no, name) values(6, 'Rattu')


INSERT VALUES INTO TABLE BY USING SELECT STATEMENT


   Syntax:

     INSERT INTO <Table_name1> SELECT * FROM <Table_name2>
                            
   Example:

     INSERT INTO Emp_bk SELECT * FROM emp;

INSERT VALUES INTO TABLE BY USING SELECT STATEMENT INTO SPECIFIED COLUMNS

  
   Syntax:
   
   INSERT INTO <Table_name1> (Column1, Column2, Column3, Column3)            SELECT Column1, Column2, Column3, Column4 from <Table_name2>
                   
  Example:

  INSERT INTO Emp_bk (EMPNO,ENAME,SAL,DEPTNO)  select 
    EMPNO,ENAME,SAL,DEPTNO  from emp
  

Continue reading >>

SQL Update Table

0 comments


This can be used to modify the table data.

Syntax:
     Update <table_name> set <col1> = value1, <col2> = value2 where <condition>;

Example:

     SQL> UPDATE student SET marks = 500;


     If you are not specifying any condition this will update entire table.

     SQL> UPDATE student SET marks = 500 WHERE no = 2;
     SQL> UPDATE student SET marks = 500, name = 'Venu'  WHERE no = 1;




Continue reading >>

SQL Table Data Delete

0 comments

This can be used to delete the table data temporarily.

Syntax:

    Delete <table_name> where <condition>;

Example:

     SQL> DELETE student;


     If you are not specifying any condition this will delete entire table.

     SQL> DELETE Student  WHERE no = 2;
Continue reading >>

SQL operators

0 comments


Arithmetic Operators
Relational Operators
Logical Operators
Special Operators
+
=
And
In ,Not In
-
! = or  <>
Or
Between,Not Between
*
> 
Not
Like ,Not Like
/
< 

Is null , Is Not Null

>=



<=




Continue reading >>

SQL Select Statement

0 comments


It is used to retrieve the data for read only purpose

Syntax:

    Select * from <table_name>;              -- here * indicates all columns
     or
    Select col1, col2, … coln from <table_name>;

Examples:

    SQL> SELECT * FROM student;
  
        NO NAME            MARKS
        ---  ------             --------
         1   Sudha             100
         2   Saketh            200
         1   Jagan             300
         2   Naren             400
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

    SQL> SELECT no, name, marks FROM student;

        NO NAME            MARKS
        ---  ------             --------
         1   Sudha             100
         2   Saketh            200
         1   Jagan             300
         2   Naren             400
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

    SQL> SELECT no, name FROM student;

        NO NAME
        ---  -------
         1   Sudha
         2   Saketh
         1   Jagan
         2   Naren
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

We have two clauses used in this

  • Where
  • Order by


SELECT STATEMENT WITH WHERE CLAUSE


Syntax:
     select * from <table_name> where <condition>;

     The following are the different types of operators used in where clause.

Arithmetic operators
Relational operators
Logical operators
Special operators
+
=
And
In,Not In
-
! = or  <>
Or
Between, Not Between
*
> 
Not
Like, Not Like
/
< 

Is null,Is not null

>=



<=






SELECT STATEMENT WITH RELATIONAL OPERATORS

  
     Example:

        SQL> SELECTFROM student WHERE no = 2;


        NO NAME            MARKS
        ---  -------           ---------
         2   Saketh            200
         2   Naren             400
       
        SQL> SELECT * FROM student WHERE no < 2;

        NO NAME            MARKS
        ---  -------           ----------
         1   Sudha             100
         1   Jagan             300

        SQL> SELECT * FROM student WHERE no > 2;

         NO NAME            MARKS
         ---  -------           ----------
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

         SQL> SELECT * FROM student WHERE no <= 2;

         NO NAME            MARKS
         ---  -------           ----------
         1   Sudha             100
         2   Saketh            200
         1   Jagan             300
         2   Naren             400
     
         SQL> SELECT * FROM student WHERE no >= 2;

         NO NAME            MARKS
         ---  -------           ---------
         2   Saketh            200
         2   Naren             400
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

         SQL> SELECT * FROM student WHERE no != 2;

         NO NAME            MARKS
         ---  -------           ----------
         1   Sudha             100
         1   Jagan             300
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

         SQL> SELECT * FROM student WHERE no <> 2;

         NO NAME            MARKS
         ---  -------           ----------
         1   Sudha             100
         1   Jagan             300
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu


SELECT STATEMENT WITH AND

   
     This will gives the output when all the conditions become true.
   
     Syntax:
          select * from <table_name> where <condition1> and <condition2> and .. <conditionn>;
   
     Ex:

         SQL> SELECT * FROM student WHERE no = 2 AND marks >= 200;


                              NO NAME            MARKS
         ---  -------           --------
         2   Saketh            200
         2   Naren             400


SELECT STATEMENT WITH OR


     This will gives the output when either of the conditions become true.

     Syntax:
         select * from <table_name> where <condition1> and <condition2> or .. <conditionn>;

     Ex:
         SQL> SELECT * FROM student WHERE no = 2 OR marks >= 200;

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


SELECT STATEMENT WITH BETWEEN


     This will gives the output based on the column and its lower bound, upperbound.

     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

SELECT STATEMENT WITH NOT BETWEEN


     This will gives the output based on the column which values are not in its lower bound, upperbound.

     Syntax:
     select * from <table_name> where <col> not between <lower bound> and <upper bound>;

     Ex:
         SQL> SELECT * FROM student WHERE marks NOT BETWEEN 200 AND 400;

         NO NAME            MARKS
         ---  -------           ---------
         1   Sudha             100

SELECT STATEMENT WITH 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);

     Ex:
         SQL> SELECT * FROM student WHERE no IN (1, 2, 3);

         NO NAME            MARKS
         --- -------            ---------
         1   Sudha             100
         2   Saketh            200
         1   Jagan             300
         2   Naren             400
         3   Ramesh


SELECT STATEMENT WITH 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);


         NO NAME            MARKS
         ---  -------           ---------
         4   Madhu
         5   Visu
         6   Rattu


SELECT STATEMENT WITH 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;

         NO NAME            MARKS
         ---  -------           ---------
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu


SELECT STATEMENT WITH 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;
         NO NAME            MARKS
         ---  -------           ---------
         1   Sudha             100
         2   Saketh            200
         1   Jagan             300
         2   Naren             400


SELECT STATEMENT WITH 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;

         NO NAME            MARKS
         ---  -------           ---------
         1   Sudha             100
      
        ii) This will give the rows whose name start with ‘S’.

             SQL> SELECT * FROM student WHERE name LIKE 'S%';

         NO NAME            MARKS
         ---  -------           ---------
         1   Sudha             100
         2   Saketh            200

        iii) This will give the rows whose name ends with ‘h’.

              SQL> SELECT * FROM student WHERE name LIKE '%h';
       
         NO NAME            MARKS
         ---  -------           ---------
         2   Saketh            200
         3   Ramesh

        iV) This will give the rows whose name’s second letter start with ‘a’.

               SQL> SELECT * FROM student WHERE name LIKE '_a%';

          NO NAME            MARKS
          ---  -------            --------
          2   Saketh            200
          1   Jagan             300
          2   Naren             400
          3   Ramesh
          4   Madhu
          6   Rattu

        V) This will give the rows whose name’s third letter start with ‘d’.

              SQL> SELECT * FROM student WHERE name LIKE '__d%';

         NO NAME            MARKS
         ---  -------           ---------
         1   Sudha             100
         4   Madhu

        Vi) This will give the rows whose name’s second letter start with ‘t’ from ending.

               SQL> SELECT * FROM student WHERE name LIKE '%_t%';

         NO NAME            MARKS
         ---  -------           ---------
         2   Saketh            200
         6   Rattu
       
        Vii) This will give the rows whose name’s third letter start with ‘e’ from ending.

                SQL> SELECT * FROM student WHERE name LIKE '%e__%';

         NO NAME            MARKS
         ---  -------           ---------
         2   Saketh            200
         3   Ramesh

        Viii) This will give the rows whose name  cotains 2 a’s.

                    SQL> SELECT * FROM student WHERE name LIKE '%a% a %';

         NO NAME            MARKS
          --- -------           ----------
         1   Jagan             300

* You have to specify the patterns in like using underscore ( _ ).


SELECT STATEMENT WITH 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.
If you want output in descending order you have to use desc keyword after the column.

Ex:
        SQL> SELECT * FROM student ORDER BY no

        NO NAME            MARKS
        ---  -------           ---------
         1   Sudha             100
         1   Jagan              300
         2   Saketh            200
         2   Naren             400
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

        SQL> SELECT * FROM student ORDER BY no DESC;

        NO NAME            MARKS
        ---  -------           ---------
         6 Rattu
         5 Visu
         4 Madhu
         3 Ramesh
         2 Saketh            200
         2 Naren             400
         1 Sudha             100
         1 Jagan             300
Continue reading >>