Working With Information Within The Tables / Relations Part – 1

Introduction

Once the formations of database as well as tables or relations are done, the subsequent stage is to stock information in the database. Begin a database designer; an individual will be necessitating altering or removing the information from the database’s tables or relation once in a while. So an individual can do these information managements by means of the Data Modification Language (DML) commands of Transact – SQL (T – SQL). Suppose, the information kept in the database requires to be obtained by means of diverse types of end – users systems, like – mobile devices or else the online web systems, so the information have to be kept in such an arrangement which can be understood through whichever end – user’s system. Intended for this, SQL Server permits an individual to stock the information in the Extensible Markup Language (XML) layout which can be understandable through every end – user’s system.

This article deliberates the knowledge in what way to practice the Data Modification Language (DML) commands to handle the information in the tables or relations. Additionally, it describes in what manner to handle the Extensible Markup Language (XML) information in the database tables or relations. In this article, an individual will pick up the adequate knowledge for handling the information by means of Data Modification Language (DML) commands plus will get to know which methods to use for handling the Extensible Markup Language (XML) information.

Controlling Information Through Data Modification Language (DML) Commands

Begin a database designer, an individual will be required to frequently add, update or remove information. All these actions guarantee that the information is the latest information. For an instance, in a database which preserves the customer particulars, an individual is required to add fresh data in the Customer . Details table or relation, every time a firsthand account is opened in the XYZBank. In the same way, if the particular of the customer alters, the individual is required to modify the present data. For an instance, if the phone number of some customer is changed, then the individual will be required to modify the present data to take effect. An individual can practice the Data Modification Language (DML) SQL commands to handle the information in every table or relation.

How To Add Information in a Table / Relation?

The minutest part of information which an individual can insert in a table or relation is a row or tuple. The individual can insert row or tuple by means of the INSERT Data Modification Language (DML) command. The code of the INSERT command is as follows:

INSERT [ INTO ] { My_Table_Name } [ ( My_Column_Name ) ] VALUES { DEFAULT | My_Values | My_Select_Command }

here,

· My_Table_Name – It stipulates the name of the table or relation into which the information is to be added. The INTO keyword is a non – compulsory keyword.

· My_Column_Name – It stipulates a non – compulsory constraint. An individual can make use of it at the time of fractional information insertion in a table or relation otherwise at the time of insertion in the columns or attributes in a dissimilar arrangement compare to the definition of the columns or attributes in the table or relation.

· DEFAULT – It stipulates the section which an individual can practice to add the by default data stated for the column or attributes. If a by default data is not stated for a column or attribute plus the column or attribute data is stated as NULL, then the NULL value is added in the column or attribute. If the column or attribute do not have some by default limitation devoted to it as well as do not permit the NULL value as the column or attribute data, then SQL Server generates an inaccuracy message as well as the insertion process is disallowed.

· My_Values – It stipulates the data values for the table or relation columns or attributes which have to be added as a row or tuple in the table or relation. When the column or attribute is to be given a by default data, an individual can use the DEFAULT keyword as an alternative of a column or attribute data value. The column or attribute data value can be an expression too.

· My_Select_Command – It stipulates a multiple inside (Nested) SELECT command which an individual can practice to add rows or tuples in the table or relation.

Instruction For Adding Rows / Tuples In A Table / Relation

An individual must think through the subsequent rules at the time of adding the information in the rows or tuples any tables or relations:

· The number of information values should be identical as the number of columns or attributes in the table or relation or else in the column or attribute list.

· The arrangement of adding the data values should be in the identical order in which the columns or attributes are itemized for adding.

· The information values section should not have the column or attribute which are declared with IDENTITY.

· The information categories of the data should match the information categories of the columns or attributes of the table or columns.

Think through an instance, of the XYZBank . Customer . Details table or relation which is castoff to stock the customer details of the bank. The subsequent table or relation defines the arrangement of the XYZBank . Customer . Details table or relation:

In the direction of adding any rows or tuples in the XYZBank . Customer . Details table or relation with every column or attribute data values, an individual can make use of several types of commands as follows:

1) Adding the data values without declaring the column or attribute list –

INSERT INTO [ XYZBank ] . [ Customer ] . [ Details ] VALUES ( ‘ C#25001 ’ , ‘ My Name ’ , ‘ Male ’ , ‘ My Address Line 1 , Address Line 2 ’ , ‘ MyEmailID . com ’ , ‘ 0123456789 ’ , ‘ SSID#3535359 ’ , ‘ Loan ’ )

2) Adding the data values with declaring the column or attribute list (maintaining the columns or attributes order as described in the table or relation definition) –

INSERT INTO [ XYZBank ] . [ Customer ] . [ Details ] ( CustomerID , Name , Gender , Address , EmailID , Phone , SocialSecurityID , AccountType ) VALUES ( ‘ C#25001 ’ , ‘ My Name ’ , ‘ Male ’ , ‘ My Address Line 1 , Address Line 2 ’ , ‘ MyEmailID . com ’ , ‘ 0123456789 ’ , ‘ SSID#3535359 ’ , ‘ Loan ’ )

3) Adding the data values with declaring the column or attribute list (not maintaining the columns or attributes order as described in the table or relation definition) –

INSERT INTO [ XYZBank ] . [ Customer ] . [ Details ] ( CustomerID , Name , Gender , Address , EmailID , Phone , AccountType , SocialSecurityID ) VALUES ( ‘ C#25001 ’ , ‘ My Name ’ , ‘ Male ’ , ‘ My Address Line 1 , Address Line 2 ’ , ‘ MyEmailID . com ’ , ‘ 0123456789 ’ , ‘ Loan ’ , ‘ SSID#3535359 ’ )

4) Adding the data values without declaring the column or attribute list (using NULL value for the columns or attributes that supports the NULL values) –

INSERT INTO [ XYZBank ] . [ Customer ] . [ Details ] VALUES ( ‘ C#25001 ’ , ‘ My Name ’ , ‘ Male ’ , ‘ My Address Line 1 , Address Line 2 ’ , NULL , ‘ 0123456789 ’ ,

‘ SSID#3535359 ’ , ‘ Loan ’ )

In the upcoming part we will be going through the Adding Fractional Information, Adding Information In Associated Tables / Relations, and Replicating Information From The Present Table /Relation To A Fresh Table / Relation in details.