Emil Drkusic
SQL-related jobs - the data model

Learn SQL: SQL-Related Jobs

September 1, 2020 by

In today’s article, we’ll take a look at 4 common SQL-related jobs and roles and explain what kind of tasks you could expect at each of these roles and what types of queries you’ll possibly use to solve these tasks. If you’re already in any of these roles, you’re probably familiar with this, but if you’re new to the world of SQL, you could find this very useful to decide in which way you want your career to develop.

Data Model

Today, we won’t dive much into coding, but the queries we’ll mention shall be related to the model we’re using in this series.

SQL-related jobs - the data model

Besides that, we’ll use queries that are not only related to the data model presented on the picture above but rather on the server level and related to the INFORMATION_SHCEMA database.

Before we start describing each role, it’s important to mention that most of the SQL-related jobs shall be related to more than one role. This depends on the size of the organization, the amount of the work that has to be done, and the internal organization.

Database Designer/Developer

I’ve decided to start with the database designer role. The reason for that is that this role is the first logical step when you think of working with databases – you simply need to have a database designed and ready before you can work anything with it (build logic, populate it with data, analyze data, and performance). Also, I’ll mention database developer tasks together with the database designer role because they have much in common.

There is a little chance you’ll only work as a database designer, especially if you’re part of the larger organization. The reason is pretty simple – you mostly have one or a few databases used throughout the organization, and, after the initial work is done, there are usually not enough tasks for the full-time job. So, you can expect that you’ll be in charge of designing databases and performing other roles, too – mostly programming and DBA roles, but analytical roles are not excluded as well.

On the other hand, if you’re a one-man-band/freelancer, there is a chance you could specialize in this role and work on many different projects and avoid wearing multiple SQL hats that way.

As a database designer/developer, you’ll be in charge of defining or programming:

To perform tasks successfully, you should have good knowledge of:

  • Database related concepts like data modeling and database design patterns
  • How this database shall be used and what other roles (DBA, BA, data scientist, end-users) want to achieve when using this database
  • Database programming and writing database queries to create SQL commands, stored procedures, functions, etc. This is required if you’re a database developer

Most of your work would be related to translating business requirements to the data model. Still, there is a possibility you’ll need to use SQL queries that would be more closely related to the DBA role. One example would be if you want to test if you’ve set everything up as planned – e.g., verify that all tables have defined primary keys and foreign keys.

SQL INFORMATION_SCHEMA query

Database Admin (DBA)

DBA or database admin does some tasks you would expect from an IT admin, and some more since this is an SQL-related job. In a larger organization, you’ll probably have this role defined because there is a lot of work that someone in this role has to do.

If you wear this SQL hat, you’ll be in charge of:

  • Installation and maintenance – this covers everything from installing a new version of the database server, configuring it, and the maintenance. Also, you can expect that you’ll be in charge when transferring data between old and new systems
  • Backup and recovery – Performing backups regularly and preforming restores when that time shall be needed. You could say – “There are two kinds of people, those who back up their stuff and those who have never lost all their data.” or better “There are two kinds of people, those who back up their stuff and those who’ll start doing it.”
  • Authentication – Defining users and permissions they have while working with the database.
  • Security – Testing the overall security of the database and performing best-practices to reduce the chance of data-related attacks
  • Monitoring performance and database tuning – This is an ongoing work that requires establishing processes to track bottlenecks and detect if something is slowing down everything. If so, the steps are needed to be taken to tune up everything
  • ETL (extract, transform, load) – In case we have a data warehouse (DWH), it’s expected that DBA shall take care of the ETL process inside your organization

As a DBA, you won’t mostly use SQL queries, but rather “play” with different tools and SQL Server and tools like DBATools.

Data/Business Analyst (BA)

The business analyst (BA) is not only an SQL-related job/role, since you could analyze the business in many different ways (organization, processes, “paper” stuff). Still, talking about business analytics today is pretty hard without considering data analytics.

As a business analyst, you’ll use SQL to retrieve the important information from the database and generate reports to mainly do kind of tasks as:

  • Analyze the as-is situation to see where the organization currently is
  • Play with trends and apply some expected future events to predict future results (or trying different scenarios based on parameters)

This role is much more interesting to us because most of the data is stored in the database, and you’ll need a good knowledge of how to get what you need from the database. To master this SQL-related job, you’ll need to join multiple tables, use aggregate functions, combine what you know and write complex queries, use pivot tables, and export data to Excel.

As a business analyst you could expect that you’ll write queries like the following ones:

SQL query using AVG function

SQL-related jobs - BA query

Data Scientist

Being a data scientist today is a red-hot role when we talk about data and SQL-related jobs. Still, this title is sometimes used in the wrong manner. So, let’s see what data scientists usually do.

Some important tasks are:

  • Collecting data from different data sets, either structured ones or unstructured ones
  • Analyzing the collected data and cleaning it to ensure accuracy and completeness
  • Identifying the problems organization currently has and going out with ideas on how to solve them
  • Analyzing previously prepared data to find patterns and trends
  • Performing advanced analytics using algorithms that work with big data
  • Drawing conclusions from the analysis performed and presenting results to decision-makers

Maybe the best way to describe a data scientist would be a business analyst on steroids. In this role, you can expect that you’ll use not only SQL queries but also a number of different tools, prepared algorithms, programming languages (Python, R, Java, Scala). The goal is to avoid inventing the wheel and focus mostly on the results and spend more time on drawing conclusions out of them.

Conclusion

Today, we’ve discussed 4 common SQL-related jobs/roles. Sometimes it’s hard to define where one role stops and another one begins because there is too much overlapping. Still, you can expect that you’ll need to use SQL queries in any of these 4 roles. Therefore, stay tuned to find out what is next in this series.

Table of contents

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query?
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Learn SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL Best Practices for Deleting and Updating data
Learn SQL: Naming Conventions
Learn SQL: SQL-Related Jobs

Emil Drkusic
Latest posts by Emil Drkusic (see all)
SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby... You can find him on LinkedIn View all posts by Emil Drkusic

357 Views