Sql Projects Classroom Database Need an database to store class and student info

Question

Sql Projects

Classroom Database

Need an database to store class and student information and the books that they have written.

First Solution

You should populate the first three tables with sample data before you add data to the StudentClasses table. You will need the id’s from all other tables to be able to insert correct data into StudentClasses.

If a student was taking a class, they would be inserted into a record on the StudentClassess table where the ClassRoomId and CourseId are required.

Tables Needed

Students

Id

FirstName

LastName

Mi

DOB

Phone

GradudationYear

Class Rooms

Id

Rm Number

BuildingName

Capcity

Courses

Id

Name

StudentClasses

StudentId

CourseId

ClassRoomId

Stored Procs Needed

Select All Student Information for students that have a Class

Select all the Class Room data for Classrooms that are associated with Course and Students

Select all the Course with students in them

Select all the students not in a class

Select all the students that are taking 1 particular class. Use the Class Id column to filter.

Explanation / Answer

Table structures
create table students (id int auto_increment primary key,firstName varchar(30),lastName varchar(30),mi varchar(30),dob date,phone BIGINT,graduationyear int(4));

create table courses (id int auto_increment primary key, name varchar(30));        

create table classrooms (id int auto_increment primary key,rmnumber int(3), buildingname varchar(30),capcity int(10));

create table studentclasses (studentid int,courseid int,classroomid int, foreign key(studentid) references students(id), foreign key(courseid) references courses(id), foreign key(classroomid) references classrooms(id));

Sample Data
insert into students(firstname,lastname,mi,dob,phone,graduationyear) values ('aaa','aaa','aami','1988-07-14',9999999999,2010);
insert into students(firstname,lastname,mi,dob,phone,graduationyear) values ('bbb','bbb','bbmi','1989-07-14',8888888888,2011);
insert into students(firstname,lastname,mi,dob,phone,graduationyear) values ('ccc','ccc','ccmi','1990-07-14',7777777777,2012);
insert into students(firstname,lastname,mi,dob,phone,graduationyear) values ('ddd','ddd','ddmi','1991-07-14',6666666666,2013);
insert into students(firstname,lastname,mi,dob,phone,graduationyear) values ('eee','eee','eemi','1992-07-14',5555555555,2014);

insert into courses(name) values ('C1');
insert into courses(name) values ('C2');
insert into courses(name) values ('C3');
insert into courses(name) values ('C4');

insert into classrooms(rmnumber,buildingname,capacity) values ('1','B1',10);
insert into classrooms(rmnumber,buildingname,capacity) values ('2','B2',20);
insert into classrooms(rmnumber,buildingname,capacity) values ('3','B3',30);
insert into classrooms(rmnumber,buildingname,capacity) values ('4','B4',40);
insert into classrooms(rmnumber,buildingname,capacity) values ('5','B5',50);

insert into studentclasses(studentid,courseid,classroomid) values (1,1,1);
insert into studentclasses(studentid,courseid,classroomid) values (2,2,2);
insert into studentclasses(studentid,courseid,classroomid) values (3,3,3);
insert into studentclasses(studentid,courseid,classroomid) values (4,1,NULL);

Stored Procedures

call the stored procedures directly. for example

to call the below procedure use "CALL classrooms_with_courseandstudent()" to see results

1. Select All Student Information for students that have a Class
DELIMITER //
CREATE PROCEDURE classrooms_with_courseandstudent
()
BEGIN
SELECT * from students s join studentclasses sc on sc.studentid=s.id
WHERE classroomid is not null;
END //
DELIMITER ;

2. Select all the Class Room data for Classrooms that are associated with Course and Students
DELIMITER //
CREATE PROCEDURE classrooms_with_courseandstudent
()
BEGIN
SELECT * from classrooms c join studentclasses sc on sc.classroomid=c.id
WHERE sc.courseid is not null and sc.studentid is not null;
END //
DELIMITER ;

3. Select all the Course with students in them
DELIMITER //
CREATE PROCEDURE all_courses_with_students
()
BEGIN
SELECT distinct c.name from courses c join studentclasses sc on sc.courseid=c.id
WHERE sc.studentid is not null;
END //
DELIMITER ;

4. Select all the students not in a class
DELIMITER //
CREATE PROCEDURE all_student_not_in_class
()
BEGIN
SELECT * from students s join studentclasses sc on sc.studentid=s.id
WHERE sc.classroomid is null;
END //
DELIMITER ;

5. Select all the students that are taking 1 particular class. Use the Class Id column to filter.
DELIMITER //
CREATE PROCEDURE particular_class
(IN cid int)
BEGIN
SELECT * FROM students s join studentclasses sc on sc.studentid=s.id join courses c on c.id=sc.courseid
WHERE c.id=1;
END //
DELIMITER ;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.