. 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.
. Select Developer version and click on Download.
. After downloading, run the executable file and it starts installation. Select Custom.
. 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.
. 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.
. Now in that database, we can create table in which data is stored.. 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:
. 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.