The Powerful Oracle RDBMS

Every firm must need data source management system software for shop and control the important points to meet the requirements like a corporate and business must collect and gaze after their employee data. The majority of the companies today use a relational data base to speed up their information program, for virtually any program the main function of databases product is data collection, data space for storing then retrieves appropriate information. In a relational databases method the info's are locked in well defined structured buy, to ensure that we can easily certainly obtain the relevant details from the database. Relational database managing process functions two procedures for generate an effect for that user that are reasonable operation and bodily operations. First, in a very logical operation you specify their queries for instance your request is any employee name or add a staff details towards the database. Then inside physical operation RDBMS determines the kind of queries and carries out the operation, for instance after user query the database first readily index to discover the request and study your data to the memory and after that perform many steps before produce an output for the user.

Oracle is currently the leading RDBMS and it is the database of choice for the majority of the world's largest producers of data. For instance, I recently designed a process depending on SQL and PL/SQL (Procedural Language SQL -Oracle's proprietor extension on the SQL language meant to provide with Oracle products) which includes loaded over three billion records every day. The Oracle database exploits advanced technological features as part of its dedication to produce robust Business Intelligence solutions.

The migration process from your current non-relational database towards the new relational database should be discussed in detail to supply a thorough comprehension of your exact needs to the service provider as one of the first steps inside the entire process. Then, the new relational database needs to be designed as well as set around preserve your organization application logic.

The Oracle RDBMS is definitely not really a free database. Licensing costs for giant multi-processor clusters may be hundreds of thousands of dollars annually. For this and also other reasons, the companies that use it tend to have enterprise-class databases. They have a much a dedicated database administrator to make sure their data is secure and properly maintained. The total number of installs could possibly be lower than those of MySQL, but a significantly higher amount of those could have an avid administrator. Of the enterprise class databases, Oracle has the largest business where there are nearly always positions available for experienced DBAs and developers.

ORACLE SQL: STRING FUNCTIONS

SQL string functions are used for string manipulation. The following functions are commonly used string functions:

1. CONCAT(): Return concatenated string

select concat('Ashok','Kumar') from dual;                                                                                       
CONCAT('AS                                                  
----------                                                         AshokKumar                      


2. INSTR(): Return position of first occurrence of a substring within a string

select instr('ASHOKKUMAR','A') FROM DUAL;                                                                                       
INSTR('ASHOKKUMAR','A')                                       
-----------------------                                             
                      1

select instr('ASHOKKUMAR','A',3) from dual;                         
                                                                
INSTR('ASHOKKUMAR','A',3)                                           
-------------------------                                           
                        9

The above example will give the give the position of 'A' within string 'ASHOKKUMAR' starting from 3rd character.


3. LOWER(): Converts a string to lowercase

select lower('Ashok Kumar') from dual;                                                                                        
LOWER('ASHO                                                         
-----------                                                         
ashok kumar   


4. LENGTH(): Return the length of a string in bytes

select length('Ashok Kumar') from dual;                                                                                         
                                                                 
LENGTH('ASHOKKUMAR')                                                
--------------------                                                
                  11 


5. REPLACE(): Replace occurences of a substring within a string with another substring

select replace('AshokKumar','Kumar','KUMAR') from dual;             
                                                                                                                                     
REPLACE('A                                                          
----------                                                          
AshokKUMAR   


6. REVERSE(): Reverse all the characters in a string

select reverse('Ashok Kumar') from dual;                                                                                        
REVERSE('AS                                                         
-----------                                                         
ramuK kohsA


7. LPAD(): Return the string, left-padded with the specified string 

select lpad('Ashok Kumar',15,'*') from dual;                                                                                    
LPAD('ASHOKKUMA                                                     
---------------                                                     
****Ashok Kumar  

In the above example the total length of the returned string will be 15


8. LTRIM(): Removes leading spaces from a string

select ltrim('    Ashok Kumar') from dual;                                                                                      
LTRIM('ASHO                                                         
-----------                                                         
Ashok Kumar  


9. SUBSTR(): Return the specified substring within a string
select substr('Ashok Kumar',4,2) from dual;                                                                                     
SU                                                                  
--                                                                  
ok    

In the above example 4 is the starting position from a string and 2 is the total number of characters to be selected


10. RPAD(): Return the string, right-padded with the specified string 

select rpad('Ashok Kumar',15,'*') from dual;                                                                                    
RPAD('ASHOKKUMA                                                     
---------------                                                     
Ashok Kumar****    


11. RTRIM(): Removes trailing spaces from a string
select rtrim('Ashok Kumar        ') from dual;                                                                                  
RTRIM('ASHO                                                         
-----------                                                         
Ashok Kumar 


12. TRIM(): Removes leading and trailing spaces from a string 

select trim('      Ashok Kumar      ') from dual;                   
                                                                 
TRIM('ASHOK                                                         
-----------                                                         
Ashok Kumar    


13. UPPER(): Converts a string to uppercase
select upper('Ashok Kumar') from dual;                                                                                                   
UPPER('ASHO                                                         
-----------                                                         
ASHOK KUMAR      


I hope the above examples in support of each string function will help you understand better.

ORACLE SQL: TOP 10 BASIC SQL QUERIES/STATEMENTS(MOST COMMONLY USED)

1. Creating a table

create table employee
(
emp_no number(4),
emp_name varchar2(30),
sex varchar2(1),
age number(3),
desg varchar2(10),
sal number(8),constraint emp_pk primary key(emp_no)
);

The above statement will create a table "employee" (with primary key on column 'emp_no') having no rows in it.

2. Inserting records in a table

insert into employee
(emp_no,emp_name,sex,age,desg,sal)
values
(1,'Adam','M',22,'Clerk',8000);


insert into employee
(emp_no,emp_name,sex,age,desg,sal)
values
(2,'Amy','F',23,'Clerk',8500);

insert into employee
(emp_no,emp_name,sex,age,desg,sal)
values
(3,'Casper','M',25,'Salesman',10500);

insert into employee
(emp_no,emp_name,sex,age,desg,sal)
values
(4,'Daniel','M',28,'Manager',18000);

insert into employee
(emp_no,emp_name,sex,age,desg,sal)
values
(5,'Erwin','M',27,'Salesman',12000);

After inserting records, table data will look like

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         1 Adam         M         22 Clerk            8000
         2 Amy          F         23 Clerk            8500
         3 Casper       M         25 Salesman        10500
         4 Daniel       M         28 Manager         18000
         5 Erwin        M         27 Salesman        12000

3. Counting the number of records in a table

select count(*) from employee

The above query will give you the result as 5.


4. Viewing all records from a table(The most commonly used query)

select * from employee;

Below is the result of this query:

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         1 Adam         M         22 Clerk            8000
         2 Amy          F         23 Clerk            8500
         3 Casper       M         25 Salesman        10500
         4 Daniel       M         28 Manager         18000
         5 Erwin        M         27 Salesman        12000

You can also use sorting using ORDER BY clause:

select * from employee order by sal;

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         1 Adam         M         22 Clerk            8000
         2 Amy          F         23 Clerk            8500
         3 Casper       M         25 Salesman        10500
         5 Erwin        M         27 Salesman        12000
         4 Daniel       M         28 Manager         18000

You can also see the data in descending order of salary by using DESC:

select * from employee order by sal desc;

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         4 Daniel       M         28 Manager         18000
         5 Erwin        M         27 Salesman        12000
         3 Casper       M         25 Salesman        10500
         2 Amy          F         23 Clerk            8500
         1 Adam         M         22 Clerk            8000

5. Viewing selected records from a table

Below is the query to view employee details having salary greater than 10000:

select * from employee where sal>10000;

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         3 Casper       M         25 Salesman        10500
         4 Daniel       M         28 Manager         18000
         5 Erwin        M         27 Salesman        12000

6. Viewing selected records from a table using multiple conditions

Below is the query to view employee details having salary greater than 10000 and their designation should not be 'Manager'

select * from employee where sal>10000 and desg <> 'Manager';

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         3 Casper       M         25 Salesman        10500
         5 Erwin        M         27 Salesman        12000

7. Updating data in a table

If you want to change the age of employee 'Erwin' to 24 then fire the below query

update employee set age=24 where emp_name = 'Erwin';

After updating, the table data will be

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         1 Adam         M         22 Clerk            8000
         2 Amy          F         23 Clerk            8500
         3 Casper       M         25 Salesman        10500
         4 Daniel       M         28 Manager         18000
         5 Erwin        M         24 Salesman        12000

8. Deleting records from a table

Use following query to delete selected records from a table

delete from employee where emp_name = 'Casper';

After deleting, the table data will be

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         1 Adam         M         22 Clerk            8000
         2 Amy          F         23 Clerk            8500
         4 Daniel       M         28 Manager         18000
         5 Erwin        M         24 Salesman        12000

9. Viewing records from a table without knowing exact value of columns

Fire below query if you want to search employees whose name starts with 'A'

select * from employee where emp_name like'A%';

    EMP_NO EMP_NAME     S        AGE DESG              SAL
---------- ------------ - ---------- ---------- ----------
         1 Adam         M         22 Clerk            8000
         2 Amy          F         23 Clerk            8500

10. Viewing selected columns from a table

If you want to see employee number, employee name and age then fire the below query

select emp_no,emp_name,age from employee;

    EMP_NO EMP_NAME            AGE
---------- ------------ ----------
         1 Adam                 22
         2 Amy                  23
         4 Daniel               28
         5 Erwin                24