Oracle Tutorial - 1 (Oracle DDL and DML statements)

Repository Link : https://github.com/oracle-expert/oracle_tutrial

What will I learn ?

• You will learn how to create a oracle user and grant a user.
• You will learn about oracle DML (Data Manipulation Language) Statements.
• You will learn about oracle DDL (Data Definition Language) Statements.
• You will learn SQL Conditional Selections and Operators.

OS and Software Requirements :

• Windows 7/ 8.1 /10
• Oracle 11g Installed.

Resources: https://www.oracle.com

Defficulty:

• Basic/intermediate.

Tutorial Description :

This tutorial is about Oracle database ,After Reading this tutorial you can create an oracle database and execute oracle DML, DDl statement. At first you need to download Oracle 11g from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html Oracle 11g software installation process is as simple as other software. After complete installation oracle 11g you Install it on your pc. Then you can see a desktop app named “Run SQL command line”. Open this app and follow these step.

Connect to system :

To connect your system database write this statement.
Syntax : Connect system/Your password;
Ex: connect system/arif;
Now you are connected to system database.

Create user:

To create user write this statement .
Syntax: Create user 'user_name' identified by 'password';
Ex: create user Roxarif identified by 1234;

Grant user :

Grant user that you created. To grant user write this statement.
Syntax: Grant connect,resource,dba to ' user_name';
Ex: grant connect,resource,dba to arif;

Create table :

To create table write this statement.
syntax: Create table 'table_name' (col_name1 datatype1(datasize1), col_name2 datatype2(datasize2), col_name3 datatype3(datasize3));
Ex: create table students_info (id number(10),name varchar2(25),Roll number(10),depertment varchar2(25),semister varchar2(25),primary key(id));

Inserting data into table :

There are two methods to insert data into table.

• By value method.
• By address method.

Using value method:

syntax : insert into 'table_name' (column_name1,column_name2,column_name3) values (value1,value2,value3);
Ex: insert into students_info (id,name,roll,depertment,semister) values (1,’arif’,873132,’computer’,’8th’);

Using address method :

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.
syntax: insert into 'table_name' values (&column_name1, &column_name2, &column_name3, &column_name4);
Ex: insert into students_info values(&id,’&name’,&roll,’&depertment’,’&semister’);

Selecting data from table :

syntax: select * from 'table_name';
N.B: here * indicate all colum
Or
select column_name1, column_name2, column_name3 from 'table_name';
Ex: select * from students_info;

Conditional selections :

We have two clauses used in this

• where.
• order by.

Using where :

syntax : select * from 'table_name' where 'condition';
Ex : select * from students_info where id=1;

Using and :

This will give output when all the condition become true.
syntax : select * from 'table_name' where 'condition1' and 'condition2' and 'condition3';
Ex: select * from students_info where id=1 and name=’arif’;

Using Between :

This will give the output based on the column and its lower bound, upper bound.
syntax : select * form 'table_name' where ‘colmn_name’ between 'lower_bound'and 'upper_bound' Ex : select * from students_info where id between 1 and 3;

Using Not Between :

This will give the output based on the column and its lower bound, upper bound.
syntax : select * form ‘table_name’ where ‘colmn_name’ not between ‘lower_bound’ and ‘upper_bound’;
Ex: select * from students_info where id not between 5 and 7;

Using in :

This will give the output based on the column and its list of values specified.
syntax : select * from ‘table_name’ where ‘column_name’ in (value1,value2,value3…valueN);
Ex: select * from students_info where id in (1,2,3);

Using not in :

This will gives the output based on the column which values are not in the list of values specified.
syntax : select * from ‘table_name’ where ‘column_name’ not in (value1,value2,value3…valueN);
Ex : select * from students_info where id not in (2,3);

Using Like :

This will be used to search through the rows of database column based on the pattern your specify.
syntax : select * from ‘table_name’ where ‘column_name’ like ‘pattern’;
Ex : select * from students_info where roll like 873132;

Using Null :

This will give the output based on the null values in the specified column.
syntax : select * from ‘table_name’ where ‘column_name’ is null;
Ex : select * from students_info where marks is null;

Using Order By :

This will be used to ordering the columns data ( ascending or descending ). By default oracle will use ascending order. If you want output in descending order you have to use desc keyword after the column.
Ascending Order :
syntax : select * from ‘table_name’ order by ‘column_name’;
Ex : select * from students_info order by roll;
Descending Order :
syntax : select * from ‘table_name’ order by ‘column_name’ desc;
Ex: select * from students_info order by roll desc;

Updating Data:

syntax: update ‘table_name’ set column1=value1,column2=value2 where ‘condition’;
Ex: Update students_info set name=’roxarif’,roll=85625 where id=2;

Deleting data:

syntax: delete ‘table_name’ where ‘condition’;
Ex: delete students_info where id=3;

Adding new column in a table :

syntax : alter table ‘table_name’ add ‘column_name datatype';
Ex; alter table students_info add reg_no number(20);

Remove column :

syntax : alter table ‘table_name’ drop ‘column_name’;
Ex: alter table students_info drop reg_no;

Renaming column name :

syntax : alter table ‘table_name’rename column old_column to new_column;
Ex: alter table students_info rename column name to student_name;

Modify column precision :

syntax; alter table ‘table_name’ modify ‘column_name datatype’;
Ex : alter table students_info modify name varchar2(15);

Using truncate :

Truncate can be used to delete the entire table data permanently.
syntax : truncate table ‘table_name’;

Using drop :

This will be used to drop the database table.
syntax : drop table ‘table_name’;
Ex : drop table students_info;

Renaming table name :

syntax : rename ‘old_table_name’ to ‘new_table_name’;
Ex: rename students_info to students_profile_info;

Run SQL Comand Line screensort :

connect system create User Grant User create Table Insert data by value mehod Insert data by address mehod Select Data Where Condition Using Between Using Not Between Using And Using OR Using In Using Not In Using Like Using OrderBy

Proof Of Work Done :

https://github.com/oracle-expert/oracle_tutrial