By plogi in mysql questions

select n,n and 3,n & 3, n mod 4, n and false,n and true
from (select 0 n union select 1 union select 2 union
select 3 union select 4 union select 5 union
select 6 union select 7 union select null) v1;

Hints:

The subquery v1 returns column n with the rows 0,1,2,3,4,5,6,7,null.

“&” is the binary and-operation. “MOD” is the modulo (remainder) calculation.

Mysql treats 0 as false and anything else as true.

The constants TRUE and FALSE have the values 1 and 0.

Tags: learning, mysql, question

By plogi in general
Hi again!

The query below uses quite a bit of strange looking SQL syntax:

select all
count(1) minus
union all
select 1 only
order by 1 limit 1 ;
+-------+
| minus |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)

The “ALL” in the “SELECT ALL” is actually the default – so you hardly ever see it.

“COUNT(1)” is mostly used with Oracle, where it may have a performance benefit over “COUNT(*)”. The result however is the same than “COUNT(*)”.

The “minus” and “only” are column aliases.

Last but not least, the “ORDER BY 1″ means, that we order by the first column.

Below the same slightly more readable:

select
count(*) as cnt
union all
select 1
order by cnt limit 1 ;

By plogi in general
Hello!

The following query does the same than yesterday’s, featuring another syntax which isn’t used that often.

MariaDB [tt]> select * from vending_price
where (price,product_id) = any (select max(price),product_id
from vending_price
group by product_id);

The “ANY/ALL” syntax can be very intuitive for some queries… Unluckliy not for this example %)

By plogi in general
Hi again!

Continuing the example from yesterday, the following query shows the the real power of that syntax:

MariaDB [tt]> select * from vending_price where (price,product_id) in
(select max(price),product_id
from vending_price group by product_id);

By plogi in general
Hi!

There are some useful SQL structures, which are not so commonly used. For example I like the following syntax:

select * from vending_price where (product_id,valid_from) = (0,'2010-01-09');

It is basically the same than the following, but more compact and easier to read.

select * from vending_price where product_id = 0 and valid_from = '2010-01-09';

By plogi in general
Hi!

I won’t be able to post questions for a while…

Lots of things to do and everybody else is on holiday.

And then soon I’ll be on holiday :)

CU!

By plogi in mysql questions
1) where a in (1,2,3)

2) where a = 1 and a = 2 and a = 3

3) where b > 10 and b < 20

4) where a = 1 or a = 2 or a = 3

5) where b between 10 and 20

6) where b >= 10 and b < = 20

7) where b >= 10 and b < 20

Which of the following are equivalent?

(Find all correct answers)

a) 3 and 5

b) 5 and 6

c) 1 and 4

d) 5 and 7

e) 1 and 2

[Just for fun]

Tags: learning, mysql, question

By plogi in mysql questions
Extending on the scenario of Q80:

We want to read the correct historical price and sum up per product/day.

The price table is below…

drop table if exists vending_price ;
create table vending_price
(product_id int,valid_from date,valid_to date,price decimal (8,2),
primary key (product_id,valid_from,valid_to)) engine = innodb;
insert into vending_price
select v1.product_id,v2.d,adddate(v2.d,interval 3 day),rand() * 15 from
(select distinct product_id from vending_log) v1,
(select '2010-01-01' as d union select '2010-01-05' union
select '2010-01-09' union select '2010-01-13' union select '2010-01-17' ) v2;

[Just for fun]

Tags: learning, mysql, question

By plogi in mysql questions
Hello again!

Still about question 80… We have now 3 solutions to read values from the previous row: Subquery, user variables and with a helper-table + join. There is another feature (at least) which could be used, maybe a more procedural way? (hint, hint)

[Just for fun]

Tags: learning, mysql, question

By plogi in mysql questions
Hi!

It is really important to find as many different solutions as possible for a problem. The reason is, that sometimes a certain solution won’t work depending on the situation. So you need to have alternatives.

For question 80 I offered two possibilities, one with user-variables and one with a subquery. Are there more?

[Just for fun]

Tags: learning, mysql, question