A beginners guide to thinking in SQL

  SELECT
  members.firstname AS "First Name",
  members.lastname AS "Last Name"
  FROM borrowings
  JOIN books ON borrowings.bookid=books.bookid
  JOIN members ON members.memberid=borrowings.memberid
  WHERE books.author='Dan Brown';

  SELECT
    members.firstname AS "First Name",
    members.lastname AS "Last Name"
  FROM borrowings
    JOIN books ON borrowings.bookid = books.bookid
    JOIN members ON members.memberid = borrowings.memberid
  WHERE books.author = 'Dan Brown';

I understand there are differences between DB vendors, but my question is about general patterns and best practices.

    SELECT members.firstname || ' ' || members.lastname
    AS "Full Name"
    FROM borrowings
    INNER JOIN members
    ON members.memberid=borrowings.memberid
    INNER JOIN books
    ON books.bookid=borrowings.bookid
    WHERE borrowings.bookid IN (SELECT bookid
       FROM books
       WHERE stock>(SELECT avg(stock)
       FROM books))
    GROUP BY members.firstname, members.lastname;

    SELECT @avgstock = avg(stock) from books; 

    SELECT members.firstname || ' ' || members.lastname
    AS "Full Name"
    FROM borrowings, members, books
    WHERE members.memberid = borrowings.memberid
      AND books.bookid = borrowings.bookid 
      AND borrowings.bookid IN 
        (SELECT bookid
         FROM books
         WHERE stock > @avgstock))
    GROUP BY members.firstname, members.lastname;

I really love the idea of SQL and am looking for a project to use it. It seems like a great way to encapsulate most of the state of your program so you can make your code more decoupled. For crunching logs or other data-dumps it seems like a no brainier, but for everything else I don't really have a a sense for the performance characteristics. Like would it make sense to keep objects' data in an SQL table (like a pImpl) ? is going over the rows of SQL table instead of std::vectors a different order of magnitude performance wise (I assume with something like SQL-Lite I can keep the tables in memory and not on disk).

I guess when is it appropriate to use a database and when is it not?

EDIT:

For context - I'm working almost exclusively in C++ and am usually pretty performance sensitive. In my last job we did radar simulations with lot of entities (radar platforms, aircraft, ballistic missiles etc.) that just lived in STD containers. Decoupling state from the simulation was always a bit of a struggle. Would keeping something like that in an SQL database make sense?

Another example. At the moment I'm working on an art project and I need to keep track of vectors and polygons which will be later used by a ray tracer. I'd also like to keep the state decoupled from my program. Would a SQL database be a bottleneck?

completely simple and contrived for example purposes only, something like:

    active_users = select * from users where user.login > 0;

    select * from active_users where active_users.country is not null;

I say this as someone who learned object-oriented programming far before I learned about SQL or its declarative model. SQL was "hard" for me because the declarative style is so functionally different than OOP, and to this day I still haven't ventured to figure out the SQL equivalents of functions, variables, and other mainstays of general programming; I'm satisfied with using a scripting glue language like Python to automate simple SQL SELECT statements.

But I love teaching SQL to non-programmers. You can do a lot of important real-world things with it without having to jump deep into the logical puzzles of "regular" programming. By "deep", I mean things like for-loops; it never fails to surprise me how befuddled beginners are about how for-loops work -- not just the syntax, but the concept of a "block" of code, that for-loops can exist inside of for-loops...or more fundamentally, you can run more than one line of code inside a for-loop.

With SQL, you don't have to worry about any of that. You simply declare what columns you want from a table, what values to filter by, and optionally, how to aggregate it or join it with another table -- concepts that have much clearer analogies to everyday thinking about information (or, at least spreadsheets).

SQL gets complicated when you try to do complicated things. In the OP's opening query, there's a joining of three tables. But without reading it closely, I can already kind of describe the real-world information-need that it fulfills: List the people who have borrowed books that have been borrowed something something to do with `stock`. If I spent a few more minutes, I could figure out what the fuck that subquery, `stock > (SELECT avg(stock) FROM books)` is all about, but the difficulty there is just my ignorance of the domain and/or particular conventions in this hypothetical problem. If I, or even the average novice, wrote this query line-by-line, its purpose would not be so opaque (as writings rarely are to their own authors). The point is that it's much easier for a beginner to author their own SQL without fucking things up in the way you can easily do so with even just a for-loop. I can't imagine that the Python or Ruby equivalent would be nearly as clear as this SQL statement, even for a far simpler kind of join.

My main complaint about SQL is that it seems more logical that the `FROM` clause should come before the `SELECT` statement. Though my initial guess is that that hampers the declarative-clarity of the statement when `JOINS` are involved.

edit: If I could make one suggestion to the submitted article, it would be that the first "Simple Query" is not simple at all:

     SELECT bookid AS "id", title
     FROM books
     WHERE author='Dan Brown';

     SELECT * FROM books;

     SELECT title FROM books;