create database Demo //* To create database(Your databse name is Demo)*//
Use Demo //* To use database(now active databse name is Demo)*//
//* To create first table named Employees*//
create table Employees(
EmpID int NOT NULL CONSTRAINT PK_Emp_EmpID PRIMARY KEY,
EmpName varchar(50) NOT NULL,
DeptNo int NULL,
JobId int NOT NULL,
NetSalary decimal(10,2) NOT NULL
)
GO
//* To create first table named Departments*//
create table Departments(
DeptNo int NOT NULL CONSTRAINT PK_Dept_DeptNo PRIMARY KEY,
DeptName varchar(20) NOT NULL
)
GO
//* To create first table named Jobs*//
CREATE TABLE Jobs(
JobId int NOT NULL CONSTRAINT PK_Jobs_JobId PRIMARY KEY,
JobDesc varchar(20) NOT NULL
)
GO
//* To insert datas to table named Departments*//
INSERT INTO Departments VALUES(1000, 'DOTNET')
INSERT INTO Departments VALUES(2000, 'JAVA/J2EE')
INSERT INTO Departments VALUES(3000, 'TESTING')
INSERT INTO Departments VALUES(4000, 'DBA')
//* To insert datas to table named Jobs*//
INSERT INTO Jobs VALUES(100, 'TL/PL')
INSERT INTO Jobs VALUES(200, 'SR.DEVELOPER')
INSERT INTO Jobs VALUES(300, 'DEVELOPER')
INSERT INTO Jobs VALUES(400, 'JR.DEVELOPER')
//* To insert datas to table named Employees*//
INSERT INTO Employees VALUES(100, 'Raj', 1000, 200, 50000.00)
INSERT INTO Employees VALUES(200, 'Nirmala', 2000, 100, 60000.00)
INSERT INTO Employees VALUES(300, 'John', 3000, 200, 50000.00)
INSERT INTO Employees VALUES(400, 'Alex', 1000, 100, 60000.00)
INSERT INTO Employees VALUES(500, 'Leesa', 4000, 300, 30000.00)
INSERT INTO Employees VALUES(600, 'Reena', 1000, 400, 20000.00)
GO
//* To join datas from 3 table*//
SELECT EmpID,EmpName,NetSalary,E.DeptNo,DeptName,E.JobId,JobDesc FROM Employees AS E
INNER JOIN
Departments AS D ON E.DeptNo = D.DeptNo
INNER JOIN
Jobs AS J ON E.JobId = J.JobId