SQL statements (Data Manipulation Language)

 

Writing SQL Commands:

Before knowing what are DML commands have a glance at what are SQL commands and how to write these commands. well, Commands are the SQL statements that are written by following some syntax to extract data from the database mainly and later on perform some other operations to manage or retain the data according to constraint(rules) in the database. SQL statements consist of reserved words and User-defined words. Reserved words have some specific meanings and can be as it is. They must be spelled properly as required and can not be split across lines such as SELECT ,ALTER,UPDATE etc. User-defined words are created by the user (according to the syntax rule) and represent the name of various database objects such as table column Views etc.

Although SQL is a free format language the statements can be made more readable by following these steps.

Ø Each clause in a statement should begin on a new line.

Ø Uppercase letters are used to represent Reserved words and correctly spelled.

Ø Lower case letters are used to define user-defined words.

Ø Vertical bar ( | ) indicates a choice among alternatives e.g dept |emp |Salary.

SQL is not a case sensitive language but the user has to enter the exact match of the word to extract specific data for example if we store the person surname “SMITH” and search it using string “Smith” then the row will not be found.

DML( Data Manipulation Language) Commands :

DML stands for Data Manipulation Language. It is a sub-language of SQL (structured query language). It is the language that is used to manipulate data of a table means that through DML commands data can be inserted deleted and updated in a particular table. It is the language that is responsible for all kinds of changes that can be performed on the data in the database.DML commands are used to manage the data in the database. DML commands are used to populate and query the table. Some common SQL commands are ISERT IN TO, UPDATE, DELETE, etc. 

Now we’ll see the purpose and usage of these commands.

·        INSERT IN TO Statement:

After creating the table if the user wants to add additional rows then the INSERT IN TO command will be used. Its syntax is as follows.

INSERT IN TO table_name (column1,column2….)

VALUES( value1,value2…)

suppose there is a “Property” table having these columns.

Now we can insert a further row by the following syntax:

Previously there were two rows in the table but now the third row has been added successfully.

Note: The column_name is optional in  the statement, If omitted, SQL assumes a list of all columns in their original order that is specified in  CREATE TABLE statement (which is DDL statement used whenever the table is going to be created) .so when we add the row without specifying the column name we should keep in mind the order of columns and put appropriate values according to the column.

·        UPDATE Statement:

UPDATE STATEMENT allows the user to change the contents of the existing rows in a given table. its syntax is as follows.

UPDATE table_name

SET column_name = new_value

WHERE column_name = some_value

 In the upper Property table if we want to update the data of Rooms from 3000 to 3 and Rent from 4006 to 3000 in row#1 we’ll write this statement like this.

And the Result will be:

·        SELECT Statement:

The purpose of SELECT STATEMENT is to retrieve and display the data from one or more tables of the database.

Syntax:

 

For extracting data from one or more column:

SELECT column_name FROM table_name ;

For extracting data of all columns:

SELECT * FROM table_name ;

For extracting data by eluding duplicate rows:

SELECT DISTINCT column_name FROM table_name;

 

Example:

If we want to extract property_no and type then the query would be:

SELECT Property_no, Type FROM Property ;

Result:

If we apply this query it will show all the rows of the table named Property.

SELECT * FROM Property;

·        DELETE Statement:

The DELETE STATEMENT allows the rows to be deleted from the named(operational )table. This statement doesn’t delete the structure of a table instead, it is used to delete only the data or contents of a table. To delete the contents along with the structure or data definition DROP TABLE statement is used. Its syntax is as follows:

For deleting data of specific row:

DELETE FROM table_name

WHERE column_name = some_value;

 

For deleting data of entire rows:

DELETE FROM table_name;

DELETE * FROM table_name;

Example:

If we want to delete the data of row#2 of the Property table then we’ll execute this query through these statements.

    Result:

The row with PropertyNo PA34 has been deleted from the above table.

For deleting all the data from the table. We'll apply this query.

The result will be