Question

Using SQL create a new database called school_app. Create a student table with fields id (auto increment), first_name, last_name. Create a course table with fields id (auto increment), course code (such as ITC or MTH), and course number (such as 100 or 295). Note that the relationship between student and course is many-to-many (n:m). Create a join table called student_course that implements the n:m relationship with fields id (auto increment), student_id, course_id, and grade (which has values 0, 1, 2, 3, or 4). Insert data into all the tables with at least 10 different students and at least 3 different courses (you must be one of the students). Make sure all the students are taking multiple courses and all courses have multiple students. Do not use the data from anyone else in this course. When finished, do a SELECT * for all three tables and copy/paste the results here:

If someone could help me with this, it would be greatly appreciated. Ill certainly give a thumbs up!

Answer 1

Here I am providing the answer for the above question:

SQL Code:

CREATE DATABASE school_app;

/* creaeting tables */
CREATE TABLE student
(
id INT IDENTITY(1,1) PRIMARY KEY,
first_name VARCHAR(20),
last_name VARCHAR(20)
);

CREATE TABLE course
(
id INT IDENTITY(1,1) PRIMARY KEY,
course_code VARCHAR(10),
course_number INT
);

CREATE TABLE student_course
(
student_id INT,
course_id INT,
grade INT,
FOREIGN KEY(student_id) REFERENCES student(id),
FOREIGN KEY(course_id) REFERENCES course(id)
);

/* Inserting the data */

INSERT INTO student(first_name,last_name) VALUES('vamsi','srivathsav');
INSERT INTO student(first_name,last_name) VALUES('swetha','koluboyina');
INSERT INTO student(first_name,last_name) VALUES('sai','manish');
INSERT INTO student(first_name,last_name) VALUES('deepu','desabattula');
INSERT INTO student(first_name,last_name) VALUES('sai','j');
INSERT INTO student(first_name,last_name) VALUES('devi','sabbella');
INSERT INTO student(first_name,last_name) VALUES('sri devi','thatha');
INSERT INTO student(first_name,last_name) VALUES('vimala','sahukari');
INSERT INTO student(first_name,last_name) VALUES('ravali','sakhinetipalli');
INSERT INTO student(first_name,last_name) VALUES('sandhya','barla');

INSERT INTO course(course_code,course_number) VALUES('ITC',100);
INSERT INTO course(course_code,course_number) VALUES('MAT',201);
INSERT INTO course(course_code,course_number) VALUES('CPP',304);

INSERT INTO student_course(student_id,course_id,grade) VALUES(1,2,5);
INSERT INTO student_course(student_id,course_id,grade) VALUES(1,1,5);
INSERT INTO student_course(student_id,course_id,grade) VALUES(1,3,4);
INSERT INTO student_course(student_id,course_id,grade) VALUES(2,3,4);
INSERT INTO student_course(student_id,course_id,grade) VALUES(2,1,4);
INSERT INTO student_course(student_id,course_id,grade) VALUES(2,2,3);
INSERT INTO student_course(student_id,course_id,grade) VALUES(3,1,4);
INSERT INTO student_course(student_id,course_id,grade) VALUES(3,2,3);
INSERT INTO student_course(student_id,course_id,grade) VALUES(4,3,2);
INSERT INTO student_course(student_id,course_id,grade) VALUES(4,2,4);
INSERT INTO student_course(student_id,course_id,grade) VALUES(4,1,5);

/* selecting data */

SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM student_course;

Screenshot:

Output:

student table:

course table:

student_course table: