functions.test

drop table if exists tbl1,tbl2;
create table tbl1 (col_1a tinyint, col_1b smallint, col_1c int, col_1d bigint, col_1e char(10) not null);
insert into tbl1 values (0,1,1,7,"a");
insert into tbl1 values (0,1,2,8,"b");
insert into tbl1 values (0,1,3,9,"c");
insert into tbl1 values (0,1,4,10,"D");
insert into tbl1 values (0,1,5,11,"a");
insert into tbl1 values (0,1,6,12,"c");
select sum(col_1a),count(col_1b),avg(col_1c),min(col_1d),max(col_1d) from tbl1;
select sum(col_1d) as s1,count(col_1d) as c2,avg(col_1d) as a3,min(col_1d) as m4,max(col_1d) as m5 from tbl1 group by col_1e order by s1 desc;
select sum(col_1d) as c1 from tbl1 where col_1d < 13 group by col_1e order by c1;
select sum(col_1d) from tbl1 where col_1d > 10;
select sum(col_1c)+count(col_1c)-avg(col_1c)*min(col_1c)/max(col_1c)%min(col_1c)+max(col_1c) as func from tbl1 group by col_1c;
CREATE TABLE t1 (spID int,userID int,score smallint);
INSERT INTO t1 VALUES (1,1,1);
INSERT INTO t1 VALUES (2,2,2);
INSERT INTO t1 VALUES (2,1,4);
INSERT INTO t1 VALUES (3,3,3);
INSERT INTO t1 VALUES (1,1,5);
INSERT INTO t1 VALUES (4,6,10);
INSERT INTO t1 VALUES (5,11,99);
select * from t1 where CAST(spID AS CHAR)='1';
select CAST(userID AS CHAR) userid_cast from t1 where CAST(spID AS CHAR)='1';
select spID+score from t1;
drop table if exists t1;
create table t1(a date);
insert into t1 values('2021-12-23');
insert into t1 values('2021-12-24');
select year(a) from t1;
select year(a) from t1;
drop table if exists t1;
create table t1(a int ,b float);
insert into t1 values(1,0.5);
insert into t1 values(2,0.499);
insert into t1 values(3,0.501);
insert into t1 values(4,20.5);
insert into t1 values(5,20.499);
insert into t1 values(6,13.500);
insert into t1 values(7,-0.500);
insert into t1 values(8,-0.499);
insert into t1 values(9,-0.501);
insert into t1 values(10,-20.499);
insert into t1 values(11,-20.500);
insert into t1 values(12,-13.500);
select a,round(b) from t1;
select a,round(b,-1) from t1;
select round(a*b) from t1;
select a,floor(b) from t1;
select sum(round(b)) from t1;
select sum(floor(b)) from t1;
select a,sum(floor(b)) from t1 group by a order by a;
select a,floor(b) as floorb from t1 order by floorb desc;
select a,avg(round(b)) as roundb from t1 where a>3 group by a order by roundb;
select a,avg(round(b)) as roundb from t1 where a in (3,5,7) group by a order by roundb;
select a,round(b,2),max(b) from t1 group by b order by a;