mysql - find grandparents/grandchildren

Another interesting set of queries are parent, grandparent queries.

To find all the grand children nodes do this.

select b.id, b.parent_id, a.parent_id from kids as a, kids as b where a.id = b.parent_id and a.parent_id is not null;

To find all the grand parents with more than 2 grand children do this.

select b.id, b.parent_id, a.parent_id from kids as a, kids as b where a.id = b.parent_id and a.parent_id is not null group by a.parent_id having count(a.parent_id) > 2;

To produce the full family tree of a grandchild node do this

select b.id, b.parent_id, a.parent_id from kids as a right outer join kids as b on a.id = b.parent_id;

To find all nodes whos grandparent(and possibly parent) is unknown do this;

select b.id, b.parent_id, a.parent_id from kids as a right outer join kids as b on a.id = b.parent_id where a.parent_id is null;

Here is my full test sql.. try it out

create table kids (id integer, parent_id integer);
describe kids;
insert into kids values (1,null);
insert into kids values (3,1);
insert into kids values (4,2);
insert into kids values (5,2);
insert into kids values (6,4);
insert into kids values (7,2);
insert into kids values (8,7);
insert into kids values (9,7);
insert into kids values (10,7);
insert into kids values (11,3);
select * from kids;
select b.id, b.parent_id, a.parent_id from kids as a, kids as b where a.id = b.parent_id and a.parent_id is not null;

select b.id, b.parent_id, a.parent_id from kids as a, kids as b where a.id = b.parent_id and a.parent_id is not null group by a.parent_id having count(a.parent_id) > 2;

select b.id, b.parent_id, a.parent_id from kids as a right outer join kids as b on a.id = b.parent_id;

select b.id, b.parent_id, a.parent_id from kids as a right outer join kids as b on a.id = b.parent_id where a.parent_id is null;

My sql -- finding duplicates in a column

Here is how to find all entries that have duplicates in a mysql database

select email, count(email) from emails group by email having count(email) > 1;

So what is happening. To me it appears that the SQL boys have decided that selects can act in two independent ways:
1) "normal mode" - give me all the results
2) "aggregate mode" - give me groups of results represented by the first group member

It appears to me that this "aggregate mode" is triggered if either the "group by" or an aggregate function is present in the statement.

To be more specific;
1) When the "GROUP BY" is not present but an aggregate function is used then it is assumed that the data is one large group and only a single row is returned. This row appears to have the data from the first row of the normal select and the results of any aggregate function applied access the entire normal select results.
2) However if the "GROUP BY" is present then the functions operate on the individual groups. The resulting row(s) will use the normal selects first row for the normal data in the group and will apply any functions across the entire group.

This statement here will demonstrate the second rule in action more clearly.

select email, count(email), num, sum(num), avg(num), min(num), max(num) from emails group by email;

Here is the full test sql which brought me to my conclusions;

create table emails (email text, num integer);
describe emails;
insert into emails values("a@a.com", 1);
insert into emails values("b@a.com", 2);
insert into emails values("b@a.com", 3);
insert into emails values("c@a.com", 4);
insert into emails values("d@a.com", 5);
insert into emails values("d@a.com", 6);
insert into emails values("d@a.com", 7);
insert into emails values("b@a.com", 8);
select * from emails;
select distinct email from emails;
select email from emails group by email;
select email, count(email) from emails;
select email, count(email), num, sum(num), avg(num), min(num), max(num) from emails group by email;
select email, count(email) from emails group by email having count(email) > 1;

There are several functions that operate on the "group"
sum(), count(), avg(), min(), max(), etc

Refer here for more info;
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Mysql grant syntax

As unbelievable as it is I forget the grant syntax way to easily...

GRANT ALL ON database.* TO username@'localhost' IDENTIFIED BY 'password';

die hard 3 jug 5 jug problem

Question:
You have two jugs a 3 and 5 liter jug how do you get 4 liters in a jug.

Answer:
This is one of the more basic questions, but it still appears in job interviews. It is basically impossible not to get the answer correct.

1) First fill the 5 liter jug.
2) Poor water from the 5 liter into the 3 liter one until its full. That leaves 2 liters in the 5 liter,
3) Empty the 3 liter and transfer the 2 liters into it. This leaves 1 liter of space in the 3 liter jug.
4) Then fill the 5 liter jug
5) Then poor 1 liter out of the 5 liter into the 1 liter of empty space in the 3 liter jug.

Done the 5 liter jug has 4 liters in it

c++: Basic Traits

Traits:
In C++ "traits" are basically a group of template classes that provide miscellaneous information about another type or data structure.

'Think of a trait as a small object whose main purpose is to carry information used by another object or algorithm to determine "policy" or "implementation details".'
- Bjarne Stroustrup

Traits are very common in C++. One of the more common ones is the string class its self. The string class uses traits to provide information about internationalization and character encoding. Often they are visible in the code and debugger output as a template parameter that has a default initialization via a second template.

In other cases the use of traits is more normal as in the the class "std::numeric_limits" and the other members of the limits.h header.

Boost also offers many interesting and helpful traits classes as an example here is the "is_void" traits class.

template< typename T > 
struct is_void{ 
  static const bool value = false;
};

template<> 
struct is_void< void >{ 
  static const bool value = true; 
};

As you can see this trait template's sole purpose to is provide information about what the input parameter type was. This is only really useful in a parts of the code which the programmer doesn't know what the input type was: Hence inside another template.

For more info refer to:
http://www.cantrip.org/traits.html
http://www.cplusplus.com/reference/std/limits/numeric_limits/

The truth about what movitates people

http://www.youtube.com/watch?v=u6XAPnuFjJc

Three coworkers sharing average salaries

Question:
Three coworkers would like to know their average salary. How can they do it, without disclosing their own salaries?

The key is to scramble the sum with unknown/unshared data. The scramble must be reversible and when reversing it must not effect the sum.

Answer on the web:
So each person adds a random number(Rn) plus there salary(Sn) to the sum(Sum) and hands it to the next one. And then in the second round they each deduce there random number

(round 1)
Sum0 = S1 + R1
Sum1 = S1 + S2 + R1 + R2
Sum2 = S1 + S2 + S3 + R1 + R2 + R3

(round 2)
Sum3 = S1 + S2 + S3 + R2 + R3
Sum4 = S1 + S2 + S3 + R3
Sum5 = S1 + S2 + S3

And final they divide by the number of people
Average(6) = (S1 + S2 + S3)/3

Here is the proof:
For person 1

Starts with:
R1
S1
Sum0 = S1 + R1
Sum2 = S1 + S2 + S3 + R1 + R2 + R3
Sum3 = S1 + S2 + S3 + R2 + R3
Sum5 = S1 + S2 + S3

Can calc
R2 + R3 = Sum3 - Sum5

So he finally knows
R1
S1
R2 + R3
S2 + S3 

For person 2

He starts with:
R2
S2
Sum0 = S1 + R1
Sum1 = S1 + S2 + R1 + R2
Sum3 = S1 + S2 + S3 + R2 + R3
Sum4 = S1 + S2 + S3 + R3
Sum5 = S1 + S2 + S3

He can calc:
R3 = Sum4 - Sum5

He finally knows knows
R2
R3
S2
S1 + R1
S1 + S3

For person 3

He Starts with:
R3
S3
Sum1 = S1 + S2 + R1 + R2
Sum2 = S1 + S2 + S3 + R1 + R2 + R3
Sum4 = S1 + S2 + S3 + R3
Sum5 = S1 + S2 + S3

He can calc:
S1 + S2 = Sum5 - S2
R1 + R2 = Sum2 - S1 + S2 

He finally knows:
R3
S3
S1 + S2
R1 + R2

Here is a much stronger answer
It requires an 2 or more arbitrators in the system, At the end of each Sum stage the summer chooses a random arbitrator who then adds his own random value to the sum.
Once Sum5 stage is complete the arbitrators each in turn recieve the sum and deduct out the total of the random numbers they added in. As a result the computation becomes

(round 1)
Sum0 = S1 + R1 + A0
Sum1 = S1 + S2 + R1 + R2 + A0 + A1
Sum2 = S1 + S2 + S3 + R1 + R2 + R3 + A0 + A1 + A2

(round 2)
Sum3 = S1 + S2 + S3 + R2 + R3 + A0 + A1 + A2 + A3
Sum4 = S1 + S2 + S3 + R3 + A0 + A1 + A2 + A3 + A4
Sum5 = S1 + S2 + S3 + A0 + A1 + A2 + A3 + A4 + A5

Sum6 =  S1 + S2 + S3 + A0 + A2 + A4
Sum7 =  S1 + S2 + S3

Hence its much more harder to break