Codeplus SS

Download as pdf or txt
Download as pdf or txt
You are on page 1of 16

CASTILLO, JANICO

DELA TORRE, ROMMEL


VILLARISCO, ANGELICA

Code:

Create database:
CREATE DATABASE RCIStudentInfoDBMS2;
USE RCIStudentInfoDBMS2;

Create table:

Year Level’s Table:


create table Yearlvl (
yearLvl INT auto_increment primary key,
yearName varchar(50) not null
);

Student’s Table:
CREATE TABLE Students (
studentID INT auto_increment primary key,
fname varchar(255) not null,
lname varchar(255) not null,
gender enum('Male', 'Female', 'Other') not null,
bday DATE not null,
address varchar(255) not null,
phoneNum varchar(15),
email varchar(255) unique,
yearLvl INT,
foreign key (yearLvl) references Yearlvl(yearLvl)
);

Subject’s Table/ Courses Table:


CREATE TABLE Subjects (
subjectID INT auto_increment primary key,
subjectName varchar(100),
subjectDescription text
);

Teacher’s Table
create table Teachers (
TeacherID INT auto_increment primary key,
fname varchar(255) not null,
lname varchar(255) not null,
email varchar(255) unique
);

Enrollment’s Table:
CREATE TABLE Enrollments (
enrollmentID INT auto_increment primary key,
studentID INT,
subjectID INT,
enrollmentDate DATE,
foreign key(studentID) references Students(studentID),
foreign key(subjectID) references Subjects(subjectID)
);
Grade’s Table:
create table Grade (
GradeID INT auto_increment primary key,
grade decimal(10,3),
semester varchar(20),
studentID INT,
subjectID INT,
foreign key(studentID) references Students(studentID),
foreign key(subjectID) references Subjects(subjectID)
);

Schedule’s Table:
Create table Schedule (
ScheduleID INT AUTO_INCREMENT PRIMARY KEY,
TeacherID INT,
subjectID INT,
DayOfWeek VARCHAR(20),
StartTime TIME,
EndTime TIME,
FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID),
FOREIGN KEY (subjectID) REFERENCES Subjects(subjectID)
);

Insert Data:
Yearlvl’s Table:
INSERT INTO Yearlvl(yearName)
VALUES
('Freshman'),
('Sophomore'),
('Junior'),
('Senior');

Students’ Table:
INSERT INTO Students(fname, lname, gender, bday, address, phoneNum, email, yearLvl)
VALUES
('Rommel', 'Dela Torre', 'Male', '2000-01-10', 'Plaridel, Bulacan', '09082996818',
'[email protected]','2'),
('Janico', 'Castillo', 'Male', '2002-11-03', 'Pulilan, Bulacan', '09559856351',
'janicakes24:[email protected]','2'),
('Angelica', 'Villarisco', 'Other','2002-05-03', 'Plaridel, Bulacan', '09996589471',
'[email protected]','2');

Subjects’ Table:
INSERT INTO Subjects (subjectName, subjectDescription)
VALUES
('Information Management', 'The course covers Information Management implementation of
database design, implementation, data modeling, and SQL using relational Database Systems.'),
('Human-Computer Interaction', 'The study and design of how people interact with computers
and other digital devices, aiming to improve usability and user experience.'),
('Enterprise Architecture', 'This includes understanding how to manage data, applications, and
technology infrastructure to support organizational objectives.');

Teachers’ Table
INSERT INTO Teachers (fname, lname, email)
VALUES
('Francis Ian', 'Africa', '[email protected]'),
('Mia Grace', 'Daylo', '[email protected]'),
('Juan', 'Luna', '[email protected]');

Enrollments’ Table
INSERT INTO Enrollments (studentID, subjectID, enrollmentDate)
VALUES
(1, 1, '2024-04-25'),
(1, 2, '2024-04-25'),
(2, 1, '2024-04-25'),
(2, 2, '2024-04-25'),
(2, 3, '2024-04-25'),
(3, 1, '2024-04-25'),
(3, 2, '2024-04-25'),
(3, 3, '2024-04-25');

Grades’ Table:
INSERT INTO Grade (grade, semester, studentID, subjectID)
VALUES
('2.00', '2nd sem', 1,1),
('1.50', '2nd sem', 1,2),
('2.00', '2nd sem', 2,1),
('1.50', '2nd sem', 2,2),
('1.75', '2nd sem', 2,3),
('1.75', '2nd sem', 3,1),
('1.50', '2nd sem', 3,2),
('2.00', '2nd sem', 3,3);

Schedules’ Table:
INSERT INTO Schedule (TeacherID, subjectID, DayOfWeek, StartTime, EndTime)
VALUES
(1, 1, 'Monday', '07:00:00', '12:30:00'),
(1, 3, 'Wednesday', '10:30:00', '13:30:00'),
(2, 2, 'Saturday', '07:00:00', '10:00:00');

a. Student information details


SELECT * FROM students
WHERE studentID = 1;

b. Students subject enrolled


SELECT s.studentID, s.fname, s.lname, c.subjectName
FROM Students s
JOIN Enrollments e ON s.studentID = e.studentID
JOIN subjects c ON e.subjectID = c.subjectID
WHERE s.studentID = 1;

c. Students year level


SELECT yl.yearLvl, yl.yearName, s.fName, s.lName
FROM Students s
JOIN yearlvl yl ON s.yearLvl = yl.yearLvl
WHERE s.studentID = 1;
d. Students summary of grades
SELECT c.subjectName, g.grade
FROM grade g
JOIN subjects c ON g.subjectID = c.subjectID
WHERE g.StudentID = 1;

e. Teacher Schedule's
SELECT t.TeacherID, t.fname, t.lname, c.subjectName, sc.DayOfWeek, sc.StartTime,
sc.EndTime
FROM teachers t
JOIN schedule sc ON t.TeacherID = sc.TeacherID
JOIN subjects c ON sc.subjectID = c.subjectID
WHERE t.TeacherID = 1;

You might also like