MS SQL

Installation:

• Open the MS SQL server and download the sql server 2016.

• Open the software and click run as administrator.

• Downloads the SQL server 2016 express edition

• Open the setup and click new SQL server stand-alone installation.

• Accept the license terms and conditions.

• Installs all setup files which are required.

• Select all required software to be installed.

• Setup  server login authentication details.
• Windows Authentication.
• SQL server authentication.

• Installing the all software.

• Click install to start installing the Microsoft SQL server management studio.

• Installs the Management Studio.

Opening and   Creating the database:

• Open the  SQL server management studio.
• Enter the credentials, server type and server name.

• Right Click on Database and click New Database.  

Syntax:

• Creates the table employeeupdate.

• Insert into enters the values into the table.

• Table values after inserting.

• Create the new column in table by using Alter Add.

• Update used to change the data in particular row or cell.

• Insert values into employeeupdate after adding dateofjoining table.

• Filers the employeeid, pf from employeeupdate when employeeid  and employeename both matches to the given condition.

Joins : 

• Join query is used to display the values from the two different tables, using primary key as foreign key.
• Below figure shows types of the queries.

Inner Join :

• Inner Join only gives the details of the matching rows in the two tables.
• Syntax :
                 Select column_name from table_name1
                 Inner Join table_name2 on table_name1.column_name = table_name2.column_name
• Below figure show the example of inner join.

Left Join:

• Left Join gives the matching details in the two tables and all the values of the first table.
• Syntax :
                 Select column_name from table_name1
                 Left Join table_name2 on table_name1.column_name = table_name2.column_name
• Below figure shows the left join.

Right Join:

• Right join gives the matching values in the two tables and all the values of the second table.
• Syntax :
                 Select column_name from table_name1
                 Right Join table_name2 on table_name1.column_name = table_name2.column_name
• Below figure shows the right join.

Full Join:

• Full join displays all the values in both tables.
• Syntax :
                 Select column_name from table_name1
                 Full Join table_name2 on table_name1.column_name = table_name2.column_name
• Below figure is an example for the Full join.

Cross Join:

• Cross join is the number of rows in the first table multiplied by the number of rows in the second table.
• Syntax :
                 Select column_name from table_name1
                 Cross Join table_name2
• Below figure illustrate an example for the cross join.

Self Join:

• Self join is nothing but joining a table with itself is called as self join.
• Syntax :
                 Select table_alias1.name as new_table_column_name,table_alias2.name as new_table_column_name.                                                  From table_name table_alias1
                 Join table_name table_alias2
                 ON table_alias1.old_column_name = table_alias2.new_column_name. 

Index:

• Index is same like a regular index present in the textbook or regular books.
• It is used to retrieve the data from database very fast.
• Syntax :
                 Select table_alias1.name as new_table_column_name,table_alias2.name as new_table_column_name.
                 ON table_alias1.old_column_name = table_alias2.new_column_name.

Views:

• It is nothing but a save sql query.
• Which ever query we write to display a table output is saved in the form of the views.
• Views are nothing but a virtual tables.
• Fields in a view are one or more real tables in database.
• Syntax : 
               Create view virtual_table_name
               as
               table or multiple tables data retrieving query
• Below is an example of the views