Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Getting Start with Oracle: Data Retrieval, DML, DDL, DCL and Transaction Control

In this Article we will learn about oracle usage, contents, features and SQL

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.

shows the shape of database design

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
  • PL/SQL
  • FORMS
  • REPORTS

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

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;
shows the oracle sql*plus and how query is made

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:

shows the query through oracle database

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;
shows the query through database and how to delete data from tables

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:

  • Commit
  • 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

Sql>Commit;

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

Sql>ROLLBACK;

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:

  • GRANT
  • REVOKE

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!



I am well versed with Computer Programming languages and possess good working knowledge on software languages such as C, Java, PHP, HTML and CSS

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login