Altenative to W3School for learning SQL (1 Viewer)

[SachAccess]

Hi,
I have started learning SQL queries from last week. I am taking help from SQL Tutorial (w3schools.com)
Have covered almost all the queries listed there. Facing issues with 2/3 queries even with their own sample database and queries but will come to that later.
This site has covered almost all the main types of queries (at least this is what I think). However, while writing and editing these queries I realized that I can add more combinations to the queries. I was successful with some, faced issues with some. This adding combination was just my imgination.
Am sure, there will be 100s of more combinations which can be tried and am not aware of.
Can experts here please help me with similar sites which will help me to lear SQL queries with more combinations, more examples.
Thanks.

 

[Minty]

IMHO, the only way to learn the really complicated stuff is to have a problem to solve, and then struggle to solve it. That's when you learn, and come across functions not available in Access that will help you (like String_Agg, Row_Number , Common Table Expressions and a lot of other things). Most things in SQL server queries (and Access to a point) can be broken down into logical portions.

You may end up writing stored procedures to do complex data manipulation, if you do try to remember is to work with SET based data.
Explore the use of temporary tables, and table valued functions, and where possible do not loop through things in order to get performance right.
It's not always possible but will make a massive difference to the efficiency of your application.

As for the combinations you talk about there are only really Select queries and Action queries, I'm not sure what else you would be thinking of. The stack overflow SQL questions might show you some interesting techniques, but they tend to be very specific in their replies.

 

[ebs17]

My school of SQL usage and formulation was and still is a forum like this. There, tasks from reality are set, in addition to the super easy ones for beginners, there are also more difficult, more complex and exciting tasks.
So when I found something like that, I tried to find my best possible solution to it. By working on the task, I also became more aware of the problem areas involved. In the second step, I then looked at suggestions from experts and was able to compare them with my solution or my failure. Based on the expert suggestions, I was able to analyze why and which steps were taken in which order. Of course, to understand it, to remember it.
I have collected sample solutions in a small sample database so that I can look it up and copy it there if necessary, always trying to solve it out of my head first. This improved the ability to write a query as an SQL statement yourself, at best using abstraction, where the SQL statement is already formulated in my head before I write it down.

In the forum you can also quickly see who offers good and interesting SQL solutions. So I then specifically searched for contributions from these my chosen ones in order to find "pearls" and develop them for myself.
New tasks are constantly being formulated in the forum. In this way you can apply your new knowledge and at some point you can also measure yourself against your role models and other experts, which is interesting in itself.

A good SQL statement must be fast, even with bulk data. That is their conceptual task. This requires that the wording of instructions also take into account things like the following:
- early volume reduction,
- shifting complex operations to the end of the processing chain, where the amount of data is already reduced,
- index usage: planning that usable indices are available + formulation of the query in such a way that indices can also be used
- existing data structures

The following script was and is my "bible" (in German):
Performance in queries from Michael Zimmermann

I then later summarized some thoughts to give back to the forum and its users:
SQL is easy --- Collection of links to the topic series

 

[SachAccess]

My school of SQL usage and formulation was and still is a forum like this. There, tasks from reality are set, in addition to the super easy ones for beginners, there are also more difficult, more complex and exciting tasks.
So when I found something like that, I tried to find my best possible solution to it. By working on the task, I also became more aware of the problem areas involved. In the second step, I then looked at suggestions from experts and was able to compare them with my solution or my failure. Based on the expert suggestions, I was able to analyze why and which steps were taken in which order. Of course, to understand it, to remember it.
I have collected sample solutions in a small sample database so that I can look it up and copy it there if necessary, always trying to solve it out of my head first. This improved the ability to write a query as an SQL statement yourself, at best using abstraction, where the SQL statement is already formulated in my head before I write it down.

In the forum you can also quickly see who offers good and interesting SQL solutions. So I then specifically searched for contributions from these my chosen ones in order to find "pearls" and develop them for myself.
New tasks are constantly being formulated in the forum. In this way you can apply your new knowledge and at some point you can also measure yourself against your role models and other experts, which is interesting in itself.

A good SQL statement must be fast, even with bulk data. That is their conceptual task. This requires that the wording of instructions also take into account things like the following:
- early volume reduction,
- shifting complex operations to the end of the processing chain, where the amount of data is already reduced,
- index usage: planning that usable indices are available + formulation of the query in such a way that indices can also be used
- existing data structures

The following script was and is my "bible" (in German):
Performance in queries from Michael Zimmermann

I then later summarized some thoughts to give back to the forum and its users:
SQL is easy --- Collection of links to the topic series

'A good SQL statement must be fast, even with bulk data' I have observed this but have no idea how actually SQL query impact the performance. For reference, when I work in Excel, I know what formulas to avoid or what loop to avoid or how to use the required looop. Will try to learn this aspect as well. Thanks for the help. Have a nice day ahead.

 

[SachAccess]

IMHO, the only way to learn the really complicated stuff is to have a problem to solve, and then struggle to solve it. That's when you learn, and come across functions not available in Access that will help you (like String_Agg, Row_Number , Common Table Expressions and a lot of other things). Most things in SQL server queries (and Access to a point) can be broken down into logical portions.

You may end up writing stored procedures to do complex data manipulation, if you do try to remember is to work with SET based data.
Explore the use of temporary tables, and table valued functions, and where possible do not loop through things in order to get performance right.
It's not always possible but will make a massive difference to the efficiency of your application.

As for the combinations you talk about there are only really Select queries and Action queries, I'm not sure what else you would be thinking of. The stack overflow SQL questions might show you some interesting techniques, but they tend to be very specific in their replies.

Thanks for the help. Have a nice day ahead.

 

[SachAccess]

One request to dear moderator(s) from my side. While learning, I will face issues, doubts.
Is it fine if I post all my doubts in this single thread only. Since I am learning I might have doubts about different objects like queries, tables, forms etc, so if I post all doubts here, experts will also be aware that it is not urgent and posted for learning purpose only.

 

[MajP]

I am old so I like learning from books. A lot of you young people have to have a video or online interactive course. With books no pop up adds, constant scrolling, tick tock videos etc. If I want to learn a computer language there are plenty of used books from $1 to $10. I get a few years old. I doubt the 2023 addition is much different than the 2015 edition. Even if they are not the greatest it is no big loss. Normally a lot of these books went form 60-100 dollars.

Here is just one of hundred of sites. Not advocating this one, it just popped up.

Browse | New & Used Books from ThriftBooks

www.thriftbooks.com

I thought this one looked real good for $5.00. Might get it.

SQL: The Ultimate Guide From Beginner To... book by Peter Adams

www.thriftbooks.com

The reason I know so much about Access and have been a VIP on multiple forums is that I read this book (best book ever written) from cover to cover. This is the best $4 investment if you want to develop Access. Yes a lot has changed, but 90% still good.

Access 2002 Desktop Developer's Handbook: 9780782140095: Computer Science Books @ Amazon.com

Access 2002 Desktop Developer's Handbook: 9780782140095: Computer Science Books @ Amazon.com

www.amazon.com

 

[CJ_London]

@SachAccess

is it fine if I post all my doubts in this single thread only.

No, they will be on different topics rather than the broad heading of 'sql' or 'queries'. One of the purposes of this forum is to share ideas and solutions. If you have multiple questions on different aspects all in one thread, that purpose is defeated. Imagine if w3Schools just had the one webpage covering all the different types of sql queries - how easily could you find what you are looking for, particularly if you are not sure of the correct terminology.

there are two forums one for sql server questions and one for queries

SQL Server

Ask your SQL Server questions here.

www.access-programmers.co.uk

Queries

Talk about Queries

www.access-programmers.co.uk

there are others for forms, reports, etc - you can find all the forums here

Access World Forums

Microsoft Access discussion forum

www.access-programmers.co.uk

By all means have a follow up question on the specific topic in the same thread even a few months later, but to switch between sql server, different types of queries, forms, etc - No.

 

[SachAccess]

I am old so I like learning from books. A lot of you young people have to have a video or online interactive course. With books no pop up adds, constant scrolling, tick tock videos etc. If I want to learn a computer language there are plenty of used books from $1 to $10. I get a few years old. I doubt the 2023 addition is much different than the 2015 edition. Even if they are not the greatest it is no big loss. Normally a lot of these books went form 60-100 dollars.

Here is just one of hundred of sites. Not advocating this one, it just popped up.

Browse | New & Used Books from ThriftBooks

www.thriftbooks.com

I thought this one looked real good for $5.00. Might get it.

SQL: The Ultimate Guide From Beginner To... book by Peter Adams

www.thriftbooks.com

The reason I know so much about Access and have been a VIP on multiple forums is that I read this book (best book ever written) from cover to cover. This is the best $4 investment if you want to develop Access. Yes a lot has changed, but 90% still good.

Access 2002 Desktop Developer's Handbook: 9780782140095: Computer Science Books @ Amazon.com

Access 2002 Desktop Developer's Handbook: 9780782140095: Computer Science Books @ Amazon.com

www.amazon.com

Sir am certainly a book guy. Will definitely consider your suggestion. I am from India, will check the price accordingly. Prefer hard copy over soft copy. Thanks for the help. Have a nice day ahead.

@SachAccess

No, they will be on different topics rather than the broad heading of 'sql' or 'queries'. One of the purposes of this forum is to share ideas and solutions. If you have multiple questions on different aspects all in one thread, that purpose is defeated. Imagine if w3Schools just had the one webpage covering all the different types of sql queries - how easily could you find what you are looking for, particularly if you are not sure of the correct terminology.

there are two forums one for sql server questions and one for queries

SQL Server

Ask your SQL Server questions here.

www.access-programmers.co.uk

Queries

Talk about Queries

www.access-programmers.co.uk

there are others for forms, reports, etc - you can find all the forums here

Access World Forums

Microsoft Access discussion forum

www.access-programmers.co.uk

By all means have a follow up question on the specific topic in the same thread even a few months later, but to switch between sql server, different types of queries, forms, etc - No.

Missed to reply to your help yesterday. Apologies. Thanks for the help. Have a nice day ahead.

PS - Using ChatGPT since morning to create my question bank. Copying content in my word file.
Seems to be very useful.
Will be helpful for future reference, direction. No, not using for copying the code.
General doubts I have about SQL, Access etc. It is pretty quick as compared to Google.
With Google, we need to click on a different URL for each question we might have.
Here we can have N number of question in a single session. In my region is limited to one hour.
Cooling period is one hour.

 

[Isaac]

In my region is limited to one hour.
Cooling period is one hour.

What does it mean?

 

[Isaac]

@SachAccess
I think it is a good idea to get the 'big picture' before studying the details of SQL. By big picture I am thinking of things like which are the different RDBMS, ANSI vs. wrappers, and different SQL options.

If possible, think of where you want to go in these areas.

There are arguments to be made for focusing on ansi SQL for a while first, and there are arguments to be made for going straight to one of the brands. I'm not here to suggest either, but I do suggest getting a firm grip on who the players are, and whether where you want to go career-wise might make it a better idea to just learn ansi-sql vs. T-SQL or mysql etc.

The reason I bring this up is that generic websites like W3 tend to mix and match a lot of stuff jumbled together. It's not necessarily 'bad', but it is just good to know what you are learning and why. Stuff to think about I guess is all I'm saying

 

[isladogs]

Here are three articles about queries from my website which you may find useful depending on your current skill level

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

www.isladogs.co.uk

Speed Comparison Tests 8

This article covers various methods of optimising queries and the use of the JET ShowPlan feature. It is the eighth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your...

www.isladogs.co.uk

How Access Stores Queries 1

This article explains how Access uses the MSysQueries system table in conjunction with other system tables to display the query structure in the query design window.

www.isladogs.co.uk

 

[The_Doc_Man]

This site has covered almost all the main types of queries (at least this is what I think). However, while writing and editing these queries I realized that I can add more combinations to the queries. I was successful with some, faced issues with some. This adding combination was just my imgination.

It is not wrong to try to compose unusual combinations of queries, but you can still miss out when you do this because your imagination is often limited by your limited experiences. (If your experience was unlimited, what have you got left to learn?) Remember Hamlet, Act 1, Scene 5: There are more things in heaven and earth, Horatio, than are dreamt of in our philosophy.

We can learn by seeing things in a textbook but such things rarely cover all possibilities and the case studies are frequently contrived or worse. We learn more when we approach a new problem and solve it, or when we see case studies with unfamiliar problems and learn how they were solved. We learn when those new things were based on situations we never would have imagined in a million years. We learn because following along on new problems helps us to see the methods used to solve problems using SQL and/or a support language such as VBA. It is one thing to solve a problem. It is entirely another to see a way to approach problems in an orderly manner.

'A good SQL statement must be fast, even with bulk data' I have observed this but have no idea how actually SQL query impact the performance.

SQL statements are fast because, unlike using VBA and a recordset loop, the SQL statements are compiled to operate in a loop driven by machine code. (VBA is not - it is an emulator.)

Also, VBA is executed in Access GUI context but SQL is executed directly in JET/ACE context. It is not well appreciated, but Access has two parts... the GUI/developer environment, and the database engine environment. They are two different environments, implemented under Windows as two different tasks with two different memory areas. Part of the speed issue is that if the query is not fully self-determined, it might have to step out of that context to do something in the GUI process, then step back into the SQL process. When you can build a complete query that has everything it needs to work, you skip the context switches. There is no data exchange until you are done. For action queries, this is almost mandatory if you want any reasonable speed.

It is possible for you to write a query that uses VBA code but the repeated context switching slows you down tremendously. Another thing that people do is to use VBA-callable functions in queries. For example, putting a DCount in a query rather than building a separate query or sub-query that requires no context switch to generate a summation.

 

[cheekybuddha]

Here is a great site I came across recently:

Database and SQL Roadmap: Everything You Need to Know About SQL

There’s a lot to learn with SQL. Once you understand the basics, you’ll feel comfortable running queries on tables. But what else is there to know? What other things do you need to know about, and …

www.databasestar.com

 

[SachAccess]

It is not wrong to try to compose unusual combinations of queries, but you can still miss out when you do this because your imagination is often limited by your limited experiences. (If your experience was unlimited, what have you got left to learn?) Remember Hamlet, Act 1, Scene 5: There are more things in heaven and earth, Horatio, than are dreamt of in our philosophy.

Thanks a lot for the help. Sorry for late reply, I was away due to some emergency in family.

 

[SachAccess]

Here is a great site I came across recently:

Database and SQL Roadmap: Everything You Need to Know About SQL

There’s a lot to learn with SQL. Once you understand the basics, you’ll feel comfortable running queries on tables. But what else is there to know? What other things do you need to know about, and …

www.databasestar.com

Thanks a lot for the help. Sorry for late reply, I was away due to some emergency in family.

 

[SachAccess]

@SachAccess
I think it is a good idea to get the 'big picture' before studying the details of SQL. By big picture I am thinking of things like which are the different RDBMS, ANSI vs. wrappers, and different SQL options.

If possible, think of where you want to go in these areas.

Thanks a lot for the help. Sorry for late reply, I was away due to some emergency in family.