About SELECT Command

I would like to discuss about a very simple command, but the most used at the same time. Its the only display statement we have in our Oracle SQL. Hence it gets the most importance. Its the SELECT Query. Its called a Data Query Language Command: a Special set which is used to retrieve and display the rows from a table. We have different types of modifications in the command to suite the different modulations of the output: for example selective rows, differed names etc.,

There can have three types of operations basically a table can have:

1. Selecting a few Rows from the table.
2. Selecting a few Columns from the table.
3. Joining two tables and getting together the output.

There are three types of syntax modifications for the three types of retrievals:

SELECT FROM  

This gets you the suggested columns from the table. 

For example:  We have 

Student(num, name,total,average,section) 

and the query goes: 

SELECT num,name,section FROM Student;

SELECT /* FROM   WHERE ; 

                           

This gets you the selected rows for a table. For example in our previous Student table, if we want to retrieve the details of a student of number 101 from the table then, We have the following Query: 

SELECT * FROM Student WHERE num=101;

SELECT FROM ,.... WHERE ;

         

This gets you rows from two tables that share a common thing. Now that in our example if we have another table Marks 

Marks(num,sub1,sub2,sub3)

If we want to have the marks of the student with number and name printed along with the marks, then: The Query will be,

SELECT s.num,s.name,m.sub1,m.sub2 from Student s,Marks m where s.num=m.num;

Now that we have seen the ways to have our select query, we can also use some constants while displaying the values: using an operator || . The constant is enclosed under ' '.

Example: SELECT num,||' is '||, name FROM Student;

This statement displays number and name with 'is' in the middle. Another interesting this is the use of keywords in the middle. Oracle uses keywords like 'SYSDATE' , 'USER' etc., for specific things. For Example: 

SELECT USER FROM DUAL; 

displays the current working user, while 

SELECT SYSDATE FROM DUAL; 

displays the current date.

Constraints in Oracle SQL

The methods by which the database provides security and Integrity are the constraints that provide some restriction on data. There are mainly three types of Constraints in Oracle. they are:

• Integrity Constraints
• Domain Constraints
• Referential Constraints

Now we'll look at some keywords which have their impact on the database, as constraints. they are:

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK

NOT NULL: This constraint provides the restriction that : the data shouldn't be given a NULL, this implies that we shouldn't leave the column empty without giving any value. The syntax for using NOT NULL, is as follows: 

Syntax:  NOT NULL

Example: 

create table Teacher_Details(

         id number(5),

        name varchar2(15),

        class_Room number(3) NOT NULL

);

UNIQUE: This constraint provides the restriction that: the data we enter shouldn't contain duplication, this implies that there should not be any repeated values in the table. the syntax for the above can be in two ways: one straight after the data definition and other after the definitions at the end of the table.

Syntax: constraint UNIQUE

Example:

create table Teacher_Details(

         id number(5),

        name varchar2(15) constraint name_un UNIQUE,

        class_Room number(3) NOT NULL

);

PRIMARY KEY: Its like a combination of UNIQUE and NOT NULL. It provides a facility to the table: it uniquely identifies a row in a table.

syntax: constraint PRIMARY KEY

Example:

create table Teacher_Details(

         id number(5) constraint id_pk PRIMARY KEY,

        name varchar2(15),

        class_Room number(3) NOT NULL

);

FOREIGN KEY: Its like a link between to related tables, that in turn provides a parent child relationship like we see in inheritance. Yes, we can say that Oracle too implements inheritance with the help of foreign key constraint, but regarding other properties: I don't know!

It helps a lot in joining two tables, suppose that we divide students and classes into two tables, we can have a column in common called, class_num say. Now if we want to search for a class then we can join the tables while query using this foreign key. a foreign key attribute can't be deleted if there's an entry in the parent table and viceversa. hence in such cases, we first delete the entry in the child table and then go for the parent one.

Syntax: constraint REFERENCES ()

Example:

//Parent Table
create table Teacher_Details(

         id number(5) constraint id_pk PRIMARY KEY,

        name varchar2(15),

        class_Room number(3)

);

//Child Table

create table Class_Details(

        class_Room number(3) constraint class_fk REFERENCES Teacher_Details(class_Room),

        class_strength number(100),

        class_Status char(2) NOT NULL

);

Data types in Oracle SQL

You all know that we are aiming at one common thing in every programming language: that is data. yes, we target data in every programming methodology with the use of types of data. in the oracle too, we have data types to work with different types of data. they are:

• Number(n)
• Number(p,q)
• char(n)
• varchar2(n)
• varchar
• Date
• Nchar(n)
• Nvarchar2(n)

First of all, we'll go for the very first one in the line: Number. Have you noticed that there are two Number types i have shown? The first one has only a single parameter in the braces, while the second one has two entries. We'll go for number(n).

number(n) is used to store or define numbers or integers of n digits. This means that if we declare an attribute by the type Number(n), then the attribute can hold a value of at most n digits.

Example:

                          create table Student_Number(snum number(5));

However, when we add another value m to the Number(n) syntax, it would become Number(n,m) which is used to define float type or decimal type variables. the variable will be at most n digits with n-m-1 as left to decimal and m to the right of decimal.

For example, if we define Number(5,2) for an attribute, then the attribute can hold:

1. 5 total digits, including decimal
2. 2 digits as number
3. 2 digits as decimal
4. one is reserved for the decimal (.)

Example:

                         create table Item_Price(price Number(10,2));

The third type is character, it is obviously used to store alpha numeric characters, or strings. If we define an attribute as char(n), then the attribute can hold at most of n characters. the dis advantage of using char(n) for defining strings is that, if the data stored is either less or equal: the fixed block of memory is given. If our data is lesser than allocated, its sure a waste. Hence a new type has been developed, its varchar2(n). it has the answer for the above discussed problem. the data is given memory up to the needed, but not excess. this sure saves memory. Hence we generally declare strings with varchar2().

Example:

create table Student_Name(initial char(1), name varchar2(10), last_name varchar2(10));

If you question me that why we're talking all the way about varchar2() and not varchar() ? well the answer is like what we would see in Java, Reserved Keywords. In the same sound, varchar() is also reserved for future use. Date type is used to store the date, Oracle supports Date in the format "DD-MON-YY". Hence the date in that format will be stored.

Example:

create table Student_Register(initial char(1), name varchar2(10), last_name varchar2(10), doj date);

Nchar() and Nvarchar2() are similar to the char() and varchar2() in their properties and behavior  but the thing that changes is the data stored. Nchar and Nvarchar2 store data in uni code format.

DCL Commands and Introduction to Programming in Oracle

DCL commands are generally used in controlling the permissions of a designer on a table. there are the following commands in the DCL category:

• Grant: This command grants permissions to the user of a database. He can be given permissions to get connected, utilize the resources like manipulation commands (insert, delete etc.,)

•  Revoke: This is the opposite of the grant: it removes the permissions of an user on a database.

Summary of the Commands:

Creating User into Oracle:

The steps are:

1. Download a version of Oracle database from Oracle site ( I prefer Oracle 10xe).
2. Install The Database onto your disk, the system will start functioning as your database.
3. Now open the Oracle command-line.
4. The window will some what be similar to the windows command-line.

After you have obtained the above state, go for the following sequence of commands to have your own user and password. Oracle will provide by default an username and a password, it can be chosen while installing the oracle itself. The default user can be system/system or system/manager. My notation is here of the form username/password style.

1. connect
2. Enter username: system
3. Enter password: system/manager
4. now you should get the result as "connected." Now type in
5. create user identified by ;
6. you'll get a sign "user created." Now type in
7. grant connect,resources to
8. you'll get a sign "grant succeeded." Now type in
9. connect
10. username:
11. password:
12. you'll get "connected." 

To check whether you are in which username, type in "show user". You'll get 
USER is

To clear the screen, type "cl scr"

Remember, every time you are to use your oracle, you must connect with your username and password combination. We'll now create a small table Student, with the following attributes:

• snum : number
• sname : string (alpha numeric)
• class : string (pure character)
• subject : string (alpha numeric)

For now just think that alpha numeric can be expressed in the best way as using "varchar2" datatype in oracle. we'll discuss the datatypes in our next issue.

the command for the above table is as:

create table student(snum number(5), sname varchar2(10), class varchar2(5), subject varchar2(5));

the result should be " table created".

Now we'll insert some data into the table:

insert into student values (1101, 'rambabu', 'section1', 'java');

output: '1 row(s) inserted'

update table student set snum=101 where snum=1101;

output: '1 row(s) updated'

delete * from student where snum=101;

output: '1 row(s) deleted'

select * from tab;

output: "This shows the tables that are present in your user directory"

DML Commands

Previously, we've been talking about the various commands of sql used in oracle database programming. Now we'll continue our talks with the next category of commands. they are the DML commands. DML stands for Data Manipulation Language commands. the commands that come under this category are:

• Select
• Insert
• Update
• Delete

SELECT:

This command is called a query, its basically used to retireve the data present in the table ofour data base. It has the following full syntax:

Syntax:

SELECT column_names FROM table_name [WHERE condition];

This is in case of a varied number of rows. If we want to retrieve the entire rows of a given table, we simply replace our column_names by a * .

SELECT * FROM table_name [WHERE condition];

Example: 

1. SELECT sno,sname,sec FROM student;
2. SELECT* FROM student;

Although SELECT command can be used in more lengthy manner, we'll restrict our syntax to here for now. we'll have a complete syntax after we have further more commands.

INSERT:

This is a command that can be used for adding rows to a table. we use this to add a row having all the attributes to a table. the unknown attributes are left with a NULL, that can be replaced later.

The syntax of an INSERT command is:

Syntax:

INSERT INTO table_name VALUES ( value_1, value_2, value_3,...);

Example:

INSERT INTO student VALUES (1101, ram, s2);

UPDATE:

this command can be used to modify an old value of a table with a new value, obviously used in replacements of entries. The syntax for this command is:

Syntax:

UPDATE TABLE table_name SET column_value=new_value WHERE column_value=old_new;

Example:

UPDATE TABLE student SET sno=1102 WHERE sno=1101;

DELETE:

this command deletes a row or a number of rows from the table, if we want to retain the table structure clearing all the entries, we can use * in place of the column names. the syntax of the command will be as follows:

Syntax:

DELETE column_names FROM table_name WHERE condition;

Example:

DELETE sno,sname FROM student WHERE sno=1101;

Summary:

SQL Command Types

As we've seen in the last discussion, the heart of any database lies in the query language. This is similar to the case that sql is the heart of database systems. There are three types of SQL Commands

• Data Definition Language
• Data Manipulation Language
• Data Control Language

Data Definition Language:
This is what a database needs to be created: a basic set of commands used to create, and drop the data from the database. We use these commands with one flexibility, that they are auto saved commands. This is one we need to look at now:

There's a command called commit, in the Oracle's Sql list. This command saves the previously modified data. Since its all related to Data manipulation, every DML command must be accompanied by a commit command.

DDL command are auto saved in this sense: once they are executed, they are auto committed. Hence we need not explicitly save them.

The commands in the DDL class are:

CREATE: 
This command creates and entirely new table, we specify the attributes of the table along with the entity at this level. we also use the data types for each attribute.

Syntax:
                                  CREATE TABLE table_name (
                                              data_1 TYPE,
                                              data_2 TYPE,
                                              data_3 TYPE,
                                                        .
                                                        .
                                                        .
                                                        .
                                   ); 

Example: 

CREATE TABLE student (sno number(5), sname varchar2(10), course varchar2(5), avg number(5,2));       

One thing we have to observe here is that Oracle command line is dependent on the ';' used. Unless and until you put a ; all the thing written will be treated as a single statement only!

ALTER:

this command alters the things such as column names, types and new columns. we can use two commands in sync with this command, they are:

• ADD
• MODIFY
• DROP

ADD command adds a new attribute to the table, this can be used to append new columns to the created table.

MODIFY command can be used to change the data type or the name of the column, in an already created table.

DROP command can be used to simply drop a column form the table. The data associated with it, will also be deleted.

syntax: 

ALTER TABLE table_name ADD data_1 type1;

ALTER TABLE table_name MODIFY data_name new_type; 

ALTER TABLE table_name DROP column_name;

DROP:

this command simply drops the entire table, it deletes all the data and associated structures, and the deletion is unchangeable. This means once dropped, we cannot bring back the data. 

Syntax: 

DROP TABLE table_name;

A Summary of the Data Definition Language commands is given below:

Transforming Schema into Programming

Till now we've been discussing the various concepts and qualities of a relational databases. These were the most efficient and most used databases now a days. Now we'll throw some light on the practical database examples.

In general, a database has a programming environment apart from the traditional File management systems. This was first developed by IBM corporation in the 20th century, and later was adapted by other companies too! This programming language used for creating, manipulating and accessing database is called Structured Query Language (SQL). This was a query type language, this means the commands we give to the database is in the form of a query, a structured command that runs with the database System. 

The various database systems available in the market are:

• Oracle
• My SQL
• SQL Server
• DB 2
• MS access etc.,

The benefit of SQL is that its a universally accepted language for databases, this means that though the interfaces may change: all the database systems use the same language for the processing. Here our scope of learning is limited to oracle. After the completion of this, we'll move on to another database system: My SQL.

Coming to the History of Oracle, Its an American Software and Hardware Manufacturer co-Founded by its CEO Lary Ellison in 1977. It operates from California, USA. Its the 3rd Largest Corporation in the world in terms of its revenue. The First released product from it was Oracle 2, in 1979. From then, till now Oracle has been developing products for Database Systems. The Latest Product launched is Oracle 11g. Oracle has also won acquisition of Sun Microsystems in 2010. Now its the official owner of Java!

In SQL Programming, there are mainly 3 types of Language Commands. These are the major commands used in Creating, manipulating and accessing a database. They are:

• DDL :    Data Definition Language
• DML :   Data Manipulation Language
• DCL :   Data Control Language