HOW TO WRITE UPDATE STATEMENT IN ORACLE SQL

USING UPDATE:

This can be used to modify the table data.

Syntax: Update <table_name> set <col1> = value1, <col2> = value2 where <condition>;

Ex: update student set marks = 500;

     (NOTE: If you are not specifying any condition this will update entire table.)

Output:

NO	NAME	MARKS
1	Visu	500
2	Vasu	500
3	Manoj	500
4	Aisu	500
5	Mohan	500
6	Manvi	500
7	Namu	500
8	Susu	500
9	Thanu	500
10	AJ	500
11	MK	500
12	MD	500

EX: update student set marks = 500 where no = 2;

Output:

 

NO	NAME	MARKS
1	Visu	100
2	Vasu	500
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)

EX: update student set marks = 500, name = 'Visu' where no = 1;

Output:

NO	NAME	MARKS
1	Visu	500
2	Vasu	500
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)

WHAT ARE THE CONDITIONAL SELECTIONS AND OPERATIONS IN ORACLE SQL

Conditional Selections and Operations:

We have two clauses used in this

Ø  Where

Ø  Order by

USING WHERE

Syntax: select * from <table_name> where <condition>;

     The following are the different types of operators used in where clause.

v  Arithmetic operators     

v  Comparison operators

v  Logical operators

v  Arithmetic operators      -- Highest precedence

+, -, *, /

v  Comparison operators

a.       =, !=, >, <, >=, <=, <>

b.       between, not between

c.       in, not in

d.       null, not null

e.       like

v  Logical operators

a.       And

b.       Or                                   -- Lowest precedence

c.       not

USING COMPARISION OPERATORS:  With WHERE Clause

a)      =, !=, >, <, >=, <=, <>

EX: SELECT *FROM STUDENT WHERE NO=5;

Output: only the number equal to 5 will give as result

NO	NAME	MARKS
5	MOHAN	500

EX: SELECT *FROM STUDENT WHERE NO>5;

Output: The numbers which are greater than 5 will give in the result.

NO	NAME	MARKS
6	Manvi	600
7	Namu	500
8	Susu	300
9	Thanu	200
10	AJ	100
11	MK	(null)
12	MD	(null)

EX: SELECT *FROM STUDENT WHERE NO<5;

Output: The numbers which are less than 5 will give in the result.

NO	NAME	MARKS
1	Visu	100
2	Vasu	200
3	Manoj	300
4	Aisu	400

EX: SELECT *FROM STUDENT WHERE NO >=5;

Output: The numbers which are greater than equal to 5 will give in the result.

NO	NAME	MARKS
5	Mohan	500
6	Manvi	600
7	Namu	500
8	Susu	300
9	Thanu	200
10	AJ	100
11	MK	(null)
12	MD	(null)

EX: SELECT *FROM STUDENT WHERE NO >=5;

Output: The numbers which are less than equal to 5 will give in the result.

NO	NAME	MARKS
1	Visu	100
2	Vasu	200
3	Manoj	300
4	Aisu	400
5	Mohan	500

EX: SELECT *FROM STUDENT WHERE NO <>5;

Output: with this statement except no 5, remaining all the rows will be displayed in the result.

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

b)      Between, Not Between

Using Between:

SYNTAX: select * from <table_name> where <col> between <lower bound> and <upper bound>;

Ex: select * from student where marks between 200 and 400;

Output: With the above statement we are going to see the result of marks between 200 and 400.

NO	NAME	MARKS
2	VASU	200
3	MANOJ	300
4	AISU	400
8	SUSU	300
9	THANU	200

Using Not Between:

SYNTAX: select * from <table_name> where <col> not between <lower bound> and <upper bound>;

Ex: select * from student where marks not between 200 and 400;

Output: With the above statement, we are going to see result of marks between 200 and 400.

NO	NAME	MARKS
1	VISU	100
5	MOHAN	500
6	MANVI	600
7	NAMU	500
10	AJ	100

c)       in, not in:

Using IN: This will give the output based on the column and its list of values specified.

Syntax: select * from <table_name> where <col> in ( value1, value2, value3 … valuen);

Ex: select * from student where no in (1, 2, 3);

Output:

NO	NAME	MRKS
1	VISU	100
2	VASU	200
3	MANOJ	300

Using Not In: This will give the output based on the column which values are not in the list of values specified.

Syntax: select * from <table_name> where <col> not in ( value1, value2, value3 … valuen);

Ex: select * from student where no not in (1, 2, 3,7,8,9,10);

Output:

NO	NAME	MARKS
4	Aisu	400
5	Mohan	500
6	Manvi	600
11	MK	(null)
12	MD	(null)

d)      null, not null:

Using Null: This will give the output based on the null values in the specified column.

Syntax: select * from <table_name> where <col> is null;

Ex: select * from student where marks is null;

Output:

NO	NAME	MARKS
11	MK	(null)
12	MD	(null)

Using Not Null: This will give the output based on the not null values in the specified column.

Syntax: select * from <table_name> where <col> is not null;

Ex: select * from student where marks is not null;

Output:

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

e)      Like:

Using Like: This will be used to search through the rows of database column based on the pattern you specify.

Syntax: select * from <table_name> where <col> like <pattern>;

1.       Ex: select * from student where marks like 100;

Output: This will give the rows whose marks are 100.

NO	NAME	MARKS
1	VISU	100
10	AJ	100

2.       EX: select * from student where name like 'V%';

Output: This will give the rows whose name start with ‘V’.

NO	NAME	MARKS
1	VISU	100
2	VASU	200

3.       EX: select * from student where name like '%J';

Output: This will give the rows whose name ends with ‘J’.

NO	NAME	MARKS
3	MANOJ	300
10	AJ	100

4.       EX: select * from student where name like '_A%';

Output: This will give the rows whose name’s the second letter start with ‘A’.

NO	NAME	MARKS
2	VASU	200
3	MANOJ	300
6	MANVI	600
7	NAMU	500

5.       EX: select * from student where name like '__N%';

Output: This will give the rows whose name’s the third letter start with ‘N’.

NO	NAME	MARKS
3	MANOJ	300
6	MANVI	600

6.       EX: select * from student where name like '%_S%';

Output: This will give the rows whose name’s the second letter start with ‘S’ from ending.

NO	NAME	MARKS
1	VISU	100
2	VASU	200
4	AISU	400
8	SUSU	300

7.       EX: select * from student where name like '%I__%';

Output: This will give the rows whose name’s the third letter start with ‘I’ from ending.

NO	NAME	MARKS
1	VISU	100
4	AISU	400

8.       EX: select * from student where name like '%S% S %';

Output: This will give the rows whose name contains 2 S’s.

NO	NAME	MARKS
8	SUSU	300

LOGICAL OPERATORS:

a)   Using And:    

Syntax: select * from <table_name> where <condition1> and <condition2> and ..<conditionn>;

Ex: select * from student where no = 2 and marks = 200;

Output: This will give the output when all the conditions become true.

NO	NAME	MARKS
2	VASU	200

b)   Using Or: This will give the output when either of the conditions become true.

Syntax: select * from <table_name> where <condition1> and <condition2> or .. <conditionn>;

Ex: select * from student where no = 2 or marks >= 200;

Output:

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

USING ORDER BY: This will be used to ordering the column's data (ascending or descending).

Syntax: Select * from <table_name> order by <col> desc;

By default, oracle will use ascending order, If you want the output in descending order you have to use desc keyword after the column.

Ex: select * from student order by no;

Output:

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)

EX: select * from student order by no desc;

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