github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/partition3.sql (about)

     1  -- 1.test KEY Partition
     2  drop table if exists t1;
     3  CREATE TABLE t1 (
     4  col1 INT NOT NULL,
     5  col2 DATE NOT NULL,
     6  col3 INT NOT NULL UNIQUE
     7  )
     8  PARTITION BY KEY(col3)
     9  PARTITIONS 4;
    10  insert into t1 values
    11  (1, '1980-12-17', 7369),
    12  (2, '1981-02-20', 7499),
    13  (3, '1981-02-22', 7521),
    14  (4, '1981-04-02', 7566),
    15  (5, '1981-09-28', 7654),
    16  (6, '1981-05-01', 7698),
    17  (7, '1981-06-09', 7782),
    18  (8, '0087-07-13', 7788),
    19  (9, '1981-11-17', 7839),
    20  (10, '1981-09-08', 7844),
    21  (11, '2007-07-13', 7876),
    22  (12, '1981-12-03', 7900),
    23  (13, '1987-07-13', 7980),
    24  (14, '2001-11-17', 7981),
    25  (15, '1951-11-08', 7982),
    26  (16, '1927-10-13', 7983),
    27  (17, '1671-12-09', 7984),
    28  (18, '1981-11-06', 7985),
    29  (19, '1771-12-06', 7986),
    30  (20, '1985-10-06', 7987),
    31  (21, '1771-10-06', 7988),
    32  (22, '1981-10-05', 7989),
    33  (23, '2001-12-04', 7990),
    34  (24, '1999-08-01', 7991),
    35  (25, '1951-11-08', 7992),
    36  (26, '1927-10-13', 7993),
    37  (27, '1971-12-09', 7994),
    38  (28, '1981-12-09', 7995),
    39  (29, '2001-11-17', 7996),
    40  (30, '1981-12-09', 7997),
    41  (31, '2001-11-17', 7998),
    42  (32, '2001-11-17', 7999);
    43  select * from `%!%p0%!%t1` order by col1;
    44  select * from `%!%p1%!%t1` order by col1;
    45  select * from `%!%p2%!%t1` order by col1;
    46  select * from `%!%p3%!%t1` order by col1;
    47  select * from t1 where col1 > 5 order by col3;
    48  delete from t1 where col1 > 20;
    49  select * from t1 order by col1;
    50  select * from `%!%p0%!%t1` order by col1;
    51  select * from `%!%p1%!%t1` order by col1;
    52  select * from `%!%p2%!%t1` order by col1;
    53  select * from `%!%p3%!%t1` order by col1;
    54  drop table t1;
    55  
    56  -- 2.test HASH Partition
    57  drop table if exists t2;
    58  CREATE TABLE t2 (
    59  col1 INT NOT NULL,
    60  col2 INT NOT NULL,
    61  col3 DATE NOT NULL,
    62  UNIQUE KEY (col1, col3)
    63  )
    64  PARTITION BY HASH(col1 + col3)
    65  PARTITIONS 6;
    66  
    67  insert into t2 values
    68  (1, 7369, '1980-12-17'),
    69  (2, 7499, '1981-02-20'),
    70  (3, 7521, '1981-02-22'),
    71  (4, 7566, '1981-04-02'),
    72  (5, 7654, '1981-09-28'),
    73  (6, 7698, '1981-05-01'),
    74  (7, 7782, '1981-06-09'),
    75  (8, 7788, '0087-07-13'),
    76  (9, 7839, '1981-11-17'),
    77  (10, 7844, '1981-09-08'),
    78  (11, 7876, '2007-07-13'),
    79  (12, 7900, '1981-12-03'),
    80  (13, 7980, '1987-07-13'),
    81  (14, 7981, '2001-11-17'),
    82  (15, 7982, '1951-11-08'),
    83  (16, 7983, '1927-10-13'),
    84  (17, 7984, '1671-12-09'),
    85  (18, 7985, '1981-11-06'),
    86  (19, 7986, '1771-12-06'),
    87  (20, 7987, '1985-10-06'),
    88  (21, 7988, '1771-10-06'),
    89  (22, 7989, '1981-10-05'),
    90  (23, 7990, '2001-12-04'),
    91  (24, 7991, '1999-08-01'),
    92  (25, 7992, '1951-11-08'),
    93  (26, 7993, '1927-10-13'),
    94  (27, 7994, '1971-12-09'),
    95  (28, 7995, '1981-12-09'),
    96  (29, 7996, '2001-11-17'),
    97  (30, 7997, '1981-12-09'),
    98  (31, 7998, '2001-11-17'),
    99  (32, 7999, '2001-11-17'),
   100  (33, 7980, '1987-07-13'),
   101  (34, 7981, '2001-11-17'),
   102  (35, 7982, '1951-11-08'),
   103  (36, 7983, '1927-10-13'),
   104  (37, 7984, '1671-12-09'),
   105  (38, 7985, '1981-11-06'),
   106  (39, 7986, '1771-12-06'),
   107  (40, 7987, '1985-10-06');
   108  
   109  SELECT * from t2 WHERE col1 BETWEEN 0 and 25 order by col1;
   110  SELECT * from t2 WHERE col1 BETWEEN 26 and 30 order by col1;
   111  select * from `%!%p0%!%t2` order by col1;
   112  select * from `%!%p1%!%t2` order by col1;
   113  select * from `%!%p2%!%t2` order by col1;
   114  select * from `%!%p3%!%t2` order by col1;
   115  
   116  delete from t2 where col1 > 30;
   117  select * from t2 order by col1;
   118  select * from `%!%p0%!%t2` order by col1;
   119  select * from `%!%p1%!%t2` order by col1;
   120  select * from `%!%p2%!%t2` order by col1;
   121  select * from `%!%p3%!%t2` order by col1;
   122  drop table t2;
   123  
   124  -- 3.test RANGE COLUMNS Partition
   125  drop table if exists t3;
   126  CREATE TABLE t3 (
   127  id int NOT NULL AUTO_INCREMENT,
   128  key_num int NOT NULL DEFAULT '0',
   129  hiredate date NOT NULL,
   130  PRIMARY KEY (id),
   131  KEY key_num (key_num)
   132  )
   133  PARTITION BY RANGE COLUMNS(id) (
   134  PARTITION p0 VALUES LESS THAN(10),
   135  PARTITION p1 VALUES LESS THAN(20),
   136  PARTITION p2 VALUES LESS THAN(30),
   137  PARTITION p3 VALUES LESS THAN(MAXVALUE)
   138  );
   139  
   140  insert into t3 values
   141  (1, 7369, '1980-12-17'),
   142  (2, 7499, '1981-02-20'),
   143  (3, 7521, '1981-02-22'),
   144  (4, 7566, '1981-04-02'),
   145  (5, 7654, '1981-09-28'),
   146  (6, 7698, '1981-05-01'),
   147  (7, 7782, '1981-06-09'),
   148  (8, 7788, '0087-07-13'),
   149  (9, 7839, '1981-11-17'),
   150  (10, 7844, '1981-09-08'),
   151  (11, 7876, '2007-07-13'),
   152  (12, 7900, '1981-12-03'),
   153  (13, 7980, '1987-07-13'),
   154  (14, 7981, '2001-11-17'),
   155  (15, 7982, '1951-11-08'),
   156  (16, 7983, '1927-10-13'),
   157  (17, 7984, '1671-12-09'),
   158  (18, 7985, '1981-11-06'),
   159  (19, 7986, '1771-12-06'),
   160  (20, 7987, '1985-10-06'),
   161  (21, 7988, '1771-10-06'),
   162  (22, 7989, '1981-10-05'),
   163  (23, 7990, '2001-12-04'),
   164  (24, 7991, '1999-08-01'),
   165  (25, 7992, '1951-11-08'),
   166  (26, 7993, '1927-10-13'),
   167  (27, 7994, '1971-12-09'),
   168  (28, 7995, '1981-12-09'),
   169  (29, 7996, '2001-11-17'),
   170  (30, 7997, '1981-12-09'),
   171  (31, 7998, '2001-11-17'),
   172  (32, 7999, '2001-11-17'),
   173  (33, 7980, '1987-07-13'),
   174  (34, 7981, '2001-11-17'),
   175  (35, 7982, '1951-11-08'),
   176  (36, 7983, '1927-10-13'),
   177  (37, 7984, '1671-12-09'),
   178  (38, 7985, '1981-11-06'),
   179  (39, 7986, '1771-12-06'),
   180  (40, 7987, '1985-10-06');
   181  
   182  SELECT * from t3 WHERE id BETWEEN 0 and 9 order by id;
   183  SELECT * from t3 WHERE id BETWEEN 20 and 29 order by id;
   184  select * from `%!%p0%!%t3` order by id;
   185  select * from `%!%p1%!%t3` order by id;
   186  select * from `%!%p2%!%t3` order by id;
   187  select * from `%!%p3%!%t3` order by id;
   188  
   189  delete from t3 where id > 30;
   190  select * from t3 order by id;
   191  select * from `%!%p0%!%t3` order by id;
   192  select * from `%!%p1%!%t3` order by id;
   193  select * from `%!%p2%!%t3` order by id;
   194  select * from `%!%p3%!%t3` order by id;
   195  drop table t3;
   196  
   197  -- 4.test RANGE Partition
   198  drop table if exists titles;
   199  CREATE TABLE titles (
   200  emp_no      INT             NOT NULL,
   201  title       VARCHAR(50)     NOT NULL,
   202  from_date   DATE            NOT NULL,
   203  to_date     DATE,
   204  PRIMARY KEY (emp_no, title, from_date)
   205  )
   206  partition by range (to_days(from_date))
   207  (
   208      partition p01 values less than (to_days('1985-12-31')),
   209      partition p02 values less than (to_days('1986-12-31')),
   210      partition p03 values less than (to_days('1987-12-31')),
   211      partition p04 values less than (to_days('1988-12-31')),
   212      partition p05 values less than (to_days('1989-12-31')),
   213      partition p06 values less than (to_days('1990-12-31')),
   214      partition p07 values less than (to_days('1991-12-31')),
   215      partition p08 values less than (to_days('1992-12-31')),
   216      partition p09 values less than (to_days('1993-12-31')),
   217      partition p10 values less than (to_days('1994-12-31')),
   218      partition p11 values less than (to_days('1995-12-31')),
   219      partition p12 values less than (to_days('1996-12-31')),
   220      partition p13 values less than (to_days('1997-12-31')),
   221      partition p14 values less than (to_days('1998-12-31')),
   222      partition p15 values less than (to_days('1999-12-31')),
   223      partition p16 values less than (to_days('2000-12-31')),
   224      partition p17 values less than (to_days('2001-12-31')),
   225      partition p18 values less than (to_days('2002-12-31')),
   226      partition p19 values less than (to_days('3000-12-31'))
   227  );
   228  
   229  INSERT INTO `titles` VALUES
   230  (10001,'Senior Engineer','1986-06-26','9999-01-01'),
   231  (10002,'Staff','1996-08-03','9999-01-01'),
   232  (10003,'Senior Engineer','1995-12-03','9999-01-01'),
   233  (10004,'Engineer','1986-12-01','1995-12-01'),
   234  (10004,'Senior Engineer','1995-12-01','9999-01-01'),
   235  (10005,'Senior Staff','1996-09-12','9999-01-01'),
   236  (10005,'Staff','1989-09-12','1996-09-12'),
   237  (10006,'Senior Engineer','1990-08-05','9999-01-01'),
   238  (10007,'Senior Staff','1996-02-11','9999-01-01'),
   239  (10007,'Staff','1989-02-10','1996-02-11'),
   240  (10008,'Assistant Engineer','1998-03-11','2000-07-31'),
   241  (10009,'Assistant Engineer','1985-02-18','1990-02-18'),
   242  (10009,'Engineer','1990-02-18','1995-02-18'),
   243  (10009,'Senior Engineer','1995-02-18','9999-01-01'),
   244  (10010,'Engineer','1996-11-24','9999-01-01'),
   245  (10011,'Staff','1990-01-22','1996-11-09'),
   246  (10012,'Engineer','1992-12-18','2000-12-18'),
   247  (10012,'Senior Engineer','2000-12-18','9999-01-01'),
   248  (10013,'Senior Staff','1985-10-20','9999-01-01'),
   249  (10014,'Engineer','1993-12-29','9999-01-01'),
   250  (10015,'Senior Staff','1992-09-19','1993-08-22'),
   251  (10016,'Staff','1998-02-11','9999-01-01'),
   252  (10017,'Senior Staff','2000-08-03','9999-01-01'),
   253  (10017,'Staff','1993-08-03','2000-08-03'),
   254  (10018,'Engineer','1987-04-03','1995-04-03'),
   255  (10018,'Senior Engineer','1995-04-03','9999-01-01'),
   256  (10019,'Staff','1999-04-30','9999-01-01'),
   257  (10020,'Engineer','1997-12-30','9999-01-01'),
   258  (10021,'Technique Leader','1988-02-10','2002-07-15'),
   259  (10022,'Engineer','1999-09-03','9999-01-01'),
   260  (10023,'Engineer','1999-09-27','9999-01-01'),
   261  (10024,'Assistant Engineer','1998-06-14','9999-01-01'),
   262  (10025,'Technique Leader','1987-08-17','1997-10-15'),
   263  (10026,'Engineer','1995-03-20','2001-03-19'),
   264  (10026,'Senior Engineer','2001-03-19','9999-01-01'),
   265  (10027,'Engineer','1995-04-02','2001-04-01'),
   266  (10027,'Senior Engineer','2001-04-01','9999-01-01'),
   267  (10028,'Engineer','1991-10-22','1998-04-06'),
   268  (10029,'Engineer','1991-09-18','2000-09-17'),
   269  (10029,'Senior Engineer','2000-09-17','9999-01-01'),
   270  (10030,'Engineer','1994-02-17','2001-02-17'),
   271  (10030,'Senior Engineer','2001-02-17','9999-01-01');
   272  
   273  select * from titles order by emp_no;
   274  
   275  select * from `%!%p01%!%titles` order by emp_no;
   276  select * from `%!%p02%!%titles` order by emp_no;
   277  select * from `%!%p03%!%titles` order by emp_no;
   278  select * from `%!%p04%!%titles` order by emp_no;
   279  select * from `%!%p05%!%titles` order by emp_no;
   280  select * from `%!%p06%!%titles` order by emp_no;
   281  select * from `%!%p07%!%titles` order by emp_no;
   282  select * from `%!%p08%!%titles` order by emp_no;
   283  select * from `%!%p09%!%titles` order by emp_no;
   284  select * from `%!%p10%!%titles` order by emp_no;
   285  select * from `%!%p11%!%titles` order by emp_no;
   286  select * from `%!%p12%!%titles` order by emp_no;
   287  select * from `%!%p13%!%titles` order by emp_no;
   288  select * from `%!%p14%!%titles` order by emp_no;
   289  select * from `%!%p15%!%titles` order by emp_no;
   290  select * from `%!%p16%!%titles` order by emp_no;
   291  select * from `%!%p17%!%titles` order by emp_no;
   292  select * from `%!%p18%!%titles` order by emp_no;
   293  select * from `%!%p19%!%titles` order by emp_no;
   294  
   295  select to_days(from_date) from titles order by emp_no;
   296  
   297  select * from titles where to_days(from_date) between to_days('1988-12-31') and to_days('1992-12-31');
   298  delete from titles where to_days(from_date) between to_days('1988-12-31') and to_days('1992-12-31');
   299  select * from titles order by emp_no;
   300  
   301  drop table titles;
   302  
   303  -- 5.test List Partition
   304  drop table if exists employees;
   305  CREATE TABLE employees (
   306  id INT NOT NULL,
   307  fname VARCHAR(30),
   308  lname VARCHAR(30),
   309  hired DATE NOT NULL DEFAULT '1970-01-01',
   310  separated DATE NOT NULL DEFAULT '9999-12-31',
   311  job_code INT,
   312  store_id INT
   313  )
   314  PARTITION BY LIST(store_id) (
   315  	PARTITION pNorth VALUES IN (3,5,6,9,17),
   316  	PARTITION pEast VALUES IN (1,2,10,11,19,20),
   317  	PARTITION pWest VALUES IN (4,12,13,14,18),
   318  	PARTITION pCentral VALUES IN (7,8,15,16)
   319  );
   320  
   321  INSERT INTO employees VALUES
   322  (10001, 'Georgi', 'Facello', '1953-09-02','1986-06-26',120, 1),
   323  (10002, 'Bezalel', 'Simmel', '1964-06-02','1985-11-21',150, 7),
   324  (10003, 'Parto', 'Bamford', '1959-12-03','1986-08-28',140, 3),
   325  (10004, 'Chirstian', 'Koblick', '1954-05-01','1986-12-01',150, 3),
   326  (10005, 'Kyoichi', 'Maliniak', '1955-01-21','1989-09-12',150, 18),
   327  (10006, 'Anneke', 'Preusig', '1953-04-20','1989-06-02',150, 15),
   328  (10007, 'Tzvetan', 'Zielinski', '1957-05-23','1989-02-10',110, 6),
   329  (10008, 'Saniya', 'Kalloufi', '1958-02-19','1994-09-15',170, 10),
   330  (10009, 'Sumant', 'Peac', '1952-04-19','1985-02-18',110, 13),
   331  (10010, 'Duangkaew', 'Piveteau', '1963-06-01','1989-08-24',160, 10),
   332  (10011, 'Mary', 'Sluis', '1953-11-07','1990-01-22',120, 8),
   333  (10012, 'Patricio', 'Bridgland', '1960-10-04','1992-12-18',120, 7),
   334  (10013, 'Eberhardt', 'Terkki', '1963-06-07','1985-10-20',160, 17),
   335  (10014, 'Berni', 'Genin', '1956-02-12','1987-03-11',120, 15),
   336  (10015, 'Guoxiang', 'Nooteboom', '1959-08-19','1987-07-02',140, 8),
   337  (10016, 'Kazuhito', 'Cappelletti', '1961-05-02','1995-01-27',140, 2),
   338  (10017, 'Cristinel', 'Bouloucos', '1958-07-06','1993-08-03',170, 10),
   339  (10018, 'Kazuhide', 'Peha', '1954-06-19','1987-04-03',170, 2),
   340  (10019, 'Lillian', 'Haddadi', '1953-01-23','1999-04-30',170, 13),
   341  (10020, 'Mayuko', 'Warwick', '1952-12-24','1991-01-26',120, 1),
   342  (10021, 'Ramzi', 'Erde', '1960-02-20','1988-02-10',120, 9),
   343  (10022, 'Shahaf', 'Famili', '1952-07-08','1995-08-22',130, 10),
   344  (10023, 'Bojan', 'Montemayor', '1953-09-29','1989-12-17',120, 5),
   345  (10024, 'Suzette', 'Pettey', '1958-09-05','1997-05-19',130, 4),
   346  (10025, 'Prasadram', 'Heyers', '1958-10-31','1987-08-17',180, 8),
   347  (10026, 'Yongqiao', 'Berztiss', '1953-04-03','1995-03-20',170, 4),
   348  (10027, 'Divier', 'Reistad', '1962-07-10','1989-07-07',180, 10),
   349  (10028, 'Domenick', 'Tempesti', '1963-11-26','1991-10-22',110, 11),
   350  (10029, 'Otmar', 'Herbst', '1956-12-13','1985-11-20',110, 12),
   351  (10030, 'Elvis', 'Demeyer', '1958-07-14','1994-02-17',110, 1),
   352  (10031, 'Karsten', 'Joslin', '1959-01-27','1991-09-01',110, 10),
   353  (10032, 'Jeong', 'Reistad', '1960-08-09','1990-06-20',120, 19),
   354  (10033, 'Arif', 'Merlo', '1956-11-14','1987-03-18',120, 14),
   355  (10034, 'Bader', 'Swan', '1962-12-29','1988-09-21',130, 16),
   356  (10035, 'Alain', 'Chappelet', '1953-02-08','1988-09-05',130, 3),
   357  (10036, 'Adamantios', 'Portugali', '1959-08-10','1992-01-03',130, 14),
   358  (10037, 'Pradeep', 'Makrucki', '1963-07-22','1990-12-05',140, 12),
   359  (10038, 'Huan', 'Lortz', '1960-07-20','1989-09-20',140, 7),
   360  (10039, 'Alejandro', 'Brender', '1959-10-01','1988-01-19',110, 20),
   361  (10040, 'Weiyi', 'Meriste', '1959-09-13','1993-02-14',140, 17);
   362  
   363  select * from employees order by id;
   364  select * from `%!%pnorth%!%employees` order by id;
   365  select * from `%!%peast%!%employees` order by id;
   366  select * from `%!%pwest%!%employees` order by id;
   367  select * from `%!%pcentral%!%employees` order by id;
   368  
   369  delete from employees where id > 10030;
   370  select * from employees order by id;
   371  select * from `%!%pnorth%!%employees` order by id;
   372  select * from `%!%peast%!%employees` order by id;
   373  select * from `%!%pwest%!%employees` order by id;
   374  select * from `%!%pcentral%!%employees` order by id;
   375  
   376  drop table employees;
   377  
   378  
   379  -- 6.test List COLUMNS Partition
   380  drop table if exists customers;
   381  CREATE TABLE customers (
   382  first_name VARCHAR(25),
   383  last_name VARCHAR(25),
   384  city VARCHAR(15),
   385  renewal DATE
   386  )
   387  PARTITION BY LIST COLUMNS(renewal) (
   388      PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03', '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
   389      PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10', '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
   390      PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17', '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
   391      PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24', '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
   392  );
   393  
   394  
   395  INSERT INTO customers VALUES
   396  ('Georgi', 'Facello', 'NEW YORK', '2010-02-01'),
   397  ('Bezalel', 'Simmel', 'San Francisco', '2010-02-08'),
   398  ('Parto', 'Bamford', 'NEW YORK', '2010-02-15'),
   399  ('Chirstian', 'Koblick', 'NEW YORK', '2010-02-22'),
   400  ('Kyoichi', 'Maliniak', 'NEW YORK', '2010-02-02'),
   401  ('Anneke', 'Preusig', 'BOSTON', '2010-02-09'),
   402  ('Tzvetan', 'Zielinski', 'BOSTON', '2010-02-16'),
   403  ('Saniya', 'Kalloufi', 'NEW YORK', '2010-02-23'),
   404  ('Sumant', 'Peac', 'Houston', '2010-02-03'),
   405  ('Duangkaew', 'Piveteau', 'NEW YORK', '2010-02-10'),
   406  ('Mary', 'Sluis', 'BOSTON', '2010-02-17'),
   407  ('Patricio', 'Bridgland', 'NEW YORK', '2010-02-24'),
   408  ('Eberhardt', 'Terkki', 'San Francisco', '2010-02-04'),
   409  ('Berni', 'Genin', 'San Francisco', '2010-02-11'),
   410  ('Guoxiang', 'Nooteboom', 'Houston', '2010-02-18'),
   411  ('Kazuhito', 'Cappelletti', 'San Francisco', '2010-02-25'),
   412  ('Cristinel', 'Bouloucos', 'San Francisco', '2010-02-05'),
   413  ('Kazuhide', 'Peha', 'Houston', '2010-02-12'),
   414  ('Lillian', 'Haddadi', 'San Francisco', '2010-02-19'),
   415  ('Mayuko', 'Warwick', 'DALLAS', '2010-02-26'),
   416  ('Ramzi', 'Erde', 'DALLAS', '2010-02-06'),
   417  ('Shahaf', 'Famili', 'San Francisco', '2010-02-13'),
   418  ('Bojan', 'Montemayor', 'DALLAS', '2010-02-20'),
   419  ('Suzette', 'Pettey', 'DALLAS', '2010-02-27'),
   420  ('Prasadram', 'Heyers', 'CHICAGO', '2010-02-07'),
   421  ('Yongqiao', 'Berztiss', 'San Francisco', '2010-02-14'),
   422  ('Divier', 'Reistad', 'CHICAGO', '2010-02-21'),
   423  ('Domenick', 'Tempesti', 'CHICAGO', '2010-02-28'),
   424  ('Otmar', 'Herbst', 'Houston', '2010-02-01'),
   425  ('Elvis', 'Demeyer', 'BOSTON', '2010-02-02'),
   426  ('Karsten', 'Joslin', 'BOSTON', '2010-02-03'),
   427  ('Jeong', 'Reistad', 'San Francisco', '2010-02-04'),
   428  ('Arif', 'Merlo', 'San Francisco', '2010-02-05'),
   429  ('Bader', 'Swan', 'San Francisco', '2010-02-06'),
   430  ('Alain', 'Chappelet', 'Houston', '2010-02-07'),
   431  ('Adamantios', 'Portugali', 'BOSTON', '2010-02-15'),
   432  ('Pradeep', 'Makrucki', 'San Francisco', '2010-02-16'),
   433  ('Huan', 'Lortz', 'BOSTON', '2010-02-17'),
   434  ('Alejandro',   'Brender', 'DALLAS', '2010-02-18'),
   435  ('Weiyi', 'Meriste', 'San Francisco', '2010-02-19');
   436  
   437  select * from customers order by first_name;
   438  select * from `%!%pweek_1%!%customers` order by first_name;
   439  select * from `%!%pweek_2%!%customers` order by first_name;
   440  select * from `%!%pweek_3%!%customers` order by first_name;
   441  select * from `%!%pweek_4%!%customers` order by first_name;
   442  
   443  delete from customers where city = 'NEW YORK';
   444  select * from customers order by first_name;
   445  select * from `%!%pweek_1%!%customers` order by first_name;
   446  select * from `%!%pweek_2%!%customers` order by first_name;
   447  select * from `%!%pweek_3%!%customers` order by first_name;
   448  select * from `%!%pweek_4%!%customers` order by first_name;
   449  
   450  drop table customers;