CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),  
  dob DATE,
  grade INT,
  address VARCHAR(100),
  phone VARCHAR(40),
  email VARCHAR(150)
);

CREATE TABLE instructor (
    instructor_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50), 
    phone VARCHAR(40),
    email VARCHAR(150)
);
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50),
    course_description TEXT,
    start_date DATE,
    end_date DATE,
    course_fees DECIMAL(10,2),   
    -- DECIMAL(digit,digit after decimal point)
    instructor_id INT
);

CREATE TABLE registration (
    registration_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    registration_date DATE
);
INSERT INTO students VALUES
(101,"Rahul","Sharma","2004-05-15",10,"Kolkata","567892345","rahulsharma19@gmail.com"),
(102,"Priya","Das","2004-07-13",11,"Bhopal","978892345","priyadas19@gmail.com");

INSERT INTO instructor VALUES
(1,"Anil","Verma","456788443","anilverma56@gmail.com"),
(2,"Sneha","Singh","945678443","snehasingh45@gmail.com");

INSERT INTO courses VALUES
(201,"SQL basics","Introduction to SQL","2026-08-01","2026-09-15",5000.0,1),
(202,"Web development","Html","2026-09-01","2026-11-30",8000.0,2),
(203,"Python","Learn Python from scratch","2026-08-01","2026-09-30",10000.0,2);

INSERT INTO registration VALUES
(1001,101,201,"2026-07-25"),
(1002,102,202,"2026-06-28");

-- SELECT * FROM students;
-- SELECT * FROM instructor;
-- SELECT * FROM courses;
-- SELECT * FROM registration;

-- SELECT courses.course_name,instructor.first_name,instructor.last_name
-- FROM courses JOIN instructor
-- ON courses.instructor_id=instructor.instructor_id;

-- SELECT students.first_name,students.last_name,registration.registration_date,courses.course_name
-- FROM registration 
-- JOIN students ON registration.student_id=students.student_id
-- JOIN courses ON registration.course_id=courses.course_id;

SELECT c.course_name,COUNT(r.student_id) as no_of_students_registered
FROM courses as c
LEFT JOIN registration as r
ON c.course_id=r.course_id
GROUP BY c.course_name;