That's the first / starter use case though, SQL can get a bit crazy once you get into enterprise spaces - stored procedures, funky datatypes, auditing & history features like temporal tables, hundreds, thousands of tables and a similar amount of columns, naming & organizing things, etc.
Thankfully, most people will never have to deal with any of that, myself included. The biggest databases I've had to deal with were very relatable - one about books & authors, another about football and historic results. The other biggest database is one I'm working with and building right now, it's a DB for an installation of an application managing tons of configurations, a lot of domain specific terms. The existing database is not normalized of course, and uses a column with semicolon-separated-values as an alternative to foreign keys. Sigh. Current challenge is to implement history, so that a user can revert to previous versions. I'll probably end up implementing temporal tables in sqlite.
I build and operated an employee database in accordance to the Danish OIO model for years, I even sat on a comity to define either models within the OIO model set for the public service.
These days I work with millions of entries from solar production.
I’ve never had to use complex SQL more than one time.
You use tools like SSIS or APIs on top of it to get and store the data.
I know you “can” create a lot of stored procedures and views, but as I’ve already said, you really, really shouldn’t do that exactly because it’s so terrible to work with for so many people.
Honestly though, SQL with an Odata api on top of it is one of my favorite ways of storing and retrieving data. If you have to actually transform the data, you do it with SSIS or similar tools that are much more efficient top level layers that are also testable and reusable.
But to each their own I guess. The join logic never bothered me much, and that seems to be an issue for a lot of people here.
True, keep the database as dumb as possible IMO. I have converted 200 line SQL queries into 30 lines of SQL plus 20 lines of code for OLTP. OLAP is a different beast though and SQL can get nasty.
Thankfully, most people will never have to deal with any of that, myself included. The biggest databases I've had to deal with were very relatable - one about books & authors, another about football and historic results. The other biggest database is one I'm working with and building right now, it's a DB for an installation of an application managing tons of configurations, a lot of domain specific terms. The existing database is not normalized of course, and uses a column with semicolon-separated-values as an alternative to foreign keys. Sigh. Current challenge is to implement history, so that a user can revert to previous versions. I'll probably end up implementing temporal tables in sqlite.