SQL: One of the most valuable skills

You can be a good developer and not know bash. Bash is, in some ways, programming horror. But SQL is pretty fundamental. It's a thing onto itself. There are plenty of shells and shell-like languages but there is really only one SQL.

james_s_tayler3• 7 years ago

wrt to shells - the combination of find/grep/sed/awk for locating and transforming textual data makes for its own kind of extremely powerful query language. I've always valued being able to think in pipelines made up of these tools.

wilsonnb3• 7 years ago

As someone currently employed as a .net developer, I’m not sure I would say the same.

tigerlily3• 7 years ago

Because so few actually know SQL well you can seem more elite than you actually are.

Thank you Craig, I'm convinced. Anyone know where best to begin learning SQL?

duck• 7 years ago

There are a lot of great free resources out there, but I had recently shared https://sqlbolt.com with a friend that wanted to learn SQL and she found it useful.

tigerlily3• 7 years ago

Wow, this is fun, thank you! I completed the Lesson 1 exercises first try!

zappo2938• 7 years ago

https://www.sqlteaching.com/

https://blog.codinghorror.com/a-visual-explanation-of-sql-jo...

https://pgexercises.com/

tigerlily3• 7 years ago

More SQL learning awesomeness, thanks zappo2938. It strikes me that the exercises identify the correct result very quickly. They must check against a required result, meaning it is possible for learners to enter a variety of different solutions. Any tips on SQL best practices?

nhebb• 7 years ago

Mode Analytics tutorial is well laid out:

https://mode.com/sql-tutorial/

xupybd• 7 years ago

The most fun I've had learning SQL. The project is a little dead by still a great project. https://schemaverse.com/tutorial/tutorial.php

If you're on MSSQL server this guy is great. https://blog.sqlauthority.com/. I also love the mug shots he puts everywhere.

Explore some weird things in SQL https://wiki.postgresql.org/wiki/Mandelbrot_set. It's fun to learn how much you can do.

Try to do things you'd normally do in code. Many algorithms can be implimented in SQL. DON'T DO THIS IN PRODUCTION but it's a good way to have fun with SQL and learn a few things.

Find an open source project in an area you're interested in that uses a lot of SQL.

thingsilearned• 7 years ago

I wrote this interactive SQL tutorial (you can write SQL in the page, and it tells you if you're right or not) last year! Always looking for feedback:

https://chartio.com/learn/sql/

NDizzle2• 7 years ago

https://use-the-index-luke.com/ - great resource for various database products.

cglee• 7 years ago

http://launchschool.com/books/sql

nablaone• 7 years ago

https://academy.vertabelo.com/

whynotminot4• 7 years ago

Having a solid working toolkit of SQL knowledge really will make you seem very useful on a team.

I do say "seem" though, in that if you weren't around it's not like the team would fail without you. It's not hard to learn SQL or Google around for a little bit to figure out what you're trying to accomplish.

Sort of like that one guy on your team who's a wizard at regular expressions. He seems like a life saver in the moment, but you'd be able to figure it out without him, too.

nicoburns• 7 years ago

Hmm... I'm not sure. I've seen people take hours or even days (in the case of more junior developers) trying to sort out these kind of issues. It's pretty helpful if you have (or are) someone who can waltz over and fix it in a couple of minutes.

Complex git problems are another one that can really throw people sometimes.

whynotminot4• 7 years ago

Oh it's definitely helpful! And it's something I should finally take the time to dedicate a weekend to shoring up my knowledge.

Just every time I have to deal with a sql database, my lack of knowledge (at least to this point) has never been a show stopper. It takes me a little more time than a real pro to get my statements figured out, but it's rarely so complex to require canvasing the shop for a true expert.

sooheon2• 7 years ago

Speaking as not an SQL guru -- isn't this true for every skill? Theoretically anyone can figure anything out with enough time, the value is of specialists having better mental maps for what solutions apply, when you need them.

whynotminot4• 7 years ago

isn't this true for every skill?

But you're implying then that all skills are equally hard to get a handle on, and that's just not true.

Working with sql is fairly simple even for those who aren't experts. But (as the article mentions) it's not always something a dev has done before or consistently so they'll often feel relieved if there's someone who's confident about it around.

ehnto3• 7 years ago

Normally if you need a guru it's because you are at a level of SQL complexity that can't be navigated without intuitive knowledge of SQL and it's various nuances.

qaq• 7 years ago

well it might be a bit too late when you start googling if you didn't have a person on the team that understands your database well. I am always surprised to see that very few people care enough to learn about isolation levels for example.

war10253• 7 years ago

There is a big difference between "get's the correct data out of the database" and "get's the data efficiently"

A significant percentage of my time over the past few years has been spent optimizing queries that other people did in obvious, but non-ideal ways.

dhbradshaw• 7 years ago

SQL is nice on a surface level and helpful in practice.

Having a working intuition for relational databases is valuable on a deep level. I mean having a sense of how to organize the tables, what sizes are large and small, when to add what kind of index and what the size and speed limits are likely to be for a given data structure. That's extremely valuable.

BTW, we're preparing to move a postgres database that's a few TB from Heroku to AWS RDS. The catch is that we can't afford more than a few minutes of downtime. If this is in your wheelhouse, reach out! We'd like to talk.

jweir• 7 years ago

Ok, we had a MySQL DB around 500GB, and not on Heroku, but we used the AWS Database Migration Service: https://aws.amazon.com/dms/

We had just a few minutes of downtime.

I did have few issues around permissions, but an AWS support person walked me through the process and we got it up and running. The support was great, and we just have standard business support.

EDIT: DMS might be a no go, as jpatokal points out. While Heroku allows external connections, it doesn't allow super user access which, AFAIK, DMS requires.

https://devcenter.heroku.com/articles/heroku-postgresql#conn...

jpatokal• 7 years ago

Heroku Postgres doesn't support external replicas, which complicates migrations out considerably. If you can't tolerate more than a few min of downtime for writes, you pretty much have to build the replication at the app level.

craigkerstiens• 7 years ago

That does sound in our wheelhouse. If you drop me an email happy to setup a time to talk.

codetrotter• 7 years ago

BTW, we're preparing to move a postgres database that's a few TB from Heroku to AWS RDS. The catch is that we can't afford more than a few minutes of downtime.

Would it be ok to serve stale data for an extended amount of time? If so then you could modify your application to write to AWS RDS but to keep reading from Heroku. Then you start copying over the data from Heroku to AWS RDS, and once the copying is done then you point your application to read from AWS RDS.

Another thing: If your tables have foreign key constraints then you need to create the tables in the new DB without them first, since the writes coming to the new DB could be referencing data from the old DB which have not yet been copied over. Once you’ve completed copying the data over you add the FK constraints to the new DB. Depending on how your application was made this could be a problem or it could be ok.

sk5t3• 7 years ago

Depending on the size of the relations, something like adding a new FK might result in more than minutes of downtime due to an exclusive lock--although that's a heavily couched "might" given all the work the Postgres team has put towards online DDL.

suls• 7 years ago

Are you controlling all the writes? If so I’d go a phased migration .. easier to pull off and much lower risk than a big-bang one-off approach.

sueders101• 7 years ago

If you're going Postgres to Postgres I enthusiastically recommend Bucardo.[1] I'm not very technical, but even I managed to get an ~1TB DB successfully replicating using it. In my case it was going from AWS RDS to AWS Aurora. Previously others had tried AWS DMS, but we kept having data integrity issues. And DMS cost us significantly more than Bucardo whose only cost was a medium size EC2 instance and data in/out.

[1] https://bucardo.org/Bucardo/

aboutruby• 7 years ago

I agree with the core idea. I've seen all kinds of people use SQL in their jobs even if it's not every day: marketing, finance, developers obviously, etc.

GolDDranks3• 7 years ago

I consider myself functional, but not proficient in SQL. Is there a reliable and easy way to assess one's skills in SQL? I feel that most of the new things I learn about SQL these days are database specific. I wonder whether I'm missing out on something, or do I already have the "core" SQL knowledge down, and everything else is special cases?

cpburns2009• 7 years ago

The core concepts are the most important things to know. How do SQL queries and their various clauses work. Tables, data types, indices, and constraints are all very important. With that knowledge you can handle most SQL databases, and begin to learn and work around their quirks. Yes, there are a lot of database specific details. Procedure languages can be useful such as PL/pgSQL for PostgreSQL and T-SQL for SQL Server.

mongol2• 7 years ago

I recommend you look at one of Markus Winand's presentations, such as this one : https://youtu.be/xEDZQtAHpX8 I felt it summarized well the modern foundation of SQL databases.

GolDDranks3• 7 years ago

Thanks, I'll be sure to watch it!

AlisdairO• 7 years ago

(self plug) take a look at pgexercises.com . It's Postgres-focused but mostly cross-platform, and I think it covers most of the important stuff.

GolDDranks3• 7 years ago

Thanks, I'll check it out!

vietvu2• 7 years ago

The more queries I write on Elasticsearch, the more I value SQL.

rb808• 7 years ago

Me too. I spent years complaining about databases and all the structure and rules and inflexibility. It was initially great to go to NoSQL backends where suddenly you're free. Now I really miss them :(

vietvu2• 7 years ago

Well, at least I can do simple query with SQL and Elasticsearch and on Hive.

lucidone• 7 years ago

I really miss being able to make arbitrary where clauses to adjust to feature changes.

NDizzle2• 7 years ago

Seriously. Each time I have to do something in elasticsearch I'm constantly trying to reason why the creators went about it like this. Yeah hey let's serialize some nested JSON object that contains no less than 2,000 brackets.

iblaine• 7 years ago

I recently had a candidate that had very good Scala skills and very poor SQL skills. Despite Scala and spark being powerful, you can over complicate a solution in those languages that could otherwise be easily solved in SQL. That’s interesting to me because I see SQL as a fundamental tool that you build on top of and some people these days seems to skip it.

opportune2• 7 years ago

Do you know how good is the SparkSQL optimization in e.g. Databricks? Conversely I have also seen SQL used in overcomplicated ways to replace a more general programming language.

When scripting in SQL I have seen that in certain SQL flavors there is no good way to split a large file into k smaller ones for a fixed k without creating a new column 1-<number of records>, selecting k new tables modulo the number in the new column, then writing the k files. The way this is implemented is usually naive so the performance is piss poor due to writing and subsequently reading so much data.

If this candidate was very good at Scala, assuming they were a Spark programmer, they could probably do lots of dataframe operations that were as good or better than SQL commands regarding performance no?

daveFNbuck• 7 years ago

I never encountered SQL or at all in my CS education. I got pretty good at Scala for Scalding MapReduce before writing my first SQL query. Learning the basics only took a few days after that.

If someone has enough understanding of data and CS fundamentals to be very good at Spark/Scala, learning SQL isn't going to be much of a barrier for them. I find that the bigger problem is usually people who have a very strong understanding of SQL but no clue as to the resources needed to perform different kinds of operations.

max763• 7 years ago

I spent a year in a role where 50% of my duties was writing sql reports. These reports where usually between 500 and 1000 lines of sql a pop. Sometimes the runtime of the report was measured in hours, so learning efficient sql was important. The company had a lot of people that had been writing sql for awhile, and there were lots of cool code snippets floating around. I learned a lot in that year.

I've moved to writing backend code. I'm surprised most of my peers cannot write anything more complicated than a join. Most people are perfectly happy to let the orm do all the work, and never care to dig into the data directly. Every once in a while my sql skills save the day and several people in other departments contact me directly when they need excel files of data in our database we don't have UIs to pull yet.

dwd8• 7 years ago

Knowing how to optimise SQL (and also database indexes) is a valuable skill.

Reducing a highly used query's execution time by several orders of magnitude can be quite gratifying.

faceplanted• 7 years ago

Stick the --+ORDERED flag on a random select sometime and look at what happens to the estimated query cost to see how easy it would be to fuck this up if you had to make all the optimisation choices yourself.

justinclift• 7 years ago

Any recommendations of a place for sharing "extremely advanced" SQL skills?

Asking from wanting to make use of such a place, and haven't seen anything like it. So, probably need to bootstrap one instead (etc).

veritas32416• 7 years ago

Are you asking for examples of advanced SQL skills?

In my experience, if you can grok lateral joins (aka cross apply), recursive CTEs, window functions, and fully understand all the join types, that's a gold star for understanding SQL!

andy_boot• 7 years ago

I made this: https://www.windowfunctions.com

CRConrad3• 7 years ago

Are you asking for “sharing skills” as in something like StackOverflow, but for SQL?

I would think there already is a Stack Exchange for SQL, possibly several (for different RDBMSes/ dialects); go have a look there, if this is what you meant.

Scaevolus• 7 years ago

Do you do much with Excel's SQL connectors? Sheets populates with database results are powerful and reasonably user friendly.

hobs2• 7 years ago

A thing I find even more user friendly is Import-Excel https://github.com/dfinke/ImportExcel

It's a powershell module that allows you to easily dump things directly to excel files, does pretty decent datatables, multi-tabs, etc.

bas3• 7 years ago

From a performance perspective, most ORMs are trash if used naively.

weka• 7 years ago

I've moved to writing backend code. I'm surprised most of my peers cannot write anything more complicated than a join.

I'd wager more don't even know what a join is.

chubot4• 7 years ago

Once your SQL gets into 500-1000 lines, and hours of runtime, I would suggest using data frames instead (in R or Python).

I wrote this post to introduce the idea:

What Is a Data Frame? (In Python, R, and SQL) https://www.oilshell.org/blog/2018/11/30.html

It's often useful to treat SQL as an extraction/filtering language, and then use R or Pandas as a computation/reporting language.

I think of it as separating I/O and computation. SQL does enough filtering to cut the data down to a reasonable size, and maybe some preliminary logic. And then you compute on that smaller data set in R or Pandas -- iterating in SECONDS instead of hours. The code will likely be shorter as well, so it's a win-win (see examples in my blog post).

I can't think of many situations where hours of runtime is "reasonable" for an SQL query. In 2 hours you could probably do a linear scan over every table in most production databases 10-100 times.

For example, if your database is 10 GB, you could cat all of its files in less than 5 minutes (probably much less on a modern SSD). In 2 hours, you can do a five minute operation 24 times. I can't think of many reports that should take longer than 24 full passes over all the data in the database (i.e. pretending that you're not bothering to use indices in the most basic way). If it takes longer than that, the joins should be expressible with something orders of magnitude more efficient.

I've mainly worked with big data frameworks, but I think that almost any SQL database (sqlite, MySQL, Postgres) should be able to do a scan of a single table with some trivial predicates within 10x the speed of 'cat' (should be within 2x really). They can probably do better on realistic workloads because of caching.

hobs2• 7 years ago

Your advice is pretty good, but I would definitely say that SQL doesnt have a strong relationship between lines of code and runtime, you can line break (and many do) your wide table's select into many columns and get there pretty quick.

If you are writing SQL regularly, understanding the basics of how the queries you write is not that hard for you engine of choice, and everyone should be required to understand the basics of reading an execution plan so they can find the right inflection points between data gathering and processing.

I regularly sigh write and maintain SQL procedures that are >10k LOC, and their runtime never would exceed minutes, much less hours.

busterarm5• 7 years ago

This isn't meant to offend, rather as a point of consideration, but seeing your example use case being 10GB and then talk about big data frameworks makes it hard for me to take this advice seriously.

I might reach for that kind of tooling at the hundreds of TB to PB scale, but in our production applications we have _tables_ that are multiple terabytes. SQL is just fine.

Yes, we also have have queries that run in the timescale of hours and they are always of the reporting/scheduled task variety and absolutely vital to our customers. Long running reporting queries are pretty acceptable (and pretty much the norm since forever) outside of the tech industry and your customers won't balk at it.

max763• 7 years ago

If catting 10gb of files takes 5 minutes than catting 500gb of files takes 250 minutes. This is, of course, an over estimation of how long it takes to look at data on a disk. It's also the most trivial thing you can do with data, read it once.

I think we have a misunderstanding about the scale of data.

EDIT: Reading your post you mention that dataframes stores data in memory. Working with data in ram would provide a significant speedup. It just wasn't possible.

DrPhish• 7 years ago

I had a similar role where I was writing boring LOB apps in a very gross language, but since we were using an SQL backend for all the data, I instead challenged myself to using bare templates in the actual programming language and writing all the extraction, transforms and logic into SQL selects and (when unavoidable) programmatic bits.

I also learned a crapload about obscure SQL since I would go to extreme lengths to achieve this. There was a lot of meta-SQL programming, where I would use SQL to generate more SQL and execute that within my statement, sometimes multiple layers deep. It was beautiful in its own way, expanding out in intricate patterns.

max763• 7 years ago

I never personally wrote meta-SQL but it was used at that office and I read it. I didn't see beauty. When I read the code and understood it I felt terror.

veritas32416• 7 years ago

You might be interested in this project then https://www.getdbt.com/ it's a SQL compiler and executor which has many features like you're talking about (macros to generate SQL and so on). It makes it pretty easy to build up a complex DAG of SQL queries and transformations.

saint_abroad2• 7 years ago

Most people are perfectly happy to let the orm do all the work, and never care to dig into the data directly.

ORM all-too-often defines data structures from code.

Linus Torvalds wrote,

I will, in fact, claim that the difference between a bad programmer and a good one is whether he considers his code or his data structures more important. Bad programmers worry about the code. Good programmers worry about data structures and their relationships.

https://lwn.net/Articles/193245/

neokantian2• 7 years ago

Learning SQL once will allow you to re-use it heavily across your career span without having to re-learn.

Like all good abstractions, SQL is the practical expression of a mathematical theory. In the case of SQL, you use Zermelo-Fränckel (ZF) set theory to reason about data sets.

While it is easy to come up with merely conjectural implementations for haphazardly doing things -- arbitrary trial and error, really -- it is notoriously hard to develop a systematic axiomatization such as ZF. Mathematics do not change and are hard to defeat. That is why math-based knowledge and skills are able to span an entire career without having to re-learn.

SQL got kicked off in all earnest when David Child's March 1968 paper, "Description of A Set-Theoretic Data Structure", explained that programmers can query data using set-theoretic expressions instead of navigating through fixed structures. In August 1968, Childs published "Feasibility of a Set-Theoretic Data Structure. A General Structure Based on a Reconstituted Set-Theoretic Definition for Relations".

Data independence, by relying on set-theoretical constructs, was explicitly called out as one of the major objectives of the relational model by Ted Codd in 1970 in his famous paper "A Relational Model of Data for Large Shared Data Banks" (Communications of the ACM 13, No. 6, June 1970).

Using Codd's work, Donald Chamberlin and Raymond Boyce at IBM, developed in the early 1970s what would later turn out to become SQL.

kccqzy2• 7 years ago

Relational algebra is a much closer foundation for SQL than ZFC (or plain ZF, because for finite sets the difference doesn't matter). You can say SQL has ZFC as its foundation but that's not very useful. In the same way you can say ZFC is the foundation of everything in mathematics. SQL doesn't even use anything beyond naïve set theory because we are dealing with finite sets, let alone the full power of ZF.

neokantian2• 7 years ago

In the same way you can say ZFC is the foundation of everything in mathematics.

Yes, technically, that is indeed true of every Turing-complete axiomatic theory.

You could conceivably even use the SKI combinator calculus as the foundation of everything in mathematics. Still, something tells me that a database query language in widespread use, based on the SKI combinator calculus is rather unlikely to emerge (certainly not at this point).

Hence, since ZFC is one of the many alternative, equivalent foundations of everything in mathematics, picking ZFC still amounts to making a choice, no matter how small ...

SQL doesn't even use anything beyond naïve set theory ...

Agreed. However, we also have to consider what situation we came from. David Childs objected to the use of custom, application-specific code, data types and functions, because these custom structures could (more easily) be represented by one, general abstraction. Given the fact that back then, people were not even using naive set theory, his considerations were clearly a step forward in the right direction.

komali2• 7 years ago

Have you written more things I can read?

vinceguidry• 7 years ago

His/her comment history is fairly interesting. Plenty of seemingly needlessly downvoted comments, which to me is a marker of a perceptive mind.

reirob• 7 years ago

Just to say there is "Project:M36 Relational Algebra Engine" [1], which says about itself:

Introduction

Project:M36 implements a relational algebra engine as inspired by the writings of Chris Date.

Description Unlike most database management systems (DBMS), Project:M36 is opinionated software which adheres strictly to the mathematics of the relational algebra. The purpose of this adherence is to prove that software which implements mathematically-sound design principles reaps benefits in the form of code clarity, consistency, performance, and future-proofing.

Project:M36 can be used as an in-process or remote DBMS.

1: https://github.com/agentm/project-m36

jsnk• 7 years ago

Reading the thread so far, I see SQL and bash suggested as being valuable skills.

Can you suggest more skills and tools like these that are timeless and valuable?

sonabinu• 7 years ago

war10253• 7 years ago

Communication. Particularly the ability to cross the boundary between technical talk and non-technical talk.

People like other people who are easy to talk with. Also you will be more likely to build what is actually needed, rather than what someone thought they needed.

war10253• 7 years ago

One of the most useful books I've ever read was "Thanks for the Feedback" [1]

I've read a lot of the more standard books like "How to Win Friends and Influence People", etc. I feel like this book is similarly valuable, but a lot less well known.

[1] https://www.goodreads.com/book/show/18114120-thanks-for-the-...

beachy• 7 years ago

Data modelling/design (closely related to SQL of course).

i.e. distilling business problems down into tables and columns.

Skills acquired decades ago are still extremely useful.

wvenable4• 7 years ago

Regular expressions

ams6110• 7 years ago

And sed, grep, and awk (though awk by itself can often do the job of the first two).

dwd8• 7 years ago

Invaluable for cleaning up raw data before it gets into the database.

sparkie• 7 years ago

I'd add that one should also learn LL and (G(LA))LR grammars. Regex are handy for quick hacks, but anything non-regular becomes hacky and overcomplicated compared with a self-describing grammar. PCREs are over-engineered.

There seems to be an equivalent of the "NoSQL" crowd in parsing which appears to be "NoLALR". I can certainly see that other kinds of parsers have their use cases - but LL and LALR parsing have sound theory which is well understood, and they are certainly not obsolete.

mongol2• 7 years ago

Http.

kumarvvr7• 7 years ago

When beginning a green-field project, a broad, 10,000 ft overview of the data models in your application.

Not only in SQL, but also in terms of config files, easy to edit data files for static data, large files, small numerous files, etc etc.

Having the right data model can mean a world of difference as to how flexible your application can be.

Also, decoupling between data models. For example, decoupling related data through proxies and procedural code.

chubot4• 7 years ago

A few months ago vogre@ suggested SQL, bash, and HTML, and I concurred.

https://news.ycombinator.com/item?id=18001267

HTML is the quickest way to share something permanently. And its client is the most widely available (the browser).

Many people seem to use something like Dropbox or Google Docs now, but my opinion is that HTML still has many benefits, once you know it. I rsync my HTML to a shared hosting account.

In addition to bash, I'd suggest learning more about Unix in general.

I saw this page a few weeks ago. I haven't gone through this particular course, and I don't necessarily endorse it, but I'll just say that the subject matter could have been largely the same 20 years ago. Unix has proven to be an effective way of working and collaborating for a long time.

https://news.ycombinator.com/item?id=19078281

EDIT: I also suggest knowing some basic analysis outside of SQL, using data frames:

https://news.ycombinator.com/item?id=19150543

The need for that skill isn't going away anytime soon. You could argue that a large portion of jobs are munging Excel spreadsheets, and data frames are more of less the programmer's way of accomplishing the same tasks.

You could be a snob and "delegate" those tasks to others, but IME if you really want to get something done that other people can't, you have to roll up your sleeves a bit. (Just like this article suggests, e.g. SQL is useful for both programmers and product managers.)

bas3• 7 years ago

Touch typing (I have no idea if this is required in middle/high school these days).

kerng• 7 years ago

The reason SQL is great in my opinion is partially what the author highlights. In addition I think one of the reasons is that it is a very mature technology that baked over decades. I first was exposed to ANSI-92 SQL in school and that was already like the 2nd or 3rd revision of the standard. I used it in my first couple of jobs and the skill applied everywhere, Oracle, mySQL, SQL Server and there was even something called Access that understood it and Access was widely used amongst small businesses.

Knowing SQL to me was and even now is extremly helpful to understand data and how to tackle data problems. There weren't many other technologies that had such profound long term applicability, besides maybe knowing and understanding HTML and C. Many new things are just variations and improvements of those core technologies, and those can easily be understood and learned with good foundational knowledge.

JimmyAustin2• 7 years ago

My first job out of university was on an analytics team at a consulting firm (big enough that you know them) that used MS SQL Server for absolutely everything.

Data cleaning? SQL. Feature engineering? SQL.

Pipelines of stored procedures, stored in other stored procedures. Some of these procedures were so convoluted that they outputted tables with over 700 features, and had queries that were hundreds of lines long.

Every input, stored procedure, and output timestamped, so a change to one script involved changing every procedure downstream of it. My cries to use git were unheeded (would have required upskilling everyone on the team).

It was probably the worst year of my life. By the end of it I built a framework in T-SQL that would generate T-SQL scripts. In the final week of a project (which had been consistent 60-70 hour weeks), the partner on the project came in, saw the procedures written in my framework and demanded that they all be converted back into raw SQL. I moved teams a few weeks later.

The only good bit looking at it, is that now I'm REALLY good with SQL. It's incredibly powerful stuff, and more devs should work on it.

dwd8• 7 years ago

You can do some insanely complicated stuff in a stored procedure. A favourite was versioning and updating a set of data across multiple tables as an atomic transaction.

Also MS SQL let you throw a data object (say XML) at a stored procedure, convert it to a table structure with some XPath (another useful black art like regex) and use that as the input to a single INSERT.

AmericanChopper2• 7 years ago

You can do some insanely complicated stuff in a stored procedure.

You’re giving me flashbacks to stored procs that directly invoke java methods, and batch scrips that load client supplied data from and FTP server into external tables.

sk5t3• 7 years ago

The only question that remains is--Accenture or KPMG?

jessaustin2• 7 years ago

It could have been Deloitte, or as I convinced most of the contractors on several projects to pronounce it, "Delouche".

tartoran2• 7 years ago

That happens in the wild unfortunately, I ve seen some sql-Frankensteins here and there but generally, if done properly, sql is the easiest to grok and understand the domain from. I was introduced to SQL circa 2000(by my dad) and I've been heavily using it since.

My bulk of experience in Sql was in MsSql. Recently I got into an Oracle shop and the dialect is a bit different though it didnt take too long to be productive in. However, being productive and such, if you ask me, oracle smells clunky and has way too many features, let's say it's not my cop of tea...

beefield2• 7 years ago

My cries to use git were unheeded (would have required upskilling everyone on the team).

What is the best practice workflow using git with SQL server views/procedures? Can you actually somehow track changes in the views/procs themselves so that if someone happens to run ALTER VIEW, git diff is going to show something?

manigandham• 7 years ago

It's mostly about using git to ensure a consistent snapshot as changes are made rather than updating one procedure at a time.

If you are asking about SQL text then a good formatter would make it easy to see the physical diffs. Otherwise there are various vendors and tools that parse SQL and show the logical diffs in queries and schemas, along with doing deployments, backups, syncing changes, etc.

dwd8• 7 years ago

Every conversation I've ever had came back to using RedGate SQL Compare to diff databases and TeamCity for CI.

You basically shouldn't allow anyone to modify anything without it being scripted (bonus points if it comes with a rollback and is repeatable for testing). Your scripts then all go into Git.

bonesss6• 7 years ago

You can version your scripts & DB as they get pushed with a common tracking number. Personally I think it's best to establish a workflow where manual changes to the DB would be pointless and likely to result in them being overwritten.

Outside of commercial tools dedicated to the purpose: you can query the DB for the content of the procedures/tables and compare them to a given set of scripts, or the most recent expected version. Auto-generated ORM models can be used to validate table/view composition for a given DB/App version, as well. Having these capacities baked into the versioning and upgrade process can do a lot over time to correct deviating schemas and train developers away from meddling with DBs outside the normal update procedure :)

ako7• 7 years ago

There's no reason you can't store your sql/tsql/plsql in version control.

We were doing this 20+ years ago, all code was in csv (we upgraded from rcs to csv), and we had a productized distributed scheduling system that would deploy all the sql scripts every night on a number of oracle databases running from aix, to solaris, to vms, to hpux, to irix, and later linux and windows NT.

Similar like you would now use jenkins to build, deploy and test your java apps.

Developers would never touch the test/production databases, only commit sql to csv. Develop local, sql text files, test on a local or shared development database, and then commit to csv.

lmm2• 7 years ago

There's no reason you can't - but there's limited tooling support and limited worker mindshare for that kind of approach.

OliverJones2• 7 years ago

umm. That's cvs. csv is something else, and it matters if you're a datashoveler.

nicoritschel• 7 years ago

And more recently, we see SQL abstractions overlaid on top of things like Kafka streams with messages encoded in Avro thanks to technologies like Spark structured streaming.

SQL is not only relevant to traditional databases. I never got into C#, but LINQ looks really interesting.

hardwaresofton4• 7 years ago

SQL is the most powerful query language ever invented and widely implemented over relation databases IMO (qualified heavily for lurking RDF zealots). Every time you see someone start to invent their own query language, I almost always mark is as folly (similar to when people invent their own configuration languages). Prometheus and GraphQL stand out as recent examples.

DBs like Kafka who recognize this and instead offer SQL on top of their things take the right approach IMO KSQL.

Scarbutt2• 7 years ago

SQL is the most powerful query language ever invented

No, its Datalog. Joking aside, they are equally powerful but one could argue about explicit vs implicit joins.

busterarm5• 7 years ago

While I agree with you, I met a friend of a friend in Denmark who works for PDC who makes the claim that, outside of them, there's only one other respectably-sized company on the planet that is actively keeping Prolog alive.

If I'm going to bet my career on one or the other, it'd be SQL.

Slight aside, I think there are good lessons to be learned from LINQ still.

hardwaresofton4• 7 years ago

No idea why this is getting downvoted, people must really dislike Datalog.

A recent up-and-coming software tool is the Open Policy Agent[0] and it uses Datalog -- though it's a custom implementation. This comment felt familiar so I went back and looked and it's come up on HN before[1].

[0] https://www.openpolicyagent.org/

[1] https://news.ycombinator.com/item?id=18156144

fdsak2• 7 years ago

I think one of major reason for being powerful is its bases on pure mathematical foundations.

brazzledazzle2• 7 years ago

What don’t you like about GraphQL? I haven’t used it enough to form a strong opinion but it seems ok so far.

hardwaresofton4• 7 years ago

I would 1000% not take my opinion as gospel on GraphQL -- I know how it works, but I have not written an implementation myself, and have not even spun up Apollo. With that heavy grain of salt, here's how I feel about GraphQL:

- It doesn't feel very interoperable nor flexible (it's a different "ecosystem") with regards to other software -- meaning that if it's much less "integrate with GraphQL" and more "build on top of GraphQL" or find the "GraphQL" way to do things. I find this to be different from approaches like OpenAPI/Swagger.

- Complicated GraphQL queries start to look like SQL to me, and I suspect that will become even more the case as time goes on, and people decide they want advanced things that are in SQL, like window functions.

- GraphQL locks you in to knowing the backend design from the frontend -- though this was mostly always the case with REST (need to know which endpoint to call), "true" REST/HATEOAS compliant backends held the promise of you just being able to ask for a shape of an object, and make decisions about whether to dive deeper as you go in a principled way. This kind of gets into the semantic web concepts -- but that's mostly a seas of never delivered promises so...

- It's features are mostly offered by tools like Postgrest (vertical/horizontal filtering[0], resource embedding[1]), which offers less complexity.

- GraphQL is going to very likely spend the next few years running into issues/features that the REST/HATEOAS has already solved (albeit not in a standardized way). I looked at a random issue[2] and this is definitely something that seems quite solved in the RESTish HATEOAS world (at the very least you don't have to wait for GraphQL to do something to solve it).

Basically, I wish someone had built the ease-of-use GraphQL offers on top of the HTTP+REST+HATEOAS model -- because it's wonderfully extensible. Excellent solutions often lose out to "good enough" solutions, and I just... don't want that to happen this time (I mean I don't think it will, it's pretty hard to beat out good ol' http).

We just got to a really good place with Swagger/OpenAPIv3 + jsonschema/hyperschema, in being able to create the standardized abstractions on top of HTTP (and bring with it all the benefits of HTTP1/2/3 as they arrive, and it feels like GraphQL is a distraction. The "entity" graph is just a rehashing of the relational database model and it doesn't feel like it offers much outside of a standardized way of presenting your DB -- if you want to present your DB to frontends why not just let them send SQL queries directly? No I'm not tone deaf, I know most devs don't want to write SQL, but it seems like they're about to get into bed with GraphQL despite the likelihood that it's just going to be another SQL once it has enough features.

That said, GraphQL is doing amazing things for developer velocity, and it deserves praise for that -- tools that grasp mind share this quickly usually offer a large amount of real benefits to the people using it, and I've seen that GraphQL does that.

[0] https://postgrest.org/en/v5.1/api.html#vertical-filtering-co...

[1] http://postgrest.org/en/v5.1/api.html#resource-embedding

[2] https://github.com/hasura/graphql-engine/issues/1598

baroffoos• 7 years ago

GraphQL doesn't even seem like the same kind of tool. GraphQL is what you expose as an api to users. It can only do a fraction of what SQL does and thats a very good thing. You certainly don't want users running their own SQL on your server.

physcab2• 7 years ago

I’ve been a career analyst for about 12 years so I write SQL every day. I know it very well. I’ve developed a very strong love / hate relationship with it over the years.

On one hand, you write what you want to find in fairly common language and you almost always get back what you want if you do it correctly. In many ways, it’s like a precursor to Alexa but in written form. It’s super easy to pick up for non technical people.

On the other hand, it’s extremely difficult to code review, and on a very complicated piece of business logic, errors could mean the difference between hiring 10 more people or laying off 100. So almost always it’s just easier to re-write.

Imagine if engineers couldn’t understand each other’s work, and had to rewrite code every time someone leaves the team. It’s insane to me that this is standard practice.

wvenable4• 7 years ago

It’s super easy to pick up for non technical people.

I haven't found that particularly true. I've trained people in SQL and yes, they can do the basics pretty well, but it requires a particular skill to actually write effect complex SQL statements. Non-technical people, being non-technical, generally cannot do that.

it’s extremely difficult to code review

I don't see why it's any more difficult than any other logic? It's always important to test.

physcab2• 7 years ago

I think the operative word there is complex.

Yes non technical people can’t write complex sql, but they can self service basic questions easier than learning Python or R for example.

And it’s difficult to code review SQL because complex business logic means complex data manipulations and those are hard to visualize and comprehend. You don’t have to think in a 3D space so much when you write python.

anorman7283• 7 years ago

As a dev, I have to use a query builder. It's frustrating because it's ten times more complicated than SQL and slower to execute, with basically zero advantages.

insulanian• 7 years ago

Why do you have to use the query builder?

anorman7283• 7 years ago

Because my CTO requires it.

aitchnyu• 7 years ago

Name and shame? Would you feel the same way about this? https://pypika.readthedocs.io/en/latest/2_tutorial.html#sele...

anorman7283• 7 years ago

Not really interested in naming and shaming because the lead dev trolls the internet to find people that don't like the framework it so he can publicly mock them.

As for PyPika, I wouldn't be fond of that, but that at least has an ability to get the raw SQL being built. There's no such option in my framework (and I've looked for hours combing the source code trying to find it), so debugging why the data coming out is wrong is kind of a nightmare (especially since the documentation is incoherent).

slap_shot4• 7 years ago

SQL is one the most amazing concepts I've ever experienced. It's nearly 5 decades old and there is no sign of a replacement. We've created countless other technologies to store and process data, and we always seem to try to re-create SQL in those technologies (e.g. Hive, Presto, KSQL, etc).

I run a early stage company that builds analytics infrastructure for companies. We are betting very heavily on SQL, and Craigs post rings true now more than ever.

Increasingly, more SQL is written in companies by analysts and data scientists than typical software engineers.

The advent of the MMP data warehouse (redshift, bigquery, snowflake, etc) has given companies with even the most limited budget the ability to warehouse and query an enormous amount of data just using SQL. SQL is more powerful and valuable today than it ever has been.

When you look into a typical organization, most software engineers aren't very good at SQL. Why should they be? Most complex queries are analytics queries. ORMs can handle a majority of the basic functions application code needs to handle.

Perhaps going against Craig's point is the simple fact that we've abstracted SQL away from a lot of engineers across the backend and certainly frontend and mobile. You can be a great developer and not know a lot about SQL.

On the other end of the spectrum are the influx of "data engineers" with basic to intermediate knowledge of HDFS, streaming data or various other NoSQL technologies. They often know less about raw SQL than even junior engineers because SQL is below their high-power big data tools.

But if you really understand SQL, and it seems few people truly today, you command an immense amount of power. Probably more than ever.

sadness2• 7 years ago

we always seem to try to re-create SQL

This tells us that people think SQL is good, but it's unfortunate that this is attempted, because the good thing about SQL is how well it corresponds to its data structures. Attempting to imitate SQL's form, rather than its principal of correspondence, has been counter-productive and prevented the reproduction of its merits. Case in point: Cypher.

rjbwork• 7 years ago

(please excuse my typing. i have one hand to use ATM) yup. we bet super hard on sql too. Its fantastic if you know what you're doing. We ca import arbitrary data and expose it as normal tables to our customers for analysis/transform/export along with having a silod access controlled place for them to see just their data. sql is a great technology, and is extremely flexible.

I love it. If you're looking to learn it and fundamentals, check out Jennifer Widom's course from Harvard. I can safely credit her w/ my career.

Beskz• 7 years ago

Do you mean this one from Stanford?

https://lagunita.stanford.edu/courses/Engineering/db/2014_1/...

dominotw4• 7 years ago

It's nearly 5 decades old

5 decade old sql is nothing like modern sql with tons of proprietary extensions, partition, windows, collations, typecast, json and god knows what else. Your examples " Hive, Presto, KSQL, etc" are a proof of this, they are so vastly different from each other you cannot simply learn "sql" and expect to use those tools in any serious manner.

This is precisely the proof of opposite that sql has not stood the test of time.

How is this even close to sql of 5 decades old,

CREATE STREAM pageviews (viewtime BIGINT, user_id VARCHAR, page_id VARCHAR) WITH (VALUE_FORMAT = 'JSON', KAFKA_TOPIC = 'my-pageviews-topic');

or this

CREATE STREAM pageviews (viewtime BIGINT, user_id VARCHAR, page_id VARCHAR, `Properties` VARCHAR) WITH (VALUE_FORMAT = 'JSON', KAFKA_TOPIC = 'my-pageviews-topic');

anything that looks vaguely like a weirdly formed english sentence is sql?

kamaal• 7 years ago

It would be insane to expect somebody arrive at a perfect feature full working version of anything in the first try.

That's true for any piece of software. SQL is not done yet, the fact that you can put in neat little features still into it is a proof to how well it was designed.

Heck we still have Lisp evolving today. A lot of things got done right in the early days.

chillacy2• 7 years ago

I understood when OP said the "concept of SQL" that they referred to a pretty broad idea of querying tabular data in rows and columns with some structured language.

nostrademons4• 7 years ago

"we always seem to try to re-create SQL in those languages (e.g. Hive, Presto, KSQL, etc)."

This is largely because of the number of non-programmers who know SQL. Add an SQL layer on top of your non-SQL database and you instantly open up a wide variety of reporting & analytics functionality to PMs, data scientists, business analysts, finance people, librarians (seriously! I have a couple librarian-as-in-dead-trees friends who know SQL), scientists, etc.

In some ways this is too bad because SQL sucks as a language for many reasons (very clumsily compositional; verbose; duplicates math expressions & string manipulation of the host language, poorly; poor support for trees & graphs; easy to write insecure code; doesn't express the full relational algebra), but if it didn't suck in those ways it probably wouldn't have proven learnable by all those other professions that make it so popular.

wvenable4• 7 years ago

I don't think it's non-programmers that keep SQL around; it's that there hasn't been a replacement for SQL that is better enough to replace it. I think C suffers from the same problem.

busterarm5• 7 years ago

So it's protectionism?

It's way easier to teach someone to code who is already really good at manipulating/modeling data than the other way around, IMO.

slap_shot4• 7 years ago

I don't disagree. I just don't think we've figured out the next step. Other paradigms (MapReduce and graph databases are perfect examples) have introduced very interesting and clever ways to querying data, but nothing has replaced SQL. If anything, it seems like it will be additive on top of what SQL has already done.

I know a lot about data, but I can't solve that problem. To anyone out there, much smarter than me: this is a real problem. If you solve it, you'll cement yourself the history of computer science.

I have no doubt it will get done, though.

3pt14159• 7 years ago

I understand your perspective, but I look at it a different way.

SQL is troublesome to some programming types because it seems alien to ask what you want instead of telling the computer what to do and I find most programmers, especially ASD-types (who I think have an edge for some situations, like writing certain code in a huge org like Google) find this an unfamiliar and strange way of thinking.

You're right about some of it (especially string manipulation, which is brutal) but I think you look at it like most programmers. SQL engages more of a simulative mindset—one popular with analysts—than the acquisitive mindset that most software developers outside of data science employ.

tlarkworthy• 7 years ago

I think SQL is entrenched by network effects. It has a unique conceptual paradigm, which is why it is hard to learn. But any replacement is also likely to be conceptually strange, but noone is gonna put in the time in to learn something weird unless it has the adoption of SQL. This we are stuck on a local mini.a with SQL. (A pretty good one though)

bogidon• 7 years ago

It’s not surprising librarians use SQL. Library science is all about organizing (informational) data!

garyclarke274• 7 years ago

SQL certainly does not suck as a language, unfortunately this is a very common perception, almost a (false) consensus within the developer community. I changed careers from Finance - I was a ACA (akin to CPA) , wizard at Excel VBA etc - to software development, after 10 years learning, I’m now finally also proficient with SQL. I have also learnt other langauges, but SQL is by far my favourite, Yes it has some mistakes in it’s design - the biggest being SELECT before FROM, Yes some key words, esp windows functions, have a silly length - this creates an impression of verbosity but SQL is actually the opposite, it is far more expressive than any Object Oriented language I can think of. 1 line of code, say a window function with a filter clause in Postgres is the equivalent of pages of Java code. Lateral Joins enable chaining, Views CTEs and Functions provide simple safely scoped composability. Postgres has incredible aggregation and analysis ability, built in out of the box. It’s String manipulation capabilities are wondrous, a 1 line StringAgg function can achieve the same as pages of VBA code - you may scoff but VBA with it’s ancient horrible editor is still necessary, if you are in Excel, is Javascript that much less verbose though? Pure SQL Postgres Functions are amazing, you can chain them, you can easily make them pure, they can include fast recursion and conditional logic. I never delete data, just append, a bitemporal immutable functional db is so powerful, enabling time travel and rock solid data integrity. Mat Views or Indexes or summary tables are easily created and often automatically. They provide an efficient reliable cache layer, which can address most performance issues that may arise

radicalbyte2• 7 years ago

SQL is amazing IF you understand it. You need to think in sets of things. It's like functional programming paradigms or recursion; once you really truly "get it" you start to feel like a Jedi master.

Unfortunately the vast majority of SQL users aren't that proficient. It's also fairly hard to learn because it's something that you only pick up with experience and specifically longer time experience with a sufficiently complex data model.

I personally would never have gotten good at SQL if I hadn't stayed in my first two jobs for 5 years each working daily with the data-models and the domain.

grigjd33• 7 years ago

As a software engineer who later learned SQL, I could not disagree more. Within the parameters that it is designed for, SQL is a terrific language that makes exploring and manipulating data much easier than tools like python or Scala. That doesn't mean I have no place for python or Scala, but that I definitely see a class of problems where an SQL interface is far superior.

ousta• 7 years ago

SQL has many limitations but at its core its a beautiful and very understandeable language very close to human language in the ways it is expressed.

Breza• 7 years ago

I agree with you and the parent comment. I run a data science department and we rely on SQL. We even embed SQL queries into R scripts to pull the latest data. It isn't worth working with ORM for an analytics project when a few dozen lines of SQL can get you everything you need.

manish_gill2• 7 years ago

On the other end of the spectrum are the influx of "data engineers" with basic to intermediate knowledge of HDFS, streaming data or various other NoSQL technologies. They often know less about raw SQL than even junior engineers because SQL is below their high-power big data tools.

I always ask a basic SQL question (involving sorting/grouping and one simple join) in my interviews for backend and/or data. IMO if you are calling yourself a Data Engineer and do not know SQL, then you haven't really worked with data, you've mostly just worked on data infrastructure.

slap_shot4• 7 years ago

It's mandatory. It's like saying you understand calculus but not being able to do algebra.

I interview a lot of data engineers/analytics engineers and I always start with SQL. If you can't grasp SQL, you're in for a very bad time.

api• 7 years ago

SQL is so long lasting because its math, sort of. It's a language for expressing data relationships in a very systematic logical and set theoretic way, not a random ad hoc hack.

Its syntax is a bit old fashioned and I do think efforts to make it more native to programming environments rather than a bolt on might be fruitful, but its concepts are timeless.

EGreg2• 7 years ago

Can I ask a simple question about efficiency?

It seems to me that graph databases are far more efficient than relational ones for most tasks.

That’s because all lookups are O(1) instead of O(log N). That adds up. Also, copying a subgraph is far easier, and so is joining.

Think about it, when you shard you are essentially approaching graph databases because your hash or range by which you find your shard is basically the “pointer” half of the way there. And then you do a bunch of O(log N) lookups.

Also, data locality and caching would be better in graph databases. Especially when you have stuff distributed around the world, you aren’t going to want to do relational index lookups. You shard — in the limit you have a graph database. Why not just use one from the start?

So it seems to me that something like Neo4J and Cypher would have taken off and beat SQL. Why is it hardly heard of, and NoSQL is instead relegated to documents without schemas?

rtpg• 7 years ago

In practice enterprise applications do a lot of relational operations.

Graph DBs might be valuable for a lot of problems, and it does feel like something like Neo4J would make a lot of sense for stuff like social networks, but for business records stuff isn't really that spread out

indigo945• 7 years ago

A hash index is O(1) no matter what language it's called from. The reason most people don't use hash indices very often is that they do not allow you to do a lot of useful things that you often end up wanting to do, such as retrieving all values within a given range.

barrkel2• 7 years ago

Chasing pointers (on the same medium) is usually slower than the access patterns that databases usually use.

First, a database can have hash indexes instead of btree indexes, so lookups can be O(1) too, but it turns out that btrees are often better because they can return range results efficiently, and finding the range in a btree is only logarithmic for the first lookup. If your index is clustered - if it covers the fields needed downstream in the plan - no further indirections are needed and locality is far better than a hash lookup. For example, a filter could be efficiently evaluated along with the index scan. And if your final results need to be sorted, major bonus if the index also covers this.

Second, it's best to think of databases doing operations on batches of data at a time. Depending on your database planner, different queries will tend to result in more row-based lookups (MySQL generally does everything that isn't a derived table, uncorrelated subquery or a filesort in a nested loop fashion) but others build lookups for more efficiency (a derived table in MySQL, or a hash join in Postgres). The flexibility to mix and match hash lookups with scans and sequential access - which are usually a few orders of magnitude faster than iterated indirection - means it can outperform graph traversal that is constantly getting caches misses at every level of the hierarchy.

The reason NoSQL and document databases suck from a relational standpoint is that they are bad at joins, and work better if you denormalize your joins and embed child structures in your rows and documents. Add decent indexes to NoSQL and they gradually turn back into relational databases - indexes are a denormalization of your data that is automatically handled by the database, but have nonlocality and write amplification consequences which can slow things down.

In terms of distribution, a relational plan can be parallelized and distributed somewhat easily if your data is also replicated - sometimes join results need to be redundantly recalculated or shuffled / copied around - and most analytics databases use this approach, though usually with column stores rather than row stores, again because scanning sequential access is so much faster than indirection. Joins don't always distribute well, is the main catch.

ako7• 7 years ago

Graph databases are great for retrieving existing data with associated data.

The power of SQL comes from the fact that you can easily create new information out of the data: create new sets, group by certain features, aggregates on certain features.

It's a lot more powerful than just store and retrieve.

asark• 7 years ago

N4J's a lot slower at tons of common tasks. If you look into its underlying data model, it's clear why. It achieves the speed it does at certain graph-traversal operations by storing its data in such a way that it's highly specialized for those operations—one would expect this to come at a high cost for other operations, and sure enough, it does.

It also doesn't bother with tons of consistency guarantees and such that you (may) get from, say, PostgreSQL. Yet is still slower for many purposes.

jwdunne• 7 years ago

Can you recommend good resources for engineers to up their skills with SQL that'll provide the understanding you discuss?

Pamar• 7 years ago

I am not the OP but I would suggest anyone who wants to get better at SQL to read this book:

https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/059600894...

raverbashing• 7 years ago

SQL is one the most amazing concepts I've ever experienced. ...and we always seem to try to re-create SQL in those technologies (e.g. Hive, Presto, KSQL, etc).

It's not SQL that's the concept. The concept there is set theory/intersection/union and predicates.

That's why you think you are "recreating" those, because they can be mapped using the same concept

SQL is only one way of expressing that concept.

michaelt• 7 years ago

When people say "SQL" they often refer to a bunch of concepts, some better than others, all bundled together.

SQL gets you tables and joins, sure. But it also gets you queries that (some) non-programmers can write, outputs that are always a table, a variety of GUI programs to compose queries and display the results, analytic functions to do things like find percentiles, and tools to connect from MS Excel. And it often means you get transactions, ACID compliance, compound indexes, explicit table schemas, multiple join types, arbitrary-precision decimal data types, constraints, views, and a statistics-based query optimiser.

Of course it also gets you weird null handling, stored procedures, and triggers.

sonnyblarney• 7 years ago

Just because it is resilient, does not mean it is good.

'Being great' usually helps a product gain traction. When SQL came about, it was very useful because hey 'I can query data!'.

But it's usually other reasons that drive incumbency.

clanrebornsx• 7 years ago

I disagree you can't use SQL for Google search.

No engineer has ever made this possible.

We've CommonCrawl data but you can't run SQL queries on that data this makes SQL useless.

When smart people have figured out how to do that, come back claiming SQL is important.

mikorym• 7 years ago

Lisbeth Salander sure can.

radicalbyte2• 7 years ago

Certain subsets of the developer community are usually excellent with SQL. It's especially evident with people who have been working with C# for > 10 years because Microsoft have always heavily pushed SQL Server with their developers.

There are plenty of use-cases for SQL with developers: especially in batch processes such as invoicing. A well crafted SQL query can execute exponentially faster than iterative code takes a lot less time to implement.

collyw5• 7 years ago

I blame the NoSQL movement for a lot of the lack of understanding in recent years. That was a step backwards.

zzzcpan6• 7 years ago

People behind NoSQL movement understand SQL better, than any people glorifying SQL here. In fact, I think this is a testament of how poorly most people actually understand SQL, they can't even see basic on the surface problems with it.

ASalazarMX• 7 years ago

SQLite is a testament to the power of SQL, it's a little wonder itself, and it's likely the most installed database globally.

berbec• 7 years ago

It still amazes my that Don Chamberlain was my dad's college roommate and co-worker at IBM. I always try to weasel college debauchery stories out of him, but as yet have been unsuccessful.

kumarvvr7• 7 years ago

SQL is a mind bender for me. I do a lot of work on Data, use python and pandas to do a lot of data magic, but the problem with me is my mind is too procedural in thinking.

- Step 1 - Step 2 - Loop through results in Step 2 - Curate and finish output.

I try very hard to transform the above steps into an SQL statement spanning multiple tables, but always fail and I usually fallback to python for manually extracting and processing the data.

Does anyone else face this problem?

Any suggested guides / books to make me think more SQL'ley ?

bluetwo• 7 years ago

I like the Ben Forta SQL in Minutes book.

Just the basics, very clear and easy to understand.

bas3• 7 years ago

Seconded.

beatgammit• 7 years ago

I took a course in prolog, really seemed to help get my mind in the right place.

Basically, instead of thinking in terms of how to get to the result, think instead of the result and figure out how to get there. For example, let's say I need to get a mapping of students to their classes. One way would be to get the students, then loop through that to get classes for each one. Another way to do it would be to ask the database to combine those sets of data for us instead and return the result (join students and class tables on the student ID).

Basically, find the data you want from each table, tell the database about the relationship between them (join on <field>), and set up your constraints. I guess you could think about the query as a function, and you're just writing the unit tests for it (when I call X with parameters Y, I expect Z).

arkh• 7 years ago

I took a course in prolog, really seemed to help get my mind in the right place.

I have the reverse problem: every time I motivate myself to learn Prolog I feel like I could just input the constraints in a db and use SQL to get my results.

conception2• 7 years ago

I think what your are doing is fine. SQL is strongest at answering questions not processing data. I think metabase has the right approach: What question do you want to ask your data? If you want to process and transform your data, I think your tools you are using are great for that.

eleclady3• 7 years ago

Not trying to play word games, but what is the difference between answering a question and processing data? Aren't they effectively the same?

Using another tool for processing data often results in recreating SQL mechanics at application level. E.g. select this data, retrieve it, loop and if this, then set that, etc. SQL does it way better, guaranteed.

Of course that's often required for technical reasons (scalability etc.) or processing that's too complex to implement at data layer, or just for cleaner design.

But SQL is amazing at processing data!

kccqzy2• 7 years ago

Try some functional programming. It will liberate your brain from thinking operationally. You will soon no longer think in terms of steps, loops or anything like that. You will get used to thinking about data transformations, which is what SQL is good at.

sk5t3• 7 years ago

I was about to add exactly this. Setwise operations, map/transform, aggregate, avoidance of mutable state, filters, avoidance of if/for with break conditions, quite strong avoidance of exceptions.

kumarvvr7• 7 years ago

Any books that teach function programming with python?

ramraj07• 7 years ago

I've thought about the same problem and think I have an explanation. SQL inherently avoids operations that cannot scale. This means that while the same operation can be written much more simpler as code if you're doing with pandas or spark, trying to do it with SQL would be less forgiving if your underlying data schema is not optimal or if your logic isn't scalable. But more often than not, the SQL query will run faster and more reliably given similar amounts of compute power to the two engines.

Of course you can always screw up and write suboptimal SQL that will take forever to finish, but it's just harder to do unless you're really trying to be dumb about it.

So I do think there's still a lot of merit in trying to learn and use SQL more.

sixo• 7 years ago

It also ignores operations of type (set of rows) => (set of rows) or (row) => (set of rows). and makes you jump through hoops of CTEs to implement them except in some special cases. Limited versions of these (flatMaps) work just fine in spark and have no problems scaling.

kumarvvr7• 7 years ago

I want to learn and do the same things in SQL because, I don't always have the opportunity to run python programs on the database server environment.

There are certain situations where an SQL query is all that I can work with.

fdsak2• 7 years ago

A good starting point could be understanding https://en.wikipedia.org/wiki/Declarative_programming

mutagen• 7 years ago

I have the same issue, procedural thinking. I've made some small progress thinking in sets and working to re-phrase my goals in terms of sets of data, sometimes using window functions (row_number(), etc) when I need n-1 items from the query.

Some problems can to be assembled from CTEs / subqueries when you might have reached for a procedural solution to 'finish' the problem. Grasping CTEs was a big thing for me and now I'm wrestling with issues of style mixing CTEs with tiny subqueries and wondering if the increase in readability (for me) is correct.

I'd love to see suggestions for digging deeper on this too.

eleclady3• 7 years ago

I used to think this too, and wondered what magical SQL perspective my brain was missing. I'd see these elegant but seemingly impenetrable queries and wonder why my brain wasn't in the "SQL dimension" yet.

But over time, and very heavy SQL reporting, I realized the procedural mindset still applies, it's just not expressed as such.

You need to think through the steps of how you want to retrieve, transform, aggregate etc. the data and build up the query to accomplish those steps. The resulting query doesn't look procedural, but the thought process was. Of course you need to know the sometimes opaque constructs/tricks/syntax to express the steps, which again, look like magic at face value or just reading docs.

I think this is why people struggle with understanding SQL. The thought process is still procedural, but the query isn't. You need to translate the query back into procedural terms for your own understanding, which is a PITA.

kumarvvr7• 7 years ago

To me trying to emulate procedural stuff in SQL feels wrong, especially with multi-table queries.

ghufran_syed• 7 years ago

I really like "Beginning database design" by Claire Churcher, and the follow-on book "Beginning SQL queries" . They sound like really basic books, and they definitelystart from the basics, but then build up steadily.

bigger_cheese• 7 years ago

I work a lot with data as well primarily I use SAS, R and SQL. I'm also very procedurally oriented (as in I prefer breaking task down into discrete chunks), R's functional way of chaining operations with pipe operator feels so alien to me.

I find myself having the opposite problem I probably use (abuse?) SQL more than should.

My world (industrial plant) is very DB/historian heavy everything speaks SQL it's pretty much the common tongue connecting everything. I think this is slowly changing some PLC/historians now offer a webservice which returns JSON objects via an ajax query - personally I vastly prefer SQL to ajax.

I'd estimate for a typical problem I'm working with maybe 90% is done in SQL vs 10% in R/SAS code.

When I need Regression, Principal Component Analysis, Time Series manipulation, Plots etc I have to break into dedicated language.

For most other things - extract, merge, filtering, high level aggregation (Count sum etc) type of operations using SQL feels more natural and expressive to me.

dwd8• 7 years ago

If raw speed is not an issue you can always fall back on temp tables or a cursor for those cases where you might have extracted an initial dataset to then iterate over.

EDIT: A CTE (Common Table Expression) might be what you're thinking you need. You can do some fun recursive queries with them.

kumarvvr7• 7 years ago

recursive queries

It's like all my fears concentrated, like the power of a thousand suns, onto a single sentence.

esfandia• 7 years ago

If you're mathematically minded (really just basic set theory), maybe try learning relational calculus. You'll express database queries in terms of Cartesian products, selections and projections. These operations translate directly into SQL, so maybe it'll help you think clearly first, then apply the syntactic translation. Honestly, if I had the choice I'd rather just use the syntax of relational calculus, as I find SQL too verbose and convoluted.

daveslash• 7 years ago

Two Things.

(1) Think of a database as a "big pile of stuff in a room". Some of it's ordered in a sane way, some of it's not. There is "a person at the door" to the room preventing you from entering - this is the Database Engine, not to be confused with the Database itself. You need to get things out of the room, and you need to put things in the room. You're not allowed to enter. If you want something out of the room, you must ask for it (query) by specifying what the thing looks like and which part of the pile you think it's in. If you want to put something in the room, you must provide explicit instructions for where it must go. When you write SQL, you are either asking a question of or giving instructions to the database engine. The DB Engine is distinct from the Database (the big pile of stuff...which you never have direct access to).

(2) As others have pointed out, SQL is very much the mathematics of Set Theory put into a programming language. Stanford has a free class on Databases. I took it a few years ago (it may have changed), and most of it had no SQL at all. It was fairly very straight forward - things you can do with pencil and paper. It's free, so no pressure. Go sign up and do some of the homework assignments. Do them until you get them right (you can re-take wrongly answered problems immediately). It'll help break your procedural mindset. https://online.stanford.edu/courses/soe-ydatabases-databases

kumarvvr7• 7 years ago

Thank you for the Stanford class reference. Will check it out.

thomassantosh• 7 years ago

Highly recommend Dmitri Fontaine's book "Mastering PostgreSQL in Application Development". Really helps emphasize how you can leverage the power of SQL to minimize all the Python / Panda steps to get the same result.

ddebernardy• 7 years ago

Reading the Postgres manual cover to cover is a fairly good starting point.

rsyring• 7 years ago

These mental paradigms helped me a great deal:

1) Think in terms of excel spreadsheets. At each step in your SQL you are creating a set of data. Doesn't usually matter how many rites it is, you just want the right columns. 2) Once you have a recordset, decide how the next recordset goes together. I would mentally visualize putting one worksheet next to the other and think through what columns I had to join together to get the rows I wanted. 3) Make sure you know what your first recordset should be. This is the base data that everything can be joined into. I refer to this as the domain of records.

bashmydotfiles• 7 years ago

I still struggle with thinking too "proceduraly" sometimes, but what really helps out with me is drawing / envisioning the sets of data as a series of circles and Venn diagrams.

It also helps to change the language you use in your inner monologue. Instead of thinking, "For each row in table A...", you should think, "For all the rows in table A that match on...".

zeveshe• 7 years ago

This book can help with getting into the right mindset: https://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/d...

zzzcpan6• 7 years ago

You can get to the opposite conclusion with some perspective. Just two decades ago SQL was pretty much a mandatory skill for new programmers. Everyone was learning it, it was in every programming job requirement in any way related to storing some data, etc. Basically databases and SQL were synonymous. But today a Citus guy writes a post attempting to convince people to learn some SQL (Citus is an SQL database company). And last time I used SQL was when Chromium dropped my cookies after an update and I was too lazy to relogin to all the websites (it stores them in an SQLite database). I guess things are not looking so promising for SQL.

Maybe we'll get to a better query language eventually. Distributed systems have some fundamental incompatibilities with SQL and theoretically could standardize on a different language, one that is not just about the data anymore, but that also lets users express and choose various performance and consistency trade-offs. I mean a query shouldn't, for example, invoke a transaction and consensus algorithm when all you need is to increment a view counter or give a one star rating or insert a log entry, those things can be propagated eventually without destroying latency and throughput and without complicating anything with transactional semantics.

busterarm5• 7 years ago

SQL is just fine. If incrementing a view counter, giving a one star review or inserting a log entry is important to you, you absolutely want that wrapped in a transaction. If you don't think so now, something will force you to think so at some point in the future. If you wait that long, it's probably too late.

The direction this line of thinking trends in is not thinking or caring about what data you're storing or caring about how it scales. If you're successful enough, this becomes an enormous problem. It literally is a problem that sinks otherwise successful companies.

Not thinking about your data model, not caring about the interactions of your data and not caring about the reliability of your data is a very expensive problem. It's also one where you don't know better until you do. Take some advice from the rest of us, please. We're saying this for your benefit, so that future you doesn't follow in our footsteps.

zzzcpan6• 7 years ago

If incrementing a view counter, giving a one star review or inserting a log entry is important to you, you absolutely want that wrapped in a transaction.

You absolutely don't. You want it as CRDT operations, not transactions.

enobrev• 7 years ago

There's not much going on in this post, so I assume the overall high rating is due to SQL being so damned fantastic.

I commented to a friend the other day that my favorite thing to do on any project is project-wide query optimization. It's like pulling weeds or power washing.

Arguably _every_ application is simply a means of manipulating data. All the other parts are important, of course, but the data comes first. And SQL is a hell of a UI for data. I've tried quite a few other query languages (or structures), even inventing my own once or twice, but none come close.

hbarka• 7 years ago

I love SQL. I love SQL for Data Warehousing even more. There’s a school of thought for DW that goes back many years. Many dimensional modeling practitioners know who Ralph Kimball is and also know the Inmon vs Kimball battles. To this day, star schema as a design pattern has helped dozens of analysts in my company abstract the relationship between measures (facts) and how to slice them (dimensions).

triplee• 7 years ago

I've been saying this for years, because I started doing web apps when we pretty much were sending raw SQL over to a database. Even after ORMs got bigger, I still stress knowing SQL even if you don't use it often, because it helps you understand the ORM.

And oddly enough, now I'm a data engineer. Everything I do, even when I'm not doing pure SQL, is influenced by years of experience in SQL. Either the languages of my big tools are still based on SQL in some fashion, or it simply helps to have an understanding of the ecosystem to figure out what's going on at scale.

Everything else has come and gone and come again, but SQL has help up pretty nicely. The only other skills that come close for me are working in languages that branched out of C (because the syntax isn't so different over time) and years of on again off again procedural languages (ColdFusion, vanilla Javascript, etc. leads to it being way easier to pick up Python).

teej• 7 years ago

For those trying to up their SQL game from basic selects and grouping, I suggest learning:

- Common Table Expressions (CTEs). They will equip you to write clean, organized, and expressive SQL.

- CREATE TABLE AS SELECT. This command powers SQL-driven data transformation.

- Window functions, particularly SUM, LEAD, LAG, and ROW_NUMBER. These enable complex calculations without messy self-joins.

Learning those three will change your relationship with SQL and blow the door open of problems you can solve with the language.

chucky_z• 7 years ago

ROW_NUMBER is absolutely the best, especially for marketing related customer data.

nimajalali• 7 years ago

I’d add LATERAL JOINs to the list.

For those unaware of LATERAL JOINs, it’s like a for each loop in SQL. Blew my mind and opened up so many possibilities.

ww520• 7 years ago

Understanding relational model is a valuable skill. SQL is useful but it's just the querying language. Understanding the relational model let you use SQL correctly.

40acres• 7 years ago

This thread has really opened my eyes. I was introduced to SQL on the first day of my first internship, it threw me for a loop and while the job was application development, the industry was finance so we were a very SQL heavy shop. That first "professional developer" experience burned into my memory the idea that all developers knew SQL, to me it was just an implicit thing that everyone knew -- even if they didnt work with it every day.

truth_seeker• 7 years ago

Hell Yes. Although it takes some time to switch from Procedural or OOP to highly Declarative world of SQL, but man, its worth pursuing. Only thing which really helped me build my confidence was solving more real world problems which involves 2NF and 3NF design, JOINs, Triggers, Indexing, Views & Materialized Views for denormalization, CTE and Recursive CTE's.

You may call me an extremist but from server side programming point of view with Postgres and FDW (Foreign Data Wrappers) which has ton of features other than SQL only thing i miss is HTTP server. :)

manish_gill2• 7 years ago

SQL is pretty great. I've been doing advance SQL in Postgres and BigQuery for quite a while and it's one of the most satisfying experiences.

Once you wrap your head around basics such as GROUP BYs, JOINs, CASE statements etc, you move on to advance concepts such as Window functions and there suddenly a new world of possibilities opens up to you for analytics! I've dabbled a bit in PL/pgSQL, but the syntax is way too arcane.

SQL can also be counter-intuitive sometimes. I rewrote a particular PG query and reduced time from ~30minutes down to 2 seconds by adding a subquery. :)

And no, ORMs can't really do what SQL can do in an equivalent manner.

democracy• 7 years ago

The thing is that most of the applications do need the complex data and thus do need need deep SQL knowledge. This is why NOSQL (I think) databases became popular. A DBA is called an "administrator" for a reason - most of the time he spends making sure the data is replicated and backed up/restored and really you need her help when things go to shits. It happens a lot but still in the majority of cases her role is invisible as the devs are trying to abstract the persistent layer (Hibernate and all other ORM frameworks, for java for example) where SQL-compliant database are just a part of the corporate landscape - not because I need it, but because it is there and all the mentioned processes are there.

minimaxir• 7 years ago

With all the data scientist thought pieces out there, I really wish there was more in-depth discussion of SQL and highlighting of the other shenanigans you can do with query optimization, subqueries, and even analytic/window functions. (although that last one is apparently the Hello World equivalent in data sci: https://twitter.com/minimaxir/status/1094430576092704768)

As a data scientist, I spend a lot more time building queries than working with models.

jlj2• 7 years ago

SQL has opened a lot of doors for me. It's a starting point, not an end though. Learning about dictionaries, lists, and other data structures has proven valuable and compliments SQL and tabular datasets very nicely. I got into those areas by working with SQL generators (ORM's) written in Python (airflow).

My thinking on SQL has evolved and lately I see it as a set definition tool. "Do action X on dataset Y." It's really useful for understanding data structures and data meaning too.

I've worked with more than a few SDE's who look down on SQL, but it's a really good tool when used properly, and it cuts across many technologies. Writing code to write SQL can be very powerful. And sometimes coded or scripted data wrangling without SQL is very useful too.

15 years ago SQL knowledge was not that widespread and it was easy to get tagged as a report writer. Today, a lot more business, product, finance, and accounting people are really strong with SQL, and rely heavily on exporting data to excel for further analysis. Knowing how to answer business questions, get insights out of the data, and define or categorize sets of data are all enhanced by SQL. Report writing is not as much of a thing anymore because people want to view the data in diverse ways.

The barrier to entry is low with SQL, but learning it well takes time and some mistakes to get efficient and precise with it. 15 years later I am still learning new uses for it. One example is JSON querying and transformation which is supported by hive, presto, and some other compute platforms. It's easy to mix and match JSON, arrays, and tabular data structures, in one or more tables, from the same SQL query.

dswalter• 7 years ago

Presto's set of functionality ( ANSI-compliant SQL plus so much more) is a pleasure to use as a data scientist who does a bunch of analysis. JSON, arrays, maps, dates, HLL, spatial stuff, rich aggregation functions, etc.

The only real problem with it is that it's easy to get into some thorny-looking transformations because there is so much to work with.

jlj2• 7 years ago

One way I've solved for this in presto (Athena) is creating a table that flattens the JSON, and views on the table to un-nest arrays or show different grains of data for different use cases. Then transformations and joins to other tables become very simple.

This works well for my current area because the JSON has a consistent and well defined schema.

richardw• 7 years ago

Started with raw light SQL, went to ORM's, then joined a team with heavy SQL background. It's been a superpower since. A very reusable skill.

diggernet• 7 years ago

In University, I needed one more CS elective course, and the ONLY class available that fit my schedule was SQL. I had no interest in SQL, wanted nothing to do with it, and only took the class under duress. Even to the point of admitting as much to the prof in a casual conversation (he was a good guy, easy to talk to).

Within two months into my first job out of school, I was assigned to implement a SQL parser as a modern new interface for an ancient proprietary database. Every job since, I've written tons of SQL queries. SQL rocks.

Life is funny that way.

rswail• 7 years ago

The most important skill that knowing SQL has given me is the ability to think in terms of sets and declarative ways of defining what I want the end result to be (I believe the fancy term is the "projection" :) ).

Yes, it can appear to be magic to people who don't think that way, but doing things with window functions etc and understanding what the output of EXPLAIN means have helped unbelievably when improving performance etc.

gigatexal• 7 years ago

I’ve met far too many devs who hide behind ORMs instead of writing raw SQL and it makes me sad. There’s so much one gives up by allowing an ORM to handle everything for you.

Vektorweg• 7 years ago

I believe, that the way data is structured in Prolog is easier and more flexible than in SQL. Sadly I was busy with other things, so I can't tell for sure how it looks for bigger projects and I haven't tried Datalog yet. But I wouldn't consider SQL as a holy grail, after I run into various issues when dealing with not so relational data.

bayesian_horse• 7 years ago

I know SQL. But I tend to download data from SQL databases and then use Python and Pandas to deal with it, even though most things could be accomplished in SQL more efficiently.

Maybe Python is more convenient, cognitively? Pandas and SQL are very similar in concept, though.

drej• 7 years ago

Valuable yet fairly common.

I remember interviewing at FAANG and being asked to code up various tree traversal algorithms... and moments later I would be asked to write window function aggregations in SQL. And it was like this for all the interviews with that company - it was fairly bizarre as I wasn't sure what the aim there was. I understand that SQL is omnipresent, but surely people with algorithmic knowledge would be able to pick up SQL in hours or days, while the opposite doesn't quite hold.

(Oh and I agree with the article and I do like SQL for all sorts of workloads, that's not the point.)

saagarjha2• 7 years ago

I understand that SQL is omnipresent, but surely people with algorithmic knowledge would be able to pick up SQL in hours or days, while the opposite doesn't quite hold.

As someone who knows essentially zero SQL, is this really true? How long would it take me to learn it well enough to be competent with it?

FroshKiller2• 7 years ago

In my experience, "algorithmic knowledge" doesn't indicate that you'll be any damn good at SQL.

I think the time it takes depends on what competence looks like for your use case. If we're talking about just retrieving records that match certain criteria, you might get over the hump of understanding joins faster than a non-programmer, and you could probably write 90% of the queries you'll ever need in a week or less.

Reporting is different. Designing schema is different. Troubleshooting data issues in a poorly designed database is way different. Tuning queries and indexes for performance is way different. Not all use cases include those things.

fogetti• 7 years ago

My two cents (I haven't read the blog yet so there is that): everybody thinks it's important but when you mention table inheritance or materialized views then the loudest (how to call them not to hurt their feelings...maybe proponents(?)) are the most clueless of all the folks.

roystonvassey• 7 years ago

Can't agree more. SQL is the one skill that I continue to use across a 15-year span while many others have come and gone - SAS, SPSS, Clementine and name-your-latest-plug-and-play-analytics platform.

It is a skill with the highest bang for buck and is a great way to introduce non-programmers to a basic querying language. It is intuitive, easy-to-read and unbelievably versatile.

bonesss6• 7 years ago

As an app developer & architect I'm continually surprised by how often I bump into other techs on projects where BIG EFFORT is being put into all kinds of crazy ORM shenanigans, or subsystem development, or integration because people didn't understand how the underlying database platform could be managed through SQL or how the underlying data could be manipulated through SQL.

Developing an data-driven app without understanding how your data-management solution works is like using just one rollerskate. Sure, you can get places, but...

jtms• 7 years ago

I consider myself lucky to have begun my career in earnest at a time just before ORMs became the norm in web development. I wouldn’t say I am an expert, but wow has the sql experience developed early on paid dividends over and over throughout my career. I feel like postgresql in particular is one of the most powerful pieces of software I have ever worked with. You really don’t need much more to build a robust backend!

xpil3• 7 years ago

SQL is for data systems what IP protocol is for networks: it is the neck of the hourglass. You can build plenty of various things on it (the top of the hourglass: applications, reporting frameworks and so on) using various underlying technologies (the bottom: storage engines etc.) but you can't remove the neck without breaking the hourglass.

This is why SQL language and IP protocol are two most valuable things in computer world.

veritas32416• 7 years ago

You've intrigued me with this comment mainly because I know SQL quite well but IP not at all. Is wikipedia[0] for IP a good reference to dive in a bit? Would you recommend something else to grok why IP is so fundamental?

[0] https://en.wikipedia.org/wiki/Internet_Protocol

xpil3• 7 years ago

There is not much to grok really. It's just that there's plenty of low level transmission protocols like Ethernet, PPP or CDP, there's plenty of even lower level physical media types like copper or fiber or pigeons. Then there's a lot of higher level things that rely on IP: TCP/UDP, ATP, SPX and so on, and then the entire universe built upon them. But in the middle there is just IP (v4 or v6) and that's it.

mediaright• 7 years ago

[digs out copy of "The Manga Guide to Databases" I bought in school as a gag...but actually turned out to be really good]

forrestthewoods• 7 years ago

I don’t know SQL. What’s the best resource to learn?

xpil3• 7 years ago

ROW_NUMBER(), LAG() and MERGE are the three most powerful operators I use in SQL that are out of the average Joe's knowledge. They are simple to learn yet extremely useful.

Also, after 25 years in the IT business I still can't write a proper PIVOT clause without googling it first. Shame on me.

(btw I'm a MSSQL nerd. Been in Oracle/ODI world for a couple of years but it was too scary)

nazri1• 7 years ago

Ha I could say the same thing about my $EDITOR, which happens to be vim. I learned how to use it about 2 decades ago and I still use it almost daily since. Some things are really worth investing your time learning them.

tapanjk• 7 years ago

I think the power comes from knowing something -- _anything_ -- well.

I feel the same about vim and common unix tools (bash, sed, awk etc). If you can use these effectively, these can be very effective productivity tools. The learning curve is steep, just like learning to ride a bicycle, but once you do, it is difficult to imagine life without it.

forinti• 7 years ago

Learn to use analytic functions and you'll become a query guru.

growlist• 7 years ago

I also love SQL! Spatial extensions are really valuable as long as you get your spatial indexes built.

dsego• 7 years ago

Reading the comments here is like someone with years of experience with jQuery saying how jQuery is simple and powerful and nothing will replace it. SQL is to relational databases what jQuery is to the DOM, only shittier (maybe like mootools) and refuses to die, probably because all these SQL experts aren't really good programmers. Reading some compare SQL to 70s style procedural code tells me they haven't moved on from expert beginner territory.

FroshKiller2• 7 years ago

I find that programmers with a chip on their shoulder about SQL have poor SQL proficiency. And I don't mean "poor" in the sense of not knowing about window functions. I mean coding a query inside of an enumerator and wondering why they get feedback about poor performance in production.

saint_abroad2• 7 years ago

Reading some compare SQL to 70s style procedural code tells me they haven't moved on from expert beginner territory.

SQL is a declarative set-oriented language. Read/writing SQL as a procedural language will cause a heap of trouble.

philonoist• 7 years ago

Given such positive response, I want to learn SQL.

What are some good resources to learn?

I need to learn from the very beginning and it would be helpful if it had practical training excercizes as part of the course.

Max_Mustermann• 7 years ago

You can check out these 2 sites with practical exercices:

https://www.dataquest.io

https://campus.datacamp.com

I've also seen this free online Stanford course recommended a lot:

https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/about

johnmarinelli• 7 years ago

I'm a frontend web developer, but for a year I worked on a custom ETL system and wrote lots & lots of SQL. I still find myself using knowledge I learned back then and applying it today - for example, being able to review BI's queries is immensely useful. I'm also much less intimidated when I look at backend code, since a lot of it is interfacing with a database in some way.

I think SQL is one of those essential "secondary" skills for developers.

phonebucket• 7 years ago

Another aspect of SQL that I have come to appreciate is it's underlying philosophy: state what you want at a high level with a strict syntax; in exchange, your backend will make aggressive optimisations for you.

This sort of paradigm really made me understand that writing 'high-level' code and performant code are not mutually exclusive.

gherkin1• 7 years ago

You seem like a superhero. You seem extra powerful when you know it because of the amount of people that aren’t fluent That's exactly what I said to newer developers about SQL. Another two examples of this sort of skills are git and regex. Most people knows how to use them but don't dive into them that deep and you just need to spend very little time -hours, or days at most- and you will better than most people at them.

veritas32416• 7 years ago

I fell in love again with SQL while working in my current position. What _really_ made me fall in love was the project dbt[0] which is a SQL compiler and executor. You can build a DAG of transformations all the way from your raw data to tables ready for viewing in your BI tool or consumption by your ML model. I'm still amazed at the things I can do with SQL alone without having to bring Python into the picture.

Also, I had the pleasure of meeting Craig at PyTennessee a few years back. Really great guy and yes, he does seem to wear a hat all the time!

[0] https://www.getdbt.com/

yittcvz• 7 years ago

Same experience here. dbt has been a pleasure to work with, and putting it on Redshift with Redshift Spectrum has proven a great pipeline for our startup.

_b4ut• 7 years ago

I can't remember a single job interview where I was asked SQL questions. But I can say that I conducted dozens of interviews for developers and almost always asked for some simple queries.

The first question would be for the statement retrieving an entire table without any restrictions or conditions and 50% would fail giving that query. If somebody would manage to actually build a query involving a JOIN, WHERE and a GROUP BY then that interviewee is pretty much hired ...

Dowwie• 7 years ago

I highly recommend the SQL and relational data modeling books by Joe Celko, Bill Kirwin:

    - https://www.goodreads.com/author/show/93496.Joe_Celko
    - https://www.goodreads.com/book/show/7959038-sql-antipatterns

OliverJones2• 7 years ago

If you're early in your career and looking for something useful to learn, SQL's a good bet. It will probably outlast every other part of the tech stack you now use. Everything the author says about SQL is true.

But, like asphalt for roads and aluminum for airplanes, it still can stand a lot of improvement. By the way, it's just as important as asphalt and aluminum, and consequently just as hard to change. That's the curse of the customer base.

I wish the SQL world could agree on standards for string manipulation, and stored function / proc programming.

miguelrochefort• 7 years ago

SPARQL: The most valuable skill.

vardump• 7 years ago

I wonder if we'll one day get something like SQL, but that is based on hypergraphs instead.

Correct me if I'm wrong; I understand you can represent any relational model in hypergraph. And a lot that aren't representable in relational model, but are natural in hypergraph form.

_bohm• 7 years ago

Fascinating; I've never heard this. Can you recommend any reading material on the topic?

emiliosic• 7 years ago

It still surprises me, although it shouldn't, that many developers try to 'hide' SQL through (in mi opinion, convoluted) ORMs. Although I see the apparent beauty in the simplicity they provide to front-end developers, to me, additional layers will always add complications down the line.

arendtio• 7 years ago

While I find SQL useful too, there are other things I value even more than my SQL skills.

Being able to use a POSIX Shell with tools like curl is certainly one of them as it enables me to connect different technologies on a very practical level.

Furthermore, understanding the basic functional programming principles is invaluable if you want to build clean algorithms (doesn't matter if you use Excel, C or Lisp).

richpimp3• 7 years ago

It's very interesting how many application developers shun SQL. I believe a lot of it is due to a pervading sentiment that SQL is unruly or inelegant. There are certainly quirks to the language, and it is a difficult transition to think in a declarative rather than imperative manner, but once you make the jump, it's an invaluable skill to have. Fortunately, I have the luxury of being able to manipulate our codebase from any level of the stack. What this means is that rather than jump through hoops on the API or frontend side to accomplish some task that would require reams of code, I can just get my output via a simple query, and it will perform faster in almost all cases.

From an analytics point of view, I can't imagine not using SQL. I've seen people pull reports from multiple websites, text files, etc., spend an entire day manipulating them in Excel, and still not get their data model working as expected, not to mention that it is very slow. A couple of queries with some temp tables and voila, magic happens. It really does make you look like a superhero when you can deliver more accurate results in a fraction of the time it originally took. I'm surprised there isn't more of a market for this skill, surely there's a lot of programmers from the 80's and 90's who have this skillset in abundance.

ben7799• 7 years ago

I learned SQL around 1996 or 1997 and had an absolutely fantastic college course or two involving it.

I've mostly been a backend developer... having the ability to go in and fix SQL and make things run in < 1 second instead of 5-10 minutes has been one of the best skills I could have picked up.

It is sad that so often "self described 10x programmers" build solutions to go around SQL that are horrible failures. Poor use of ORMs, weird abstractions at the application layer that force developers to use poor data access patterns, unnecessary "locking" at the application layer, unnecessary "existence checks" at the application layer, processing objects 1-by-1 in the application. All these things lead to terrible performance and huge wastes of application memory/IO.

I love some of the NoSQL solutions too as in some cases they can force teams to use better data organizations/patterns and scale so well. Those patterns are often possible in an RDBMS but the system doesn't guide a team to using those patterns. The way CQL in Cassandra forces you to think about data organization is great for example.

rafiki6• 7 years ago

The only thing I'll disagree with is "SQL is permanent". Nothing is permanent. SQL will live for a long time, but eventually it will be displaced by something else once something displaces RDBMS as the defacto data storage standard of the world and chooses some other interface. That being said, learn SQL, it really is a very very useful thing to know.

childintime• 7 years ago

Well, I'm not a fan of SQL. To me the math underpinnings are not well exposed, probably because the language doesn't "see" obvious relations and that because of this, joins must be performed explicitly, over and over again, and come to dominate the query.

Isn't it obvious that when a column has the name of a table that the idea is that the columns of that refactored out tables become available as if it were a part of the main table?

When the query works regardless of the structure of the table, the database can be refactored with ease. Most such queries would simply specify column names, and a filter to apply on the records. Any joins needed, and which result from the structure of the database, would be inferred.

Such a "NoJoinDB" would clearly boost productivity, and lots of applications could be written with no explicit join at all. A language like SQL seems to hide the simplicity of most queries!

Please comment about the validity of this perspective.

scatterwilds• 7 years ago

"like the other mechanistic software theories, the relational database model is a pseudoscience; that it is worthless as a programming concept; and that the relational systems became practical only after annulling their relational features, and after reinstating – in a more complicated form, and under new names – the traditional data management principles." --Software and Mind, pg. 676

SQL: One of the most valuable skills

Related Stories