What is intermediate-advanced SQL and how to learn it?

[shivajikobardan]

Non-DBA advanced part:
For me subqueries, HAVING clause, GROUP BY clause, CTEs, Indexing, Optimization etc are advanced sql queries. This is mostly used for data analyst, data scientist etc type I mean.

For DBA part:

No idea

What is advanced sql and how'd you learn it.

For data analyst roles? I'll ask in next post about for DBA roles.

I've learnt basic sql. I can write subqueries, but I'm no expert at it. I can do joins and feel pretty confident at it. I am still confused with group by clause which has been my biggest hurdle as a data analyst.

I want to practice. IDK what to practice. Hackerrank sql problems are seemingly useless and tough. Is there something that teaches from ground 0 for intermediate sql data anlysts and admins?

 

[jedishrfu]

My take on SQL levels:

Basic SQL:
- write queries to get tabular data sorted or grouped by some conditions
- write queries combining data from multiple tables
- create index tables to speed up your queries
-insert, update and delete data

Intermediate SQL:
- Design a Star schema for your data
- Adhere to Codd's laws on database design
- optimize your design
- learn how to alter tables and schemas
- backup your data
- working with UNION queries

Advanced SQL:
- write SQL that writes SQL to get around some limitation like multi-dimensional queries

Some websites to checkout would be:

https://sqlzoo.net/wiki/SQL_Tutorial

Personally, I would go with the book and play with the various recipes. With respect to SQL writing SQL, I had a coworker show me where it was useful in a datamart we were developing. SQL statements are likely compact programs.

https://mode.com/sql-tutorial/

A good book to start with is the SQL Cookbook:

https://www.amazon.com/dp/1492077445/?tag=pfamazon01-20

Checkout its table of contents.

In my last project, I used the pure java H2Database engine. It was quite good and very easy to integrate into my java application. It didn't support every SQL feature but did have the ones I needed and could run embedded within my app for faster SQL INSERT/UPDATE/DELETE operations while other apps used the TCPIP connection ie JDBC to do query things.

 

[shivajikobardan]

I tried cookbook for css and it was outdated and not helpful for learning. Do you think this cookbook is not the similar type?

 

[DrJohn]

SQL does not change very much, CSS changes are much more common, mainly additions to what it can do.
An SQL query that worked on a database ten years ago will work today unless the database has been restructured. So cookbook examples of SQL are as valid today as they have ever been, although I've not got that book. But you can probably get it cheaply on ebay. There's a latest version dated 2020 on amazon but finding that on ebay might be trickier.
Also basic SQL queries and techniques usually work in all the standard relational databases.

Things like HAVING, GROUP BY are basic level things, so any beginners book on SQL will help. Rudy Limeback's Simply SQL is easy reading for revision of the basics. PDF available at https://fsim.ca/docs/sql.pdf

 

[Vanadium 50]

Just as there is more to programming than knowing the syntax of a programming language, there is more to databases than knowing the syntax of a particular query language. A statement like "my data is naturally hierarchical, but we are emulating that with a relational database" should not be some sort of deep mystery.

 

[harborsparrow]

This post is meaningless unless you explain what database you are writing SQL for. SQL is a generic term, but the implementation of the language can vary quite widely across specific relational databases, and language features are not available for all databases. T-SQL, for example, is the version of SQL used by Microsoft SQL Server databases, but even then, you need to know the database version to know exactly what language features are available.

 

[berkeman]

This post is meaningless unless you explain

If by "you" you mean the OP, they left the building about 2 weeks ago.