SQL Table manipulation: A mind Map

This is the 3rd post to expose a mind-map on sql, this time: Table Manipulation, this is necessary to CRUD our data in the datable defines via DDL, check the first post here.

1 INSERT Statement

It has two different forms: INSERT [INTO] tab_name [(col_list)] DEFAULT VALUES | VALUES ({ DEFAULT | NULL | expression } [ ,…n] ) INSERT INTO tab_name | view_name [(col_list)] {select_statement | execute_statement} N>all character-based values and temporal data must be enclosed in apostrophes

1.1 ex

 USE sample; INSERT INTO employee VALUES (25348, 'Matthew', 'Smith','d3'); INSERT INTO employee VALUES (10102, 'Ann', 'Jones','d3'); INSERT INTO employee VALUES (18316, 'John', 'Barrimore', ' 


 USE sample; INSERT INTO employee (emp_no, emp_fname, emp_lname) VALUES (15201, 'Dave', 'Davis'); -- single row insertion 


 INSERT INTO dallas_dept (dept_no, dept_name) SELECT dept_no, dept_name FROM department WHERE location = 'Dallas'; -- multiple rows insertion 

1.2 table (or row) value constructor
it allows you to assign several tuples (rows) with a DML statement such as INSERT or UPDATE.

1.2.1 ex

 USE sample; INSERT INTO department VALUES ('d4', 'Human Resources', 'Chicago'), ('d5', 'Distribution', 'New Orleans'), ('d6', 'Sales', 'Chicago'); 

2 UPDATE Statement

UPDATE tab_name { SET column_1 = {expression | DEFAULT | NULL} [,...n] [FROM tab_name1 [,...n]] [WHERE condition]
2.1 SET clause

2.2 ex

 USE sample; UPDATE works_on SET job = 'Manager' WHERE emp_no = 18316 AND project_no = 'p2'; 

3 DELETE Statement

DELETE FROM table_name [WHERE predicate]; DELETE table_name FROM table_name [,…] [WHERE condition] N> after a DELETE statement, the table still exists in the database (although possibly with zero rows), but after a DROP TABLE statement, the table no longer exists.

3.2 ex

 USE sample; DELETE FROM works_on WHERE job = 'Manager'; 

3.3 TRUNCATE TABLE Statement
TRUNCATE TABLE table_name it drops the contents of the table page by page, while DELETE drops the contents row by row.

4 MERGE Statement

This statement combines the sequence of conditional INSERT and UPDATE commands in a single atomic statement N> new in sql 2008

4.1 ex

 USE sample; CREATE TABLE bonus (pr_no CHAR(4), bonus SMALLINT DEFAULT 100); INSERT INTO bonus (pr_no) VALUES ('p1'); 

5 OUTPUT Clause
INSERT, UPDATE, or DELETE statements always contains only the text concerning the number of modified rows; the OUTPUT clause, which displays explicitly the rows affected by the operation

5.1 ex

 USE sample; DECLARE @del_table TABLE (emp_no INT, emp_lname CHAR(20)); DELETE employee OUTPUT DELETED.emp_no, DELETED.emp_lname INTO @del_table WHERE emp_no > 15000; SELECT * FROM @del_table 

About authorOutlineShareDownload


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s