I am not a DBA.
I am not a DBA. (had to repeat myself as most of my customers dont hear me the first time).
I can do basic SQL queries/updates/inserts.
I want to be much better at SQL. Dont need to be a SQL Ninja or SQL Guru or whatever. Not going to be a DBA. But I do need to better use the SQL engine of the database I am using (engine varies from project to project).
the question is, how can I learn more about SQL? Kahn Acadmey? Udemy? something else?
thanks for your advice.
Joe Celko’s books are very good. I like having books for technical stuff. It is easier for me to find things I sort of remember!
Don’t write SQL manually. Use something like Navicat Premium or MS Access and then let the application show you the result.
Then use something called query planner to see where the bottlenecks are.
I totally disagree. DO WRITE IT MANUALLY… otherwise you will get in the habit of relying on tools to write your code, and there will (I promise) come a time when you aren’t sure WHY it is what it is. If you learn to do it the “hard way” (at least in the beginning), you will be forced to learn more about it, and then reliance on a “tool” will be less problematic.
And EXPLAIN PLAN, can be useful… but it may be confusing, especially with the SQL optimizer chooses a plan other than what you “expected”
But since you ARE a DBA, why is this a problem? (lol, sorry could not resist)
and you will remember more when doing that way.
Huh ? You must be kidding
@Joost: nope, not kidding. The query editor in Access is very good.
My brain is visual. Of course, I can write a simple select by hand. But doing joins with more than 2 tables: it’s 10 times easier to build a pattern instead of fiddling around. Especially with debugging SQL being so hard.
Good practice is looking at your existing projects. Figure out where you can use joins to minimize the number of queries needed.
I’m not sure how long SQL will hold out supreme. Databases like RethinkDB really change the role of the database server. It’s like PostgreSQL/Valentina channels on steroids.
Sounds better. Navicat Premium is a wonderful tool, worth the money.
I fully agree with you @Beatrix that using a good query editor is far preferable above typing out by hand.
Since I got tired of transfering the queries into string-variables, I’ve made myself a little application that stores my application- queries into a sqlite-database-file having the same name as my application-exe and stored in the resource-folder.
With just a little code in my appication-template, I cash the queries into a dictionary so that they can be looked up very fast. The same function returning the query takes care of " ’ " and replaces the parameters by values.
The project I am currently working on must be able to connect to sqlite, mysql or postgresql production database. Depending on it’s configuration I cash only the queries I need., which may differ from one engine to another.
As with most things that are complex or intricate it takes practice to become proficient or fluent.
My SQL skills have evolved over the years but I too am not a DBA. I was trying to craft a SQL query some years ago when I was first learning and I could not figure out what to do. I guy I worked with said “that sounds like you should join the table to itself”. That was one of my first “Homer Simpson D’oh moments” for SQL. That was a “weird” idea at the time but it can be a useful trick.
Over the years I have tended to browse the documentation for a particular language that I am using to see what functions and statements are available. I do the same with SQL. I will “file away” interesting ideas that I end up using later.
Each time I run across a new SQL task that is just not a simple Select/Insert/Update I try to explore to learn more. Unless it is very simple I never start building a SQL statement in XOJO. I use my favorite tool and tinker until I get it right. I also wrote a little debugger function that will dump a string to a file. If I am having SQL troubles in XOJO where I am using a declared constant as a SQL statement template I need to see the SQL and the result. I typically use a constant template and then do some “adjustments” with REPLACE or other methods before execution. I can “dump” the modified SQL to a file then copy/paste it into my SQL tool for much more detailed debugging. Using message boxes or System.Debug can work for this but if the SQL statement is a screen full of code it is not so practical to copy from the XOJO IDE and paste it into a query tool.
I also try to be pragmatic and not do something just because you can. In practice I believe topics like normalization are more of an art than an exact science and “bending” a few rules is OK if you have a reason. Also you could “go crazy” and move lots of logic from your XOJO code and put it in SQL statements, stored procedures, and SQL functions. Just be careful because this makes debugging harder when the logic is broken into multiple parts and “hidden”.
Being a DBA is also about performance but I generally ignore that issue unless I know I have a dataset with sufficient rows that it could be an issue or the SQL contains lots of JOINs. The EXPLAIN feature in some SQL servers can help when the datasets are large or the query has complex JOINs or Sub-Queries.
A never ending journey.
Many years ago I purchased “Using SQL” by James R Grott and Paul N Weinberg (Osborne McGraw-Hill, ISBN 0-07-881524-X). I found it highly useful and still refer to it from time to time. A quick search on Amazon reveals a huge number of SQL books.
Books ?
Learning standard sql is a good start
But then vendors do / do not support various bits of the standard so you always need to investigate the vendor specific docs to see what they support
My two cents: if you want to learn SQL and don’t plan on using Access, stay away from Access/Jet. I move between Postgres, SQL Server and Sqlite with ease. And then there’s Access.
Everyone thanks for all your advice so far. I am still taking more advice and reviewing all yours.
Thank you!!
as for databases that I use (by choice or by force) is SQLite/cubeSQL/PostgreSQL/MySQL/MariaDB (and that is in order of most to least used).
As for MS Access, I wont use it. I am a “Mac Guy” so Access is out. Although I use Windows (by force) at work, I dont have access to Access (that the that I dont).
Thanks again…
As for books I hv a couple but I particularly like SAMS, teach yourself SQL in 10 minutes which i have as a handy guide. Taught me a lot and not expensive.
Hmmmm… wondering if it might be profitable to write a simple SQL with XOJO and SQLite tutorial…
I would buy a reasonably priced ebook on the subject. especially if it goes behind the basics.
Eugene Dakin has written nice books all called “I wish how to …” , sold by @Norman Palardy. Really handy with lots of xojo codesnipets.