Working With Information Within The Tables / Relations Part – 2

Adding Fractional Information

Subject to the constrictions smeared to the columns or attributes of the tables or relations, an individual can add fractional information in the database tables or relations. It means that at the time of carrying out the data insertion job, an individual can add information for discriminating columns or attributes in a table or relation also. It is not needed that an individual have to add data values for every columns or attributes in the table or relation. The SQL Server permits an individual to add fractional information for a column or attributes which tolerates the NULL value otherwise has a DEFAULT constriction allocated to it. The INSERT section lists the columns or attributes for which information is to be added, excluding those columns or attributes which tolerate the NULL value otherwise have a DEFAULT constriction. The VALUES section offers data values for the stated columns or attributes. The preceding instance of the XYZBank . Customer . Details table, the EmailID column or attribute permits an individual to give a NULL value in a row or tuple. Thus, an individual can make use of several types of commands as follows to add fractional information in the table or relation:

1) Adding the data values without declaring the column or attribute list (providing NULL value for the columns or attributes which support 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 ’ )

2) Adding the data values with declaring the column or attribute list. However, ignoring the NULL supporting columns or attributes from the column or attribute list as it will take the value NULL automatically (maintaining the columns or attribute order as described in the table or relation definition) –

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

3) Adding the data values with declaring the column or attribute list. However, ignoring the NULL supporting columns or attributes from the column or attribute list as it will take the value NULL automatically (not maintaining the columns or attributes order as described in the table or relation definition) –

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

Adding Information In Associated Tables / Relations

Information relating to an object can be kept over numerous table or relation. For that reason, at the time of inserting data for a fresh object, an individual is required to add fresh rows or tuples in every associated tables or relations. For this type of scenario, an individual is required to add the row or tuple in the table or relation which has the primary key first. Then, the individual can add that particular row or tuple in the associated table or relation having the foreign key.

Think through, an instance, in the XYZBank . Customer . Details database, the customer particulars are kept in the Customer . Details, Customer . Loan and Customer . SavingsAc tables or relation. In the direction of saving the particulars of a fresh customer, an individual is required to add the information in all the tables or relation. The subsequent commands add information of a fresh customer in the database:

1) Adding information in the Customer . Details table or relation (MASTER Table / relation)

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 ’ )

2) Adding information in the Customer . Loan table or relation

INSERT INTO [ XYZBank ] . [ Customer ] . [ Loan ] ( CustomerID , LoanID , LoanType , ROI , LoanAmt , EMI , TimePeriod ) VALUES ( ‘ C#25001 ’ , ‘ L#003 ’ , ‘ Personal ’ , 12 . 43 ,

‘ $ 20000 ’ , ‘ 4000 ’ , ‘ 2 Years ’ )

3) Adding information in the Customer . SavingsAc table or relation

INSERT INTO [ XYZBank ] . [ Customer ] . [ SavingsAc ] ( CustomerID , AccountNo , LastTranDate , ROI , BalanceAmt ) VALUES ( ‘ C#25001 ’ , 48494858548 , ‘ 12/10/2015 ’ , 04 .00 , ‘ $ 94000 ’ )

Replicating Information From The Present Table /Relation To A Fresh Table / Relation

At the time of adding the information in the table or relation, an individual may require the replica of the rows or tuples from a present table or relation to a different table or relation. An individual can perform this task by means of the SELECT * INTO command.

Think through an instance, in the XYZBank database, information of customer with a balance of over $ 50000 is needed to be saved in a different table or relation named as Customer . PriorityCustomer from the Customer . SavingsAc table or relation.

The subsequent commands will replica the data values from the Customer . SavingsAc table or relation into the Customer . PriorityCustomer table or relation:

SELECT * INTO Customer . PriorityCustomer FROM Customer . SavingsAc WHERE BalanceAmt >= ‘ 50000 ’

The previous command will form a table or relation titled as Customer . PriorityCustomer. This table or relation will have the identical arrangement as the Customer . SavingsAc.

In the upcoming part we will be going through the Altering Information In The Table / Relation, Instruction For Altering The Information In A Table / Relation, Removing Information From The Table / Relation and Removing Information From Linked Tables / Relation in details.