msc/big-data/practical-0

-- Create the university database
CREATE DATABASE IF NOT EXISTS university;

-- Use the university database
USE university;

-- Lecturer table
CREATE TABLE Lecturer (
    employee_id INT PRIMARY KEY,
    forename VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),
    surname VARCHAR(50) NOT NULL,
    dob DATE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    `rank` TINYINT NOT NULL CHECK (`rank` BETWEEN 1 AND 40)
);

-- Course table
CREATE TABLE Course (
    course_code VARCHAR(10) PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    accreditation_level TINYINT NOT NULL CHECK (accreditation_level BETWEEN 4 AND 8),
    coordinator_id INT,
    FOREIGN KEY (coordinator_id) REFERENCES Lecturer(employee_id)
);

-- Module table
CREATE TABLE Module (
    module_code VARCHAR(10) PRIMARY KEY,
    module_name VARCHAR(100) NOT NULL,
    lecturer_id INT NOT NULL,
    course_code VARCHAR(10) NOT NULL,
    FOREIGN KEY (lecturer_id) REFERENCES Lecturer(employee_id),
    FOREIGN KEY (course_code) REFERENCES Course(course_code)
);

-- Student table
CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    forename VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),
    surname VARCHAR(50) NOT NULL,
    dob DATE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    enrollment_level TINYINT NOT NULL CHECK (enrollment_level BETWEEN 1 AND 20)
);


INSERT INTO Lecturer (employee_id, forename, middle_name, surname, dob, email, rank)
VALUES
(1, 'Alice', 'M.', 'Smith', '1980-02-14', 'alice.smith@uni.edu', 10),
(2, 'Bob', 'J.', 'Johnson', '1975-04-22', 'bob.johnson@uni.edu', 15),
(3, 'Carol', 'A.', 'Brown', '1983-06-30', 'carol.brown@uni.edu', 20),
(4, 'David', 'B.', 'Williams', '1982-12-09', 'david.williams@uni.edu', 12),
(5, 'Eva', 'L.', 'Jones', '1979-10-18', 'eva.jones@uni.edu', 8),
(6, 'Frank', 'T.', 'Garcia', '1985-11-01', 'frank.garcia@uni.edu', 11),
(7, 'Grace', 'C.', 'Miller', '1978-07-23', 'grace.miller@uni.edu', 22),
(8, 'Hank', 'D.', 'Davis', '1981-09-15', 'hank.davis@uni.edu', 18),
(9, 'Ivy', 'E.', 'Rodriguez', '1984-05-05', 'ivy.rodriguez@uni.edu', 25),
(10, 'Jake', 'F.', 'Martinez', '1980-08-28', 'jake.martinez@uni.edu', 30),
(11, 'Kim', 'G.', 'Hernandez', '1986-03-16', 'kim.hernandez@uni.edu', 14),
(12, 'Leo', 'H.', 'Lopez', '1987-01-12', 'leo.lopez@uni.edu', 9),
(13, 'Mia', 'I.', 'Gonzalez', '1977-06-17', 'mia.gonzalez@uni.edu', 16),
(14, 'Nate', 'J.', 'Wilson', '1983-04-01', 'nate.wilson@uni.edu', 17),
(15, 'Olivia', 'K.', 'Anderson', '1989-07-11', 'olivia.anderson@uni.edu', 19),
(16, 'Paul', 'L.', 'Thomas', '1976-08-05', 'paul.thomas@uni.edu', 13),
(17, 'Quinn', 'M.', 'Taylor', '1982-05-25', 'quinn.taylor@uni.edu', 6),
(18, 'Rachel', 'N.', 'Moore', '1974-02-20', 'rachel.moore@uni.edu', 4),
(19, 'Steve', 'O.', 'Jackson', '1981-11-09', 'steve.jackson@uni.edu', 31),
(20, 'Tina', 'P.', 'Martin', '1985-09-30', 'tina.martin@uni.edu', 28);



INSERT INTO Course (course_code, course_name, accreditation_level, coordinator_id)
VALUES 
('CS101', 'Computer Science', 6, 1),
('ENG201', 'English Literature', 5, 2),
('MATH301', 'Mathematics', 7, 3),
('BIO110', 'Biology', 6, 4),
('BUS150', 'Business Studies', 5, 5),
('CHEM205', 'Chemistry', 7, 6),
('PHYS210', 'Physics', 6, 7),
('HIST102', 'History', 5, 8),
('LAW300', 'Law', 8, 9),
('ART100', 'Fine Arts', 4, 10);

INSERT INTO Module (module_code, module_name, lecturer_id, course_code)
VALUES
('M101', 'Intro to Programming', 11, 'CS101'),
('M102', 'Data Structures', 12, 'CS101'),
('M103', 'Shakespeare Studies', 13, 'ENG201'),
('M104', 'Calculus I', 14, 'MATH301'),
('M105', 'Organic Chemistry', 15, 'CHEM205'),
('M106', 'Business Ethics', 16, 'BUS150'),
('M107', 'Classical Physics', 17, 'PHYS210'),
('M108', 'Modern Art', 18, 'ART100'),
('M109', 'Constitutional Law', 19, 'LAW300'),
('M110', 'World History', 20, 'HIST102');

INSERT INTO Student (student_id, forename, middle_name, surname, dob, email, enrollment_level)
VALUES
(1001, 'Adam', 'J.', 'Lee', '2001-01-01', 'adam.lee@students.uni.edu', 1),
(1002, 'Bella', 'K.', 'Wright', '2000-02-15', 'bella.wright@students.uni.edu', 2),
(1003, 'Chris', 'L.', 'Green', '1999-03-10', 'chris.green@students.uni.edu', 3),
(1004, 'Diana', 'M.', 'Hall', '2001-04-20', 'diana.hall@students.uni.edu', 4),
(1005, 'Ethan', 'N.', 'Allen', '1998-05-18', 'ethan.allen@students.uni.edu', 5),
(1006, 'Fiona', 'O.', 'Young', '2000-06-12', 'fiona.young@students.uni.edu', 6),
(1007, 'George', 'P.', 'King', '1999-07-22', 'george.king@students.uni.edu', 7),
(1008, 'Hannah', 'Q.', 'Scott', '2001-08-30', 'hannah.scott@students.uni.edu', 8),
(1009, 'Ian', 'R.', 'Evans', '1998-09-09', 'ian.evans@students.uni.edu', 9),
(1010, 'Julia', 'S.', 'Campbell', '1997-10-28', 'julia.campbell@students.uni.edu', 10),
(1011, 'Kevin', 'T.', 'Parker', '1999-11-13', 'kevin.parker@students.uni.edu', 11),
(1012, 'Lily', 'U.', 'Edwards', '2000-12-01', 'lily.edwards@students.uni.edu', 12),
(1013, 'Mike', 'V.', 'Collins', '1998-01-17', 'mike.collins@students.uni.edu', 13),
(1014, 'Nina', 'W.', 'Stewart', '1997-02-25', 'nina.stewart@students.uni.edu', 14),
(1015, 'Oscar', 'X.', 'Sanchez', '1999-03-19', 'oscar.sanchez@students.uni.edu', 15),
(1016, 'Paula', 'Y.', 'Morris', '2000-04-11', 'paula.morris@students.uni.edu', 16),
(1017, 'Quincy', 'Z.', 'Rogers', '1998-05-22', 'quincy.rogers@students.uni.edu', 17),
(1018, 'Rita', 'A.', 'Reed', '1997-06-07', 'rita.reed@students.uni.edu', 18),
(1019, 'Sam', 'B.', 'Cook', '1999-07-01', 'sam.cook@students.uni.edu', 19),
(1020, 'Tara', 'C.', 'Morgan', '1998-08-10', 'tara.morgan@students.uni.edu', 20),
(1021, 'Uma', 'D.', 'Bell', '2000-09-14', 'uma.bell@students.uni.edu', 2),
(1022, 'Victor', 'E.', 'Murphy', '1997-10-08', 'victor.murphy@students.uni.edu', 5),
(1023, 'Wendy', 'F.', 'Bailey', '1996-11-05', 'wendy.bailey@students.uni.edu', 8),
(1024, 'Xander', 'G.', 'Rivera', '1995-12-29', 'xander.rivera@students.uni.edu', 3),
(1025, 'Yasmin', 'H.', 'Cooper', '1999-01-25', 'yasmin.cooper@students.uni.edu', 10),
(1026, 'Zack', 'I.', 'Richardson', '1998-02-20', 'zack.richardson@students.uni.edu', 7),
(1027, 'Amelia', 'J.', 'Cox', '1997-03-05', 'amelia.cox@students.uni.edu', 1),
(1028, 'Ben', 'K.', 'Howard', '1996-04-15', 'ben.howard@students.uni.edu', 6),
(1029, 'Clara', 'L.', 'Ward', '1995-05-21', 'clara.ward@students.uni.edu', 4),
(1030, 'Daniel', 'M.', 'Torres', '1999-06-17', 'daniel.torres@students.uni.edu', 9);

SELECT *
FROM Module
ORDER BY module_name ASC;

SELECT *
FROM Student
WHERE enrollment_level = 1;


SELECT 
  m.module_name,
  m.module_code,
  CONCAT(l.forename, ' ', l.middle_name, ' ', l.surname) AS lecturer_name
FROM Module m
JOIN Lecturer l ON m.lecturer_id = l.employee_id;


SELECT 
  course_name,
  accreditation_level
FROM Course;


SELECT *
FROM Lecturer
WHERE rank > 10;