Data Manipulation Language : Insert, Update, Delete in SQL

Create Database

Create some tables

DROP TABLE IF EXISTS PROJECT;
DROP TABLE IF EXISTS WORKS_ON;
DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE IF EXISTS DEPARTMENT;

-- DEPARTEMENT
CREATE TABLE DEPARTMENT(
Dname           VARCHAR(15) NOT NULL        UNIQUE,
Dnumber         INT         PRIMARY KEY,
Mgr_ssn         CHAR(9)     NOT NULL        CHECK (Mgr_ssn != 123456789),
Mgr_start_date  DATE);

-- EMPLOYEE
CREATE TABLE EMPLOYEE( 
Fname       VARCHAR(10) NOT NULL,
Minit       CHAR,
Lname       VARCHAR(20) NOT NULL,
Ssn         CHAR(9)     PRIMARY KEY,
Bdate       DATE,
Address     VARCHAR(30),
Sex         CHAR(1),
Salary      DECIMAL(5),
Super_ssn   CHAR(9),
Dno         INT         NOT NULL,
FOREIGN KEY (Dno)       REFERENCES Department (Dnumber));

-- PROJECT
CREATE TABLE PROJECT(
Pname       VARCHAR(15) NOT NULL,
Pnumber     INT         NOT NULL,
Plocation   VARCHAR(15),
Dnum        INT         NOT NULL,
PRIMARY KEY (Pnumber),
UNIQUE (Pname),
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber));

-- WORKS_ON
CREATE TABLE WORKS_ON(
Essn    CHAR(9)         NOT NULL,
Pno     INT             NOT NULL,
Hours   DECIMAL(3,1)    NOT NULL,
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber));

-- INSERT
INSERT INTO DEPARTMENT
VALUES ('Research',5,333445555,'1988-05-22'),
('Administration',4,987654321,'1995-01-01'),
('Headquarters',1,888665555,'1981-06-19');

-- DIFFERENT TYPE OF INSERT
-- INSERT INTO table-name VALUES (values) ; SINGLE VALUE
INSERT INTO EMPLOYEE
VALUES ('John','B','Smith',123456789,'1965-01-09','731 Fondren, Houston TX','M',30000,333445555,5);

-- INSERT INTO table-name VALUES (values) ; MORE THAN ONE VALUE
INSERT INTO EMPLOYEE
VALUES ('Franklin','T','Wong',333445555,NULL,NULL,'M',40000,888665555,5),
('Alicia','J','Zelaya',999887777,'1968-01-19','3321 Castle, Spring TX','F',25000,987654321,4);

-- INSERT INTO table-name (column-names) VALUES (values) ;   COMPLETE COLUMN
INSERT INTO EMPLOYEE (Fname, Minit, Lname,Ssn, Bdate, Dno, Salary, Address, Super_ssn, Sex)
VALUES ('Jennifer','S','Wallace',987654321,'1941-06-20',4,4300,'291 Berry, Bellaire TX',888665555,'F'),
('Ramesh','K','Narayan',666884444,'1962-09-15',5,38000,'975 Fire Oak, Humble TX',333445555,'M');

-- INSERT INTO table-name (column-names) VALUES (values) ;   SOME COLUMN
INSERT INTO EMPLOYEE (Fname, Minit, Lname,Ssn, Dno)
VALUES  ('Joyce','A','English',453453453,5),
        ('Ahmad','V','Jabbar',987987987,4); 
        
-- INSERT INTO table-name (column-names) VALUES (values) ;   RANDOM COLUMN
INSERT INTO EMPLOYEE (Fname, Lname,Ssn, Dno, Salary, Minit, Bdate)
VALUES ('James','Borg',888665555,1,55000,'E','1937-11-10'); 

INSERT INTO PROJECT
VALUES ('ProductX',1,'Bellaire',5),
('ProductY',2,'Sugarland',5),
('ProductZ',3,'Houston',5),
('Computerization',10,'Stafford',4),
('Reorganization',20,'Houston',1),
('Newbenefits',30,'Stafford',4);

INSERT INTO WORKS_ON
VALUES (123456789,1,32.5),
(123456789,2,7.5),
(666884444,3,40.0),
(453453453,1,20.0),
(453453453,2,20.0),
(333445555,2,10.0),
(333445555,3,10.0),
(333445555,10,10.0),
(333445555,20,10.0),
(999887777,30,30.0),
(999887777,10,10.0),
(987987987,10,35.0),
(987987987,30,5.0),
(987654321,30,20.0),
(987654321,20,15.0),
(888665555,20,16.0);

View The Tables

SELECT * FROM DEPARTMENT;

3 records
Dname	Dnumber	Mgr_ssn	Mgr_start_date
Research	5	333445555	1988-05-22
Administration	4	987654321	1995-01-01
Headquarters	1	888665555	1981-06-19

SELECT * FROM EMPLOYEE;

8 records
Fname	Minit	Lname	Ssn	Bdate	Address	Sex	Salary	Super_ssn	Dno
John	B	Smith	123456789	1965-01-09	731 Fondren, Houston TX	M	30000	333445555	5
Franklin	T	Wong	333445555	NA	NA	M	40000	888665555	5
Alicia	J	Zelaya	999887777	1968-01-19	3321 Castle, Spring TX	F	25000	987654321	4
Jennifer	S	Wallace	987654321	1941-06-20	291 Berry, Bellaire TX	F	4300	888665555	4
Ramesh	K	Narayan	666884444	1962-09-15	975 Fire Oak, Humble TX	M	38000	333445555	5
Joyce	A	English	453453453	NA	NA	NA	NA	NA	5
Ahmad	V	Jabbar	987987987	NA	NA	NA	NA	NA	4
James	E	Borg	888665555	1937-11-10	NA	NA	55000	NA	1

SELECT * FROM PROJECT;

6 records
Pname	Pnumber	Plocation	Dnum
ProductX	1	Bellaire	5
ProductY	2	Sugarland	5
ProductZ	3	Houston	5
Computerization	10	Stafford	4
Reorganization	20	Houston	1
Newbenefits	30	Stafford	4

SELECT * FROM WORKS_ON;

Displaying records 1 - 10
Essn	Pno	Hours
123456789	1	32.5
123456789	2	7.5
666884444	3	40.0
453453453	1	20.0
453453453	2	20.0
333445555	2	10.0
333445555	3	10.0
333445555	10	10.0
333445555	20	10.0
999887777	30	30.0

Foreign Key Constraint

DROP TABLE IF EXISTS WORKS_ON;
DROP TABLE IF EXISTS PROJECT;
DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE IF EXISTS DEPARTMENT;

CREATE TABLE DEPARTMENT(
Dname           VARCHAR(15) NOT NULL        UNIQUE,
Dnumber         INT         PRIMARY KEY,
Mgr_ssn         CHAR(9)     NOT NULL        CHECK (Mgr_ssn != 123456789),
Mgr_start_date  DATE);

CREATE TABLE EMPLOYEE( 
Fname       VARCHAR(10) NOT NULL,
Minit       CHAR,
Lname       VARCHAR(20) NOT NULL,
Ssn         CHAR(9)     PRIMARY KEY,
Bdate       DATE,
Address     VARCHAR(30),
Sex         CHAR(1),
Salary      DECIMAL(5),
Super_ssn   CHAR(9),
Dno         INT         NOT NULL,
FOREIGN KEY (Dno)       REFERENCES Department (Dnumber));

CREATE TABLE PROJECT(
Pname       VARCHAR(15) NOT NULL,
Pnumber     INT         NOT NULL,
Plocation   VARCHAR(15),
Dnum        INT         NOT NULL,
PRIMARY KEY (Pnumber),
UNIQUE (Pname),
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber));

CREATE TABLE WORKS_ON(
Essn    CHAR(9)         NOT NULL,
Pno     INT             NOT NULL,
Hours   DECIMAL(3,1)    NOT NULL,
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber));

INSERT INTO DEPARTMENT
VALUES ('Research',5,333445555,'1988-05-22'),
('Administration',4,987654321,'1995-01-01'),
('Headquarters',1,888665555,'1981-06-19');

INSERT INTO EMPLOYEE
VALUES ('John','B','Smith',123456789,'1965-01-09','731 Fondren, Houston TX','M',30000,333445555,5);

INSERT INTO EMPLOYEE
VALUES ('Franklin','T','Wong',333445555,NULL,NULL,'M',40000,888665555,5),
('Alicia','J','Zelaya',999887777,'1968-01-19','3321 Castle, Spring TX','F',25000,987654321,4);

INSERT INTO EMPLOYEE (Fname, Minit, Lname,Ssn, Bdate, Dno, Salary, Address, Super_ssn, Sex)
VALUES ('Jennifer','S','Wallace',987654321,'1941-06-20',4,4300,'291 Berry, Bellaire TX',888665555,'F'),
('Ramesh','K','Narayan',666884444,'1962-09-15',5,38000,'975 Fire Oak, Humble TX',333445555,'M');

INSERT INTO EMPLOYEE (Fname, Minit, Lname,Ssn, Dno)
VALUES  ('Joyce','A','English',453453453,5),
        ('Ahmad','V','Jabbar',987987987,4); 

INSERT INTO EMPLOYEE (Fname, Lname,Ssn, Dno, Salary, Minit, Bdate)
VALUES ('James','Borg',888665555,1,55000,'E','1937-11-10'); 

INSERT INTO PROJECT
VALUES ('ProductX',1,'Bellaire',5),
('ProductY',2,'Sugarland',5),
('ProductZ',3,'Houston',5),
('Computerization',10,'Stafford',4),
('Reorganization',20,'Houston',1),
('Newbenefits',30,'Stafford',4);

INSERT INTO WORKS_ON
VALUES (123456789,1,32.5),
(123456789,2,7.5),
(666884444,3,40.0),
(453453453,1,20.0),
(453453453,2,20.0),
(333445555,2,10.0),
(333445555,3,10.0),
(333445555,10,10.0),
(333445555,20,10.0),
(999887777,30,30.0),
(999887777,10,10.0),
(987987987,10,35.0),
(987987987,30,5.0),
(987654321,30,20.0),
(987654321,20,15.0),
(888665555,20,16.0);

CASCADE

DROP TABLE IF EXISTS Works_On;
DROP TABLE IF EXISTS WORKS_ON_CASCADE;
DROP TABLE IF EXISTS WORKS_ON_RESTRICT;
DROP TABLE IF EXISTS WORKS_ON_NO_ACTION;
DROP TABLE IF EXISTS WORKS_ON_SET_NULL;
DROP TABLE IF EXISTS WORKS_ON_SET_DEFAULT;

-- WORKS_ON_CASCADE
CREATE TABLE WORKS_ON_CASCADE(
Essn    CHAR(9)         NOT NULL,
Pno     INT             NOT NULL,
Hours   DECIMAL(3,1)    NOT NULL,
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)
                        ON UPDATE CASCADE
                        ON DELETE CASCADE);

INSERT INTO WORKS_ON_CASCADE
VALUES (123456789,1,32.5),
(123456789,2,7.5),
(666884444,3,40.0),
(453453453,1,20.0),
(453453453,2,20.0),
(333445555,2,10.0),
(333445555,3,10.0),
(333445555,10,10.0),
(333445555,20,10.0),
(999887777,30,30.0),
(999887777,10,10.0),
(987987987,10,35.0),
(987987987,30,5.0),
(987654321,30,20.0),
(987654321,20,15.0),
(888665555,20,16.0);

SELECT * FROM WORKS_ON_CASCADE;

RESTRICT

DROP TABLE IF EXISTS Works_On;
DROP TABLE IF EXISTS WORKS_ON_CASCADE;
DROP TABLE IF EXISTS WORKS_ON_RESTRICT;
DROP TABLE IF EXISTS WORKS_ON_NO_ACTION;
DROP TABLE IF EXISTS WORKS_ON_SET_NULL;
DROP TABLE IF EXISTS WORKS_ON_SET_DEFAULT;
-- WORKS_ON_RESTRICT
CREATE TABLE WORKS_ON_RESTRICT(
Essn    CHAR(9)         NOT NULL,
Pno     INT             NOT NULL,
Hours   DECIMAL(3,1)    NOT NULL,
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)
                        ON UPDATE RESTRICT
                        ON DELETE RESTRICT);

INSERT INTO WORKS_ON_RESTRICT
VALUES (123456789,1,32.5),
(123456789,2,7.5),
(666884444,3,40.0),
(453453453,1,20.0),
(453453453,2,20.0),
(333445555,2,10.0),
(333445555,3,10.0),
(333445555,10,10.0),
(333445555,20,10.0),
(999887777,30,30.0),
(999887777,10,10.0),
(987987987,10,35.0),
(987987987,30,5.0),
(987654321,30,20.0),
(987654321,20,15.0),
(888665555,20,16.0);

SELECT * FROM WORKS_ON_RESTRICT;

NO ACTION

DROP TABLE IF EXISTS Works_On;
DROP TABLE IF EXISTS WORKS_ON_CASCADE;
DROP TABLE IF EXISTS WORKS_ON_RESTRICT;
DROP TABLE IF EXISTS WORKS_ON_NO_ACTION;
DROP TABLE IF EXISTS WORKS_ON_SET_NULL;
DROP TABLE IF EXISTS WORKS_ON_SET_DEFAULT;

-- WORKS_ON_NO_ACTION
CREATE TABLE WORKS_ON_NO_ACTION(
Essn    CHAR(9)         NOT NULL,
Pno     INT             NOT NULL,
Hours   DECIMAL(3,1)    NOT NULL,
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)
                        ON UPDATE NO ACTION
                        ON DELETE NO ACTION);

INSERT INTO WORKS_ON_NO_ACTION
VALUES (123456789,1,32.5),
(123456789,2,7.5),
(666884444,3,40.0),
(453453453,1,20.0),
(453453453,2,20.0),
(333445555,2,10.0),
(333445555,3,10.0),
(333445555,10,10.0),
(333445555,20,10.0),
(999887777,30,30.0),
(999887777,10,10.0),
(987987987,10,35.0),
(987987987,30,5.0),
(987654321,30,20.0),
(987654321,20,15.0),
(888665555,20,16.0);

SELECT * FROM WORKS_ON_NO_ACTION;

SET NULL

DROP TABLE IF EXISTS Works_On;
DROP TABLE IF EXISTS WORKS_ON_CASCADE;
DROP TABLE IF EXISTS WORKS_ON_RESTRICT;
DROP TABLE IF EXISTS WORKS_ON_NO_ACTION;
DROP TABLE IF EXISTS WORKS_ON_SET_NULL;
DROP TABLE IF EXISTS WORKS_ON_SET_DEFAULT;
-- WORKS_ON_SET_NULL
CREATE TABLE WORKS_ON_SET_NULL(
Essn    CHAR(9)         NOT NULL,
Pno     INT,
Hours   DECIMAL(3,1)    NOT NULL,
--PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)
                        ON UPDATE SET NULL
                        ON DELETE SET NULL);

INSERT INTO WORKS_ON_SET_NULL
VALUES (123456789,1,32.5),
(123456789,2,7.5),
(666884444,3,40.0),
(453453453,1,20.0),
(453453453,2,20.0),
(333445555,2,10.0),
(333445555,3,10.0),
(333445555,10,10.0),
(333445555,20,10.0),
(999887777,30,30.0),
(999887777,10,10.0),
(987987987,10,35.0),
(987987987,30,5.0),
(987654321,30,20.0),
(987654321,20,15.0),
(888665555,20,16.0);

SELECT * FROM WORKS_ON_SET_NULL;

SET DEFAULT

DROP TABLE IF EXISTS Works_On;
DROP TABLE IF EXISTS WORKS_ON_CASCADE;
DROP TABLE IF EXISTS WORKS_ON_RESTRICT;
DROP TABLE IF EXISTS WORKS_ON_NO_ACTION;
DROP TABLE IF EXISTS WORKS_ON_SET_NULL;
DROP TABLE IF EXISTS WORKS_ON_SET_DEFAULT;
-- WORKS_ON_SET_NULL
CREATE TABLE WORKS_ON_SET_NULL(
Essn    CHAR(9)         NOT NULL,
Pno     INT,
Hours   DECIMAL(3,1)    NOT NULL,
--PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)
                        ON UPDATE SET NULL
                        ON DELETE SET NULL);

INSERT INTO WORKS_ON_SET_NULL
VALUES (123456789,1,32.5),
(123456789,2,7.5),
(666884444,3,40.0),
(453453453,1,20.0),
(453453453,2,20.0),
(333445555,2,10.0),
(333445555,3,10.0),
(333445555,10,10.0),
(333445555,20,10.0),
(999887777,30,30.0),
(999887777,10,10.0),
(987987987,10,35.0),
(987987987,30,5.0),
(987654321,30,20.0),
(987654321,20,15.0),
(888665555,20,16.0);

SELECT * FROM WORKS_ON_SET_NULL;