University SQL

USE University3
GO

--> Question 1
CREATE PROC Proc_Courses_Department AS
    SELECT c.Course_Name, c.Credits
        FROM dbo.Courses c
        INNER JOIN dbo.Departments d ON c.Department_Code = d.Department_Code
        WHERE d.Department_Name = 'Minhal Asakim';
GO

EXEC Proc_Courses_Department



--> Question 2
ALTER PROC Proc_Courses_Department AS
    SELECT c.Course_Name, c.Credits
        FROM dbo.Courses c
        INNER JOIN dbo.Departments d ON c.Department_Code = d.Department_Code
        WHERE d.Department_Name = 'Computers';
GO

EXEC Proc_Courses_Department



--> Question 3
ALTER PROCEDURE Proc_Courses_Department
    @DeptCode DECIMAL
AS
    SELECT c.Course_Name, c.Credits
    FROM dbo.Courses c
    INNER JOIN dbo.Departments d ON c.Department_Code = d.Department_Code
    WHERE d.Department_Code = @DeptCode;
GO

EXEC Proc_Courses_Department @DeptCode=22



--> Question 4
CREATE PROCEDURE Proc_Students_Cities_Marks
    @CityCode DECIMAL,
    @MinGrade DECIMAL
AS
    SELECT s.First_Name, s.Last_Name, s.Address, m.Mark_Sem_A, m.Mark_Sem_B
    FROM dbo.Students s
    INNER JOIN dbo.Marks m ON m.Student_Id = s.Student_Id
    INNER JOIN dbo.Cities c ON s.Address = c.City_Name
    WHERE c.City_Code = @CityCode
      AND m.Mark_Sem_A >= @MinGrade AND m.Mark_Sem_B >= @MinGrade;
GO

EXEC Proc_Students_Cities_Marks @CityCode=1, @MinGrade=81



--> Question 5
CREATE PROCEDURE Proc_Courses_On_Air
    @OpenDate DATE,
    @EndDate DATE
AS
    SELECT coa.Open_Date, coa.End_Date, c.Course_Name
    FROM dbo.Courses_On_Air coa
    INNER JOIN dbo.Courses c ON coa.Course_Code = c.Course_Code
    WHERE coa.Open_Date >= @OpenDate AND coa.End_Date <= @EndDate;
GO

EXEC Proc_Courses_On_Air @OpenDate = '2016-11-01', @EndDate = '2016-02-25';



--> Question 6
CREATE PROCEDURE Proc_Courses_Dates_Teacher
    @TeacherID DECIMAL,
    @OpenDate DATE,
    @EndDate DATE
AS
    SELECT coa.Open_Date, coa.End_Date, c.Course_Name, t.First_Name
    FROM dbo.Courses_On_Air coa
    INNER JOIN dbo.Courses c ON coa.Course_Code = c.Course_Code
    INNER JOIN dbo.Teachers t ON t.Teacher_Id = coa.Teacher_Id
    WHERE coa.Open_Date >= @OpenDate AND coa.End_Date <= @EndDate
      AND coa.Teacher_Id = @TeacherID;
GO
EXEC Proc_Courses_Dates_Teacher @TeacherID = 11223344, @OpenDate = '2016-10-01', @EndDate = '2016-02-25';




--> Question 7
CREATE VIEW V_Students_Courses_Details AS
    SELECT s.Student_Id, s.First_name AS Student_First_Name, s.Last_name,
           c.Course_Name, m.Mark_Sem_A, m.Mark_Sem_B, t.First_Name AS Teacher_First_Name
    FROM dbo.Students s
    INNER JOIN dbo.Marks m ON m.Student_Id = s.Student_Id
    INNER JOIN dbo.Courses c ON c.Course_Code = m.Course_Code
    INNER JOIN dbo.Courses_On_Air coa ON coa.Course_Code = c.Course_Code
    INNER JOIN dbo.Teachers t ON coa.Teacher_Id = t.Teacher_Id;
GO

CREATE PROC Proc_Students_Courses_Details
    @StudentID DECIMAL
AS
    SELECT * FROM V_Students_Courses_Details WHERE Student_Id = @StudentID;
GO

EXEC Proc_Students_Courses_Details @StudentID=11111111