-- Create database
CREATE DATABASE SchoolManagement;
USE SchoolManagement;
-- Create tables
CREATE TABLE dmlophoc (
malop INT PRIMARY KEY,
tenlop VARCHAR(100),
phonghoc VARCHAR(100)
);
CREATE TABLE dmgiaovien (
magv INT PRIMARY KEY,
tengv VARCHAR(100),
diachi VARCHAR(200)
);
CREATE TABLE dmmonhoc (
mamon INT PRIMARY KEY,
tenmon VARCHAR(100),
sotiet INT
);
CREATE TABLE tiendo (
malop INT,
mamon INT,
magv INT,
PRIMARY KEY (malop, mamon, magv),
FOREIGN KEY (malop) REFERENCES dmlophoc(malop),
FOREIGN KEY (mamon) REFERENCES dmmonhoc(mamon),
FOREIGN KEY (magv) REFERENCES dmgiaovien(magv)
);
cau2
-- Insert data into dmlophoc
INSERT INTO dmlophoc (malop, tenlop, phonghoc) VALUES
(1, 'Lop 1', 'Phong 101'),
(2, 'Lop 2', 'Phong 102'),
(3, 'Lop 3', 'Phong 103'),
(4, 'Lop 4', 'Phong 104');
-- Insert data into dmgiaovien
INSERT INTO dmgiaovien (magv, tengv, diachi) VALUES
(1, 'Giao Vien A', 'Ha Noi'),
(2, 'Giao Vien B', 'Hai Phong'),
(3, 'Giao Vien C', 'Da Nang'),
(4, 'Giao Vien D', 'HCM'),
(5, 'Giao Vien E', 'Can Tho');
-- Insert data into dmmonhoc
INSERT INTO dmmonhoc (mamon, tenmon, sotiet) VALUES
(1, 'Mon 1', 30),
(2, 'Mon 2', 40),
(3, 'Mon 3', 50),
(4, 'Mon 4', 60),
(5, 'Mon 5', 70);
-- Insert data into tiendo
INSERT INTO tiendo (malop, mamon, magv) VALUES
(1, 1, 1),
(1, 2, 2),
(2, 3, 3),
(2, 4, 4),
(3, 5, 5),
(3, 1, 1),
(4, 2, 2);
-- Query to find the teacher teaching the most classes
SELECT
magv,
tengv,
COUNT(DISTINCT malop) AS tongsolop
FROM
tiendo
JOIN
dmgiaovien ON tiendo.magv = dmgiaovien.magv
GROUP BY
magv, tengv
ORDER BY
tongsolop DESC
LIMIT 1;
cau3
-- Query to find subjects not assigned to any class
SELECT
mamon,
tenmon,
sotiet
FROM
dmmonhoc
WHERE
mamon NOT IN (SELECT DISTINCT mamon FROM tiendo);
cau4
-- Create trigger to save records before deletion in the tiendo table
DELIMITER //
CREATE TRIGGER BeforeDeleteTiendo
BEFORE DELETE ON tiendo
FOR EACH ROW
BEGIN
INSERT INTO tiendo_backup (malop, mamon, magv) VALUES (OLD.malop, OLD.mamon,
OLD.magv);
END;
//
DELIMITER ;
-- Create backup table for tiendo
CREATE TABLE tiendo_backup (
malop INT,
mamon INT,
magv INT
);
cau5
-- Create function to calculate the total number of subjects with periods greater
than a given number
DELIMITER //
CREATE FUNCTION TotalSubjects(sotiet_threshold INT) RETURNS INT
BEGIN
DECLARE total INT;
SELECT COUNT(*) INTO total
FROM dmmonhoc
WHERE sotiet > sotiet_threshold;
RETURN total;
END;
//
DELIMITER ;
-- Test the function
SELECT TotalSubjects(40) AS TotalSubjects;
