Some Queries For Performing Operation On Oracle Database. - FindersAdda : Tutorials of Make Money, Computer or Internet

Monday, 21 August 2017

Some Queries For Performing Operation On Oracle Database.


In this article, I am going to share some queries for performing operations on oracle database like create a table, delete a table, update a table, insert data into table etc.

First of all, I will tell you about data types that means some data types which is commonly used when we create a table.



DATA TYPES
DESCRIPTION
CHAR (Size)
This data type is used to store character strings values of fixed length. The size in bracket determines the number of characters the cell can hold. The character data type can hold maximum 255 characters.
VARCHAR(size) / VARCHAR2(size)
This data type is used to store variable length alphanumeric data. It is the more flexible form of the char data type. This data type can hold up to 4000 character. Keep in mind, that char is much faster than varchar, sometimes up to 50%.
DATE
This data type is used to represent date and time. The standard format is DD-MON-YY as in 21-AUG-2017. DateTime stores date in the 24-hour format. By default, the time in a date field is 12:00:00 am, if no time portion is specified. The default date for a date field is the first day of the current month. Valid dates range from January 1, 4712 B.C to December 31, 4712 A.D.
NUMBER(P, S)
The NUMBER data type is used to store numbers (fixed or floating point). Numbers of virtually and magnitude maybe stored up to 38 digits of precision. Valid values are 0, and positive and negative numbers with magnitude 1.0E-130 to 9.9…E125. Numbers may be expressed in two ways: first, with the numbers 0 to 9, the signs + and – and a decimal point (.); second, in scientific notation, such as, 1.85E3 for 1850. The precision (P) determines the maximum length of the data, whereas, the scale (S), determines the number of places to the right of the decimal. If scale is omitted then the default is zero. If precision is omitted, values are stored with their original precision up to the maximum of 38 digits.
LONG
This data type is used to store variable length character strings containing up to 2 GB. LONG data can be used to store arrays of binary data in ASCII format. Only one LONG value can be di\defined pre table. LONG values cannot be used in sub queries, functions, expressions, where clauses or indexes and the normal character functions such as SUBSTR cannot be applied to LONG values. A table containing a LONG value cannot be clustered.
RAW / LONG RAW
The RAW / LONG RAW data types are used to store binary data, such as digitized picture or image. Data loaded into columns of these data types are stored without any further conversion. RAW data types can have maximum length of 255 bytes. LONG RAW data type can contain up to 2GB. Values stored in columns having LONG RAW data type cannot be indexed.






The CREATE TABLE command


This command is used for creating table. Each table column definition is separated from the other by a comma. Finally, the SQL statement is terminated with a semi colon.



Rules for Creating Tables


1). Table name can have maximum up to 30 characters.

2). The table name are allowed alphabets (UPPER CASE OR LOWER CASE) and numbers (0-9).

3). A table name should begin with an alphabet.

4). The use of the special character like _ (underscore) is allowed and also recommended. (Special characters like $, # are allowed only in oracle).

5). SQL reserved words not allowed. For example: create, select and so on.





Syntax:


CREATE TABLE <TABLE NAME> (<columnName1> <data type>(<size>), <ColumnName2> <data type>(<size>));



Example:


CREATE TABLE Findersadda (roll number(5), name varchar2(40));



Inserting Data into Tables


This query is used for insert data into table. There are several ways to insert data into the table. You can see it in syntax's or Examples.


Syntax:


1. INSERT INTO <table name> (<ColumnName1>, <ColumnName2>) values(<values of column1>, <values of column2>);

2. INSERT INTO <table name> values(<&columnname1>, <&columnname2>);



Example:


1.   INSERT INTO STUDENT(roll, name) values('1', 'sumit');

2.   INSERT INTO STUDENT VALUES('&roll', '&name');
      Enter value for Roll : 1
      Enter value for name : sumit

1 rows Created.



Viewing Data in The Table


When data is inserted in the table then next operation is performed to view the data from table. There are many ways to view data from table.


Syntax:


SELECT * FROM <TABLE NAME>;

The above syntax is used for viewing all data in the table.

SELECT * FROM <TABLE NAME> WHERE <columnname>=<value>;

The above syntax is used for viewing condition applied all data in the table.

SELECT <columnname1>, <columnname2> FROM <TABLE NAME>;

This syntax is used for viewing the selected column data in the table.



Example:


SELECT * FROM STUDENT;

SELECT * FROM STUDENT WHERE COURSE='mca';

SELECT ROLL, NAME FROM STUDENT;




Eliminating Duplicate Rows From the Table

A table could hold duplicate rows. In such a case, to view only unique rows the distinct clause can be used .

Distinct clause can only be used with select statements. Distinct clause allows removing all duplicates data from the result set.


Syntax:


SELECT DISTINCT <columnname1>, <columnname2> from <table name>;

The above syntax is used to show the data selected column data after eliminating duplicate values from the table.


SELECT DISTINCT * FROM <TABLE NAME>;

The above syntax is used to show all column data after eliminating all duplicate values from the table.

SELECT DISTINCT * FROM <TABLE NAME> WHERE <Condition>;

The above syntax is used for show the condition applied data after eliminating duplicate data from the table.




Example:


SELECT DISTINCT ROLL, NAME FROM STUDENT;

SELECT DISTINCT * FROM STUDENT;

SELECT DISTINCT * FROM STUDENT WHERE COURSE = 'MCA';



Sorting Data In a Table


Oracle allows data from a table to be viewed in a sorted order. The rows retrieved from the table will be sorted in either ascending or descending order depending on the condition specified in the Select sentences.



Syntax:


SELECT * FROM <TABLENAME> ORDER BY <COLUMNNAME1>, <COLUMNNAME2> <[SORT ORDER]>;

The order by clause sorted the data in ascending order by default.


Example:


SELECT * FROM STUDENT ORDER BY NAME DESC;



Creating A Table From The Table


Syntax:


CREATE TABLE <NewTableName> (<ColumnName1>, <ColumnName2>) AS SELECT <ColumnName1>, <ColumnName2> FROM <OldTableName>;

The above syntax is used when we want to create some column in new table from old table.

CREATE TABLE <NewTableName> AS SELECT * FROM <OldTableName>;

The above syntax is used when we want to create all structure in new table from the old table.


Example:

CREATE TABLE Finders (roll,name) AS SELECT roll, name FROM STUDENT;

CREATE TABLE finde AS SELECT * FROM STUDENT;




Inserting Data INTO A Table From The Another Table


Sometimes, we want to insert same data to a new table from the existing table then this syntax is used.


Syntax:


INSERT INTO <NewTableName> SELECT * FROM <OldTableName>;

The above syntax is used to insert ALL data to the new table from the old table.
INSERT INTO <NewTableName> SELECT * FROM <OldTableName> WHERE <Condition>;

The above syntax is used when we want to apply condition during inserting data.



Example:

INSERT INTO Finders SELECT * FROM Student;

INSERT INTO finders SELECT * FROM Student WHERE Course='bca';



Deletion Operation


The Delete command deletes rows from the table that satisfies the condition provided by its where clause, and returns the number of records deleted.


Syntax:


DELETE FROM <TableName>;

The above syntax is used for deleting all rows from the table.

DELETE FROM <TableName> WHERE <Condition>;

The above syntax is used for deleting specific rows from the table.


Example:


DELETE FROM STUDENT;

DELETE FROM STUDENT WHERE ROLL > 5;



Updating The Content Of The Table


The update command is used to change or modify data values in a table.



Syntax:


UPDATE <TableName> SET <ColumnName 1> = <Expression>, <ColumnName 2> = <Expressioin>;

The above syntax updates all rows in the table.

UPDATE <TableName> SET <ColumnName 1> = <Expression>, <ColumnName 2> = <Expressioin> WHERE <condition>;

The above syntax updates specific rows in the table.


Exapmle:


UPDATE STUDENT SET COURSE = 'BCA';

UPDATE STUDENT SET COURSE = 'BCA' WHERE ROLL = 2;



Modifying The Structure Of Table


The Structure of a table can be modified by using the ALTER TABLE command. Alter table allows changing the structure of an existing table. With ALTER TABLE , it is possible to add or delete columns, create or destroy indexes, change the data type of existing columns or rename columns or the table itself.



Syntax:


ALTER TABLE <TableName> ADD(<NewColumnName> <Data Type>(<size>),<NewColumnName> <Data Type>(<size>)..);

The above syntax is used for adding new column in table.

ALTER TABLE <TableName> DROP COLUMN <ColumnName>;

The above syntax is used for deleting or dropping a column from table.

ALTER TABLE <TableName> MODIFY(<ColumnNmae> <NewDataType>(<NewSize>));

The above syntax is used for modifying the column structure.

RENAME <TableName> To <NewTableName>;

The above syntax is used for rename the table name.

TRUNCATE TABLE <TableName>;

The above syntax drop and re-create the table that means it deletes table and then re-creates the table as same structure.

DROP TABLE <TableName>;

The above syntax is used for delete table.



Example:


ALTER TABLE STUDENT ADD(CITY VARCHAR2(50));

ALTER TABLE STUDENT DROP COLUMN CITY;

ALTER TABLE STUDENT MODIFY(course varchar2(50));

RENAME STUDENT TO LISTOFSTUDENT;

TRUNCATE TABLE LISTOFSTUDENT;

DROP TABLE LISTOFSTUDENT;



Read Also : 2 ways to set path in java. 
Read Also : What is Literals In java full guide.
Read Also : What is Data Types in Java.
Read Also : What is Java And Features of Java Full Details.



FINAL WORDS

In this article, I told some query for performing operations on SQL database.
I hope you liked this post so much. If you loved this post, so, share with your friends and also give some suggestion that what should i improve in this post. Thank You So Much...... 

About Author

SUMIT KUMAR GUPTA
SUMIT KUMAR GUPTA

Author & Editor

Myself Sumit Kumar Gupta & 21 year's old person. I am a programmer and blogger. I spend much more time on programming and blogging and helps other programmers and bloggers.

0 comments:

Post a Comment

We are eager to see your comments.
Comment Rule :

1. Don't use any link in the comment box. If you have any problem related to link then contact me with the contact form.

2. Don't use the wrong word if you write the wrong word in comment box then it will get spam.