What is INSERT, DELETE, and UPDATE Statements in SQL

INSERT, DELETE, and UPDATE Statements in SQL:-

Three commands used to modify the database:  INSERT, DELETE, and UPDATE

INSERT typically inserts a tuple (row) in a relation( (table)

UPDATE may update a number of tuples (rows) in( a relation (table) that satisfy the condition 

DELETE may also update a number of tuples( (rows) in a relation (table) that satisfy the condition

The INSERT Command:

In its simplest form, it is used to add one or more tuples to a relation 

Attribute values should be listed in the same( order as the attributes were specified in the CREATE TABLE command  

Constraints on data types are observed( automatically  

Any integrity constraints as a part of the DDL specification are enforced

Specify the relation name and a list of values for the tuple. All values including nulls are supplied.

             U1: INSERT INTO EMPLOYEE

                  VALUES ( ‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-30’,             ‘98 Oak Forest, Katy, TX’, ‘M’, 37000, ‘653298653’, 4 );

The variation below inserts multiple tuples where a new table is loaded values from the result of a query.

       U3B: INSERT INTO WORKS_ON_INFO ( Emp_name, Proj_name,

Hours_per_week )

SELECT E.Lname, P.Pname, W.Hours

FROM PROJECT P, WORKS_ON W, EMPLOYEE E

WHERE P.Pnumber = W.Pno AND W.Essn = E.Ssn;

BULK LOADING OF TABLES

Another variation of INSERT is used for bulk-loading of several tuples into tables  

A new table TNEW can be created with the same( attributes as T and using LIKE and DATA in the syntax, it can be loaded with entire data.  EXAMPLE: CREATE TABLE D5EMPS LIKE EMPLOYEE (SELECT E.* FROM EMPLOYEE AS E WHERE E.Dno=5) WITH DATA;

The DELETE Command:

Removes tuples from a relation  

Includes a WHERE-clause to select the tuples to be( deleted  

Referential integrity should be enforced(  

Tuples are deleted from only one table at a time( (unless CASCADE is specified on a referential integrity constraint)  

A missing WHERE-clause specifies that all tuples in( the relation are to be deleted; the table then becomes an empty table  

The number of tuples deleted depends on the number( of tuples in the relation that satisfy the WHERE-clause

Includes a WHERE clause to select the tuples to be( deleted. The number of tuples deleted will vary

U4A: DELETE FROM EMPLOYEE

WHERE Lname = ‘Brown’;

U4B: DELETE FROM EMPLOYEE

WHERE Ssn = ‘123456789’;

U4C: DELETE FROM EMPLOYEE

WHERE Dno = 5;

U4D: DELETE FROM EMPLOYEE

The UPDATE Command:-

Used to modify attribute values of one or more selected tuples  

A WHERE-clause selects the tuples to be( modified  

An additional SET-clause specifies the attributes( to be modified and their new values 

Each command modifies tuples in the same( relation  

Referential integrity specified as part of DDL( specification is enforced

EXAMPLE: Change the location and controlling department number of project number 10 to 'Bellaire' and 5, respectively

U5: UPDATE PROJECT

SET Plocation = ‘Bellaire’, Dnum = 5

WHERE Pnumber = 10;

Example: Give all employees in the 'Research' department a 10% raise in salary.

 U6:UPDATE EMPLOYEE SET SALARY = SALARY *1.1 WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research')

In this request, the modified SALARY value depends on the original SALARY value in each tuple

(a)The reference to the SALARY attribute on the right of = refers to the old SALARY value before modification  

(b)The reference to the SALARY attribute on the left of =( refers to the new SALARY value after modification