DBMS Indus University Test Scenario1

create table Students(
student_id int primary key,
first_name varchar(50),
last_name varchar(50),
email varchar(100) UNIQUE,
age int
);

create table Courses (
course_id int primary key,
course_name varchar(100) UNIQUE,
duration_in_weeks int,
fee decimal(10,2)
);

CREATE TABLE Enrollments(
enrollment_id int primary key,
student_id int,
course_id int,
enrollment_date DATe,
foreign key (student_id) references Students(student_id),
foreign key (course_id) references courses(course_id)
);

insert into Students(student_id,first_name,last_name,email,age)
values
(1,'hello','world','[email protected]',19),
(2,'ok','world','[email protected]',19),
(3,'new','nice','[email protected]',18),
(4,'good','morning','[email protected]',17),
(5,'wow','world','[email protected]',18);

insert into Courses(course_id,course_name,duration_in_weeks,fee)
values
(11,'Maths',3,12000),
(22,'Sci',4,13000),
(33,'Physics',2,14000),
(44,'Chemistry',3,10000);

insert into Enrollments(enrollment_id,student_id,course_id,enrollment_date)
values
(111,1,11,'2024-01-01'),
(222,2,11,'2024-02-01'),
(333,3,22,'2024-03-01'),
(444,4,33,'2024-04-01'),
(555,5,44,'2024-05-01');

selectfrom Students;
selectfrom courses;
select*from Enrollments;

alter table Students add column gender char(10);
select*from Students;

SELECT Students.first_name, Students.last_name, Courses.course_name
FROM Enrollments
JOIN Students ON Students.student_id = Enrollments.student_id
JOIN Courses ON Courses.course_id = Enrollments.course_id;