Data Modification

Question 1

What are the most common SQL statements associated with Data Manipulation Language?

Answer 1

SELECT, INSERT, UPDATE, DELETE, TRUNCATE, and MERGE.

Question 2

What does the acronym DML stand for?

Answer 2

Data Manipulation Language

Question 3

Name the statements which T-SQL provides for inserting data into tables.

Answer 3

INSERT VALUES, INSERT SELECT, INSERT EXEC, SELECT INTO, and BULK INSERT.

Question 4

Describe the INSERT VALUES statement.

Answer 4

This statement is used to insert rows into a table based on specified values.

Example:

INSERT INTO dbo.Orders
  (orderid, orderdate, empid, custid)
VALUES
  (10003, '20160213', 4, 'B'),
  (10004, '20160214', 1, 'A'),
  (10005, '20160213', 1, 'C'),
  (10006, '20160215', 3, 'C');

A single row or multiple rows can be inserted.

Question 5

Describe the INSERT SELECT statement.

Answer 5

This statement inserts a set of rows returned by a SELECT query into a target table.

Example:

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE shipcountry = N’UK’;

Question 6

Describe the INSERT EXEC statment

Answer 6

This statement inserts a set of rows returned by a stored procedure or a dynamic SQL batch into a target table.

Example:

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
EXEC Sales.GetOrders @country = N’France’;

Question 7

Describe the SELECT INTO statement.

Answer 7

The SELECT INTO statement is a nonstandard T-SQL statement that creates a target table and populates it with the result set of a query. This statement cannot be used to insert data into an existing table; additionally, the target table’s structure and data are based on the source table. The SELECT INTO statement copies from the source the base structure (such as column names, types, nullability, and identity property) and the data. It does not copy from the source constraints, indexes, triggers, column properties such as SPARSE and FILESTREAM, and permissions.

Example:

SELECT country, region, city
INTO dbo.Locations
FROM Sales.Customers

Question 8

Describe the BULK INSERT statement.

Answer 8

This statement is used to insert into an existing table, data originating from a file. There are many options which may be specified when using this statement so refer to documentation if this statement is needed.

Example:

BULK INSERT dbo.Orders FROM 'c:\temp\orders.txt'
  WITH
    (
       DATAFILETYPE    = 'char',
       FIELDTERMINATOR = ',',
       ROWTERMINATOR   = '\n'
    );

Question 9

Name the two solutions which SQL Server supports to automatically generate numeric keys.

Answer 9

The identity column property and the sequence object.

Question 10

Describe how the Identity property is used.

Answer 10

Example:

CREATE TABLE  
(
keycol  INT         NOT NULL IDENTITY(1, 1)
name   VARCHAR NOT NULL
)

Identity is a standard column property. You can define this property for a column with any numeric type with a scale of zero (no fraction). When defining the property, you can optionally specify a seed (the first value) and an increment (a step value). If you don’t provide those, the default is 1 for both. You typically use this property to generate surrogate keys, which are keys that are produced by the system and are not derived from the application data.

Question 11

Describe how to select the Identity column without providing the column name and without using the *

Answer 11

SELECT $identity FROM

Question 12

What does @@identity return?

Answer 12

The last identity value generated by the session regardless of scope.

Question 13

What does SCOPE_IDENTITY() return?

Answer 13

This function returns the last identity value generated by the current scope. This function is preferred over @@identity.

Question 14

What does IDENT_CURRENT() return?

Answer 14

provides the last identity value assigned to a table regardless of session.

Question 15

True or False: the identity property can be added or removed from an existing column.

Answer 15

False.

Question 16

Describe the statements which must be made in order to insert user-defined values for an identity column when the INSERT INTO statement.

Answer 16

SET IDENTITY_INSERT ON;
INSERT INTO ( keycol, datacol) VALUES (5,’FFFFF’);
SET IDENTITY_INSERT OFF;

Question 17

Describe how to use the sequence object in T-SQL.

Answer 17

The sequence object is an object and not a property in T-SQL. This means that there can be multiple instances of this object in a db having different conditions.

Example of how to create this object.

CREATE SEQUENCE dbo.SeqOrderIDs AS INT (default is BIG INT) NO CYCLE;

NEXT VALUE FOR dbo.SeqOrderIDs; - produces the next value in the sequence. this statement is often provided as a default constraint for a column needing indexing.

Example:

ALTER TABLE dbo.T1
ADD CONSTRAINT DFT_T1_keycol
DEFAULT (NEXT VALUE FOR dbo.SeqOrderIDs)
FOR keycol;

Question 18

Name the two T-SQL statements provided for deleting rows from a table.

Answer 18

DELETE, TRUNCATE

Question 19

Describe the main difference between the DELETE statement and the TRUNCATE statement.

Answer 19

The Truncate Statement does not use a predicate to filter rows within a table. When TRUNCATE FROM is invoked. It deletes all rows from the table leaving it empty.

Question 20

Describe the UPDATE statement and its purpose.

Answer 20

The UPDATE statement is a standard statement you can use to update a subset of rows in a table. To identify the subset of rows you need to update, you specify a predicate in a WHERE clause. You specify the assignment of values to columns in a SET clause, separated by commas.

Example:

UPDATE dbo.OrderDetails
SET discount = discount + 0.05
WHERE productid = 51;

Question 21

List the major clauses of a MERGE statement.

Answer 21

MERGE INTO AS, USING AS ON, WHEN MATCHED THEN, UPDATE SET, WHEN NOT MATCHED THEN, INSERT VALUES

Example:

MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
  ON TGT.custid = SRC.custid
WHEN MATCHED THEN
  UPDATE SET
    TGT.companyname = SRC.companyname,
    TGT.phone = SRC.phone,
    TGT.address = SRC.address
WHEN NOT MATCHED THEN
  INSERT (custid, companyname, phone, address)
  VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address);