Loading

Friday, October 28, 2011

ORACLE SQL TUTORIAL: INTRODUCTION





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

CREATE TABLE SYNTAX

Create table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen);
Ex:
     SQL> create table student (no number (2), name varchar (10), marks number (3));

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);
           
     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.

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);

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)

c) INSERTING DATA INTO SPECIFIED COLUMNS USING VALUE METHOD
    
     Syntax:
           insert into <table_name)(col1, col2, col3 … Coln) values (value1, value2, value3 ….
                                                              Valuen);
     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, &col3 …. &coln);
     This will prompt you for the values but for every insert you have to use forward slash.
     
     Ex:
          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')

SELECTING DATA

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

Ex:
    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




1 Responses to “ORACLE SQL TUTORIAL: INTRODUCTION”

Sridevi Koduru said...
July 7, 2016 at 1:02 AM

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.