What is SQL? Basic SQL Command for Oracle 10g/11g Data Base.

Hello Friends, Today, I gonna show you what is the database and some basic commands of SQL which is being used in Oracle 10g/11g database. It will help you at that time when you put your leg in the JDBC (Java Database Connectivity). I have written some basics command which every java developer /python developer etc must know if you want to design any project. Without it, we can not make our project optimized and accurate because if you don't use the database then you have to write your logic. The database is a very important part of the language because if you want to keep your data secure and easy to access.

What is Database?

The database is a collection of data that is organized in such a manner that facilitate ease of access, as well as efficient management and updating.

SQL(Structured Query Language):-

SQL is a standard language for accessing and manipulating databases. To build a web site that shows data from a database, you will need an RDBMS database program (i.e. MS Access, SQL Server, MySQL). The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows. You can search much more information about SQL database from google because SQL is a very vast topic for discussing if I explain it then you will feel getting bored. Here, I have shown you how you can write basic command or you can say fires query in SQL run Command. For your kind information, I want to tell one thing, It is a case In-sensitive language it means you can write command or queries in lower case as well as an upper case but always remember one thing when you compare two things then it will become Case sensitive language. It is of the following type:-

DDL (Data Definition Language):-These language's queries are AutoSave in oracle means if we close the run command then data will be saved in the database.

1. create:- to create the table

2. alter:- For modify the table

3. truncate:- for blank the table

4. drop:- to delete the whole table

DML (Data Manipulation language):-

These language's query is not Auto Saved in oracle.

1. insert:- for inserting a new row

2. update:- to modify the table

3. delete:- for delete the particular row

DQL (Data Query Language):-

1. select:- For display the data in the console.

TCL (Transaction Control Language):-

1. commit:- For save the data permanently.

2. rollback:- For undo.

3. savepoint:- For saving the data in modules.

DCL (Data Control Language):-

1. grant:- to permit to access.

2. revoke:- For regain permission.

Data Type in Oracle:-

Just like java, python, c, c++ has provided us with primitive data type which identifies what will be the value you want to create. Similarly, oracle also has some data type which identifies what will be the value you want to put in the table.

Data type Range

char 2000

varchar2 4000

a.integer

number 1.0*10^130 to 9.9*10^125

b. real

date 01-Jan.-4712 B.C.

31-Dec.-4712 A.D.

ORACLE:-

When you create your table then you first connect or we can say login yourself in the database with username and password. When you install the oracle 10g/11g database then you have to set your username and password which is your data administrator account. I suggest you please remember this username and password or you can write it in your diary. You can also make your user which I have shown below.

SQL> connect system/abc;

Note:- here system is my username and ABC is my password

For creating user:-

Here, I have shown you how you can create your user. To do this you just write a code create user then you have to decide your username then write identified by then your decided password. In this way, you can make a username and password. But you have to also do one more thing.

Syntax:- create user <username> identified by <password>;

SQL> create user Username identified by Password;

Please Remember that It can not permit to login. to do this you have to grant the username which I have shown below:-

SQL> grant resource, connect to Username;

When you create your table then tab select command shows you how many tables you created but currently zero tables have been created then it will not display.

SQL> select *from tab;

Note:- This displays how many tables you have created

Create command:-

This is a very very crucial command because the whole database is depended upon it. Create Command is used to create a table in the database.To do this you have to write the code. which has shown below:-

Syntax:- create table <table_name>( <column_name> <data type> <(size)>);

SQL>create table student(roll number(10),per number(5,2),name varchar2(20));

The describe command is used to display the structure of the table. It displays like how many columns are in it and what was the data type of the value.

Syntax:- desc <Table_name>;

SQL> desc student;

Insert command:-

This is also a very important command because when you create the table then you have to put the row in it. To do this you can write it in the following Five ways:-

Syntax:-

1) insert into <table_name> (<list of column>)values(<list of value>);

e.g. :-

SQL>insert into student(roll,per,name)values(101,89.66,'Shubham');

2)insert into (<table_name>)values(<list of value>);

e.g.:-

SQL>insert into student values(102,90.99,'Rakesh');

3)insert into <table_name>(<list of column)values(<list of values>);

e.g.:-

SQL>insert into student(roll,name)values(103,'Santosh');

4)insert into <table_name> values(<list of values>);

e.g. :-

SQL>insert into student values(104,null,'Ritik');

e.g. :-

insert into student values(&roll,&per,'&name');

After all, if you want to display your table then you have to write the following command:-

Syntax :-

select *from <table_name>;

e.g.

SQL> select *from student ;

Commit:-

This is a very crucial command because it helps us to save our table. Means By default some basic commands like delete, insert etc. has not auto-saved in the database means when you close your command screen then it will not save in your database. if you want to save your table in the database until you delete it by yourself. Then you have to write commit.

SQL>commit;

SQL> /

Note :-For reuse the previous command

Update command:-

Update Command is used to update your existing values. Suppose you you want to put the total marks of physics, chemistry and mathematics marks in the tot column. You can understand by seeing the below command:-

update <table_name> set <col_name>=<value>,

<col_name>=<value>;

[where <test_condition>]; is optional

if you want to update the whole table's column then

SQL> update student set tot=phy+chem+math;

SQL> update student set per=tot/30;

SQL> update student set math=88 where roll=101;

SQL> update student set phy=71,math=89 where roll=102 or roll=105;

Delete command:-

It will delete the only row from the existing table. Please pay attention here it did not remove or we can say drop table from our database. It only deletes a row from the existing table. it means the table you created is available after all.

Syntax:-

delete from <table_name> [where <test_condition>]; it is not auto save command

e.g.

SQL> delete from student where name=' Shubham' ;

Note:-

Here where clause is used to filter records.
The where clause is used to extract only those records that fulfil a specified condition.

For remove whole table's columns and rows then you have to write below command:-

Syntax:-

delete from <table_name>;

e.g.

SQL> delete from Student;

If you do this by mistake means you don't want to delete whole columns and rows. you forget to give where condition then you simply write rollback command:-

SQL> rollback;

After then you can write your delete commands:-

SQL> delete from student where roll=102;

Truncate Command:-

Just like we have to delete command to delete any row from the table. Similarly, we also have a truncate command to delete one or we can also delete the whole row from the table.

Syntax:-

truncate <table> <table_name>;

e.g.:-

SQL> truncate table student;

Select command:-

Select Command is used to get particular information about the existing row. We can also get all information and details from the row, columns and it's valued. There are so many ways to write this command. To do this we have to write the command below:-

Syntax:-

select <list of col name> from <table_name> [where <test_cond>];

e.g. :-

SQL>select roll, name from student where per>=75;

SQL>select * from student where per>=80;

SQL>select *from student where per>=80 and per>=90;

SQL> select * from student;

here * stands whole data from a table.

If you want search maximum percentage student details from the student table then you can write in this way:-

Syntax:-

select max(<Column_name>) from <table_name>;

e.g.:-

SQL>select max(per) from student;

If you want to know how many students are registered in the student table then you can write in the following ways:-

Syntax:-

select count(*) from <table_name>;

e.g. :-

SQL>select count(*) from student;

If you want to fetch the particular values from the table which is not present in another table means to suppose, I have a two table whose name is employees and user and I want to search those employees which are not present in the user's table. To do this we can write the queries in the following way:-

SQL>select EmpId, EmpName from employees where Role='RECEPTIONIST' and EmpId not in (select EmpId from users where userType='RECEPTIONIST'

here, I have two tables employees and users. In the employees' table, I have fields whose name is EmpId and EmpName here i use EmpId as a primary key and in the employees' table, I have a field whose name is EmpId, EmpName, Sal, userType etc. If you don't know how to make a primary key then don't worry about that I have also explained in the 'Add Constraints' section.

Alter command:-

Alter is a very important command because if you want to add or modify or drop the existing table values then you can fire below queries.

Syntax:- alter table <table_name> <add/drop/modify> (<col_name><data type> (<size>),........);

SQL>alter table student add (dob date, mob number(10));

SQL>alter table student modify(namevarchar2(30));

SQL>alter table student drop(mob);

Add Constraint:-

This Command is under the alter command. For understanding the Below command I will try to understand by example the example is that suppose you create a table whose name is employees with column EmpId, EmpName, EmpSal. If you seem that I have to make EmpId unique then you have to apply some restriction to the oracle that doesn't make use of existing EmpId if user again put same EmpId then it will show a message which we have to decide. Please have a look at the below command:-

SQL>alter table employees add constraint EMP_EMPID_PK Primary Key (EmpId);

Note:-

here alter table is the syntax to write this command.

here employees are the table name.

here add constraint is the syntax which represents to restriction.

here EMP_EMPID_PK is the error message which displays when the user again gives existing EmpId.

here Primary Key is the syntax which represents the Column, no you have given has become unique.

here (EmpId) represents the column name which you want to make unique.

If you want to add constraint which represents that the column you given become foreign key means if this column name is also present in another table then you tell to the oracle that this column name is same which I use in another table. Means if the user gives E101 it will copy in the another table's column name. If you want to do this then you have to write following command:-

SQL>alter table users add constraint USERS_EMPID_FK Foreign Key(EmpId) references Employees(EmpId);

Note:-

here alter table is the syntax to write this command.

here users are another table name.

here add constraint is the syntax which represents to restriction.

here USERS_EMPID_FK is the error message which displays when in oracle has some issue.

here (EmpId) represents the column name which you want to make use in another table.

here references Employees(EmpId) is the part of the syntax.

In this way, you can make your database on your computer. I hope it will be helpful for you. Thank you for reading this article.

Search your interest

ḰnowⱣroÍnsight | A perfect portal for you