Oracle on the NGS
Oracle on the NGS
In this section you will find basic information on how to get started with Oracle on the NGS. If you have never used Oracle and SQL before, or wish to test Oracle on the NGS without an Oracle account, please use the new automatic sign-on feature described below.
How to / FAQs
- logon [automatic sign on]
- finding the tables
- create table
- inserting data
- select basic information
The following is a list of FAQs for basic database operations. Further functionality and details can be found on the Oracle website. Please note that these examples are kept simple for ease of understanding and practise. Additionally, all Oracle keywords are in capital though SQL is NOT case sensitive.
Oracle SQL links
How to Logon
The automated sign on method eases the way in which this can be done. From the command line in ngs.rl.ac.uk simply type:
[ngs0228@ngs ~]$ sql_login
You will automatically be logged in to the NGS Oracle database. If this is your first time, an account will be created for you and some dummy tables will be created for you to look at. If you are a returning user, you be returned to your predefined Oracle account. This will hold all information that has been created and updated since its initial creation.
Selecting the tables
To first look at the data, you need to know what tables they are stored in. If you logged in via the automated logon process [sql_login] some tables have already been created for you to peruse. You can find what table (and views) are stored in a users' schema by typing the following:
Select * from tab;
For example:
SQL>
SQL> select * from tab;
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL>
SQL>
Creating a Table
Tables are used to hold data and are one of the key objects in an Oracle database. Each table holds at least one column of a specific data type.
A simplified format for creating the table is:
Create table <table_name> (
<column_name> <data_type> ,
….
);
Where
<table_name> is what you’d like the table to be called
<column_name> is what you’d like the column to be called
<data_type> is the type of information you’d like stored e.g. characters, date, numbers etc..
The following example is a table called employee that holds information about employees, i.e. their name; date of birth and current salary would be done as the following:
SQL>
SQL> CREATE TABLE emp (
2 ename VARCHAR2(30),
3 Sal NUMBER(7,2),
4 hiredate DATE);
Table created.
SQL>
The ename column is defined as a Varchar2 datatype that can take any character (and number), in this case the column will accept any name that’s 30 characters (or less) long.
The salary is defined as a number that can have 7 digits and two decimal places.
The hiredate is stored as a date. Oracle dates are generic and can be stored and retrieved in any format desired.
Inserting Data into a table
Once a table is created, data can then be inserted into it. This data can insert explicitly or implicitly.
Explicit insertion is when you define what columns you are insert data for. It is of the form:
Insert into <table_name>(<column_name> .. ..) values(<data for column> .. ..);
Where
<table_name> is the name of the table you’re inserting data into.
<column_name> is the name of the column you’re inserting data for
<data for column> is the actual information you’d like to store.
Example:
SQL>
SQL>INSERT INTO emp (sal,ename, hiredate)
2 VALUES(40000,'David Moyes', TO_DATE('01/01/1960','dd/mm/yyyy'));
1 row created.
SQL>
Inserting implicitly is not having to specify the columns. It’s assumed you know the order of how the columns are defined so just insert the data in the same order. This is of the form:
Insert into <table_name> values (<data for column> .. ..);
Where
<table_name> is the name of the table you’re inserting data into.
<data for column> is the actual data you’d like to store.
Example:
SQL>
SQL>INSERT INTO EMPLOYEE VALUES('David Moyes',40000,
2 TO_DATE('01/01/1960','dd/mm/yyyy'));
1 row created.
SQL>
Select Information from a Table
Once information has been placed in a table, it can then be viewed by the Select command. It’s possible to limit what you see in the table by the use of a where clause. This can be done in the following way:
Select <column_name>,…
from <table_name>
where
<column_name> = <data>;
Where
<table_name> is what you’d like the table to be called
<column_name> is what you’d like the column to be called
<data> is whatever specified information you would like to select.
An example is
SQL>
SQL> SELECT ename, sal,hiredate
2 FROM emp
3 WHERE ename = 'David Moyes';
NAME SALARY DATE_OF_B
------------------------------ ---------- ---------
David Moyes 40000 01-JAN-60
SQL>
It’s also possible to list all columns in a table instead typing them out explicitly by using the wildcard *. E.g.
SQL>
SQL> SELECT *
2 FROM emp
3 WHERE ename = 'David Moyes';
NAME SALARY DATE_OF_B
------------------------------ ---------- ---------
David Moyes 40000 01-JAN-60
SQL>