ORACLE Database

Oracle Database Fundamentals In this article we will mainly focus on basic database development using Oracle. We will learn how to create new tables, alter them, insert data into the database, update data, retrieve data, delete data and drop tables. We have lots to do, so let's get started.

We will start with the widely used Oracle example of an employee information database. We can store all the information in a table like the one below where each row will represent an employee, and each column will represent employee attributes.

Oracle Database Fundamentals - Creating Database Tables:

You need to log in to Oracle before executing any SQL statement. SQL is case-insensitive, even with the Oracle username and password!
The main data types used in Oracle are: varchar2(x) which can hold a variable length of string up to x characters long; number, an integer or real value up to 40 decimal digits; and date, which holds a date. For our example department table, the Dept field can be varchar2, while the EmpID and DeptID fields can be numbers. To create the table, the SQL statement is like this:

create table tablename (columnname type, columnname type ...);
create table department ( Dept varchar2(20), EmpID number, DeptID number);

If we execute the above SQL statement a table will be created with the name "department." To view the information of a table, the describe or desc statement can be used, like so:

describe department;
or
desc department;

So the SQL for the employee table would be:

create table employee ( "First Name" varchar2(20), "Last Name" varchar2(20), Address varchar2(60), Phone varchar2(15), Salary number, EmpID number, DeptID number);

Do you find any differences between the column names? The "First Name" and "Last Name" column names have a space in between the words. To use spaces in column names, you need to enclose them with quotation marks ("").
Here are some things to remember:
SQL select statements return column names in upper case.
If you want to mix upper cases and lower cases in the column name, then you need to enclose them with quotation marks ("").
Single quote marks are used to express a string in SQL. 'String' is a string but "Not a String" is not.
Oh no! I forgot to add the "Joining Date" column in the employee table. Don't worry. Oracle tables can be altered to add/delete columns or change column types. To add the "Joining Date" column we need to execute following SQL.

alter table employee add ("Joining Date" date);

We used varchar2 for the Phone column. If we want to change this column type to number then we need to modify the table using the following SQL:

alter table employee modify (Phone number);

To drop a column from a table we need to use the following statement.

alter table tablename drop column columnname;

If it's not specified, then columns are nullable by default, i.e. they can hold null values. To specify a column as not nullable add the words "not null" after the column type in create table or alter table statements, like so:

alter table employee add ("Joining Date" date not null);

Once we tried to create a table of about 1200 columns, but failed, because Oracle only supports 1000 columns in a single table!
Now we have some idea of how to create and alter tables. In the next section we will show you how to insert some data in our tables.
Oracle Database Fundamentals - Inserting Data (Page 3 of 5 )
The syntax for an SQL insert statement is as follows:

insert into tablename values (somevalue, somevalue, ...);
Or
insert into tablename (columnname, columnname, ...) values (somevalue, somevalue, ...);

The difference between above two insert statements is the columnname part. In the first statement values will be inserted in the order of columns as specified during creation. Let's look at an example. If we want to insert the first row of our department table as specified earlier then the insert statement would be:

insert into department values ('Sales',1,1);

Our department database table would look like this after this insertion.

insert into department values (1,'Sales',1);

Now if we use above statement, it will cause an error. Oracle is expecting the first element to be Dept, a varchar2, but it gets a number, 1. We can use the statement below to make Oracle think like us.

insert into department (DeptID, Dept, EmpID) values (1,'Sales',1);

It is better to mention the column names also, since sometimes we don't know the order of the columns.

Oracle Database Fundamentals - Selecting Data :

The syntax for selecting data from an Oracle table is

select columnname, columnname... from tablename;

If we want to select data from our department table we will use

select dept, empid, deptid from department;

This will select all data from a department table. There is a shortcut version of selecting all data from a table, and that is *.

select * from department;

The above two SQL statements are the same. To get the count of records (rows) in a table use the count() function.

select count(*) from department;

To get the maximum value of a column we can use the max() function. Use the min() function to get the minimum.

select max(salary) from employee;
select min(salary) from employee;

If we want to get the information about a particular employee whose first empid is 5 from our employee table, the SQL will be

select * from employee where empid=5;

In SQL we can add a where clause such as where empid=5 to get data that match the condition. There can be more than one where condition combined by 'and' and 'or'.
We want to get the full name of the employees whose deptid is 1 and who earn more than 5000.

select "First Name"' ''Last Name' from employee where deptid=1 and salary>5000;

In Oracle, is used for string concatenation as well as concat() function.
If you see the resulting column name then you can see it is the same as the expression we used in our SQL. We can give an alias for this expression using AS. For example:

select "First Name"' ''Last Name' as 'Full Name' from employee where deptid=1 and salary>5000;
or (without AS, it is the same)
select "First Name"' ''Last Name' 'Full Name' from employee where deptid=1 and salary>5000;

We can also give an alias to a table name in this way. We can sort the output of a SQL select using order by.

select "First Name", 'Last Name', salary from employee where salary>5000 order by salary;

The above SQL will select the first name, last name and salary from the employee table whose salary is more than 5000, and sort the result using salary in ascending order. The default order is ascending (asc).

select "First Name", 'Last Name', salary from employee where salary>5000 order by salary asc;
To put the list in descending order use desc.
select "First Name", 'Last Name', salary from employee where salary>5000 order by salary desc;

Oracle Database Fundamentals - Updating Data :

We can update a table using following statements.

update tablename set columnname=somevalue;
Or
update tablename set columnname=somevalue where conditions;

To update all the records use the first statement. To update particular records, add where conditions at the end of the update statements. To give an increment of 1000 to all our employees use:

update employee set salary=salary+1000;
update employee set salary=60000 where empid=1;

The above statement will change the salary of employee with empid 1 to 60000.

Deleting Data:

Data from tables can be deleted using the following statements.

delete from tablename;
delete from tablename where conditions;
delete tablename;
delete tablename where conditions;

To delete the record of an employee with the first name Tim use:

delete from employee where "First Name"='Tim';

To delete all records from the employee table use:

delete employee;

Dropping a Table:

To drop a table use:

drop table tablename;

To drop an employee table use:

drop table employee;

To commit changes in Oracle Database use commit and to roll back your changes use the rollback command.