CpSc 3220 The Language of SQL

CpSc 3220The Language of SQL Chapter 17 Modifying 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 statements • Insert specific data that is included explicitly in the INSERT statement • 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 columnList2 FROM tableName2 WHERE 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 i where dept_name='History';

The DELETE statement • Even simpler than the UPDATE • General form DELETE FROM tableName [WHERE condition] • 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 table SET colName1 = expression1 [,colName2 = exp2 . . . ] WHERE condition

An Example of an UPDATE statement UPDATE instructor SET salary = salary * 1.5 WHERE dept_name = 'Comp. Sci.';

Another Example of an UPDATE statement UPDATE takes SET 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

CpSc 3220 The Language of SQL