Structured Query Language (SQL): Data Manipulation Language (DML)

Structured Query Language (SQL) is a Query Language that is used to retrieve, add, update and delete records from a Relational Database Management System (RDBMS). Within SQL there are two main types of statements: Data Definition Language (DDL) and Data Manipulation Language (DML) Statements. This article focuses on Data Manipulation Language (DML). There are four main commands in Data Manipulation Language (DML): SELECT, INSERT, UPDATE and DELETE. These commands can affect one to many rows in a Database.

Structured Query Language like other languages has Data Types and Comparison Operators. When using an expression to compare a Data Type, there are specific formatting requirements. For example, String Data Types are wrapped in single quotes.

SQL Data Types

1. String - String Data Types are wrapped in single quotes, i.e. FieldName1 = 'Microsoft'
2. Numerical - Numerical Data Types are only the number, i.e. FieldName1 = 5
3. DateTime - DateTime Data Types are wrapped in single Quotes, i.e. FieldName1 = '2030-01-01'

Comparison Operators

1. =
2. <>
3. >
4. <
5. >=
6. <=
7. BETWEEN
8. LIKE
9. [NOT] IN
10. IS [NOT] NULL

1. SELECT - The SELECT Statement is used to retrieve rows from a Database Table. The SELECT Statement has the following elements: Field Name(s) and Table Name(s). In addition, a SELECT Statement may also have a Where Clause, an Order By Clause, a Group By Clause and Join Statements. A basic SELECT Statement is in the following form:
   

   'SELECT [ID], [Company], [Last Name], [First Name], [E-mail Address] FROM [Customers] WHERE [ID] = 5 ORDER BY [Last Name], [First Name]
                               
   This example is for the Customers Table of Microsoft's Northwind Sample Database
   '--------------------------------------------------------------------------------------
   SELECT FieldName1, FieldName2, FieldName3 FROM TableName WHERE FieldName1 = Numeric, String or DateTime Value ORDER BY FieldName2
                             
   

   The following is an alternate syntax for the same SQL Statement.
   

   SELECT
       TableName1.FieldName1,
       TableName1.FieldName2,
       TableName2.FieldName3,
       TableName2.FieldName4
   FROM
       TableName1 INNER JOIN TableName2
         ON TableName1.FieldName1 = TableName2.FieldName3
   WHERE
       TableName1.FieldName1 = TableName2.FieldName3
   

2. INSERT - The INSERT Statement is used to add rows to a Database Table. The INSERT Statement has the following elements: Field Name(s) and a Table Name. An INSERT Statement is in the following form:
   

   'INSERT INTO [Customers] ([Company], [Last Name], [First Name], [City]) VALUES ('Software Startup Company', 'Jason', 'Smith', 'San Francisco')
   This example is for the Customers Table of Microsoft's Northwind Sample Database.
   '--------------------------------------------------------------------------------------
   INSERT INTO Table_Name (FieldName1, FieldName2, FieldName3) VALUES (FieldValue1, FieldValue2, FieldValue3)
                             

3. UPDATE - The UPDATE Statement is used to update the values of in one or more rows in a Database Table. The UPDATE Statement has the following elements: Field Name(s) and a Table Name. An UPDATE Statement is in the following form:
   

   'UPDATE [Customers] SET [Company] = 'Software Engineering Company', [City] = 'Kansas City' WHERE [ID] = 1
   This example is for the Customers Table of Microsoft's Northwind Sample Database.  The ID Value is set by the AutoNumber ID field after the record is inserted into the Customers Table.
   '--------------------------------------------------------------------------------------
   UPDATE Table_Name SET FieldName2 = FieldValue2, FieldName3 = FieldValue3, FieldName4 = FieldValue4 WHERE FieldName1 = Numeric, String or DateTime Value
                             

4. DELETE - The DELETE Statement is used to delete one or more rows in a Database Table. The DELETE Statement has the following elements: a Table Name. A DELETE Statement is in the following form:
   

   'DELETE FROM [Customers] WHERE [ID] = 1
   This example is for the Customers Table of Microsoft's Northwind Sample Database.  The ID Value is set by the AutoNumber ID field after the record is inserted into the Customers Table.
   '--------------------------------------------------------------------------------------
   DELETE FROM Table_Name WHERE FieldName1 = Numeric, String or DateTime Value