Do modern R and/or Python libraries make SQL obsolete?
R and SQL are two completely different beasts. SQL is a language that you can use to query data that is stored in databases as you already experienced. The benefits of SQL versus R lays mostly in the fact of the database server (MS SQL, Oracle, PostgreSQL, MySQL, etc.).

Most, if not all, modern database servers permit multiple users to query data from the same data source and insert, update and delete data in the same tables all while ensuring that the data remains consistent. This is essential for say recording a bank transaction. Can you imagine running a bank on R? That's where database servers come in. They ensure ACID properties of procedures run on the database. ACID stands for Atomicity, concurrency, isolation and durability (see ACID description on wikipedia). R is a single user platform where everything happens in memory. So, if your computer stops working halfway in a big operation, your data will not be stored. You are also the only person who can access the data. To be clear, R is not considered an alternative for database servers and/or SQL.

Another main advantage of database servers is that a good database design will ensure that you can query your database fast by performing query optimization. To achieve this database servers keep track of the design of a table. See for a full discussion of this topic the wiki page. R cannot perform query optimization. Poor database design,  can lead to slow execution of your queries. Database servers can also perform optimization over queries that query multiple tables if foreign keys are properly used in the database design.

The SQL language has a very different syntax and I share your experience that it is shorter to write data munging steps using the data table or dplyr syntax. However, sometimes your data is too big for R or you need to store the results in the database as part of a periodic batch job, which will require to code your logic in SQL.

In my experience there are particular use cases for SQL and R/Python. SQL is great for storing business critical data and for allowing multiple people to access, modify, insert and delete data in a centralized environment. For any one-off data munging R and Python are great. If your data munging needs to be periodically executed, you will need to port your R/Python script to SQL.