【Coursera】University of Colorado ; Database Management Essentials (6)
【Module 3 最終課題】
テーブル作成課題について。Assignment Detailsを参照する。
OracleでもMySQLでも可。(今回はMySQLを利用)
提出物の冒頭にMySQLの利用を明示してクエリーと作成したテーブルを提出。
1. Basic CREATE TABLE Statement Requirements
1. MySQLはストリングのカラムについてVARCHARを指定。
2. 主キーとなるCustNo, LocNo, EventNo, PlanNo, EmpNo, ResNo, and FacNoはVARCHAR型で文字数を8文字とすること。外部キー(EventRequestやCustNo) の定義は参照元と参照先で同じにすること。* (課題資料のテーブル名・カラム名に一貫性がない。ER図は全て大文字、テーブル例はおおよそ全て小文字、INSERT文のクエリファイルで使われる列名は全て大文字。)
3. MySQL利用時はEventRequestテーブルとEventPlanテーブルの日付カラムについてはDATE型を利用。EventPlanLineテーブルの時間カラムについてはDATETIME型を利用。
4. Customer.InternalについてはCHAR(1)型の利用を推薦。
2. Constraints
制約はインラインでもexternalでもどちらでも可。 制約名は意味がある名前にすること。
主キーに対して PRIMARY KEY constraint を設定すること。 単一カラムの主キー (CustNo, LocNo, EventNo, PlanNo, EmpNo, ResNo, and FacNo)はインラインでもexternalでも可。 For 複数列の主キー (combination of PlanNo and LineNo)についてはexternaで記述。
外部キーに対して FOREIGN KEY constraint を設定すること。 インラインでもexternalでも可。
eventplan.empno, EventRequest.DateAuth, EventRequest.BudNo, and EventPlan.Notesを除く全カラムにNOT NULL制約を設定すること。 主キーへのNOT NULL制約の設定を忘れないこと。 MySQLはexternalでNOT NULL制約を設定できないのでインラインで設定すること。
eventrequest.statusカラムはCHECK制約で”Pending”, “Denied”, or “Approved”のいずれかを含むようにすること。CHECK制約のINオペレーターで制約を設定。
CHECK制約でresource.rateとeventrequest.estaudienceが0より大きい値となるように制約を設定。
CHECK制約でEventPlanLine.TimeStart と EventPlanLine.TimeEndについて、TimeStartの値がTimeEndより小さい値となるように制約を設定。
3. Populating Tables
Populate data : データを投入する。
提供教材に、作成したテーブルに対してデータを投入するINSERTクエリーのファイルがある。
SQLクエリーのファイルを参照して親テーブルに対して先にデータを投入後、子テーブルに対してデータを投入すること。
教材のINSERT文はデータ投入に際して適切な順序になっている。
(クエリをみるとテーブル名とカラム名は全て大文字になっているので、適切な文字列に修正する必要がある。。。)
4. Initial CREATE TABLE Statements
Customer, Facility, and Location以外の5つのテーブルについて、module 3の課題で発行したSQLクエリーを参考にクエリを発行。(提出は5つのテーブルだがテーブル発行とデータ投入クエリの結果は以下に全テーブル分紹介。)
5. Submission
提出物は、5つのテーブルについてのCREATE TABLE文自体と、そのクエリの実行エビデンス、作成されたテーブルのエビデンス(スナップショット。SELECT * FROM <テーブル名>の内容)。
クエリとエビデンスは整形して見やすいようにすること。個人情報は載せないこと。テーブルのスナップショットはカラムと行が可能な限り多く映るように取得すること。
課題詳細のテーブル名、カラム名共に一貫性がない。ER図は全て大文字、テーブル例はおおよそ全て小文字、INSERT文のクエリファイルで使われるテーブル名とカラム名は全て大文字。
大量のINSERT文を修正するのは手間なので、テーブル名とカラム名は全て大文字とする。
テーブルを作成する。
## CUSTOMERテーブル mysql> CREATE TABLE CUSTOMER( -> CUSTNO char(10) NOT NULL PRIMARY KEY, -> CUSTNAME char(30) NOT NULL, -> ADDRESS char(20) NOT NULL, -> INTERNAL char(1) NOT NULL, -> CONTACT char(30) NOT NULL, -> PHONE int(20) NOT NULL, -> CITY char(20) NOT NULL, -> STATE char(20) NOT NULL, -> ZIP int(10) NOT NULL, -> CONSTRAINT Internal_check CHECK( INTERNAL IN(Y,N)) -> ); Query OK, 0 rows affected (0.03 sec) mysql> SHOW COLUMNS FROM CUSTOMER; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | CUSTNO | char(10) | NO | PRI | NULL | | | CUSTNAME | char(30) | NO | | NULL | | | ADDRESS | char(20) | NO | | NULL | | | INTERNAL | char(1) | NO | | NULL | | | CONTACT | char(30) | NO | | NULL | | | PHONE | int(20) | NO | | NULL | | | CITY | char(20) | NO | | NULL | | | STATE | char(20) | NO | | NULL | | | ZIP | int(10) | NO | | NULL | | +----------+----------+------+-----+---------+-------+ 9 rows in set (0.00 sec) mysql> ## FACILITYテーブル mysql> CREATE TABLE FACILITY( -> FACNO char(10) NOT NULL PRIMARY KEY, -> FACNAME char(30) NOT NULL -> ); Query OK, 0 rows affected (0.03 sec) mysql> SHOW COLUMNS FROM FACILITY; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | FACNO | char(10) | NO | PRI | NULL | | | FACNAME | char(30) | NO | | NULL | | +---------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> ## LOCATIONテーブル mysql> CREATE TABLE LOCATION( -> LOCNO char(10) NOT NULL PRIMARY KEY, -> FACNO char(10) NOT NULL, -> LOCNAME char(20) NOT NULL, -> FOREIGN KEY (FACNO) REFERENCES FACILITY(FACNO) -> ); Query OK, 0 rows affected (0.02 sec) mysql> SHOW COLUMNS FROM LOCATION; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | LOCNO | char(10) | NO | PRI | NULL | | | FACNO | char(10) | NO | MUL | NULL | | | LOCNAME | char(20) | NO | | NULL | | +---------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> ## EVENTREQUESTテーブル mysql> create table EVENTREQUEST( -> EVENTNO char(8) NOT NULL PRIMARY KEY, -> DATEHELD DATE NOT NULL, -> DATEREQ DATE NOT NULL, -> FACNO char(10) NOT NULL, -> CUSTNO char(10) NOT NULL, -> DATEAUTH DATE, -> STATUS char(10) NOT NULL, -> ESTCOST decimal(8,2) NOT NULL, -> ESTAUDIENCE int(8) NOT NULL, -> BUDNO char(5), -> CONSTRAINT FKcustno FOREIGN KEY (CUSTNO) REFERENCES CUSTOMER (CUSTNO), -> CONSTRAINT FKfacno FOREIGN KEY (FACNO) REFERENCES FACILITY (FACNO), -> CONSTRAINT ValidStatus CHECK (STATUS IN (Approved,Pending,Denied)), -> CONSTRAINT ValidValueForEstaudience CHECK ( ESTAUDIENCE > 0) -> ); Query OK, 0 rows affected (0.04 sec) mysql> SHOW COLUMNS FROM EVENTREQUEST -> ; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | EVENTNO | char(8) | NO | PRI | NULL | | | DATEHELD | date | NO | | NULL | | | DATEREQ | date | NO | | NULL | | | FACNO | char(10) | NO | MUL | NULL | | | CUSTNO | char(10) | NO | MUL | NULL | | | DATEAUTH | date | YES | | NULL | | | STATUS | char(10) | NO | | NULL | | | ESTCOST | decimal(8,2) | NO | | NULL | | | ESTAUDIENCE | int(8) | NO | | NULL | | | BUDNO | char(5) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 10 rows in set (0.00 sec) mysql> ## EMPLOYEEテーブル mysql> CREATE TABLE EMPLOYEE ( -> EMPNO char(8) not null PRIMARY KEY, -> EMPNAME char(20) not null, -> DEPARTMENT char(20) not null, -> EMAIL char(20) not null, -> PHONE char(10) not null -> ); Query OK, 0 rows affected (0.03 sec) mysql> SHOW COLUMNS FROM EMPLOYEE; +------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | EMPNO | char(8) | NO | PRI | NULL | | | EMPNAME | char(20) | NO | | NULL | | | DEPARTMENT | char(20) | NO | | NULL | | | EMAIL | char(20) | NO | | NULL | | | PHONE | char(10) | NO | | NULL | | +------------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> ## EVENTPLANテーブル mysql> CREATE TABLE EVENTPLAN ( -> PLANNO char(8) not null PRIMARY KEY, -> EVENTNO char(20) not null, -> WORKDATE DATE not null, -> NOTES char(30), -> ACTIVITY char(10) not null, -> EMPNO char(4), -> CONSTRAINT FKempno FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE(EMPNO), -> CONSTRAINT FKeventno FOREIGN KEY (EVENTNO) REFERENCES EVENTREQUEST(EVENTNO) -> ); Query OK, 0 rows affected (0.03 sec) mysql> SHOW COLUMNS FROM EVENTPLAN; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | PLANNO | char(8) | NO | PRI | NULL | | | EVENTNO | char(20) | NO | MUL | NULL | | | WORKDATE | date | NO | | NULL | | | NOTES | char(30) | YES | | NULL | | | ACTIVITY | char(10) | NO | | NULL | | | EMPNO | char(4) | YES | MUL | NULL | | +----------+----------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> ## RESOURCETBLテーブル mysql> CREATE TABLE RESOURCETBL ( -> RESNO char(8) NOT NULL PRIMARY KEY, -> RESNAME char(15) NOT NULL, -> RATE int(2) NOT NULL, -> CONSTRAINT OffYearValid CHECK ( OffYear > 1970 ), -> CONSTRAINT ValidValueForRate CHECK ( RATE > 0) -> ); Query OK, 0 rows affected (0.01 sec) mysql> SHOW COLUMNS FROM RESOURCETBL; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | RESNO | char(8) | NO | PRI | NULL | | | RESNAME | char(15) | NO | | NULL | | | RATE | int(2) | NO | | NULL | | +---------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> ## EVENTPLANLINEテーブル mysql> CREATE TABLE EVENTPLANLINE ( -> PLANNO char(4) NOT NULL, -> LINENO int(1) NOT NULL, -> TIMESTART DATE NOT NULL, -> TIMEEND DATE NOT NULL, -> NUMBERFLD int(1) NOT NULL, -> LOCNO char(4) NOT NULL, -> RESNO char(4) NOT NULL, -> CONSTRAINT FKplanno FOREIGN KEY (PLANNO) REFERENCES EVENTPLAN (PLANNO), -> CONSTRAINT PKplanno_lineno PRIMARY KEY (PLANNO,LINENO), -> CONSTRAINT FKlocno FOREIGN KEY (LOCNO) REFERENCES LOCATION (LOCNO), -> CONSTRAINT FKresno FOREIGN KEY (RESNO) REFERENCES RESOURCETBL (RESNO), -> CONSTRAINT ValidStatusForTime CHECK (TIMESTART < TIMEEND) -> ); Query OK, 0 rows affected (0.03 sec) mysql> SHOW COLUMNS FROM EVENTPLANLINE; +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | PLANNO | char(4) | NO | PRI | NULL | | | LINENO | int(1) | NO | PRI | NULL | | | TIMESTART | date | NO | | NULL | | | TIMEEND | date | NO | | NULL | | | NUMBERFLD | int(1) | NO | | NULL | | | LOCNO | char(4) | NO | MUL | NULL | | | RESNO | char(4) | NO | MUL | NULL | | +-----------+---------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql>
テーブル作成後、別紙INSERT文ファイルでデータを投入する。
##EMPLOYEEテーブル mysql> Insert into EMPLOYEE (EMPNO,EMPNAME,DEPARTMENT,EMAIL,PHONE) values ('E100','Chuck Coordinator','Administration','chuck@colorado.edu','3-1111'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EMPLOYEE (EMPNO,EMPNAME,DEPARTMENT,EMAIL,PHONE) values ('E101','Mary Manager','Football','mary@colorado.edu','5-1111'); Query OK, 1 row affected (0.01 sec) mysql> Insert into EMPLOYEE (EMPNO,EMPNAME,DEPARTMENT,EMAIL,PHONE) values ('E102','Sally Supervisor','Planning','sally@colorado.edu','3-2222'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EMPLOYEE (EMPNO,EMPNAME,DEPARTMENT,EMAIL,PHONE) values ('E103','Alan Administrator','Administration','alan@colorado.edu','3-3333'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM EMPLOYEE; +-------+--------------------+----------------+--------------------+--------+ | EMPNO | EMPNAME | DEPARTMENT | EMAIL | PHONE | +-------+--------------------+----------------+--------------------+--------+ | E100 | Chuck Coordinator | Administration | chuck@colorado.edu | 3-1111 | | E101 | Mary Manager | Football | mary@colorado.edu | 5-1111 | | E102 | Sally Supervisor | Planning | sally@colorado.edu | 3-2222 | | E103 | Alan Administrator | Administration | alan@colorado.edu | 3-3333 | +-------+--------------------+----------------+--------------------+--------+ 4 rows in set (0.00 sec) mysql> ## CUSTOMERテーブル mysql> Insert into CUSTOMER (CUSTNO,CUSTNAME,ADDRESS,INTERNAL,CONTACT,PHONE,CITY,STATE,ZIP) values ('C100','Football','Box 352200','Y','Mary Manager','6857100','Boulder','CO','80309'); Query OK, 1 row affected (0.00 sec) mysql> Insert into CUSTOMER (CUSTNO,CUSTNAME,ADDRESS,INTERNAL,CONTACT,PHONE,CITY,STATE,ZIP) values ('C101','Men''s Basketball','Box 352400','Y','Sally Supervisor','5431700','Boulder','CO','80309'); Query OK, 1 row affected (0.00 sec) mysql> Insert into CUSTOMER (CUSTNO,CUSTNAME,ADDRESS,INTERNAL,CONTACT,PHONE,CITY,STATE,ZIP) values ('C103','Baseball','Box 352020','Y','Bill Baseball','5431234','Boulder','CO','80309'); Query OK, 1 row affected (0.00 sec) mysql> Insert into CUSTOMER (CUSTNO,CUSTNAME,ADDRESS,INTERNAL,CONTACT,PHONE,CITY,STATE,ZIP) values ('C104','Women''s Softball','Box 351200','Y','Sue Softball','5434321','Boulder','CO','80309'); Query OK, 1 row affected (0.00 sec) mysql> Insert into CUSTOMER (CUSTNO,CUSTNAME,ADDRESS,INTERNAL,CONTACT,PHONE,CITY,STATE,ZIP) values ('C105','High School Football','123 AnyStreet','N','Coach Bob','4441234','Louisville','CO','80027'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM CUSTOMER; +--------+----------------------+---------------+----------+------------------+---------+------------+-------+-------+ | CUSTNO | CUSTNAME | ADDRESS | INTERNAL | CONTACT | PHONE | CITY | STATE | ZIP | +--------+----------------------+---------------+----------+------------------+---------+------------+-------+-------+ | C100 | Football | Box 352200 | Y | Mary Manager | 6857100 | Boulder | CO | 80309 | | C101 | Men's Basketball | Box 352400 | Y | Sally Supervisor | 5431700 | Boulder | CO | 80309 | | C103 | Baseball | Box 352020 | Y | Bill Baseball | 5431234 | Boulder | CO | 80309 | | C104 | Women's Softball | Box 351200 | Y | Sue Softball | 5434321 | Boulder | CO | 80309 | | C105 | High School Football | 123 AnyStreet | N | Coach Bob | 4441234 | Louisville | CO | 80027 | +--------+----------------------+---------------+----------+------------------+---------+------------+-------+-------+ 5 rows in set (0.00 sec) mysql> ## RESOURCETBLテーブル mysql> Insert into RESOURCETBL (RESNO,RESNAME,RATE) values ('R100','attendant',10); Query OK, 1 row affected (0.00 sec) mysql> Insert into RESOURCETBL (RESNO,RESNAME,RATE) values ('R101','police',15); Query OK, 1 row affected (0.01 sec) mysql> Insert into RESOURCETBL (RESNO,RESNAME,RATE) values ('R102','usher',10); Query OK, 1 row affected (0.00 sec) mysql> Insert into RESOURCETBL (RESNO,RESNAME,RATE) values ('R103','nurse',20); Query OK, 1 row affected (0.00 sec) mysql> Insert into RESOURCETBL (RESNO,RESNAME,RATE) values ('R104','janitor',15); Query OK, 1 row affected (0.00 sec) mysql> Insert into RESOURCETBL (RESNO,RESNAME,RATE) values ('R105','food service',10); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM RESOURCETBL; +-------+--------------+------+ | RESNO | RESNAME | RATE | +-------+--------------+------+ | R100 | attendant | 10 | | R101 | police | 15 | | R102 | usher | 10 | | R103 | nurse | 20 | | R104 | janitor | 15 | | R105 | food service | 10 | +-------+--------------+------+ 6 rows in set (0.00 sec) mysql> ## FACILITYテーブル mysql> Insert into FACILITY (FACNO,FACNAME) values ('F100','Football stadium'); Query OK, 1 row affected (0.01 sec) mysql> Insert into FACILITY (FACNO,FACNAME) values ('F101','Basketball arena'); Query OK, 1 row affected (0.01 sec) mysql> Insert into FACILITY (FACNO,FACNAME) values ('F102','Baseball field'); Query OK, 1 row affected (0.00 sec) mysql> Insert into FACILITY (FACNO,FACNAME) values ('F103','Recreation room'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM FACILITY; +-------+------------------+ | FACNO | FACNAME | +-------+------------------+ | F100 | Football stadium | | F101 | Basketball arena | | F102 | Baseball field | | F103 | Recreation room | +-------+------------------+ 4 rows in set (0.00 sec) mysql> ## LOCATIONテーブル mysql> Insert into LOCATION (LOCNO,FACNO,LOCNAME) values ('L100','F100','Locker room'); Query OK, 1 row affected (0.01 sec) mysql> Insert into LOCATION (LOCNO,FACNO,LOCNAME) values ('L101','F100','Plaza'); Query OK, 1 row affected (0.00 sec) mysql> Insert into LOCATION (LOCNO,FACNO,LOCNAME) values ('L102','F100','Vehicle gate'); Query OK, 1 row affected (0.01 sec) mysql> Insert into LOCATION (LOCNO,FACNO,LOCNAME) values ('L103','F101','Locker room'); Query OK, 1 row affected (0.00 sec) mysql> Insert into LOCATION (LOCNO,FACNO,LOCNAME) values ('L104','F100','Ticket Booth'); Query OK, 1 row affected (0.00 sec) mysql> Insert into LOCATION (LOCNO,FACNO,LOCNAME) values ('L105','F101','Gate'); Query OK, 1 row affected (0.01 sec) mysql> Insert into LOCATION (LOCNO,FACNO,LOCNAME) values ('L106','F100','Pedestrian gate'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM LOCATION; +-------+-------+-----------------+ | LOCNO | FACNO | LOCNAME | +-------+-------+-----------------+ | L100 | F100 | Locker room | | L101 | F100 | Plaza | | L102 | F100 | Vehicle gate | | L103 | F101 | Locker room | | L104 | F100 | Ticket Booth | | L105 | F101 | Gate | | L106 | F100 | Pedestrian gate | +-------+-------+-----------------+ 7 rows in set (0.01 sec) mysql> ## EVENTREQUESTテーブル mysql> Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E100',str_to_date('25,OCT,18','%d,%b,%y'),str_to_date('06,JUN,18','%d,%b,%y'),'C100','F100',str_to_date('08,JUN,18','%d,%b,%y'),'Approved',5000,80000,'B1000'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E101',str_to_date('26,OCT,18','%d,%b,%y'),str_to_date('28,JUL,18','%d,%b,%y'),'C100','F100',null,'Pending',5000,80000,'B1000'); 18','%d,%b,%y'),'Approved',5000,80000,'B1000'); Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E104',str_to_date('03,DEC,18','%d,%b,%y'),str_to_date('28,JUL,18','%d,%b,%y'),'C101','F101',str_to_date('31,JUL,18','%d,%b,%y'),'Approved',2000,12000,'B1000'); Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E105',str_to_date('05,DEC,18','%d,%b,%y'),str_to_date('28,JUL,18','%d,%b,%y'),'C101','F101',str_to_date('01,AUG,18','%d,%b,%y'),'Approved',2000,10000,'B1000'); Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E106',str_to_date('12,DEC,18','%d,%b,%y'),str_to_date('28,JUL,18','%d,%b,%y'),'C101','F101',str_to_date('31,JUL,18','%d,%b,%y'),'Approved',2000,10000,'B1000'); Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E107',str_to_date('23,NOV,18','%Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E103',str_to_date('21,SEP,18','%d,%b,%y'),str_to_date('28,JUL,18','%d,%b,%y'),'C100','F100',str_to_date('01,AUG,18','%d,%b,%y'),'Approved',5000,80000,'B1000'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E102',str_to_date('14,SEP,18','%d,%b,%y'),str_to_date('28,JUL,18','%d,%b,%y'),'C100','F100',str_to_date('31,JUL,18','%d,%b,%y'),'Approved',5000,80000,'B1000'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E104',str_to_date('03,DEC,18','%d,%b,%y'),str_to_date('28,JUL,18','%d,%b,%y'),'C101','F101',str_to_date('31,JUL,18','%d,%b,%y'),'Approved',2000,12000,'B1000'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E105',str_to_date('05,DEC,18','%d,%b,%y'),str_to_date('28,JUL,18','%d,%b,%y'),'C101','F101',str_to_date('01,AUG,18','%d,%b,%y'),'Approved',2000,10000,'B1000'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E106',str_to_date('12,DEC,18','%d,%b,%y'),str_to_date('28,JUL,18','%d,%b,%y'),'C101','F101',str_to_date('31,JUL,18','%d,%b,%y'),'Approved',2000,10000,'B1000'); d,%b,%y'),str_to_date('28,JUL,18','%d,%b,%y'),'C105','F100',str_to_date('31,JUL,18','%d,%b,%y'),'Denied',10000,5000,null); Query OK, 1 row affected (0.01 sec) mysql> Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E107',str_to_date('23,NOV,18','%d,%b,%y'),str_to_date('28,JUL,18','%d,%b,%y'),'C105','F100',str_to_date('31,JUL,18','%d,%b,%y'),'Denied',10000,5000,null); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM EVENTREQUEST; +---------+------------+------------+-------+--------+------------+----------+----------+-------------+-------+ | EVENTNO | DATEHELD | DATEREQ | FACNO | CUSTNO | DATEAUTH | STATUS | ESTCOST | ESTAUDIENCE | BUDNO | +---------+------------+------------+-------+--------+------------+----------+----------+-------------+-------+ | E100 | 2018-10-25 | 2018-06-06 | F100 | C100 | 2018-06-08 | Approved | 5000.00 | 80000 | B1000 | | E101 | 2018-10-26 | 2018-07-28 | F100 | C100 | NULL | Pending | 5000.00 | 80000 | B1000 | | E102 | 2018-09-14 | 2018-07-28 | F100 | C100 | 2018-07-31 | Approved | 5000.00 | 80000 | B1000 | | E103 | 2018-09-21 | 2018-07-28 | F100 | C100 | 2018-08-01 | Approved | 5000.00 | 80000 | B1000 | | E104 | 2018-12-03 | 2018-07-28 | F101 | C101 | 2018-07-31 | Approved | 2000.00 | 12000 | B1000 | | E105 | 2018-12-05 | 2018-07-28 | F101 | C101 | 2018-08-01 | Approved | 2000.00 | 10000 | B1000 | | E106 | 2018-12-12 | 2018-07-28 | F101 | C101 | 2018-07-31 | Approved | 2000.00 | 10000 | B1000 | | E107 | 2018-11-23 | 2018-07-28 | F100 | C105 | 2018-07-31 | Denied | 10000.00 | 5000 | NULL | +---------+------------+------------+-------+--------+------------+----------+----------+-------------+-------+ 8 rows in set (0.00 sec) mysql> ##EVENTPLANテーブル mysql> Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P100','E100',str_to_date('25,OCT,18','%d,%b,%y'),'Standard operation','Operation','E102'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P101','E104',str_to_date('03,DEC,18','%d,%b,%y'),'Watch for gate crashers','Operation','E100'); EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P199','E102',str_to_date('10,DEC,18','%d,%b,%y'),'ABC','Operation','E101'); Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P299','E101',str_to_date('26,OCT,18','%d,%b,%y'),null,'Operation','E101'); Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P349','E106',str_to_date('12,DEC,18','%d,%b,%y'),null,'Setup','E101'); Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P85','E100',str_to_date('25,OCT,18','%d,%b,%y'),'Standard operation','Cleanup','E102'); Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P95','E101',str_to_date('26,OCT,18','%d,%b,%y'),'Extra security','Cleanup','E102'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P102','E105',str_to_date('05,DEC,18','%d,%b,%y'),'Standard operation','Operation','E102'); Query OK, 1 row affected (0.01 sec) mysql> Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P103','E106',str_to_date('12,DEC,18','%d,%b,%y'),'Watch for seat switching','Operation',null); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P104','E101',str_to_date('26,OCT,18','%d,%b,%y'),'Standard cleanup','Cleanup','E101'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P105','E100',str_to_date('25,OCT,18','%d,%b,%y'),'Light cleanup','Cleanup','E101'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P199','E102',str_to_date('10,DEC,18','%d,%b,%y'),'ABC','Operation','E101'); Query OK, 1 row affected (0.01 sec) mysql> Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P299','E101',str_to_date('26,OCT,18','%d,%b,%y'),null,'Operation','E101'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P349','E106',str_to_date('12,DEC,18','%d,%b,%y'),null,'Setup','E101'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P85','E100',str_to_date('25,OCT,18','%d,%b,%y'),'Standard operation','Cleanup','E102'); Query OK, 1 row affected (0.00 sec) mysql> Insert into EVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P95','E101',str_to_date('26,OCT,18','%d,%b,%y'),'Extra security','Cleanup','E102'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM EVENTPLAN; +--------+---------+------------+--------------------------+-----------+-------+ | PLANNO | EVENTNO | WORKDATE | NOTES | ACTIVITY | EMPNO | +--------+---------+------------+--------------------------+-----------+-------+ | P100 | E100 | 2018-10-25 | Standard operation | Operation | E102 | | P101 | E104 | 2018-12-03 | Watch for gate crashers | Operation | E100 | | P102 | E105 | 2018-12-05 | Standard operation | Operation | E102 | | P103 | E106 | 2018-12-12 | Watch for seat switching | Operation | NULL | | P104 | E101 | 2018-10-26 | Standard cleanup | Cleanup | E101 | | P105 | E100 | 2018-10-25 | Light cleanup | Cleanup | E101 | | P199 | E102 | 2018-12-10 | ABC | Operation | E101 | | P299 | E101 | 2018-10-26 | NULL | Operation | E101 | | P349 | E106 | 2018-12-12 | NULL | Setup | E101 | | P85 | E100 | 2018-10-25 | Standard operation | Cleanup | E102 | | P95 | E101 | 2018-10-26 | Extra security | Cleanup | E102 | +--------+---------+------------+--------------------------+-----------+-------+ 11 rows in set (0.00 sec) mysql> ##EVENTPLANLINEテーブル mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P100',1, str_to_date('25,OCT,18 8:00:00','%d,%b,%y %H:%i:%s'), str_to_date('25,OCT,18 17:00:00','%d,%b,%y %H:%i:%s'),2,'L100','R100'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P100',2, str_to_date('25,OCT,18 12:00:00','%d,%b,%y %H:%i:%s'),str_to_date('25,OCT,18 17:00:00','%d,%b,%y %H:%i:%s'), 2,'L101','R101'); Query OK, 1 row affected, 2 warnings (0.01 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P100',3, str_to_date('25,OCT,18 7:00:00','%d,%b,%y %H:%i:%s'), str_to_date('25,OCT,18 16:30:00','%d,%b,%y %H:%i:%s'), 1,'L102','R102'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P100',4, str_to_date('25,OCT,18 18:00:00','%d,%b,%y %H:%i:%s'),str_to_date('25,OCT,18 22:00:00','%d,%b,%y %H:%i:%s'),2,'L100','R102'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P101',1, str_to_date('3,DEC,18 18:00:00','%d,%b,%y %H:%i:%s'),str_to_date('3,DEC,18 20:00:00','%d,%b,%y %H:%i:%s'),2,'L103','R100'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P101',2, str_to_date('3,DEC,18 18:30:00','%d,%b,%y %H:%i:%s'),str_to_date('3,DEC,18 19:00:00','%d,%b,%y %H:%i:%s'),4,'L105','R100'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P101',3, str_to_date('3,DEC,18 19:00:00','%d,%b,%y %H:%i:%s'),str_to_date('3,DEC,18 20:00:00','%d,%b,%y %H:%i:%s'),2,'L103','R103'); Query OK, 1 row affected, 2 warnings (0.01 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P102',1, str_to_date('5,DEC,18 18:00:00','%d,%b,%y %H:%i:%s'),str_to_date('5,DEC,18 19:00:00','%d,%b,%y %H:%i:%s'),2,'L103','R100'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P102',2, str_to_date('5,DEC,18 18:00:00','%d,%b,%y %H:%i:%s'),str_to_date('5,DEC,18 21:00:00','%d,%b,%y %H:%i:%s'),4,'L105','R100'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P102',3, str_to_date('5,DEC,18 19:00:00','%d,%b,%y %H:%i:%s'),str_to_date('5,DEC,18 22:00:00','%d,%b,%y %H:%i:%s'),2,'L103','R103'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P103',1, str_to_date('12,DEC,18 18:00:00','%d,%b,%y %H:%i:%s'),str_to_date('12,DEC,18 21:00:00','%d,%b,%y %H:%i:%s'),2,'L103','R100'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P103',2, str_to_date('12,DEC,18 18:00:00','%d,%b,%y %H:%i:%s'),str_to_date('12,DEC,18 21:00:00','%d,%b,%y %H:%i:%s'),4,'L105','R100'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P103',3, str_to_date('12,DEC,18 19:00:00','%d,%b,%y %H:%i:%s'),str_to_date('12,DEC,18 22:00:00','%d,%b,%y %H:%i:%s'),2,'L103','R103'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P104',1, str_to_date('26,OCT,18 18:00:00','%d,%b,%y %H:%i:%s'),str_to_date('26,OCT,18 22:00:00','%d,%b,%y %H:%i:%s'),4,'L101','R104'); Query OK, 1 row affected, 2 warnings (0.01 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P104',2, str_to_date('26,OCT,18 18:00:00','%d,%b,%y %H:%i:%s'),str_to_date('26,OCT,18 22:00:00','%d,%b,%y %H:%i:%s'),4,'L100','R104'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P105',1, str_to_date('25,OCT,18 18:00:00','%d,%b,%y %H:%i:%s'),str_to_date('25,OCT,18 22:00:00','%d,%b,%y %H:%i:%s'),4,'L101','R104'); 18 18:00:00','%d,%b,%y %H:%i:%s'),str_to_date('25,OCT,18 22:00:00','%d,%b,%y %H:%i:%s'),4,'L100','R104');Query OK, 1 row affected, 2 warnings (0.01 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P105',2, str_to_date('25,OCT,18 18:00:00','%d,%b,%y %H:%i:%s'),str_to_date('25,OCT,18 22:00:00','%d,%b,%y %H:%i:%s'),4,'L100','R104'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P199',1, str_to_date('10,DEC,18 8:00:00','%d,%b,%y %H:%i:%s'), str_to_date('10,DEC,18 12:00:00','%d,%b,%y %H:%i:%s'),1,'L100','R100'); Query OK, 1 row affected, 2 warnings (0.01 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P349',1, str_to_date('12,DEC,18 12:00:00','%d,%b,%y %H:%i:%s'),str_to_date('12,DEC,18 15:30:00','%d,%b,%y %H:%i:%s'),1,'L103','R100'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P85',1, str_to_date('25,OCT,18 9:00:00','%d,%b,%y %H:%i:%s'), str_to_date('25,OCT,18 17:00:00','%d,%b,%y %H:%i:%s'),5,'L100','R100'); Query OK, 1 row affected, 2 warnings (0.01 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P85',2, str_to_date('25,OCT,18 8:00:00','%d,%b,%y %H:%i:%s'), str_to_date('25,OCT,18 17:00:00','%d,%b,%y %H:%i:%s'),2,'L102','R101'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P85',3, str_to_date('25,OCT,18 10:00:00','%d,%b,%y %H:%i:%s'), str_to_date('25,OCT,18 15:00:00','%d,%b,%y %H:%i:%s'),3,'L104','R100'); Query OK, 1 row affected, 2 warnings (0.01 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P95',1, str_to_date('26,OCT,18 8:00:00','%d,%b,%y %H:%i:%s'), str_to_date('26,OCT,18 17:00:00','%d,%b,%y %H:%i:%s'),4,'L100','R100'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P95',2, str_to_date('26,OCT,18 9:00:00','%d,%b,%y %H:%i:%s'), str_to_date('26,OCT,18 17:00:00','%d,%b,%y %H:%i:%s'),4,'L102','R101'); Query OK, 1 row affected, 2 warnings (0.01 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P95',3, str_to_date('26,OCT,18 10:00:00','%d,%b,%y %H:%i:%s'), str_to_date('26,OCT,18 15:00:00','%d,%b,%y %H:%i:%s'),4,'L106','R100'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P95',4, str_to_date('26,OCT,18 13:00:00','%d,%b,%y %H:%i:%s'), str_to_date('26,OCT,18 17:00:00','%d,%b,%y %H:%i:%s'),2,'L100','R103'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> Insert into EVENTPLANLINE (PLANNO,LINENO,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P95',5, str_to_date('26,OCT,18 13:00:00','%d,%b,%y %H:%i:%s'), str_to_date('26,OCT,18 17:00:00','%d,%b,%y %H:%i:%s'),2,'L101','R104'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> mysql> select * from EVENTPLANLINE; +--------+--------+------------+------------+-----------+-------+-------+ | PLANNO | LINENO | TIMESTART | TIMEEND | NUMBERFLD | LOCNO | RESNO | +--------+--------+------------+------------+-----------+-------+-------+ | P100 | 1 | 2018-10-25 | 2018-10-25 | 2 | L100 | R100 | | P100 | 2 | 2018-10-25 | 2018-10-25 | 2 | L101 | R101 | | P100 | 3 | 2018-10-25 | 2018-10-25 | 1 | L102 | R102 | | P100 | 4 | 2018-10-25 | 2018-10-25 | 2 | L100 | R102 | | P101 | 1 | 2018-12-03 | 2018-12-03 | 2 | L103 | R100 | | P101 | 2 | 2018-12-03 | 2018-12-03 | 4 | L105 | R100 | | P101 | 3 | 2018-12-03 | 2018-12-03 | 2 | L103 | R103 | | P102 | 1 | 2018-12-05 | 2018-12-05 | 2 | L103 | R100 | | P102 | 2 | 2018-12-05 | 2018-12-05 | 4 | L105 | R100 | | P102 | 3 | 2018-12-05 | 2018-12-05 | 2 | L103 | R103 | | P103 | 1 | 2018-12-12 | 2018-12-12 | 2 | L103 | R100 | | P103 | 2 | 2018-12-12 | 2018-12-12 | 4 | L105 | R100 | | P103 | 3 | 2018-12-12 | 2018-12-12 | 2 | L103 | R103 | | P104 | 1 | 2018-10-26 | 2018-10-26 | 4 | L101 | R104 | | P104 | 2 | 2018-10-26 | 2018-10-26 | 4 | L100 | R104 | | P105 | 1 | 2018-10-25 | 2018-10-25 | 4 | L101 | R104 | | P105 | 2 | 2018-10-25 | 2018-10-25 | 4 | L100 | R104 | | P199 | 1 | 2018-12-10 | 2018-12-10 | 1 | L100 | R100 | | P349 | 1 | 2018-12-12 | 2018-12-12 | 1 | L103 | R100 | | P85 | 1 | 2018-10-25 | 2018-10-25 | 5 | L100 | R100 | | P85 | 2 | 2018-10-25 | 2018-10-25 | 2 | L102 | R101 | | P85 | 3 | 2018-10-25 | 2018-10-25 | 3 | L104 | R100 | | P95 | 1 | 2018-10-26 | 2018-10-26 | 4 | L100 | R100 | | P95 | 2 | 2018-10-26 | 2018-10-26 | 4 | L102 | R101 | | P95 | 3 | 2018-10-26 | 2018-10-26 | 4 | L106 | R100 | | P95 | 4 | 2018-10-26 | 2018-10-26 | 2 | L100 | R103 | | P95 | 5 | 2018-10-26 | 2018-10-26 | 2 | L101 | R104 | +--------+--------+------------+------------+-----------+-------+-------+ 27 rows in set (0.00 sec) mysql>
