SQL Server Assignment
1) Programming in sql server
Assign1 :
Ref : Sql Server Notes
1) Calculate the factorial of the given number.
2) Find prime number in 1-100 numbers and print the same.
3) PRINT 1-10 odd even numbers.
2) The structured query language
Assign1 :
Ref: Sql Server Notes
1) Create tables
2) Use constrains and keys (PK, FK) as mentioned
Table creation guidelines
1) First column must be int, primary key and auto generated.
2) Use pascal notation for table name and column name.
Eg. Table Name – EmployeeMaster(here E capital and M capital), Column Name-
EmployeeId(E capital and I capital).
3) Table name should not start with any special character or number.
4) Spaces are not allowed in table name and column names
5) Use proper constrains, primary keys and foreign keys.
Constrains
1) All Name columns should have “Not Null” constrains
2) All “IsActive” colums – Default (1) constrains with default value 1
3) All tables columns must have “Primary key constraints” with identity (1,1) property.
Tables –
Refer “Client_DB” DB for below tables data types and columns
1) ItemMaster–
Create below table first –
select * From ColourMaster
select * from ManufacturerMaster
select * From CategoryMaster
select * from SubCategoryMaster
select * From UnitMaster
select * From BranchMaster
select * From Admin_LoginUserMaster
Once above tables are created, Create below table. Use above tables primary keys as a foreign key
in the below table –
Foreign Keys columns for below tables
(
[SubCategoryId] [int] ,
[ManufacturerId] [int] ,
[ColourId] [int] ,
[UnitId] [int] ,
[UserId] [int] ,
[BranchId] [int] ,
[CategoryId] [int] ,
)
select * From ItemMaster
2) CustomerMaster –
Create below table first –
select * From CustomerTypeMaster
select * From CountryMaster
select * From StateMaster
select * from CityMaster
Once above tables are created , Create below table. Use above tables primary keys as a foreign
keys in the below table –
Foreign Keys columns for below tables
(
CustomerTypeId ,
CityId] [int] ,
StateId] [int] ,
CountryId] [int] ,
BranchId] [int] ,
LoginUserId] [int] ,
)
select * From CustomerMaster
Database diagram
Admin_LoginUserMaster CustomerTypeMaster
LoginUserId CustomerTypeId
UserName CustomerType
Password IsActive
IsActive
RoleId
EmployeeId
BranchId
SystemEntryDate
CustomerMaster CityMaster
CityId
Fax
CityName
CountryMaster Website
CountryId GSTIN
StateId
CountryName Remarks
Username
LoginBranch
SystEmentryDate
CustomerTypeId
CityId
StateId
CountryId
BranchId
StateMaster
LoginUserId
StateId
StateName
CountryId
3) EmployeeMaster
Create below table first –
select * From BloodGroupMaster
select * From GenderMaster
select * From WeekOffMaster
select * From MaritalStatusMaster
select * From DepartmentMaster
select * From CityMaster
select * From StateMaster
select * From CountryMaster
select * From ContractorMaster
select * from DesignationMaster
select * from QualificationMaster
select * From DepartmentMaster
select * From LeaveMaster
Once above tables are created, Create below table. Use above tables primary keys as a foreign key
in the below table –
Foreign Keys columns for below tables
(
[GID] [int] , -- GenderId
[BloodGroupID] [int] ,
[MSID] [int] , -- MaritalStatusId
[LeaveId] [int] ,
[QualificationId] [int] ,
[DesignationId] [int] ,
[did] [int] , -- DepartmentId
[CategoryId] [int] ,
[WID] [int] , -- WeakOfId
[ContractorId] [int] ,
[StaffId] [int] ,
[PayTypeId] [int] ,
[UnitId] [int] ,
[CityId] [int] ,
[StateId] [int] ,
[CountryId] [int]
)
select * From EmployMaster
4) SupplierMaster -
Create below table first –
select * From suppliertypemaster
Once above tables are created, Create below table. Use above tables primary keys as a foreign key
in the below table –
Foreign Keys columns for below tables
(
supptypeid ,
CountryId ,
StateId ,
CityId ,
BranchId ,
LoginUserId
)
select * From SupplierMaster
Assign2 :
Ref : Sql Server Notes
1) Insert records in all tables which are created in the Assign1(Chapter 2)
Assign3 : - Indexes
select * From EmployMaster
select * From ItemMaster
select * From CustomerMaster
select * From SupplierMaster
eg.
create nonclustered index idx_ManfId on
ItemMaster(ManufacturerId)
Ref : Sql Server Notes
1) Apply Indexes on all foreign key in the table for above four main tables.
select * From ItemMaster
select * From CustomerMaster
select * From EmployMaster
select * from SupplierMaster
Assign4 : -Views
Ref : Sql Server Notes
1) Create complex view for below four main tables. Make sure it will join all main master and
its sub master tables data.
select * From ItemMaster
select * From CustomerMaster
select * From EmployMaster
select * from SupplierMaster
Assign5 : -Procedure
Ref : Sql Server Notes
1) Write procedures with DML(Insert,Update,Delete,Select-all, select By id) operations for
above 4 master tables. ItemMaster,CustomerMaster,SupplierMaster,EmployeeMaster.
