OBJECT ORIENTED PROGRAMMING AND CPP

Basic concept of  oops:                     

                       *Objects:                

                       * Classes

                       *Data Abstraction  and Encapsulation

                       *Inheritence

                       *Polymorphism

                       *Dynamic binding

                       *Messsage passing                              

 What is the advantage of OOP?

You will get varying answers to this question depending on whom you ask. Major advantages of OOP are: 
1. Simplicity: software objects model real world objects, so the complexity is reduced and the program structure is very clear; 
2. Modularity: each object forms a separate entity whose internal workings are decoupled from other parts of the system; 
3. Modifiability: it is easy to make minor changes in the data representation or the procedures in an OO program. Changes inside a class do not affect any other part of a program, since the only public interface that the external world has to a class is through the use of methods; 
4. Extensibility: adding new features or responding to changing operating environments can be solved by introducing a few new objects and modifying some existing ones; 
5. Maintainability: objects can be maintained separately, making locating and fixing problems easier; 
6. Re-usability: objects can be reused in different programs

 *OBJECTS:

                    *Object are basic runtime entities in an object oriented system.

                    *They may represent a table of data or any item that program has to handle.

*ATTRIBUTE:

                        *A quality or feature regarded as a characteristic or inherent part of someone or something.

*DATA ABSTRACTION AND ENCAPSULATION:

                                       An abstraction denotes the essential characteristics of an object that distinguish it from all other kinds of object  and thus provide crisply defined conceptual boundaries, relative to the perspective of the viewer.

                                      *The wrapping up of data and functions into a single unit is  is known as encapsuation.

                                      *The data is not accessible to the outside world and only those functions which are wrapped in the class can access it.

                                      *This insulation of data from direct access by program is called data hiding or information hiding.Data hiding is the process by which access modifiers are used to hide the visibility of java methods and variables. They access modifiers are: public, private and protected.

ABSTRACTION:
                 *The process of hiding unwanted details from the users. And providing only importand details.

INHERITANCE:

                 *Deriving a new class from existing one.

                  *It provides reusability.

                 *Reduce the coding.

TYPES:

                   *Single Inheritance(one to one)

                  *Multilevel Inheritance(one to one to one)

                  *Multiple Inheritence(many to one)

                   *Hierarchical Inheritence(one to many)

                   *Hybrid Inhertance

POLYMORPHISM:

                        *one name having multiple forms.

                        *The process of making an operator to exhibit differnet behaviours

 in different  instatnces is knows as opertaor overloading.

                        *Using a single fucntion name to perform different types of task is 

known as function overloading.

DYNAMIC BINDING:

                       *It means that the code associated with a given procedure call is not known until the time of the call at runtime.

                       *It is assiciated with inheritance and polymorphism.

MESSAGE PASSING:
 *AN OOP consists of a set of objects that communicate with each other.               

 *BASIC STEPS:

                    *Creatnig classes that define objects and their behavour.

                       *Creating objects from class definitions

                     *Establishing communication among objects.

 HISTORY OF C++:

                *C++ was created by Bjarney Stroustrup in 1980 at AT&T Bell Laboratories in Murray Hill in USA. 

                     *It was standardized in 1998 by the ANSI and ISO.

 OPERATORS IN C++;

                     *All C operators are valid in c++.Some new operators are

                       1)<<   insertion operator

                       2)>>  Extractoin opertaor

                       3)::    Scope resolution operator

                       4)::*   pointer to member declarator

                       5)->*  pointer to member operator

                       6).*    pointer to member operator

                       7)delete   memory release operator

                       8)endl   line feed operator

                       9)new   memory allocation operator

                       10)setw   field with operator

 MANIPULATOR:

                     *Manipulators are operators that are used to format the  data display.Commonly used manipulators are endl,setw.

FUNCTION  PROTOTYPING:

                      *It describes the function interface to the compiler by giving details such as the number and type of arguments and the type of return values.

INLINE FUNCTION:
                      *An inline function is a function that is expanded in line when it is invoked.

                      *That is the compiler replaces  the function call with the corresponding code.

CONSTRUCTURES  AND DESTRUCTORS:

                         *Constructor is a special member function when a object is created then function will be called automatically.

                          *The constructor  that  take argument is called  as Parameterized Constructors.

                          *A copy constructor  takes a reference to an object of the same class as itself as an argument.

                          *Also default constructure and dynamic constructures.

 DESTRUCTORS:

                         *When a deustructor fuction is called the object will automatically destroyed.

*this  POINTER:

                            *this is a pointer that points to the object for which this function was called.

VIRTUAL  FUNCTION:

                                 *When we use the same function name in both the base and derived classes,the function in base class is declared as virtual using the keyword virtual.

                               *A virtual function equated to zero is called a pure virrual function.

It is a function declared in a base class that has no definition relative to the base class.

TEMPLATE:
                              *A template can be used to create a family of classes or functions.  

PROCEDURE ORIENTED PROGRAMMING

PROCEDURE ORIENTED PROGRAMMING:

              *COBOL,FORTRAN and C is commonly known as procedure oriented programming.

                  *In POP, the problem is viewed as sequence of things to be done such as reading,calculating and printing.

                 *It is a top down approach.    

HISTORY OF C: 

          *The first computer language was ALGOL introduced in 1960.It is a structured programming.

          *Martin Richards developed a language called as BCPL(Basic Combined Programming Language) in 1967.

          *Ken Thompson created a language using many features of BCPL and called as B.

          *B was used to early version of UNIX operating system at Bell Laboratories.

          *Both B and BCPL were Typeless system programming languages.

          *C language was evolved from ALGOL,BCPL and B languages by Dennis Ritchie at the Bell Laboratories in 1972.

          *C uses many concepts from these languages and added the concept of Data types end other powerful features.

          *In 1983 ANSI(American National Standards Institute) appointed a technical committee defined a standard for C.

          *The committee approved a version of C in December 1989 which is known as ANSI C also referred as C89.

          *Then it was approved by ISO.

          *Current version of C is C99.          

IDENTIFIER:

          *An identifier is a symbolic name used to refer to an entity.Such as Data type,Constant,Variable,Function,Array etc in a program.

VARIABLE AND CONSTANT:

          *Variable is an identifier used for storing data in a program. The value of the variable may be changed during execution of the program.

          *Constant is a fixed value. It never changes its value during execution.

 Types of Constants:

                  1)Integer Constants(123,-321,xbcd)

                  2)Real Constants(92.3,0.65e4)

                  3)Single character Constants('5','x',';')

                  4)String Constants("hello","1087")

                  5)Backslash Constants('\t','\n')

                       

SCALAR DATA TYPE AND DERIVED DATA TYPE:

                *A Scalar data type is used for representing a single value only.

                 Such as int,float,char,double.

                *A Derived data type is used for representing a single value or multiple value.

                * It is derived from Scalar data type. Such as Array,Structure,Union and Functions.

                                                

 TOKEN:

           *Token is a individual entity of a program. A compiler identifies and splits a  program into  no of tokens.

             C has six types of tokens.

                      1)Keywords

                      2)Identifier

                      3)Constants

                      4)Strings

                      5)Special symbols

                      6)Operators

KEYWORDS:

                     *All Key words have fixed meanings and these meaning cannot be changed.

                         ANCI C has defined 32 keywords.

                         C99 has added 5 more keywords.

                                        1)_Bool

                                        2)_Complex

                                        3)_Imaginary

                                           4)inline

                                        5)restrict       

OPERATOR AND OPERAND:

                        *An operator is a symbol that tells a computer to perform certain mathematical or logical manipulations.

CLASSIFICATION OF OPERATORS IN C:

               1)Arithmetic operator(+,-,*,/,%)

               2)Relational operator(<,<=,>,>=,==,!=)

               3)Logical operator(&&-AND,||-OR,!-NOT)

               4)Assignment operator(=)

                           3 types of assignments:

                                             i)Simple assignment(a=10)

                                            ii)Compound assignment(a+=5)

                                            iii)Assignment(a=b=c=0)

               5)Increment and Decrement operators(++,--)

                                 The execution of n++ is much faster than execution of n=n+1.

                                          n++ requires single machine instruction but n=n+1 requires more instructions to carry out this operation.

               6)Conditional operators()

               7)Bitwise operators(&-bw AND,|-bw OR,^-bw EXOR,<<-shift left,>>-shift right)

               8)Special operators (comma,sizeof)

 EXPRESSION:

      *A valid combination of constants,variables,and operators constitutes an expression.

DATA TYPES:

                        *ANCI C supports three classes of data types

                                1)primary or fundamental data type

                                2)Derived data type(Array,Functions,Pointers)

                                3)User defined data type(Structures,Unions,Enumerations)

                1)primary data types:

                            1)Integer has two types.

                                          1)Signed(short int=1 byte,int,long int=4 byte)

                                          2)Unsigned(Unsigned short int,unsigned int,unsigned long int)

                             2)Float has three types:

                                          1)float=4 byte

                                          2)double=8 byte

                                          3)long double =10 byte

                            3)Char has three types(char,signed char,unsigned char)=1 byte

DECISION MAKING:

                                     *C language possesses such decision making capabilities by supporting the following elements:

                                         1)if statement

                                                   1)simple if statement

                                                   2)if...else statement

                                                   3)Nested if....else statement

                                                   4)else if ladder

                                         2)switch statement

                                         3)Conditional statement

                                         4)goto statement            

LOOPING:

                            1)The while statement(Entry Controlled Loop)

                            2)The do statement(Exit Controlled Loop)

                            3)The for statement (Entry Controlled Loop)

What is difference between Entry controlled

 and Exit Controlled?

          *In Entry controlled loop the test condition is checked first and if the condition is true then the block of sttaement in the loop body will be executed. While in exit controlled loop the body of loop will be executed first then check the condition.

  

ARRAYS:

                       *An array is a fixed size sequenced collection of elements of same datatype.

 Types of array:

             1)One-dimensional arrays (datatype arr-name[size])

             2)Two-dimensional arrays(datatype arr-name[row-size][col-size])

             3)Multi-dimensional arrays(datatype arr-name[s1][s2][s3]...[sn])                                                                                                             

 STRING:

                     *String is a sequence of chars that is treated as a single data item.

         Ways of getting string:

                        1)scanf("%c",n)

                        2)ch=getchar()->only for single word

                        3)gets(a)

                        4)c=getc(fp2)  

       Ways of display string:

                        1)printf("%c",n)

                        2)putchar(ch)

                        3)puts(n)

                        4)putc(c,fp1)

                    * fprintf,fscanf handle a group of mixed data simultaneously.

EX:

fprintf(fp,'control string',list)

STRING HANDLING FUNCTIONS:

                       1)strcat function(str1,str2)

                       2)strcmp function(str1,str2)

                       3)strlen function(str)

FUNCTIONS:

                              *Function is a sub program that can perform any particular task.

                     Two types of functions:

                        1)library functions or predefined(printf,strcat)

                        2)User defined functions

CATAGORY OF FUNCTIONS:

                        1)Functions with no argument and no return values.

                        2)Functions with arguments and no return values.

                        3)Function with no argument but return a value.

                        4)Function that return multiple values.   

STORAGE CLASS:   

                          *In C all variables have a storage class:They are

                         1)Automatic variables(Local or internal variables)

                         2)External variables(Global)

                         3)Static variables()

                         4)Register variables                          

  1)Automatic variables:

                          *These are declared inside the function. They are created when the function is called and destroyed automatically when the function is executed.    

 2)External variables:

                          *Variables that are  both alive and active throughout the entire program is external variables.

EX: extern int a=5;

3)Static variable:

                          *The value of static variables  persists until the end of the program.     

EX: static int count=5;

4)Register variable:

                        *In register variable, variables are stored in one of the machine register. Because the register access is much faster than a memory access.

EX: register int count;

STRUCTURE:

               *It is a collection of data items of  different types  using a single name.

UNION:

              *Union is similar to structure. The distinction   between them in terms of storage.

               *In structure, all member has its own storage location.

               *In union, all member uses the same location.

POINTER:

               What is a pointer and does Java support pointers?

Pointer is a reference handle to a memory location. Improper handling of pointers leads to memory leaks and reliability issues hence Java doesn't support the usage of pointers.

               *Pointers contain memory address as their values. Since  these memory addresses are locations in the computer memory where the data are stored.

      Features of pointers:

                *pointers reduce length and complexity of programs.

                *They increase the execution speed and thus they reduce the program execution time.

Disadvantages:

                * By using pointer the hacker can get the address of source code and can change them. 

             *EX  const  int x=5;

             *In the above ex the value of x can't be change.But using pointer it is possible.

FILE:

              *A file is a place on the disk where a group of related data is stored.

              *The basic file operations are

                 1)Naming a file

                 2)Opening a file

                 3)Reading data from a file

                 4)Writing data to a file.

                 5)Closing a file

 1)Naming and opening a file and closing a file:

                 *FILE *fp;

                   fp=fopen('filename','mode');

mode->specifies the purpose of file opening.

           r=open the file reading only

           w=open the file for writing only

           a=open the file to adding data to it.  

To close a file:

                   *fclose(file pointer);

MEMORY ALLOCATON FUNCTIONS:
                    *malloc()

                    *calloc(allocating space for arrays)

                    *free(frees previously allocated space)

                    *realloc(modofies the size of previously allocated space)

 PREPROCESSOR DIRECTORIES:

                       *#define 

                      *#undef       *#ifndef

                      *#Include     *#if

                      *#ifdef     *#else

MYSQL TUTORIAL PART III

TO FIND THE SUM OF A COLUMN:

mysql> select @a:=sum(cid) from client where cid>100;

+--------------+

| @a:=sum(cid) |

+--------------+

|          687 |

+--------------+

1 row in set (0.02 sec)

mysql> select @a:=sum(cid) from client;

+--------------+

| @a:=sum(cid) |

+--------------+

|          787 |

+--------------+

1 row in set (0.00 sec)

TO FIND THE MAX NUMBER IN A COLUMN:

mysql> select @a:=max(cid) from client;

+--------------+

| @a:=max(cid) |

+--------------+

|          118 |

+--------------+

1 row in set (0.01 sec)

TO LOCK A TABLE:

you must specify a READ lock type or a WRITE lock type. If you specify READ, any connection can read from the table, but no connection can write to the table

mysql> lock table cust read;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into cust(no,name)values(50,'ravi');

ERROR 1099 (HY000): Table 'cust' was locked with a READ lock and can't be updated

mysql> update cust set name='susi'where no=40;

ERROR 1099 (HY000): Table 'cust' was locked with a READ lock and can't be updated

If you specify WRITE, the current connection can read or write to the table, but no other connections can access the table until the lock has been removed.

mysql> lock table cust write;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from client join cust on cust.name=client.cname;

ERROR 1100 (HY000): Table 'client' was not locked with LOCK TABLES

mysql> lock table client write;

Query OK, 0 rows affected (0.00 sec)

GROUP BY

     The MySQL GROUP BY clause is used with SQL SELECTstatement to to group selected records into a set of summary records by the one or more column's value or expression.

mysql> select cid,cname,cemailid from client group by cid;

+------+---------+-------------------+

| cid  | cname   | cemailid          |

+------+---------+-------------------+

|  100 | vivek   | viv@gmail.com     |

|  111 | raja    | raja@gmail.com    |

|  112 | raji    | raji@gmail.com    |

|  113 | karthik | kar@gmail.com     |

|  116 | abi     | abi@y7mail.com    |

|  117 | abi1    | abi1@y7mail.com   |

|  118 | vivek20 | vivek20@gmail.com |

+------+---------+-------------------+

7 rows in set (0.00 sec)

JOINS:           ‘join’ used to connect two or more tables logically with or without common field.

Types of Join:

       1)Inner Join

       2)Outer Join

           a)Left Outer Join

           b)Right Outer Join

       3)Full Join(Full Outer Join)

       4)Cross Join

       5)Equi Join

       6)Nature Join

       7)Self Join
1)INNER JOIN
     In oracle we should specify condition in inner join. In mysql inner join without condition  is similar to cross join
Two tables having the common column values go with inner join.

mysql> select * from bank inner join client on bank.bid=client.cid;

+------+-------+---------------+-----+-------+------------------------+

| bid  | bname | bmailid       | cid | cname | cemailid               |

+------+-------+---------------+-----+-------+------------------------+

|  100 | kvl   | kvl@gmail.com | 100 | vivek | rvivekshiva@y7mail.com |

+------+-------+---------------+-----+-------+------------------------+

1 row in set (0.11 sec)

mysql> select * from bank  join client on bank.bid=client.cid;

+------+-------+---------------+-----+-------+------------------------+

| bid  | bname | bmailid       | cid | cname | cemailid               |

+------+-------+---------------+-----+-------+------------------------+

|  100 | kvl   | kvl@gmail.com | 100 | vivek | rvivekshiva@y7mail.com |

+------+-------+---------------+-----+-------+------------------------+

1 row in set (0.00 sec)
LEFT OUTER JOIN:
A left outer join will give all rows in first table, plus any common rows in second table.
mysql> select * from bank  left (outer) join client on bank.bid=client.cid;

+------+-------+---------------+------+-------+------------------------+

| bid  | bname | bmailid       | cid  | cname | cemailid               |

+------+-------+---------------+------+-------+------------------------+

|  100 | kvl   | kvl@gmail.com |  100 | vivek | rvivekshiva@y7mail.com |

|  101 | kmb   | kmb@gmail.com | NULL | NULL  | NULL                   |

|  102 | tnj   | tnj@gmail.com | NULL | NULL  | NULL                   |

|  103 | mtr   | mtr@gmail.com | NULL | NULL  | NULL                   |

+------+-------+---------------+------+-------+------------------------+

4 rows in set (0.30 sec)
RIGHT OUTER JOIN:

A Right outer join will give all rows in secondt table, plus any common rows in first table.

mysql> select * from bank  right (outer) join client on bank.bid=client.cid;

+------+-------+---------------+-----+---------+------------------------+

| bid  | bname | bmailid       | cid | cname   | cemailid               |

+------+-------+---------------+-----+---------+------------------------+

|  100 | kvl   | kvl@gmail.com | 100 | vivek   | rvivekshiva@y7mail.com |

| NULL | NULL  | NULL          | 111 | raja    | raja@gmail.com         |

| NULL | NULL  | NULL          | 112 | raji    | raji@gmail.com         |

| NULL | NULL  | NULL          | 113 | karthik | kar@gmail.com          |

| NULL | NULL  | NULL          | 114 | vicky   | viki@gmail.com         |

| NULL | NULL  | NULL          | 116 | abi     | abi@y7mail.com         |

| NULL | NULL  | NULL          | 117 | abi1    | abi1@y7mail.com        |

| NULL | NULL  | NULL          | 118 | vivek20 | vivek20@gmail.com      |

+------+-------+---------------+-----+---------+------------------------+

8 rows in set (0.00 sec)
FULL OUTER JOIN:
A full outer join will give you the union of A and B, i.e. All the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a    |  b 

-----+-----

   1 | null

   2 | null

   3 |    3

   4 |    4

null |    6

null |    5
CROSS JOIN:

The cross join is used to retrieve data from tables as Cartesian product format of set theory in mathematics. All rows are shown to be multiply by to another tables' rows.
EQUI JOIN:

When you relate two tables on the join condition by equating the columns with equal(=) symbol, then it is called an Euqi-Join. Equi-joins are also called as simple joins.

mysql> select client1.aid,client1.aname,bank.bname from client1,bank where bank.

bid=client1.aid;

+------+-------+-------+

| aid  | aname | bname |

+------+-------+-------+

|  100 | vivek | kvl   |

|  101 | kar   | kmb   |

|  102 | vicky | tnj   |

|  103 | raja  | mtr   |

+------+-------+-------+

4 rows in set (0.02 sec)
NATURE JOIN:
Mysql Natural Join is a specialization of equi-joins. The join compares all columns in both tables that have the same column-name in both tables that have column name in the joined table. The resulting set include only one column for each pair of the same named column.

mysql> select * from client1 natural  join bank;

+------+-------+-----------------+------+-------+---------------+

| aid  | aname | amailid         | bid  | bname | bmailid       |

+------+-------+-----------------+------+-------+---------------+

|  100 | vivek | viv@gmail.com   |  100 | kvl   | kvl@gmail.com |

|  100 | vivek | viv@gmail.com   |  101 | kmb   | kmb@gmail.com |

|  100 | vivek | viv@gmail.com   |  102 | tnj   | tnj@gmail.com |

|  100 | vivek | viv@gmail.com   |  103 | mtr   | mtr@gmail.com |

|  101 | kar   | kar@gmail.com   |  100 | kvl   | kvl@gmail.com |

|  101 | kar   | kar@gmail.com   |  101 | kmb   | kmb@gmail.com |

|  101 | kar   | kar@gmail.com   |  102 | tnj   | tnj@gmail.com |

|  101 | kar   | kar@gmail.com   |  103 | mtr   | mtr@gmail.com |

+------+-------+-----------------+------+-------+---------------+

20 rows in set (0.00 sec)

mysql> create table test5 select * from bank;

Query OK, 4 rows affected (0.12 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from bank;

+------+-------+---------------+

| bid  | bname | bmailid       |

+------+-------+---------------+

|  100 | kvl   | kvl@gmail.com |

|  101 | kmb   | kmb@gmail.com |

|  102 | tnj   | tnj@gmail.com |

|  103 | mtr   | mtr@gmail.com |

+------+-------+---------------+

4 rows in set (0.00 sec)

mysql> select * from test5;

+------+-------+---------------+

| bid  | bname | bmailid       |

+------+-------+---------------+

|  100 | kvl   | kvl@gmail.com |

|  101 | kmb   | kmb@gmail.com |

|  102 | tnj   | tnj@gmail.com |

|  103 | mtr   | mtr@gmail.com |

+------+-------+---------------+

4 rows in set (0.00 sec)

mysql> select * from test5,test6 where test5.bid=test6.cid;

+------+-------+---------------+-----+---------+------------------------+

| bid  | bname | bmailid       | cid | cname   | cemailid               |

+------+-------+---------------+-----+---------+------------------------+

|  100 | kvl   | kvl@gmail.com | 100 | vivek   | rvivekshiva@y7mail.com |

|  101 | kmb   | kmb@gmail.com | 101 | raja    | raja@gmail.com         |

|  102 | tnj   | tnj@gmail.com | 102 | raji    | raji@gmail.com         |

|  103 | mtr   | mtr@gmail.com | 103 | karthik | kar@gmail.com          |

+------+-------+---------------+-----+---------+------------------------+

4 rows in set (0.02 sec)

SELF JOIN:

A self-join is a query in which a table is joined (compared) to itself.  Self-joins are used to compare values in a column with other values in the same column in the same table.

select e1.bid||'works for'||e1.bname "Employessa and their manager" from bank e1  where e1.bid>10100;

Employessa And Their Manager

10200works fortnj

10300works fortcy

10400works forche

10500works forbng

DBMS vs. RDBMS

• Relationship among tables is maintained in a RDBMS whereas this not the case DBMS as it is used to manage the database.

• DBMS accepts the ‘flat file’ data that means there is no relation among different data whereas RDBMS does not accepts this type of design.

• DBMS is used for simpler business applications whereas RDBMS is used for more complex applications.

• Although the foreign key concept is supported by both DBMS and RDBMS but its only RDBMS that enforces the rules.

• RDBMS solution is required by large sets of data whereas small sets of data can be managed by DBMS.

DELETE

        The DELETE command is used to remove rows from a table. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE

        TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.

COMMIT

Purpose

Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. This statement also erases all savepoints in the transaction and releases transaction locks.

Oracle Database issues an implicit COMMIT before and after any data definition language (DDL) statement.

You can also use this statement to

•        Commit an in-doubt distributed transaction manually

•        Terminate a read-only transaction begun by a SET TRANSACTION statement

1)What is the difference between VARCHAR, VARCHAR2 and CHAR data types?

Character string values storage:

1. CHAR:

§ Stores strings of fixed length.

§ The length parameter s specifies the length of the strings.

§ If the string has smaller length it padded with space at the end

§ It will waste of a lot of disk space.

§ If the string has bigger length it truncated to the scale number of the string.

2. VARCHAR:

§ Stores strings of variable length.

§ The length parameter specifies the maximum length of the strings

§ It stores up to 2000 bytes of characters

§ It will occupy space for NULL values

§ The total length for strings is defined when database was created.

3. VARCHAR(2):

§ Stores strings of variable length.

§ The length parameter specifies the maximum length of the strings

§ It stores up to 4000 bytes of characters

§ It will not occupy space for NULL values

§ The total length of strings is defined when strings are given

2)What is the difference between UNION and UNION ALL operators?

Both UNION and UNION ALL concatenate the result sets of two different SQLs. They differ in the way they handle duplicates.

UNION performs a DISTINCT on the result set, eliminating any duplicate rows.

UNION ALL does not remove duplicates, and is therefore faster than UNION.

3)What is an Oracle database?

Oracle is an object-relational hybrid database - a relational database with added object-

oriented features - and, since Oracle 10g, a grid-enabled database - the ability to scale across multiple inexpensive servers to provide more processing resources.

Each release of the Oracle database brings new features to improve scalability, reliability, performance and availability. We'll be examining some of these new features in later Oracle tutorials

4)What is the difference between a "where" clause and a "having" clause? - "Where" is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command.

5) What is Normalization?

Normalization is the process of organizing the columns, tables of a database to minimize the redundancy of data. Normalization involves in dividing large tables into smaller tables and defining relationships between them. Normalization is used in OLTP systems.

6)What are different types of Normalization Levels or Normalization Forms?

The different types of Normalization Forms are:

•        First Normal Form: Duplicate columns from the same table needs to be eliminated. We have to create separate tables for each group of related data and identify each row with a unique column or set of columns (Primary Key)

•        Second Normal Form: First it should meet the requirement of first normal form. Removes the subsets of data that apply to multiple rows of a table and place them in separate tables. Relationships must be created between the new tables and their predecessors through the use of foreign keys.

•        Third Normal Form: First it should meet the requirements of second normal form. Remove columns that are not depending upon the primary key.

•        Fourth Normal Form: There should not be any multi-valued dependencies.

Most databases will be in Third Normal Form

7) What is De-normalization?

De-normalization is the process of optimizing the read performance of a database by adding redundant data or by grouping data. De-normalization is used in OLAP systems.

8) What is a Transaction?

A transaction is a logical unit of work performed against a database in which all steps must be performed or none.

9) What are ACID properties?

A database transaction must be Atomic, Consistent, Isolation and Durability.

•        Atomic: Transactions must be atomic. Transactions must fail or succeed as a single unit.

•        Consistent: The database must always be in consistent state. There should not be any partial transactions

•        Isolation: The changes made by a user should be visible only to that user until the transaction is committed.

•        Durability: Once a transaction is committed

10)What is a "constraint"?

          constraint is a rule which can not be voilated by end users.

Different types of constraints are available.

They are:-

1)default constraint:-which is used to define a default

value.

2)primary key:-the key which does not allows  duplication

and null values.

3)foreign key:-the key used to refer primary key defined

field in another table and it allows duplication.

4)null:-which allows NULL values.

5)not null:-which does not allows NULL values.

6)unique key:-which does not allows duplication but allows NULL values

7) Check A check constraint allows to state a minimum requirement for the value in a column.

CREATE TABLE emp512 (empno NUMBER ,

                  ename VARCHAR2(20),

                  sal   NUMBER CHECK (sal between 1000 and 20000)

);

11)What is a SQL view?

- An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doesn’t physically take any space. View is a good way to present data in a particular format if you use that query quite often. View can also be used to restrict users from accessing the tables directly.

12)What is  FOREIGN KEY?

A  foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDERS table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.

We can add foreign key in following three ways.

create table order320(ono number,oname number,foreign key(ono) references pri(cno));

create table for2(no number,name varchar2(45),constraints for2_pri2  foreign key(no)references pri1(no));(only in oracle)

create table for6(no number references pri1(no),name varchar2(45));

by alter: alter table sss add foreign key(no) references pri1(no);

13)What is Constraints?

Purpose Of Constraints:

            Constraints provide a means to check tables for referential integerity.SQL Server has four types of constraints and a default.

DEFAULT-indicates a column default value in the even that one is not provided by an insert operation.

NOT NULL-does not allow null values in the specified column

PRIMARY/UNIQUE-requires values in a specific column to be u

nique.

FOREIGN KEY-checks that each value in a particular column exists in a different table

CHECK-verifies that all stored values in particular column exist in a list that is specified.


USE OF CHECK:


mysql> create table test45(no int check(no between 0 and 50),name varchar(52));
Query OK, 0 rows affected (0.12 sec)


mysql> desc test45;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(52) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)


mysql> insert into test45(no,name)values(10,'vivek');
Query OK, 1 row affected (0.02 sec)


mysql> select * from test45;
+------+-------+
| no   | name  |
+------+-------+
|   10 | vivek |
+------+-------+
1 row in set (0.00 sec)


14)Diference between primary key and unique key:
     While the primary and unique keys enforce uniqueness in their columns, a clustered index is created in trhe column of the primary key,and a non clustered key index is created on the column of the unique key.Also, null values are not allowed with a primary key but upto one is allowed with a unique key.


15)What is trigger:
         Triggers are procedures that are created for a database to enforce rules of integrity.They are executed whenever an operation to modify data such as insert or delete is performed.  


DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object


DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency


DCL
Data Control Language (DCL) statements. Some examples:
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command
 The GRANT and REVOKE statements allow system administrators to create users and grant and revoke rights to MySQL users at four privilege levels:
Global level
Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table. GRANT ALL ON *.* and REVOKE ALL ON *.* will grant and revoke only global privileges.
Database level
Database privileges apply to all tables in a given database. These privileges are stored in the mysql.db and mysql.host tables. GRANT ALL ON db.* and REVOKE ALL ON db.* will grant and revoke only database privileges.
Table level
Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. GRANT ALL ON db.table and REVOKE ALL ON db.table will grant and revoke only table privileges.
Column level
Column privileges apply to single columns in a given table. These privileges are stored in the mysql.columns_priv table. When using REVOKE you must specify the same columns that were granted.
To make it easy to revoke all privileges for a user, MySQL 4.1.1 has added.


TCL
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use


mysql> create table savept(no int,name varchar(45),mailid varchar(45));
Query OK, 0 rows affected (0.07 sec)


mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)


mysql> insert into savept(no,name,mailid)values(101,'kar','kar0@gmail.com');
Query OK, 1 row affected (0.00 sec)


mysql> savepoint pt1;
Query OK, 0 rows affected (0.00 sec)


mysql> insert into savept(no,name,mailid)values(102,'viv','viv@gmail.com');
Query OK, 1 row affected (0.00 sec)


mysql> savepoint pt2;
Query OK, 0 rows affected (0.00 sec)


mysql> insert into savept(no,name,mailid)values(103,'abi','abi@gmail.com');
Query OK, 1 row affected (0.00 sec)


mysql> savepoint pt3;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from savept;
+------+------+----------------+
| no   | name | mailid         |
+------+------+----------------+
|  101 | kar  | kar0@gmail.com |
|  102 | viv  | viv@gmail.com  |
|  103 | abi  | abi@gmail.com  |
+------+------+----------------+
3 rows in set (0.00 sec)


mysql> rollback to savepoint pt3;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from savept;
+------+------+----------------+
| no   | name | mailid         |
+------+------+----------------+
|  101 | kar  | kar0@gmail.com |
|  102 | viv  | viv@gmail.com  |
|  103 | abi  | abi@gmail.com  |
+------+------+----------------+
3 rows in set (0.00 sec)


mysql> rollback to savepoint pt2;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from savept;
+------+------+----------------+
| no   | name | mailid         |
+------+------+----------------+
|  101 | kar  | kar0@gmail.com |
|  102 | viv  | viv@gmail.com  |
+------+------+----------------+
2 rows in set (0.00 sec)


mysql> rollback to  pt1;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from savept;
+------+------+----------------+
| no   | name | mailid         |
+------+------+----------------+
|  101 | kar  | kar0@gmail.com |
+------+------+----------------+
1 row in set (0.00 sec)


ADD INDEX:


Types of indexes
There are five types of indexes: unique and non-unique indexes, and clustered and non-clustered indexes, and system generated block indexes for multidimensional clustered (MDC) tables .

Unique and non-unique indexes

Unique indexes are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values.
Non-unique indexes, on the other hand, are not used to enforce constraints on the tables with which they are associated. 


Clustered and non-clustered indexes
Index architectures are classified as clustered or non-clustered. Clustered indexes are indexes whose order of the rows in the data pages correspond to the order of the rows in the index. This is why only one clustered index can exist in a given table, whereas, many non-clustered indexes can exist in the table.

mysql> desc bank1;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| bid   | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

1 row in set (0.06 sec)

mysql> create index individualindex on bank1(bid);

Query OK, 4 rows affected (0.12 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> desc bank1;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| bid   | int(11) | YES  | MUL | NULL    |       |

+-------+---------+------+-----+---------+-------+

Difference between union and join:

          The union operator combines the results of two or more quries into a single result set.But no.of columns must match in both /all the quries (and also the order) which are used for union.

Union-returns with no duplication order.

Union All-returns with duplicate rows.

Union is a set operator.We cant use union operator within a create view statement.


Difference between order by and group by:

ORDER BY alters the order in which items are returned.
GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc).
Aggregate functions are ('COUNT', 'MIN', 'MAX', and 'SUM')


DATA TYPES IN MYSQL:
MySQL uses many different data types, broken into three categories: numeric, date and time, and string types.
Numeric Data Types:

INT - A normal-sized integer that can be signed or unsigned.. You can specify a width of up to 11 digits.(4 bytes)

TINYINT - A very small integer that can be signed or unsigned. You can specify a width of up to 4 digits.(1 byte)

SMALLINT - A small integer that can be signed or unsigned. You can specify a width of up to 5 digits.(2 bytes)

MEDIUMINT - A medium-sized integer that can be signed or unsigned. You can specify a width of up to 9 digits.(3 bytes)

BIGINT - A large integer that can be signed or unsigned You can specify a width of up to 11 digits.(8 bytes)

FLOAT(M,D) - A floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a FLOAT.Size 4 bytes.

DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a synonym for DOUBLE. .Size 8 bytes

DECIMAL(M,D) - An unpacked floating-point number that cannot be unsigned. In unpacked decimals, each decimal corresponds to one byte. NUMERIC is a synonym for DECIMAL. .Size 16 bytes


Date and Time Types:
The MySQL date and time datatypes are:

DATE - A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31

DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59.

TIMESTAMP - A timestamp between midnight, January 1, 1970 and sometime in 2037. This looks like the previous DATETIME format, only without the hyphens between numbers; 3:30 in the afternoon on December 30th, 1973 would be stored as 19731230153000 ( YYYYMMDDHHMMSS ).

TIMESTAMP is 4 bytes Vs 8 bytes for DATETIME.
DATETIME is constant while TIMESTAMP is effected by the time_zone setting.
TIMESTAMP is better and run faster.



TIME - Stores the time in HH:MM:SS format.

YEAR(M) - Stores a year in 2-digit or 4-digit format. If the length is specified as 2 (for example YEAR(2)), YEAR can be 1970 to 2069 (70 to 69). If the length is specified as 4, YEAR can be 1901 to 2155. The default length is 4.

String Types:

Although numeric and date types are fun, most data you'll store will be in string format. This list describes the common string datatypes in MySQL.

CHAR(M) - A fixed-length string between 1 and 255 characters in length (for example CHAR(5)), default is 1. .( Length bytes)

VARCHAR(M) - A variable-length string between 1 and 255 characters in length; for example VARCHAR(25). You must define a length when creating a VARCHAR field. String length + 1 bytes

BLOB or TEXT - A field with a maximum length of 65535 characters. BLOBs are "Binary Large Objects" and are used to store large amounts of binary data, such as images or other types of files. Fields defined as TEXT also hold large amounts of data; the difference between the two is that sorts and comparisons on stored data are case sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a length with BLOB or TEXT.(String Length 2 bytes)

TINYBLOB or TINYTEXT - A BLOB or TEXT column with a maximum length of 255 characters. You do not specify a length with TINYBLOB or TINYTEXT.(String length 1 byte)

MEDIUMBLOB or MEDIUMTEXT - A BLOB or TEXT column with a maximum length of 16777215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT.(String length +3bytes)


LONGBLOB or LONGTEXT - A BLOB or TEXT column with a maximum length of 4294967295 characters. You do not specify a length with LONGBLOB or LONGTEXT. (String length )
ENUM - An enumeration, which is a fancy term for list. When defining an ENUM, you are creating a list of items from which the value must be selected (or it can be NULL). For example, if you wanted your field to contain "A" or "B" or "C", you would define your ENUM as ENUM ('A', 'B', 'C') and only those values (or NULL) could ever populate that field.

REGULAR EXPRESSION:
A regular expression is a set of characters that specify a pattern. The term "regular" has nothing to do with a high-fiber diet. It comes from a term used to describe grammars and formal languages.



Regular expressions are used when you want to search for specify lines of text containing a particular pattern. Most of the UNIX utilities operate on ASCII files a line at a time. Regular expressions search for patterns on a single line, and not for patterns that start on one line and end on another.


It is simple to search for a specific word or string of characters. Almost every editor on every computer system can do this. Regular expressions are more powerful and flexible.


Query to find all the names which contains '@g '
mysql> select * from bank where bmailid regexp '@g';

+------+-------+---------------+

| bid  | bname | bmailid       |

+------+-------+---------------+

|  100 | kvl   | kvl@gmail.com |

|  101 | kmb   | kmb@gmail.com |

|  102 | tnj   | tnj@gmail.com |

|  103 | mtr   | mtr@gmail.com |

+------+-------+---------------+

4 rows in set (0.00 sec)

mysql> select * from client where cname regexp 'hi';

+------+---------------------+-----------------+

| cid  | cname               | cemailid        |

+------+---------------------+-----------------+

|  113 | karthik             | kar@gmail.com   |

| 1000 | vivek SHiva krishna | krish@gmail.com |

+------+---------------------+-----------------+

2 rows in set (0.00 sec)

Query to find all the names ending with 'com'


mysql> select * from client where cemailid like '%com';
+------+---------+-------------------+
| cid  | cname   | cemailid          |
+------+---------+-------------------+
|  100 | vivek   | viv@gmail.com     |
|  111 | raja    | raja@gmail.com    |
|  112 | raji    | raji@gmail.com    |
|  113 | karthik | kar@gmail.com     |
|  116 | abi     | abi@y7mail.com    |
|  117 | abi1    | abi1@y7mail.com   |
|  118 | vivek20 | vivek20@gmail.com |
+------+---------+-------------------+
7 rows in set (0.00 sec)

mysql> select * from bank where bname regexp 'com$';

+------+-------+---------------+

| bid  | bname | bmailid       |

+------+-------+---------------+

|  101 | kcom | kmb@gmail.com |

+------+-------+---------------+

1 row in set (0.00 sec)


Query to find all the names starting with 'k'
mysql> select * from bank where bname regexp '^K';

+------+-------+---------------+

| bid  | bname | bmailid       |

+------+-------+---------------+

|  100 | kvl   | kvl@gmail.com |

|  101 | kmb   | kmb@gmail.com |

+------+-------+---------------+

2 rows in set (0.00 sec)





Query to find all the names starting with a vowel

mysql> select * from client where cemailid regexp '^[^aeiou]';

+------+---------------------+-------------------+

| cid  | cname               | cemailid          |

+------+---------------------+-------------------+

|  100 | vivek               | viv@gmail.com     |

|  111 | raja                | raja@gmail.com    |

|  112 | raji                | raji@gmail.com    |

|  113 | karthik             | kar@gmail.com     |

|  118 | vivek20             | vivek20@gmail.com |

| 1000 | vivek SHiva krishna | krish@gmail.com   |

| 1001 | kar mani menan      | kar@gmail.com     |

| 1002 | viv mani menan      | kar@gmail.com     |

+------+---------------------+-------------------+



Query to find all the names starting with a vowel and ending with 'I’
mysql> select * from client where cname regexp '^[aeiou]|i$';
+------+-------+-----------------+
| cid  | cname | cemailid        |
+------+-------+-----------------+
|  112 | raji  | raji@gmail.com  |
|  116 | abi   | abi@y7mail.com  |
|  117 | abi1  | abi1@y7mail.com |
+------+-------+-----------------+
3 rows in set (0.00 sec)

Pattern	What the pattern matches
^	Beginning of string
$	End of string
.	Any single character
[...]	Any character listed between the square brackets
[^...]	Any character not listed between the square brackets
p1|p2|p3	Alternation; matches any of the patterns p1, p2, or p3
*	Zero or more instances of preceding element
+	One or more instances of preceding element
{n}	n instances of preceding element
{m,n}	m through n instances of preceding element