mysql 5.0/5.1 questions for learning purposes
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.
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 ;
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 %)
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);
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';
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!
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]
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]
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]
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]