SQL Tutorial-- Basics

SQL refers to "Structured Query Language"

·         It is an Structured Query Language because it follows ANSI, ISO standards.

·         It is an editor which helps the user to communicate with the Oracle Server.

·         User communicates with the help of queries. A query is a request / question / command submitted to oracle server to perform operations over DB.

·         SQL is developed by IBM, olden days it was called as “Sequel”.

·         It is the language common for all RDBMS software’s like Oracle, SQL Server, MySQL.

·         Apart from SQL, we also have QBE and QUEL which are used to communicate with the RDBMS server.

-          QBE refers to Query by Example. Used in MS-ACCESS.

-          QUEL refers to Query Language.

Depending on the operations, SQL is categorized into following sub languages:

1.       DML     ( Data Manipulation Language )

2.      DDL      ( Data Definition Language )

3.       DRL      ( Data Retrieval Language )

4.      TCL       ( Transaction Control Language )

5.      DCL       ( Data Control Language )

Data Definition Language (DDL):

-          DDL is a set of instructions to perform operations over Data Definition.

-          Data Definition is also called as Metadata. (data about data)

-          Commands in DDL are:

(i)                 CREATE            

(ii)               ALTER

(iii)             DROP

(iv)              TRUNCATE

(v)                RENAME

 CREATE :

Syntax:

Create table <table_name>

(

                                <column_name>             <data_type> (size),

                                <column_name>             <data_type> (size),

                                <column_name>             <data_type> (size),

                                --

                                --

);            

               

Ex:

                Create table Employee

(

                EmpNo                 Number(4),

                EmpName          varchar2(20),

                Salary                   Number(7,2),

                Desg                     varchar2(20),

                DeptNo                Number(2)

);

Scenarios:

                                 1.       Create a table from the existing table ?

                                 CREATE TABLE EMP_TARGET

                                 AS

                                SELECT * FROM EMP;

                                It creates a new table with name EMP_TARGET. It will create the table along with 

                                the data.

                               2.      Copy the table definition but not the data ?

                               CREATE TABLE EMP_TARGET

                               AS

                               SELECT * FROM EMP WHERE 1=2;

ALTER :

-          It is used to modify data definition of a table.

-          Using alter command :

1.       Add columns

2.      Drop columns

3.       Rename a column

4.      Modify a column

a.       Increasing or decreasing size

b.      Changing data type

c.       Changing Null to Not Null

d.      Changing Not Null to Null

1.       Adding columns using Alter ?

Syntax:

                ALTER TABLE <table_ name>

                                ADD ( column_name datatype(size));

Ex:

Alter Table Employee

                Add ( DOB Date, Gender CHAR(1));

2.      Dropping a column using Alter ?

Syntax:

                ALTER TABLE <table_ name>

                                DROP ( column_name datatype(size));

Ex:

Alter Table Employee

                DROP ( DOB, GENDER);

               

3.       Rename a column using Alter ?

Syntax:

                ALTER TABLE <table_ name>

                                RENAME COLUMN <old_name>  to  <new_name>;

Ex:

Alter Table Employee

                RENAME  column  HireDate   to   DateOfJoin;

4.      Modifying a column using Alter ?

a)      Increasing or decreasing the column size.

Syntax:

                ALTER TABLE <table_ name>

                                MODIFY ( column_name datatype(size)…);

Ex:

Alter Table Employee

                                MODIFY ( ename    varchar2(20));

b)      Changing datatype.

Ex:

Alter Table Employee

                                MODIFY ( COMM    varchar2(20));

c)      Changing from NULL to NOT NULL.

Ex:

Alter Table Employee

                                MODIFY ( ename    NOT NULL);

d)      Changing from NOT NULL to NULL.

Ex:

Alter Table Employee

                                MODIFY ( ename    NULL);

DROP :

Syntax:

                DROP TABLE <tableName>

Ex:

                DROP TABLE EMP;

-          Prior to 10g, we don’t have the option of restoring.

-          From 10g, we can even restore the Dropped tables. This is possible with the concept of “Flashback”.

-          In 10g, when the table is dropped, it is moved to recyclebin in Oracle server.

-          To view  the contents of the Recyclebin, execute the following commands:

Select * from recyclebin;

OR

Show recyclebin;

-          To restore the tables from Recyclebin, execute the following commands:

FLASHBACK table EMP to before DROP;

                This restores the table back along with the data.

-          To delete the table permanently,  execute the following commands:

PURGE   TABLE   EMP;

               

                This deletes the table permanently. Hence FLASHBACK is not possible in this case.

-          To empty Recycle bin, execute the following commands:

DROP   TABLE    EMP   PURGE;

SQL stands for Structured Query Language. and it is generally referred to as SEQUEL. SQL is simple language to learn. SQL is a Nonprocedural language, as compared to the procedural or third generation languages (3GLs) such as COBOL and C. SQL was developed by IBM in the 1970s.
The American National Standards Institute (ANSI) published its first SQL standard in 1986 and a second widely adopted standard in 1989. ANSI released updates in 1992, known as SQL92 and SQL2, and again in 1999, termed both SQL99 and SQL3. Each time, ANSI added new features and incorporated new commands and capabilities into the language.
SQL is a simple, yet powerful, language used to create, access, and manipulate data and structure in the database.

SQL Statements categories: DDL - Data Definition Language.

DDL is used to define, alter, or drop database objects and their privileges. DDL statements will implicitly perform a commit.

DDL Statements:

Create	It is used to create objects(tables, views) in the database.
Alter	It is used to alter the structure of the database objects.
Drop	delete database objects (It will invalidate the dependent objects ,it also drops indexes, triggers and referential integrity constraints ).
Truncate	remove all records from a table, including all spaces allocated for the records are removed (It is fast as compared to Delete and does not generate undo information as Delete does. It performs an implicit commit as it is a DDL. It resets the high water mark.)
Grant	assigning privileges

DML - Data Manipulation Language.

DML is used to access, create, modify or delete data in the structures of the database.

DML Statements:

Select	Select data from the database
Insert	It is used to insert data into a table
Update	It is used to update existing data within a table
Delete	It removes rows from the table.

DCL - Data Control Language

Following are the examples of Data control Statements.

DCL Statements:

Commit	It will end the current transaction making the changes permanent and visible to all users..
Savepoint	It will identify a point(named SAVEPOINT) in a transaction to which you can later roll back
Rollback	It will undo all the changes made by the current transaction.
Set- Transaction	It is used to define the properties of a transaction

Functions in Oracle

Functions in Oracle:

            A function is a program which accepts some input, perform some tasks and returns a value.

            1. Single row functions

            2. Multiple row functions

1. Single row functions :

            These functions will process one row and returns one value per row.

           

            - They are categorized into following categories:

                        (i)         String

                        (ii)        Date

                        (iii)       Mathematical

                        (iv)       Conversion

                        (v)        Special

                        (vi)       OLAP or Analytical

String Functions:

           

                        - UPPER( String )

                        - LOWER( String )

                        - INITCAP( String )

                        - LENGTH( String )

                        - SUBSTRING( String, start [,length] )

                        - INSTR( String1, String2 [,start,occurances] )

                        - LTRIM( String1 [,String2] )

                        - RTRIM( String1 [,String2] )

                        - TRIM( [Leading/Trailing/BOTH String2 from] String )

                        - RPAD( String, length, char )

                        - LPAD( String, length, char )

                        - REPLACE( String, searchString, replaceString )

                        - TRANSLATE( String, searchString, translateString )

                        - SOUNDEX( String )

                        - CONCAT( String1, String2 )

            Date Functions:

                        - EXTRACT( year/month/day from date )

                        - ADD_MONTHS( date, no )

                        - LAST_DAY( date )

                        - NEXT_DAY( date, day )

                        - MONTHS_BETWEEN( date1, date2 )

           

            Mathematical Functions:

                        - ABS( number )

                        - SIGN( number )

                        - POWER( number1, number2 )

                        - SQRT( number )

                        - MOD( number1, number2 )

                        - ROUND( number [,decimal_places] )

                        - TRUNC( number [,decimal_places] )

                        - CEIL( number )

                        - FLOOR( number )

1. UPPER( ):

            Converts string to Uppercase.

                        Syntax:           UPPER(string);

            ex:       Select upper(ename) from emp;              -- returns all employee names in upper case.

            Usage:            Mostly used when there is a  case in-sensitivity comparision.

            ex:       select * from emp where upper(ename) = 'SCOTT';    -- selects the SCOTT record without case sensitivity

2. LOWER( ):

            Converts string to Lowercase.

                        Syntax:           LOWER(string);

            ex:       Select lower(ename) from emp;               -- returns all employee names in lower case.

           

            Usage:            Mostly used when there is a  case in-sensitivity comparision.

            ex:       select * from emp where lower(ename) = 'adams';     -- selects the adams record without case sensitivity

3. INITCAP( )

            Converts the Initial to capitals.

                        Syntax:           INITCAP(string);

            ex:       Select  INITCAP( "hello world..!!!" ) from dual;    -- retuns Hello World..!!!

            Usage:            Mostly used when the name of the user is to be displayed.

            ex:       Select  INITCAP( "hi shashi..!!!") from dual;         -- retuns Hi Shashi..!!!

4. LENGTH( ):

            Retuns the Length of the String.

                        Syntax:           LENGTH(string);

            ex:       Select LENGTH("Ename") from EMP;                   -- returns 5

            Usage:            Mostly used in case of Checking the length of a password.

            CREATE TABLE USER

            (

                        UNAME          VARCHAR2(20),

                        PWORD          VARCHAR2(20)         CHECK ( LENGTH(PWORD) > 8 )

            );

5. SUBSTR( ):

            Extract the part of the String.

                        Syntax:           SUBSTR( str1, start [,length] );

           

            ->        start may be +ve/ -ve.

                                    If +ve, starts from right side

                                    If -ve, starts from left side

           

            ex 1:    Select SUBSTR( 'Hello', 2, 3) from dual;               -- returns ell  ( H=1, E=2; hence starting from 'e' 3 characters)

            ex 2:    Select SUBSTR( 'Hello', -2, 3) from dual;  -- returns lo   ( starting from right side; O=1, L=2; hence starting from 'L' 3 characters)

                                    - no problem if there are in-sufficient characters.

           

6. INSTR( ):

            Used to check the occurance of one string in another string.

                        Syntax:           INSTR( str1, str2 [, start, occurance ] );

           

            ->        start may be +ve/ -ve.

                                     If +ve, starts from right side

                                    If -ve, starts from left side

           

            ->        If str1 contains str2, it returns the position.

                        else, it returns 0.

            ex 1:    Select INSTR( 'hello world', 'o') as "First Occurance",

                                      INSTR( 'hello world','o',-1) as "Second Occurance",

                                      INSTR( 'hello world','o',-1,2) as "First Occurance from Right Side"

                        from dual;

                        o/p:                 First Occurance:                  5

                                                Second Occurance:            8

                                                First Occurance from Right Side: 5

           

            Usage:

                        Extract Username from Email Id.

                       

                        Ex: Display abc2xyz from 'abc2xyz@gmail.com'

                        ->        Select substr('abc2xyz@gmail.com',1,instr( 'abc2xyz@gmail.com' , '@')-1) from dual;

                        Ex:       Insert the data from CustomerS to CustomerT.

                                    Rules: CNAME split into FNAME, MNAME, LNAME.

                                    CustomerS                            CustomerT

                                    - CID                                        - CID

                                    - CNAME                                - FNAME

                                                                                    - MNAME

                                                                                    - LNAME

                       

                        ->        Insert into CustomerT

                                                Select cid, substr( cname, 1, instr( cname, ' ')-1)

                                                                  , substr( cname, instr( cname, ' ')+1, instr( cname, ' ',1,2)-1)

                                                                  , substr( cname, instr( cname, ' ',1,2)+1)

                                                from CustomerS

7. LTRIM( ):

            Used to trim spaces and un-wanted characters present on the left side of the String.

                        Syntax:           LTRIM( string1 [, String2] );

           

            ex:       Select LTRIM ('          hello') from dual;     -- return hello.

                        Select LTRIM ('xxxhello','x') from dual;    -- return hello.

8. RTRIM( ):

            Used to trim spaces and un-wanted characters present on the right side of the String.

                        Syntax:           RTRIM( string1 [, String2] );

           

            ex:       Select RTRIM ('hello   ') from dual;           -- return hello.

                        Select RTRIM ('helloxxx','x') from dual;    -- return hello.

9. TRIM( ):

            Used to trim on both sides. It trims spaces and un-wanted characters.

                        Syntax:          

                                    TRIM( '            String ');                    -- to trim both the sides.

                                    TRIM( Leading 'String1' from <String2> );

                                    TRIM( Trailing 'String1' from <String2> );

                                    TRIM( BOTH 'String1' from <String2> );

            ex:       TRIM( '            Hello   ');                                                        -- returns Hello

                        TRIM( Leading 'xyz' from 'xyzHelloxyz' ); -- returns Helloxyz

                        TRIM( Trailing 'xyz' from 'xyzHelloxyz' );  -- returns xyzHello

                        TRIM( BOTH 'xyz' from 'xyzHelloxyz' );                  -- returns Hello

10. RPAD( ):

            Used to fill the string with characters on Right side. It fills specified no of times.

                        Syntax:           RPAD( String, length, char);

            ex:       Select RPAD( "Hello", 10, '*' );        -- returns Hello*****

                                    Total no of characters = 10 (including String + *).

11. LPAD( ):

            Used to fill the string with characters on Left side. It fills specified no of times.

                        Syntax:           LPAD( String, length, char);

                                               

            ex:       Select LPAD( "Hello", 10, '*' );        -- returns *****Hello

                                    Total no of characters = 10 (including String + *).

           

            Usage:

                        Display ename, job and sal. Sal must be hidden such that 1 * for each thousand.

                        Select ename, job, RPAD( '*', sal/1000, '*') as 'Sal' from EMP;

12. REPLACE( ):

            Used to replace the String with another String.

                        Syntax:           replace( String, search, withString );

            ex:       Select replace ( 'UTI BANK', 'UTI', 'AXIS' ) from dual;    -- returns AXIS BANK

            Usage:

                        Display the names of the employees who has only one 'A' in his name.

                        Select ename from emp where length( replace (UPPER(ename), 'A',''))= length(ename)-1;

13. TRANSLATE( ):

            Used to translate one character to another character.

                        Syntax:           Translate( String, search, translateString );

                       

            ->        Replace function is to replace String - String.

            ->        Translate function is to translate char - char.

            ex:       Select replace( 'Hello', 'ell', 'xyz' ) from dual;                  -- returns Hxyzo

                        Select translate( 'Hello', 'ell', 'xyz' ) from dual;   -- returns Hxyyo

                        Here l->y , hence z is not assigned to any character.

                       

            Usage:            Mostly used for Encoding and Decoding.

                        Encode Employee sal.

                        Select ename, translate( sal, '0123456789', 'abcdefghij' ) from emp;

14. SOUNDEX( ):

            Used to find the string which sounds same.

                        Syntax:           SOUNDEX( string );

                                   

            ex:       Select * from emp where SOUNDEX( ename ) = SOUNDEX( 'smyth' );         -- returns smith record.

            Usage:

                        -> Display the employees with-out using LOWER and UPPER functions.

                        -> Mostly used to display results when there are any spelling mistakes.

15. CONCAT(  ):

            Used to cooncatenate two strings.

                        Syntax:           CONCAT( string1, string2 );

           

            ex:       Select CONCAT( 'Hello', 'Welcome' ) from dual;             -- returns 'Hello', 'Welcome'

            -> We can also used '||' for concatenation.

            Usage:

                        Display employee records as follows:

                                    " Smith working as Clerk for deptno 10 ".

                        Select ename ||" working as "|| job ||" for deptno"|| deptno from emp;

Date Functions:

            Date Operations:

                        1. Sysdate + 10        -           10 days are added to the system date.

                        2. Sysdate - 10          -           10 days are subtracted from the systemdate.

                        3. D1-D2                     -           Returns the difference in the Days.

                        4. D1+D2                   -           Invalid Operations.

            Functions:

                        1. EXTRACT( )

                        2. ADD_MONTHS( )

                        3. LAST_DAY( )

                        4. NEXT_DAY( )

                        5. MONTHS_BETWEEN( )

1. Extract( ):

            Used to extract part of the date.

                        Syntax:           EXTRACT ( year/month/day  from <date>);

           

            ex 1:                Select extract( year from sysdate ) from dual;  -- returns current year                              

            ex 2:                Select extract( month from sysdate ) from dual;          -- returns current month

            ex 3:                Select extract( day from sysdate ) from dual;   -- returns current day

            Usage:

                        Display employee records joined in year 2010, 2011.

                       

                        Select * from emp where extract( year from hiredate ) in (2010, 2011);

2. ADD_MONTHS( ):

            Used to add no of months to a date.

                        Syntax:           ADD_MONTHS( date, no );

           

            ->        If no>0, months are added.

            -.          If no<0, months are sutracted.

            ex 1:                Select sysdate, ADD_MONTHS( sysdate, 2 ) as "Next date" from dual;       -- returns  '15-DEC-2011' as Sysdate, '15-FEB-2012' as "Next date"

            ex 2:                Select sysdate, ADD_MONTHS( sysdate, -2 ) as "Next date" from dual;      -- returns  '15-DEC-2011' as Sysdate, '15-OCT-2011' as "Next date"

            Usage:

                        Every emp of the org has to retired at a stage of 40 yrs of exp.

                        Display all the employees who gets retired in 2015;

                        Select  * from emp where EXTRACT( year from ADD_MONTHS(hiredate,40*12) ) = 2015;

3. LAST_DAY( ):

            Used to get the last day of the month.

                        Syntax:           LAST_DAY( date )

                       

            ex 1:                Select LAST_DAY(sysdate) from dual;                            -- returns '31-DEC-11'

            Usage:           

                        What is current months 1st day .

                        Select ADD_MONTHS( LAST_DAY( sysdate) + 1, -1) from dual;

4. NEXT_DAY( ):

            Used to get the next specified date starting from today.

                        Syntax:           NEXT_DAY( date, day )

            ex 1:                select NEXT_DAY( sysdate, 'saturday' ) from dual;                   -- returns '17-DEC-11', where sysdate = '15-DEC-11'

                       

            Usage:

                        Display first monday of the month.

                        Select NEXT_DAY( LAST_DAY(ADD_MONTHS(sysdate,-1)), 'saturday' ) from dual;

5. MONTHS_BETWEEN( ):

            Returns the no of months between 2 days.

                        Syntax:           MONTHS_BETWEEN( date1, date2 );

           

            ex 1:    Select MONTHS_BETWEEN('01-JAN-2011', '21-MAR-2011') from dual;                    -- returns              -2.6451613

            Usage:

                        Display the employees with their experience.

           

            Select ename, MONTHS_BETWEEN( sysdate, hiredate)/12 as "Experience" from EMP; 

Mathematical Functions:

1. ABS( )

2. SIGN( )

3. POWER( )

4. SQRT( )

5. MOD( )

6. ROUND( )

7. TRUNC( )

8. CEIL( )

9. FLOOR( )

1. ABS( )

            Returns the absolute values.

                        Syntax:           ABS( number );

            Ex 1:    Select ABS( -10 ) from dual;                      -- returns 10

2. SIGN( )

            Return the sign of the values.

                        Syntax:           SIGN( number );

           

            -> If number>0 , sign = 1

            -> If number<0 , sign = -1

            -> If number=0 , sign = 0

            Ex 1:    Select SIGN( 10 ) from dual;          -- return 1

                        Select SIGN( -10 ) from dual;         -- return -1

                        Select SIGN( 0 ) from dual;             -- return 0

3. POWER( )

            Return the power for the expression.

                        Syntax:           POWER( number1, number2);

            Ex 1:    Select POWER( 3,2 ) from dual;    -- returns 9.

4. SQRT( )

           

5. MOD( )

6. ROUND( )

7. TRUNC( )

8. CEIL( )

9. FLOOR( )