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.
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:
- tables
- constraints
- indexes
- triggers (database developer)
- stored procedures (database developer)
- functions (database developer)
- views (database developer)
- other database objects
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE our_first_database; -- join tables and constraints data SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME, SUM(CASE WHEN INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 1 ELSE 0 END) AS pk, SUM(CASE WHEN INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'UNIQUE' THEN 1 ELSE 0 END) AS uni, SUM(CASE WHEN INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN 1 ELSE 0 END) AS fk FROM INFORMATION_SCHEMA.TABLES LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME GROUP BY INFORMATION_SCHEMA.TABLES.TABLE_NAME ORDER BY INFORMATION_SCHEMA.TABLES.TABLE_NAME ASC; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
-- the difference between AVG call duration per employee and AVG call duration SELECT single_employee.id, single_employee.first_name, single_employee.last_name, single_employee.call_duration_avg, single_employee.call_duration_avg - avg_all.call_duration_avg AS avg_difference FROM ( SELECT 1 AS join_id, employee.id, employee.first_name, employee.last_name, AVG(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_avg FROM call INNER JOIN employee ON call.employee_id = employee.id GROUP BY employee.id, employee.first_name, employee.last_name ) single_employee INNER JOIN ( SELECT 1 AS join_id, AVG(DATEDIFF("SECOND", call.start_time, call.end_time)) AS call_duration_avg FROM call ) avg_all ON avg_all.join_id = single_employee.join_id; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
-- select number of calls per day in the given month DECLARE @date DATE; DECLARE @start_date DATE; DECLARE @end_date DATE; DECLARE @loop_date DATE; -- declaring a table variable DECLARE @dates TABLE (date DATE); -- setting the first and the last date in the month given by date SET @date = '2020/01/12'; SET @start_date = DATEFROMPARTS(YEAR(@date ), MONTH(@date ), '01'); SET @end_date = EOMONTH(@date); -- populating a table (variable) with all dates in a given month SET @loop_date = @start_date; WHILE @loop_date <= @end_date BEGIN INSERT INTO @dates(date) VALUES (@loop_date); SET @loop_date = DATEADD(DAY, 1, @loop_date); END; SELECT d.date, COUNT(call.id) AS calls FROM @dates d LEFT JOIN call ON d.date = CAST(call.start_time AS DATE) GROUP BY d.date; |
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
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
- Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
- Learn SQL: Dynamic SQL - March 3, 2021
- Learn SQL: SQL Injection - November 2, 2020