HOW TO WRITE INSERT STATEMENT IN ORACLE SQL

INSERT:  Insert the records into the table.

We have two methods of inserts

a.       Value Method

b.       Address Method

a. Value Method: In this method, we are going to insert the values into the row by one by one.

SYNTAX: insert into <table_name) values (value1, value2, value3 …. Valuen);

EX:

Insert Values:

1	Visu	100
2	Vasu	200

                                i.            insert into student values (1, ’VISU’, 100);

                               ii.            insert into student values (2, ’VASU’, 200);

Output: For checking the output we are going to write the select statement. (select * from student;)

NO	NAME	MARKS
1	Visu	100
2	Vasu	200

     To insert a new record again you have to type entire insert command, if there are a lot of records this will be difficult. This will be avoided by using address method.

b. ADDRESS METHOD: This will prompt you for the values.

SYNTAX: insert into <table_name) values (&col1, &col2, &col3 …. &coln);

EX: Insert Values in the prompt:

3	Manoj	300
4	Aisu	400
5	Mohan	500
6	Manvi	600
7	Namu	500
8	Susu	300

 insert into student values (&no, '&name', &marks);

Output:  Prompt will pop up as

NO:

NAME:

MARKS:

In that pop up’s we are going to enter the values. After entering the values the table looks like this, for checking the output we are going to write the select statement. (select * from student;)

NO	NAME	MARKS
1	Visu	100
2	Vasu	200
3	Manoj	300
4	Aisu	400
5	Mohan	500
6	Manvi	600
7	Namu	500
8	Susu	300

c. INSERTING DATA INTO SPECIFIED COLUMNS USING VALUE METHOD:

Syntax: Insert into <table_name)(col1, col2, col3 … Coln) values (value1, value2, value3 …. Valuen);

Ex:  Insert Values:

9	Thanu	200
10	AJ	100

        i.            SQL> insert into student (no, name, marks) values (9, ’Thanu’,200);

       ii.            SQL> insert into student (no, name, marks) values (10, ’AJ’,100);

Output: For checking the output we are going to write select statement. (select * from student;)

NO	NAME	MARKS
1	Visu	100
2	Vasu	200
3	Manoj	300
4	Aisu	400
5	Mohan	500
6	Manvi	600
7	Namu	500
8	Susu	300
9	Thanu	200
10	AJ	100

d. INSERTING DATA INTO SPECIFIED COLUMNS USING ADDRESS METHOD: This will prompt you for the values.

Syntax: Insert into <table_name)(col1, col2, col3 … coln) values (&col1, &col2 ….&coln);                                                                              Insert Values:

11	MK
12	MD

EX: SQL> insert into student (no, name) values (&no, '&name');

NO:

NAME:

In that pop up’s we are going to enter the values. After entering the values the table looks like this, for checking the output we are going to write the select statement. (select * from student;)

NO	NAME	MARKS
1	Visu	100
2	Vasu	200
3	Manoj	300
4	Aisu	400
5	Mohan	500
6	Manvi	600
7	Namu	500
8	Susu	300
9	Thanu	200
10	AJ	100
11	MK	(null)
12	MD	(null)

HOW TO WRITE CREATE STATEMENT IN ORACLE SQL

1.CREATE: Creating the tables with column names.

SYNATAX: Create table <table_name>(col1 datatype1, col2 datatype2……coln datatypen);

Ex: create table student (no number (2), name varchar (10), marks number (3));

Output:  For checking the output we are going to write the select statement. (select * from student;)

NO

NAME

MARKS

What Are The Languages Available in Oracle SQL.

v  DATA DEFINITION LANGUAGE:(DDL)

                Set up, changes and removes data structure from tables.

DDL STATEMENTS:

1.       Create

2.       Alter

3.       Drop

4.       Rename

5.       Truncate

6.       Comment

v  DATA MANIPULATION LANGUAGE: (DML)

                It retrieves data from the database, enters new rows changes existing rows and removes unwanted rows from the tables in the database.

DML STATEMENTS:

1.       INSERT

2.       UPDATE

3.       DELETE

4.       MERGE

v  DATA QUERY LANGUAGE: (DQL)

SELECT:  The SQL SELECT statement returns a result set of records from one or more tables.

A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used data query language (DQL) command. As SQL is a declarative programming language, SELECT queries specify a result set but do not specify how to calculate it. The database translates the query into a "query plan" which may vary between executions, database versions, and database software. This functionality is called the "query optimizer" as it is responsible for finding the best possible execution plan for the query, within applicable constraints.

The SELECT statement has many optional clauses:

1.       WHERE specifies which rows to retrieve.

2.       GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group.

3.       HAVING selects among the groups defined by the GROUP BY clause.

4.       ORDER BY specifies an order in which to return the rows.

5.       AS provides an alias which can be used to temporarily rename tables or columns.

v  DATA CONTROL LANGUAGE: (DCL)

                It Provides or removes access rights to the oracle database and the structure within it.

DCL STATEMENTS:

1.       GRANT

2.       REVOKE

v  TRANSACTION CONTROL LANGUAGE:(TCL)

This will be used to save the work.

TCL STATEMENTS:

1.       COMMIT

2.       ROLLBACK

3.       SAVEPOINT

What PATIENCE will give to you?

PATIENCE

There was a rich old noble who lived in a great palace. There also lived nearby a poor man in a dilapidated hut, who subsisted on crumbs of food cast away by others. But he was ever cheerful, and never complained of his ill-luck.

Once it so happened that the poor man had nothing to eat for a long while. So he went to the rich noble for help. The old noble received him kindly and asked what for he had come. The poor man said that for days he had nothing to eat and that he would be happy if he was given some food. “Is that all!” said the noble. “Come, sit down!” Then he called-out; “Boy! A very important guest has come to dine with me. Ask the chief to make ready the dinner at once, and bring some water to wash our hands.”

The poor man was surprised. He had heard that the noble was a very kind man, but he did not expect such a ready welcome. He was all praise about his host. The noble at once interrupted him and said, “Don’t mention it, my friend. Let us sit down for the feast.” And the old noble began to rub his hands as if some water was poured on them and asked the poor man why he did not wash his hands.

The poor man found no boy or water but decided that he should do what he was told, and so he pretended to wash his hands likewise. “Now let us sit down to dinner,” said the noble, and began to order various delicious dishes. But there was no trace of any food or even a single bearer.

Then the noble said to the poor man, “We have such wonderful feast before us. Enjoy yourself, my friend. You must finish all these fine dishes.” And the noble pretended to eat from imaginary plates.

The poor man was faint with hunger, but kept his wits. He did not allow despair to overcome him. He also pretended to eat from the empty table. The noble now and then exclaimed, “What a delicious soup! The curry is wonderful, isn’t it my friend?” The poor man replied, “Sure, sure!” “Then why not have some more,” and the noble pretended to dish out some imaginary curry. Likewise, he pressed more and more imaginary dishes on the poor man and asked him if they tasted all right.

Though desperately hungry, the poor man thanked his host profusely and said that he had never eaten such a glorious feast in his life. He did not betray a sign of remorse. He kept on maintaining the face cheerfully without the least affectation, as though everything was real.

The noble was a generous person. He was a man of charitable disposition. He wanted to test whether the poor man would give way to despair. He had heard of his reputation that he never lost patience. He thought that such a contented, cheerful person as this poor man should not starve and suffer from poverty. But he had his doubts. So he himself wanted to test him. Now he found that, all that was said about him was true.

The noble then clapped his hands and a retinue of servants came in with all the delicious dishes he had been mentioning. An elaborate dinner was laid on the table. This time the poor man did not have to pretend. He now ate heartily with the noble.

After they had finished their meals, the noble said, “Friend, you are a man of infinite patience. You know well how to make best of everything and bear adversity cheerfully. You are the man I was looking for to manage one of my farms. You should live with me hereafter.”

Thereupon the poor man did not have to suffer any more from poverty.

This story has several lessons for the common man to learn. When the poor man went to the rich man, he did not ask for any charity so that he might dispense with begging for some days. This shows that he was not greedy. He lived in the present. He wanted some food and he asked for only that. Now, if he had asked for some money, he would have got it, and would have spent it in a few days, only to revert back to his former poverty. He did not ask any more than what he needed immediately, and this paved the way for his good luck in being employed in the rich man’s farm.

When the poor man was harassed by his host with imaginary dishes, he did not lose his patience in spite of his extreme hunger. If he had done so, he would have been asked to get out and would have lost his dinner as well as his unforeseen appointment.

He did not either complain about his ill-luck or bewail his misfortune as an ordinary beggar would have done before a rich man.

Hence the moral is that :

One must be patient and make the best of everything.

One should learn to bear adversity cheerfully, do one’s best, pray to God, and trust in His grace.

One should never complain about one’s misfortune. As one soweth so one reapeth. Hence there is no use in bewailing ill luck.

One must learn how to master courage and build one’s destiny through self-effort.

Patience is golden.

Without patience life will be a total failure.

One important point in this story is that when one goes to somebody for any favour, one should be prepared, to nod to his tune, if anything is to be expected from him.

Greed and the Grace of God cannot live together.

Where there is greed, there good luck can hardly exist. One should learn to live in the present, and ask for nothing more than one’s due.

With patience, cheerfulness, contentment and amiable disposition one should learn to make the best of the circumstances one is situated in.

--Manoj Ganadi