Why Every Database Administrator Needs SQL

What is a database administrator? What do they do? Why do they need to know SQL, and what do they use it for? In this article, I'll answer these questions.

As technology advances, more and more data is available to help organizations make informed decisions. You can't stay competitive – or provide good service to your customers – without the right information.

Database administrators are responsible for making sure the right data is stored safely, that it's easily accessible to those who need it, and that it’s protected from malicious attacks. This is one job that's not going to go away in the foreseeable future.

The most important tool in the database administrator's workbox is SQL, or Structured Query Language. With SQL, you can define what data is to be stored, how it will be stored, and who can access it. You can extract data, convert it to another format, and move it to a different location. SQL allows you to monitor how the database is performing and fix any bottlenecks that may occur.

In short, SQL allows you to manage the data in a database in every possible way.

If you'd like to know more about databases and how SQL allows you to work with them, LearnSQL.com's SQL Basics course is a great place to start. You'll learn what a database is, how it stores information, and how to run simple queries to extract and aggregate the data. This course takes about 10 hours to complete and includes over a hundred interactive exercises you can complete in your browser. By the end of the course, you'll know whether you enjoy working with databases and if a career in database administration is something you might want to pursue.

What’s SQL and Why Do Database Administrators Need it?

In the 1960s, the development of better data storage techniques allowed large amounts of data to be stored. Various programs to manage this data and access it quickly and efficiently began to evolve, and the concept of the database was born.

In the 1970s, most of the industry adopted a technology known as a Relational Database Management System (RDBMS). This is still the most popular method of data storage today, although some specialized databases use different structures geared towards specific needs. You can read more about relational databases, sometimes known as SQL databases, in What Is an SQL Database?. Many Cloud-based databases are also relational databases.

If you'd like to know more about what types of databases are used today, check out our article on the most popular databases in 2023.

SQL – which was initially developed by IBM – became the standard language for creating, accessing and managing relational databases. Defining standards and revisions to this language is now the responsibility of ANSI (American National Standards Institute). Even most popular non-relational databases allow data to be extracted and managed with SQL, since it's convenient and easy to use.

SQL consists of English-like commands to carry out database-related tasks. An SQL command to extract data may look something like this:

SELECT account_number, name, balance FROM customers ORDER BY account_number;

SQL has commands for:

• Retrieving data from a database.
• Creating and altering the structure of databases, tables, and indices.
• Inserting, updating and deleting data.
• Granting and revoking permissions to control who can access what data.
• Updating the statistics used by the RDBMS to decide the best way of finding data.

Most RDBMS systems maintain a lot of information about database usage in their own system tables. These can be accessed via SQL to monitor and tune database performance. These tasks make up the bulk of database administrators' work, so SQL is the one tool they use more than any other. This article gives more information about SQL queries.

What Does a Database Administrator Do?

The database administrator is the person who takes responsibility for data stored by an organization. This is a highly responsible position because accurate and easily-accessed data is essential to any successful business. Information is needed both for day-to-day operations, and for marketers and decision-makers to plan their strategies.

Let's have a look at some of the tasks that make up a database administrator's job.

Creating and Adjusting the Database Structure

Relational databases consist of tables containing columns and rows. These tables are indexed for fast access, and they are related to each other via keys. A database also has rules to ensure data integrity: for example, you can set a rule that the account number entered on a sale must exist in the customer table.

The database administrator uses SQL to create these structures and adjust them according to the changing needs of the organization. In small to medium organizations, the database administrator will work with business analysts and developers to design the database. In very large organizations, this task is carried out by a database architect – who very likely has past experience as a database administrator.

Allocating Access to Users

Originally, only IT staff had access to databases. This is no longer true. The trend towards data democratization – making data directly available to decision-makers – means a large number of people need data access. To maintain data confidentiality and integrity, it's important that only the right users have the right kind of access to information.

The administrator controls who can access what part of the database and whether they are allowed to make changes to it. In a small organization, the database administrator may be the one to design security policies, but in a large organization, a database security expert will take over this responsibility.

Backup, Recovery, and Archiving

The database administrator must design and implement adequate backup and recovery procedures to make sure that data:

• Cannot be accidentally lost.
• Is recoverable in case of malicious damage.

For critical applications, data mirroring may be implemented; if one server fails, another automatically takes over. It's also important to have well-designed archiving processes, so that old data is moved to somewhere where: 1)  it can be accessed when needed, but 2) it doesn't clutter up the database and affect its performance.

Ensuring Hardware and Software Performance

Database administrators are usually responsible for ensuring that software updates and upgrades are carried out, and part of this responsibility includes implementing protection from malware to safeguard the database against security threats. They'll also make sure the hardware is performing as it should, adding extra hard disks, memory, or processing power if the organization outgrows its original computer setup.

ETL Tasks

ETL (extract, transform, load) is the process where data is moved and repurposed for specific usages. One example is maintaining a data warehouse. It's a good practice to have an operational database (which is used in day-to-day tasks like making a sale or paying a creditor) along with a data warehouse.

The data warehouse is used to store data for analyzing trends and creating reports; it also keeps historical data. Each database is optimized for its own type of task, so when data is moved to the warehouse, it may be summarized or stripped of any information that's not historically significant.

Automating Processes

Many database tasks (such as backups, ETL, archiving, and creating regular reports) are repetitive and need to be automated. A database administrator sets up these processes and schedules jobs to run them as needed.

Performance Monitoring and Tuning

As an organization grows, the volume of data stored and the number of users who need to access it also increase. This results in performance issues, where access to the database becomes slow. The database administrator must continually monitor performance, identify bottlenecks, and find ways to correct problems as they arise.

SQL for Database Administrators

SQL is a versatile tool that can be used to carry out most of the database administrator's tasks, although many RDBMS systems also have a menu-driven interface. Even where a menu is available, administrators will often use SQL instead. It provides more control, plus it's easy to automate repetitive tasks with SQL scripts and stored procedures.

Let's look at some of the ways a database administrator may use SQL.

1.   Creating database structure

Although this can often be done using a menu, SQL has the advantage that the instructions can be saved and reused. You might either create the same structures in a different database or to create similar structures for a different purpose. SQL lets you do this by making a few needed changes to your script.

Let’s examine a simple SQL statement. It will create a table called customer that contains an account ID, name and balance for each customer:

CREATE TABLE customer
(
account_id		char(10),
name			varchar(50),
balance		decimal(12,2)
);

You'll notice that I've specified a data type for each column (e.g. char(10) for the account_id); this defines what type of data will be stored in that column. In this example, the column can contain any characters and will be 10 characters long.

If I wanted the table to be indexed on account_id, I could use this SQL instruction:

CREATE INDEX account_index ON customer(account_id);

SQL can do many more administrative tasks in this area, such as:

• Modifying existing tables.
• Setting constraints to maintain data integrity.
• Creating views so users are restricted to seeing only the data relevant to their role.

If you want to really dig into this subject, LearnSQL.com has several courses that teach you how to create and maintain database structures:

• Data Types in SQL
• SQL Constraints
• Views in SQL
• Creating Tables in SQL

2.   Managing User Access

Controlling which users have access to which data is an important part of the administrator's job. SQL allows you to specify that a user can access a particular table in a particular way:

GRANT SELECT ON TABLE customer TO Fred;
GRANT UPDATE ON TABLE customer TO Charlie;

These commands allow Charlie to update customers, but Fred can only extract information from them.

If Charlie leaves the company, you can remove his access permissions:

REVOKE UPDATE ON TABLE customer FROM Charlie;

3.   ETL Operations

SQL is the simplest way to carry out ETL operations. Suppose you had an operational database containing details of each sale. You also wanted a table in the data warehouse that would contain aggregates by year, month, product type, customer type, and location that could be used by data analysts to quickly analyze trends over several years.

The operational database may only hold this year's sales, but you want the analytical data to go all the way back to when the company started business.

Every month, you could run an SQL statement like the one below to extract, transform, and load the data into the data warehouse:

INSERT INTO warehouse.sales_history
SELECT
year(sale_date),
month(sale_date),
product_type,
customer_type,
location,
sum(items) AS num_sales,
sum(sales_value) AS total_value
FROM last_month_sales
GROUP BY year(sale_date), month(sale_date), product_type, customer_type, location;

4.   Performance Monitoring and Tuning

Most RDBMS systems maintain system tables. The administrator can use SQL to query these tables for useful information. For example, they can answer the questions:

• What's causing bottlenecks in performance?
• Are any users behaving suspiciously?
• Do we have enough disk space?

An example of an SQL command that can be used to monitor disks on SQL Server is:

SELECT * FROM sysalerts WHERE name = 'Disk Warning';

Administrators can also use the SQL UPDATE STATISTICS command to make sure the database engine uses optimized statistics when deciding how to carry out a query.

5.   Automating Tasks

SQL has two facilities that can be used to automate tasks:

• Stored Procedures: These are stored lists of SQL commands that can be re-used whenever needed.
• Triggers: These are SQL scripts that will run automatically whenever a certain event takes place. This could be used to alert account managers in a bank whenever an unusually large withdrawal has been made.

SQL vs. NoSQL

I've been asked whether database administrators will still need SQL in the future, since a lot of applications use NoSQL databases. My answer is yes, they definitely will.

First, the term NoSQL leads to some confusion, since many people think a NoSQL database does not use SQL at all. In fact, the term is short for Not Only SQL. NoSQL databases can (and often do) allow you to access them using SQL, but they're not limited to SQL.

Second, large organizations don't rely on a single database for everything. They may use a NoSQL database for fast access to a limited range of data, such as the details of products to be displayed on a webpage. At the same time, they will often use one SQL database for their data warehouse and another for their accounting and management information. This is because SQL is flexible and fast for analytical uses. And it integrates with many data analysis and visualization tools, such as Tableau and Power BI.

Even if your organization exclusively uses a NoSQL database, it's still worth learning SQL because:

• SQL is by far the easiest database management language to learn. All databases have many concepts in common; by learning SQL, you can quickly gain a good understanding of database management tasks. This will help you when you learn to manage NoSQL databases.
• There's a good chance that as your organization grows, it will make use of SQL databases as well as NoSQL databases.
• If you change employers, you'll have in-demand skills that can be used in your future career. SQL is compatible with almost all the major databases, so you won't be limited in your job searches.

Although some databases use other languages to carry out database-related tasks, these are often complex and hard to learn. Let's compare a simple query in SQL with the same query in MongoDB. Suppose we wanted a list of customers from New York who spent more than $10,000 last month.

In MongoDB, the command looks like this:

db.customer.find({city: "New York", {last_month_purchases: {$gt: 10000}}})	

In SQL, the command would be:

SELECT * from customer WHERE city = 'New York" and last_month_purchases > 10000;

SQL also has the advantage of wide community support. If you don't know how to do something, there's a good chance there will be examples in various forums and support groups to help you.

So although NoSQL has its place, it's essential that a database administrator should have excellent SQL skills.

Although the SQL language has been around for almost 50 years, it’s still keeping its place in a changing world. Like the wheel, it's a great invention that’s unlikely to become obsolete. It's simple to use, and it does its job. That's not to say SQL doesn't evolve with the times: it definitely does.

• New capabilities are added from time to time as needs arise.
• An increasing number of data analysis and data management tools integrate with SQL.
• Cloud storage providers like Google include the ability to use SQL to manage and extract the data.

You can read more on the future of SQL elsewhere in our blog. For now, let’s return to the database administrator career path. Can you make a good living doing DB admin?

Database Administration Career Opportunities

This is one job that's not going to be replaced by Artificial Intelligence! Since AI requires large amounts of data in order to make decisions, it can only increase the demand for administrators to manage that data.

The US Bureau of Labor Statistics (BLS) projects a faster-than-average growth rate of 8% for database administrators.

The same organization publishes statistics on what database administrators earned in 2022. According to their website, the average annual salary is approximately $102,000 USD, with a low around $50,000 and a high of $150,000.

Opportunities for Career Growth

Experience as a database administrator is a must-have for progressing your career in areas such as:

• Database Architect. This job involves planning and implementing data storage and management policies for large organizations. According to the BLS, the salary range for this job is $75,000 to $197,000 USD.
• Information Security Analyst. This job entails planning and implementing security policies for enterprise data. The BLS lists the salary range as $66,000 to $175,000

For more information, read this article on database-related jobs and this one on the future of data engineering jobs.

Keeping Your Database Administrator Skills Up to Date

Since information technology is such a fast-changing field, database administrators need to continually keep up with current trends, innovations, and skills.

Here are a few suggestions for making sure you never get left behind in your career.

• Keep an eye on any SQL-related news released by ANSI.
• com's blog regularly publishes informational articles on SQL-related topics.
• Read database-related books and articles. Here are some good book recommendations.
• If you're really serious about your career, you may like to purchase LearnSQL.com's All Forever Package. This will not only give you lifetime access to all our existing courses, but also to any SQL-related courses we may develop in the future. Since we're always upgrading our courses and adding new ones to keep up with the latest technology, you'll be able to learn new skills as SQL and relational databases evolve.

Take the First Step to Becoming a Database Administrator

Being a database administrator is a great career. It offers plenty of opportunities, and you'll be contributing to the growing field of information technology.

If you think you may be interested in database administration, it's a good idea to start by gaining some skills in SQL.  Not only will these skills help you in your career, you'll also find out more about what database administration involves and whether you enjoy working with databases.

Here are some course suggestions:

• SQL Basics Course. This course is for beginners and teaches you about databases and how to extract information from them
• Creating Database Structure. This learning track teaches you important administration skills, including how to create and modify databases, tables, indices, and constraints.
• SQL From A to Z. This learning track takes you all the way from SQL beginner to SQL expert.

Keep ahead of the game and start learning SQL today!