JNTU Kakinada to Change the pattern of Evaluation to Online

It was recently intimated to all the colleges under the affiliation of JNTU Kakinada, that the pattern of conducting and evaluating the end examinations: semesters are being changed to online system: that the papers from now will be valuated through an online systems. Although students may feel it embarrassing, the thing is that the papers are corrected manually, but the marks will be posted through an automated system, so that there won't be any more blunders in the evaluation. The principals of the affiliated colleges were informed to reflect the changes suggested accordingly. There are news that the changes will come into effect after 20th December 2013, while some say that this will apply to all the examinations held in and after december. However students are advised for the following while writing exams:

1. Use only black ink ball point pen for writing the exam
2. Use only HB pencil for diagrams
3. The Question numbers are to be marked in the boxes given on the left side.
4. Avoid strikes and over writings on the paper.

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: