Querying MariaDB: Use SQL Operations, Data Extraction, and Custom Queries to Make your MariaDB Database Analytics more Accessible (English Edition)

By Adam Aspin

This book will take you on an engaging experience to understand SQL querying from the most basic to the most expert level, using various SQL techniques to extract valuable insights from your MariaDB databases. The adventure will begin with the most basic level and end with the most professional level.

This book's first section provides an introduction to the fundamental concepts of both SQL and MariaDB. It then guides users through building queries using simple and then more complex table joins and progressively more powerful data filtering to generate advanced queries utilizing various SQL techniques. The reader will understand MariaDB database analysis better by learning the basics of SQL computation, relational logic, and data grouping and aggregation. Also covered are SQL basics like formatting queries, making subqueries, making derived tables, joining and filtering tables, etc. In addition, the book explains how to conduct rolling analysis, investigate time-series data, and manage enormous and complex MariaDB datasets.

When readers have finished the book, they can devise their techniques for analyzing relational MariaDB datasets and acquiring the necessary insights.

• Language: English
• Publisher: BPB Online LLP
• Release date: Jul 25, 2022
• ISBN: 9789355512604

Book preview

Introduction

Whatever the motivation, you know that the data is stored in MariaDB, and you want to access this information and learn how to slice it, filter it, tweak the presentation, and deliver the output you need.

This book is the first step on this journey. It aims to teach you the essentials of SQL (Structured Query Language) querying using data stored in a MariaDB database. It presumes that you have no previous knowledge of SQL or MariaDB and introduces you to the core concepts, structures, and approaches that you will need to write basic SQL queries.

Why Learn SQL?

Most of the data that fuels businesses throughout the world is stored in relational databases. Nearly all of these databases are queried using a variant of Structured Query Language (SQL). So, simply put, SQL is key to data analysis. A mastery of SQL will help you to delve deep into the data that is stored in corporate databases. You can apply SQL to analyze the data and then present it in a clearly understandable form.

SQL can usually serve a vital role in preparing the data for final delivery, whatever the output application that you are using to present your analysis. Most end-user tools have an option for entering SQL to help derive meaning from the underlying data sources. Consequently, a knowledge of SQL can help you analyze data faster and more clearly. The aims of this book are to give you the necessary mastery of SQL to enable you to get the most out of your data and to deliver the insights that will drive your competitive advantage.

Why MariaDB?

MariaDB is one of the world’s leading databases and has an ever-growing user base. That’s why learning the MariaDB flavor of SQL has the immediate potential to be a career-enhancing move. As the data store for innumerable corporate, commercial, and web-driven databases, this mature and impressive system is used to power data analysis across the globe.

What Is SQL?

SQL (pronounced Ess-Queue-Elle) is the standard computing language for managing and querying data in relational databases. Every database has its own flavor of SQL, and these variants are subtly different. MariaDB uses its own dialect of SQL. While the core elements are the same as those found in other databases, there are many subtle differences between the SQL used by MariaDB and other flavors.

This means that learning plain vanilla SQL will soon leave you struggling with queries that use MariaDB. It can also mean that you will find yourself unable to extract the deeper insights that a mastery of SQL on MariaDB can provide.

So, right from the start this book takes you into the world of MariaDB and SQL. This way you will learn to use the MariaDB dialect of SQL to its full capacity, not limiting yourself to underperforming queries and not missing out on features that other flavors of SQL simply do not have.

However, the MariaDB flavor of SQL is, fortunately, close to the SQL used by rival databases. Therefore, learning SQL with MariaDB will set you on the path to applying standard SQL query techniques in most of the available relational databases currently deployed.

Who This Book Is For

This book will help anyone who wants to know more about using SQL to deliver analysis. This means you could be any of the following:

A data analyst

A student

A database developer

A finance professional

A business analyst

A job seeker looking to get ready for a technical interview

A trainee preparing for a SQL exam

Indeed, this book is for anyone who needs to deliver accurate analytics from the data stored in a MariaDB database.

What This Book Will Bring You

This book was written to help you, the reader, to become proficient in querying databases using SQL. It will help you to master a language that might seem arcane or even weird at first sight. To overcome any initial reticence you may have, it progresses step-by-step through all the core concepts and techniques that you need to master. This way, you learn the essential keywords that you need to query MariaDB databases progressively, without information overload. To make SQL comprehensible, the book introduces each new concept or keyword individually so that you can learn each element in isolation. As the book progresses, you will learn how to combine SQL keywords to extend the power of SQL as you learn to create more powerful queries.

However, no one queries databases purely for fun, so each query that you apply in this book also has a purely practical purpose. You also see how to develop real-world queries that deliver essential data analysis. You can then adapt these queries to your own requirements using your own data.

How to Read This Book

You can use this book in several ways, depending on your knowledge of SQL and your real-world requirements. Some examples of how you could choose to read it are discussed next.

SQL Novices

If you are a complete beginner, then you can begin with Chapter 1 and progress through the book until you feel that you have attained a level of SQL skills that matches your needs. This book is designed to be a complete SQL querying course that allows readers with no previous SQL experience progressively to gain the skills and experience they need.

Refreshing Your Knowledge

If you are coming back to SQL after being away for a while, then you should probably skim through the first few chapters until you start meeting techniques and approaches that are less obvious. Then you can slow down and concentrate on progressing through the book and consolidating as well as refreshing your knowledge.

Coming from Another SQL Database

If you are proficient in another SQL database and are used to writing SQL queries, then you can probably skim through the first few chapters fairly quickly and concentrate more fully on the later chapters. The later chapters contain information that is specific to the flavor of SQL used by MariaDB. This variant of SQL might be slightly different from the version you are used to using.

In-Depth Querying

If your needs are more advanced, then you might want to begin by skimming through the initial chapters in this book and use them to provide inspiration on how best to solve your specific problems. You can then take a deep dive into the later chapters to make quite sure that your advanced SQL querying knowledge is up to scratch.

The Structure of This Book

This book is aimed at true MariaDB beginners. It presumes no previous knowledge of SQL or MariaDB and helps you progressively to acquire the core knowledge that is required to carry out basic SQL queries.

It consists of the following 21 chapters:

Chapter 1, Writing Basic SQL Queries: This chapter introduces the basic concepts of SQL and MariaDB and shows you how to write simple queries.

Chapter 2, Using Multiple Tables When Querying Data: This chapter extends your knowledge by showing you how to join tables to return data from more than one table at a time. It also explains many of the ways that you can join tables.

Chapter 3, Using Advanced Table Joins: This chapter discusses how to use more advanced table joins.

Chapter 4, Filtering Data: This chapter introduces a fundamental concept: filtering the data that you want to use.

Chapter 5, Applying Complex Filters to Queries: This chapter shows you how to combine filters to produce more complex queries.

Chapter 6, Making Simple Calculations: This chapter shows you some of the ways that you can apply basic math to the data in a MariaDB database.

Chapter 7, Aggregating Output: This chapter explains how you can use SQL to group and aggregate data to deliver analysis.

Chapter 8, Working with Dates in MariaDB: This chapter introduces you to some of the essential ways that SQL handles dates and can use dates to deliver analysis over time.

Chapter 9, Formatting Text in Query Output: This chapter shows you some of the key ways that SQL can be applied to format queries.

Chapter 10, Formatting Numbers and Dates: This chapter introduces a series of techniques that you can use to change the way that dates and numbers appear in the final output.

Chapter 11, Using Basic Logic to Enhance Analysis: This chapter concludes the book with an introduction to using SQL to analyze data and deliver added value.

Chapter 12: Subqueries. This chapter shows you how you can use independent SQL queries inside other queries.

Chapter13: Derived Tables. This chapter teaches you how to compare data at different levels of aggregation or carry out calculations that mix and match different ways of grouping data.

Chapter14: Common Table Expressions. This chapter explains a powerful way to simplify working with complex datasets.

Chapter15: Correlated Subqueries. This chapter shows you how you can use certain kinds of subquery to filter the data in the outer query in a specific way.

Chapter16: Joining and Filtering Datasets. This chapter introduces methods of handling data spread across a series of tables with similar or identical structures.

Chapter17: Using SQL for More Advanced Calculations. This chapter teaches you how SQL can go much further than simple addition, subtraction, division, and multiplication and how SQL handles numbers.

Chapter18: Segmenting and Classifying Data. This chapter introduces ways of prioritizing and classifying lists of data in order to analyze the elements that really matter.

Chapter19: Rolling Analysis. This chapter covers ways of discerning trends, tracking growth, and establishing a solid factual base that you can use for your analysis.

Chapter20: Analyzing Data Over Time. This chapter goes deeply into techniques that you can apply to track the evolution of sales, profits, or, indeed, any metric over any time period: from years to days to hours and seconds.

Chapter 21: Complex Data Output. This chapter concludes the book with a look at ways of shaping output so that the essence of your analysis is immediately comprehensible. These techniques will also help you to present your analysis in various ways that can make the results easier to read and understand.

The Sample Data and Sample Queries

To help you learn SQL, the sample data as well as all the SQL queries in this book are available on the BPB Publishing website. You can download and install the sample data into a MariaDB database on your PC and practice SQL querying using this data to help you learn SQL.

If typing the queries is a little laborious (though I do recommend it as an excellent way of learning), then you can also download all the queries in the book and simply copy them into the querying application that you are using to see the results.

MariaDB Versions

The queries in this book have been written using version 10.3.9 of MariaDB. However they should all work on any previous version of this database.

Time to Get Started Querying MariaDB

That is enough about the theory! It is time to get up close and personal with MariaDB.

The first thing you will need is a working copy of the latest version of MariaDB. This book was written with MariaDB 10.3.9, and I strongly advise you to install this version (or later) of the database, as described in Appendix A.

Once you have a functioning database, you will need an application that you can use to test your queries. Many such apps are available, but I advise that you use MariaDB Workbench. Indeed, this is the application I have used throughout this book. You can find out how to download and install this tool in Appendix B.

Finally, you will need some sample data to work on. For this (and especially if you want to test your SQL using the examples in this book), you will need to download and set up the sample database PrestigeCars. This is described in Appendix C.

You are now ready to start on your journey toward becoming a SQL querying maestro.

Have fun!

CHAPTER 1

Writing Basic SQL Queries

Prerequisites

It may seem obvious, but you will need some data in an accessible database before you can start your analysis. So, throughout this book I will be asking you to develop your analytical skills with the aid of a sample database named PrestigeCars. This database contains a small amount of data concerning sales of vehicles by a fictitious British car reseller. If you want to try the examples in this chapter, you will have to download the sample database from the BPBPublishing website and install the database into a version of MariaDB. So, it follows that now could be a good time to set up the sample database as described in Appendix C, unless you have already done so. Of course, you can install the sample database only if you have a version of MariaDB already installed and available. So, if you are not in an enterprise environment where MariaDB is already accessible, you will need to install a version of the database software before anything else. This is described in Appendix A.

Once MariaDB is up and running, you will need somewhere to enter and run your queries. I am presuming that you have also installed MySQL Workbench (or one of the other interfaces that are available for MariaDB) as the tool to query the sample database. Installing this piece of software is described in Appendix B. I presume that you will be trying your queries using this particular application.

MySQL Workbench is not the only tool that you can use to query databases. There are indeed many excellent apps that you can use to analyze MariaDB data. So, if you prefer to use another application to test the queries in this book, then that is entirely up to you. However, you will have to handle the specifics of installing and working with that application yourself; there are simply too many of them for us to explain every one!

Whatever the tool that you use to write your queries, this chapter will teach you how to

Query MariaDB databases using MySQL Workbench

List the contents of tables

Select only certain fields in tables to display

Display only a few records from a table

Give columns new names in your query output

Sort your output

When you have all the prerequisites in place, it is time to move on to the core focus of this chapter and start querying MariaDB data.

Note: If you know a little about the standard MariaDB data tools and if you have a basic knowledge of databases, then feel free to skip past the first few sections of this chapter until you find the parts that are new to you. However, I realize that the first steps for a novice are important. Consequently, I prefer to start from the beginning and provide all the information that you are likely to need to get the most out of your SQL learning experience

1. Relational Databases

As you have decided to learn to analyze data using SQL queries, you need to know a few basic concepts to begin with.

To start, what is a relational database (or a relational database management system, RDBMS)? At its simplest, a relational database is a method of storing data in a clearly defined way. A database consists of tables (sometimes thousands of tables) that each contain rows of data. All the rows in a table consist of the same number of columns. So, a table is really nothing more than a well-structured list—rather like the ones that you have probably encountered in Excel.

In a well-designed database, tables will be organized to avoid duplicating data. The tables in the database can then be linked together to present the data in different ways.

To resume, then, a relational database is a collection of lists (tables) containing columns (fields) of data in a set of rows (records). These elements can then be accessed independently or joined together to deliver the analysis you are looking for.

Conceptually, a database looks something like Figure 1-1.

Figure 1-1: Conceptualizing a relational database

Note: Please note that all the exercises in this book use a sample database named PrestigeCars. If you need to install this database, then please consult Appendix C.

2. Running MySQL Workbench

The time has come to start putting the theory into practice. As relational databases can require considerable IT horsepower, they will nearly always reside on powerful servers either on-premises or in the cloud. They are rarely installed on PCs or laptops unless they are being used for learning.

So, what you will nearly always do is connect to the database using a separate piece of software that is, itself, installed on a PC. This is called a client-server model, where you use one application (the client) to connect to the database (the server). While there are many applications that can read data from MariaDB databases, there is one that tends to be used by most analysts and developers. This is MySQL Workbench. It is an application currently distributed by Oracle and is (at least at the time that this book went to press) free to install and use. As befits a piece of software that has been evolving for many years, it is both efficient and reliable. Indeed, it is the standard tool that is used by hundreds of thousands of analysts and developers. Consequently, this is the tool that we will be using throughout this book.

I will assume that you have MySQL Workbench installed on your PC. If this is not the case, then please consult Appendix B to learn how to find and install this tool. Otherwise you will have to run the application that you have chosen to work with. Indeed, you may prefer not to use a graphical user interface at all, but to query MariaDB using a command line client. The choice is entirely up to you.

To open MySQL Workbench, follow these steps (exactly how you do this will depend on the actual operating system as well as the version of the operating system that you are working with):

Under Windows 10, you can open the Start menu and expand the MySQL folder, where you will find the MySQL Workbench option. You can see this in Figure 1-2. (On a Macintosh, open the Applications folder and double-click MySQL Workbench).

Figure 1-2: Running MySQL Workbench

Select MySQL Workbench.

MySQL Workbench opens and displays the Welcome screen with the server details, as you can see in Figure 1-3.

Figure 1-3: The MySQL Workbench Connect to Server dialog

Select the server name from the available connections. If you are in an enterprise environment, you can always ask a system administrator which database to use. If you have just installed a stand-alone version of MariaDB on your laptop or workstation (this is described in Appendix A), then just select MariaDB from the available connections. You might see a warning dialog like the one shown in Figure 1-4.

Figure 1-4: The MySQL Workbench connection warning

As this warning is largely irrelevant when querying MariaDB just click Continue Anyway. You will see MySQL Workbench, ready for you to begin working, as shown in Figure 1-5.

Figure 1-5: MySQL Workbench

Expand the PrestigeCars database in the list of databases in the Navigator window on the left by clicking the small triangle to the left of the database name. You will then see all the elements (or objects as geeks call them) that are contained in this database, as shown in Figure 1-6.

Figure 1-6: The PrestigeCars database

You may be wondering why MariaDB should use the graphical user interface from another database. Well, MariaDB and MySQL have a lot in common, and there are also many companies and institutions currently using MariaDB that have switched from MySQL, but have grown used to using MySQL Workbench as the preferred front end tool. So it seems rational to introduce you to this piece of software.

There are, of course, many other GUIs (graphical user interfaces) that you can use when querying MariaDB. So feel free to install the one that suits you best. The SQL that you will enter and the results that you will obtain will be the same.

3. Connecting to a Database

Before you can do anything at all with MariaDB, you need to tell MySQL Workbench (or the GUI that you are using) which database you want to work with. After all, you could be using a corporate or educational system with hundreds of available databases.

To connect to a database:

Double-click on the database that you want to connect to (PrestigeCars in this example) in the Navigator window of MySQL Workbench.

You will see that the active database now appears in boldface in the MySQL Workbench Navigator window.

Now that you have connected to the database, you are ready to start analyzing the data it contains. I realize that the first time you carry out this sequence of instructions the process may seem a little laborious. However, you will probably launch MySQL Workbench only a couple of times a day when you start working with MariaDB. So, it is not really any different than opening desktop applications when you first start creating documents or editing spreadsheets. In any case, this routine will certainly become second nature in a short time.

4. Displaying the Tables in a Database

All the data in a MariaDB database is stored in tables. A database can consist of dozens—or even hundreds—of tables of data that have been carefully designed and created by database professionals. The first thing that you will have to do when faced with any database that is new to you is to take a look at the tables it contains. To do this, follow these steps:

Expand the Tables folder by clicking the small triangle to the left of the Tables folder in the PrestigeCars database. You should see something like the list of tables shown in Figure 1-7.

Figure 1-7: Displaying the tables contained in a database

As this sample database is small, it contains only a handful of tables. You will learn what they all contain as you progress through this book and in the next volume.

5. Finding All the Views in a Database

SQL-based databases do not just contain tables. Indeed, they can contain hundreds of different items (that are collectively called objects). One of the aims of this book is to introduce you progressively to the essential objects that you will need to learn to manipulate when querying data.

Apart from tables, one kind of object that you need to know about is the view. Quite simply, although all data is stored in tables, you could frequently find yourself querying not only tables but views too. This is because views are a way of looking at data that is filtered or combined in some way, essentially making life easier for users by doing some of the work for you.

In SQL queries you can treat views exactly as if they were tables. The first thing to know is how to find the views in a database. Fortunately, this is similar to what you just did when looking at the database tables.

Expand the Views folder by clicking the triangle symbol to the left of the Views folder in the PrestigeCars database. You should see something like the list of views in Figure 1-8.

Figure 1-8: Displaying the views contained in a database

As you can see, there are even fewer views than tables in the PrestigeCars database. Indeed, there is only a single view named SalesByCountry.

6. Using the Command Line Client

Although we live in an age of graphical user interfaces, you can also query MariaDB using a command line client if you prefer (or if you have no other options). Here is how to connect to a database and list all the tables and views using the command line client for MariaDB that is installed by default with the product.

Launch the command line client. Exactly how you do this will depend on the operating system and the version of the operating system that you are working with. Under Windows 10, you can open the Start menu and expand the MariaDB folder, where you will find the MySQL Client (MariaDB 10.2) option. You can see this in Figure 1-9.

Figure 1-9: Running the MariaDB Command Line Client

Click MySQL Client (MariaDB 10.2). A window will open asking you for the MariaDB password. If you have installed your own MariaDB instance on your computer this will be the password that you set when installing the database software.

Enter the Password and press [Enter]. The screen will look like the one shown in Figure 1-10.

Figure 1-10: The Command Line Client with a successful database connection

Enter the following command to select the database to query

use prestigecars;

Press [Enter].

Enter the command to list all the tables and views in the database:

show tables;

Press [Enter]. The screen should look like the one shown in Figure 1-11.

Figure 1-11: Displaying tables and views using the Command Line Client

When you have finished using the Command Line Client simply enter:

Quit

followed by [Enter]

Then close the command line window.

Tricks and Traps

There are a few comments to make about the command line interface.

A Command Line interface may seem a little old fashioned, but it is certainly extremely efficient.

Each separate SQL statement that you write must end with a semi-colon.

The SQL that you enter at the command line is absolutely identical to the SQL that you enter using a graphical user interface. A command will be run, however, when you enter a semi colon and press [Enter].

A GUI may be easier to use (and is certainly prettier) but the end result of a query is exactly the same.

7. Displaying the Data in a Table

Now that you have learned how to look inside a database, it is time to look at some actual data. For your first query, let’s suppose you want to see what makes of vehicle are sold by Prestige Cars Ltd.

In the query window, type in the following short piece of SQL:

SELECT  *

FROM    make;

Select Query Execute (All or selection) from the MySQL Workbench menu to run the code and show the results. A new pane will open under the code, and the results of the query will appear as shown in Figure 1-12.

Figure 1-12: Displaying all the data in a table with SELECT *

How It Works

In just five words you have said to MariaDB, Show me the complete contents of the Make table, including all the rows and all the columns of data. All you needed to know is which table you wanted to look at. SQL did the rest.

This code snippet is incredibly simple—five words in all—but that is enough to show you how SQL works in practice. If you issue the right command, then you will get back the data you want to see.

As you can see, this command did more than just list the makes of car. It also showed any other elements that are present in the table, but that is exactly the point of the command. This way you get to see everything that is stored in a table of data.

Of course, you will need to know which table contains the data you want to display when you are dealing with your own data. If you are working in an enterprise environment, this may involve talking to the people in your organization who developed or maintain the databases. Alternatively, there may be documentation that you can read to find the information you require.

If there is no one you can ask and no documentation available, then you can still acquaint yourself with the data by running the SELECT * FROM clause with each data table that you can see in the Tables folder. However, before actually carrying out this operation I advise you to continue a little further with this chapter and learn how to limit the number of records returned by a query.

So, what exactly have you done here? Let’s take a closer look at what you have written. Figure 1-13 breaks down the SQL statement into its constituent parts.

Figure 1-13: The anatomy of a simple SQL statement

These constituent parts are:

Tricks and Traps

This was a simple SQL command, but the following are nonetheless a few key points that you will need to remember:

As I mentioned, each MariaDB instance can contain (or host if you prefer) dozens of databases. This means you always have to tell MariaDB which database you want to interrogate. One easy way to do this is to double-click the appropriate database in the Navigator before opening a new query window (by selecting Query New Tab to Current Server, for instance). Alternatively you can enter the command

use prestigecars;

And then execute this piece of code.

If you do not indicate the right database to use, MariaDB will either return no data at all or, worse, return the wrong data from another database.

All SQL commands in MariaDB must end with a semicolon.

As an alternative to using menu commands, the Windows [Shift]-[Control]-[Enter] keyboard combination runs (or executes if you prefer) the SQL code that you have typed or copied into a query window. On a Macintosh, pressing [Command]-[Enter] will do the trick. Another alternative is to click the Execute button (the yellow lightning bolt) in the toolbar.

It can happen (even when you are an experienced data analyst) that executing a query returns nothing more than an irritating error message like this:

17:38:50 SELECT  ** FROM    Make Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘* FROM    Make’ at line 1 0.000 sec.

This probably means you have made a typo or entered a wrong keyword, and so will have to check and correct the SQL that you entered and re-run the code snippet.

In this book I will explain the data tables that you need to use as you meet them. Here you have seen the first of the small set of tables that make up the core tables in the sample database. As its name suggests, the Make table contains the makes of cars that are stocked and sold by Prestige Cars Ltd.

Note: Make sure you have not selected any of the SQL text before you execute the query. This guarantees that MySQL Workbench will run the entire SQL snippet.

8. Displaying Data from a Specific Field

A MariaDB table can contain hundreds of columns. Most of the time you will want to display data from only a few of the available columns in a table. The next piece of SQL shows how you choose a single column to display instead of all the available columns in a table. More specifically, it shows how to list only the customer names.

SELECT CustomerName

FROM   customer;

Executing this piece of code (using any of the techniques that I pointed out at the end of the previous section) will show you something similar to the output shown in Figure 1-14.

Figure 1-14: Choosing a column from a table

How it Works

By replacing the star (or asterisk if you prefer) in your SQL with a specific column name, you have told MariaDB that it is this column—and this column only—that you want to display. This example also makes the point that SQL is an extremely modular and extensible language, and you can easily extend or modify a simple command to make it more specific.

Of course, this approach relies on your knowing the exact name of a column and also typing it exactly as it appears in the table. Fortunately, MySQL Workbench has ways of displaying column names and using them in your SQL, as you will discover in the next section.

Tricks and Traps

Even simple SQL commands have their subtleties. When listing data, you need to remember the following:

You can write short SQL commands like this one on a single line of you prefer. That is, you could write the following instead of placing each clause on a separate line:

SELECT CustomerName FROM customer;

This is entirely a question of personal choice. As I am presuming that you are new to SQL, we will keep the core SQL statements on separate lines in this book to accentuate the underlying logic of the language. In your queries, you can write the SQL any way you want, as long as it works. All that MariaDB wants is that the grammar of the command is technically accurate and that keywords are separated by spaces, tabs or returns.

9. Finding the Columns in a Table

You may well be wondering how on Earth you can be expected to remember all the columns in each table so that you can type them into your SQL queries. Fortunately, MySQL Workbench (like most graphical user interfaces) can help you here by displaying all the columns in a table in a single click.

In the MySQL Workbench Navigator, expand the PrestigeCars database and click the triangle symbol to the left of the table whose columns you want to see (we will use the Country table in this example).

Click the triangle symbol to the left of the Columns folder. You should see something like Figure 1-15.

Figure 1-15: Displaying the columns in a table

10. Displaying Data from a Specific Set of Fields

SQL does not limit you to displaying all the fields—or only one field—from a table. You can choose not only the fields from a table that you want to display but also the order in which they will appear in the output from the query. The following piece of code shows you how to select two fields (Country Name and Sales Region) from another table in the database—the Country table:

SELECT CountryName, SalesRegion

FROM   country;

Executing this piece of code will show you something similar to the output in Figure 1-16.

Figure 1-16: Displaying multiple fields from the Country table

How it Works

Here again, you have extended the base SQL that you used at the start of the chapter. Specifically, you have developed the SELECT statement to include the field names containing the data that you want to view. All you had to do was to separate each field name with a comma and place the field names in the order that you want to see their data in the output from left to right.

Note: The data that you see might not be in the same order as the data shown in Figure 1-16. You will learn how to apply a specific sort order in a few pages.

Tricks and Traps

There is only one major trick to remember when listing a specific set of fields.

Remember not to add a comma after the final field in a list of fields in the SELECT clause.

11. Using the Command Line to Show the Structure of a Table

Another way to visualize the structure of a table is from the command line. To display the essential information about the fields in a specific table use the desc keyword—as you can see in the following piece of sql:

desc country;

Press [Enter] to execute this short snippet and you will see something similar to the output in Figure 1-17.

Figure 1-17: Displaying multiple fields from the Country table

How it Works

The desc command followed by a table name and a semi-colon lists the structure of the selected table.

Tricks and Traps

As simple as this short command is, it nonetheless needs a couple of key points calling out.

The desc command shows the field names as well as core information about the fields themselves. However, for the moment it is only the names of the fields that interest us. We will be looking at the other information in later chapters.

You can only view the structure of one table at a time.

12. Modifying the Field Name in the Output

Many databases have cryptic—or frankly incomprehensible—field names. While as an analyst or data guru you might get used to this, it is not always a good idea to present information to users in a way that makes the data harder to understand than is really necessary. So, SQL allows you to output the data under a different field header to enhance readability. In this example, you will display the country field under another name. Start by taking a look at the code snippet and then at the output it returns in Figure 1-18:

SELECT CountryName, CountryISO3 AS IsoCode FROM country;

Figure 1-18: Changing a field name using an alias

How It Works

In the query output, the original field name is replaced by the name you have chosen—IsoCode in this example. This technique is called aliasing; you are giving the field another name in the query. Applying an alias has no effect at all on the underlying data and does not change the underlying field name. What it does do is apply a different (and ideally more comprehensible) name in the query output.

Tricks and Traps

Aliases have their own particular set of rules that must be adhered to if they are to work correctly. Essentially you need to remember the following points:

You may have noticed that all the table and field names that have been used so far contain neither spaces nor special (that is, nonalphanumeric) characters. This is because SQL requires you to specify the names of what it calls objects—that is, fields and tables among other things—in a specific way. However, I do not want to make things appear over-complicated here, so as a starting point, let’s just say you are better avoiding all nonalphanumeric characters when creating an alias for a field. Moreover, you should never use SQL keywords as aliases.

If you want to add a space to an alias (suppose in the example used in this section you want to see ISO Code as the field heading), then you must place the alias inside single or double quotes—or inside back-facing quotes (`), also known as backticks. That is, you can write any of the following—on most systemsto add a space to the alias ISOCode that you want to use instead of the real field name—on most systems:

SELECT CountryName, CountryISO3 AS 'Iso Code' FROM country;

SELECT CountryName, CountryISO3 AS Iso Code FROM country;

SELECT CountryName, CountryISO3 AS `Iso Code` FROM country;

In practice, many data people advise that you avoid spaces and nonstandard characters if you can, as once you have started down this route, you will have to add the backticks every time you refer to this alias (or table or field) in your code, which can get painful when writing complex queries. So, we will stick to names without spaces or nonalphanumeric characters in this book.

You may find that single or double quotes cannot be used to create table or field names containing spaces or non-standard characters. In this case—or if you are simply unsure—then use backticks, as these will work in all cases.

In some queries you may find that you are faced with field names so cryptic that they are hard to read. In these cases, you could try using the underscore character instead of a space in a field name. This would give an alias that looks like Iso_Code in this example. While this is certainly a little geeky, this is nonetheless easier to read than a name without any spaces while being much easier to use in more advanced (and complex) queries. An added advantage is that no quotes or backticks are required.

An alias cannot be more than 256 characters long. However in most cases getting anywhere near this limit would make the alias longer than the field name and consequently make using an alias pointless—as well as harder to read.

13. Sorting Data

Now that you can select the fields that contain the data you want to see in the sequence that you want to see them, you probably also want to sort the data. For example, you might want to sort car sales by increasing sale price. To do this, just run the following snippet and take a look the numbers in the SalePrice field in Figure 1-19. They are now sorted from lowest to highest.

SELECT    *

FROM      SalesByCountry

ORDER BY  SalePrice;

Figure 1-19: Sorting data in descending order

How it Works

To sort the data returned by a query, just add the ORDER BY keyword (it is considered to be a single keyword even if it is really two words) after the FROM clause of the SQL command. Then you add the field that you are sorting the data on. This creates an ORDER BY clause.

Equally important is the fact that, once again, writing SQL can be all about making simple extensions to the code that you have written so far. So, you do not have to produce instant reams of code that work the first time. You can start with a small snippet of code, test it, and then extend it until it does exactly what you want it to do.

Tricks and Traps

These are several key points to remember here:

The ORDER BY keyword can also be used on text (in which case it sorts in alphabetical order), on numbers (where it sorts from lowest to highest), or on dates (in which case it places the dates in sequence from the earliest to the latest).

If you want, you can add the ASC keyword after the sort field name to force an ORDER BY statement to sort the data in ascending order. However, MariaDB sorts data in ascending order out