There is the
Field Calculator which has a lot of functionalities. Then there is
Select by Expression which has a SQL Style Syntax.
However, is it possible to write plain SQL in QGIS on a Layer like a shapefile or geopackage?
No, the tool
Select by Expression is not using proper SQL. It's QGIS Expression. https://docs.qgis.org/latest/en/docs/user_manual/working_with_vector/expression.html
It has its own syntax. But it works for any layer provider : ogr, geopackage, postgresql, csv as long as you have loaded the layer in QGIS.