By Stefanie Jahnke
Spreadsheets like Excel are pretty commonly used for calculating or visualizing data. They’re helpful tools and quite easy to master. But if you are dealing with a huge amount of data, such programs get overwhelmed easily. Our marketing department for instance sometimes has to work with around five million data sets just to get one piece of information. It can get pretty annoying to use a spreadsheet for that purpose — I’m sure they could tell you a thing or two about it.
Some programs won’t even open files that contain so much data. And even if you do manage to open your files, performing a single operation on the data set can take several minutes. The marketing team often only receives plain CSV files that contain relevant information, information that they must find a way to get.
This is where our IT department comes in. To support marketing with their problems and to increase the collaboration between teams, Florian Brick and I started offering an SQL school for non-developers and especially for the marketing department.
SQL, which is an acronym for Structured Query Language, is a programming language that is used to communicate with databases. A database is a place where a huge amount of data can be stored in a structured and relational way. Relational means that attributes of database entries can be shared across tables, thus enabling data to be accessed, combined, and sorted in almost any way you can think of. Using SQL, you can create these database tables, as well as insert, view, and update your data. You can also view data from multiple tables if you’re able to connect them to each other and it’s possible to filter out a subset of data which fulfils specific conditions. Databases are made for working with huge amounts of data and you can easily import CSV files into them as well.
The only problem: Our marketing team is not comprised of SQL experts. That is precisely what we wanted to change. In order to reach that goal, the “teachers” from IT and around twelve “students” from marketing and several other departments came together.
The first session started with a crash course on SQL and database fundamentals. To allow for easy interaction between user and database, it is helpful to have a database management system. There are many open source applications available, such as MySQL, MongoDB and PostgreSQL. We decided to work with the latter.
Every participant created their own database and some tables. Data was inserted into the tables and some first easy commands like select statements were practiced. In keeping with the principle of “learning by doing”, every student worked on their own computer and brought theory into practice.
In the following sessions, the more advanced select queries were explained. It was especially hard to get across how the “group by” clause, “sub-selects”, and “joins” work. While solving the exercises, the group developed their own collaborative dynamic. The students that were able to solve a task explained the strategy to the ones still struggling with it.
After three sessions of practice and explanations, the basics were clear and the possibilities SQL has to offer were understood. For the last two sessions, some real-life examples were planned. The marketing team brought data from their daily work, which we inserted into the database together. They explained their problems with Excel and other tools. We assured them that using the SQL database there won’t be any issues related to huge amounts of data and that most queries will only take a few seconds to execute. After listing the questions that needed to be answered concerning the data set, the group solved the tasks together.
For most of the required information, only a simple query was needed. But as our marketing colleagues told us it ended up saving them hours of work which would have been necessary without SQL. For the more advanced queries of course the marketing department can still ask for help from their colleagues in IT.
From my point of view, the SQL school was a great success not only in terms of gaining expertise in SQL and making the life of our marketing team a little easier, but also in terms of collaboration between departments and the understanding of what the other team’s work life really looks like.