I am trying to practice writing simple SQL queries but I can't connect to my school account on Microsoft SQL Server Studio because they delete your database once you finish the class. I downloaded MySQL but I wasn't sure if I could practice queries on it or not. Any answers would be great, thanks!
2 Answers
The syntax and built-in functions are not identical, but they are similar for many things. For example, SQL TOP, LIMIT, or ROWNUM clauses is a case where they diverge.
Personally, I’d recommend taking a look at what w3schools.com has for SQL (and MySQL) resources. They have a ton of info to get you going, tutorials, references, etc. They also have “Try It Yourself” modules where you can use a playground database they provide to practice executing in the language your looking at material for. In cases where the SQL Server and MySQL (and other languages) syntax differs, w3schools shows examples of both (all that they support) like in the example I mentioned at the top.
Snippet from the top of their SQL tutorial home page: “Our SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems.”
I suggest SQLite. Why?
It is an embedded database, rather than something like MySql which stores folders of metadata. Sqlite stores only a single file. My reasons for beginning with Sqlite are as follows:
1) It is the default database for a lot of common applications (Django, Airflow, etc). Knowing it would come in real handy when learning these tools.
2) The download is not only much simpler, but the ide is much faster. Your complete database is also only a single file (very beginner friendly).
3) In memory databases. That's correct, you can spawn and delete databases in Sqlite within memory (or even delete the whole file with your OS file removal). Very useful for learning, data science, and on the fly OLAP.
4) It can store up to 140 TB of data. It is the perfect tool to load a csv to quickly analyze the data. Also, you can create a small database, compress the file, and send it to anyone! Sharing your whole database is really just sharing a file.
5) You can import sqlite into Python, C, C++, and start automating your queries. You can do this with MySQL too but there is more library downloading and reading to do. Do not use sqlite in production (multi threading limits) but it is great for ad hoc analysis (Jupyter notebooks), prototyping, and learning.
Overall, mysql is not the best tool for beginners (it's not even used in production as much as Postgres or SQL server). It abstracts too much from the user to even understand what the database represents or the query engine. Also, Sqlite is closer to standard ANSI than MySQL in my opinion (given all the syntactic sugar). Learn Sqlite, move to postgres, and then explore all the nosql, blockchain, etc. If you ever face MySQL, you'll pick it up in minutes. I guarantee, you will have a much easier time picking up sqlite!
Related