Database consists of some tables with some rows that has a relation with each other, this tables contains a lot of data that describe what the database is used for.
And the following image shows us the shape of database construction.
Figure 1: Shows the shape of database design
There are a lot Idioms that we must know about the database and we will explain that later.
What is ORACLE?
ORACLE is a Database management language; it’s used for managing the database with all its types. Oracle is used in a lot of big companies and organizations to manage and admin database.
Advantages of oracle:
- it’s the most secured database.
- It can manage a hundreds of millions of database with a high flexibility.
- It uses a lot of features that enable you to highly control database.
- it’s compatible with all database functions and procedures.
Oracle has a lot of fields like Oracle administration and Oracle development and in this article, we will have the part of Oracle developers, as no one can understand what Oracle administration means without understanding how Oracle create it’s applications.
Oracle development consists of four courses:
SQL is the database queries that used to deal with the database management. And that is the part that we will have in our article.
PL/SQL is the operation language that needed to interact with user.
FORMS AND REPORTS is the most important part of oracle development because it’s the biggest part and the part that needs a joker with a lot of skills to be able to learn and apply.
SQL consists of five types of queries:
- Data Retrieval.
- Data Manipulation Language.
- Data Definition Language.
- Transmission control.
- Data control language.
These five types is used to make operations on database and we will have them with small examples and clear explanation.
Data retrieval is acted in select query and this is the most famous query in oracle database because it’s used in displaying data from the database.
Select Query is used for:
- Choose row from a table in database
- Choose a column from a table in database
- To get data from two tables
As we know that oracle advantage had an existing database tables so fortunately we will have some examples on every query in SQL.
Starting with select query it must be written as follow:
Listing 1 : shows how to select data from table
Sql>select ename,empno from emp;
Figure 2: shows the oracle sql*plus and how query is made
Every query must end with (;) semicolon. That example is used to select employee name and employee number from the table which called emp.
Simply if we need to select all data from table then we type:
Listing 2 : shows code of selecting all data from a certain table
Sql>select * from emp;
This asters (*) means we need to select all data from table emp.
Data Manipulation Language (DML)
It’s used to make changes to database like: adding, changing and deleting any row from the table.
How to add a row on database:
Adding rows on database one of the main operations in database management and that would be through the order (Insert), for example:
Figure 3: shows the query through oracle database
Listing 3: shows insertion of data in a certain table
Sql> insert into dept(deptno,dname,loc) values(50,’education’,’cairo’);
Previous example shows how we use data insertion on oracle database as we use the first part (insert into) then the name of the table then name of columns then (values) then the data that we need to insert.
How to update row on database:
Updating database in oracle means editing in database and editing in the content data, moreover there is no updatable rows in tables that rows is identified as a primary rows
The following example shows how to make query to update existing data in an existing table:
Listing 4 : shows changing data in a specific table
Sql>update emp set deptno=55 where deptno=10;
This example explains the update process, we can see that he used update then table name which is emp then (set) the row we want to update which is deptno and the cursor which points at the row.
How to delete a row:
Deleting a row from a table in oracle database is an easy process, you just have to define which row you need to delete and then make the following query:
Listing 5: shows how to delete a certain data from a table
Sql>delete from dept where deptno=40;
Figure 4: shows the query through database and how to delete data from tables
In the previous example we deleted a row which deptno equal 40 from the table which called dept.
Data definition Language (DDL)
It has a lot of features but it handles the following main points:
- Create tables
- Alter table
- Drop table
This part of our article we will discuss how we manage and design the database from scratch starting with creating tables.
Example on creating table and explanation of details:
Listing 6 : shows how to create table through database
Sql>create table x(column number(4),column_2 varchar(15));
This example discusses how table is created.
First we type create table as shown before then name of the table (x) then the number of columns in table then a space then the type of table, in this example we created table called x with two columns called column and column_2 and type of first column is number and accept four digits and the type of second column is variable character and accept 15 character.
How to Alter table
To alter table or adding columns on an existing table we use the order Alter like that way:
Listing 7 : shows how to add a certain column in a specific table
Sql>Alter table x ADD (column_3 number(6));
In the previous example we added a column called column_4 and type of column is number and accept six digits.
There’s another option in Alter order is modifying as the following example:
Listing 8 : shows how to modify in setting of a certain table
In this example he used alter order to modify in a column setting he changed the number that accept six digits only to eight digits.
Sql>Alter table x modify(column_3 number(8));
How to drop table:
To drop table, it’s an easy query you must know but be careful this order after deleting is automatically save the change in database, so you must be sure that you want to delete that table example on deleting a table from oracle database:
Listing 9 : shows how to delete table with all the entire data
sql>drop table x;
We use drop table then type the name of table which in our example is (x).
Transaction Control (TC)
A transaction control order is a very simple order that mostly used in oracle database development and it’s necessary to all developers because it prevents a lot of mistakes that developers fall into.
Transaction control consists of two types:
- Roll Back
First commit order is used to save data that made by developer after he finishes all database queries so he can save his work after finishing.
And the following example is clearing how we use that order:
Listing 10 : shows how to save data after finishing work
Just type the word commit and the semicolon (;) then system will save the changes and you can turn off your system.
Second to stop the changes that developer made in the database we use the order roll back as it get back to the save point that we started from it.
This order is very important as it rescue a lot of developers from falling in whole mistakes in the system
The next example is defining how rollback order is written:
Listing 11 : shows how to get back to the last save point
It’s so simple to always remember just type rollback then the semicolon (;) then the system automatically will go back to the previous save point.
Data control language:
Data control language or (DCL) is used to control on the privileges of the users, it consists of two important types:
About Grant type, it gives the user any privileges we define.
This example shows how grant order give the privileges to users:
Listing 12 : shows how to give a certain user a privileges
Sql>grant select on emp to scott2;
In this previous example we give the user called scott2 the authority to make select query on table emp.
There are a lot of privileges in oracle database you can find it in any oracle tutorial but we just give the general idea and work and application can make you learn a lot.
Second is the revoke order as it can pull any privileges from any user we define as follow:
Listing 13: shows how to revoke or to prevent a certain user to do some privileges
Sql>REVOKE select,insert on dept from scott2;
In this example we clear that to pull any privileges from a user you type revoke then the privileges then table then from then name of the user.
And in this example we prevent the user scott2 to make select or insert order on table called dept.
Hope u like it! See you next time!