How do I find all occurrences of a node by name using SQL2

The Problem You want to use a SQL2 query to retrieve all nodes that are named "nameofnode"

The Solution:

SELECT * FROM [nt:base] AS s WHERE LOCALNAME() LIKE "nameofnode"

The LOCALNAME() function returns the node name.