The short of it
There are three techniques that should serve you well in this, and future, endeavors to learn practical application of complex subjects:
- Apply it,
- Dissect it,
The long of it (tl;dr)
Install a database manager on your own computer. (Your personal home computer by choice, but a work computer is possible if the learning is sanctioned and supported by the supervisor.) It does not need to be the same system you have at work, or the same one you want to eventually "know." There are quirks to every DBMS, yet they mostly try to follow the SQL standards.
If a local install of a server with T-SQL is not possible, a good choice, in my personal opinion, is MySQL. MySQL has a developer and user community that is often helpful. The manuals, for many versions, is available online, or downloadable for local use. The manual includes explicit statements of how MySQL deviates from ANSI/ISO SQL standards. Having those differences delineated allows you to know what to watch for when developing advanced statements yourself. (There are serious syntax differences between ANSI SQL and T-SQL, but the logic behind what you do is what you need to learn more than the syntax, it seems.)
Take what you know about the SQL so far and rework old projects you've done using your new SQL knowledge.
The size of the old projects can grow as you work through them, but isn't all that important. It also doesn't have to be a project that requires a database, only that it could use a database. It's the long way around, and usually not needed, but a database can hold configuration information and initial state values. Doing so can even increase the re-usability of the original code. Since it is a project you've already done, the algorithm and program logic is something you already have a handle on, and applying the new knowledge to the old problem lets you focus on the new knowledge.
If you don't have any old code-based projects of your own, which is likely in you case, then you can still use an old "problem" from elsewhere. You don't need to have a program that uses the data, since learning the C# or Perl is not the objective, only the SQL. (Often the DBA has very limited skills in other languages, but is excellent at the SQL for their system.)
Obtain real code from existing projects that use the SQL that you want to learn. Read the code and learn to follow what the original coder has done. Try to figure out other ways it could have been done, and then figure out why they choose the method they did.
Next, look for things that might change in either the dataset or the use the data is put to. Figure out how to rework the existing code to accommodation those changes.
Next, develop the requirements for a new dataset to apply to a new problem. If it's useful in your work environment, that can be a plus for later, but it could equally be useful at home. (Movies: plots, actors, directors, awards, reviews, and your collection could make a useful project.)
As a part of "dissecting" what you want to use, learning to use a different system, such as MySQL or Oracle's database, can help by highlighting the differences and similarities. Looking behind the syntax to what the DBMS "does" to fulfill commands can help you make more efficient use of the commands.
RTFM (Read The Fine Manual)
In this case, the Manual includes any community that has developed around the server you're using. If you have installed one DBMS at home and another at work, then you have two communities to draw from. You can also use some SE communities in your learning. [dba.se] is one that you might find helpful, as well as the usual ones for developers.
Often, in the manual proper, or the community around the system, there are tips, tricks, and warnings that can make significant improvements in your understanding of the system once you can fathom the how and why of the given tips, etc.
The short of it (redux)
Take what you know, practice it, explore other uses, and practice some more.