Structured Query Language (SQL) is a way of interacting with relational databases. But what does that really mean? What is a relational database, and what interactions might a database administrator (DBA) need to have with it?
Relational databases store information in named tables consisting of rows. The tables are linked by keys that uniquely identify each record in a table. Users create SQL queries to organize and pull information from the database. Business units can then use the results to display basic information, such as inventory, or more abstract data, such as business insights or trends.
This article introduces SQL and its interaction with relational databases. It discusses the language, gives examples of its use and contextualizes it with other languages you might encounter. While SQL isn't a programming language used to develop applications, it's a unique syntax or communication scheme that goes beyond regular systems administrator command-line or scripting skills.
Many iterations of SQL exist—think of it as an umbrella term with various implementations available. These implementations are relational database management systems (RDBMS).
Here are a few of the essential SQL RDBMS:
- MySQL: An open-source RDBMS that is extremely common. Includes cloud and enterprise editions.
- MariaDB: An open-source RDBMS forked from MySQL. This popular system includes container versions in addition to enterprise-class solutions.
- MS SQL Server: Microsoft's SQL-based RDBMS solution, which runs on-premises and in the cloud. The system is one of Microsoft's flagship products. It runs on Windows, Linux and container platforms.
- PostgreSQL: Another open-source RDBMS known for its performance and reliability. Available for Linux, macOS, Windows, BSD and Solaris.
- SQLite: Universal SQL implementation committed to stability, cross-platform support and backward compatibility. According to the vendor site, more than 1 trillion SQLite databases are actively used!
The primary difference between them is syntax usage. While concepts are usually the same, specific functions vary somewhat. If you're just starting with the SQL language, I suggest picking a single "dialect" and learning it. Once you've got the basics, it will be easier to switch to other similar products. MySQL and MS SQL Server are good places to begin.
SQL documentation refers to portability, or the ability to use SQL code with multiple dialects. Portability is not perfect, and moving from one SQL implementation to another may take some effort. Still, the dialects are very similar.
SQL Key Features
SQL includes some basic features common to all dialects. These features enable database users to manage database structures and the data stored within them.
Common choices include:
- Query databases to retrieve data for use
- Create, modify and remove tables within a database
- Add, modify and delete data within a database
- Control access to that data, restricting users to specific roles
- Enforce data consistency and integrity by constraining data entry
SQL is the language for managing databases using these features.
Uses Cases for SQL Databases
Databases store enormous quantities of information in a structured way—in the case of SQL, that way is tables. Organizations use databases to store information about products, customers, vendors, financial transactions and more. Storing the information isn't helpful if you can't retrieve it in a way that makes sense. That's where SQL comes in.
SQL Statements and Keywords
You construct SQL statements to work with databases. These statements consist of keywords. Keywords are not case-sensitive, but many DBAs prefer to use uppercase for each to increase code readability. That's probably a good habit to get into. Some SQL dialects require a semi-colon (;) at the end of statements. Check the documentation for whichever SQL dialect you choose to work with.
Here are some common keywords for working with data:
- SELECT: Returns data from the database
- INSERT INTO: Adds data to the database
- UPDATE: Modifies existing data in the database
- DELETE: Removes data from the database
Use the following keywords to work with databases and tables:
- CREATE DATABASE: Creates a new database
- ALTER DATABASE: Modifies an existing database
- CREATE TABLE: Adds a new table to a database
- ALTER TABLE: Modifies an existing table
- DROP TABLE: Deletes a table
Indexing a database speeds up searches—important with very large databases. Indexing uses the following keywords:
- CREATE INDEX: Creates an index
- DROP INDEX: Deletes an index
Notice that the keywords are intuitive. Need to construct a new database for your project? Use the CREATE DATABASE keyword. Removing information from a database? That's the DELETE keyword. Keywords may be verbs ("create") or nouns ("table"), helping to make their function more obvious. Often, they combine, such as with CREATE TABLE or DROP TABLE.
There are many additional keywords. Consider the following examples to understand more about SQL's potential:
- VIEW: A virtual table based on the results of a stored query (the query is stored, not the results). Creating views makes it easier to gather information from the database.
- BACKUP DATABASE: Create a full backup of an existing database to the specified location.
Combine keywords with specific arguments to manipulate data. Here are a few examples of statements using some of the keywords above:
CREATE DATABASE
customersDB;
CREATE TABLE
Employees (
EmployeeID int,
LastName varchar (255),
FirstName varchar (255),
Role varchar (255)
);
BACKUP DATABASE
customersDB
TO DISK = 'E:\dbbackups\customersDB.bak';
SQL Management Tools
SQL users can choose from many tools of varying complexity to manage their databases. These tools provide code completion and syntax checking. Many also offer a graphical representation of the SQL infrastructure, making them relatively easy to use.
Here are a few common examples:
- MySQL Workbench: Visual interface for working with databases, available for Linux, macOS and Windows.
- JetBrains DataGrip: Code completion, syntax checking, suggests fixes.
- Microsoft SQL Server Management Studio: Complete solution for managing SQL database environments. Includes script editors and graphical tools.
Get Started With SQL
Getting started with SQL comes down to two tasks: Selecting a SQL RDBMS (dialect) and working with data.
The first thing to consider when selecting a SQL RDBMS is your end goal. If you're working toward a promotion at your current job, select the kind of SQL your organization uses. If you're job hunting, choose a SQL that is common in the field, such as MySQL or PostgreSQL. You may strongly support open-source software and want to consider MySQL Community or MariaDB.
Vendors provide detailed deployment instructions. The general installation steps are:
- Choose a SQL type
- Download the code or installer
- Follow the installation instructions
You now have a database environment to work in.
Next, begin building a database so you can work with commands and learn syntax. There are many project ideas available online, but you can essentially just choose anything you want to store information on. Are you a musician with a bunch of guitars? Create a database to track your instruments. Do you collect fantasy novels? Add them to a database. Do you hike various trails frequently? Keep track of your favorite trails and their attributes (distance, difficulty, trailhead locations, etc.) in a database.
Once you create a database, you can really start working with SQL statements. Query the database for various types of information. Add and remove records to simulate business events. As you begin to work with more complex statements, you may need to add additional data and tables.
You'll have plenty of resources. Consider formal classroom training, online tutorials, vendor documentation, non-vendor websites and your colleagues. SQL has been around a very long time; there's a lot of information out there.
Like any language, SQL requires practice. Commit to working with it regularly, or you'll begin to forget what you've learned.
What Is NoSQL?
Most discussions of SQL end up referencing NoSQL (Not Only SQL) at some point. NoSQL is a different approach to managing data in a database.
SQL databases store information in pre-defined structures called schemas. This design is good for complex queries of structured information. Not all information is stored this way. NoSQL works with large quantities of dynamic unstructured data. NoSQL stores data in JSON documents consisting of key-value pairs. These documents are gathered into collections. This structure is significantly different from the fixed tables found in SQL databases. As a general rule, NoSQL databases scale more easily than SQL databases.
Common NoSQL databases include:
When to Use SQL
You can find SQL in almost every industry. After all, nearly every organization needs to track data, and even small businesses want reports about customers, inventory, vendors and proprietary information. Job prospects are high with this skill, and you can expect them to remain that way.
SQL Versus Other Programming Languages
While SQL isn't a programming language in the sense that Python[e1] is (meaning you don't create games or other apps in SQL), it does have its own structure, rules and syntax. The Stackoverflow 2023 Developer Survey ranks it very high on the list of common languages to learn.
SQL is often considered easier to learn than some programming languages. It uses a fairly logical declarative approach, and it's a reasonable place for new developers to start.
Database administration is a huge area in the IT field. Databases are an essential component of nearly every business infrastructure, and they may be found on-premises and in the cloud. Learning to use the Structured Query Language (SQL) provides you with the concepts and tools to manage the data today's organizations need to maximize their potential.
With so many open-source SQL solutions for relational database management, it's easy to start with just your computer and some information (real or fictional) you want to store. Sharpening these skills could lead you down a whole new career path. Start today by studying SQL statement syntax and downloading a tool such as MySQL.
Understanding and working with SQL can help you become a database administrator (DBA). Being CompTIA Data+ certified shows employers that you have the skills to succeed as a DBA. CompTIA Data+ validates the skills of IT pros with hands-on experience manipulating data, applying statistical methods and analyzing complex datasets throughout entire data lifecycles.
Learn the skills you need with CompTIA CertMaster Learn. Sign up today for a free trial today!
