-- Alter Table, Add column 

-- Add a column named Head_Of_Department ot the table. It is a variable length string with up to 100 to store the department head's name 
ALTER TABLE Department 
ADD COLUMN Head_Of_Department VARCHAR(100);

-- Modify the Office_Location column in the department table to increase its limit from 50 to 100 characters 
ALTER TABLE Department 
MODIFY COLUMN Office_Location VARCHAR(100);


-- Delete the Office_Location column from the department table 
ALTER TABLE Department 
DROP COLUMN Office_Location; 

-- Modify the program table to add a foreign key constraint on Department_ID linking it to the Department (DepartmentID) column in the Department Table 
ALTER TABLE Department 
ADD CONSTRAINT fk_Department
FOREIGN KEY(Department_ID) REFERENCES Department(Department_ID); 

ALTER TABLE Porgram 
ADD CONSTRAINT fk_Department 
FOREIGN KEY(Department_ID) REFERENCES Department(Department_ID);

-- Drop the student table from the database 
DROP TABLE Student; 

-- Delete the student_courses view 
DROP VIEW student_Courses; 

-- Create a view called FT_Instructors to display all full-time(FT) Insturctors display their ID and name 
CREATE VIEW FT_Instructors AS 
SELECT InstructorID, Name 
FROM Instructor 
WHERE Job_Type 'FT'; 

-- add two new programs to the program table 
INSERT INTO Program(ID, Number_Of_Courses, Rating, Level, Department_ID)VALUES 
    (106, 'Game Development',8, 4.60, 'Undergraduate', NULL), 
    (107, 'Artificial Intelligence', 9, 4.80, 'Graduate', 5); 

-- Remove the index idx_course_name of the course table 
DROP INDEX idx_course_name ON Course; 


-- Write an SQL query to increase all students age by 1 year 
UPDATE Student 
SET Age = Age + 1; 


-- Update the Program_ID to 5 for the student with ID = 101 
UPDATE Student 
SET Program_ID = 1 
WHERE ID = 101; 

-- Retrieve students older than 20 enrolled in program 102 
SELECT * 
FROM Student 
WHERE Age >= 20 AND Program_ID = 102;