SQL Server

SQL Server

Sql script:

=====================================================================
CREATE TABLE [dbo].[Address] (
    [AddrId]  INT           NOT NULL,
    [Address] VARCHAR (100) NULL,
    PRIMARY KEY CLUSTERED ([AddrId] ASC)
);
Insert into address values(1,'Hyderabad')
Insert into address values(2,'Banglore')
Insert into address values(3,'Chennai')

CREATE TABLE [dbo].[Dept] (
    [DepId] INT          NOT NULL,
    [Dname] VARCHAR (50) NULL
);
insert into dept values(1,'IT')
insert into dept values(2,'HR')
insert into dept values(3,'FIN')

CREATE TABLE [dbo].[Technilogies] (
    [TechId]   INT           NOT NULL,
    [TechName] VARCHAR (100) NULL
);
insert into [dbo].[Technilogies] values(1,'C#')
insert into [dbo].[Technilogies] values(2,'ASP.NET')
insert into [dbo].[Technilogies] values(3,'SQL Server')
insert into [dbo].[Technilogies] values(4,'Azure')
insert into [dbo].[Technilogies] values(5,'WebAPI')

CREATE TABLE [dbo].[Employee] (
    [EId]          INT           NOT NULL,
    [Ename]        VARCHAR (100) NULL,
    [Description]  VARCHAR (200) NULL,
    [AddressType]  VARCHAR (100) NULL,
    [Address]      INT           NULL,
    [Gender]       Varchar(20)          NULL,
    [TechnologyID] INT           NULL,
    [Salary]       MONEY         NULL,
    [DepID]        INT           NULL
);

insert into [dbo].[Employee]  values(1,'srinivas','Hello test desc', 'Primary',1,'Male',1,50000,1)
insert into [dbo].[Employee]  values(2,'Shiva','Hello test desc', 'Secondary',2,'Male',2,50000,2)
insert into [dbo].[Employee]  values(3,'Teja','Hello test desc', 'Primary',3,'Male',3,50000,3)
insert into [dbo].[Employee]  values(4,'Kiran','Hello test desc', 'Secondary',2,'Male',1,50000,2)

insert into [dbo].[Employee]  values(5,'Kumar','Hello test desc', 'Primary',3,'Male',3,50000,1)

======================================================================

sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record

CREATE TABLE [dbo].[EmpAddress] (
    [AddrNo]   INT           NOT NULL,
    [EAddress] VARCHAR (100) NULL

);
CREATE TABLE [dbo].[Dept] (
    [DepId] INT          NOT NULL,
    [Dname] VARCHAR (50) NULL

);
CREATE TABLE [dbo].[Emp] (
    [Id]      INT          NOT NULL,
    [Ename]   VARCHAR (50) NULL,
    [Address] INT          NULL,
    [Salary]  MONEY        NULL,
    [DepID]   INT          NULL
);

CREATE TABLE EmpAddress(AddrNo INT PRIMARY KEY , EAddress VARCHAR(100))
INSERT INTO EmpAddress values(101,'Hyd Pragathi Nagar')
INSERT INTO EmpAddress VALUES(102,'Hyd Gachibowli')
INSERT INTO EmpAddress values(103,'Hyd Rayadurgam')
INSERT INTO EmpAddress values(104,'Hyd JNTU')

INSERT INTO EmpAddress values(105,'Hyd KPHB')


INSERT into emp VALUES(1,'Srinivas',101,15000,10)
INSERT into emp VALUES(2,'Srinivas',102,10000,11)
INSERT into emp VALUES(3,'Srinivas',103,12000,12)
INSERT into emp VALUES(4,'Srinivas',104,11000,10)

INSERT into emp VALUES(5,'Srinivas',105,14000,11)


===============================================================

--CREATE TABLE UserRoles(RID INT IDENTITY(1,1) PRIMARY KEY ,RoleName VARCHAR(100))

INSERT INTO UserRoles(RoleName) VALUES ('SuperAdmin')
INSERT INTO UserRoles(RoleName) VALUES ('Admin')
INSERT INTO UserRoles(RoleName) VALUES ('User')

CREATE TABLE UsersData(UID INT IDENTITY(1,1) PRIMARY KEY ,UserName VARCHAR(100), UserAddress VARCHAR(50), EmailID varchar(50),MobNumber BIGINT ,
RoleID INT FOREIGN KEY REFERENCES UserRoles(RID))
INSERT INTO UsersData(UserName,UserAddress,EmailID, RoleID,MobNumber) VALUES('Srinivas','Hyd','msrinivas0606@gmail.com',1,9177369409)

INSERT INTO UsersData(UserName,UserAddress,EmailID, RoleID,MobNumber) VALUES('Shiv','Hyd','maalesreenivas@gmail.com',2,8919047157)
INSERT INTO UsersData(UserName,UserAddress,EmailID, RoleID,MobNumber) VALUES('Teja','Hyd','msrinivas0606@gmail.com',3,9177369409)



CREATE TABLE Courses(CID INT  IDENTITY(1,1) PRIMARY KEY, CName VARCHAR(50), CType varchar(50),CCategoryID INT FOREIGN KEY REFERENCES CourseCategory(CourseCategoryID), AssignedTo INT)

INSERT INTO courses (CName,CType,AssignedTo)VALUES('','',)

CREATE TABLE CourseCategory(CourseCategoryID INT  IDENTITY(1,1) PRIMARY KEY, CategoryName VARCHAR(50))

INSERT INTO courses (CName,CType,CCategoryID,AssignedTo)VALUES('.NET','Video',1,2)
INSERT INTO courses (CName,CType,CCategoryID,AssignedTo)VALUES('Mathematical Solutions','Video',2,2)

INSERT INTO courses (CName,CType,CCategoryID,AssignedTo)VALUES('Environmental science','Video',3,3)

INSERT INTO CourseCategory (CategoryName)VALUES('Technology')
INSERT INTO CourseCategory (CategoryName)VALUES('Maths')
INSERT INTO CourseCategory (CategoryName)VALUES('Science')

CREATE TABLE Questionnaire(QID INT  IDENTITY(1,1) PRIMARY KEY, QName VARCHAR(50),CourseID INT FOREIGN KEY REFERENCES courses(CID),OptionID INT  )

CREATE TABLE QOptions (OptionID INT  IDENTITY(1,1) PRIMARY KEY,OptionValue VARCHAR(200))