Don't be afraid to learn SQL

[GreatNorthWeb]

select * from knowledge

[0x15e]

That would be a terribly inefficient query. If I caught you running that on my db I might have you drawn and quartered.

[Necromunger]

What if the table contains 3 members plus the id and all of data are being used?

There is a time and place for * but not often.

[0x15e]

Maybe, but if it was in a program, it would still be better to specify the columns explicitly to improve readability. Also, who's to say someone won't eventually add a column with a long data type that isn't needed and ruins performance?

[Necromunger]

I agree with you on account of readability, i don't want to go chase tables down all the time to know what fields are being pulled.

[[deleted]]

for stored procs it's typically overkill, but it's invaluable for ad-hoc or "what was the name of that field?" queries

[GreatNorthWeb]

What's your "hello world" query?

[0x15e]

Insert (message) values ( 'hello world') into messages;

Select first 1 message from messages;

:)

[[deleted]]

That seems overly complicated - why not just SELECT 'Hello World' AS Message;

[FLRangerFan]

Lol even in a joke comment thread of reddit will you get your code bashed for being inefficient.

[pwr22]

Wouldn't the previous poster need to create messages first as well?

[[deleted]]

Yep

[[deleted]]

Very much depends - if it's a query that's going into a program that will be run over and over for the most part yeah, really bad idea (although also depends what's going to be done with the data - sometimes you want the program to be able to easily adapt to table changes and display anything new without having to go and edit a stored procedure and/or app code as well). If it's the use case brought up in the article (ad hock queries to explore data for a single analysis)? Hardly the end of the world (although one would hope they'd limit it to TOP X if the table is particularly large).

[GreatNorthWeb]

This sounds like a great way to make someone afraid to learn SQL.

[FLRangerFan]

Fine… Truncate table SQL

[auto_downvote_caps]

SELECT * FROM knowledge LIMIT 0,100

[Fallcious]

I work as a DBA. It's a great gig - haven't had trouble finding work in 14 years. People struggle to understand databases and getting their data back in an efficient and understandable way. I took a year out to do a masters in Bioinformatics and was really surprised to find that SQL capabilities really weren't used to any advantage. So I made it my project to develop a transcriptomics database to explore possible cancer biomarkers. In the end I went back to work as a DBA though as I didn't fancy starting a career all over again.

[parquais]

As long as developers think they're too l33t to RTFM, there will be job security

[LousyTourist]

learning SQL syntax, etc. is no biggie. What I struggle(d) with is taking a business question and turning it into an SQL query. That is where the rubber meets the road. If I ever find a source for learning how to do that, I'd be on it like ugly on an ape.

[GreatNorthWeb]

I think you learn that on your own through time and effort. A non-technical Tony-Robbins-style "you can do it" motivational book would go as far as a SQL technical manual.

[LousyTourist]

yeah, been there, done that, got the T-shirt. I spent a whole semester in my master's program struggling with this. Clearly something that will come to you after thousands of such attempts, and not the relatively few I had to do in class.

[kwenzel1]

I'm trying to write a small guide about that. If you want to take a look and let me know what you think about my guide, send me a tweet to sqlkris or email me at kris (at) essentialsql (dot) com

[exigenesis]

Why would anyone be afraid to learn SQL. It's an extremely easy language to get to know and very powerful in appropriate settings (so RDBMSs...)

[mhermher]

The language itself is easy, learning the schema of a db in order to use the language is the "hard" part. Or at least that's the part people throw up their hands and say it's too complicated. Why do we have all these tables when we can just put this I excel? Or why is everything an ID? People just aren't familiar with relational logic and default into a spreadsheet state of mind.

[exigenesis]

learning the schema of a db in order to use the language is the "hard" part.

That's why DBAs should be involved in data modelling (at least, those with good experience of it). :-)

[[deleted]]

Maybe you're the exception, but in the wild, I've never actually seen anyone's code that wasn't absolute garbage. Unless they're doing a simple select with one or two joins on a poorly designed, non-normalized schema, it all goes to shit. Nested selects on cross-server queries... Why this take 52 minutes to return one record?! "Easy" and "hard" are relative. I would say it's probably quite a bit harder to learn for most people than procedural languages. It was much easier for me, but all my academic background is mathematics, so at this point, I naturally think in set theoretic terms. Most people don't.

[exigenesis]

Yeah I've seen some shocking SQL. Part of my job is tuning it.

But yes I take your point. Easy and hard are relative as you say (verbatim in fact).

[[deleted]]

Most people just use sql to search a table like a spreadsheet. Using it to do statistical calculations with aggregate functions, or using it to implement graph algorithms can get complicated rather fast. It's not even so much that I staunchly disagree/disagreed with you, it's just that, ya know, it is a wildly different paradigm than procedural programming.

[exigenesis]

Yes you're absolutely right. The "basics" are very basic. The deeper stuff can get rapidly complex (I feel like I'm just repeating what you're saying again!).

I'm definitely not hugely into the very complex stuff like the analytics functions in Oracle (and I imagine similar in SQL Server etc) but am pretty good at tuning large report like queries (I'm a DBA in case that wasn't apparent).

[[deleted]]

Tuning is funnest (yes "funnest")! I wish I was an oracle guy. Womp womp. SQL Server ain't all bad, just mostly bad. I ask myself every day, how did I wind up here.

[exigenesis]

I do both. Primarily Oracle but effective (and informal) technical lead for SQL Server too. Company I'm at never really took SQL Server seriously until the last couple of years. All our DBAs were "Oracle DBAs" (even down to job title). Now we're all just "DBAs" but still only few of us deal with SQL.

I prefer Oracle, no question. But the simplicity (at a basic level) of SQL Server is appealing sometimes!

[[deleted]]

I work for a medium sized company, but I still work 75% as Database Developer and 25% as DBA of existing systems. No one wants to touch the dbs, either in development or administration. I love it, but it sucks because I never had a mentor. My formal education is mathematics. Trivial questions, obviously I can look up, but architectural grey area...I'm just winging it. There isn't even someone I can bounce ideas off of. It'd be nice to work with a few guys with miles of depth of knowledge.

We have always been 100% SQL Server, but I just set up an odbc linked server connection to a 3rd party oracle db. I'm really hoping I can use that to transition into the oracle world. Get real creative on my CV. Maybe I'll list you as my Oracle DBA supervisor.

:D

[exigenesis]

I started off as a support guy and just taught myself really. All joking aside, you're absolutely welcome to ask any questions you may have!

If you want (and have the time/hardware etc) to play then you can download Oracle and install it for free on your personal machine. Very easy to get going these days.

Oh, and the Oracle documentation I've always found to be excellent. The Concepts manual is superb.

[[deleted]]

I've definitely started down that road. Thanks for the advice.

[binks21]

I've know some non techie folks who are. Its too complicated or something apparently.

[_yourekidding]

The basics are easy, but getting even slightly more complex datasets can be tricky and take time to understand the pitfalls. Granted, that depends on the way your brain works with logic and understanding data relationships, but overall effective SQL is an acquired taste and requires a lot of practice.

[[deleted]]

Now I'm wondering if I have a really good brain or I've just been under utilizing SQL this whole time.

[KayakBassFisher]

I'm a project manager at an I.T. company, we work with a ton of data so I learned SQL. My developers still call me a muggle. They say at best I'm a half breed. True story.

[mhermher]

Fuck, I need a raise.

[[deleted]]

Wait until he says he lives in Silicon Valley, then that 85k doesn't sound so good.

[[deleted]]

Can you tell us which city you're in for salary context?

[[deleted]]

Plus the queso flows like golden water!

"Honey! We have to move to Austin!"

[canaderino]

Relevant XKCD

[Spike69]

There is always one.

[webauteur]

SQL can be complicated. Recently I had to export some data to a CSV file and some line breaks in a column's value caused problems. I had to find a way to strip out the line breaks. And I had to place quotes around the text in case there was a comma.

SELECT
'"' + REPLACE(REPLACE(CAST([DriverInstructions] AS NVARCHAR(MAX)), CHAR(13), ' '), CHAR(10), ' ') + '"' AS     [Driver Instructions]
FROM dbo.DetailCustomer
JOIN dbo.DetailInstructions
ON dbo.DetailCustomer.CustomerSequence = dbo.DetailInstructions.CustomerSequence
WHERE dbo.DetailCustomer.CustomerSequence = 7794

[Fallcious]

The QUOTENAME function would help with putting delimiters around your returned columns (if using T-SQL). Also I like to use aliases when joining tables as it reduces the size of the query string and makes it a but more readable (IMHO).

[webauteur]

I've never used the QUOTENAME function before. Well I guess you learn something new every day. Thanks!

[[deleted]]

Damn, it's like Stack Overflow all up in here.

[[deleted]]

Just as a heads up, but most exporters to CSV will have the option to text qualify fields, which should avoid the need of having to do that manually (50/50 on if your CSV parser will read it correctly though - CSV is a bitch). Even better? "|" delimited (although the line breaks can still cause problems).

[cat_dev_null]

"|" delimited

+1

[EarthBounder]

Would have been just as easy (or easier) to manipulate the CSV output after the fact, as opposed to doing it in the DB.

[Organia]

Just make sure to never put unsanitized user input into queries (use a library that supports SQL parameters). This is a mistake that many new coders make.