DROP DATABASE IF EXISTS mcs77_db;
CREATE DATABASE mcs77_db;
USE mcs77_db;
CREATE TABLE t1
(
t1_tinyint TINYINT DEFAULT 0,
t1_int INT DEFAULT NULL,
t1_bigint BIGINT,
t1_decimal DECIMAL(5,2),
t1_text TEXT,
t1_char CHAR(5),
t1_varchar VARCHAR(255) DEFAULT 'hello world!',
t1_datetime DATETIME
)ENGINE=Columnstore;
INSERT INTO t1 VALUES();
INSERT INTO t1 VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO t1 VALUES(1, 123456, 987654321, 122.32, repeat('z', 20), 'aaa', repeat('a', 20), '1111-11-11 11:11:11');
INSERT INTO t1 VALUES(0, 1023456, 887654321, 222.32, repeat('y', 20), 'aaa', repeat('b', 20), '1111-11-11 11:11:11');
INSERT INTO t1 VALUES(1, -123456, -987654321, -122.32, repeat('z', 20), 'bbb', repeat('a', 20), '1111-11-11 11:11:11');
INSERT INTO t1 VALUES(0, -1023456, -887654321, -222.32, repeat('x', 20), 'bbb', repeat('b', 20), '1111-11-11 11:11:11');
SELECT * FROM t1;
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
0	NULL	NULL	NULL	NULL	NULL	hello world!	NULL
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
1	123456	987654321	122.32	zzzzzzzzzzzzzzzzzzzz	aaa	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
0	1023456	887654321	222.32	yyyyyyyyyyyyyyyyyyyy	aaa	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
1	-123456	-987654321	-122.32	zzzzzzzzzzzzzzzzzzzz	bbb	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
0	-1023456	-887654321	-222.32	xxxxxxxxxxxxxxxxxxxx	bbb	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
SELECT * FROM t1 WHERE t1_int IS NULL;
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
0	NULL	NULL	NULL	NULL	NULL	hello world!	NULL
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT * FROM t1 WHERE t1_tinyint = 0;
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
0	NULL	NULL	NULL	NULL	NULL	hello world!	NULL
0	1023456	887654321	222.32	yyyyyyyyyyyyyyyyyyyy	aaa	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
0	-1023456	-887654321	-222.32	xxxxxxxxxxxxxxxxxxxx	bbb	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
SELECT * FROM t1 WHERE t1_tinyint != 0;
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
1	123456	987654321	122.32	zzzzzzzzzzzzzzzzzzzz	aaa	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
1	-123456	-987654321	-122.32	zzzzzzzzzzzzzzzzzzzz	bbb	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
SELECT * FROM t1 WHERE t1_bigint > 0;
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
1	123456	987654321	122.32	zzzzzzzzzzzzzzzzzzzz	aaa	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
0	1023456	887654321	222.32	yyyyyyyyyyyyyyyyyyyy	aaa	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
SELECT * FROM t1 WHERE t1_bigint < 0;
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
1	-123456	-987654321	-122.32	zzzzzzzzzzzzzzzzzzzz	bbb	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
0	-1023456	-887654321	-222.32	xxxxxxxxxxxxxxxxxxxx	bbb	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
SELECT * FROM t1 WHERE t1_decimal >= -122.22;
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
1	123456	987654321	122.32	zzzzzzzzzzzzzzzzzzzz	aaa	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
0	1023456	887654321	222.32	yyyyyyyyyyyyyyyyyyyy	aaa	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
SELECT * FROM t1 WHERE t1_decimal <= -122.22;
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
1	-123456	-987654321	-122.32	zzzzzzzzzzzzzzzzzzzz	bbb	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
0	-1023456	-887654321	-222.32	xxxxxxxxxxxxxxxxxxxx	bbb	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
SELECT * FROM t1 WHERE t1_text LIKE '%x%';
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
0	-1023456	-887654321	-222.32	xxxxxxxxxxxxxxxxxxxx	bbb	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
SELECT * FROM t1 WHERE t1_datetime IN ('1111-11-11 11:11:10','1111-11-13 0:0:0');
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
SELECT * FROM t1 WHERE t1_datetime IN ('1111-11-11 11:11:11','1111-11-13 0:0:0');
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
1	123456	987654321	122.32	zzzzzzzzzzzzzzzzzzzz	aaa	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
0	1023456	887654321	222.32	yyyyyyyyyyyyyyyyyyyy	aaa	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
1	-123456	-987654321	-122.32	zzzzzzzzzzzzzzzzzzzz	bbb	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
0	-1023456	-887654321	-222.32	xxxxxxxxxxxxxxxxxxxx	bbb	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
SELECT * FROM t1 WHERE t1_datetime BETWEEN '111-11-10' AND '1111-11-12';
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
1	123456	987654321	122.32	zzzzzzzzzzzzzzzzzzzz	aaa	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
0	1023456	887654321	222.32	yyyyyyyyyyyyyyyyyyyy	aaa	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
1	-123456	-987654321	-122.32	zzzzzzzzzzzzzzzzzzzz	bbb	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
0	-1023456	-887654321	-222.32	xxxxxxxxxxxxxxxxxxxx	bbb	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
SELECT * FROM t1 WHERE t1_text LIKE '%x%' OR t1_varchar LIKE '%a%';
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
1	123456	987654321	122.32	zzzzzzzzzzzzzzzzzzzz	aaa	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
1	-123456	-987654321	-122.32	zzzzzzzzzzzzzzzzzzzz	bbb	aaaaaaaaaaaaaaaaaaaa	1111-11-11 11:11:11
0	-1023456	-887654321	-222.32	xxxxxxxxxxxxxxxxxxxx	bbb	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
SELECT * FROM t1 WHERE t1_text LIKE '%x%' AND t1_varchar LIKE '%b%';
t1_tinyint	t1_int	t1_bigint	t1_decimal	t1_text	t1_char	t1_varchar	t1_datetime
0	-1023456	-887654321	-222.32	xxxxxxxxxxxxxxxxxxxx	bbb	bbbbbbbbbbbbbbbbbbbb	1111-11-11 11:11:11
DROP DATABASE mcs77_db;