Creating Database Tables and Relationshpis

-- Create class table and insert data
CREATE TABLE classes (
    class_id INT AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(32) NOT NULL DEFAULT ""
) CHARSET utf8;

INSERT INTO classes (class_name) VALUES 
("Grade 3 Class 2"), ("Grade 1 Class 3"), ("Grade 3 Class 1");

-- Create student table with foreign key to classes
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(32) NOT NULL DEFAULT "",
    gender VARCHAR(32) NOT NULL DEFAULT "",
    class_id INT,
    CONSTRAINT fk_class_id FOREIGN KEY (class_id)
    REFERENCES classes (class_id)
) CHARSET utf8;

INSERT INTO students (student_name, gender, class_id) VALUES
("Gang Dan", "Female", 1), ("Tie Chui", "Male", 1), ("Shan Pao", "Male", 2);

-- Create teacher table
CREATE TABLE teachers (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_name VARCHAR(32) NOT NULL DEFAULT ""
) CHARSET utf8;

INSERT INTO teachers (teacher_name) VALUES
("Bo Duo"), ("Cang Jing"), ("Fan Dao");

-- Create course table with foreign key to teachers
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(32) NOT NULL DEFAULT "",
    teacher_id INT,
    CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id)
    REFERENCES teachers (teacher_id)
) CHARSET utf8;

INSERT INTO courses (course_name, teacher_id) VALUES
("Biology", 1), ("Physical Education", 1), ("Physics", 2);

-- Create score table with foreign keys to students and courses
CREATE TABLE scores (
    score_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL DEFAULT 0,
    course_id INT NOT NULL DEFAULT 0,
    score_value INT NOT NULL DEFAULT 0,
    
    CONSTRAINT fk_score_student FOREIGN KEY (student_id)
    REFERENCES students (student_id),
    
    CONSTRAINT fk_score_course FOREIGN KEY (course_id)
    REFERENCES courses (course_id)
) CHARSET utf8;

INSERT INTO scores (student_id, course_id, score_value) VALUES
(1, 1, 60), (1, 2, 59), (2, 2, 100);

SQL Query Exercises

1. Find Students with Scores Above 60

SELECT DISTINCT students.student_name, students.student_id
FROM students LEFT JOIN scores
ON students.student_id = scores.student_id 
WHERE scores.score_value > 60;

2. Count Courses Taught by Each Teacher

SELECT teachers.*, COUNT(courses.teacher_id) AS course_count 
FROM teachers LEFT JOIN courses 
ON teachers.teacher_id = courses.teacher_id
GROUP BY teachers.teacher_id;

3. Student Information with Class Details

SELECT students.*, classes.class_name 
FROM students LEFT JOIN classes
ON students.class_id = classes.class_id;

4. Count Students by Gender

SELECT gender, COUNT(gender) AS count 
FROM students GROUP BY gender;

5. Find Students Studying Biology

SELECT students.student_id, scores.score_value, students.student_name
FROM students LEFT JOIN scores
ON students.student_id = scores.student_id
LEFT JOIN courses
ON scores.course_id = courses.course_id
WHERE courses.course_name = "Biology";

6. Count Teachers with Surname "Bo"

SELECT COUNT(teacher_name) AS teacher_count 
FROM teachers WHERE teacher_name LIKE "Bo%";

7. Students with Scores Below 60

SELECT students.student_id, student_name 
FROM students LEFT JOIN scores
ON students.student_id = scores.student_id
WHERE scores.score_value < 60;

8. Delete Scores for Teacher "Bo Duo"

DELETE FROM scores
WHERE course_id IN (
    SELECT courses.course_id FROM courses
    LEFT JOIN teachers 
    ON courses.teacher_id = teachers.teacher_id
    WHERE teacher_name = "Bo Duo"
);

9. Find Highest and Lowest Scores per Course

SELECT course_id, MAX(score_value), MIN(score_value) 
FROM scores GROUP BY course_id;

10. Count Students per Course

SELECT course_id, COUNT(student_id) 
FROM scores GROUP BY course_id;

11. Find Students with Surname "Gang"

SELECT * FROM students 
WHERE student_name LIKE "Gang%";

12. Average Scores per Course with Ordering

SELECT course_id, AVG(score_value) AS average_score 
FROM scores
GROUP BY course_id
ORDER BY average_score, course_id DESC;

13. Students with Average Score Above 85

SELECT students.student_id, student_name, AVG(score_value) AS average_score 
FROM students LEFT JOIN scores
ON students.student_id = scores.student_id
GROUP BY students.student_id
HAVING average_score > 85;

14. Students with Course 2 Score ≥ 59

SELECT students.student_id, student_name 
FROM students LEFT JOIN scores
ON students.student_id = scores.student_id
WHERE scores.course_id = 2 AND score_value >= 59;

15. Course Enrollment Counts

SELECT course_id, COUNT(student_id) AS student_count 
FROM scores GROUP BY course_id;

16. Course 2 Scores Below 60 in Descending Order

SELECT student_id FROM scores 
WHERE course_id = 2 AND score_value < 60
ORDER BY score_value DESC;

17. Delete Student 2's Course 2 Score

DELETE FROM scores 
WHERE student_id = 2 AND course_id = 2;