Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server - Lecture 8: How to insert, update, and delete data - Lê Thị Tú Kiên

Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 1 
Lecture 8 
How to insert, update, 
and delete data 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 2 
Objectives 
Applied 
 Given the specifications for an action query, code the INSERT, 
UPDATE, or DELETE statement for doing the action. 
 Create a copy of a table by using the INTO clause of the SELECT 
statement. 
Knowledge 
 Describe the three types of action queries. 
 Explain how to handle null values and default values when coding 
INSERT and UPDATE statements. 
 Explain how the FROM clause is used in an UPDATE or 
DELETE statement. 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 3 
The syntax of the SELECT INTO statement 
SELECT select_list 
INTO table_name 
FROM table_source 
[WHERE search_condition] 
[GROUP BY group_by_list] 
[HAVING search_condition] 
[ORDER BY order_by_list] 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 4 
Create a complete copy of the Employee table 
SELECT * 
INTO EmployeeCopy 
FROM Employee; 
Create a partial copy of the Employee table 
SELECT * 
INTO RetireEmp 
FROM Employee 
WHERE YEAR(GETDATE())- YEAR(bdate)>60 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 5 
Create a table with summary rows 
SELECT DNo, SUM(Salary) AS 
SumOfSalaries 
INTO DepSumSalary 
FROM Employee 
GROUP BY DNo; 
Warnings 
 When you use the SELECT INTO statement to create a table, only 
the column definitions and data are copied. 
 Definitions of primary keys, foreign keys, indexes, default values, 
and so on are not included in the new table. 
Murach's SQL Server 2012, C7 © 2012, Mike Murach & Associates, Inc. 
Slide 6 
The syntax of the INSERT statement 
INSERT [INTO] table_name 
[(column_list)] 
[DEFAULT] VALUES (expression_1 [, 
expression_2]...) 
[, (expression_1 [, 
expression_2]...)...] 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 7 
The values for a new row in the Employee table 
Column Value 
FName Thanh 
Minit T 
LName Nguyen 
SSN 223344555 
BDate 1980-08-15 
Address 460 Dallas, Houston,TX 
Sex F 
Salary 35000 
SuperSSN 888665555 
DNo 4 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 8 
Insert the row without using a column list 
INSERT INTO EmployeeCopy 
VALUES ('Thanh', 'T', 'Nguyen', '223344555', 
 '1980-08-15','460 Dallas, Houston,TX', 
 'F', 35000, 888665555,4); 
Insert the row using a column list 
INSERT INTO EmployeeCopy 
(FName, Minit,LName,SSN,BDate,Address, 
 Sex,Salary,SuperSSN,DNo) 
VALUES ('Thanh', 'T', 'Nguyen', '223344555', 
 '1980-08-15','460 Dallas, Houston,TX', 
 'F', 35000, 888665555,4); 
The response from the system 
(1 row(s) affected) 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 9 
Insert three rows 
INSERT INTO Employee 
VALUES 
(N'Jonh ', N'B', N'Smith', N'123456789', 
CAST(0x21F20A00 AS Date), N'731 Fondren, 
Houston, TX', N'M', 30000, N'333445555', 5), 
(N'Franklin ', N'T', N'Wong', N'333445555', 
CAST(0x83050B00 AS Date), N'638 Voss, Houston, 
TX', N'M', 40000, N'888665555', 5), 
(N'Joyce ', N'A', N'English', N'453453453', 
CAST(0xE8FC0A00 AS Date), N'5631 Rice, Houston, 
TX', N'M', 25000, N'333445555', 5); 
The response from the system 
(3 row(s) affected) 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 10 
The definition of the ColorSample table 
Column Data Allow Default 
Name Type Length Identity Nulls Value 
ID Int 4 Yes No No 
ColorNumber Int 4 No No 0 
ColorName VarChar 10 No Yes No 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 11 
Six INSERT statements for the ColorSample table 
INSERT INTO ColorSample (ColorNumber) 
VALUES (606); 
INSERT INTO ColorSample (ColorName) 
VALUES ('Yellow'); 
INSERT INTO ColorSample 
VALUES (DEFAULT, 'Orange'); 
INSERT INTO ColorSample 
VALUES (808, NULL); 
INSERT INTO ColorSample 
VALUES (DEFAULT, NULL); 
INSERT INTO ColorSample 
DEFAULT VALUES; 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 12 
The ColorSample table after the rows are inserted 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 13 
The syntax of the INSERT statement 
for inserting rows selected from another table 
INSERT [INTO] table_name [(column_list)] 
SELECT column_list 
FROM table_source 
[WHERE search_condition] 
Insert retire employees into the RetireEmp table 
INSERT INTO RetireEmp 
SELECT * 
FROM Employee 
WHERE YEAR(GETDATE()) - YEAR(bdate)>60 
Note: The table RetireEmp must aready 
exist in the database COMPANY. 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 14 
The same INSERT statement with a column list 
INSERT INTO RetireEmp 
(FName, Minit,LName,SSN,BDate, 
Address,Sex,Salary,SuperSSN,DNo) 
SELECT 
 FName, Minit,LName,SSN,BDate, 
 Address,Sex,Salary,SuperSSN,DNo 
FROM Employee 
WHERE YEAR(GETDATE()) - YEAR(bdate)>60 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 15 
The syntax of the UPDATE statement 
UPDATE table_name 
SET column_name_1 = expression_1 [, 
column_name_2 = expression_2]... 
[FROM table_source [[AS] table_alias] 
[WHERE search_condition] 
Update two columns of a single row 
UPDATE EmployeeCopy 
SET Salary = 40000, 
 DNo = 5 
WHERE SSN = '223344555'; 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 16 
Update one column of multiple rows 
UPDATE EmployeeCopy 
SET DNo=3 
WHERE DNo = 4; 
Update a column using an arithmetic expression 
UPDATE EmployeeCopy 
SET Salary = Salary + 1000 
WHERE DNo = 3; 
Warning 
 If you omit the WHERE clause, all the rows in the 
table will be updated. 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 17 
A subquery that returns the value assigned 
to a column 
UPDATE EmployeeCopy 
SET Salary = (SELECT AVG(Salary) 
 FROM EmployeeCopy 
 WHERE DNo = 5) 
WHERE SSN = '223344555'; 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 18 
A subquery used in a search condition 
UPDATE EmployeeCopy 
SET Salary = Salary + 1000 
WHERE DNo = (SELECT DNumber 
 FROM Department 
 WHERE DName = 'Research'); 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 19 
A column in a joined table 
used in a WHERE clause 
UPDATE EmployeeCopy 
SET Salary = Salary + 1000 
FROM EmployeeCopy JOIN Department 
 ON DNo=DNumber 
WHERE DName = 'Research'; 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 20 
The syntax of the DELETE statement 
DELETE [FROM] table_name 
[FROM table_source] 
[WHERE search_condition] 
Delete a single row from the EmployeeCopy table 
DELETE EmployeeCopy 
WHERE Ssn = '223344555'; 
(1 row(s) affected) 
Delete all the employees for a department 
DELETE EmployeeCopy 
WHERE Dno = 3; 
(3 row(s) affected) 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 21 
Delete all the rows 
DELETE EmployeeCopy; 
Warning 
 If you omit the WHERE clause from a DELETE 
statement, all the rows in the table will be deleted. 
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE 
Slide 22 
A subquery used in a search condition 
DELETE EmployeeCopy 
WHERE DNo = (SELECT DNumber 
 FROM Department 
 DName = 'Research') 
The same statement using a join 
DELETE EmployeeCopy 
FROM EmployeeCopy JOIN Department 
 ON DNo=DNumber 
WHERE DName = 'Research';