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

• Jul 19, 2023

• #1

shivajikobardan

675

54

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?

 

Technology news on Phys.org

• Research team improves fuel cell durability with fatigue-resistant membranes
• Self-extinguishing batteries could reduce the risk of deadly and costly battery fires
• Chemists decipher reaction process that could improve lithium-sulfur batteries

• Jul 19, 2023

• #2

jedishrfu

Mentor

Insights Author

14,740

9,085

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.

 

• Jul 19, 2023

• #3

shivajikobardan

675

54

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

 

• Jul 20, 2023

• #4

DrJohn

164

153

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

 

• Jul 20, 2023

• #5

Vanadium 50

Staff Emeritus

Science Advisor

Education Advisor

2023 Award

33,017

19,456

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.

 

• Aug 17, 2023

• #6

harborsparrow

Gold Member

673

186

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.

 

• Aug 17, 2023

• #7

berkeman

Mentor

66,802

19,441

harborsparrow said:

This post is meaningless unless you explain

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

 

What is intermediate-advanced SQL?

Intermediate-advanced SQL refers to a level of proficiency in SQL (Structured Query Language) that goes beyond the basics. It involves a deeper understanding of the language and its various functions, as well as the ability to write complex queries and manipulate large databases.

How can I learn intermediate-advanced SQL?

There are various ways to learn intermediate-advanced SQL, including online courses, tutorials, books, and practice exercises. It is also helpful to work on real-life projects where you can apply your skills and learn from experience.

What are some key concepts to master in intermediate-advanced SQL?

Some key concepts in intermediate-advanced SQL include joins, subqueries, aggregate functions, stored procedures, and views. It is also important to understand data types, indexing, and database design principles.

What are the benefits of learning intermediate-advanced SQL?

Learning intermediate-advanced SQL can open up many opportunities for a career in data analysis, business intelligence, or database management. It can also increase efficiency and productivity in handling and analyzing large datasets.

How much time does it take to learn intermediate-advanced SQL?

The time it takes to learn intermediate-advanced SQL can vary depending on individual learning speed and the resources used. However, with consistent practice and dedication, one can become proficient in a few months.