17th Nov 2022 7 minutes read What Is an SQL Database? Luke Hande sql database Table of Contents What Is a Database? Relational Databases SQL Databases SQL Database Options Time to Learn SQL SQL databases have been used for decades and have grown in popularity, becoming one of the most common data management tools. They provide the user with an efficient way to store the data and an intuitive way to access or modify it. In this article, we’ll start by explaining what a database is and showing some examples. We’ll move on to how databases can be used to efficiently work with large amounts of structured data. Then we’ll show you some examples of different types of SQL databases and when to use them. We won’t go into the details of writing SQL queries, but if you are looking for some learning material, a good place to start is the SQL Basics course. It starts from a beginner level and works up to some more advanced queries. And it includes a ton of interactive exercises. What Is a Database? You may be familiar with storing data in a spreadsheet, which is handy for smaller amounts of data. These amounts of data you typically store locally on your computer. Spreadsheets are great for single users who don’t need to manipulate the data too much. For example, such users don’t often combine data from different sheets. But if you need to work with larger amounts of data from different sources, you might need more advanced functionality. This is where databases come into the picture. A database is a software product designed to hold large amounts of information in a highly structured way. A database could be stored locally on your personal computer or remotely on an external server. In the latter case, you’ll need to use a program to connect to the server over a network so you can access the data. Question: What is a database? A database is a structured set of data stored on a computer. Databases should be organized to efficiently store, retrieve, and manage information. They are essential for data analysis and management and are a key part of data-driven decision-making. Behind the scenes, a database does a few things to make your life easier. It efficiently manages the storage of large amounts of data, i.e. millions to billions of rows, which isn’t even possible with a spreadsheet. In a database, there are internal consistency checks to ensure any new data added to the database conforms to predefined data types. For example, certain entries may only be allowed to contain text or numbers. In larger projects, many people usually work on the same data. A database can manage concurrent access to and simultaneous modifications of its data. This means multiple people can use the database at the same time without interfering with each other. Also, user permissions can be managed for security. For instance, some users may only be able to read the data, while others may also be able to add or modify it. Relational Databases We mentioned that the data in a database is highly structured. But how exactly is it structured? The most common type of database is known as a relational database. Here, data is organized into tables that contain columns and rows, with a unique key identifying each row. The columns represent different variables (or attributes), and the rows are measurements (or instances) of those variables. If you work with spreadsheets, this may be conceptually familiar to you. In a relational database, data is typically stored in multiple tables. However, data from different tables can be combined. This can make the whole database quite flexible. Consider the following example: we have a company with several employees. We can store the name and job title of each employee in a table called Employee. The table would look like this: FirstNameLastNameJobTitle SarahWilcoxCEO LiYangDepartment Head EveTaylorDeveloper LiamNelsonDeveloper In addition to this, we can define another table called Job. It contains the salaries and weekly working hours for each job title: JobTitleSalaryHoursPerWeek CEO160,00050 Department Head130,00045 Developer115,00035 We can store these tables in a relational database, and we can combine information from each table to easily find out how many hours per week each developer must work or their yearly salary. Each row has a specific data type. The entries in the JobTitle column can only contain text information, while the Salary column can only contain a number. If a new developer joins the company, you just need to add a row to the Employee table and you automatically know their salary and how many hours they need to work. There are many more situations where relational databases can be used. A university database, could have a table for all the students in a department, the courses they’re enrolled in, and the students’ grades. In the same database could be another table with lecturers’ names, the courses they’re teaching, and what time their lectures are held. A relational database could store information for an online shop, including its customer information, sales and order information, and product information (i.e. what they sell). But it’s not just information relating to people or products that can be stored in a relational database. Time series data could be stored in tables; each row would represent a different point in time. Imagine measuring temperature every 10 minutes during the day. Your records could be stored in one table; records of the amount of rainfall every hour could be stored in another table in the same database. In this way, relational databases can be very flexible. SQL Databases We’ve done a bit of groundwork to get to the point of understanding what an SQL database is. The term SQL database is more or less a synonym for a relational database. It’s a database with the same structure we described above; it is composed of one or many tables that contain data. Relational databases are called SQL databases because SQL is the language designed to talk to relational databases. This means the SQL language is used to access and modify the data within an SQL database. This is why the language was originally developed, as we discussed in our article What Is SQL?. An SQL database is typically built with SQL and designed to work optimally when the SQL language is used to manage the data within it. With a SQL database, you can use the intuitive and easy-to-learn syntax of SQL to take advantage of the database’s advanced functionalities and manage large amounts of data and many users. SQL was developed with relational databases, but nowadays it is used with many other data technologies. To learn more, check out our article SQL Technologies Are More Common Than You Think. SQL Database Options There are a number of software products available that allow you to create and manage an SQL database. If you’re working in a team, you probably won’t be able to choose which database you work with. However, knowing what options are available and when they should be used is important. In our article What is SQL Used For?, we go into a bit more detail about how different people utilize SQL databases. One of the most popular products available is MySQL. This typically runs on an external server, meaning you first need to connect to the server to start using the data. SQLite, on the other hand, is useful for smaller projects where a database on your local machine is sufficient. This is a good option for beginners to gain a bit of experience with SQL databases. SQL Server is another very popular database management system. It’s an easy-to-use solution that’s a good choice for situations with more complex requirements. However, you’ll have to pay to use it. PostgreSQL, also known as Postgres, is another free option. This is a very mature and popular solution that can deal with multiple data formats. The last database system we’ll mention is Oracle. It is commonly used for online transaction processing and data warehousing. However, it is not particularly beginner friendly. Time to Learn SQL In this article, we discussed relational databases and how SQL can be used to work with data stored in a database. We’d like to encourage you to take the next step and start learning SQL. For some tips on how to proceed, see our article How to Learn SQL Fast. This can then become another programming language to put on your CV for your next job application. And yes, SQL is a programming language; we explain the reasons why in Is SQL a Programming Language?. Tags: sql database