project-database.sql

create database project_database

--DDL:
create table Trainer (
    trainer_id int primary key,
    trainer_name varchar(100),
    email varchar(100),
    phone varchar(20),
    trainer_specialty varchar(100)
);

	create table Course (
		course_id int primary key,
		title varchar(100),
		category varchar(50),
		level varchar(50),
		duration_hours int
	);

create table Trainee (
    trainee_id int primary key,
    trainer_id int,
    gender varchar(10),
    email varchar(100),
    backgroud varchar (100),
    trainee_name varchar(100),
    foreign key (trainer_id) references Trainer(trainer_id)
);

create table Enrollment (
    enrollment_id int primary key,
    trainee_id int,
    course_id int,
    enrollment_date date,
    foreign key (trainee_id) references Trainee(trainee_id),
    foreign key (course_id) references Course(course_id)
);

create table Schedule (
    schedule_id int primary key,
    trainer_id int,
    course_id int,
    time_slot varchar(50),
    start_date date,
    end_date date,
    foreign key (trainer_id) references Trainer(trainer_id),
    foreign key (course_id) references Course(course_id)
);


-- DML:
insert into Trainer (trainer_id, trainer_name, email, phone, trainer_specialty) 
values
(1, 'Khalid Al-Maawali', 'khalid@example.com', '9689123456', 'Databases'),
(2, 'Noura Al-Kindi', 'noura@example.com', '96892345678', 'Web Development'),
(3, 'Salim Al-Harthy', 'salim@example.com', '96893456789', 'Data Science');


insert into Course (course_id, title, category, level, duration_hours) 
values
(1, 'Database Fundamentals', 'Databases', 'Beginner', 20),
(2, 'Web Development Basics', 'Web', 'Beginner', 30),
(3, 'Data Science Introduction', 'Data Science', 'Intermediate', 25),
(4, 'Advanced SQL Queries', 'Databases', 'Advanced', 15);


insert into Trainee (trainee_id, trainer_id, gender, email, backgroud, trainee_name) 
values
(1, 1, 'Female', 'aisha@example.com', 'Engineering', 'Aisha Al-Harthy'),
(2, 2, 'Male', 'sultan@example.com', 'Business', 'Sultan Al-Farsi'),
(3, 2, 'Female', 'mariam@example.com', 'Marketing', 'Mariam Al-Saadi'),
(4, 1, 'Male', 'omar@example.com', 'Computer Science', 'Omar Al-Balushi'),
(5, 3, 'Female', 'fatma@example.com', 'Data Science', 'Fatma Al-Hinai');


insert into Enrollment (enrollment_id, trainee_id, course_id, enrollment_date) 
values
(1, 1, 1, '2025-06-01'),
(2, 2, 1, '2025-06-02'),
(3, 3, 2, '2025-06-03'),
(4, 4, 3, '2025-06-04'),
(5, 5, 3, '2025-06-05'),
(6, 1, 4, '2025-06-06');


insert into Schedule (schedule_id, trainer_id, course_id, time_slot, start_date, end_date) 
values
(1, 1, 1, 'Morning', '2025-07-01', '2025-07-10'),
(2, 2, 2, 'Evening', '2025-07-05', '2025-07-20'),
(3, 3, 3, 'Weekend', '2025-07-10', '2025-07-25'),
(4, 1, 4, 'Morning', '2025-07-15', '2025-07-22');



 -- Trainee Perspective: 
-- 1. Show all available cources (title, level and category):
select title, level, category
from Course

-- 2. View beginner-level DS cources:
select title
from Course
where category = 'Data Science' and level = 'Beginner';

-- 3. Show cources this trainee is enrolled in:
select C.title
from Course C inner join Enrollment E on C.course_id = E.course_id
where E.trainee_id = 1;

-- 4. View the schedule (start_date, time_slot) for the trainee's enrolled courses:
select S.start_date, S.time_slot
from Schedule S inner join Enrollment E on S.course_id = E.course_id
where E.trainee_id = 1;

-- 5. Write a query that returns the number of courses the trainee is currently enrolled in:
select COUNT(E.course_id) [number of courses the trainee is currently enrolled in]
from Enrollment E
where E.trainee_id = 1;

-- 6. Join the relevant tables to show which trainer is teaching each course the trainee attends, along with the course title and session time:
select C.title [Course Title], S.time_slot, T.trainer_name
from Enrollment E inner join Course C on E.course_id = C.course_id
inner join Schedule S on S.course_id = C.course_id
inner join Trainer T on S.trainer_id = T.trainer_id
where E.trainee_id = 1



-- Trainer Perspective
-- 1. Show all course titles where the trainer is responsible for teaching, using the trainer’s ID:
select C.title, T.trainer_name
from Schedule S inner join Course C on S.course_id = C.course_id
inner join Trainer T on S.trainer_id = T.trainer_id
where S.trainer_id = 1;

-- 2. Display future sessions this trainer is conducting, including start and end dates, and the time slot (Morning, Evening, etc.):
select T.trainer_name, S.start_date, S.end_date, S.time_slot
from Schedule S inner join Trainer T on S.trainer_id = T.trainer_id
where S.start_date > GETDATE() -- This used for current date and time

-- 3.  Count and display how many trainees are registered in each of the trainer’s courses:
select C.title [Course Title], T.trainer_name [Trainer Name], COUNT(E.trainee_id) [Total Trainee]
from Schedule S inner join Course C on S.course_id = C.course_id
inner join Trainer T on S.trainer_id = T.trainer_id
inner join Enrollment E on C.course_id = E.course_id
group by T.trainer_name, C.title

-- 4. Join Enrollment and Trainee tables to list trainee details for each course taught by the trainer (Sinle).
select C.title [Course Title], TR.trainer_name[Trainer Name], T.trainee_name [Trainee Name], T.email [Trainee Email], T.backgroud [Trainee Backgroud], T.gender [Trainee Gender]
from Schedule S inner join Course C on S.course_id = C.course_id
inner join Trainer TR on S.trainer_id = TR.trainer_id
inner join Enrollment E on C.course_id = E.course_id
inner join Trainee T on E.trainee_id = T.trainee_id
where S.trainer_id = 1 -- Because in the question they only mention one Trainer not all and each course.

-- 5.  Return the trainer’s phone and email along with a list of their assigned courses:
select T.phone [Trainer Phone Number], T.email [Trainer Email], C.title
from Schedule S inner join Trainer T on S.trainer_id = T.trainer_id
inner join Course C on S.course_id = C.course_id

-- 6. Write a query to find how many different courses the trainer is teaching:
select COUNT(DISTINCT C.title) [How many Cources], T.trainer_name [Trainer Name]
from Schedule S inner join Course C on S.course_id = C.course_id
inner join Trainer T on S.trainer_id = T.trainer_id
group by T.trainer_name



-- Admin Perspective:
-- 1. Write an SQL INSERT command to add a new course to the Course table with details like title, category, duration, and level.
insert into Course (course_id, title, category, level, duration_hours) 
values
(5, 'Machine Learning Basics', 'Data Science', 'Intermediate', 22)

-- 2. Write an INSERT statement to schedule a course by assigning a trainer, course, start/end dates, and time slot in the Schedule table:
insert into Schedule (schedule_id, trainer_id, course_id, time_slot, start_date, end_date) 
values
(5, 2, 5, 'Morning', '2026-07-25', '2027-07-25')

-- 3. Create a joined query across Enrollment, Course, and Schedule to display which trainees are enrolled in which courses, along with scheduling info:
select DISTINCT  title [Course Title], T.trainee_name [Trainee Name], S.start_date [Start Date], S.end_date [End Date], S.time_slot [Time Slot], S.schedule_id [Schedual ID]
from Schedule S inner join Enrollment E on S.course_id = E.course_id
inner join Course C on S.course_id = C.course_id
inner join Trainee T on E.trainee_id = T.trainee_id


-- 4. Write a query that counts the number of courses assigned to each trainer:
select T.trainer_name, COUNT(S.course_id) [Number Of Cources Assigned]
from Schedule S inner join Trainer T on S.trainer_id = T.trainer_id
group by T.trainer_name

-- 5.  Retrieve trainee names and emails for those enrolled in the course titled "Data Basics":
select T.trainee_name [Trainee Name], T.email [Trainee Email]
from Enrollment E inner join Trainee T on E.trainee_id = T.trainee_id
inner join Course C on E.course_id = C.course_id
where C.title = 'Data Basics'

-- 6. Write a query that ranks courses by enrollment count and displays the one with the highest number:
select top 1 C.title [Course Title], COUNT(DISTINCT T.trainee_id) [Numbers Of Trainees]
from Enrollment E inner join Course C on E.course_id = C.course_id
inner join Trainee T on E.trainee_id = T.trainee_id
group by C.title
ORDER BY COUNT(DISTINCT E.trainee_id) DESC;

-- 7. Select all rows from the Schedule table and sort them in ascending order based on start date.
select *
from Schedule S
order by S.start_date ASC