Basic SQL Commands in Oracle Database #Diksha Oracle Training Centre

 

Basic SQL Commands in Oracle Database.

 Hello Everyone,

In my previous blog I discussed about SQL and Classification of SQL . Today I will discuss about SQL basic commands which widely used in RDBMS.

The Topics of SQL command which I am going to cover in this blog are mainly divided into four Categories:

·        DDL:  DDL consists of commands which are used to define and design the database.

·        DML:  DML consists of commands which are mainly used to manipulate the data in the database.

·       DCL: DCL Consists of commands which deal with the user permissions/access and controls in the database.

·      TCL:  TCL Consist of commands which deal with the transaction in the database.

If you want to explore theory part please follow my below blog as in today blog I will discuss about how to use the commands.

https://dikshaoracletraining.blogspot.com/2020/09/sql-and-classification-of-sql-in-oracle.html

 

Apart from above topics, I will also cover below topics in this blog:

 

·        Different types of Keys in Database

·        Constraint in Database/SQL

·        Comments in SQL

·        Nested Queries

·        Set Operation

·        Dates and Auto increment

Let’s start with some Basic commands.

I am considering one table from Database i.e. Employee and put some dummy data. Please see the below Table image with some dummy data for your Reference.

Comments in SQL: We can do comment in SQL in two ways.

Single- Line Comments: Single line comment starts with two hyphens (–). So, any text mentioned after (–), till the end of a single line will be ignored by the compiler.

Syntax:

 --Select all:

 SELECT * FROM Employee;

Multi – Line comments:  The Multi-line comments start with /* and end with */. So, any text mentioned between /* and */ will be ignored by the compiler.

Syntax :

     /*Select all the columns of all the records

   from the Employee:*/  

  SELECT * FROM Employee;

  Above is Multi-Line Comment.

  Let's Start with SQL Commands: Data Definition Language Commands (DDL)

 CREATE : First Create the database  : Once you install the Oracle Database by default  Sys (super user) and The SYSTEM account is one of a handful of predefined administrative accounts generated automatically when Oracle is installed. SYSTEM is capable of most administrative tasks, but the task we’re particularly interested in is account management.

Creating user in Oracle Database first we need to connect first in System then issue the below command  :

CREATE USER DOTC IDENTIFIED BY DOTC;

After running the above command, here we’re simply creating a DOTC user that is IDENTIFIED or authenticated by the specified password and in Oracle database user DOTC database is created.

// Creating object that is table in Oracle Database

Syntax for creating Table:

CREATE TABLE TableName (

Column1 datatype,

Column2 datatype,

Column3 datatype,

....

 

ColumnN datatype

);

 

Example :

 

Create Table Department

(  emp_id      Number,

   dept_name  Varchar(20),

   Salary  Number,

   Location   Varchar2(40),

  Age Number

 );

After issue above command in user DOTC department table is created to see the structure of table we need use below command.

desc department;

Below is the output image of above command for table structure:

Here we can see the table is created , but there is no data since we haven't use DML yet , once we start DML we will show you how to insert the data.

 

You can also create another table by selecting some columns of existing table by using below Syntax:

 

Create table employee1 as select emp_id,

emp_name ,

address ,

country

from employee ;

DROP Command :

Drop Command is basically used to drop database as well as another objects like table , index , views etc.

 Drop Database command :

DROP user DOTC ;

If DOTC schema contains objects, then you must use the CASCADE clause to drop DOTC and the objects:

Syntax:

DROP USER DOTC CASCADE;

This command will drop user in Oracle database , so for now i am not providing you the output because later we need to use this database for different commands like Manipulation , transaction etc.

But if you want you can drop and try this command , after that recreate the new database.

Drop Table Command :

Drop Table Employee;

This statement is used to drop an existing table. When you use this statement, complete information present in the table will be lost.

This command will drop  employee table , so for now i am not providing you the output as later I need this table for manipulation, transaction etc.

But if you want you can drop and try this command , after that recreate the new Table.

 

TRUNCATE

Truncate Command is used to delete all the records and in statement we also not able to put any condition , but it will not delete the table it's remove all the records from the table.

Syntax

TRUNCATE TABLE TableName;

Example

 Truncate table employee;

ALTER

Alter command is used to delete, modify or add constraints or columns in an existing table.

The ‘ALTER TABLE’ Statement  with ADD/DROP COLUMN

You can use the ALTER TABLE statement for Adding and Dropping Column

Syntax

ALTER TABLE TableName

ADD ColumnName Datatype;

Example :

--- Add column age in Employee Table

Alter table employee add age number

desc employee;

Please see the Output of Above command in below image as age table added in Employee Table:

-- Drop Column Age

Alter Table employee

 drop column age ;

Please see the Output of Above command in below image:

Here we see column age is dropped.

 

The ‘ALTER TABLE’ Statement to MODIFY COLUMN.

 

This statement is used to change the datatype of an existing column in a table.

Syntax:

 

ALTER TABLE TableName

MODIFY COLUMN ColumnName Datatype;

Example :

Alter Table employee modify empname varchar2(30);

After issuing Above command you can see data type is changed from varchar2(20) to Varchar2(30) in employee table.

// Adding Primary Key by using Alter Command.

Alter table employee add primary key (emp_id);

Here Primary key created on emp_id in employee table so we can’t able to put any duplicate records as well as null values in emp_id column.

// Adding Foreign Key by using Alter Command.

Alter table department add foreign key (emp_id) references employee(emp_id);

Here Foreign key is created on emp_id of department table so we are restricting user as it will check the records in employee table if record exist then only we can insert multiple records in Department Table , if we try to insert any other emp_id which not exist in employee table we can’t insert that value in department table.

Below are the output of above command in below image.

 

Rename Command:

Oracle RENAME table statement basically used to rename an existing table in the database.

RENAME table_name TO new_name;

RENAME EMPLOYEE TO EMPLOYEE_NEW;

After issuing the above command table name will be changed from employee to employee_new.

Now this all are the basic  DDL commands used in SQL, Now let me explain you the different type of Keys that needs to be understand before DML(Data Manipulation Language).

  

Primary Key – A set of attributes which are used to uniquely identify every tuple is also a primary key. A Primary key can’t accept duplicate records as well null values.

 

Foreign Key – An attribute that can only take the values present as the values of some other attribute, is the foreign key to the attribute to which it refers. Foreign key accept only values which exist in Primary key. In other word we can say Parent – child relation.

 

Unique Key –  The unique key is same as  the primary key, but allows one NULL value in the column.

 

Candidate Key – A set of attributes which can uniquely identify a table can be termed as a Candidate Key. A table can have more than one candidate key, and out of the chosen candidate keys, one key can be chosen as a Primary Key.

 

Alternate Key – Alternate Keys are the candidate keys, which are not chosen as a Primary key

 

Composite Key – A composite key is a combination of two or more columns that identify each tuple uniquely.

 

SQL Commands: Constraints Used In Database

Constraints are used to restrict the user to enter value in database or in a table. The following are the different types of constraints:

NOT NULL

UNIQUE

CHECK

DEFAULT

INDEX

NOT NULL : constraint ensures that a column cannot have a NULL value.

 

Example -- NOT null on create table

Create Table Employee

(  emp_id      Int ,

   emp_name  Varchar2(30) NOT NULL,

   Mobile_Number  NUMBER NOT NULL,

   address   Varchar2(40),

   city   Varchar2 (20),

   country Varchar2(10) );

 

Also we can Alter to ADD and MODIFY NOT NULL

 

Example :

 

ALTER TABLE EMPLOYEE

MODIFY Mobile_Number bigint NOT NULL;

 

UNIQUE : Unique constraint ensures that all the values in a column are unique.

Example - Unique on create table syntax

Create Table Employee

(  emp_id     NUMBER UNIQUE,

   emp_name  Varchar2(30) NOT NULL,

   Mobile_Number NUMBER NOT NULL,

   address   Varchar2(40),

   city   Varchar2(20),

   country Varchar2(10) );

-- Unique Key on Multiple Columns

 

Create Table Employee

(  emp_id     NUMBER ,

   emp_name  Varchar2(30) NOT NULL,

   Mobile_Number NUMBER NOT NULL,

   address   Varchar2(40),

   city   Varchar2 (10),

   country Varchar2(20)

CONSTRAINT UK_employee UNIQUE(emp_id,Mobile_Number)

 );

--UNIQUE by using ALTER

ALTER TABLE Employee

ADD UNIQUE (emp_id);

--To drop a UNIQUE constraint using ALTER

ALTER TABLE  Employee_Info

DROP CONSTRAINT UK_employee;

CHECK : Check constraint ensures that all the values in a column satisfy a specific condition.

Example : Check constraint on Employee Table

Create Table Employee

(  emp_id      NUMBER UNIQUE,

   emp_name  Varchar2(30) NOT NULL,

   Mobile_Number NUMBER NOT NULL,

   address   Varchar2(40),

   city   Varchar2 (10),

   country Varchar2(20) CHECK (Country='India')

 );

 

NOTE : In Database you can’t create the same name of object / table , so for practice you need to change the table name or drop the existing table by using drop command.

 

 

DEFAULT  constraint consists of a set of default values for a column when no value is specified.

 -- Default Constraint on Create table

Create Table Employee

(  emp_id      number  UNIQUE,

   emp_name  Varchar2(30) NOT NULL,

   Mobile_Number number NOT NULL,

   address   Varchar2(40),

   city   Varchar2(10),

   country Varchar2(20)  Default 'India'

 );

 

Below is the output of above command in image.

 

Note : We can do it same in existing table by using Alter command.

--To drop the Default Constraint using Alter

 ALTER TABLE Employee_Info

ALTER COLUMN Country DROP DEFAULT;

 

INDEX

Index is an object basically used for fast retrieval of data . This constraint is used to create indexes in the table.

Syntax

--Create an Index where duplicate values are allowed

 

CREATE INDEX IndexName

ON TableName (Column1, Column2, ...ColumnN);

 

--Create an Index where duplicate values are not allowed

 

CREATE UNIQUE INDEX IndexName

ON TableName (Column1, Column2, ...ColumnN);

 

Examples :

 

CREATE INDEX idx_EmployeeName

ON employee (emp_id);

 

--To delete an index in a table

 

DROP INDEX Employee_idx_EmployeeName;

 

Note :Index is a big topic which I will discuss in another blog how index works and parts of index etc. for now we are given overview since it's also a constraint.

 

SQL Commands: Data Manipulation Language Commands (DML)

Through DML we will be able to insert ,delete and manipulate data in tables. The commands are as follows:

INSERT

UPDATE

DELETE

SELECT

 

Insert Command : Insert command  use to insert the values in the tables.

 

Syntax :

 

insert into table_name values ( value1,value2,value3 ,   );

 

In another way we can select only columns which want to be inserted in table  like below

 

insert into employee (emp_id,emp_name ) values (value1,value2);

 

Examples :

insert into employee values (100,'Rajesh',8789787889,'lajpat Nagar','Delhi','India');

insert into employee values (101,'Ashish',7789787889,'Saket','Delhi','India');

insert into employee values (102,'Sakshi',6789787889,'BTM','Bangalore','India');

After issuing the above command 3 records inserted in employee table.

 

Now i am selecting data whether records inserted in table or not by using below select statement.

select * from employee;

UPDATE

Update  statement is used to modify the records which already exist in the table.

Syntax:

UPDATE TableName

SET Column1 = Value1, Column2 = Value2, ...

WHERE Condition;

 

Example : Suppose I want 1 record name Gyan to DOTC  then we use below update command:

Update employee set empname='DOTC'

where emp_id=100;

Note : if we not put where condition then it will update all the emp_name as Aman but i want 1 records to update so i put where condition.

After Running above command below is the output in image:

 

Here you can easily see that for emp_id 100 empname value updated to DOTC as previous it was Gyan.

 

 

DELETE

This statement is used to delete the existing records in a table also we can put condition as per our requirement.

Syntax

DELETE FROM TableName WHERE Condition;

Example

I want to delete 2 records whose emp_id is 109 and 110

 

Below is the command :

 

delete from employee where emp_id in (400,600);

 

See the output in below image where you can see 2 records deleted whose emp_id is 400 and 600.

 

 

Now you can see 2 records deleted from employee table and only 8 records exist in employee table. for emp_id 400 and 600 all the records deleted.

 

Select :

 

Select is basically use to fetch  the data from the table:

 

Example :

 

select emp_name , address, city from employee where city='Delhi';

The above command will give you the record where City lies in Delhi only .

 

Select * from employee;

Here * means all as no where condition so it will give all the records from the table.

 

DISTINCT

This statement is used to return only different and not duplicate values .

 

select distinct city from employee;

Above command will not give you any duplicate records.

 

ORDER BY

The Order by used to sort the required results in Ascending or Descending order.

For Ascending you need to use ASC or for descending you need to use DESC.

 

Examples :

 

select * from employee order by city desc;

For  Ascending order write the below select statement

select * from employee order by city asc;

 

Now For SQL part only DCL and TCL are pending which I will cover in another blogs as  transaction Control Language mostly used in procedures and function and DCL is nothing just used to give permission to the user. So once i will start writting PL/SQL blogs we will able to use TCL command in programming language so that you can understand Rollback , Savepoint and Commit .

 

For now you can simply use Rollback command if you deleted any wrong record by using Below command.

Rollback;

If you are sure you have updated/deleted the correct record and it needs to be stored and saved in database use commit command.

Commit;

 

Please check all the commands and try to create some designs of tables and make relationship between them by using primary , foreign Key etc and write some basic SQL queries as you are able to understand all the commands of SQL . Please make some tables and write some queries and do some manipulation in table by using basics command. For any doubts related to SQL command please let me know.

 

Please go through this blog carefully , as this blog is very important for you to understand basic SQL command . Once you know all the commands it’s easy for you to understand next step of SQL. In next blog I will come up with Oracle Joins .

Please let me know if you have any doubts regarding SQL command.