ORACLE Datatypes Basics

1) CHAR
-> Char contain alphanumeric data.
-> Length of Char datatype is between 1 and 255 characters.
-> If you don't specify a length, a char column stores a single character by default.
-> Char datatypes stores data in the Fixed-Length Format.
-> Be aware, when using this data type, that spaces are padded to the right of the value to supplement the total allocated length of the column.
-> It can be declared as char(size) where size is the total length allowed for a value in a column defined as char.

2) DATE
-> Date datatype is used to store all date and Time information.
-> Oracle always allocates a Fixed - 7 bytes for a DATE column.
-> Oracle uses the default format of DD-MON-YY for entering and displaying.
-> Following Include as a Part of DATE are century, year, month, day, hour, minute and second.
-> It enables you to store dates in range of January 1, 4712 B.C. To December 31, 4712 A.D.
-> To view System Date and time we can use the SQL function called SYSDATE.

3) LONG
-> It can store alphanumeric strings.
-> It stores the data in Variable-Length Format.
-> Length of Long rise upto 2 giga bytes.
-> Long is used to store more than 2000 characters in a column.
-> You cannot use any of Oracle's built-in functions or operator with LONG column.
-> You can think of a LONG column as a large container into which you can store or retrieve data but not manipulate it.
Limitations of LONG data type are as under
i> Only one column in a table can have LONG datatype, which should not contain unique or primary key constraint.
ii> Column cannot be indexed.
iii> Procedures or stored procedures cannot accept LONG datatype as argument.

4) NUMBER
-> It can store Numeric data such as Zero, Positive or Negative, Fixed or Floating point data.
-> It accepts Positive and negative integers and real number and has from 1 to 38 digits of precision.
-> Scale which refers to number of digits to the right of the decimal point, which varies between (-84) to 127.
-> Format of declaring NUMBER datatype is NUMBER( Precision, Scale)
example:-
1) NUMBER(5) - It can store numeric data upto 5 digits i.e. 99999
2) NUMBER(8,2) - It can store numeric data of 6 Integers and 2 for decimal.
i.e. 999999.99

5) VARCHAR2
-> It stores Alphanumeric data values.
-> The field must be between 1 and 2000 characters long.
-> VARCHAR2 supports Oracle built-in functions and operators.
-> It supports a variable length character string.
-> Format of declaring VARCHAR2 IS VARCHAR2( size ).

6) VARCHAR
Same as Varchar2. Always use Varchar2 instead of varchar as it may not be supported in future.

7) BLOB (Binary Large OBjects)
-> Blob include documents, graphics, sound, video, actually any type of binary file you can think of.
-> A binary large object with a limit of 4GB in length.
-> When you want to associate a BLOB with a 'NORMAL' row, two choices are available to you.
i> Store the BLOB in an operating system file(such as an MS-DOS file) and stores the directory and filename in the associated table.
ii> Store the BLOB itself in the LONG RAW column.

8) CLOB (Character Large OBject)
-> A character Large object with a limit of 4GB in length.

9) BOOLEAN
-> Boolean variables are used in PL/SQL control structure such as IF-then-ELSE and LOOP statements. A Boolean value can hold true, false or NULL only.

10) LONG RAW
-> It can store binary data upto 2GB.
-> LONG RAW datatype is designed for BLOB storage. You can't use any of the built-in functions with a LONG RAW column.

11) RAW
-> It can store binary data upto 255 bytes.
-> Because of this storing restriction, a RAW column is less useful than a LONG RAW column.

12) ROWID
-> Hexadecimal string representing the address of a row in a table.

13) INTEGER
-> Specifies size of an INTEGER(n) digits wide.

14) BINARY INTEGER
-> The number type is stored in a decimal format which is optimized for accuracy and store efficiency. This datatype is used to store signed integer values, which range from TM2147483647 to +2147483647. It is stored in a 2's complement binary format. Loop Counter are often of type BINARY INTEGER.

INTRODUCTION to DML
By data manipulation language we mean:
->The retrieval of information stored in the database.
->The insetion of new information into the database.
->The deletion of information from the database.
->The modification of data stored in the database.
Thus, it is a language that enables users to access or manipulate data as organised by the appropriate data model.

There are basically two types of DML
i>Procedural DMLs
->It requires a user to specify what data is needed and how to get it.
ii>Non-Procedural DMLs
->It requires a user to specify what data is needed without specifying how to get it.

As SQL is Non-Procedural language we will switch on to Non-Procedural DMLs as it is easy to understand and became very efficient for new users to begin with it.


The category of DML contains four basic statements:
i>select - which retrieves rows from a table.
ii>Insert - Which adds rows to a table.
iii>Update - Which modifies existing rows in a table.
iv>Delete - Which removes rows from a table.



SELECT Statement
->To view all rows or some of the rows from a table or more than one table depending upon a userdefined criteria,this command is used.
->By default, select statement display all rows which matches the criteria but we can get the unique records by using keyword distinct with it.

syntax:
SELECT [DISTINCT ALL] FROM
WHERE

->keyword ALL is optional by default it consider ALL rows in table.

example:
1)SQL>select * from emp;
->It will display all rows of emp table including all feilds.we can customize the output by selecting the columns which are needed to us.
2)SQL>select empno,ename from emp;
->It will display all rows of emp table including empno and employee_name detail for an employee.
3)SQL>select * from emp
where
job = 'clerk';
->It will display all details of employee whose status is clerk.
4)SQL>select distinct ename from emp;
->It will display all unique name of employee if employee_name is repeated twice than it will display only ones.Thus it discards the duplicate records.


INSERT Statement
->INSERT command is used to insert one or more rows in a table.
(There are many of syntax for insert command but one mentioned as under is the only standard way to insert multiple rows.)

syntax:-
INSERT INTO
(........)
VALUES
(<&Fieldname1>......<&FieldnameN>)
->Where the Fieldname should be valid field for a table.
->Field having datatype char,varchar2 and Date kind of data should be written in single quota.

examples:
1)SQL>Insert into emp
(empno,ename,job)
values
('1','SHREY','MANAGER');
->Above command will insert data for one record, here as data are mentioned directly, so we have made use of single quota.
2)SQL>Insert into emp
(empno,job)
values
(&empno,'&amp;job');
->Here we have customize the insert command to take data for only two field that is empno and job for multiple records.
->When you don't want to type the command which is used last than simply press the slash to activate the command which is used by you last.
3)SQL>Insert into emp
values
(&empno,'&amp;ename','&job');
->Note in Above command we haven't declare the field in which data is to be entered, it is optional when we have to enter data for all fields in column.
4)SQL>Insert into emp
(empno,ename,job)
values
('5','VRAJ',NULL);
->The above command will create a row and assign value 5 for empno and vraj for ename and leave the column job.
->If we doesn't want to enter value for a particular field we can just type NULL in it place during defining the INSERT command. And just press enter while entering the value.
5)SQL>Insert into emp_bkup
(select * from emp);
->The above command will copies all the rows from table emp and insert it into the table emp_bkup, provided that the structure of emp and emp_bkup is same.
->The above command is efficient to run when we want to create backup copy of our data.


UPDATE Statement
->Update command is used to modify content of table, that to under satisfaction of particular criteria.

syntax:
UPDATE
SET =
WHERE
;
->Where Clause is optional.
->Fieldname is name of column whose contents are to be manipulated.

example:
1)SQL>Update emp
set job = 'ACCOUNTANT'
where
job = 'MUNIM';
->Above sql statement will Modify Job field for employee whose status is munim, it will update the status munim with accountant.
2)SQL>Update emp
set salary = salary + (salary * 10)
where
job = 'PROGRAMMER';
->Above statement will increase salary of employee by 10% whose status is programmer.


DELETE Statement
->DELETE command is used to delete one or more rows from the table.
Note:-No warnings are given before deleting rows so be careful while performing this operation.

syntax:
DELETE FROM
WHERE
;
->Table_name is name of table from which you want to delete record/s.
->Criteria is condition under which you want to delete record/s.

example:
1)SQL>Delete from emp
Where
empno = 4;
->Above statement remove record of empno 4.
->Only one record is deleted.
2)SQL>Delete from emp
Where
job = 'OPERATOR';
->Above statement remove record/s of those employee whose status is operator in the company.

DBMS Basic Concept

A)DATABASE MANAGEMENT SYSTEM(DBMS)
The software used for the management, maintenance and retrieval of the data stored in a database is called DBMS.
Database management systems frees the programmer from the need to worry about the organization and location of data. The primary goal of a DBMS is to provide an environment which is both convenient and efficient to use in retrieving and storing information.

B) A DATABASE STORAGE HIERARCHY
1) DATABASE
A collection of data files integrated and organised into a simple comprehensive file system, which is arranged to minimize duplication of data and to provide conenient access to information within that system to satisfy a wide variety of user needs.

2) DATA FILES
A file is a number of related records that are treated as a unit.
eg:- A collection of all employee records for one company would be an employee file.

3) RECORDS
A collection of related items of data that are treated as a unit.
eg:- An employee record would be collection of all fields of one employee.
->Record is sometimes referred as tuple.

4) FIELD
Individual element of data is called Field.
eg:- Bank cheque consist of following field cheque no, date, payee, numeric amt, signature, bank,etc.
->Field is sometimes referred as Data item.


C) Basic DBMS TERMS
1) RELATION
A relationship is an association among several entities
eg:- A cusst_Acct relationship associates a customer with each account that she or he has.

2) REDUNDANCY
If same piece of information is stored in database for number of times the database is said to be redundant. We should check our database should not be redundant as it wastes make our disk space, reduced efficiency of database, require more processing time, and their are chances of Inconsistency due to it in our database.
eg:-If we have to tables emp_details (contains details of employee) and Payroll(contains Payment details to employee), than if we include details of employee in payroll table, than it is said to be redundancy as same piece of information is repeated.

3) INCONSISTENCY
Inconsistency is various copies of the same data may no longer agree. Inconsistency occurs due to redundancy, so redundancy should be reduced . Though we cannot elimnates the redundancy , but we can reduced it upto certain level.
eg:- If we have details of employee stored in emp_details and payroll table than while updating information we should check that both tables are updated or not, if we update the address of one employee in emp_details and same details is not updated in payroll table, than database is said to be in inconsistent state.


4) PROPAGATING UPDATES
Propagating updates ensures users that any change made to record of one files, automatically made to other files. This process is known as Propagating updates. Where the term "Updates" is used to cover all the operations of creation, deletion and modification.
-we can avoid inconsistency by using propagating update technique.

5) INSTANCES
The collection of information stored in the database at a particular moment in time is called an instances of the database.

6) SCHEMES
The overall design of the database is called the database schemes. Schemes are changed infrequently, if at all.

7) USERS
There are four different types of database system users.

- Application programmers:- A person who prepares application program are called application programmer. Application programs operates on the data in all the usual ways: retrieving information, creating new information, deleting or changing existing information.

- SOPHISTICATED USERS:- Sophisticated users interact with the system without writing programs. Instead, they form their requests in a database query language. Each such query is submitted to a query processor whose function is to take a DML statement and break it down into instructions that the database manager understands.

- SPECIALIZED USERS:-Some sophisticated users write specialized database application that do not fit into the traditional data processing framework. Among these application are computer-aided design systems, knowledge-base and expert systems, systems that store data with complex data types
eg:-For Graphics and Audio data.

- END USERS:-Unsophisticated users interact with the system by invoking one of the permanent application programs that have been written previously.Thus they are persons who uses the information generated by a computer based system. Retrival is the most common function for this class of user.


D) KEYS concept in DBMS
A Key is a single attribute or combination of two or more attributes of an
entity set that is used to identify one or more instances of the set.

1) PRIMARY KEY:-A primary key is a field that uniquely identifies each record in a table. As it uniquely identify each entity, it cannot contain null value and duplicate value.
eg:-Consider the customer table, which has field :customer_number, customer_socialsecurity_number, and customer_address.here customer_number of each entity in customer table is distinct so customer-number can be a primary key of customer-table.

2) SUPER KEY :- If we add additional attributes to a primary key, the resulting combination would still uniquely identify an instance of the entity set. Such augmented keys are called superkey.
A primary key is therefore a minimum superkey.

3) CANDIDATE KEY:-A nominee's for primary key field are know as candidate key.
eg:-From above example of customer table, customer_socialsecurity_number is candidate key as it has all characteristics of primary key.

4) ALTERNATE KEY:-A candidate key that is not the primary key is called an Alternate key.
eg:- In above example, customer_socialsecurity_number is a candidate key but not a primary key so it can be considered as alternate key.

5) COMPOSITE KEY:- Creating more than one primary key are jointly known as composite key.
eg:-In above example, if customer_number and customer_socialsecurity_number are made primary key than they will be jointly known as composite key.

6) FOREIGN KEY:- Foreign key is a primary key of master table, which is reference in the current table, so it is known as foreign key in the current table. A foreign key is one or more columns whose value must exist in the primary key of another table.
eg:-Consider two tables emp(contains employees description) and emp_edu(contains details of employee's education), so emp_id which is primary key in emp table will be referred as foreign key in emp_edu table.

E) NORMALIZATION
It is important to understand the concept of normalization before switch on to the creation of table and its manipulation.

- NORMALIZATION is the process of grouping data into logical related groups.
- Normalization is the process of reducing the redundancy of data in a relational database.
- A database that is not normalized may include data that is contained in one or more different tables for no apparent reason. This could be bad for security reasons, disks space usage, speed of queries, efficiency of database updates, and may be most importantly, data integrity. A database before normalization is one that has not been broken down logically into smaller, more manageable tables.

BENEFITS OF NORMALIZATION
Normalization provides numerous benefits to a database.

some of the major benefits include

• Wider the overall database organization.
• The reduction of redundant data.
• Data Inconsistency can be avoided.
• A much more flexible database design.
• A better handle on database security.

INTRODUCTION to DDL

A database scheme is specified by a set of definition which are expressed by a special language called a Data Definition Language. The result of compilation of DDL statement is a set of tables which are stored in a special file called Data Dictionary.
-> DDL defines the structure of data.
-> DDL statements in SQL language are responsible for creating or modifying database structures such as tables, views, and indexes.


Let us now understand DDL statements and how it works on Oracle database.

TABLE
(A table consist of Rows and Columns. Table is a collection of related records that are treated as a unit. )

Convention for Naming a Table in Oracle
-> Each table owned by a Oracle account must have a unique name.
-> A table name cannot exceed 30 characters in length.
-> A table name must begin with an alphabetic character.
-> A table name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _(underscore).
-> A table name cannot be an SQL reserved word.
-> You can use uppercase and lowercase characters in naming tables; oracle is not case sensitive as long as table or column names are not enclosed in double quotes.
-> A table name should be descriptive.

Convention for Naming Column in Oracle.
-> Within a single table, a column name must be unique. However, you may use the same column name in different tables.
-> Like other database objects, a column name can be upto 30 characters long.
-> The column name must begin with an alphabetic character.
-> The column name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _(underscore). The name cannot include spaces.
-> A column name cannot be an SQL reserved word.
-> As with tables, be descriptive in naming a column. Descriptive column names help users understand the definition of each column.
-> An Oracle table may have upto 254 columns.

Commands Related To Table

1) Syntax to CREATE Table
CREATE TABLE
(
[column level constraint],
:
:
,
[Table level constraint]
);
Here,
-> Table_name is the name for the table.
-> Column_name1 through Column_nameN are valid column names.
-> Datatypes is a valid Oracle datatype specification.
-> Constraint_name is a optional, but it should be used in order to avoid invalid information to be entered in the database.
NOTE:-you can assign default value for field by using the keyword default and specifying default value for that field.
eg:-city varchar2(25) default 'AHMEDABAD'
(Though SQL is not case sensitive, but contents of table are case sensitive so it is good practise to write data always in uppercase).
Tip - CHAR, VARCHAR AND DATE kind of data should be written in single quota.

example
sql> create table emp
(
emp_no number (6) primary key,
emp_name varchar2(35),
emp_address varchar2(45),
emp_city varchar2(30),
emp_state varchar2(30),
emp_age number(2),
emp_sex char,
emp_department varchar2(30)
);
Table created.

2) Describe Table
-> This command will describe the table.
Syntax for describing table
sql> desc ;
or
sql> describe ;
example
sql> desc emp;


3) ALTER Table
-> Alter command is used to make modification after table has created.
-> Modification like to add column, to drop constraint like primary key,etc., However you cannot delete the column nor you can decrease the width of column.
Syntax to ALTER Table definition
ALTER TABLE
[ADD MODIFY DROP]
( ,
:
:
);

examples

-> To Add column in a table.
sql> alter table emp
add
(emp_phno number(10));

-> To Modify Table
sql> alter table emp
modify
(
emp_city varchar2(35),
emp_state varchar2(35)
);

-> To Drop Table's Primary key and other constraints.
general syntax
ALTER TABLE
[DROP ENABLE DISABLE]
CONSTRAINT ;

sql> alter table emp
drop primary key;

After successfully completion of above command you get the message table altered.


4) Command to Delete contents of Table
TRUNCATE
-> Truncate command will delete all the table contents by removing all the rows(records) from the table.
-> After the implementation of this command, only the structure of the table remains.
Syntax for Truncate command
sql> TRUNCATE TABLE ;
example
sql> truncate table emp;
-> It will delete all the rows of the emp table and only structure of the table remains.

5) Command to Delete Table
DROP
-> Drop command will delete the table contents and its structure.
Syntax for Drop command
sql> DROP TABLE ;
example
sql> drop table emp;
-> It will delete all the rows and the structure of emp table.

6) Command to Rename Table
-> Rename command will rename the table name.
Syntax for Rename command
Sql > RENAME TO
example
sql> Rename employee to emp;
-> Now the table name is renamed and you can manipulate it using name emp.

INTEGRITY CONSTRAINTS
An Integrity constraint is a trick used by oracle to prevent invalid data entry into the table. It is only a rules applied to restrict user to enter particular value. It prevent this by defining it at column-level or table-level constraint. The main difference between both is that column-level constraint is defined at the column level and cannot reference by any other columns in the table. A table-level constraint is defined at the table level and can reference to any of the table's columns.
NOTE:-not null and check constraint cannot be defined as table-level constraint.
Integrity constraint are categorized into three types they are as under
1) Domain Integrity Constraints
2) Entity Integrity Constraints
3) Referential Integrity Constraint.

I) DOMAIN INTEGRITY CONSTRAINTS
-> Domain integrity constraint Prevent invalid data entry by enforcing rules like NOT NULL and CHECK on the column.

NOT NULL
->By default, every column can contain null value. But, If we want to restrict the user to enter the value at any cost we should put not null constraint.
->In other words, not null field should always contains value.
example
create table emp
(
empno number(4) constraint ntnl not null,
ename varchar2(25),
job varchar2(25)
);
->here empno is column-level constraint and after implementation of the above command while user want to enter the value, he must have to enter empno. ntnl is the constraint name.

CHECK
-> check constraint is used to match the enter data with predifined criteria during designing of table.
-> As it check for predifined criteria it stops the invalid user to do mischief with the database.
-> Thus it helps in making database consistent by feeding reliable information.
example
create table emp
(
empno number(4) constraint ntnl not null,
ename varchar2(25),
job varchar2(25) constraint check(job in('clerk','manager'))
);
->here check constraint will look for job in clerk or manager and if enduser try's to enter job for another category an error code for it will be generated by sql.

II) ENTITY INTEGRITY CONSTRAINTS
-> Entity integrity constraint Prevent invalid data entry by enforcing rules like UNIQUE and PRIMARY KEY on the column.

UNIQUE
-> Unique constraint allowed null values, but it does not allowed duplicate values.
-> It may be composite upto 16 columns.
example
create table emp
(
empno number(4),
ename varchar2(25),
job varchar2(25),
constraint unino unique(empno)
);
->here unino is constraint name for table-level constraint definition and constraint unique is applied to empno, so after execution of above command user can enter only unique value or may not enter the value.

PRIMARY KEY
-> A primary key is a field that uniquely identifies each records in a table.
-> It should neither be duplicate nor null.
-> It may be composite upto 16 columns.
example
create table emp
(
empno number(4) constraint pkno primary key ,
ename varchar2(25),
job varchar2(25)
);
->here Primary key is created for empno so it will alone identifies each records in a table. pkno is a constraint name for column-level definition of constraint.


III) REFRENTIAL INTEGRITY CONSTRAINTS
-> Often, we wish to ensure that a value that appers in one relation for a given set of attributes also appears for a certain set of attributes in another realtion. This condition is called referential integrity.
-> To implement this, we should define the column in the parent table as a primary key and the same column in the child table as a foreign key referring to the corresponding parent entry.
->foreign key is a column or combination of column which refers to primary key of primary table.

example
create table emp
(
empno number(4) constraint pkno primary key ,
ename varchar2(25),
job varchar2(25)
);


create table education_detail
(
empno number(4) ,
degree varchar2(30),
foreign key (empno) references emp(empno)
);

-> here emp table contains the details of the employee while education_detail table consist of their achived degree's by their no. one-to-many relationship is formed here. As one employee have many degree's and even few employee have no degree's.

ON CASCADE DELETE
-> It is special facilty through which we can ensure that the row deleted in the master table will automatically deletes the row in the reference table.
for example if we have records of employee details than after deleting any one of their record will automatically deletes the corresponding entry in the reference table.
example
create table education_detail
(
empno number(4) references emp(empno) on-delete-cascade,
degree varchar2(30)
);