CN2180 MS SQL Server

1. CN2180MS SQL Server KemtisKunanuraksapong MSIS with Distinction, A+ MCTS, MCDST, MCP

2. Agenda • 3 SQL Statements – Insert, Update, Delete • Assignment • Quiz

3. INSERT Statement INSERT [INTO] tbl_name | view_name [(column_list)] {DEFAULT VALUES | VALUES ({DEFAULT | expression_1}…)} • Used to insert a single row. INSERT INTO tbl_name | view_name [(column_list)] {select_statement | execute_statement} • Used to insert from the SELECT statement or from the stored procedure.

4. Prepared the database USE cn2180 CREATE TABLE employee (emp_no INTEGER NOT NULL, emp_fname CHAR(20) NOT NULL, emp_lname CHAR(20) NOT NULL, dept_no CHAR(4) NULL) CREATE TABLE department(dept_no CHAR(4) NOT NULL, dept_name CHAR(25) NOT NULL, location CHAR(30) NULL)

5. Prepared the database (2) CREATE TABLE project (project_no CHAR(4) NOT NULL, project_name CHAR(15) NOT NULL, budget FLOAT NULL) CREATE TABLE works_on (emp_no INTEGER NOT NULL, project_no CHAR(4) NOT NULL, job CHAR (15) NULL, enter_date DATETIME NULL)

6. Insert data into database USE cn2180 INSERT INTO employee VALUES (25384, ‘Matthew’, ‘Smith’,’d3’) INSERT INTO employee VALUES (10102, ‘Ann’, ‘Jones’,’d3’) INSERT INTO employee VALUES (29346, ‘James’, ‘James’,’d2’) INSERT INTO employee VALUES (18316, ‘John’, ‘Barrimore’,’d1’) INSERT INTO employee VALUES (9031, ‘Elsa’, ‘Bertoni’,’d2’) INSERT INTO employee VALUES (2581, ‘Elke’, ‘Hansel’,’d2’) INSERT INTO employee VALUES (28559, ‘Sybill’, ‘Moser’,’d1’) INSERT INTO department VALUES (‘d1’, ‘Research’, ‘Dallas’) INSERT INTO departmentVALUES (‘d2’, ‘Accounting’, ‘Seattle’) INSERT INTO departmentVALUES (‘d3’, ‘Marketing’, ‘Dallas’)

7. Insert data into database (2) USE cn2180 INSERT INTO project VALUES (‘p1’, ‘Apollo’, 120000.00) INSERT INTO employee VALUES (‘p2’, ‘Gemini’, 95000.00) INSERT INTO employee VALUES (‘p3’, ‘Mercury’, 186500.00) INSERT INTO works_onVALUES (10102, ‘p1’, ‘Analyst’, ‘1997.10.1’) INSERT INTO works_onVALUES (10102, ‘p3’, ‘Manager’, ‘1999.1.1’) INSERT INTO works_onVALUES (25348, ‘p2’, ‘Clerk’, ‘1998.2.15’) INSERT INTO works_on VALUES (18316, ‘p2’, NULL, ‘1998.6.1’) INSERT INTO works_on VALUES (29346, ‘p2’, NULL, ‘1997.12.15’) INSERT INTO works_on VALUES (2581, ‘p3’, ‘Analyst’, ‘1998.10.15’) INSERT INTO works_on VALUES (9031, ‘p1’, ‘Manager’, ‘1998.4.15’) INSERT INTO works_on VALUES (28559, ‘p1’, ‘NULL’, ‘1998.8.1’) INSERT INTO works_on VALUES (28559, ‘p2’, ‘Clerk’, ‘1999.2.1’) INSERT INTO works_on VALUES (9031, ‘p3’, ‘Clerk’, ‘1997.11.15’) INSERT INTO works_on VALUES (29346, ‘p1’, ‘Clerk’, ‘1998.1.4’)

8. Insert data into database (3) USE cn2180 INSERT INTO employee (emp_no, emp_fname, emp_lname) VALUES (15201, ‘Dave’, ‘Davis’) • To insert multiple rows into the table • See Example 7.8 on Page 209

9. UPDATE Statement • WHERE statement isn’t required, but recommended. • *Unless you want to update every records* UPDATE tbl_name | view_name SET col_1 = expression [{col_2 = expression}…] FROM tbl_name1 | view_name1 [{tbl_name2 | view_name2}..]] WHERE condition USE cn2180 UPDATE works_on SET job = ‘Manager’ WHERE emp_no = 18316 AND project_no = ‘p2’

10. UPDATE Statement (2) • Update record with calculation • USE cn2180 • UPDATE project • SET budget = budget*0.56 • More complex UPDATE Statement • See Example 7.12, 7.13, 7.14 on Page 212 - 213

11. DELETE Statement DELETE tbl_name | view_name [FROM tbl_name1 | view_name1 [{, tbl_2 | view_2}…]] [WHERE predicate] DELETE tbl_name | view_name [WHERE condition] USE sample DELETE FROM works_on WHERE job = ‘Manager’ • See More complex example on page 214

12. Assignment • E.7.1, E.7.4 – E.7.7