Which SQL query will display the most frequently occurring records?

Question

PostgreSQL

Hello. There is a test library database that I created myself. There are two tables: BOOKS and POSSESSION. The first stores information about books (title, author, year of publication, publisher and number of books in the library). In the second information about who, what downwards, when he took and handed over. The data model in the figure:

I need to find out the most popular author of the year. Actually here is the request I made:

SELECT AUTHOR
FROM POSSESSION
INNER JOIN BOOKS ON BOOK_ID = BOOKS.ID
WHERE TAKING_DATE > (CURRENT_DATE - INTERVAL '1 year')
GROUP BY AUTHOR
ORDER BY COUNT(AUTHOR) DESC
LIMIT 1;

However, I myself understand that if there are two such authors, then the request will not return the correct value. The conclusion suggests itself that some kind of subquery with the MAX aggregate function is needed, but due to the fact that I haven’t worked with SQL for a long time, I don’t understand how and where to insert it. Can you suggest?

Answer 1

select 
t1.AUTHOR,
t1.total
from
(
SELECT AUTHOR, count(*) as total
FROM POSSESSION
INNER JOIN BOOKS ON BOOK_ID = BOOKS.ID
WHERE TAKING_DATE > (CURRENT_DATE - INTERVAL '1 year')
GROUP BY AUTHOR
)  t1
where t1.total =
(
select
max(t2.total)
from
(
SELECT AUTHOR, count(*) as total
FROM POSSESSION
INNER JOIN BOOKS ON BOOK_ID = BOOKS.ID
WHERE TAKING_DATE > (CURRENT_DATE - INTERVAL '1 year')
GROUP BY AUTHOR
) t2
)

Answer 2

Will a client ping another client with address 10.45.18.194 and why?

Without a route on the router, it won't, because 10.45.18.194 is on a different subnet. 10.45.18.15 is in the 10.45.18.0/25 subnet, and 10.45.18.194 is in 10.45.18.128/25. Unless, of course, clients have a /25 mask.
Will.

Answer 3

Collected topology on GNS3:
On the first question:

Will a client ping another client with address 10.45.18.194 and why?

Yes, pings pass:

ClientA#ping 10.45.18.194 repeat 10

Type escape sequence to abort.
Sending 10, 100-byte ICMP Echos to 10.45.18.194, timeout is 2 seconds:
!!!!!!!!!!
Success rate is 100 percent (10/10), round-trip min/avg/max = 20/34/48 ms

Now the most interesting. 10.45.18.194 is not in Client A's routing table, so icmp requests before 10.45.18.194 are encapsulated in Ethernet frames with the ISP's router destination address (00ff.ffff.ffff). At the same time, the provider's router decapsulates the IP packet with the ICMP request, encapsulates it in an Ethernet frame with its address (00ff.ffff.ffff) as the source address and Client B's address (00bb.bbbb.bbbb) as the destination address (this is confirmed by traffic dumps). Client B, having received an ICMP request, generates a response, sends an ARP request to determine the MAC address of Client A (because Client A's IP address is in the same "subnet" as Client B's address), and sends a response directly to him.
Unlike the usual situation (when 1-2 pings are lost if there are no ARP entries), in this case 3 ARP requests are generated (Client A determines the MAC address of the provider's router, the provider's router determines the MAC address of Client B, Client B determines the MAC -address of Client A), respectively, 3 pings are initially lost.

The client on the router changes the interface configuration to:
Interface e0/0
Ip address 10.45.18.15 255.255.255.128
Ip address 10.45.19.15 255.255.255.0 sec
No shutdown
!
Will the address 10.45.19.15 be present in the ARP table of the ISP's router?

First, if you simply assign an address and do not generate any ARP requests, then, of course, the provider's router simply does not know about the presence of such an address. Secondly, if you generate, for example, a gratuitous arp reply (by deactivating and reactivating the interface, shutdown, no shutdown), the provider's router will receive an ARP packet (because ARP is broadcast), but it may well not take it into account, given the lack of a route to 10.45.19.0/24 via incoming interface:

ISP#sh arp
Protocol  Address          Age (min)  Hardware Addr   Type   Interface
Internet  10.45.18.5              -   00ff.ffff.ffff  ARPA   FastEthernet0/0
Internet  10.45.18.15            24   00aa.aaaa.aaaa  ARPA   FastEthernet0/0
Internet  10.45.18.194           22   00bb.bbbb.bbbb  ARPA   FastEthernet0/0
ISP#
*Mar  1 01:24:18.735: IP ARP: rcvd rep src 10.45.18.15 00aa.aaaa.aaaa, dst 10.45.18.15 FastEthernet0/0
*Mar  1 01:24:18.739: IP ARP rep filtered src 10.45.19.15 00aa.aaaa.aaaa, dst 10.45.19.15 ffff.ffff.ffff wrong cable, interface FastEthernet0/0
*Mar  1 01:24:18.739: IP ARP: rcvd rep src 10.45.18.15 00aa.aaaa.aaaa, dst 10.45.18.15 FastEthernet0/0
*Mar  1 01:24:18.739: IP ARP rep filtered src 10.45.19.15 00aa.aaaa.aaaa, dst 10.45.19.15 ffff.ffff.ffff wrong cable, interface FastEthernet0/0
ISP#sh arp
Protocol  Address          Age (min)  Hardware Addr   Type   Interface
Internet  10.45.18.5              -   00ff.ffff.ffff  ARPA   FastEthernet0/0
Internet  10.45.18.15             0   00aa.aaaa.aaaa  ARPA   FastEthernet0/0
Internet  10.45.18.194           23   00bb.bbbb.bbbb  ARPA   FastEthernet0/0

Thus, the experimental answers to your questions are:
1) yes, pings pass
2) no, there will be no ARP entry
I personally believe that practice (at least such) is the best criterion of truth. Another thing is that the one who will check your answers may think differently.

Answer 4

If the mask is /25 on the client 10.45.18.194, then it will be, if /24, then no.
Here the whole question is how the packets will go, through the gateway or inside one broadcast domain, it all depends on the mask on the host of another client. Recording in arp will be, because. arp request is broadcast and will go to the gateway as well.