Three interesting problems on SQL knowledge

Just a few hours ago, after a careful study of the comments on this topic, I was somewhat surprised that many people do not really like to use the HAVING terms and it looks like other DBMS features. Frankly speaking, I really liked the discussion of the simplest and not working request, as a result of which many people learned a lot of new and informative (this is my personal opinion). So I decided to do this — to bring three of my most beloved tasks in knowledge of SQL (MySQL) to the public.

These tasks are absolutely real, and in the distant bright past they did take place, and were successfully solved by the victim of a certain amount of working time and nerve cells. I am 100% sure that they are relevant to this day, and will be relevant for a long time. Difficult to call them, but very simple too (depending on the level of preparation of course).
')


So ... Honestly, I am very interested to find out who decides how. Of course, I have solutions, but nevertheless I admit that my vested interest was to find out how their SQL pros would decide, which I think have not yet died out. Yes, and everyone will be interested, I think ... In a word - I would very much like kamenty to steer. :-)

Condition.

Gallery of pictures consists of 3 simple tables. There are a number of categories, each of which includes a certain number of photo albums, which, in turn, include clouds of photos from the “left-handed” cycle. It just so happened that the existing structure cannot be changed (yes, it would not be interesting).

--
CREATE TABLE `photo_category` (
`id` int (11) NOT NULL AUTO_INCREMENT,
`title` varchar (255), --
`is_published` tinyint(1), -- 1/0 - /
`ordi` int (11), -- , 1 ...
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

--
CREATE TABLE `photo_gallery` (
`id` int (11) NOT NULL AUTO_INCREMENT,
`c_id` int (11), -- ID
`title` varchar (255), --
`is_published` tinyint(1), -- 1/0 - /
`ordi` int (11), -- , 1 ...
PRIMARY KEY (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

--
CREATE TABLE `photo_image` (
`id` int (11) NOT NULL AUTO_INCREMENT,
`g_id` int (11), -- ID
`title` varchar (255), -- ( )
`is_published` tinyint(1), -- 1/0 - /
`is_main_foto` tinyint(1), -- 1/0 - /.
`ordi` int (11) DEFAULT NULL , -- , 1 ...
PRIMARY KEY (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

* This source code was highlighted with Source Code Highlighter .

Conditions applicable to all tasks:

1) by the is_published flag, the object itself and all incoming objects into it should be excluded. That is, if the category is_published = 0, then all the albums and, accordingly, all their pictures should be excluded (and they don’t already have published).

2) All sorting conditions must be according to the keys ordi.

3) For all tasks you can not show empty galleries and categories, that is, those categories in which there are no galleries and those galleries in which there are no categories.

4) In the gallery, only one photo can be marked as the main or none. Even if such a photo is_published = 0, it is still shown if there is another photo with is_published = 1 besides it

5) I will also add - combat conditions, that is, all requests should be as simple as possible and work as quickly as possible, the number of requests should be minimized.

Yes, I deliberately excluded little meaningful fields and indices from the tables, so as not to be distracted by trifles :-)

And now the tasks themselves (by difficulty in ascending order):

First task

Given category ID. It is necessary to write a request (one!) That would receive all the galleries in this category, for each of which received the ID of the main photo, and if there is none, then the ID is included in the category (anyway, if only there was a photo).

Task Two

Given ID foty. If you want - the same gallery ID. It is required with a minimum of effort to determine the next / previous photo in order of ordi. (I remind you that it is impossible to make a decision here only by ordi since the next / previous one can be is_published = 0, so you need to take the closest one which is_published = 1). The task is solved by 2 requests, I am sure that it can be solved by one (without UNION), but I did not succeed. If anyone succeeds in that respect and respect. :-)

Third task

This is the tin. A certain number of N is given. It is required to display a list of categories, and the number of recent albums in them, and for each category this number should not be more than N and sorted in descending order by ordi. That is, let's say 3 categories, in the 1st 10 photos, in the second 25, and in the third only three. It is necessary that the output be for the first 5 of the last (with the largest ordi sorted in descending order), for the second 5 (similarly) and for the third - 3 (similarly). Plus the condition of the first task, that is, you still need the main photo for the gallery or some other one.

I have solutions for all three tasks and I will definitely publish them but ... later and with explanations of what, how and why. :-) And yet - for many, at first glance, this will seem just incredibly complex - in fact, the solutions are quite simple, although they include some “rarely used” (according to some) SQL constructions of the same renowned HAVING type.

DBMS - MySQL 5. Do without stored procedures and functions. Good luck! :-)

UPD: Solutions here . There it was typed for the whole topic, which I did.