Basic SQL queries

Create a new table :

Create table tblEmployee (EmployeeID int primary key identity, Name VARCHAR(30), Gender VARCHAR(10), City VARCHAR(40), DeptID int)

Fetching data from table :

Select * from tblEmployee

Insert sample data to table :

Insert into tblEmployee values (10117, 'Venu', 'Male', 'Hyderabad',2)
Insert into tblEmployee values (10118, 'Naresh', 'Male', 'NewYork', 3)
Insert into tblEmployee values (10119, 'Varun', 'Male', 'Secunderabad',3)
Insert into tblEmployee values (10120, 'Angel', 'Female', 'London',1)

Add new column after table created :

ALTER table tblEmployee ADD DepartmentName int)

Making column as NOT NULL :

ALTER TABLE [tblEmployee] ALTER COLUMN [EmployeeID] int NOT NULL

Making column as PRIMARY KEY :

ALTER TABLE tblEmployee ADD PRIMARY KEY (EmployeeID)


Create table tblDepartments (ID int PRIMARY KEY, Name VARCHAR(30))
Insert into dbo.tblDepartments values (1, 'HR')
Insert into dbo.tblDepartments values (2, 'IT')
Insert into dbo.tblDepartments values (3, 'Engineering')

Table name change :

EXEC sp_rename 'Departments','tblDepartments'

Select * from tblDepartments

Stored procedure for adding new employee :

Create procedure spAddEmployee
@Name nvarchar(50),
@Gender nvarchar(10),
@City nvarchar(10),
@DateOfBirth DateTime
as
Begin
Insert into tblEmployee (Name, Gender , City, DateOfBirth)
values (@Name, @Gender, @City,@DateOfBirth)

End