Database interview questions-competition data query and question Data Query

Database interview questions-competition data query and question Data Query

This interview is quite special. I gave a test paper and asked to give a verbal answer. To be honest, I really like it.

The last question is the database question. The table is as follows:

YY initialization statement

mysql> create table fmatch( mdate date, team varchar(20),res enum('WIN','LOSE'));mysql> insert into fmatch values('2010-10-19','76ers','WIN');Query OK, 1 row affected (0.07 sec)mysql> insert into fmatch values('2010-10-20','76ers','LOSE');Query OK, 1 row affected (0.07 sec)mysql> insert into fmatch values('2010-10-20','NET','WIN');Query OK, 1 row affected (0.04 sec)mysql> insert into fmatch values('2010-10-21','NET','WIN');Query OK, 1 row affected (0.04 sec)

Below are the issues

1. Identify the data of the first day and the last day

That is

Train of Thought: max, min

Answer:

select * from fmatch where mdate in (select max(mdate) from fmatch) or  mdate in (select min(mdate) from fmatch);

This is a simple question, but if you have better answers, please share them!

The second problem is more difficult.

2. Count the winning and losing situations of each team

I didn't think about it on the spot. I told the interviewer that an SQL statement would not solve the problem if I wanted to use the if statement. Ah, that's stupid.

I checked it online and found a very close answer.

 select a.team ,a.win,b.lose from (select count(*) win, team from fmatch where res='WIN' group by team ) a left join  (select count(*) lose, team from fmatch where res='LOSE' group by team ) b on a.team=b.team;

However, there is still a flaw. Sometimes the data does not show NULL instead of 0.

Also, during the interview, the interviewer gave a prompt that the rows and columns were changed. Do you really need to use the rows and columns change ??

How can this problem be solved ??? Thank you!

======================================

User ArkStone gives the answer to the second question. The test is available!

select team, count(res = 'WIN' or null) as win, count(res = 'LOSE' or null) as lose from fmatch group by team;

Explanation: res = 'win' or null

If the res column value is 'win', the column value is 1; otherwise, it is null.

 

So

For 76ers, win = count (1, null) = 1; lose = count (null, 1) = 1;

For Net, win = count (1, 1) = 2; lose = count (null, null) = 0;

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.