Microsoft SQL Server

  . It is relational database management system developed by Microsoft.
  . Primary function is to storing and retrieving data as requested by any application which may be in the same computer or in the same network or across internet.
  . There are different editions of Microsoft SQL serer which can be used as per the requirement.
  . It is also referred as MS SQL Server or SQL server.

   Advantages:

           . Easy to use.

           . Includes professional and enterprise level database management software.

           . Close integration with .NET framework.

           . Individual tables may not be recovered back but complete database restoration is possible.

           . Good for Finance, IT services, Retail and Marketing, Computer Software industries.

   Disadvantages:

           . It is not an open source. Cost is the main disadvantage of MS SQL Server.

           . Designed to run on Windows based servers.

           . Compatibility issues with applications which are running on other platforms.

Installing MS SQL Server:

  . Download MS SQL Server from the internet.

 . Select Developer version and click on Download.

   . After downloading, run the executable file and it starts installation. Select Custom.

    . Choose Destination location and hit Install.

    . Now click on Installation in the left and select "New SQL server stand-alone installation" as shown by the arrow.

    . If you have a product key, then type or else click on Next.

    . Accept license and hit Next.

    . Next window may show error in Windows Firewall, hit Next.

   . Select Database Engine Services and hit Next.

    . Give a Named instance and instance ID. Hit Next.

    . Select SQL Server Agent and hit Next.

    . Select Mixed Mode for authentication and give password to connect. Add Current User and hit Next.

    . It shows all the files which are ready to install, hit Next.

    . It shows Install successful. Now close this.

    . We need a GUI to interact with database which is provided as Server Management Tool.
   . So go to the first window where you started installation and select Install SQL Server Management Tool. 

    . It shows the download file.
   . Download the file and run it. It shows the below window, click on Install.

    . Now open SQL Management Tool in your computer and login with credentials as given during the installation time.

   . Select the server and click on New Query tab.

    . Now create a Database using the following syntax:
     CREATE DATABASE database_name;

   . After writing this query, click on Execute to execute the query which you written in the database.

  Creating a table:

    . Now in that database, we can create table in which data is stored.
    . Use the following syntax to create a table:
      USE database_name;
      CREATE  TABLE table_name(Column_name datatype, Column_name datatype, Column_name datatype,......)

    . In the example, created a table called "emp" with columns Id, Name, Age.
    . Datatypes must be mentioned after defining the column name.
    . int - numbers
    . varchar - characters
    . date - date 

   Inserting values into the table:

   . The table created has the empty rows since data is not inserted into it.

   . We use the following syntax to insert data into the table:

     INSERT INTO table_name VALUES(list of values separated by commas)

   . After writing any query, we must simply click on Execute.

  Reading data from tables:

  . By using Insert command, we can insert values into the tables as many as we need.

  . To read all the data present in that table, we use the following command:

     SELECT * FROM table_name;

  . To read data from particular columns of a table, we use the following command:

     SELECT column_name1, column_name2 FROM table_name;

   

  . By using select command, data present in the table can be viewed below.

   Altering a Table:

   . Altering a table means we can add, delete, change datatype of a column, add or remove primary key or foreign key etc.

   . Following command is used to add a column to existing table:

     ALTER TABLE table_name ADD column_name datatype;

   . Command to drop a column from a existing table:

     ALTER TABLE table_name DROP column_name;

   . Command to modify datatype of a column:

     ALTER TABLE table_name ALTER column_name new_data_type;

   . Command to add primary constraint to a column:

    ALTER TABLE  table_name ADD CONSTRAINT 'constraint_name' Primary key(column_name);

   . Command to drop primary constraint:

     ALTER TABLE table_name DROP CONSTRAINT 'constraint_name';

   . Below figure shows an example how to add a column called Location to the existing table emp:

   Updating data in tables:

   . We use update command to update the data in the table.

   . Currently Location column has no data present in it.

   . So, we can use update command to insert values in that particular column.

  

   . Syntax for using Update command is as follows:

     UPDATE table_name SET column_name='value' WHERE condition;

   . WHERE is a command used to filter the data in tables.

   . The condition is followed by where which is used to update the value as mentioned in the condition.

   . Condition can be like =, >, >=, <, <= etc.

    . Similarly we can use the same command to update the current value with new value also.
   . Command to update the current data:
     UPDATE table_name SET Column_name="New_value" WHERE condition

    . By using SELECT command, we can check the updated data.

  Deleting a record from a table:

   . Delete command is used to delete a particular row.

   . Following command is used to delete a row:

     DELETE FROM table_name WHERE condition

    . Using select command, we can check whether data is deleted or not.