1． Get countries where both people speak Chinese and people speak English reach 1% of the population

MySQL8:

    select countrycode from world.countrylanguage
    where language in ('Chinese', 'English') and percentage>=1
    group by countrycode
    having count(*)>=2;

SPL script:

	A
1	=connect("mysql")
2	=A1.query@x("select  * from world.countrylanguage where   percentage>=1")
3	=A2.group(CountryCode)
4	=A3.select(~.(Language).contain("Chinese","English"))
5	=A4.(CountryCode)

  A4: Get groups containing both Chinese and English;

  

 

2. Here is a table of (id,v) structure where data is ordered by id in ascending order. Task: When v values are 23,7,11 respectively in continuous records, find v value in their next record

MySQL8:

    with t(id,v) as (select 1,3 union all select 2,15
    union all select 3,23 union all select 4,7
    union all select 5,11 union all select 6,19
    union all select 7,23 union all select 8,7
    union all select 9,6),
    s(v) as (select '23,7,11'),
    t1(v) as (select group_concat(v order by id) from t),
    t2(p1,p2,p3,next) as (
    select @p1:=locate(s.v,t1.v), @p2:=if(@p1>0,@p1+char_length(s.v)+1,null),
    @p3:=locate(',',t1.v,@p2),@s:=substr(t1.v,@p2,@p3-@p2)
    from s,t1)
    select next from t2;

  Note: This is value capture with string processing. In variable t, id is a sequence number and v is a value; in variable s, v is a sequence of values to be queried.

SPL script:

	A
1	=connect("mysql")
2	=A1.query@x("with   t(id,v) as (select 1,3 union all select 2,15 union all select 3,23 union all  select 4,7 union all select 5,11 union all   select 6,19 union all select 7,23 union all select 8,7 union all select 9,6)   select * from t order by id")
3	[23,7,11]
4	=A2.(v)
5	=A4.pos@c(A3)
6	=if(A5>0,A4.m(A5+A3.len()))

  A3: A sequence of values to queried.

  A5: Get the start position from A4 for A3’s members.

  

 

3. In a table of (id,used) structure, id values are continuous, it is unused when used value is 0 and it is used when used value is 1. Task: List starts and ends of unused intervals.

MySQL:

    with t(id,used) as (select 1,1 union all select 2,1
    union all select 3,0 union all select 4,1
    union all select 5,0 union all select 6,0
    union all select 7,1 union all select 8,1
    union all select 9,0 union all select 10,0
    union all select 10,0 union all select 11,0),
    first as (select a.id
    from t a left join t b on a.id=b.id+1
    where a.used=0 and (b.id is null or b.used=1)),
    t2 as (select first.id firstUnused, min(c.id) minUsed, max(d.id) maxUnused
    from first 
    left join t c on first.id<c.id and c.used=1
    left join t d on first.id<d.id and d.used=0
    group by firstUnused)
    select firstUnused, if(minUsed is null, ifnull(maxUnused,firstUnused), minUsed-1) lastUnused
    from t2;

  Note: The SQL solution doesn’t use a window function to compare a used value with its next one and put records with same used values to one group, it uses a join and a left join instead. first gets the starts all unused intervals. t2, for each start id, gets the smallest used id that is greater than the id and the largest unused if that is greater than the id.

SPL script:

	A
1	=connect("mysql")
2	=A1.query@x("with   t(id,used) as (select 1,1 union all select 2,1 union all select 3,0 union all    select 4,1 union all select 5,0 union   all select 6,0 union all select 7,1  union all select 8,1 union all select 9,0 union   all select 10,0 union all select 10,0 union all select 11,0) select * from t   order by id")
3	=create(firstUnused,lastUnused)
4	>A2.run(if(used==0&&used!=used[-1],a=id),     if(used==0&&used!=used[1],A3.insert(0,a,id)))

  A3: When a row’s used value is 0 and it isn’t the same with the previous used value, the row’s id is a start. When a row’s used value is 0 and it isn’t the same with the next used value, the row’s id is an end and needs to be inserted into the new table sequence.

  

 

4. List names and population of European and African cities where population is over 2 million in two column groups (in descending order).

MySQL:

    with t as (select t1.name,t1.population,t2.continent,
    rank()over(partition by t2.continent order by t1.population desc) rk
    from world.city t1 join world.country t2 on t1.countrycode=t2.code
    where t2.continent in ('Europe','Africa') and t1.population>=2000000
    ),
    m(rk) as (select distinct rk from t)
    select t1.name `Europe City`, t1.Population, t2.name `Africa City`, t2.Population
    from m
    left join (select * from t where continent='Europe') t1 using(rk)
    left join (select * from t where continent='Africa') t2 using (rk);

SPL script:

	A
1	=connect("mysql")
2	=A1.query@x("select   t1.name,t1.population,t2.continent from world.city t1 join world.country t2   on  t1.countrycode=t2.code where   t2.continent in ('Europe','Africa') and  t1.population>=2000000 order by   t1.population desc")
3	=A2.select(continent:"Europe")
4	=A2.select(continent:"Africa")
5	=create('Europe  City',population,'Africa City', population)
6	=A5.paste(A3.(name),A3.(population),A4.(name),A4.(population))

  A6: Paste sequence of values to corresponding columns.

  

 

5. Here is a score table of (Student,Math,Chinese,English,Physics,Chemistry,Information) structure. Task: Suppose there are subject where Maliang gets scores less than 90, then get scores of the all students on these subjects.

MySQL:

    create temporary table 
    scores(Student varchar(20),Math int,Chinese int,English int,
    Physics int,Chemistry int,Information int);
    insert into scores 
    select 'Lili', 93,99,100,88,92,95 
    union all select 'Sunqiang', 100,99,97,100,85,96 
    union all select 'Zhangjun', 95,92,94,90,93,91
    union all select 'Maliang', 97,89,92,99,98,88;

    select @m:=concat(if(Math<90, 'Math,', ''),
    if(Chinese<90, 'Chinese,', ''),
    if(English<90, 'English,', ''),
    if(Physics<90, 'Physics,', ''),
    if(Chemistry<90, 'Chemistry,', ''),
    if(Information<90, 'Information,', ''))
    from scores
    where student='Maliang';

    set @s:=left(@m, length(@m)-1);
    set @sql:=concat('select Student,', @s, 'from scores');
    prepare stmt from @sql;
    execute stmt;
    deallocate prepare stmt;
    drop table scores;

SPL script:

	A
1	=connect("mysql")
2	=A1.query@x("with  t(Student,Math,Chinese,English,Physics, Chemistry,Information)   as (select'Lili', 93,99,100,88,92,95 union all select'Sunqiang',   100,99,97,100,85,96  union all select'Zhangjun',   95,92,94,90,93,91 union all select'Maliang',  97,89,92,99,98,88) select * from t")
3	=A2.select@1(Student:"Maliang")
4	=A3.array().pselect@a(#>1&&~<90)
5	=A2.fname()(A4).concat@c()
6	=A2.new(Student,${A5})

  A4: Convert Maliang’s record to an array and find column numbers holding subjects for which Maliang get scores less than 90.

  A5: Get names of A4’s columns from A2 and join them up by commas.

  A6: Create a new table sequence consisting of Student and the selected columns according to A2.

  

  

 

6. List sales amounts in March, 2016 for all provinces where the first three provinces are Beijing, Shanghai and Guangdong.

MySQL:

    select *
    from detail
    where yearmonth=201603
    order by case when province='Beijing' then 1 
    when province='Shanghai' then 2
    when province='Guangdong' then 3 else 4 end;

SPL script:

	A
1	=connect("mysql")
2	=A1.query@x("select  * from detail where yearmonth=201603")
3	=["Beijing","Shanghai","Guangdong"]
4	=A2.align@s(A3,province)

  A4: Align province values in A2’s records with A3’s sequence and append the other provinces to the end.

  

 

7. Get countries where there isn’t a city whose population is over 1000.

MySQL:

    select t1.code,t1.name
    from world.country t1
    left join (select * from world.city where population>=1000) t2
    on t1.code=t2.countrycode
    where t2.countrycode is null;

SPL script:

	A
1	=connect("mysql")
2	=A1.query("select  code,name from world.country")
3	=A1.query@xi("select  distinct countrycode from world.city where   population>=1000")
4	=A2.switch@d(code,A3:countrycode)

  A4: Get records where code value doesn’t exist in A3.