-- ============================================
-- Midterm_Exam_Students_Courses.sql
-- Base Schema for Midterm Exam
-- ============================================

-- Drop tables if they already exist
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE ENROLLMENTS CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL; END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE STUDENTS CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL; END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE COURSES CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL; END;
/

-- ============================================
-- STUDENTS Table
-- ============================================
CREATE TABLE STUDENTS (
    Student_ID     VARCHAR2(5) PRIMARY KEY,
    Student_Name   VARCHAR2(40) NOT NULL,
    BirthDate      DATE,
    Phone          VARCHAR2(15)
);

-- Sample data
INSERT INTO STUDENTS VALUES ('S101', 'Ali Ahmad', TO_DATE('1999-05-12','YYYY-MM-DD'), '0791122334');
INSERT INTO STUDENTS VALUES ('S102', 'Lina Khaled', TO_DATE('2000-09-01','YYYY-MM-DD'), '0789988776');
INSERT INTO STUDENTS VALUES ('S103', 'Sara Naser', TO_DATE('1998-02-22','YYYY-MM-DD'), '0776655443');
INSERT INTO STUDENTS VALUES ('S104', 'Omar Salim', TO_DATE('1997-11-10','YYYY-MM-DD'), '0795566778');
INSERT INTO STUDENTS VALUES ('S105', 'Hadi Yazan', TO_DATE('1999-03-18','YYYY-MM-DD'), '0782233445');

-- ============================================
-- COURSES Table
-- ============================================
CREATE TABLE COURSES (
    Course_ID     VARCHAR2(5) PRIMARY KEY,
    Course_Name   VARCHAR2(25),
    Credits       NUMBER(2) NOT NULL
);

-- Sample data
INSERT INTO COURSES VALUES ('C001', 'Database Systems', 3);
INSERT INTO COURSES VALUES ('C002', 'Programming I', 4);
INSERT INTO COURSES VALUES ('C003', 'Web Development', 3);
INSERT INTO COURSES VALUES ('C004', 'AI Fundamentals', 3);
INSERT INTO COURSES VALUES ('C005', DataBase Lab', 1);
INSERT INTO COURSES VALUES ('C006', ML Lab', 1);

COMMIT;
