Servage Magazine

As promised in the second part of this series, I have initiated a discussion on DML (Data Manipulation Language) section in SQL. One more thing you may have noticed in the syntax of SQL statements, that each SQL statement is ending with semicolon as well as SQL commands are not case sensitive at all. Moreover, text values are in quotes and numeric values are without quotes.

DML is use mainly to interact within database so retrieving, inserting, and modifying database object and its components. Since these activities are occurring in routine operations, the DML commands are high in usage. However, they are a few DML commands we should know at this stage such as:

• SELECT: To extract data from the database
• INSERT: This command also refers as INSERT INTO and use to add new data into a database of choice
• UPDATE: To update data in a selected database
• DELETE: To deleted selected data from a database of data object

Let’s look at these tags/commands closely.

SELECT Command to Select Data

Generally, select statement is used to run query in the database in order to perform many other operations including retrieving, deleting, or modifying the database components and data objects. Its syntax is in following format:

SELECT column_name,column_name
FROM table_name;

Or

select "column1"
[,"column2",etc]
from "tablename"
[where "condition"];
[] = optional

When you define the column names SELECT command will retrieve data only from them, but if you have sizable database or table and wish to select all columns from it you should use universal selector (*) in following way:

SELECT * FROM table_name;

In FROM tag you need to specify the data object or table from where you querying. In standard tagging WHERE clause is used frequently in optional manner. In this clause, you have to mention the condition that returned or display the data values as per your specifications. There are few numbers of conditions or conditional selections occur within SELECT command such as:

=             Equal
>             Greater than
<             Less than
>=           Greater than or equal
<=           Less than or equal
<>           Not equal to
LIKE        A pattern matching operator

However LIKE is a powerful operator and you can select specific rows as per your likeness “like”. Here you have a wild card in form of % (percentage sign) to match any possible character that may occur before or after the characters specified. For instance:

SELECT “first_name”, “last_name”
FROM “table_name”
WHERE last LIKE '%s'

In above code the result will match the all last names, which are ending in “s”.

INSERT Command to Insert Data

This command is used to insert the record or data into the database or data object/table. Therefore, its syntax is accordingly. You need to specify the database name at first and then define column/row. In value clause, you need to specify your data that you want to insert them in same respective order that you mentioned in the column parenthesis. You should enclosed all strings between single quotes in most of the DBMS.

UPDATE Command to Update Record

This command is useful when you need to update existing data in the database and its syntax is in following way:

UPDATE "table_name"
SET "column_name" =
"new_value"
[,"next_column" =
"new_value2"...]
WHERE "column_name"
OPERATOR "value"
[and|or "column"
OPERATOR "value"];

[] = optional

Here we have powerful WHERE clause to deal with intricate queries as discussed above in SELECT command.

DELETE Command to Delete Record

This is same as above UPDATE command with WHERE clause. Its syntax is as following:

DELETE from "table_name"
WHERE "column_name"
OPERATOR "value"
[and|or "column"
OPERATOR "value"];

[ ] = optional

Be careful, using this command because if you leave off the where clause, all your recordes will be deleted.

References & More Reading
Data Manipulation Language
Introduction to SQL
What is SQL?