Oracle, typical SQL tasks. Guaranteed choice

On the nose elections, which means that today we will choose the governor. Or rather, to appoint a barrier to the passage of candidates in the next round. But first you need to decide on the terms.

What is a guaranteed choice in SQL? Suppose that the condition of a query to a table compares any field with some variable. Depending on the value of this variable, the query may return rows from the table, or it may not return them at all. If the variable falls in such a value that the rows are not returned from the table, then for this case you need to specially generate a predefined left result. That is, in any case, the general request should be guaranteed something and return. The term itself is taken from here . However, the task is complicated by the fact (and maybe vice versa, simplified) that instead of one simple cell with a value, we need to be guaranteed to return the full string.
')
I cite the data of the Central Election Commission. The first round of voting ended with such results.

ID	The name of the candidate	Profession	Number of votes
one	The Incorruptible Amoral Chistorukovich	prosecutor	9867
2	Effective Budget Osvoilovich	businessman	8650
3	Truthful Chadadut Post	newspaper editor	745
four	Lucifer the good-looking Feoktistovna	abbess	234
five	Khrensgory Ktotakoy Niktoneznaevich	rural school teacher	3

create table election as with t (id, name, profession, votes) as ( select 1, '  ', '', 9867 from dual union all select 2, '  ', '', 8650 from dual union all select 3, '  ', ' ', 745 from dual union all select 4, '   ', '', 234 from dual union all select 5, '  ', '  ', 3 from dual ) select * from t; alter table election add primary key (id); 

The initial request defining the exit of candidates to the next round is extremely simple:

 select * from election where votes > :bound 

Suppose the entry barrier is 8,000 votes. With this number with: bound, we get

ID	NAME	PROFESSION	VOTES
one	The Incorruptible Amoral Chistorukovich	prosecutor	9867
2	Effective Budget Osvoilovich	businessman	8650

But what if the entry barrier is 10,000, that is, more than the maximum number of votes? Then it is obvious that none of the aforementioned candidates go to the next round. In this case, the dictatorship is established and the cat Colonel automatically becomes the governor of the region. Here are some of the ways to assign it:

Method 1. UNION ALL tables with the same aggregated

 with t as ( select * from election where votes > :bound ) select id, name, profession, votes from t union all select 0, '', '', null from t having count(*) = 0 order by votes desc 

Method 2 UNION ALL tables with DUAL

 with t as ( select * from election where votes > :bound ) select id, name, profession, votes from t union all select 0, '', '', null from dual where not exists (select null from t) order by votes desc 

Method 3. LEFT JOIN tables with DUAL

 select nvl(e.id, 0) id, nvl2(e.id, e.name, '') name, nvl2(e.id, e.profession, '') profession, e.votes from dual d left join election e on e.votes > :bound order by e.votes desc 

for cases when the table does not have a unique NOT NULL field

 select nvl2(e.rowid, e.id, 0) id, nvl2(e.rowid, e.name, '') name, nvl2(e.rowid, e.profession, '') profession, e.votes from dual d left join election e on e.votes > :bound order by e.votes desc 

Method 4. Modelka with a cat.

 select id, name, profession, votes from election where votes > :bound model dimension by (rownum rn) measures (id, name, profession, votes) rules ( name[1] = nvl2(id[1], name[1], ''), profession[1] = nvl2(id[1], profession[1], ''), id[1] = nvl(id[1], 0) ) order by votes desc 

Below is a guaranteed result with a bin with too large a barrier of 10,000

ID	NAME	PROFESSION	VOTES
0	Colonel	cat

It is clear that if you set the initial bar of 8000, then these requests will also work correctly.

That's all for now. All matches of characters' names with real people are considered random.

The solution of some other typical SQL tasks can be found here and here .

Until new meetings.