create database CTU__YourICASNo GO USE CTU__YourICASNo; GO CREATE TABLE Course( CourseID int PRIMARY KEY, CourseName varchar(255), CourseDesc varchar(255), ); insert into Course(CourseID,CourseName,CourseDesc) values(11334,'Foundation','Basic1') insert into Course(CourseID,CourseName,CourseDesc) values(11335,'MCSD','Basic2'); insert into Course(CourseID,CourseName,CourseDesc) values(11336,'MCSE','Basic3'); insert into Course(CourseID,CourseName,CourseDesc) values(11337,'MCSA','Basic4'); insert into Course(CourseID,CourseName,CourseDesc) values(11338,'CAD','Basic5'); CREATE TABLE Students( ICASNo int Primary Key, StudentName varchar (255), StudentSurname varchar (255), StudentContact varchar (255), StudentEmail varchar(255), HighSchoolAttended varchar(255), courseID varchar (255), ); insert into Students(ICASNo,StudentName,StudentSurname,StudentContact,StudentEmail,HighSchoolAttended,courseID) values(11189,'Fiona','Battle','1-657-624-8476','sapien.imperdiet.ornare@veliteu.co.uk','Grey High-School',11334); insert into Students(ICASNo,StudentName,StudentSurname,StudentContact,StudentEmail,HighSchoolAttended,courseID) values(11190,'Ryan','Avery','(267) 835-3606','velit.justo@utodiovel.net','Boys High Kimberley',11335); insert into Students(ICASNo,StudentName,StudentSurname,StudentContact,StudentEmail,HighSchoolAttended,courseID) values(11191,'Angelica','Moon','(427) 885-6026','integer.vulputate@justonecante.com','Oranje Meisies High-School',11334); insert into Students(ICASNo,StudentName,StudentSurname,StudentContact,StudentEmail,HighSchoolAttended,courseID) values(11192,'Lester','Fernandez','(703) 939-2798','curabitur.consequat.lectus@porttitoreros.edu','Fichardtpark',11337); insert into Students(ICASNo,StudentName,StudentSurname,StudentContact,StudentEmail,HighSchoolAttended,courseID) values(11193,'Priscilla','Rasmussen','1-747-549-7171','congue@tacitisociosqu.co.uk','Diamantveld High-School',11334); CREATE TABLE Semester( SemesterID int Primary Key, SemesterStartDate varchar(255), SemesterEndDate varchar(255), ICASNo varchar(255), ); insert into Semester(SemesterID,SemesterStartDate,SemesterEndDate,ICASNo) values(34567,'Aug 1, 2021','May 27,2022',11190) insert into Semester(SemesterID,SemesterStartDate,SemesterEndDate,ICASNo) values(98709,'Dec 13, 2021','18 Nov, 2022',11192) insert into Semester(SemesterID,SemesterStartDate,SemesterEndDate,ICASNo) values(76345,'Jul 16, 2021','Jun 21, 2022',11189) insert into Semester(SemesterID,SemesterStartDate,SemesterEndDate,ICASNo) values(42900,'Oct 24, 2021','Oct 27, 2022',11193) insert into Semester(SemesterID,SemesterStartDate,SemesterEndDate,ICASNo) values(67234,'Jan 20, 2021','Feb 18, 2022',11191) CREATE TABLE Facilitator( FacilitatorID int Primary Key, FacilitatorName varchar(255) , FacilitatorContact varchar(255), FacilitatorEmail varchar(255), ICASNo varchar(255), ); insert into Facilitator(FacilitatorID,FacilitatorName,FacilitatorContact,FacilitatorEmail,ICASNo) values(9009127,'Kelsie Lewis','(328) 114-3041','mollis.lectus.pede@fuscealiquam.net',11110) insert into Facilitator(FacilitatorID,FacilitatorName,FacilitatorContact,FacilitatorEmail,ICASNo) values(9808139,'Charity Mcfarland','(572) 501-4782','sapien.cras@integeridmagna.com',11111) insert into Facilitator(FacilitatorID,FacilitatorName,FacilitatorContact,FacilitatorEmail,ICASNo) values(8802190,'Hilary Nixon','1-710-696-7666','varius@sollicitudinamalesuada.ca',11112) insert into Facilitator(FacilitatorID,FacilitatorName,FacilitatorContact,FacilitatorEmail,ICASNo) values(9012109,'Deirdre Terrell','1-491-662-4421','elit.fermentum.risus@ipsumsodales.edu',11113) insert into Facilitator(FacilitatorID,FacilitatorName,FacilitatorContact,FacilitatorEmail,ICASNo) values(7809179,'Tyrone Erickson','1-831-287-2701','dictum.eu@cras.com',11114) CREATE TABLE Module( ModuleID int Primary Key, ModuelName varchar(255), ModuleDuration varchar(255), FacilitatorID varchar(255), ); insert into Module(ModuleID,ModuelName,ModuleDuration,FacilitatorID) values(101,'Foundation','3years',9808139) insert into Module(ModuleID,ModuelName,ModuleDuration,FacilitatorID) values(102,'CAD','2years',8802190) insert into Module(ModuleID,ModuelName,ModuleDuration,FacilitatorID) values(103,'MCSA','3years',7809179) insert into Module(ModuleID,ModuelName,ModuleDuration,FacilitatorID) values(104,'MCSD','1year',9012109) insert into Module(ModuleID,ModuelName,ModuleDuration,FacilitatorID) values(105,'MCSE','2years',9009127) Create a query that retrieve information from Table Facilitator and Table Module. The result should be join using the facilitatorID how do i do this in SQL Server
create
GO
USE CTU__YourICASNo;
GO
CREATE TABLE Course(
CourseID int PRIMARY KEY,
CourseName varchar(255),
CourseDesc varchar(255),
);
insert into Course(CourseID,CourseName,CourseDesc)
values(11334,'Foundation','Basic1')
insert into Course(CourseID,CourseName,CourseDesc)
values(11335,'MCSD','Basic2');
insert into Course(CourseID,CourseName,CourseDesc)
values(11336,'MCSE','Basic3');
insert into Course(CourseID,CourseName,CourseDesc)
values(11337,'MCSA','Basic4');
insert into Course(CourseID,CourseName,CourseDesc)
values(11338,'CAD','Basic5');
CREATE TABLE Students(
ICASNo int Primary Key,
StudentName varchar (255),
StudentSurname varchar (255),
StudentContact varchar (255),
StudentEmail varchar(255),
HighSchoolAttended varchar(255),
courseID varchar (255),
);
insert into Students(ICASNo,StudentName,StudentSurname,StudentContact,StudentEmail,HighSchoolAttended,courseID)
values(11189,'Fiona','Battle','1-657-624-8476','sapien.imperdiet.ornare@veliteu.co.uk','Grey High-School',11334);
insert into Students(ICASNo,StudentName,StudentSurname,StudentContact,StudentEmail,HighSchoolAttended,courseID)
values(11190,'Ryan','Avery','(267) 835-3606','velit.justo@utodiovel.net','Boys High Kimberley',11335);
insert into Students(ICASNo,StudentName,StudentSurname,StudentContact,StudentEmail,HighSchoolAttended,courseID)
values(11191,'Angelica','Moon','(427) 885-6026','integer.vulputate@justonecante.com','Oranje Meisies High-School',11334);
insert into Students(ICASNo,StudentName,StudentSurname,StudentContact,StudentEmail,HighSchoolAttended,courseID)
values(11192,'Lester','Fernandez','(703) 939-2798','curabitur.consequat.lectus@porttitoreros.edu','Fichardtpark',11337);
insert into Students(ICASNo,StudentName,StudentSurname,StudentContact,StudentEmail,HighSchoolAttended,courseID)
values(11193,'Priscilla','Rasmussen','1-747-549-7171','congue@tacitisociosqu.co.uk','Diamantveld High-School',11334);
CREATE TABLE Semester(
SemesterID int Primary Key,
SemesterStartDate varchar(255),
SemesterEndDate varchar(255),
ICASNo varchar(255),
);
insert into Semester(SemesterID,SemesterStartDate,SemesterEndDate,ICASNo)
values(34567,'Aug 1, 2021','May 27,2022',11190)
insert into Semester(SemesterID,SemesterStartDate,SemesterEndDate,ICASNo)
values(98709,'Dec 13, 2021','18 Nov, 2022',11192)
insert into Semester(SemesterID,SemesterStartDate,SemesterEndDate,ICASNo)
values(76345,'Jul 16, 2021','Jun 21, 2022',11189)
insert into Semester(SemesterID,SemesterStartDate,SemesterEndDate,ICASNo)
values(42900,'Oct 24, 2021','Oct 27, 2022',11193)
insert into Semester(SemesterID,SemesterStartDate,SemesterEndDate,ICASNo)
values(67234,'Jan 20, 2021','Feb 18, 2022',11191)
CREATE TABLE Facilitator(
FacilitatorID int Primary Key,
FacilitatorName varchar(255) ,
FacilitatorContact varchar(255),
FacilitatorEmail varchar(255),
ICASNo varchar(255),
);
insert into Facilitator(FacilitatorID,FacilitatorName,FacilitatorContact,FacilitatorEmail,ICASNo)
values(9009127,'Kelsie Lewis','(328) 114-3041','mollis.lectus.pede@fuscealiquam.net',11110)
insert into Facilitator(FacilitatorID,FacilitatorName,FacilitatorContact,FacilitatorEmail,ICASNo)
values(9808139,'Charity Mcfarland','(572) 501-4782','sapien.cras@integeridmagna.com',11111)
insert into Facilitator(FacilitatorID,FacilitatorName,FacilitatorContact,FacilitatorEmail,ICASNo)
values(8802190,'Hilary Nixon','1-710-696-7666','varius@sollicitudinamalesuada.ca',11112)
insert into Facilitator(FacilitatorID,FacilitatorName,FacilitatorContact,FacilitatorEmail,ICASNo)
values(9012109,'Deirdre Terrell','1-491-662-4421','elit.fermentum.risus@ipsumsodales.edu',11113)
insert into Facilitator(FacilitatorID,FacilitatorName,FacilitatorContact,FacilitatorEmail,ICASNo)
values(7809179,'Tyrone Erickson','1-831-287-2701','dictum.eu@cras.com',11114)
CREATE TABLE Module(
ModuleID int Primary Key,
ModuelName varchar(255),
ModuleDuration varchar(255),
FacilitatorID varchar(255),
);
insert into Module(ModuleID,ModuelName,ModuleDuration,FacilitatorID)
values(101,'Foundation','3years',9808139)
insert into Module(ModuleID,ModuelName,ModuleDuration,FacilitatorID)
values(102,'CAD','2years',8802190)
insert into Module(ModuleID,ModuelName,ModuleDuration,FacilitatorID)
values(103,'MCSA','3years',7809179)
insert into Module(ModuleID,ModuelName,ModuleDuration,FacilitatorID)
values(104,'MCSD','1year',9012109)
insert into Module(ModuleID,ModuelName,ModuleDuration,FacilitatorID)
values(105,'MCSE','2years',9009127)
Create a query that retrieve information from Table Facilitator and Table Module. The result should be join using the facilitatorID
how do i do this in SQL Server
Trending now
This is a popular solution!
Step by step
Solved in 7 steps with 6 images