one to many relationship in sql

create table department ( dept_id int identity primary key, dept_name nvarchar(20) not null ) insert into department values('CS') insert into department values('MATHS') insert into department values('PHYSICS') insert into department values('CHEMISTRY') insert into department values('GEOLOGY') SELECT * FROM department create table TEACHERS ( T_id int identity primary key, T_name nvarchar(20) not null ) INSERT INTO TEACHERS VALUES('SALMAN') INSERT INTO TEACHERS VALUES('ALI') INSERT INTO TEACHERS VALUES('AHSAN') INSERT INTO TEACHERS VALUES('AHMED') INSERT INTO TEACHERS VALUES('BILAL') INSERT INTO TEACHERS VALUES('ZOHAIB') INSERT INTO TEACHERS VALUES('SANA') INSERT INTO TEACHERS VALUES('KHALID') INSERT INTO TEACHERS VALUES('KHIZRA') INSERT INTO TEACHERS VALUES('SAMI') INSERT INTO TEACHERS VALUES('UZAIR') INSERT INTO TEACHERS VALUES('RAZA') INSERT INTO TEACHERS VALUES('YASIR') SELECT * FROM TEACHERS CREATE TABLE T_D_RECROD ( S_ID INT IDENTITY PRIMARY KEY, FK_T INT unique FOREIGN KEY REFERENCES TEACHERS(T_id), FK_d INT FOREIGN KEY REFERENCES department(dept_id) ) insert into T_D_RECROD values(1,1) insert into T_D_RECROD values(3,1) insert into T_D_RECROD values(5,1) insert into T_D_RECROD values(7,1) insert into T_D_RECROD values(9,1) insert into T_D_RECROD values(11,1) insert into T_D_RECROD values(2,3) insert into T_D_RECROD values(4,4) insert into T_D_RECROD values(6,2) insert into T_D_RECROD values(8,3) insert into T_D_RECROD values(12,1) select t.T_id,t.T_name,d.dept_id,d.dept_name from T_D_RECROD r inner join TEACHERS t on t.T_id=r.FK_T inner join department d on d.dept_id=r.FK_d