6 ms·
I teach SQL to non-techie students. I used to give the the option of doing either MySQL or SQLite, but not only did I underestimate how different the syntaxes w
by danso 10y ago
I teach SQL to non-techie students. I used to give the the option of doing either MySQL or SQLite, but not only did I underestimate how different the syntaxes were, I also underestimated how not-trivial it is for students to successfully install and run both the MySQL server and client. These are students who can't even use a spreadsheet well, not that that makes a huge difference in understanding databases.
I've moved everything to SQLite and couldn't be happier. Not only is it easier to distribute assignments (e.g. a single SQLite file, instead of CSVs that need to be manually imported), it does everything I need it to do to teach the concepts of relational databases and join operations. This typically just needs read-only access, so our assignments can involve gigabytes of data without issue.
- RangerScience 10y agoNote: http://postgresapp.com/ http://postgresapp.com/ is trivial to install, although it only works on macs, is still more involved than a file, etc etc...
- danso 10y agoThe one thing missing from SQLite is stronger type casting...however, SQLite does have a solid, cross-platform GUI in DB Browser [0]. It's not as nice as Sequel Pro and some of the commercial clients for MySQL. Postgres, unfortunately, doesn't have near the variety of reliable clients that MySQL and SQLite do. However, SQLite follows most of the standard syntax that Postgres does, so having students move right into CartoDB, which lets you run raw Postgres, has never been a problem.its just the self hosting that's a pain :). One more thing that I wish I could have from Postgres as a teacher: how it returns an error when you include non-aggregated columns in the SELECT clause. In MySQL and SQLite, selecting extra columns won't throw an error, and the results look close enough to correct to be fairly dangerous for novices. Better to just throw an error as Postgres does. [0] http://sqlitebrowser.org/ http://sqlitebrowser.org/
- placebo 10y agoAnother good cross-platform GUI for SQLite that I've been using for years is TkSQLite (http://reddog.s35.xrea.com/wiki/TkSQLite.html http://reddog.s35.xrea.com/wiki/TkSQLite.html)
- elchief 10y agoSqlite was a postgres fork, off of 8.something, IIRC
- yoo1I 10y agoNo. This is incorrect.
- pmontra 10y agoIt owes something to PostgreSQL but it's the syntax, not the source code. I quote from https://en.wikipedia.org/wiki/SQLite#History https://en.wikipedia.org/wiki/SQLite#History Hipp based the syntax and semantics on those of PostgreSQL 6.5. In August 2000, version 1.0 of SQLite was released, with storage based on gdbm (GNU Database Manager).
- elchief 10y agoAccording to these slides from Sqlite founder, Sqlite was a "spin-off" of Postgres: http://www.pgcon.org/2014/schedule/attachments/319_PGCon2014OpeningKeynote.pdf http://www.pgcon.org/2014/schedule/attachments/319_PGCon2014... According to this, "SQLite was originally written from PostgreSQL 6.5 documentation" https://www.pgcon.org/2014/schedule/events/736.en.html https://www.pgcon.org/2014/schedule/events/736.en.html So, "fork" wasn't the right word. Apologies.
- googie 10y agoYet another free, open source and multiplatform GUI is SQLiteStudio ( http://sqlitestudio.pl/ http://sqlitestudio.pl/ ).
- mjmsmith 10y agoPostico (https://eggerapps.at/postico/ https://eggerapps.at/postico/) is a really nice PostgreSQL client for OS X.
- izacus 10y agoWhile that may be true for Apple owners, it's very untrivial to install and properly configure (permissions, etc.) on Windows and Linux. SqLite usually "just works", can easily be reset if you do something wrong (just delete file!) and has better GUI browsers and tools.
- Yaggo 10y ago"I used to give the the option of doing either MySQL or SQLite" SQLite is good choise for absolute beginners. Later, when teaching "real" multi-user RDBMSes, although MySQL may be more popular, it makes more sense to teach PostgreSQL as "default open source database". Both will do the job, but PostgreSQL has got more stuff right from the beginning, which is especially important when learning. Think PHP vs. Python, PHP may cut few corners but it's not ideal language for teaching generic concepts.
- yannis 10y ago>Think PHP vs. Python, PHP may cut few corners but it's not ideal language for teaching generic concepts. Maybe not even Python, suggest go or rust. My generation we started learning CS with Pascal and moved to C, when we had a bit more understanding.
- StavrosK 10y agoI think it's "Python will teach you more about programming, C will teach you more about computers".
- jstimpfle 10y agoI'm not intimate with Go, but I figure its clean and simple design makes it a good first language; and in particular a good preparation for C, because of its focus on memory layout. Rust? NO! One of the worst choices for a first language. Similar to C++, Rust is really heavy on concepts.
- danso 10y agoSQLite teaches virtually all of the necessary concepts to jump right into a multi-user RDBMS, even most of the syntax, with the exception of being more lax about datatypes. My audience is not people intending to build applications, but to do data analysis beyond what they can do in your typical spreadsheet. I don't think the PHP vs. Python analogy is particularly accurate. I don't feel that PHP is easier to learn than Python unless the main goal is to produce something public-facing on the web. Nevermind that the syntax is significantly different.
- drvdevd 10y agoI actually think SQLite could probably be used from beginning to advanced level, depending on what you're teaching (relational theory for example), but perhaps I'm not aware of its' limitations in that regard. That being said have you considered using docker (or other popular container tech) for making installation of MySQL easier?
- drvdevd 10y agoAnd I should also add - what about cheap cloud SQL instances/services as well? Perhaps the students could learn a bit about how simple it is to use some cloud resources too without the management overhead?
- pbhjpbhj 10y agoIf it's just too learn SQL then would a LibreOffice database do, perhaps they removed it but you used to be able to use (or perhaps just import?) a spreadsheet as a DB and run SQL against it. Definitely did joins and order-bys using standard SQL syntax.
- barking 10y agoLibreoffice now use firebird under the hood I think. Firebird has a more complete support for sql than sqlite has. It also has the advantage of being able to be used both in a manner analogous to sqlite or as a full blown server database.