ARC Technologies & Institutions
Software Development And Software Testing Training with Placements Institute
WELCOME TO SQL
SESSION
Address
ARC Technologies and Institute ,NIRMAN ALTIUS, 1,
Mundhwa - Kharadi Rd, behind RADISSON BLU,
Pandhari Nagar, Kharadi, Pune, Maharashtra 411014 9606060871 / 9606060872
ARC Technologies & Institutions DATA MANIPULATION LANGUAGE
INSERT
UPDATE
DELETE
9606060871 / 9606060872
ARC Technologies & Institutions
INSERT INTO : Statement is used to insert new records in a table
EMP NO ENAME JOB SAL COMM DEPTNO
Record 1 101 SMITH CLERK 800 20
Record 2 102 ALLEN MANAGER 3459 1400 20
Record 3 103 SCOTT ANALYST 3000 20
Record 4 104 FORD SALESMAN 2450 0 20
We can insert data into table in two ways
1. Specify both the column names and the values to be inserted:
Syntax : INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query.
However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO
syntax would be as follows:
Syntax : INSERT INTO table_name
VALUES (value1, value2, value3, ...); 9606060871 / 9606060872
ARC Technologies & Institutions
Create database Swiggy;
create table Employee (EMPNO numeric(4) primary key,
ENAME varchar(255) NOT NULL,
JOB varchar(255) NOT NULL,
MGR numeric(4),
HIREDATE date NOT NULL,
SAL int NOT NULL ,
COMM numeric , EMP NO ENAME JOB SAL COMM DEPTNO DNAME LOC
Deptno numeric(4),
constraint foreign key(deptno) references Department(DEPTNO),
check (SAL>0) );
INSERT INTO Employee
(column1, column2, column3, ...) EMP NO ENAME JOB SAL COMM DEPTNO DNAME LOC
VALUES 101 SMITH CLERK 800 20 RESEARCH DALLAS
(value1, value2, value3, ...);
9606060871 / 9606060872
ARC Technologies & Institutions
Emaple 1 : Write a query to add following data into the Employee table.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 800 20
Query : Insert into Employee(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,deptno)
Values(7369,'SMITH','CLERK',7902,"1980-12-17",800,null,20);
insert into employee values(7499,'ALLEN','SALESMAN',7698,"1981-02-20",1600,300,30);
insert into employee values(7521,'WARD','SALESMAN',7698,"1981-02-22",1250,500,30);
insert into employee values(7566,'JONES','MANAGER',7839,"1981-04-02",2975,null,20);
insert into employee values(7654,'MARTIN','SALESMAN',7698,"1981-09-28",1250,1400,30);insert into
employee values(7698,'BLAKE','MANAGER',7839,"1981-05-01",2850,null,30);insert into employee
values(7782,'CLARK','MANAGER',7839,"1981-06-09",2450,null,10);insert into employee
values(7788,'SCOTT','ANALYST',7566,"1987-04-19",3000,null,20);insert into employee
values(7839,'KING','PRESIDENT',null,"1981-11-17",5000,null,10);insert into employee
values(7844,'TURNER','SALESMAN',7698,"1981-09-08",1500,0,30);insert into employee
values(7876,'ADAMS','CLERK',7788,"1987-05-23",1100,null,20);insert into employee
values(7900,'JAMES','CLERK',7698,"1981-12-03",950,null,30);insert into employee
values(7902,'FORD','ANALYST',7566,"1981-12-03",3000,null,20);insert into employee
values(7934,'MILLER','CLERK',7782,"1982-01-23",1300,null,10);
9606060871 / 9606060872
ARC Technologies & Institutions
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-1980 800 20
7499 ALLEN SALESMAN 7698 20-Feb-1981 1600 300 30
7521 WARD SALESMAN 7698 22-Feb-1981 1250 500 30
7566 JONES MANAGER 7839 2-Apr-1981 2975 20
7654 MARTIN SALESMAN 7698 28-Sep-1981 1250 1400 30
7698 BLAKE MANAGER 7839 1-May-1981 2850 30
7782 CLARK MANAGER 7839 9-Jun-1981 2450 10
7788 SCOTT ANALYST 7566 19-Apr-1987 3000 20
7839 KING PRESIDENT 17-Nov-1981 5000 10
7844 TURNER SALESMAN 7698 8-Sep-1981 1500 0 30
7876 ADAMS CLERK 7788 23-May-1987 1100 20
7900 JAMES CLERK 7698 3-Dec-1981 950 30
7902 FORD ANALYST 7566 3-Dec-1981 3000 20
7934 MILLER CLERK 7782 23-Jan-1982 1300 10
9606060871 / 9606060872
ARC Technologies & Institutions
Department
DEPTNO DNAME LOC GRADE LOSAL HISAL
10 ACCOUNTING NEW YORK 1 700 1200
2 1201 1400
20 RESEARCH DALLAS
3 1401 2000
30 SALES CHICAGO
4 2001 3000
40 OPERATIONS BOSTON 5 3001 9999
insert into department values (10,'ACCOUNTING','NEW YORK’);
insert into department values (20,'RESEARCH','DALLAS’);
insert into department values (30,'SALES','CHICAGO’);
insert into department values (40,'OPERATIONS','BOSTON');
9606060871 / 9606060872
ARC Technologies & Institutions Update
2) Update
The Update statement is used to modify the existing records in a table.
Syntax : UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-1980 800 20
7499 ALLEN SALESMAN 7698 20-Feb-1981 1600 300 30
7521 WARD SALESMAN 7698 22-Feb-1981 1250 500 30
7566 JONES MANAGER 7839 2-Apr-1981 2975 20
Example 1 : Write a Query to update Allen COMM from 300 to 800
Example 2 : Write a Query to update all the employee designation as Manager for those
who are getting salary more then 4000;
Note : If we wont provide where Clause all the records will be updated.
9606060871 / 9606060872
ARC Technologies & Institutions
3) Delete
The delete statement is used to delete existing records in a table.
Syntax : DELETE FROM table_name
WHERE condition;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-1980 800 20
7499 ALLEN SALESMAN 7698 20-Feb-1981 1600 300 30
7521 WARD SALESMAN 7698 22-Feb-1981 1250 500 30
7566 JONES MANAGER 7839 2-Apr-1981 2975 20
Example 1 : Write a Query to delete Smith record from Database.
Example 2 : Write a Query to delete all the employee details whose designation is Manager.
Note : If we wont provide where Clause all the records will be deleted.
9606060871 / 9606060872
ARC Technologies & Institutions
9606060871 / 9606060872
