SQL Exercise 5

Stored Objects

-- Exercise: MySQL Stored Objects
-- Database schema
 
CREATE DATABASE IF NOT EXISTS school;
USE school;
 
CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  enrolled_at DATETIME
);
 
CREATE TABLE courses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  credits INT
);
 
CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  enrolled_on DATE,
  PRIMARY KEY (student_id, course_id)
);
 
INSERT INTO students (name) VALUES ('Alice'), ('Bob');
INSERT INTO courses (title, credits) VALUES ('Databases', 5), ('Networking', 4);
INSERT INTO enrollments VALUES (1,1,'2025-01-01'), (2,2,'2025-01-02');
 
-- TASKS
-- VIEW (Simple):
-- Create a view that shows student names with their course titles.
 
-- VIEW (Advanced):
-- Create a view that also shows total credits per student.
 
-- PROCEDURE (Simple):
-- Create a procedure that lists all courses for a given student id.
 
-- PROCEDURE (Advanced):
-- Create a procedure that enrolls a student into a course and prevents duplicates.
 
-- FUNCTION (Simple):
-- Create a function that returns credits * 30 (workload hours).
 
-- FUNCTION (Advanced):
-- Create a function that returns the total credits for a student.
 
-- TRIGGER (Simple):
-- Automatically set enrolled_at when inserting into students.
 
-- TRIGGER (Advanced):
-- Prevent enrollment if a student already has more than 10 credits.
 
-- EVENT:
-- Create an event that runs daily and removes enrollments older than 5 years.