On a related note, I always found the syntax of SQL to be great. Someone told me that simplicity of SQL made relational databases even more popular. It is one of the best designed language where even someone new to the field won't feel intimidated. I'm surprised designers of SQL haven't won the Turing Award yet.
See LINQ. There they had the opportunity to fix it and it works just great.
This nullifies some other common sources of confusion, such as which statements are executed before a GROUP BY and which ones after (and why HAVING exists as a keyword). It's also similar to how CTE syntax is generally much more readable than subqueries.
This declarative property also provides a (somewhat weak) motivation of why SELECT should come first. Precisely because it answers part of the WHAT, i.e., the schema of the result.
SELECT a1,a2,... FROM Table AS t WHERE Condition
[(t.a1,t.a2,...) for t in Table if Condition]
SELECT is also analogous to normal loops:
foreach t in Table
if not Condition: continue
# Use t.a1, t.a2 etc.
So, I will start with a SELECT * FROM myTable t, then go back and replace * with t.<columns appear here>.
I'll use the same in other places in the query like WHERE conditions.
I use DataGrip at work and it can complete the column names without knowing the table name, so it ends up not being a problem.
If the query is simple enough that a natural join would work and you aren't doing FROM clause aliasing (which can be useful to make reusable queries self-documenting), sure.
For more complex queries and obviously any time table or column aliasing are used, that becomes somewhere between less likely and logically impossible.
Ofc that might just be the emphasis on joins makes group by the next lowest hanging fruit
Urban Dictionary (https://www.urbandictionary.com) or even just typing something like "what does tbh mean?" into your preferred search engine will often help you find likely interpretations of unknown abbreviations, in my experience.
I mean, that's what they did.
There is significant problem-solution mismatch in joins and some other SQL constructs which are therefore semantically quite controversial in many use cases:
CREATE FUNCTION fullname(person) RETURNS TEXT AS $$
SELECT $1.firstname || ' ' || $1.lastname
$$ LANGUAGE SQL
But for my taste the language is to close to English and therefore redundant and inconsistent in an annoying way. Example:
Select x from tbl ...
Delete from tbl ...
Update tbl set x=...
Why change the word order?
Codd wrote a couple of query languages before, but they apparently weren't usable by mere mortals.
SQL is over 40 years old, and still dominates. The most recent contenders had to position themselves in terms of SQL (NoSQL).
20 years ago was 1998.
But Java and Python? Both languages have changed beyond recognition over the past 20 years. A programmer who hasn't touched either for 20 years would probably be overwhelmed.
Another difference is that even though SQL has changed, there isn't much you need to know outside of the DBMS you are using. With Java and Python on the other hand, you need to keep up with the changing library landscape to stay relevant.
For C, its just library management is troublesome enough that you end up writing your own ;)
Perl has amazing backwards compatibility, but has also solidified on much better coding practices and libraries in the last 20 years. You can still run that code from 20 years ago with a new interpreter and it will be safer and faster, or you can choose a more modern style or library and it will work just as well.
And to forestall anyone trying to bring up Perl 6 as a counterargument, unless you actually know what it is and how it interacts with Perl 5 and the community, I doubt the argument is actually saying what you think...
My point wasn't that one can't name languages with staying power. There are several. But, SQL is still special, I think. It's almost universally needed. If you go to work in a Node or Go shop today, you're probably still gonna need to know some SQL, just like if you went to work in a Python or Perl or Java shop in the 90s.
If we include non-Turing-complete languages as “programming languages”, which we must for SQL to get that 20-year label since SQL wasn't Turing-complete until recursive CTEs were part of the language, then XML.
So much more interesting, important and compelling than the usual tired customer/product examples.
I'm tired of seeing the implicit assumption around the place that software is just about business. Software should be about more than that.
That said: Doesn't work in Firefox and Edge? Common, are we back in 1998?
Sorry for the rant, but please don't do that.
With the writeup on why i made it here: https://medium.com/@__dave/why-i-wrote-yet-another-sql-tutor...
Do let me know if there's any collaboration we could do!
edit: the site will now warn you if your browser is incompatible.
People have always wanted to use "the web" as an application platform. It didn't work that well in 1998, but it's pretty good now. Progress has been made.
Scroll down to Dataset.
After "I’ve prepared for this book:"
There is a little interactive code editor you can type in and run the query.
The page works fine on Chrome.
I also discovered the ability to select from multiple tables and do joins using where clauses instead, which to me is a lot more intuitive than explicit/imperative join statements. I didn’t see anything covering queries like those (maybe because there’s only one table in the dataset, idk i’m on mobile).
here is the Github repository in case anyone wants to send PRs or start the project.
>The SQL query may look like an ordinary sentence, but you should view it as three Lego blocks: SELECT FROM executions LIMIT 3. As with Lego, each block has a fixed format and the different blocks have to fit together in particular ways.*
Explain what SQL is.
One example for this could be:
SQL means Structured Query Language, and with SQL we can build (structured) questions (query) using a syntax (language). To find what we want in the data, much like we build structures with various Lego bricks, we can use the following statement:
SELECT * FROM executions LIMIT 3
The SQL query may look like an ordinary sentence, but viewed as three Lego blocks, each block has a fixed format and the different blocks have to fit together in particular ways.
Just thinking of how to distill it even further...
In fact, there’s tons of anecdotal evidence that free MOOCS haven’t faired well in terms of retention precisely because of a lack of a buy-in from the people taking those courses.
• The Case for Free Online Books (FOBs): Experiences with "Operating Systems: Three Easy Pieces" by Remzi Arpaci-Dusseau, http://from-a-to-remzi.blogspot.com/2014/01/the-case-for-fre...
Free is great.
Are these funky, quirky books generally well-received outside the programming community? I quite liked Why's Poignant Guide to Ruby, for example, but I would never recommend it to anyone at work.
I don't know that I'd use "funky" or "quirky" to describe it though. Instead it comes across as clear, concise and approachable.
Search the page for the word “llanguage”.
The sum/count checker told me it was incorrect for using lowercase nulls.
I normally write all my sql lowercase for brevity, but I also mostly use sql server.
I really like what you have done here and I hesitate to report it in your show hn thread because this really is great.
You say that: "Programming is best learned by doing", but I am struggling to do anything...
It would be great if there was some online area like SQL Fiddle with the data loaded, so I can run queries and see results in my browser without having to set anything up or am I missing something.
The code is open source, anyone is welcome to fork and populate with new data like this.
Its pretty easy to stay within the free tier (1TB of data per month)
The <sql-exercise data-question...> elements aren't visible in Firefox at all.
SELECT * FROM customers
WHERE company_name LIKE 'foo[lt]'
SELECT * FROM customers
WHERE zipcode LIKE '12[^30]45'
and besides: technology is a facet of society, it is not a purely rationalist act.
1. In The Long Tail > Nested Queries, the first example contains the function LEN, which gives an error as LENGTH is expected.
2. On the questions with multiple answers, when showing hints, I would use different font colors for correct/incorrect statements as it will be easier to check your results.
That said, as a novice, the challenge questions are not in anyway suitable for a beginner. I wouldn't, and didn't, have any idea how to do them; or know where to look to find a starting point. The previous chapters do not prepare you for the challenges and I came away thinking I was useless until I click the solutions and realised there was no way I would realistically ever been able to solve them without some form of further training in SQL.
I used the MIT license for the interactive SQL tutorial I built ( https://www.sqlteaching.com/ )
> WHERE 0
> 1 and 0 are the most basic Boolean statements. This block guarantees that no rows will be returned.
This doesn't work in SQL Server and it has always bothered me. But is it part of any SQL standard?
The book also says this is an invalid where clause:
> WHERE '%obert%' LIKE first_name
> More than one wildcard is fine. But the pattern has to come after the LIKE operator.
This one is probably a bug/mistake, but it's definitely not invalid in the SQL language.
Thank you for creating and sharing this!
"Now you have the tools you need to complete our project."
Where is this quiz and this project? Am I missing something?
I also use http://www.mysqltutorial.org and https://www.w3schools.com/sql/