SQL is fucking weird

[Underseer]

Ahem.

So I'm almost entirely self-taught when it comes to computers. I learned C++ by looking at other people's programs, editing them, and paying attention to what happened as a result of the changes, although I didn't really wrap my head around all that object-oriented stuff until I bought a book on C++.

I've finally decided to bite the bullet and get a low-grade certification in something, and right now I'm taking a database class where I'm finally learning the reason for the peculiar terminology I kept seeing in relational database software. I have to admit that I was never able to teach myself SQL just by picking apart or editing other people's code the way I can with procedural languages. I'm now on my second chapter on SQL, and I still don't quite have my brain wrapped around it.

It's just weird as fuck.

 

[rousseau]

SQL is in a different ballpark than C based languages, as it's more about data querying than manipulation and transformation. But if you can learn a basic select, the various join types, how to do a multi-join, and maybe a group by that'll cover the vast majority of cases you'll ever need.

After that point you just need to assume SQL can do whatever it is you want to do, then find the query you're looking for on Stack Overflow. It can get pretty funky, and it's way easier to just find someone who's done the work already.

 

[Underseer]

Thanks for the advice.

One thing that really trips me up is the fact that an alias can be referenced before it is defined because inner queries are evaluated before outer. That often makes my brain hurt (that doesn't take much at my age). Anyway, I had one SQL line in the textbook that contained the following:

WHERE [i]tablename[/i],
       ([i]subquery[/i]) CP1,
       ([i]subquery[/i]) CP2

Now I know that CP1 and CP2 are aliases because they are referenced as such in another part of the SQL code, but

• Where the fuck is the "AS" keyword? This textbook is fucking religious about putting the AS keyword when making an alias
• What the fuck are those parentheses for, and why are the aliases outside them?
• The syntax of assigning aliases in SQL is fucking bizarre; I'm used to assignments putting the variable name first, then the definition WTFLOLBBQ

Also, I'm going to have to reread the subsection on correlated subqueries. I think I hurt my brain reading that the first time.

 

[steve_bank]

I do not know about MS Excel. The open source Open Office suite includes an SQL distribution. I have played around with it.

It is for data base, report generating and query function. I did some work for an entrepreneur that had a lab set up in his garage. He used SQL for his parts database. He Could sort resisto for example by values and check stock on hand. He had functions for updating the database when using and receiving parts. Don't know if it is iSQL, the Open Office distribution has functions to create GUI applications for accessing, sorting and searching a data base..

It is a common tool in business.

 

[HaRaAYaH]

Learning SQL is no different than learning any other computer commands. Build a little database and play around. Build 3 tables. Customers, orders and parts. Select all customers in a zip code. Then select all customers with an order than select all customerrs with an order containing a specific part. Those are easy simple queries, Then you can move on to take more complicated queries. Just pat attention to the syntax. One wrong word and then you have a 40 billion record output.....

 

[James Chapman]

Yeah, SQL is a mess, partly for historical reasons. Bear in mind that it fundamentally a non-procedural language.

 

[excreationist]

I use it a lot for my job. I quite like it.

Here is a pretty good place to learn it:
https://www.w3schools.com/sql/sql_where.asp

Go to "Try It Yourself"

In my job I use INNER JOINs a lot and aliases.

e.g.
https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_where

SELECT c.CustomerName, o.* FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID

BTW when I use it I often put the query in the free Mac program Sequel Pro and keep running it as I'm building the query.

 

[phands]

I hate inner joins. They are a clear sign that SQL isn't really up to job, and that RDMSs are not in fact, very good at relationships. Foe some complex queries, query time can tend toward infinity rendering them unusable.

A lot of companies are turning to NoSQL - I use HBase for very large data sets. Others are starting with Graph databases - I am playing with Neo4j, and it's amazing.

 

[Underseer]

Learning SQL is no different than learning any other computer commands. Build a little database and play around. Build 3 tables. Customers, orders and parts. Select all customers in a zip code. Then select all customers with an order than select all customerrs with an order containing a specific part. Those are easy simple queries, Then you can move on to take more complicated queries. Just pat attention to the syntax. One wrong word and then you have a 40 billion record output.....

I find it very different from learning procedural languages, hence all the whining I've done.

 

[excreationist]

I hate inner joins. They are a clear sign that SQL isn't really up to job, and that RDMSs are not in fact, very good at relationships. Foe some complex queries, query time can tend toward infinity rendering them unusable.

A lot of companies are turning to NoSQL - I use HBase for very large data sets. Others are starting with Graph databases - I am playing with Neo4j, and it's amazing.

If queries are taking a long time you can make indexes with the columns.

 

[phands]

I hate inner joins. They are a clear sign that SQL isn't really up to job, and that RDMSs are not in fact, very good at relationships. Foe some complex queries, query time can tend toward infinity rendering them unusable.

A lot of companies are turning to NoSQL - I use HBase for very large data sets. Others are starting with Graph databases - I am playing with Neo4j, and it's amazing.

If queries are taking a long time you can make indexes with the columns.

True, but once you start having to use joins or add secondary keys, performance falls fast. And if the database is sharded across multiple clusters or even data centers, the indexing and shard-tracking can cause some queries to choke.

 

[KeepTalking]

I hate inner joins. They are a clear sign that SQL isn't really up to job, and that RDMSs are not in fact, very good at relationships. Foe some complex queries, query time can tend toward infinity rendering them unusable.

A lot of companies are turning to NoSQL - I use HBase for very large data sets. Others are starting with Graph databases - I am playing with Neo4j, and it's amazing.

Joins in general suck, every SQL implementation uses them differently. MSSQL is different from MySQL is different from Oracle SQL.

But it's not just that, any time you have to change, add, or update tables, it is data migration time, and you have a great chance of fucking up the entire process for others working in the same code base. You also have to often jump through hoops to avoid locking tables, unless you actually want to lock a table for some reason. SQL is just way too brittle any way you look at it, so why not minimize your db, and maximize your data? Start using event sourcing and event streams, and ditch the SQL bitch, you will never look back. Yes, your event streams are still saved in a db, but you just suck up all of the data into a payload, and then work with that payload in whatever language you are comfortable with, no need to deal with SQL and all of the baggage that comes with it.

 

[Underseer]

I hate inner joins. They are a clear sign that SQL isn't really up to job, and that RDMSs are not in fact, very good at relationships. Foe some complex queries, query time can tend toward infinity rendering them unusable.

A lot of companies are turning to NoSQL - I use HBase for very large data sets. Others are starting with Graph databases - I am playing with Neo4j, and it's amazing.

If queries are taking a long time you can make indexes with the columns.

NoSQL is used in cases where no amount of indexing is going to fix things because the rate at which data comes in and out is simply too high.

 

[Jimmy Higgins]

I've only used SQL for limited databases, so it plays nicer, but it is a pain sometimes, as I used it in an editor with no error reports, so if something doesn't work, I need to figure out what is wrong.

 

[steve_bank]

https://en.wikipedia.org/wiki/SQL

There is an ANSI/ISO standard.

Looks like the full implementation ios not suited for small applications. More suited to multiple data types, complex queries, like database searches in intelligence-anti terror applications. Searching for matches to multiple profile data.

I looked at it and for my own technical data base of parts and documents I used a spreadsheet, macros, and hot links to documents. I had a lot of documents accumulated over time. I had a prt number scheme that made it easy to serch for types of documents.

When I downloaded a new document I simply gave it a sequential part number and put it all in one folder.

https://en.wikipedia.org/wiki/SQL