Introduction to Structured Query Language (SQL) – Part – 2

Data Definition Language (DDL) in Structured Query Language (SQL)

The Data Definition Language (DDL) is a part of Structured Query Language (SQL) which allows database tables or relations to be formed or removed. An individual can describe indexes, different keys, relationship among the different tables or relations, as well as can enforce constrictions among the database tables or relations too.

The utmost significant Data Definition Language (DDL commands in Structured Query Language (SQL) are mentioned below:

• CREATE TABLE – This command forms a fresh database table or relation.

• ALTER TABLE – This command modifies the existing tables or relations of a database.

• DROP TABLE – This command removes an existing table or relation form the database.

In What Manner to Form A Table or Relation

Forming a database is generally straightforward. The code of the CREATE DATABASE command is as subsequent:

CREATE DATABASE My_Database_Name

here,

· My_Database_Name – It is the label of the fresh database that an individual wants to provide.

In this instance an individual will create a database named as XYZBank, accordingly the statement which an individual have to use is given below:

CREATE DATABASE XYZBank

The previous command forms a database titled as XYZBank in the “C:Program FilesMicrosoft SQL ServerMSSQLData folder”. The information file label of the database is XYZBank.mdf and the log file label is XYZBank_Log.ldf.

An individual can form a database in the Object Explorer windows by means of right – clicking the Database folder in addition to choosing the New Database choice from the shortcut menu. The minute a database is formed, the end – user, who forms that database, spontaneously turn out to be the proprietor of that database. The proprietor of the database is known as DBO.

As soon as the database is formed it is imaginable to start applying the design that was outlined beforehand. Therefore, an individual have formed the database and now it’s time to practice a number of Structured Query Language (SQL) to form the tables or relations as requisite by the design. All Structured Query Language (SQL) key words are presented in upper case and the variable names in a combination of upper as well as lower cases.

In SQL Server, an individual can form a table or relation by means of CREATE TABLE command. The code of the CREATE TABLE command is as follows:

CREATE TABLE

[ My_Database_Name . [ My_Schema_Name ] . ] My_Table_Name

( { < My_Column_Defination > | < My_Computed_Column_Defination > }

[ < My_Table_Constatints > ] )

Where,

· My_Database_Name – It stipulates the label of the database where the table or relation is to be formed. When an individual does not stipulates a database title, the table or relation is formed in the recent database.

· My_Schema_Name – It stipulates the schema title to which the fresh table or relation fits. Schema is a rational cluster of database items in a database. Schemas benefit in enhancing the handling part of the items in a database.

· My_Table_Name – It stipulates the fresh table or relation title that an individual wants to provide. The table or relation title should be under 128 characters.

· My_Column_Defination – It stipulates the label of the column or attribute in addition the labels should be exclusive in the table or relation. The column or attribute title should be under 128 characters

· My_Computed_Column_Defination – It stipulates the code that creates the data for the calculated column or attribute. A calculated column or attribute do not occurs materially in the system memory however it is castoff to produce a calculated data.

For an instance, if an individual have the PrincipleAmount kept in one column or attribute, Rate kept in another column or attribute and the TimePeriod kept in another column or attribute. Now, individual need to calculate the EMI value which is a computed column or attribute. In this case the individual can make practice of My_Computed_Column_Defination to find the EMI value of the loan. The subsequent SQL query presentations the usage of My_Computed_Column_Defination:

( PrincipleAmount * Rate * TimePeriod ) / 100

· My_Table_Constatints – It is a noncompulsory keyword which stipulates the PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constrictions.

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:

CREATE TABLE [ XYZBank ] . [ Customer ] . [ Details ] ( CustomerID VARCHAR (10) NOT NULL , Name VARCHAR (30) NOT NULL, Gender VARCHAR (7) NOT NULL, Address VARCHAR (MAX) NOT NULL , EmailID VARCHAR (25) NOT NULL , Phone INT NOT NULL , SocialSecurityID VARCHAR (15) NOT NULL )

In What Manner to Alter A Table or Relation

As soon as a table or relation is formed its arrangement is not necessarily secure in stone. In some sort of demanding necessities few modifications as well as the arrangement of the database can be modified to match the requirements. Structured Query Language (SQL) can be castoff to alter the arrangement of a table or relation, hence, for instance if an individual wants to insert a new column named as AccountType to the XYZBank . Customer . Details table or relation for knowing the type of bank account the customer is holding, then the individual can implement an ALTER TABLE statement as displayed below:

ALTER TABLE XYZBank . Customer . Details

ADD AccountType NVARCHAR (15) NOT NULL

For deleting any column or attribute replace the ADD key word with DROP, so to remove the column or attribute an individual just need to use the following Structured Query Language (SQL) command:

ALTER TABLE User DROP COLUMN AccountType;

In the upcoming part we will be discussing in what manner to delete a table or relation, how to make use of data manipulation language (DDL) in structured query language (SQL) and in what manner to insert information.