db_session_1.sql

-- Dropping the table 'courses' if it exists
DROP TABLE IF EXISTS courses;

-- Creates table 'courses' with the specified attributes and their respective datatype
CREATE TABLE courses(course_no INT PRIMARY KEY, course_name TEXT);

-- ALTER = Alters the Data Definition of a Table
-- Adding a column 'course_credits'
ALTER TABLE courses ADD course_credits INT;

-- Adding a column 'course_nooblife'
ALTER TABLE courses ADD course_nooblife TEXT;
-- Removing the column 'course_nooblife'
ALTER TABLE courses DROP course_nooblife;

-- Adding in a constraint. These constraints are checked when
--  an INSERT operation takes place
-- Constraints are identifiable by their names
-- Here, we create a constraint 'constraint_credits'
-- check() checks the condition specified in it
-- The condition is whether the 'course_credits' value is greater than 5
ALTER TABLE courses ADD CONSTRAINT constraint_credits check(course_credits > 5);

-- Removing the constraint 'constraint_credits'
ALTER TABLE courses DROP CONSTRAINT constraint_credits;

CREATE TABLE course_offer(
	course_no INT,
	course_section INT,
	course_year TEXT,
	course_semester TEXT,
	course_classroom TEXT,
	course_timing TEXT
);

-- Adding a constraint which makes
-- course_no, and course_section the Primary Keys of
-- The table 'course_offer'
ALTER TABLE course_offer 
	ADD CONSTRAINT course_offer_primary_key PRIMARY KEY(course_no, course_section);

CREATE TABLE student(
	student_no INT PRIMARY KEY,
	student_name TEXT
);

-- Foreign Key reference
CREATE TABLE enrolls(
	course_no INT REFERENCES courses(course_no),
	student_no INT REFERENCES student(student_no)
);