Explore Compatibility with OceanBase Cloud

The client-demo.sql file uses a course management system as an example, covering common database operations, including creating tables, indexes, views, data partitioning, etc.

Complete SQL Statements

1. Create the Students table

   CREATE TABLE IF NOT EXISTS students (
       student_id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(255) NOT NULL,
       registration_date DATE NOT NULL,
       INDEX idx_registration_date (registration_date)
   ) ENGINE=InnoDB
   $$
   

2. Create the Courses table

   CREATE TABLE IF NOT EXISTS courses (
       course_id INT AUTO_INCREMENT PRIMARY KEY,
       title VARCHAR(255) NOT NULL,
       credits INT NOT NULL
   ) ENGINE=InnoDB
   $$
   

3. Create the Teachers table

   CREATE TABLE IF NOT EXISTS teachers (
       teacher_id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(255) NOT NULL
   ) ENGINE=InnoDB
   $$
   

4. Create the Course Schedules table with partitioning

   CREATE TABLE IF NOT EXISTS course_schedules (
       schedule_id INT AUTO_INCREMENT PRIMARY KEY,
       course_id INT NOT NULL,
       teacher_id INT,
       schedule_time DATETIME NOT NULL,
       INDEX idx_schedule_time (schedule_time)
   ) ENGINE=InnoDB
   PARTITION BY RANGE(YEAR(schedule_time)) (
       PARTITION p2020 VALUES LESS THAN (2021),
       PARTITION p2021 VALUES LESS THAN (2022),
       PARTITION p2022 VALUES LESS THAN (2023),
       PARTITION p2023 VALUES LESS THAN (2024),
       PARTITION p_future VALUES LESS THAN MAXVALUE
   )
   $$
   

5. Create the view: Course and Teacher

   CREATE VIEW `vw_course_teacher` AS
   SELECT 
       c.course_id,
       c.title AS course_title,
       c.credits,
       t.name AS teacher_name
   FROM courses AS c
   LEFT JOIN course_schedules AS cs ON c.course_id = cs.course_id
   LEFT JOIN teachers AS t ON cs.teacher_id = t.teacher_id;
   $$
   

6. Create the view: Upcoming Courses

   CREATE VIEW `vw_upcoming_courses` AS
   SELECT 
       c.title AS course_title,
       t.name AS teacher_name,
       cs.schedule_time
   FROM course_schedules AS cs
   JOIN courses AS c ON cs.course_id = c.course_id
   JOIN teachers AS t ON cs.teacher_id = t.teacher_id
   WHERE cs.schedule_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 WEEK);
   $$
   

7. Create the view: Student Registration

   CREATE VIEW `vw_student_registration` AS
   SELECT 
       student_id,
       name AS student_name,
       registration_date
   FROM students;
   $$