Is really good, though its more related to indexing than SQL specifically.

[1] http://sql-performance-explained.com/

[2] http://dataintensive.net/

[0] http://modern-sql.com/

I had a Samsung Galaxy Note 12.2 for a while, but the built in reader (iBooks) in the iPad is far superior to everything I tried on Android over a period of months.

Preview on macOS is also good.

EDIT: I don't know, when that review was written the device was just released. The device received various firmware updates since then. I convert everything to EPUB with Calibre.

* Most modern browsers have it built in

* Preview on MacOS is good

* Kindle and iBooks import PDF files

* SumatraPDF and Skim, depending on your OS

On https://pgexercises.com I focus quite a bit on developing a mental model for SQL. I would suggest not skipping the easier exercises - even if you can do the SQL, you might find the explanations useful.

Oh and I had an idea for practicing deletions/insertions/updates & other behavior that you couldn't do on web: create an Electron app that uses a local db. I'd buy that in a heartbeat.

That is a really good idea on the app. I do have some ideas for making DDL and DML available via the web interface, but sadly I'm extremely short on free time in the last couple of years.

I wish you and the flexboxfroggy guy has a patreon account so I could pay y'all to make games/exercises that improved my mental model of things.

Maintaining the site is in many ways its own reward - it gives me a great deal of joy to see how much it's used now. Improvements are actually a reasonably high priority for me, it's just that toddlers have a way of creating their own special priority levels :-).

All that said, I very much appreciate the sentiment!

I'm trying to start a career as a BA and expect that it will be handy to know SQL, which is fine, but I haven't been able to find anything out there that simulate the kind of scenarios and tasks SQL would be used in. I suppose your site is a step closer to that.

Aside from SQL, I've got UML and Visio on my to-do list. There's also the BABOK reference, but it sounds like it's something that only really starts to make sense when you have something to apply it to and observe.

Great site, thanks

Very easy to get going and very hands-on.

[1] https://www.postgresql.org/docs/manuals/

The HackerRank SQL challenges were also helpful in getting some extra practice: https://www.hackerrank.com/domains/sql/

Finally, this Quora post will also point you to some useful resources and has some great tips that I'm working through now: https://www.quora.com/How-do-I-learn-SQL

https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/cours... https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/cours... https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/cours... https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/cours... https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/cours... https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/cours...

One of the things I sometimes find tricky with SQL is pattern matching a problem to a solution. It can be tricky to describe what you want and sometimes direct human help can't be beat so I do recommend Stackoverflow (normally I have mixed feelings about SO). There are few power users on SO like Craig Ringer and the horsesomethingsomething (can't recall the actual handle) that are helpful and friendly.

Postgres has an excellent manual. It has an internal scripting language called PL/pgSQL which is (to put it politely) not intuitive at all. The manual was enough to help me write a query to implement a binary tree search.

Unfortunately I could never find anything online by him, except these videos: https://tonguc.wordpress.com/2008/01/29/good-sql-practices-v... and not sure if this is what you are looking for.

I also look at the postgres docs very frequently for syntax and format and those help me a lot. Stack Overflow is also a great resource!

The first reason is that the relational model is a combination of logic and set theory, which happens to be great for a lot of business applications.

The second reason is that people have adapted SQL surprisingly well to other kinds of data, like JSON. Even if you try to build a database system specialized to JSON, it will still probably come out worse because getting things like storage, replication, administration, etc. right takes a long time.

The third reason is that data has more value when combined seamlessly with other data. So if you have business data (which everyone has) and JSON, you are better off with a single system that is great at business data and OK at JSON, than two specialized systems.

Keeping these things in mind makes it easier to understand SQL in my opinion.

Starting from the relational model and going through SQL really makes the language make sense.

If this is too basic for you, then, at worst, you'll spend a few hours reviewing the basics and strengthening your foundation.

This will take you from the theory of indexes to actually seeing how it is used by the SQL engine.

This will go a long way to building the right indexes and SQL tuning in general.

I would suggest learning how to generate the query plans for your SQL engine of choice.

This is key. I cannot count how many times I go to a client that is complaining that their software is slow and ends up that is because they don't have the right indexes

It is the lowest hanging fruit to improve performance.

Even if you don't have the flexibility to change the SQL on a project, you may still have the ability to create/rebuild the indexes to make the query faster.

Sometimes I see job offers that ask for experience with large websites, large databases, large servers, etc, etc, etc (you get the idea).

How do you land those jobs if getting in that kind of subject is impossible alone? You can't simulate that kind of things at your home, so unless your side project grow and you must learnt it the hard way, or you had luck to be at a company where they allowed you to be involved, how do you learn that? Thank you

In particular: https://www.postgresql.org/docs/9.6/static/sql.html

https://www.postgresql.org/docs/9.6/static/server-programmin...

https://www.postgresql.org/docs/9.6/static/sql-commands.html

https://www.amazon.com/Joe-Celkos-SQL-Smarties-Fourth/dp/012...

[1] https://schemaverse.com/

It doesn't necessarily teach each advanced technique, but it does give you a good place to practice what you've learned.

(Thanks for the mention!)

The Try SQL course is free, the other 2 you have to pay for. https://www.codeschool.com/courses/try-sql

That will get you pretty far but the advanced SQL topics will require the study of what the underlying database provides and how it works. Every database is different in terms of how it implements advanced features, if at all. For example, MySQL doesn't have window functions. For postgres related topics, the documentation is excellent, postgresguide.com gives a high level overview, or you can follow craig's blog (http://www.craigkerstiens.com/) that provides a gentle introduction to many of these topics as well.

Whenever I do come across blogs that have good articles we aim to feature them in Postgres Weekly, so if you want a regular stream of that type of content it's worth checking out - http://www.postgresweekly.com

https://www.amazon.com/SQL-Antipatterns-Programming-Pragmati...

But as you said, you haven't actively learned SQL, so probably need to find some free data sets to work with.

You can probably start with Data is Plural. That will, at least, give you some raw data sets so you can get started on learning how to build up a database from unorganized data first:

https://tinyletter.com/data-is-plural

Edit to add: First and foremost, you have to learn normalization. Without that, you aren't doing any SQL.

Not shiny, but very functional. It's been around 5+ years and has lots of great practice for advanced queries.

I took the Stanford course when it was first offered online, and I regard it as one of the best things I ever did. You're absolutely right in that there's a lot of newer stuff that it didn't teach me, but the database's documentation is usually sufficient to plug that gap.

Database Design for Mere Mortals - https://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp... (not an affiliate link)

Legend of the Drunken Query Master - http://www.joinfu.com/2008/09/slides-from-drunken-query-mast...

I would also encourage you to learn a bit of Relational Algera if you really want to improve your mental model.

This. SQL is just a tool for expressing your thinking and learning it doesn't help you to problem solve. The theory, both relational algebra and set theory, will teach you how to think about the problems, not how to express your thinking. That's what's necessary to solve difficult SQL problems.

One thing you have to realize is that once you get a little advanced, you have to get to the details of the single SQL implementations, it's not about SQL but about Postgres.

I've found these books really valuable

# SQL Performance Explained Everything Developers Need to Know about SQL Performance

https://www.amazon.com/Performance-Explained-Everything-Deve...

This book fundamentally talks about how to effectively use and leverage the SQL indices. Talks about all the important implementations (Postgres, MySQL, Oracle, SQL Server).

# Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems

https://www.amazon.com/Designing-Data-Intensive-Applications...

This book gets mentioned a bunch around here and for a good reason. There aren't too many concrete resources on making your systems "webscale" and this one is really good.

# PostgreSQL 9.0 High Performance

https://www.amazon.com/PostgreSQL-High-Performance-Gregory-S...

Discusses all the different settings and tweaks you can do in Postgres. It's crazy how much of a perf gain you can get just by twiddling the parameters of the database, i.e. all the tricks you can do when the single instances are bottle necks.

There's a similar book for MySQL https://www.amazon.com/High-Performance-MySQL-Optimization-R...

# PostgreSQL 9 High Availability Cookbook

https://www.amazon.com/PostgreSQL-9-High-Availability-Cookbo...

Discusses how do you go from 1 Postgres instance to 1+ instance. Talks about replication, monitoring, cluster management, avoiding downtime etc i.e. all the tricks you can do to manage multiple instances. Again there's a similar book for MySQL https://www.amazon.com/MySQL-High-Availability-Building-Cent...

Last but not least check out the postgres documentation, people consider it a standard of what good documentation looks like https://www.postgresql.org/docs/9.6/static/index.html

Also last but not least, read up on relational algebra (the foundation of SQL) https://en.wikipedia.org/wiki/Relational_algebra. I've always found SQL to be extremely verbose (the syntax reminds me of idk COBOL or smth) but there's another query language called Datalog, that's for our purposes similar to SQL but the syntax is much more legible.

E.g. check out these snippets from these slides (page 29) (and check out the whole class too)

https://pages.iai.uni-bonn.de/manthey_rainer/IIS_1617/IIS201...

Datalog:

s(X) <- p(X,Y).

s(X) <- r(Y,X).

t(X,Y,Z) <- p(X,Y), r(Y,Z).

w(X) <- s(X), not q(X).

SQL:

CREATE VIEW s AS (SELECT a FROM p)

UNION

(SELECT b FROM r);

CREATE VIEW t AS

SELECT a, b, c

FROM p, r

WHERE p.b = r.a,

CREATE VIEW w AS (TABLE s)

MINUS (TABLE q);

http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf

a) row_number over() and partition: in the abstract: https://www.youtube.com/watch?v=-X3eIyZV728

b) applied to customer value analysis: https://www.youtube.com/watch?v=iHxJvF0tZOA

c) applied to time differences (also uses materials from b above) https://www.youtube.com/watch?v=5f8tF4U70Ic

After a while you can write these from scratch and they generally work the 1st or 2nd time, but it takes lots of trial and error at first when setting up the partition and ordering clause within the OVER() expression.

http://tapoueh.org/blog/2013/08/20-Window-Functions

I recommend reading all things what Brent Ozar [1] has to say

There is also another blog from Dr. DMV on SQL Server performance [2]

[1] https://www.brentozar.com/ [2] http://www.sqlskills.com/blogs/glenn/category/dmv-queries/

High Performance MySQL by Jeremy D. Zawodny