Sample Script for Creating Database Tables

Last modified: June 26, 2023

Use this script to create tables necessary for completing Debugging a Stored Procedure, Debugging a Stored Function, and Debug a Trigger topics.

CREATE TABLE scheduledetail (
  ScheduleId int(11) NOT NULL,
  DateOut date NOT NULL,
  TimeSheetDate date NOT NULL,
  AbsenceCode varchar(25) DEFAULT NULL,
  PRIMARY KEY (ScheduleId, DateOut)
);
 
CREATE TABLE scheduletemplatedetail (
  TemplateId int(11) NOT NULL,
  DayOrder int(11) NOT NULL,
  AbsenceCode varchar(25) DEFAULT NULL,
  WorkShiftCd varchar(25) DEFAULT NULL,
  PRIMARY KEY (TemplateId, DayOrder)
);
 
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 1, NULL, '1/10');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 2, NULL, '1/11,5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 3, NULL, '1/10');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 4, NULL, '1/11,5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 5, NULL, '1/10');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 6, 'offdays', NULL);
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (1, 7, 'offdays', NULL);
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 1, NULL, '2/11,5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 2, NULL, '1/11,5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 3, NULL, '2/11,5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 4, NULL, '1|11.5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 5, NULL, '1/11,5');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 6, 'offdays', NULL);
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (2, 7, 'offdays', NULL);
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (3, 1, NULL, '1#8');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (3, 2, NULL, '1.0');
INSERT INTO scheduletemplatedetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
  VALUES (3, 3, 'business_trip', NULL);
 
CREATE TABLE schedule (
  ScheduleId int(11) NOT NULL AUTO_INCREMENT,
  TemplateId int(11) DEFAULT NULL,
  PRIMARY KEY (ScheduleId)
);
 
INSERT INTO schedule (ScheduleId, TemplateId)
  VALUES (1, 5);
INSERT INTO schedule (ScheduleId, TemplateId)
  VALUES (2, 1);
INSERT INTO schedule (ScheduleId, TemplateId)
  VALUES (4, 2);
INSERT INTO schedule (ScheduleId, TemplateId)
  VALUES (3, 3);
INSERT INTO schedule (ScheduleId, TemplateId)
  VALUES (5, 4);
 
DELIMITER $$