MS SQL Training (Basics) Part-1

Written by creation2018, September 2, 2018


Requirement for MS SQL Development
Download SQL Express 2014 from microsoft website and install on your system by clicking on run as administrator.Link is here

Creation InfoTech SQL Training

than open MSSQL Management Studio select
Server Type : Database Engine
Server Name : ComputerName\SQLEXPRESS
Authentication : you can choose either Windows Authentication or UserID and Password Based SQL Server Authentication and then click on connect button.I would like to suggest u to choose SQL Server Authentication.

after that click on new query tab and open query editor.

Query 1 : Create a new Database
create database db_name
ex. create database ProjectX;
After creating database now expand databases directory so you will see ProjectX database is created there.

Query 2 : Drop an existing Database
drop database db_name
ex. drop database ProjectX;

Query 3 : Access a particular database from database server.Like I want to access ProjectX
use db_name
ex. use ProjectX;
So finally after accessing ProjectX you can perform operation on this database.Our default database is system generated Master.

Creation InfoTech SQL Training

Query 4 : Create a new Table

create table table_name(
col_name1 datatype,
col_name2 datatype,

col_name datatype
);

ex.
create table Customers(
CustomerID int,
Name varchar(100),
Email varchar(100),
Mobile varchar(20),
City varchar(100),
Payment int
);

Creation InfoTech SQL Training

Now I want to insert value into Customers table

Query 5 : Insert value into Table
a. If I want to insert value into all columns
insert into table_name values(val1,val2,val3….);
b. If I want to insert value into some specific columns
insert into table_name(col1,col2,col3….) values(val1,val2,val3….);

ex.
insert into Customers values(101,’Prabhat Mishra’,’[email protected]’,’9860332778′,’Pune’,2000);

insert into Customers(CustomerID,Name,Email) values(421,’Creation InfoTech’,’[email protected]’);

Now I want to check weather row has inserted or not

Query 6 : Fetch Rows from a table
select * from table_name;
ex. Select * from Customers;

Creation InfoTech SQL Training

Query 7 : Add a new column into an existing table
alter table table_name add col_name datatype;
Ex. alter table Customers add Gender varchar(40);

Creation InfoTech SQL Training

Query 8 : Drop an existing column from a table
alter table table_name drop colum col_name;
Ex. alter table Customers drop column Gender;