19 ms·

SQL: One of the most valuable skills

• hbarka 8y 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).
• Hippocrates 8y ago
  I would say the same about bash.
  • diggan 8y ago
    With enough bias, you can say the same about anything :)
    • bordercases 8y ago
      You could also say that about bias.
      • pupdogg 8y ago
        I haven't chuckled the same way in a while until reading your comment. Thank you for making my day!
  • emilsedgh 8y ago
    Products in production environments aren't relying on Bash that much anymore. Nowhere near how they depend on SQL databases.
    • whynotminot 8y ago
      I've never worked on a product of any kind where I didn't have to have solid command line knowledge to truly trouble shoot an issue. Especially in production, and especially if you don't necessarily have perfect control over the environments you're deploying to.
    • ehnto 8y ago
      I have never seen a production environment not use bash for part of the deploy process, if not all of it. I have seen a lot of prod environments.
    • opportune 8y ago
      This does not at all echo my experience
  • all2 8y ago
    Ooh, I have some resources for bash that I keep coming back to. - https://www.gnu.org/software/bash/manual/bash.pdf https://www.gnu.org/software/bash/manual/bash.pdf - https://devhints.io/bash https://devhints.io/bash - https://www.gnu.org/software/bash/manual/bashref.html https://www.gnu.org/software/bash/manual/bashref.html
    • tingletech 8y ago
      this is my goto bash source https://www.tldp.org/LDP/abs/html/ https://www.tldp.org/LDP/abs/html/
  • wvenable 8y ago
    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_tayler 8y 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 8y ago
    As someone currently employed as a .net developer, I’m not sure I would say the same.
• tigerlily 8y 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 8y ago
    There are a lot of great free resources out there, but I had recently shared https://sqlbolt.com https://sqlbolt.com with a friend that wanted to learn SQL and she found it useful.
    • tigerlily 8y ago
      Wow, this is fun, thank you! I completed the Lesson 1 exercises first try!
  • zappo2938 8y ago
    https://www.sqlteaching.com/ https://www.sqlteaching.com/ https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ https://blog.codinghorror.com/a-visual-explanation-of-sql-jo... https://pgexercises.com/ https://pgexercises.com/
    • tigerlily 8y 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 8y ago
    Mode Analytics tutorial is well laid out: https://mode.com/sql-tutorial/ https://mode.com/sql-tutorial/
  • xupybd 8y 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 https://schemaverse.com/tutorial/tutorial.php If you're on MSSQL server this guy is great. https://blog.sqlauthority.com/ 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 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.
• whynotminot 8y 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 8y 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.
    • whynotminot 8y 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.
      • tingletech 8y ago
        wait to you have a performance issue in prime time and you need to refactor your query in a hurry
      • james_s_tayler 8y ago
        This is pretty much where I'm at. Though I'm on a mission to really nail fundamentals this year and SQL plus query tuning is the next stop for me. I've got 20 hours of courses related to SQL fundamentals lined up Pluralsight and another 30 odd hours SQL Server / Query Tuning. The half-life of the knowledge is so long it's just a big boost for a really long time to become an order of magnitude better at it.
• dhbradshaw 8y 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.
  • craigkerstiens 8y ago
    That does sound in our wheelhouse. If you drop me an email happy to setup a time to talk.
  • jweir 8y 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/ 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#connection-permissions https://devcenter.heroku.com/articles/heroku-postgresql#conn...
    • jpatokal 8y 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.
  • codetrotter 8y 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.
• aboutruby 8y 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.
• GolDDranks 8y 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?
  • AlisdairO 8y 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.
    • GolDDranks 8y ago
      Thanks, I'll check it out!
  • cpburns2009 8y 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.
  • mongol 8y ago
    I recommend you look at one of Markus Winand's presentations, such as this one : https://youtu.be/xEDZQtAHpX8 https://youtu.be/xEDZQtAHpX8 I felt it summarized well the modern foundation of SQL databases.
    • GolDDranks 8y ago
      Thanks, I'll be sure to watch it!
• vietvu 8y ago
  The more queries I write on Elasticsearch, the more I value SQL.
  • rb808 8y 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 :(
    • vietvu 8y ago
      Well, at least I can do simple query with SQL and Elasticsearch and on Hive.
  • deleted 8y ago
    [deleted]
  • lucidone 8y ago
    I really miss being able to make arbitrary where clauses to adjust to feature changes.
  • NDizzle 8y 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.
• deleted 8y ago
  [deleted]
• iblaine 8y 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.
  • opportune 8y 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 8y 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.
• max76 8y 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.
  • justinclift 8y 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).
    • veritas3241 8y 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!
      • SamuelAdams 8y ago
        I would add indexes to that list. Knowing the different types and how they impact performance can be very valuable.
        • veritas3241 8y ago
          I didn't add indexes mainly b/c for analytic warehouses that are columnar, indexes are less important / meaningless. Partitions though, that's important!
• neokantian 8y 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.
  • kccqzy 8y 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.
    • neokantian 8y 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.
• jsnk 8y 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 8y ago
    C
  • war1025 8y 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.
    • war1025 8y 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-feedback https://www.goodreads.com/book/show/18114120-thanks-for-the-...
      • yesenadam 8y ago
        Thanks, will have a look. Crucial Conversations was recommended to me on HN, it's also super-useful - on how to talk about difficult/important things, whether in relationships or organizations or everyday life, where most people would be afraid and say nothing. How to speak difficult truths and ensure everyone feels good about it. Not many people would be advanced enough to not get a lot from that book, I think.
  • abraae 8y 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.
• kerng 8y 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.
• JimmyAustin 8y 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.
  • dwd 8y 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.
    • AmericanChopper 8y 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.
      • dwd 8y ago
        xp_cmdshell always seemed like a really bad idea. The sql server agent was easy and reliable for scheduling so it made sense to let it run the whole process.
• nicoritschel 8y 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.
• deleted 8y ago
  [deleted]
• hardwaresofton 8y 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.
  • Scarbutt 8y 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.
    • busterarm 8y 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.
    • hardwaresofton 8y 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/ https://www.openpolicyagent.org/ [1]: https://news.ycombinator.com/item?id=18156144 https://news.ycombinator.com/item?id=18156144
  • fdsak 8y ago
    I think one of major reason for being powerful is its bases on pure mathematical foundations.
  • brazzledazzle 8y ago
• physcab 8y 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.
  • wvenable 8y 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.
    • physcab 8y 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.
• anorman728 8y 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 8y ago
    Why do you have to use the query builder?
    • anorman728 8y ago
      Because my CTO requires it.
  • aitchnyu 8y ago
    Name and shame? Would you feel the same way about this? https://pypika.readthedocs.io/en/latest/2_tutorial.html#selecting-data https://pypika.readthedocs.io/en/latest/2_tutorial.html#sele...
    • anorman728 8y 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_shot 8y 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.
  • rjbwork 8y 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 8y ago
      Do you mean this one from Stanford? https://lagunita.stanford.edu/courses/Engineering/db/2014_1/about https://lagunita.stanford.edu/courses/Engineering/db/2014_1/...
• berbec 8y 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.
• kumarvvr 8y 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 ?
  • ramraj07 8y 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 8y 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.
    • kumarvvr 8y 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.
  • fdsak 8y ago
    A good starting point could be understanding https://en.wikipedia.org/wiki/Declarative_programming https://en.wikipedia.org/wiki/Declarative_programming
• zzzcpan 8y 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.
  • busterarm 8y 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.
    • zzzcpan 8y 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 8y 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.
• triplee 8y 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 8y 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 8y ago
    ROW_NUMBER is absolutely the best, especially for marketing related customer data.
  • nimajalali 8y 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 8y 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 8y 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 8y 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_gill 8y 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 8y 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 8y 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 https://twitter.com/minimaxir/status/1094430576092704768) As a data scientist, I spend a lot more time building queries than working with models.
• jlj 8y 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 8y 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.
    • jlj 8y 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 8y 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 8y 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 8y 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 8y 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 8y 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.
• deleted 8y ago
  [deleted]
• bayesian_horse 8y 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 8y 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.)
  • saagarjha 8y 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?
    • FroshKiller 8y 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 8y 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 8y 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.
  • bonesss 8y 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 8y 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!
• xpil 8y 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.
  • veritas3241 8y 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 https://en.wikipedia.org/wiki/Internet_Protocol
    • xpil 8y 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 8y 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 8y ago
  I don’t know SQL. What’s the best resource to learn?
• xpil 8y 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 8y 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 8y ago
    +1 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 8y ago
  Learn to use analytic functions and you'll become a query guru.
• growlist 8y ago
  I also love SQL! Spatial extensions are really valuable as long as you get your spatial indexes built.
• dsego 8y 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.
  • FroshKiller 8y 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_abroad 8y 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 8y 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 8y ago
    You can check out these 2 sites with practical exercices: https://www.dataquest.io https://www.dataquest.io https://campus.datacamp.com 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 https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/about
• johnmarinelli 8y 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 8y 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 8y 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.
• veritas3241 8y 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/ https://www.getdbt.com/
  • yittcvz 8y 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.
• rv-de 8y 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 8y 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
• OliverJones 8y 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 8y ago
  SPARQL: The most valuable skill.
• vardump 8y 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 8y ago
    Fascinating; I've never heard this. Can you recommend any reading material on the topic?
• emiliosic 8y 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 8y 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).
• richpimp 8y 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 8y 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 8y 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 8y 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 8y 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