Working with Tables in an Oracle database.

-

In this post I will be sharing with you the basics in table management on an oracle database.
Tables are the database segments that organize data in the form of rows and columns.
A table forms part of the objects created and managed in a schema.

What is a schema?
A schema is a collection of all objects owned by a particular user which includes tables

When a user is created, the schema for the user is automatically created.
As this is a new and blank environment, the new user will not have any objects (i.e. tables etc) unless it is created.

Let’s now understand how tables are created and managed.
Note 
This tutorial will be done using SQLPLUS command prompt.

Click here to view this tutorial using Oracle LIVE SQL



Tables can be created in an Oracle database using the following syntax:

CREATE TABLE table_name
( column1 data type (size),
column2 data type (size), ...,
column data type (size) );

Lets analyse one by one

·         table_name:  This is the name of the table to be created.
·         column: Refers to a name for a column in the table being created.
·         data type (size): Refers to the corresponding data type and size of the specified column. 
You should consider the following guidelines for naming tables:
·         It must begin with a letter.
·         It can be from 1 to 30 characters long.
·         May include letters, numbers, and the _, #, and $ symbols.
·         Cannot be a SQL reserved word, such as NUMBER or INDEX.
·         Must not be the same as any other database object in the same schema.

Creating tables is fairly straight forward. It requires you to however understand what is called a data type.

What are data types?
Data types are used to enforce integrity on the columns they are assigned to in the table. Take
a look at this example:
Below is a table that has four columns with their contents.
ID : Can only contain numbers. Cannot be empty
Name: Can only contain Text
Email: Can contain both text and numbers
Salary: Can only contain numbers. Cannot be empty

Now look carefully at the populated table called Workforce and see if the data have been placed right.
ID
Name
Email
Salary
001
Tayo
toluwole@yahoo.com
300,000
002
2345
kevin@aol.com
150,000
Ayodeji
Musa
Mau786@gmail.com
-        



Let’s see if your analysis is correct.
-       The ID column will reject Ayodeji. This is because it can only allow numbers.
-       The Name column will reject 2345. This is because it will not accept numbers only but text.
-       The Salary column will not accept empty value.

Now you understand what data types really do.
So let’s look at the various types of data types available on Oracle database

Types of Datatypes

1.    VARCHAR2: This is called Variable-length character data. It is used to store Characters.
2.    CHAR: This is called Fixed-length character. It works just like Varchar2. The only difference is that it does not release its unused storage space which is a waste. Varchar2 on the other hand releases unused storage space. Varchar2 is highly recommended.
3.    NUMBER: This is used to store numeric data
4.    FLOAT: This is an ANSI data type, for floating-point numbers
5.    INTEGER: This is equivalent to NUMBER, with scale zero.
6.    DATE: This is used to store dates
7.    LOB: This type of data type are used to store text, image, video and audio. The different LOB data types are:
-       CLOB – Stores variable-length character data.
-       NCLOB – Stores variable-length character data using a Unicode character set.
-       BLOB – Stores binary variable-length data in the database.
-       BFILE – Stores binary variable-length data external to the database on operating system files.

Now that we understand datatypes let us create our first table

We will create the table we used in our initial example. The table is called Workforce.

1) log on to SQLPLUS as the user hr

2) Create the following table:

create table workforce
(ID number(3) not null,
NAME varchar2(25),
EMAIL varchar2(25),
SEX varchar2(1),
SALARY number(10) not null);


Notice that the datatypes have values. I will explain this in very easy terms.

-       Number (3): This can store up to a maximum of 3 numbers. It will not accept 4 digits number.
-       Varchar2 (25): This will store characters with a maximum of 25 characters. Any character above that will be rejected.
We should be fine now. At this stage your table is empty with no data.

3) Describe the structure of your table to see that it has indeed been created.

 describe workforce 

Adding Columns in a Table
The ALTER TABLE statement is used to add columns to, or drop columns from a table.
The syntax for adding columns to a table is:

ALTER TABLE table_name ADD ( new_column_name data type (size));

-       table_name: Refers to the name of the table in which the column is to be added.
-       new_column_name: Specifies the name of the column to be added.
-       data type: Specifies the data type of the new column.
-       size: Specifies the size of the new column, if a size is applicable for the data type specified.
Now let’s add a new column called AGE to the WORKFORCE Table

ALTER TABLE WORKFORCE ADD (AGE NUMBER (2));
Let us describe our table once again to confirm the new column added


describe workforce 
Dropping Columns in a Table
 The syntax for removing columns from a table is:
ALTER TABLE table_name DROP COLUMN (column_name);
-       table_name: Specifies the name of the table.
-       column_name: Specifies the name of the column to be removed.

Now let’s drop the SEX column and describe the WORKFORCE table to confirm

ALTER TABLE WORKFORCE DROP (SEX); 

DESCRIBE WORKFORCE 


Specifying Constraints on a Table
Constraints provide rules to limit the acceptable data values for one or more columns in a table.
The following are the constraints that can be defined on a table:
-       NOT NULL: Specifies that a column in a table cannot contain null values.
-       UNIQUE: Specifies that each value in a column must be unique and a null value is allowed. An example of this is Email. No two persons can have the same email.
-       PRIMARY KEY: Specifies that each value in a column must be unique and not null. This means that there should be no duplicates. Primary keys are mostly used on the row ids that identify the entire columns. An example is a staff identity number which is unique to the staff.
-       FOREIGN KEY: Specifies that the value in a foreign key column can be either equal to a value in the primary key column or can be null.
-       CHECK: Specifies that a column value or set of column values must meet a specific condition.
Now let’s add constraints to our WORKFORCE  table. This is how we’ll issue the constraints.
-       We will give the ID column a  primary key constraints
-       We will give the EMAIL column a Unique key constraint
-       We will give the AGE column the check constraint. We are putting a check on the AGE so that it should prevent ages that are less than 18 from entering the table.

ALTER TABLE WORKFORCE ADD (CONSTRAINT PK_ID PRIMARY KEY (ID));

ALTER TABLE WORKFORCE ADD (CONSTRAINT UK_EMAIL UNIQUE (EMAIL));

ALTER TABLE WORKFORCE ADD (CONSTRAINT CK_AGE CHECK (AGE>18)); 

Now let’s insert into our table

Inserting into a Table
Inserting into a table is also fairly easy. Here’s the format:

INSERT INTO tablename
VALUES(… Values are entered here.....);

Here’s an example. I will be inserting into the WORKFORCE table:

SQL> INSERT INTO WORKFORCE (ID, NAME, SALARY, AGE, EMAIL)
VALUES (1,'TAYO', 350000,30,'tayo123@yahoo.com');

Don’t be confused yet. Please stay with me and let’s go through it one by one.

-       At the first line we stated the names of the columns in brackets.
-       At the second line VALUES command come first. NUMBER values are written normally while letters or characters are put in Quotes. Please take note of this else your insert will not be successful.
Please also note that you can only do this insert one by one. There are however other methods available but beyond the scope of this post.

Now it’s time for an exercise:

Exercise
Insert the following below into the WORKFORCE table. Insert them as you see it.

ID
NAME
SALARY
AGE
EMAIL
1
TAYO
350000
31
tayo123@yahoo.com
2
MUSA
200000
25
musa@aol.com
5
FEMI
150000
17
femo23@gmail.com
2
DEOLA
300000
27
ddol@yahoo.com
3
KABIRU
200000
20
tayo123@yahoo.com
4
NIKE
120000
19
Niky56@aim.com

• How many rows were successfully entered?
• What was the challenge experienced during data entry?

Solution
We will do the insert one by one as stated earlier so that we can see the errors where ever they are:
INSERT INTO WORKFORCE (ID, NAME, SALARY, AGE, EMAIL)
VALUES (1,'TAYO', 350000,30,'tayo123@yahoo.com');
COMMIT;

This went smoothly. Notice I used the command called COMMIT. This command is used to save the data you just inserted. It’s very important to do this after a successful insert or you will lose your data.
Let us see our first data. Run this query below:

Select * from workforce;
Now let’s insert the next row

INSERT INTO WORKFORCE (ID, NAME, SALARY, AGE, EMAIL)
VALUES (2, 'MUSA', 200000,25,' musa@aol.com ');
COMMIT;

Let’s also see the outcome of this data by running the select query again:2q1

Select * from workforce;

Let use continue with the inserts. It’s time to insert FEMI’s data:

INSERT INTO WORKFORCE (ID, NAME, SALARY, AGE, EMAIL)
VALUES (5,'FEMI', 150000,17,'femo23@yahoo.com');
COMMIT;

Aha!!. An error occurred. This is actually a violation of the check constraint we created earlier that it should only allow ages above 18yrs. It failed because we added an age of 17 which is lower than our required constraint.
Now let’s try the next row which is DEOLA’s data

INSERT INTO WORKFORCE (ID, NAME, SALARY, AGE, EMAIL)
VALUES (2,'DEOLA', 300000,27,'ddol@yahoo.com');
COMMIT;


And another error has occurred. This one also violated one of our constraints (primary key constraint on the ID column). What this means is that it cannot allow duplicate values in the ID column.

Let’s move on

INSERT INTO WORKFORCE (ID, NAME, SALARY, AGE, EMAIL)
VALUES (3,'KABIRU', 200000,20,'tayo123@yahoo.com');
COMMIT;
Yet another error has occurred. This one also violated one of our constraints (unique key constraint on the EMAIL column). What this means is that it cannot allow duplicate values in the EMAIL column.

INSERT INTO WORKFORCE (ID, NAME, SALARY, AGE, EMAIL)
VALUES (4,'NIKE', 120000,19,'Niky56@aim.com');
COMMIT;
This last row did not give us any error.
 So at the end of this exercise we only have three (3) records successfully inserted:

Select * from workforce;

Creating table with the CREATE TABLE.. AS command
Existing tables can be easily cloned with the create table .. as command. It reduces the hassles of recreating a table with its data type and table constraints.
As an exercise, let us create a copy/clone of the employees table. Let’s call the table emp_clone

CREATE TABLE emp_clone AS
select * from employees;
Lets now query our new table

Set lin 300
Select  * from emp_clone;
Let use create another table called NAME

CREATE TABLE  name  AS
Select first_name,last_name from employees;
Let’s query the table as see its contents

Set lin 300
Select * from name;
Now let use create a table using the data from 2 different tables
We will create a table called KYC. It will contain the customer’s full name and address.

CREATE TABLE KYC AS
select first_name,last_name,street_address
from employees e,departments d, locations l
where e.department_id=d.department_id
and
d.location_id=l.location_id
Let’s query the table as see its contents

Set lin 300
Select * from KYC;
DELETE, TRUNCATE and DROP command on tables

These three (3) commands are used to remove data from a table albite there function differently. Let us look at the one by one.

DELETE
The Delete command is used to remove rows from a table. A “where” clause can be used to only remove some rows. If no “where” condition is specified, all rows will be removed. After performing a delete operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

Let’s do a count of our workforce table

Select count(*) from workforce;
We have just 3 records in the table.
Let us now delete a row from the table

Delete from workforce
Where id=4;
Lets count the table to see if it has reduced to 2 records

Select count(*) from workforce;
Yes it has.

Please note that the commit command is required to ensure that this change is saved.
Also note that without the where clause, the entire table record is deleted.

TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.

As an example let us count the records in the KYC table, truncate it and then count it again.

Select count(*) from KYC;
Truncate table KYC;
Let’s do a count of the table record to see if it has truly deleted all records

Select count(*) from KYC;
Notice in this command we did not use the COMMIT command as it is not needed for this particular type of record removal.

DROP
The Drop command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. The operation cannot be rolled back.
This is the perfect table exterminator.

Let’s drop the emp_clone table

Drop table emp_clone;
Now let’s see if the table still exist

Select * from emp_clone;
We have succeeded in expunging the table.
As you can see the DROP command is really dangerous and should be carefully used.

I hope you found this blog post very interesting and informative.

If you are interested in further reading you can Click here

Stay tuned for more..

Comments

Post a Comment

Popular posts from this blog

Auditing Virtualization

-
Image
Virtualization is the process of creating virtual, representation of an entity, such as virtual applications, servers, storage and networks. It is the single most effective way to reduce IT expenses by reducing hardware footprint while boosting efficiency and agility for all size businesses. However good as it is, virtualization can create a single point of failure if poor implementation introduced a security flaw to the system. In this post I will be sharing with you how to audit virtualization.
Read more

How to Identify if the capacity of your FLASH storage device is genuine or counterfeit

-
Image
Have you at some time purchased a flash drive  or memory card that always stored corrupt data? You paid for a 64gig memory card and all you stored in it is simply just got corrupted. I have been a victim several times even with reputable retailers. These counterfeit flash storages are low capacity in nature, devices that have been maliciously altered so that they appear to have much more capacity than its original. An example is the 64 gig memory card I purchased which ended up having actual capacity of 2gig. So I actually paid for 2gig. Now I didn't get to know this on time because I actually copied a video file inside it as a test which checked out fine. The video file size was 1.5gb. I was able to play the video with no issue of corruption. Thus I "Assumed" the flash was genuine. Boy was I wrong. Later in the week I decided to do a major backup of my mobile phone since I was purchasing another one. After the backup, I formatted ...
Read more

Address Resolution Protocol (ARP): Understanding the basics

-
Image
Address resolution Protocol or ARP for short is basically used to resolve an IP address to a MAC address. A MAC address is a unique identifier for any device. And since we are talking networking today, The mac address we will be concentration on is that of a network device. Its important to note that, no two device can have the same mac address. Whenever a network device needs to communicate with another network device in a Local Area Network, it needs the MAC address of that device. This is where ARP comes to play. Lets take a look at the scenario bellow to clearly understand this concept: 1. There are 2 computers say A and B on the network. 2. They have both been assigned an IP address as seen below:        A: 192.168.0.1        B: 192.168.0.2 3. Computer A needs to communicate with computer B. 4. It looks at a list for the IP address to see if it has a corresponding MAC address. This list is called an ARP Cache. 5. If the en...
Read more