Notes

CREATE TABLE Instructor(
_id int IDENTITY(1,1),
InstructorName varchar(20),
DepartmentId int

)

CREATE TABLE Department(
_id int IDENTITY(100,1),
DepartmentName varchar(20),
)

CREATE TABLE Student(
_id int IDENTITY(700,1),
StudentName varchar(20),
)

CREATE TABLE Course(
_id int IDENTITY(500,1),
CourseName varchar(50),
StudentId varchar(50),
InstructorId varchar(50)
)


INSERT INTO Department VALUES('CSE')
INSERT INTO Department VALUES('IT')
INSERT INTO Department VALUES('ECE')
INSERT INTO Department VALUES('ME')
INSERT INTO Department VALUES('CE')
INSERT INTO Department VALUES('ETC')

INSERT INTO Instructor values ('John',105)
INSERT INTO Instructor values ('Robert',101)
INSERT INTO Instructor values ('James',103)
INSERT INTO Instructor values ('Michael',102)
INSERT INTO Instructor values ('Donald',100)
INSERT INTO Instructor values ('Steve',104)

INSERT INTO Student values('Mark')
INSERT INTO Student values('Ronald')
INSERT INTO Student values('Eldward')
INSERT INTO Student values('Jecob')
INSERT INTO Student values('Garry')
INSERT INTO Student values('Eric')

INSERT INTO Course values('Fundementals of Java',700,1)
INSERT INTO Course values('CORE Mechanical',702,3)
INSERT INTO Course values('CORE Civil',701,4)
INSERT INTO Course values('Fundementals of IT',703,1)
INSERT INTO Course values('CORE Electronics',705,2)
INSERT INTO Course values('Fundementals of ETC',704,5)

SELECT D.DepartmentName, I.InstructorName FROM Department D LEFT JOIN Instructor I ON D._id=I.DepartmentId
SELECT I.InstructorName,D.DepartmentName FROM Instructor I RIGHT JOIN Department D ON D._id=I.DepartmentId
SELECT C.CourseName, S.StudentName FROM Course C JOIN Student S ON C.StudentId=S._id

CREATE PROCEDURE SP_getStudentforAllCourse
AS BEGIN
SELECT C.CourseName, S.StudentName FROM Course C LEFT JOIN Student S ON C.StudentId=S._id
END;
exec SP_getStudentforAllCourse

CREATE PROCEDURE SP_getInsCourseWise
AS BEGIN
select I.InstructorName,C.CourseName from Instructor I LEFT JOIN Course C on I._id=C.InstructorId
END;
exec SP_getInsCourseWise


CREATE PROCEDURE SP_FindInstructor
AS BEGIN
SELECT D.DepartmentName, I.InstructorName FROM Department D LEFT JOIN Instructor I ON D._id=I.DepartmentId
END;

EXEC SP_FindInstructor

CREATE PROCEDURE SP_FindStudentEnrolled3
@Id int
AS BEGIN
SELECT StudentName from Student where _id=(Select StudentId from Course where _id=@Id)
END;

EXEC SP_FindStudentEnrolled3 500

CREATE PROCEDURE SP3
AS BEGIN
RETURN
(Select Count(StudentId) from Course where _id=500)
END;
Declare @count int;
Exec @count=SP3
Print CAST(@count as varchar)

CREATE PROCEDURE SP5
@Id int ,
@count int output
AS BEGIN

Select @count=count(StudentId) from Course where _id=@Id
END;

Declare @s_count int;
Exec SP5 500, @s_count out
Print CAST(@s_count as varchar)