CpSc 3220The Language of SQL

Chapter 17Modifying Data

SQL handles CRUD operations

• The DML part of the SQL language allows the implementation of all CRUD operations

• C - create: handled by the INSERT statement• R – retrieve: handled by the SELECT statement• U – update: handled by the UPDATE statement• D – delete: handled by the DELETE statement

The INSERT statement

• INSERT statements allow in insertion of rows of data into an existing table

• Each inserted row must be unique, i.e., it must contain the primary key

• Some tables are defined to have auto-increment PKs and the DBMS will insert those values automatically

• Values need not be inserted for all columns, columns for which no value is inserted are set to NULL

• Insertions can be constrained by tables rules; i.e., PK and FK constraints

Basic Forms of the INSERT statement

• There are two basic types of INSERT statements1. Insert specific data that is included explicitly in

the INSERT statement2. Insert data that is obtained from a SELECT

statement (a subquery)

General Format for Type One INSERT

INSERT INTO tableName[(columnList)]VALUES(row1Values) [,(row2Values) . . .] ;

Example of Type One INSERT

INSERT INTO Student(ID, name, dept_name, tot_cred)VALUES('12127','Howard','Comp. Sci.','0') ;

Example of Type One INSERT

insert into student values ('54444','Wilson','Comp. Sci.', '0');

Example of Type One INSERT

insert into student (ID,name,tot_cred)values ('44499','Wilson', '0');

Example of Type One INSERT

insert into student (name,ID,tot_cred)values ('Bixby','44999', '0');

Example of Type One INSERT

INSERT INTO Student(ID, name, dept_name, tot_cred)VALUES(12127,’Howard’,,0) ;

Example of Type One INSERT

INSERT INTO Student(ID, name, tot_cred)VALUES(12127,’Howard’,0) ;

Example of Type One INSERT

INSERT INTO Student(ID, tot_cred, name)VALUES(12127, 0, ’Howard’) ;

Example of Type One INSERT

insert into student values ('64445','Wilson','Comp. Sci.', '0'),('74446','Jackson','Comp. Sci.', '0'),('74447','George','Comp. Sci.', '0'),('74440','James','Comp. Sci.', '0'),('74440','Andrews','Comp. Sci.', '0');

Error in Type One INSERT

insert into student values ('64445','Wilson','Comp. Sci.', '0'),('74446','Jackson','Comp. Sci.', '0'),('74447','George','Comp. Sci.', '0'),('74440','James','Comp. Sci.', '0'),('74440','Andrews','Comp. Sci.', '0');

Error in Type One INSERT

insert into student values ('64445','Wilson','Comp. Sci.', '0'),('74446','Jackson','Comp. Sci.', '0'),('74447','George','Comp. Sci.', '0'),('74440','James','Comp. Sci.', '0'),('74448','Andrews','CompSci.', '0');

General Format for Type Two

INSERT INTO tableName[(columnList1)]SELECT columnList2FROM tableName2WHERE condition;

Example of Type Two INSERT

insert into student (ID,name,dept_name,tot_cred)select i.id,i.name,'Comp. Sci.','0'from instructor as iwhere dept_name='History';

The DELETE statement

• Even simpler than the UPDATE• General form

DELETEFROM tableName[WHEREcondition]

• There is an alternate form to delete an entire table:TRUNCATE TABLE tableName

The UPDATE statement

• A bit more complex• We must specify the columns to be updated,

give an expression that specifies the update, and include logic for determining which rows are to be updated.

• All data can be supplied in the UPDATE statement

• Some data may be determined by subqueries

General Form of an UPDATE statement

UPDATE tableSET colName1 = expression1 [,colName2 = exp2 . . . ]WHERE condition

An Example of an UPDATE statement

UPDATE instructorSET salary = salary * 1.5WHERE dept_name = 'Comp. Sci.';

Another Example of an UPDATE statement

UPDATE takesSET grade = 'A+'WHERE course_id = 12345;

That’s It!

• You know all the CRUD about SQL, the DML part

• But you don’t know how to create at database• That’s what we look at next; the DDL part of

SQL• Read Chapter 18 of the Rockoff textbook