Sunday, 23 December 2012

SQL Tutorial

SQL stands for  Structured Query Language is a special-purpose programming language designed for managing data in relational database management systems (RDBMS).
Originally based upon relational algebra and tuple relational calculus, its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
If one has knowledge of SQL then h/she can work any type of database package running in market becouse  SQL is supported by all DBMS software. So if you have knowledge of SQL then no need to worry what type of Database we have to use chilllll have a look.SQL statement are divide in three part
  • DML (Data Manipulation Language )
  • DDL ( Data Definition Language ) 
  • DCL (Data Control Language )
Creating a Table
     syntax.
         create table <table_name>
          (field1 data_type [constraint] ,field2 data_type [constraint],------- field_n data_type_n)
     e.g.
         create table student
             (roll number(2) primary key,name varchar2(255) not null,city varchar2(255));
Updating a Table
      syntax .
           update <table_name>
           set  old_field1=new_value,old_fleld2=new_value--------------n
               [ where=condition]
    e.g.
          update stu
          set name='sona',city='vns'
          where roll=101
Delete A Record
    syntax.
            delete from <table_name>
            [where = condition]
   e.g.
           delete from stu
          where roll=101
Select A Record
   syntax
            Select <field1,field2---------n,[*]> from <table_name>
             [ where =<condition> ]
  e.g.
          select * from stu    
          ( Return all row with all column )
  •          select roll,name from stu
            ( Return all row with only roll and name field )
  •          select * from stu
            where city='vns'
           (Return rows which city is vns )
Logical Operator
  There are three Logical Operators namely, AND, OR, and NOT. These operators compare two conditions or more than one condition  at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition.

OR Operator
      Or operator is useful where we have many option and we have take to our decision if any condition becomes true from specified condition then our result will be true.  
  i.e.
       Select * from student
       where city='vns' or marks>101

       select * from student
       where city='vns' or lastname='singh'

AND Operator

  And operator is useful where we have many option and we have to our take decision if all condition are becomes true from specified condition then our result will be true.  
 i.e.        
       Select * from student 
       where city='vns' or marks>101

       select * from student
       where city='vns' or lastname='singh'


Not Operator

For a row to be selected the specified condition must be false.
      i.e.
         Select firstname,lastname from student
          where not middlenam='Shukla'

Not in Operator

    This operator is useful when  we don't want rows any specific row. We provide condition sql checked it and return all row leaving these rows which satisfied the condion

i.e.
    Select * from student
   where city not in ('vns','alld')


NULL


NULL is a special database concept to represent the absence of value. It is neither a zero nor an empty string, but a special character that can be substituted for any data type. Nulls are usually used when the value is unknown or meaningless. NULL columns can later be updated with some real data.
For example, when a new employee is hired, he/she might neither yet have the phone number nor be assigned to a department. In such situation the NULL values are appropriate for PHONE and DEPARTMENT columns.

Distinct

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to return only distinct (different) values.
e.g
select distinct * from student
where city='vns'
(reurns unique row which belongs to vns city )

Add A Column



ALTER TABLE table_nameADD column_name datatype