SQL DML Commands - SELECT, INSERT, DELETE , UPDATE Commands
Create an Employee Table
SQL> create table employee(id number, name varchar2(10),address varchar2(20),salary number);
Table created.
SQL> desc employee;
Output
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(10)
ADDRESS VARCHAR2(20)
SALARY NUMBER
Insert a record into employee
SQL> insert into employee values (123,'satish','mumbai',100000);
Inserting a null value for a column
SQL> insert into employee values (123,'ram','chennai','');
1 row created.
SQL> select * From employee;
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
123 satish mumbai 100000
123 ram chennai
Inserting multiple records
SQL> insert into employee values(&id,'&name','&address',&salary);
Output
Enter value for id: 126
Enter value for name: chris
Enter value for address: us
Enter value for salary: 200
old 1: insert into employee values(&id,'&name','&address',&salary)
new 1: insert into employee values(126,'chris','us',200)
1 row created.
SQL> /
Enter value for id: 127
Enter value for name: parker
Enter value for address: u
Enter value for salary: 3000
old 1: insert into employee values(&id,'&name','&address',&salary)
new 1: insert into employee values(127,'parker','u',3000)
Viewing data inserted in the table
SQL> select * from employee;
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
123 satish mumbai 100000
123 ram chennai
126 chris us 200
127 parker u 3000
Search using an ID
SQL> select * from employee where id=123;
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
123 satish mumbai 100000
123 ram chennai
select a few columns from the table for an employee
SQL> select name,address from employee where id=123;
Output
NAME ADDRESS
---------- --------------------
satish mumbai
ram chennai
Projecting columns for all employees
SQL> select name,address from employee;
Output
NAME ADDRESS
---------- --------------------
satish mumbai
ram chennai
chris us
parker u
Using select with where clause
SQL> select * from employee where salary>10000;
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
123 satish mumbai 100000
Use of not equal to operator
SQL> select * from employee where salary != 200;
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
123 satish mumbai 100000
127 parker u 3000
use of equality operator
SQL> select * from employee where name='satish';
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
123 satish mumbai 100000
Like operator demo
SQL> select * from employee where name like 's%';
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
123 satish mumbai 100000
SQL> select * From employee where name like '%s';
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
126 chris us 200
SQL> select * From employee where name like '%h';
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
123 satish mumbai 100000
SQL> insert into employee values(128,'tom','vellore',5000);
1 row created.
SQL> select * From employee where name like 't%m';
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
128 tom vellore 5000
Between Operator
SQL> select * from employee where salary between 1000 and 5000;
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
127 parker u 3000
128 tom vellore 5000
Fetch data for employee whose salary is null
SQL> select * From employee where salary is null;
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
123 ram chennai
Fetch data for employee whose salary is not null
SQL> select * From employee where salary is not null;
Output
ID NAME ADDRESS SALARY
---------- ---------- -------------------- ----------
123 satish mumbai 100000
126 chris us 200
127 parker u 3000
128 tom vellore 5000
Deleting a record from the table
SQL> delete from employee where name = 'satish';
1 row deleted.
SQL> desc employee;
Output
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(10)
ADDRESS VARCHAR2(20)
SALARY NUMBER
Delete using mulitple conditions
SQL> delete from employee where address='chennai' and salary='30000';