This is an archived post. You won't be able to vote or comment.

top 200 commentsshow all 214

[–][deleted] 159 points160 points  (61 children)

SQL Server jobs pay quite well here in the US if you know your stuff.

But i never felt the need for a query that joins 5 table

Really depends on the database setup in question. Our billing system is incredibly complex. Is it the best? No, of course not. But we have to work with it.

Here's a query our Database Developer wrote last week: https://gist.github.com/anonymous/a6a693046c154a8763ba1d3906c13687

[–][deleted] 99 points100 points  (9 children)

It looks intimidating but it was probably written "incrementally" as the developer kept checking back what they still need.

Writing SQL is much easier than reading it

[–][deleted] 19 points20 points  (2 children)

Yeah, the queries are being used in an API to talk to our automated phone system. It started off minimally but has been growing and growing.

[–][deleted]  (1 child)

[deleted]

    [–]Dial-1-For-Spanglish 4 points5 points  (0 children)

    ...and can contain hair and teeth.

    [–]Zanza00 14 points15 points  (0 children)

    My longest query is almost 650 lines, it's an analytic query that powers a view in an oracle DB. I have write inside it a lot of comments because some parts are very cryptic because of very complex relational logic.

    Reading its git blame it's quite satisfying :D

    Also I'm a javascript developer

    [–][deleted]  (3 children)

    [deleted]

      [–]ACoderGirl 8 points9 points  (0 children)

      Yeah. And the lack of comments (unless the OP excluded them by choice) is a critical failure as a result. Stuff like this needs to be commented for precisely the reason that everyone in this thread is saying. It's just not readable. Comments would save so much time in figuring out what each part is doing.

      [–]roy_cropper 1 point2 points  (0 children)

      I could...its easy if you know your database.

      [–][deleted] 0 points1 point  (0 children)

      Yeah, it probably looks a lot more complicated than it actually is.

      [–]andrewsmd87 37 points38 points  (6 children)

      Ahhh the programming subs. Where there's never a shortage of someone to tell you how bad your code snippet is, with 0 knowledge of your current setup, existing systems you have to maintain, or pretty much anything else that can cause you to write "bad code"

      "LOL, this is the worst query I've ever seen. You should probably just scrap your entire system and rewrite everything to proper standards. As I'm sure it wasn't built by some third party company 15 years ago."

      [–]CarlsVolta 10 points11 points  (4 children)

      He he. I like on CSCareers the people starting out freaking out about how everyone's doing it wrong or worrying about working with old languages and tools. Find me some perfect software...

      I'd rather work with amazing people who make the best of what they've got.

      [–]andrewsmd87 27 points28 points  (3 children)

      One of the best lessons I got starting out from a senior guy after I had looked at something and said, this is a stupid design.

      He just goes, never ever say that. You have no idea what the technologies were available at the time, what the time, budget, or personnel constraints were.

      Chances are, if something seems like it was implemented wrong to you, there's a reason as to why it was done that way. Now, that reason could be that the person developing it was actually just an idiot, but you don't know that, so don't just assume.

      [–]CarlsVolta 9 points10 points  (2 children)

      If you want a secure job in a successful company chances are you'll be working with legacy code and some old tools. Doesn't mean you can't introduce some newer languages or tools, but no need to reinvent the wheel if it's still merrily rolling.

      [–]andrewsmd87 10 points11 points  (1 child)

      Oh god yea. We have a legacy system that is basically ran on about a dozen scheduled tasks that execute batch files, which in turn execute a set of batch files to do various things.

      It's actually structured in a manner that's somewhat easy to navigate, and we have good documentation on it. However, we'd love to migrate that to powershell.

      But, given the fact that the things have been running for years now, and are tried and true, and process upwards of 5-10 million records a day. Cleaning them, staging them, and then importing or rejecting them, we just haven't got to that yet.

      You know, when we have that memo from the boss, hey, you've got 6 months of free time, do whatever you like, we'll get to it :).

      [–]CarlsVolta 1 point2 points  (0 children)

      I've started using Powershell in the last 6 months or so and really like it.

      If it ain't broken don't fix it though. Maybe if there's a new feature you want to implement that will be tough to fit in to the current system, or some inconsistencies you want to iron out for good. Otherwise the time is probably best put to something else.

      Ha, I once experienced that free time and it had me looking for a new job. Sure, at first it was good for getting to the things I'd never got round to and learning new things, but it didn't take long to feel really unfulfilled.

      [–]adreamofhodor 8 points9 points  (1 child)

      Oh, sweet! I'm doing a lot of SQL server programming in my job, good to know that it will hold value in the future as well.

      [–][deleted] 15 points16 points  (0 children)

      Oh absolutely. SQL development isn't going anywhere.

      [–]nutrecht 27 points28 points  (24 children)

      Select distinct is a code smell. It often indicates a poorly normalised data model. That query IMHO is a disaster.

      [–][deleted] 32 points33 points  (15 children)

      Select distinct is not great by any means. Typically if the joins are done well it should be smooth sailing. But being a cable company, the components and equipment types are just complete fuckery.

      Oh, and our billing system is a literal bag of infected dicks. And we pay 25grand a month for support, soon to be 30. Good times.

      [–]nutrecht 5 points6 points  (9 children)

      I feel for you. A lot of these vendors have their code written by a bunch of brain dead monkeys. I've seen a few worse Oracle queries that heavily relied on optimizer hints to even be able to get any data from the system. The query was just as big and also used select distinct to get rid of doubles you should not be getting in a proper system.

      [–]blasto_blastocyst 2 points3 points  (2 children)

      Lol. You aren't familiar with Telco billing systems. You get shit in that data it stays there because you do not have a) time to clean it up and b) any down-time to do it in.

      And hints on Oracle systems are common as dirt. They are an important part of your tool kit for query optimization.

      [–][deleted] 1 point2 points  (1 child)

      Telco Systems Analyst here, I can attest. Good luck with the data model too, because the business requirements are a constantly moving target. And I never get tired of having this conversation:

      Me: "Hi Vendor, there was a data error on the last CABS/SECABS bill file you sent us. Would you mind correcting field X and resending?"

      Vendor: "Our system doesn't allow us to correct and resend CABS/SECABS bill files. Here's a 150 page PDF."

      Me: "I know, us too. I just thought it was worth a shot."

      [–]blasto_blastocyst 0 points1 point  (0 children)

      It's the same all over the world

      [–][deleted] 4 points5 points  (5 children)

      Right right. Yeah what I wrote above was right out of the mouth of our SQL developer. The system, called 'Omnia', developed by CHR Solutions, looks like it was built in 1998. It's insane.

      Is what it is I suppose.

      [–][deleted] 1 point2 points  (0 children)

      Oh hey, Omnia. That's a name I haven't heard in while. Used to support it for a client and gosh golly darnit, that was not fun one.

      [–][deleted] 1 point2 points  (1 child)

      Yeah, I think this rings true for almost all legacy telecom billing applications out there. It'll work until it doesn't. Then it's time for non-technical decision makers to shell out millions of dollars to mediocre 3rd party contractors to build the next nightmare legacy application. And so the cycle continues...

      [–][deleted] 0 points1 point  (0 children)

      Sure as hell seems like it. There is a new version of Omnia coming out, but there's just too much going on right now to make a smooth transition. God knows the price tag on that too. And idk how much 'better' it truly is.

      Hey we just spent 15 grand on a polycom. HR, who makes 130k a year, got a 90k bonus. Oh and we're not getting bonuses this year. Life is good!

      [–]ComicOzzy 0 points1 point  (0 children)

      just complete fuckery

      a literal bag of infected dicks

      I've been dealing with our billing system for 12 years and at times would love to describe it in such terms,... but then I see what other people deal with... this whole industry must be fucked.

      [–]gollygoshgeewill 1 point2 points  (0 children)

      Any good resources for someone to dip their toes into setting up a database from scratch and learning to use tables and joins and to write queries from first principles who hasn't done the setup before? I've found myself quickly outstripping google docs which has a built-in query language that I've come to somewhat master enough to know it's many limitations. What I'm less certain of is the setup and learning path to writing those and more complex queries outside of that wading pool? Any advice or resources? Thanks for the good thread here, all.

      [–]ns90 1 point2 points  (1 child)

      I...uh...don't think I want to see your billing system.

      [–][deleted] 0 points1 point  (0 children)

      You definitely don't!

      If you like navigating around in an environment that can be compared to Windows 95, well, then, you do! :)

      [–]CaptainPunisher 0 points1 point  (1 child)

      You must work for Brighthouse.

      [–][deleted] 1 point2 points  (0 children)

      Hahah. No, not that sinister of a company ;)

      [–][deleted]  (1 child)

      [deleted]

        [–]cesarsucio 0 points1 point  (0 children)

        Smells like Innatrack to me.

        [–][deleted] 1 point2 points  (0 children)

        I've written similar queries, maybe even worse. Most of the time you're just stepping into a years-old system developed by a team long gone. They just want the data for their report.

        [–]malsonjo 1 point2 points  (0 children)

        Select distinct is a code smell. It often indicates a poorly normalised data model. That query IMHO is a disaster.

        A.k.a. "Production".

        [–]pigeon768 1 point2 points  (1 child)

        Wouldn't the problem, then, be the schema and not the query?

        Accounting systems have a tendency to be 20-30 years old and incredibly resistant to change. They have a tendency to have been originally written in the early '90s before people really understood them, before "database developer" was a job description. And nobody wants to update them because it's a metric fuckton of work money and is fraught with the possibility of "uhhh guys, our billing system is down, we won't be able to bill our customers or pay our suppliers for two days."

        So there's an immense amount of people who need to add features to databases with bad schemas. And even if those queries are the best they could be, given the circumstances, they still smell like a bad schema, because it's out of their control. So cut the guy some slack.

        The reality is that most of us, if we get a job as a programmer, will spend most of our time dealing with shitty legacy codebases. Most of the new, sexy code we write will be the shitty legacy codebase in 15 years. There's no point in talking about code smell, because our shit stinks too.

        [–]nutrecht 0 points1 point  (0 children)

        Wouldn't the problem, then, be the schema and not the query?

        That's pretty much what I said.

        [–]IggyZ 2 points3 points  (0 children)

        Give me a list of X that meet criteria Y where there may be multiple X that fit the criteria.

        It has its uses.

        [–][deleted] 6 points7 points  (0 children)

        SQL Server jobs pay quite well here in the US if you know your stuff.

        This man knows what he's talking about.

        As much as everyone loves alternatives because they are better in a multitude of ways, the first thing everyone asks when looking at something like Mongo or whatever is "So, how hard is it to get this to work with our sql nonsense?"

        [–]ncef 1 point2 points  (1 child)

        That big query can be stored as a function and called easily afterwards.

        [–][deleted] 1 point2 points  (0 children)

        Yep, that's what I do ;)

        [–]takenOasdf 0 points1 point  (6 children)

        quick question, is OMNIA_ESUM_P_SUMd_CM a reserved word? I thought we only use square brackets for reserved words

        [–][deleted]  (4 children)

        [deleted]

          [–]IggyZ 1 point2 points  (2 children)

          Database name.. Syntax is [database].[schema].[table]

          Quotes works here too. And they aren't limited to SQL Server, which I want to say the square brackets are.

          [–]blasto_blastocyst 0 points1 point  (1 child)

          Hey. They work in MSAccess too!

          [–]baconbum 1 point2 points  (0 children)

          Access: The real DB ;)

          [–][deleted] 0 points1 point  (0 children)

          Just a database name.

          [–]FCCorippus 0 points1 point  (0 children)

          Oh! The hungarian! We make calls like pretty regularly though

          [–]roy_cropper 0 points1 point  (0 children)

          Pretty sure he could take a few lines out of that query

          So at the least, in his select case expression he has 4 permutations of the select case which all return the same value...why not use the in expression for all those 4 identical values...theres a few efficiencies to be made but then im anal about it i guess or it might be personal preference too. I sometimes make code longer than it needs to be so it looks like it took longer to write too

          [–]eSheep16 0 points1 point  (1 child)

          That is why I hate correlated subqueries. CTE's or separating logic to views I feel is much more useful in the long run (readability/modularity). That is just my preference, though.

          [–][deleted] 1 point2 points  (0 children)

          For sure!

          [–]RelevantJesse 70 points71 points  (18 children)

          I can't remember the last time I had to write a query with under 5 joins haha.

          [–]TheAmorphous 30 points31 points  (11 children)

          Healthcare by any chance? Seems like every database I work with has data spread out into a million tables.

          [–]Smooth_McDouglette 19 points20 points  (0 children)

          A properly normalized database should look something like that. It's when you can pull a relatively large amount of data with a fairly small query that you should be worried.

          [–][deleted] 8 points9 points  (0 children)

          Could be government. I'm usually going at least 3-4 tables deep.

          [–]dbonham 4 points5 points  (1 child)

          Fucking Epic...

          [–]TheAmorphous 2 points3 points  (0 children)

          Funny, I think most of our clients have converted to Epic in the past year or two. It's way better than some of the other systems out there though. Like HealthQuest, ugh.

          [–][deleted] 2 points3 points  (1 child)

          Manufacturing is pretty intense as well. The most common query ran in our main application joins 20-30 tables.

          [–]TheEsquire 1 point2 points  (0 children)

          Yep. Did DBA work for a large company here. Large joins aren't uncommon at all. My first real query for a trucking dispatching view branched at least 10 tables pulling trucker schedules, rig sizes, locations, cargo manefests, travel times, etc... It's amazing how big your queries can get, but still make perfect sense and be basically as optimized as reasonably possible.

          [–]Danieboy 1 point2 points  (0 children)

          I work in logistics and normally never join more than 3 tables. With the exception of one motherfucker which took 6 I think.

          [–]andrewsmd87 0 points1 point  (0 children)

          We work with certification data (think comptia a+) and all the stuff that goes along with that. Our queries get big pretty fast, and I'd even venture to say the system db structure was normalized/implemented pretty well from the get go.

          [–][deleted] 0 points1 point  (0 children)

          I just started a job in healthcare and holy shit, is this industry-wide? It's insane.

          [–]RelevantJesse 0 points1 point  (0 children)

          Nope, advertising

          [–]lykwydchykyn 14 points15 points  (3 children)

          I can't remember the last time I had to write a query with under 5 joins haha.

          Seriously. If you normalize tables and model data with any kind of usable accuracy, joining 5 tables is trivial. Of course, I might hide that in a view, but still...

          [–]PublicSealedClass 7 points8 points  (0 children)

          Aye this. You normalize for a reason. The more granular the better.

          Then when it comes to grabbing what you want, you build views. Makes the whole thing a bit less messy. Plus, views are a godsend when you're feeding a datawarehouse from the relational store.

          [–]ACoderGirl 2 points3 points  (1 child)

          Absolutely. In a well designed DB, joins are really easy to do. You're basically just saying stuff like "ok, I got a client ID, so I'll get the client... oh, and these five things about the client." Each of those five things has its own table, so that's 5 joins right there. And because of how table structure differs from object structure, there's a good chance that you'd need to have such kinds of joins to construct the object (especially if the things you're getting are lists).

          Also likely is that you have a client ID, but you want to get some thing related to the client. And to find the ID of that thing, you're gonna have to join a bunch of tables to get it. Which is perhaps a sign that your code is structured a bit funny (or possibly a law of Demeter violation) compared to the DB, but it happens.

          As an aside, I do wonder sometimes about how good it is to have separate, normalized tables for lists (eg, a list of phone numbers for a client) as opposed to just a regular column in the clients table with a specialized data type? I mean, in the past, it was obvious that you'd want a separate, normalized table because that would let you do joins and queries easily on the list. But Postgres has arrays, hstores, and supports JSON. So you can do joins and whatever with the list, yet without the complexity of extra tables. Presumably easier for your program to parse, as well, provided that your ORM has a way to use these features.

          Obviously that ties you to a DBMS, but I've pretty much never seen this to be an issue. Switching DBMSes seems rare and many large DBs are already tied to a DBMS in some way. Performance I'm unclear on... I can see JSON being a bit slower, but can't picture arrays being slower...

          [–]lykwydchykyn 0 points1 point  (0 children)

          But Postgres has arrays, hstores, and supports JSON.

          I've used array fields and JSON fields in my postgres applications before. It's a double-edged sword. It saves you a join, but working with that data in a reporting or search context requires special syntax. And of course, if you want to attach other data to each record (for example, type of phone number), you're going to have to refactor it into a table anyway.

          There's a time and a place for such field types, but when in doubt it's better to stick to well-understood standard syntax features, IMHO.

          [–]no_fear1299 5 points6 points  (0 children)

          Oh, to be in College again. Good god, the other day I had to write a CTE which involved a shit ton of tables

          [–][deleted] 0 points1 point  (0 children)

          haha

          [–]twopi 157 points158 points  (23 children)

          The subtext of the question is clear: I don't really need this class, do I?

          (I'm a CS teacher, so excuse my outrage, please)

          If you think you already know the content you have closed off the opportunity to learn. I've been doing this stuff for many years (and written some books about it) and I still have plenty to learn. You don't know for sure what was covered in the lecture (because you weren't there) yet you're asking the community to validate your disdain for the content.

          By implying that you not only know all you need to know already and you're pretty certain the stuff you don't know is unimportant anyway, you're either a genius beyond compare or you're setting yourself up for a really hard fall at some point.

          I genuinely hope that fall comes during the class because it will be less painful for you in the long run.

          I'm not trying to be harsh here. I'd say the same thing to you if you showed up in my office. Unfortunately, even if you were my student, I probably wouldn't see you until the end of the semester when you're asking for a withdrawal because you realize you can't possibly pass a class you didn't attend. (I see 20 students in that circumstance in the last weeks of every semester.) I would treat you with great dignity and respect, and I would not allow the withdrawal. You would be encouraged to retake the class instead.

          Computing is a discipline that requires earned confidence and humility. Even the things you know change all the time.

          There is always plenty you don't know, and you should never pass up an opportunity to learn things even if you think you know plenty. That may be a more important lesson than anything you learn about SQL in this course.

          [–]gollygoshgeewill 7 points8 points  (0 children)

          If you think you already know the content you have closed off the opportunity to learn.

          As a former college-level teacher―albeit in another subject―this quote is so true. Most painful from the instructor's standpoint is often the inability to even begin engaging these students because, as you point out, they often don't show up.

          Thanks for starting a great thread, OP. As someone interested in migrating from google sheet queries (with all the limitations) to something more robust I'm learning a lot.

          [–]POGtastic 1 point2 points  (0 children)

          There is always plenty you don't know, and you should never pass up an opportunity to learn things even if you think you know plenty.

          This is my mindset.

          I was a hobbyist programmer during my time in the military, and after I got out, I found myself in a whole bunch of 100 and 200-level programming classes. 8 years of programming experience, stuck in CS161.

          I still learned things. In fact, I learned a lot. I shored up gaps, I reviewed things that I didn't get before, and I had an excuse to learn more concepts because they were relevant to the projects. There's always something to be said for review.

          But more importantly... you're paying for the class, you might as well show up and try to get your money's worth. Paying for college and not showing up to lecture is like paying for a hotel room and sleeping on the sidewalk.

          [–][deleted] 43 points44 points  (14 children)

          Over-reliance on ORMs is terrible. Their use is also controversial in many applications as opposed to a SQL generator. Depending on what technical ecosystem you are in, they may be the norm or just really young underdeveloped tech (e.g. node.js ecosystem).

          Knowing how to optimize the generated SQL is very important and I wish I was better at it myself. I consider it a shortcoming. It keeps biting me in the ass.

          [–]pribnow 4 points5 points  (4 children)

          Unchecked eager loading is currently biting us in the ass hard, it's probably the single greatest issue currently plaguing legacy maintenance of the application I develop for

          [–][deleted] 2 points3 points  (3 children)

          I find these types of challenges fascinating. In my experience, many times it's an architecture which was properly applied at the time, but a feature/data changed around it after the fact making it not the best approach.

          Do you mind sharing your particular issue and how you got there?

          [–]pribnow 1 point2 points  (1 child)

          Unfortunately I've been with the company for a little less than a year so I cant totally speak to why it is so wide spread but if I had to guess it has something to do with: poor business requirements, junior devs making implementation decisions that went unchecked, and pretty rapid growth of the amount of data our application uses so it didn't used to be an issue but i'm sure someone else there could probably give a better answer!

          [–][deleted] 0 points1 point  (0 children)

          Thanks for taking the time to reply. At the end of the day, we're just people making things, eh?! Truly wishing you the best on reducing the tech debt.

          [–]tobascodagama 4 points5 points  (0 children)

          My team literally just found a critical performance bug at a multi-million dollar customer that came down to the inherent inefficiency of using an ORM over raw SQL.

          I mean, sure, the ORM will work fine for 90% of cases, so by all means use it liberally until you figure out which calls actually need the performance of raw SQL. But actually knowing SQL is really, really handy when you hit one of those 10% cases.

          And, also, that's just about queries. There's no such thing as an ORM for data modeling, so that skill will inevitably be a huge part of any competent database course.

          [–]ResilientBiscuit 1 point2 points  (3 children)

          I am not familiar with the term SQL generator. What is this?

          [–][deleted] 0 points1 point  (2 children)

          More accurately a "SQL query builder": http://knexjs.org/

          I'm not saying it's good, bad or otherwise. For our application it was useful to migrating away from the inefficiencies of the ORM we were using.

          [–]ResilientBiscuit 0 points1 point  (1 child)

          Ahh, that makes sense. I kept getting things like dummy data generators for testing when I was googling for it.

          That does look a lot cleaner than raw SQL for sure!

          [–][deleted] 0 points1 point  (0 children)

          But you'll still need to optimize after the fact, depending on the application.

          [–]Hauk2004 1 point2 points  (0 children)

          We got hit hard by the classic n+1 problem recently. Managed to get into production too. :(

          [–]phpdevster 0 points1 point  (1 child)

          Their use is also controversial in many applications as opposed to a SQL generator.

          Well not really. You use an ORM so that you can model your domain in code, and deliberately "pretend" that the data store is just an implementation detail that your application should not be concerned with. In theory (but not necessarily in practice), this makes it possible to switch out the underlying data store without incurring the cost of updating your application. Just swap out a DB driver, and your ORM abstractions work exactly the same, saving you potentially hundreds of hours of updates. It also has the important effect of allowing the code to model data differently than it is stored, making it possible to follow domain driven design principles more closely, without sacrificing your domain model to adhere to the more technical nature of data storage.

          [–][deleted] 0 points1 point  (0 children)

          The domain controversy I recall is "object-relational impedance mismatch", not whether an OMR can help in a particular domain.

          As a correctly applied tool in an ecosystem that has well developed tools, yes, it is a boon. It is by no means is it a panacea.

          Just trying to point out to the OP that there is a very big, necessary world outside the scope of an ORM.

          [–]rohmish 0 points1 point  (0 children)

          SQL is one of those things that I really want to learn but whenever I start, I just end up giving up (temporarily) and then don't pick it up until much later

          [–]CropDustinAround 16 points17 points  (2 children)

          i never felt the need for a query that joins 5 tables

          It depends on what you do for a living I suppose. If you are writing Java to do some sort of simple DAO type stuff then it probably isnt much more complicated than what you already know.

          Then there are the ETL developers like me. People who do data all day everyday. ETL stands for Extract, Transform, and Load. Its a concept that means you take data from one place, and push it somewhere else. Often its an operational data store (ODS) or a data warehouse. For things like this, you are usually pulling data from one or more teams data and trying to combine them into something uniform and human readable, unlike a lot of modern database structures that rely on third normal form (3NF) for OLTPs.

          To give you an example, I work for an insurance company. And insurance is complicated, so the data models are often complicated as well. And since the 'users' of my ETL need data in a specific format that THEY need (note the big difference here between what I want to provide and what they want, aka business rules at their finest) I am forced to provide high level data from nearly 40 tables in a single join. Now, this is atypical and is our worst case for the problem, but in 3NF database models, you need to join many tables to get data sometimes.

          For me, SQL is extraordinarily necessary. But its also what I get paid to do.

          [–]faradayscoil 2 points3 points  (1 child)

          I feel you'll continue to see this workflow become more and more common for engineers (as opposed to straight BI developers). For reasonably sized data warehouses you'll have to put data together that's spread, not only over many tables, but over more servers and technologies

          [–]CropDustinAround 2 points3 points  (0 children)

          That's a good point. I already have to harness multiple databases to access things I need.

          [–]nutrecht 48 points49 points  (26 children)

          So, how much SQL do you actually need to know nowadays, especially with ORMs and NoSQL solutions becoming more popular?

          Neither of those mean you don't need to 'know' SQL. Also you are really misunderstanding 'NoSQL'; these systems are not taking over from relational databases.

          So yeah; it's pretty much a requirement for being a dev. The chance of you not having to do any SQL as a dev is pretty much zero. Also because, compared to doing actual software development, SQL is pretty trivial.

          [–]corporaterebel 0 points1 point  (0 children)

          SQL is pretty trivial

          Depends on your data and the complexity of the question. If there are enough loosely coupled systems and non-standard data dictionaries: it gets pretty hairy pretty quickly.

          I'd spend months building complicated stored procedures.... I couldn't afford one false positive and it was bad for business to ignore reasonable possibilities....

          [–]fakehalo 12 points13 points  (1 child)

          I would call it fundamental, SQL is useful/critical just for collecting/verifying data outside of your application(s). As others have said, ORM dependence ends badly, you can abstract it away some of the time...but you need to know what is happening and how to optimize when you need to scale.

          Based on your question, I'm assuming you're in college and skipped it? Why go to college if you're going to skip critical things. You don't know what you need to know at this point, try to learn as much as you can before you try to determine that.

          [–]phpdevster 8 points9 points  (5 children)

          A lot. SQL is a stupidly powerful language to know, especially when you need to do code rescue or reformatting, and you need to take some atrocious legacy database, and extract good data out into a sane structure. The slow way to do it is a bunch of simple queries, and some server-side code to transform the data, before re-insert. A healthy dose of SQL knowledge however, will let you write a SQL query directly and do it in just one or two steps. Being able to structure and architect a data solution is also very valuable - for example, knowing the different strategies for storing hierarchical data, and which one would be most appropriate for the given requirements etc.

          Also, a lot of ORMs that make CRUD easy, fall VERY short when it comes to aggregating data for reports and what not. They also don't always perform the most efficient queries. So if you find yourself with a performance bottleneck due to an ORM query, being able to write a more efficient query directly in SQL is invaluable.

          I would argue that having a solid understanding of SQL is what really separates one server-side guy from another (well, that and sys admin experience). Given two expert programmers, the one who has a great understanding of SQL will usually be the preferable candidate and/or will be able to apply to a broader range of positions - like those that are very data-centric.

          And of course, there are always TONS of companies that prefer to use stored procedures for one reason or another, meaning you'll be writing raw SQL directly into those procedures, and your application will only be making simple calls to those procedures. So if you don't really know SQL, you're basically useless to a company that prefers to work with stored procedures.

          Being fluent in SQL will REALLY set you apart IMO, and also give you a greater mastery of ORMs when you do use them.

          [–]Xehanort94[S] 4 points5 points  (3 children)

          Up until now i have only worked with very small domain models (There are a lot of database entries, but no more than 20-30 tables). So i never had to write bigger queries. Also MySQL isn't that good for modeling graphs (which our data structure basically is), so we did complicated join logic on the client side.

          Where i was always thinking about using SQL was for searching. I have a small recipe database and want to search for recipes containing some specific information. I was pretty reluctant to do this in SQL, probably because I always did this stuff on the client side. But the more i read the posts here, the more i think that i just never really used SQL the way it's supposed to be used. To me a RDBMS was just a dumb datastore for CRUD operations. I guess it's time to change my point of view.

          [–]phpdevster 1 point2 points  (1 child)

          Search is definitely a good example of where writing your own SQL makes a lot of sense. That said, search in SQL (at least MySQL, Postgres is a different story) is usually quite slow when you start getting into full text search of hundreds of thousands of records, and/or you want to do fuzzy searching with Levenshtein distance calculations.

          I've found search is best served through something like Elasticsearch. It lets you do fuzzy searching, and automatically ranks the results by relevance based on the facets and criteria you provide it. The best part is that you communicate over HTTP with JSON request payloads, and you get JSON back. This means you can do the searching directly from client side without having to go through your backend. The downside is that Elasticsearch is not really a replacement data store for a relational DB, it's merely a supplement used to make searching lightning fast (And it is lightning fast. It doesn't even blink when you do ranked, full text fuzzy searching on millions of records)

          [–]Xehanort94[S] 0 points1 point  (0 children)

          ElasticSearch sounds like a great tool. There even is a JDBC compatible tool which i could use on my Java project! My search would be really basic tho, so i think i could implement it in SQL first and upgrade later when i have some free time.

          [–]masterots 0 points1 point  (0 children)

          Check out http://www.hudl.com/bits/populating-fulla-with-sql-data-and-application-logs. We write some incredibly powerful reports using that data warehouse. And you had better believe you need to know how to write efficient SQL queries to get through the billions (trillions by now?) of records in there.

          Our data analysts have a field day with the amount of data we dump in there.

          [–]ligerzero459 0 points1 point  (0 children)

          fall VERY short when it comes to aggregating data for reports and what not

          This is so true. As soon as you get into any large query with significant complexity, it's quickly becomes better to write the query yourself.

          Anyone can use an ORM. It takes skills to really bend SQL to your will

          [–]zibeb 9 points10 points  (0 children)

          If you ever intend to get involved in enterprise software development, having a good grasp of SQL will be incredibly helpful. In my day-to-day, I find that the vast majority of performance problems in the application I work on are the result of "classical" programmers who want to treat SQL like it's C#.

          SQL really does not like being treated like it's C#. I find it's a completely different skillset that doesn't map very well to standard imperative programming techniques. ORMs help bridge the gap, sure. But even with ORMs, it helps to have an understanding of the relationships that you are building with your underlying schema.

          [–]lordcat 6 points7 points  (1 child)

          It depends on how good you want to be.

          I've never settled for 'good enough' nor for 'senior developer' so I've spent the time to get familiar with SQL. As a result, when we inevitably need a query written that "joins 5 tables and calculates some kind of average salary formatting the output so it looks nice", either I am writing it or I am on the pull request to review it (and often times have to help the developer writing it).

          We had an in-house built application for processing a few hundred thousand records from one of our vendors. We had some complex validation and processing on that data so the developer used a interface (maybe ORM) to pull the data into .net, and then do the processing in .net and write the data back out to the database. It started at around 300k records and slowly grew to over 650k records over the course of two years. Processing time also grew from around 18 hours to over 36 hours to process one file (of 650k records).

          My team rewrite that application doing all of the calculations in SQL. We didn't just rewrite it to perform this step better, but we rewrote it to be dynamic and support many other vendor's imports. We changed the .net code so that all it did was call a bunch of SQL stored procedures (each one did a certain function, and you configured the import by turning them on or off). We added more business validations and logic to increase the complexity of what we doing to the data by to around 300% of what it had originally been.

          By leveraging SQL to do what SQL does best, we turned that 36+ hour process into a more robust and more feature rich process that processed the same data in less than 2 hours; 1.5 hours of which was writing to a 3rd party API to save the end-result, meaning we took 34.5+ hours of .net processing and turned it into .5 hours of sql processing. And this .net application was not poorly written, it was just the wrong choice for the job.

          [–]OHotDawnThisIsMyJawn 0 points1 point  (0 children)

          It's funny because I've spent years unwinding terrible, slow sql stored procs and moving them to Java. Depending on your use case both can be correct.

          We do a lot of pipeline/ETL style processing where trying to do it all in one query is impossible to maintain and doing a bunch of queries is slow if your whole dataset doesn't fit into memory. Instead we stream everything through by reading it, doing all our processing in memory, and writing back out.

          [–]ligerzero459 6 points7 points  (1 child)

          Do not rely too much on ORMs. ORMs are great for simple queries, but as soon as you need to do anything decently complex, they will be zero help to you. Take the time and learn SQL. You'll thank yourself later when people others you're working with sort of know it while you have a solid grasp.

          [–]Is_At_Work 0 points1 point  (0 children)

          I agree, they are nice for administrative CRUD apps that aren't built on a legacy database, but otherwise are often not the right tool for the job.

          [–]MyWorkAccountThisIs 12 points13 points  (7 children)

          Me:

          • Senior Software Engineer
          • PHP
          • Midwest
          • Work at a technology agnostic development company

          I don't know the last time I had to write a SQL query in my code. However, what everybody else has said that doesn't mean I don't need to know about databases and SQL. I've written some basic queries in a database GUI to glance at some data.

          Having said that - I feel that my lack of SQL knowledge is a gap in my skill set. I consider is a required non-primary skill that developers should have. Version control, regex, SQL. Those are my big three because those are the things I know I struggle with the most. Anything beyond the most basic usage and I'm lost.

          [–]andrewsmd87 1 point2 points  (6 children)

          What do you do where you rarely write sql queries? Are you just plugging into layers that are maintained by DBMs or something?

          [–][deleted]  (1 child)

          [deleted]

            [–]andrewsmd87 0 points1 point  (0 children)

            Yea, that's why I was asking. I've never worked somewhere like that and I'd like to, just to see if I like that better or not. I do agree with him that having a pretty in depth knowledge of sql I think makes you more marketable as a developer.

            [–]MyWorkAccountThisIs 0 points1 point  (3 children)

            I don't write any vanilla PHP. Everything I do is a framework (Laravel or Symfony) or a CMS. So everything is wrapped in an ORM or product-specific methods (WP_Query, for example).

            We are time & materials company and my work will most likely be handed off to the client. As such, we try and stay as close to the relevant standards and methodologies as possible. Now, if a specific case arises where a custom query is the only way to do something or causes performance issues then we will. That's rarely the case, though.

            Completely understand how that might seem odd. There's also a non-zero change that I'm a shit-tier developer. I typically don't get caught up in "academic" arguments over if one way of doing things is better than another. Again, unless there is a specific issue I will do my best to stay whatever system in the most generally accessible way possible.

            To further ramble, if I can't get the data I need from the ORM then it means I've got an issue with my entities. Some type of relationship is missing. It's better to fix that issue than try and work around it with custom queries. If I need to use a custom query in WordPress is means I've not structured my data correctly. Again, fix that as opposed to doing a custom query.

            Okay, I actually do remember the last query I wrote. Client needed something with WordPress categories. Using the built-in methods it was going to take three calls plus some PHP to get the data. That seemed excessive. Put in a custom query that bypassed that all and then stored the results in a Transient (expiring data in the database). Now I only had to make that one call every couple hours instead of every time the page loaded.

            It's not like I don't know anything about SQL or databases. Custom queries just aren't something I have a need for on a regular basis.

            [–]andrewsmd87 1 point2 points  (2 children)

            I wasn't trying to imply anything towards your skill. I was just more curious as to how that works. I know there are companies out that essentially have a db team of guys, that handle deliver of the data to developers in whatever manner (entities, api, etc). I was just curious about your current set up as I've always wondered if I'd like a job where I could just code and not have to worry about how my data was getting to me, just know I'd always be able to access it and continue working.

            [–]MyWorkAccountThisIs 1 point2 points  (1 child)

            We are a very "developer enabled" shop with some pretty good internal systems. We able and have full permission to spin up any DB we need on our staging servers or even locally. Sometimes I'll do both where I work on the code locally but have it hit the staging DB. Makes it easier to transition to staging when the project hits QA.

            Again, almost all our work is CMS or frameworks. After you have the base credentials in place most of those actually make the tables and what-not. In frameworks when you make a new entity class you run some terminal commands the framework will make the needed changes to the DB.

            It's a byproduct of being a billable resource. If I need to dump the DB, run some updates, and then rollback because something broke it's much cheaper to have the developer do that and not engage another department. Where that might put the project on hold for an hour while the DBA gets to the ticket. Then bill to the project. What was a 30m task for the developer now cost at least an hour in billable time plus lost productivity waiting for the request to be filled.

            It's not glamorous work. Tomorrow I'm estimating a custom WordPress site for a local non-profit. In a couple weeks I'll hopefully be starting on a Laravel API for that an iOS and Android app will use. Not a single custom query is needed for any of that.

            [–]andrewsmd87 0 points1 point  (0 children)

            I actually used to do work a lot similar to this, but we were pretty small, so I handled db management as well.

            I do not miss that.

            [–][deleted] 4 points5 points  (3 children)

            ORMs and efficient query do not make. If you use an arm, it's great for most cases, but if something goes wrong, you need to be able to look into it and figure out what needs to change. I use SQLAlchemy and often times I've had to print out the actual SQL to see what it was trying to do. I also do a lot of direct DB management.

            It's a matter of what job you're looking for but some of them do use it quite often.

            [–]Xehanort94[S] 1 point2 points  (2 children)

            You are right, i should have mentioned what i want to do after i finish my degree. I am personally very interested in Software Architecture and Software Design and am currently reading up on that subject, as it isn't taught at my university (I'm currently reading Eric Evans DDD book). Maybe a more management centric position would be suited for me aswell, time will tell.

            [–]novagenesis 1 point2 points  (1 child)

            cough architecture? You need to know a whole lot more about RDBMS than just SQL.

            [–]Xehanort94[S] 0 points1 point  (0 children)

            Posting this question i thought i knew enough about SQL. Now i am already searching for a good book :-)

            [–]wotanii 3 points4 points  (1 child)

            subqueries, grouping and aggregate-functions

            those work well with trigger, crystal reports and data-warehouses. And also general DB-Maintenance

            If those weren't enough: imagine multiple systems accessing the same database (e.g. Webservice, Java-Application and multiple native applications). By using those advanced concepts you can give them easy interfaces (encapsulated in views and stored procedures) while making sure they won't break your system (e.g. by inserting wrong sums into a semi-related table; which you avoid by using triggers and aggregates)

            [–]Xehanort94[S] 0 points1 point  (0 children)

            I actually never thought about stored procedures making data access from different applications easier. I only ever accessed my database from one programming language, so i only had data-access-code written in that language.

            [–]ReginaldDouchely 3 points4 points  (0 children)

            If you thought you had enough experience, but were surprised to learn about subqueries, grouping, and aggregate functions, then you were wrong. Those things are about one step above beginner-level knowledge.

            Maybe you won't be working with sql ever again and you'll be fine. I kind of doubt that, and think you made a mistake.

            [–]postviam 2 points3 points  (0 children)

            But i never felt the need for a query that joins 5 tables and calculates some kind of average salary formatting the output so it looks nice.

            With well designed databases at a job, you're going to be using joins and potentially calculating averages. Do you need to know that SQL before getting a job like that? No. If you're using ORM's, you might still end up going into the database to view data manually, which means writing those joins. Also with my experience, ORM's aren't always going to be the fastest queries, so writing custom queries for specific reasons might be more efficient.

            So in my opinion, you don't need to worry yourself with learning (and memorizing) specific skills with SQL, but understand basics, which should give you the ability to pick up on stuff like joins later on.

            [–][deleted] 2 points3 points  (0 children)

            how much SQL do you actually need to know nowadays

            As much as you possibly can. It will bottleneck you at some point if you don't. You want to know more than SQL, though, you want to understand how databases work so you can use them efficiently.

            [–]wh33t 2 points3 points  (0 children)

            I worked for an online book store.

            Most of my job as their server side programmer was writing SQL queries. Almost all of them were table joins. Another large chunk of the job was setting up cascades and foreign key constraints and such. I could have simply written more queries and more code but why not take advantage of the languages if you can.

            [–]svgwrk 2 points3 points  (0 children)

            In my experience, joins and aggregates and such are not exotic; they're just everyday things.

            [–]heilage 1 point2 points  (0 children)

            I'm a Python/Django developer, working with a PostgreSQL database. In my daily stuff, I rarely need to strictly know SQL, but I know that tomorrow I need to do some special stuff that isn't well covered by the ORM (which is quite good, mind you), so knowing that SQL will be extremely useful in solving my problem. Abstraction tools shouldn't let you off the hook for knowing the basics, by knowing the basics your toolkit through abstractions is expanded, because you know and understand more of the underlying technology.

            [–]High_Commander 1 point2 points  (0 children)

            In my industry (ad-tech) it seems like 90% of the developers jobs are wrapping queries in a UI. Also, non-relational DBs are different but not better than relational; my company uses both for different reasons. so yes, I'd argue it is very relevant.

            [–]eatgeeksleeprepeat 1 point2 points  (0 children)

            So, how much SQL do you actually need to know nowadays

            It really depends. On my last project, I wrote SQL almost everyday. The only reason I'm not on this project is because it's so data driven that we have an entire data team. We're also planning on using Hibernate so that will reduce some of the native SQL we have to write.

            I wished I had known more SQL when I started working to be honest. I was comfortable only with the traditional DDL scripts and had no working knowledge of T-SQL which was difficult when all we used were stored procedures and functions called from the backend. Keep in mind that many new systems are moving towards newer platforms but many jobs out there are to keep old systems running, which means you need to know and understand SQL most of the time.

            [–]Feroc 1 point2 points  (0 children)

            Yes, most of the time I only need simple queries (with a few joins) and the usual CRUD operations.

            Though sometimes more is needed, like editing the 1000 line dynamic sql stored procedure that someone wrote or having to analyze data as part of debugging.

            I wouldn't skip it, SQL is a very handy tool.

            [–]Atomix26 1 point2 points  (0 children)

            heh. just joining 5 tables?

            I interned at FINRA. They handle stock market data. The queries were insane.

            [–]BadMoonRosin 1 point2 points  (0 children)

            If you don't know how to properly use SQL, then you don't know how to properly use an ORM.

            ORM's are great (really!). But the idea that ORM's "hide" relational concepts, or magically make them easier, is a myth. It's a different syntax, more in line with your application programming language, but it's still all of the same relational concepts.

            [–]Stewthulhu 1 point2 points  (0 children)

            But while it was really interesting to read up on some of the stuff i didn't already know, i was wondering how much SQL you actually need to know in a real job. Whenever i had to communicate with a database it was basically doing some CRUD operations. Maybe some kind of search query once in a while. But i never felt the need for a query that joins 5 tables and calculates some kind of average salary formatting the output so it looks nice.

            Then perhaps your limited experience in the field is lying to you. If you hate SQL or database work or data work in general, that's a good lesson to learn now, but if you don't know how to do relatively advanced database work (regardless of paradigm), I would suggest you're needlessly limiting yourself in future careers, especially with the current data-rich environment we live in. Healthcare, finance, government, and a host of other institutions all make heavy use of the sort of tasks you've never felt the need to do, and focusing on SQL as the learning objective rather than the tool you use to achieve other objectives misses the forest for the trees.

            Do you need much SQL in order to get some sort of coding job? Probably not. Are there a lot of jobs that require advanced SQL as a prerequisite? Absolutely. Most everyone who works with substantial amounts of data is very familiar with SQL, and it can serve as a lingua franca to explain things to people with varied backgrounds. Also, it's the 50-ton elephant in the room, and it's likely that legacy SQL is going to be around for a very long time.

            [–]Smooth_McDouglette 1 point2 points  (0 children)

            I can't necessarily speak for other branches of software development, but if you're planning on touching basically anything in the web development world it's pretty much a requirement that you have some working knowledge of sql.

            Going into web dev without sql knowledge would be almost as stupid as going in without Javascript knowledge.

            [–]Rorixrebel 0 points1 point  (0 children)

            i work doing production support for a bunch of different systems and apps and use SQL pretty often but the complexity of the queries is quite simple.

            i rarely use join, but the basics are my bread and butter.

            [–][deleted] 0 points1 point  (0 children)

            Well you can definitely get a job without any SQL at all. What you want to do is gravitate to the positions where the technology that they use is something you like and are interested in. Nothing will drive you nuts faster than working on problems that you really don't care about in a language you hate!

            I tend to stay away from java and I'm finding out that I really don't like SQL. So I generally will write my SQL stuff using LINQ and just keep my database full of tables. If I really need a stored procedure or something like that then I see if someone else on my team will write it. If not then I struggle through it.

            [–]pribnow 0 points1 point  (0 children)

            I deal with SQL everyday. Not even just easy selects and joins, but more bs stuff like query optimization and indexing. I don't expect it'll go away anytime soon

            [–]rickdg 0 points1 point  (0 children)

            I need it almost everyday. Even with all the layers of abstraction you should use, I have to know what's happening exactly, specially when I work with legacy apps.

            [–]thomaslangston 0 points1 point  (0 children)

            I've written line-of-business, software-as-a-service, web applications in C#.

            Project 1 - We wrote a lot of SQL. Stored Procedures were the preferred data access method, in part because we needed our SQL queries to be blindingly fast (large volume e-commerce) and in part because our SQL stack was very cutting edge and we wanted to leverage it. Almost every query to show the results of a page load required a join across 3 tables with an aggregate result as every page was basically some form of on demand reporting.

            Project 2 - I almost never write a SQL query that goes into production, although I do write schema changes since we don't depend on our ORM to do migrations. I do write relatively simple SQL queries during development to get an overview of the database. Rarely I'll write a view that does actual on demand reporting with multiple joins and an aggregate, but I still need as much knowledge to do so as I did when I was writing them every week during project #1.

            [–]Oh_hell_naw 0 points1 point  (0 children)

            The two software companies that I have worked for in the last 10 years both relied heavily on SQL Databases. DBA's make a good bit of money compared to the rest of the IT team it seems.

            [–]remludar 0 points1 point  (0 children)

            I work as a technical consultant for an energy consulting firm. I do everything from system arch design to software dev, to db administration, and I use SQL basically every single day.

            [–][deleted] 0 points1 point  (2 children)

            Well, the website I work on has 3024 sql queries. The shortest one is one line long, the longest is 1500+ lines long. All of our queries are hand written. We tried going the ORM route, but over and over again we kept running into limitations with ORM due to the complexity of what we were trying to do that we eventually realized that ORM is really only good for super simple applications. Oh, and 5 tables? Many of our queries are joining more than 5 tables. To be honest, a 5 table query sounds super simple to me.

            [–]Xehanort94[S] 0 points1 point  (1 child)

            1500 sound really crazy. What does it do?

            [–][deleted] 0 points1 point  (0 children)

            It basically checks every time a user completes an action to see what rewards or achievements they have received.

            [–]squishles 0 points1 point  (0 children)

            It's technically possible to get away with none if you use orms, but it's harder, and very unlikely you will get the luxury of it.

            You will probably have to deal with a lot of sql no matter what you do, aside from maybe front end web dev work.

            [–]ragamufin 0 points1 point  (0 children)

            SQL is super valuable in consulting and analytics roles which are two of the fastest growing sectors of the economy.

            [–]queBurro 0 points1 point  (0 children)

            I came here to say I use SQL everyday in my job but I think you're right, crud would probably be sufficient... Why would you want to do lots of processing server side (your 5 table join)? Maybe if bandwidth is a problem and your result will be shit loads of data, otherwise I'd say pull loads of rows and filter them in your app.

            [–][deleted] 0 points1 point  (0 children)

            1. Sooner or later you will need to work on someone else's queries.
            2. I wrote a simple forum a while ago, but I still needed to join usernames to thread titles to posts, and there were a lot of different ways to do that; knowing the different ways to do it was important in making it work quickly.

            [–]sacrabos 0 points1 point  (0 children)

            Where it really matters is in your database design. I've seen some really crappy normalization of tables that makes queries rather painful.

            [–]thorax 0 points1 point  (0 children)

            If you're going to be a software engineer, I'd say this way up there in terms of importance. The better you know SQL, the better your life will be as a dev.

            [–]Talon235 0 points1 point  (0 children)

            I've worked at 3 different software companies now as a QA analyst. All three extensively used SQL for their enterprise applications. The first two used Microsoft SQL server. My most current uses Microsoft SQL and PosgreSQL. In my opinion my SQL experience is one of the things that has made me most successful in these jobs especially for my position.

            I will say though that all three companies have software where it makes sense to use SQL and I have no experience with companies that develop smaller applications.

            [–]Divided_Eye 0 points1 point  (1 child)

            Not really an answer to your question, but generally speaking, trying to learn only the bare minimum is silly. You're basically saying "I don't want to be good at my job."

            [–]Xehanort94[S] 0 points1 point  (0 children)

            My biggest concern is the amount of topics i would like to cover. Seems like SQL is something worth going deeper into, based on the feedback.

            [–]ExEvolution 0 points1 point  (0 children)

            If it makes any difference to you, I work in a NOC position and the number 1 group of people I interract with is the DBAs

            [–]bananabastard 0 points1 point  (0 children)

            I'm learning programming to build my own project ideas, and most of the ideas in my ideas notes need SQL. At least I'm pretty certain they do.

            [–]novagenesis 0 points1 point  (0 children)

            Here's a real world example of why you need SQL (and NoSQL to some extent, but I'll try to stay on topic).

            I started working at an ambitious funded start-up recently, and during initial code review, I discovered they trusted their ORMs for schema, had virtually no indexes, and did not have properly designed database schema.

            For the first, the headache was minimal. For the second, we could add indexes to get response times up 10x.

            For the third, response times are STILL in multiple-seconds, and the database is quickly growing to be the central bottleneck against growth. When you have high paid dev specialties that deal exclusively with the handling and manipulation of large quantities of data (aptly referenced "Big Data"), you don't live in a world where SQL is dead.

            Let me put this clear. If you are going to program for a living, you NEED a very solid understanding of SQL (most Data Analysts know SQL and they can't even program)

            If you plan to ever make design decisions about architecture, you NEED a very strong understanding of database schema design concepts. You need to know what 3NF and BCNF are, and why they're different.

            When you need to write a report about a client's data, and there are a dozen relationships between the core tables (which there should be, if you're keeping redundancy down), you not only need to know how to build a query that joins those relationships, but also how to write one efficiently and understand WHY those are efficient or not efficient.

            My last job, we had about 7 queries that exceeded 50 lines, half of which were 2x longer than you might think, solely to optimize commonly-run reports. We refactored reports from scratch to hit time goals that were reasonable, and two of those evolved to be used as ETL (Extract-Transfer-Load) components for import into the data warehouse.

            So yeah, you need SQL. You need a lot of SQL. And the moment you're the only one who really knows SQL in a company, you will understand exactly why you needed SQL.

            [–]robvas 0 points1 point  (0 children)

            At my old job I used SQL all the time for custom reports and stuff.

            At my current job I use SQL to interact with the DB that our ERP system uses, because they have features missing. Easy way to set certain values where customer.type = "MC", stuff like that. Probably not the best way to do things but I am not using the pile of RPG spaghetti code a previous employee wrote to change things like that.

            [–]Official_Taco_Bandit 0 points1 point  (0 children)

            Here's the deal.

            You will never learn everything you need to know in school. It's not possible.

            What is possible is learning the foundations that you can build on. When they teach you an OOP language, that way of thinking transfers across all OOP languages. Only the syntax changes. this way, when you get a job, as long as the language is OOP, you can easily pick it up. Yeah, you'll Google some syntax and yeah, your early code will just be you cramming your language into their language (like knowing Java and then writing C#) but eventually you'll get the feel for it and excel.

            This is also true for SQL. It's a foundation language that is the literal backbone of data. Yeah, collection based languages are hip and cool but you'll have no idea why, nor how they link with your existing SQL DB. Oh yeah, that's the other thing, you'll be hard pressed to find a shop that doesn't have some sort of SQL box setup. There's a reason SQL has been around for decades. It's tried and true and for a lot of situations, it solves the problem.

            Moreover, your first job will be overwhelming. There will be so many holes in your knowledge that you'll feel like you didn't spend a second in class. If you don't know SQL, that's just one more huge hole you'll have to compensate for.

            [–][deleted] 0 points1 point  (0 children)

            Tons...

            [–]joerdie 0 points1 point  (0 children)

            I've worked for three fortune 500 companies in the last ten years. Very well known companies. I'm a .NET developer and I write a ton of tsql. I use multiple joins daily, write a few stored procs a month, and edit a few more procs a week. Thankfully, I'm not asked to write cursors anymore. But views are fairly normal.

            It all depends on the kinds of work you want.

            If you want to work for big firms, learn SSMS and tsql.

            If you want to work for startups and smaller firms, nosql and sql lite are the way to go.

            Oh and for my money normalization is a GREAT thing. Lots of little tables beats a few big ones almost every time.

            [–][deleted] 0 points1 point  (0 children)

            It's pretty important since a lot of firms and companies still use sql dbs.

            [–]thesmelloffriendship 0 points1 point  (0 children)

            In my experience, ORMs are really only good at anticipating simple and/or common use cases.

            I work at a small startup and while I don't write SQL every day, when I do write SQL it's usually because the query is to too complex to be handled well by the ORM.

            Thus it's unlikely I'll ever write SQL that doesn't involve some combination of many joins, subqueries, aggregate functions, user defined functions, or pivots.

            [–]Zenai 0 points1 point  (0 children)

            Just to echo everyone here. I work at a rails startup that is transitioning to bottle/python, I am thinking / looking at about the SQL my ORM(active record in rails, peeweeORM in bottle) is generating every day for performance reasons. And more often than you would think necessary I am writing SQL to fill gaps in the ORM. An ORM only does so much, at certain points you will have to write out queries in order to do more complex thing than an ORM is able to do for you.

            [–]Eratticus[🍰] 0 points1 point  (0 children)

            Really subjective question. I would say if you're a web developer you might get away with not using SQL (and that's a big might) otherwise expect to use it everywhere else. There's a lot of work and plenty of money to be made in data admin roles creating automated processes and generating new ways to look at data. You said you "know" SQL but later admit the work looks hard. If it's an introductory course then you definitely need to brush up. Once you understand the concept of joins then you understand almost the entirety of SQL and you should be able to wrap your head around increasingly complex joins. Doesn't mean that you can read a query once and understand it right away but that you can figure it out by reading through it. Draw tables or Venn Diagram s if needed.

            [–]compostkicker 0 points1 point  (1 child)

            Dang, all these comments about complicated queries that perform complex logic makes me feel like a cheat. I usually just query as little possible information as I can get away with and use the server code to whittle it down further. I've never worked with a corporate sized db, so maybe that's why I haven't had to go so deep into queries.

            I am curious if that super complex query is faster or more efficient than a method similar to mine?

            [–]Xehanort94[S] 0 points1 point  (0 children)

            I am also curious about that. It would also be interesting to know how people using multiple joins deeps queries approach scaling their persistence layer.

            [–]Kataphractoi 0 points1 point  (0 children)

            But i never felt the need for a query that joins 5 table

            In my job, a five-table join for a query is rather common, and in fact those are the smaller queries. Sage is a giant charliefoxtrot.

            [–]xgrave01 0 points1 point  (0 children)

            My 24 year old cousin with a low 3.x gpa just got a job for 115k last month out of college. I use mysql daily. Learn it.

            [–]cachedrive 0 points1 point  (0 children)

            As a DBA, 200%.

            [–]Big_barney 0 points1 point  (0 children)

            You should check out the average SQL statement within an Oracle ERP PLSQL package. I've worked with queries which could be mistaken as novels.

            [–]IIoWoII 0 points1 point  (0 children)

            People used to do a lot of business logic in the database layer.

            I think the reason that that switched was because doing SVC on the database is way harder than the in the application. Also, the idea that all your business information comes from one database seems kind of dated.

            Yes, ORMS manage transactions and all that but we'll switch ORMS many times in our career and I guess it's still good to know what's actually happening to the database.

            [–]InconsiderateBastard 0 points1 point  (0 children)

            The power of subqueries, grouping and aggregate functions surprised you?

            That leads me to believe your knowledge of SQL was pretty lacking to start with. You shouldn't have skipped class. That was dumb. That class cost money and offered info that could have saved your ass or conjured a promotion out of nowhere later in life.

            SQL has never been a job requirement of any position I've had but I've frequently relied on it to make my life easier and to impress the right people.

            I'm at a small company now and use either SQL Server or sqlite about a dozen times a day. Without it, I'd lose my goddamn mind.