9
$\begingroup$

I'm currently learning SQL (specifically T-SQL) and focusing on searching databases, not building/editing them. This is pretty much the first language I'm learning, so I don't have a CS background to draw on.

I've worked through the tutorials at Code Academy and SQLBolt. They gave me a great foundation into the basics but when I look at actual SQL searches in the database I'm using, they're much more complex than the ones in these tutorials and use query terminology that's never introduced, to the point that I'm somewhat overwhelmed and confused by just looking at them.

Part of this is related to how simplistic the examples are in the tutorials and part is because the database I'm looking at is huge. Dozens of tables, some with 20+ columns each and lots of ways to compare the information.

The difficulty I'm having is that proficiency seems to require knowing what searches are "easy" with no actual experience... and then finding out what is even possible to simplify the search I'm trying to write.

Considering the complexity of the database I'm using and my current skill level, how can I ease into more complex searches while still finding the results I need from the information?

Some notes:

  • I do have people who are willing to help me as needed but I'm concerned about bothering them too much with stuff that should have been simple for me to solve on my own, particularly as they are quite busy. How can I determine what's simple and what's complex?
  • Another part of this is that talking with someone isn't a great way for me to learn, as I quickly forget what they've said, so I don't retain the information. I want to learn what they're telling me, not just have it redone "correctly" for me. The tutorials have been good because they give me the chance to actually write the searches myself.
$\endgroup$
  • 3
    $\begingroup$ Welcome to Computer Science Educators. This is a great question, and I believe you'll get some excellent answers. I hope you do, and that you hang around a while as well. Although it's often quiet, you may also wish to pop into the site's chatroom on occasion. $\endgroup$ – Gypsy Spellweaver Aug 15 '18 at 15:49
  • $\begingroup$ You might get some useful information from this related post: Advice for becoming more confident in programming $\endgroup$ – Kevin Workman Aug 15 '18 at 18:42
  • $\begingroup$ Once you get into it a bit more, you'll realize that the number of columns does not increase the complexity, only the number and types of keys, and the types of relationships. Most of the columns on any table are "just cargo" from the perspective of database design. $\endgroup$ – pojo-guy Aug 16 '18 at 3:13
  • 1
    $\begingroup$ @pojo-guy It increases complexity when it takes 5-10 minutes to go through which columns are even an option. :) If I don't know what I can select for, learning all of that takes time, particularly when the columns are in a list view with sections by table. It kinda looks a lot like the Stack Exchange SEDE: data.stackexchange.com/stackoverflow/query/new But with more columns. $\endgroup$ – Kaba Kun Aug 16 '18 at 13:12
  • $\begingroup$ Years of experience here - knowing what to ignore and for how long. Start with the unique indexes and foreign key relationships. Identify root tables and their children. Ignore the other columns until you have the relations under control. $\endgroup$ – pojo-guy Aug 16 '18 at 15:41
6
$\begingroup$

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:

  1. Apply it,
  2. Dissect it,
  3. RTFM

The long of it (tl;dr)

Apply it

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.)

Dissect it

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.

$\endgroup$
  • $\begingroup$ What do you do when the manual is broken, has important things missing, not maintained, or new products/technologies don't even get a manual? $\endgroup$ – YetAnotherRandomUser Aug 21 '18 at 21:48
  • $\begingroup$ I'm not a programmer, though... I don't have any "old projects"... and I'm not sure what you mean in your last paragraph in that section. Why would I go to all the work to create a database from scratch that won't be anywhere near as complex (or in the same syntax) as the one I need to learn to use? This seems like a lot of work for little gain. I'm not actually even sure what you mean by "code based projects". I'm using the database directly, not trying to implement it into something - imagine something like SEDE here on SE. $\endgroup$ – Kaba Kun Aug 28 '18 at 2:38
  • $\begingroup$ @KabaKun The suggestion to use MySQL was "If a local install of a server with T-SQL is not possible" The paragraph in question was because I thought, correctly, that you would be using direct DBMS access. Why to use something less complex than the one at work is because you don't design a skyscraper until you can design a flat-level house. You have to have basic knowledge and keep building into larger, more complex projects. "A lot of work for little gain" defines any educational exercise. The "gain" is experience. $\endgroup$ – Gypsy Spellweaver Aug 28 '18 at 4:29
  • $\begingroup$ But I don't need to learn how to build a database. I just need to learn to use one. I don't have access to the system to add, remove, or change content. If all I'm doing is searching the data, I don't need a simpler database to practice with, I just need to know how to determine what types of searches are simple to do so that I can use the actual database for practice. $\endgroup$ – Kaba Kun Aug 29 '18 at 5:57
  • $\begingroup$ A "simple" search uses one table, retrieves one column, and is selected by one value in one field. Anything else has to build on that. Practice - in some fashion is the only way you will "move to more complex work." If you cannot understand the big database, use a smaller one. If none of the suggestions here are what you are willing to do, then you'll have to just keep asking your work associates to help. $\endgroup$ – Gypsy Spellweaver Aug 29 '18 at 9:39
1
$\begingroup$

Since you have access to people who can help and who are more advanced than yourself, I'd suggest that you ask them, as often as you can, to give you a search task that you can carry out. If they start with only moderately difficult ones and work toward the more complex you will probably be able to figure it out with the practice.

In general, the best way to learn something is to practice it.

You can also ask them to review your searches if you can't decide if they re correct, but this will increase the load on them. If these other people actually work with you then you can begin to be a better contributor.

$\endgroup$
1
$\begingroup$

One thing you can make for example, is participating in websites such as:

  1. Stack Over Flow in english or even spanish
  2. giving answers to some questions, gives you the opprtunity to increase your level
  3. Obviously you can write a tutorial about the basics about SQL you have learned with topics such as:
    • connection to the database
    • creating and giving permissions to users
    • sintax about Create/Read/Update/Delete data from your tables

After the basic level you can make much more for example

  1. learning sintax about CTE
  2. how to make and building pivot tables
  3. using multiple CASES for building complex queries that produce new columns into your own query

Another way to improve your level is for example starting to create a complex demo database about: bussiness, accounting or an school control

Here you got an example

WITH usuariosPosts AS(
    SELECT row_number() OVER(ORDER BY idPost) AS Listado, users.nameUser, posts.idPost, posts.namePost, posts.statusPost, posts.created_at
    FROM users
    JOIN posts
    ON users.idUser = posts.user_id
)

SELECT Listado, nameUser, namePost, COUNT(namePost) AS 'Total', IF(statusPost, 'activo', 'inactivo') AS 'Status',
         GROUP_CONCAT(namePost SEPARATOR '/') AS 'Títulos', created_at
FROM usuariosPosts
GROUP BY nameUser;

which has Common table expressions, group and order by functions, group_contact function, window functions and obviously mutiple JOINS starting from scratch I mean this is the result of a very simple exercise about a database blog idea

Create a learning critical route

For example you can organize it in these stages

  • Connection
  • User creations and permissions
  • Creating databases and tables
  • making relationships between tables
  • CRUD operations
  • Union or Union ALL
  • dates operations
  • CTE
  • schemaless with JSON data type
  • JOINS, LEFT JOIN, RIGHT JOIN FULL OUTER JOIN
  • CASES
  • etc
$\endgroup$
1
$\begingroup$

If you are starting to learn about computing with databases and SQL, congratulations! you have chosen the very best place to start, because the whole point of computers is to store and manipulate data, usually lots of it. SQL is not the same kind of 'language' as most programming, but it is so well-suited and understandable that it is a great introduction.

My advice for learning any topic area, particularly to do with computing is to

Make It Visual

It is not for nothing that we have had GUIs and visual programming environments for decades. Trying to comprehend database structure without a chart is like trying to walk around a room with your eyes closed. Microsoft Access has a way of showing the database schema with basically one click. It is the single most helpful part of the product! Other products can create charts or diagrams too, so find a way.

I would suggest though, that you try to "roll your own" by taking a sticky-note pad and for each table, write the name at the top, then the primary key columns, foreign key columns and maybe a few other useful columns. Stick these down on a big piece of paper or a whiteboard and draw lines to connect the keys. Highlight columns that are not indexed but might be good for searching on. Experiment until the structure of the database is clear to you!

From there, it is a piece of cake to write Select statements. Once you understand visually how a database is designed, try learning a standard diagramming method like UML. With a picture of the schema and pile of sticky-notes, you can move the world. Don't try to do it blind.

$\endgroup$
0
$\begingroup$

It sounds as if your stumbling block is less to do with a knowledge of SQL, and more that you lack understanding of the structure of the specific database/tables at work.

Broadly, I agree with Pojo-guy's comments - advice suggesting you look at (and diagram/document) each table's primary keys (and foreign keys). Can I assume you have learned about joining tables in SQL? FKs show you where and how the DBA expected tables to relate. Gradually then, you build knowledge of this "structure".

A.N.Other talked about MySQL which is not a lot of use if you are limited to the mentioned tool-set. The MySQL product/project includes a tool called MySQL-Workbench which enables reviewing a DB of tables and "reverse engineering" a Data Structure Diagram. Oracle (and others) offer similar tools. Apologies: I can't speak for T-SQL. Might there be something similar?

I've run courses for business people (ie non-CS) in data retrieval from DBs. Feel free to request more specific detail, if needed... Regards =dn

$\endgroup$

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.