Join our book community on Discord

https://packt.link/EarlyAccessCommunity

Data collection and analysis is an old practice dating back to the beginning of civilization. Records from ancient Egyptian papyrus suggest that pharaohs collected census information from villages, possibly to determine the number of soldiers that could be enlisted for war. Local authorities would collect the census and send it to the pharaoh’s court, where the data would be analyzed to determine the possible size of the army. The same process has been repeated thousands of times in the years since then. However, it was after the arrival of modern computers that the art of data analytics became a significant phenomenon that is changing people’s lives every day.This book, as its name suggests, teaches you how to use Structured Query Language (SQL) for data analytics. SQL is the language that you will be focusing on in the rest of the book. But before diving into SQL, this chapter will provide an overview...

Technical requirements

In order to complete the exercises in this chapter, you need to have a computer that has internet connectivity, and you need to have the privilege to install software on the computer.

Describing the world with data

Start with a simple question: What is data? Data is the recorded description or measurements of something in the real world. A real-world object or event is a unit of observation. For example, an exam participation of one student is a unit of observation. Data is used to describe observations. In the case of these grades, a list of exam grades is data; that is, exam grades are a measure of the performance of students in exams.As you can imagine, there is a lot of data you can gather to describe a person. For a student, that can include their age, height, courses registered, exam grades, and more. One or more of these measurements used to describe a specific unit of observation is called a data point, and each measurement in a data point is called a variable (also referred to as a feature). When you have several data points together, you have a dataset. For example, you may have Student A, who is a 19-year-old male, and Student B, who is a 22-year-old female...

Understanding relational databases and SQL

Now that you have learned how to describe a real-world entity/relationship using a document comprised of key-value pairs, it is time to consider the downside of it (i.e., the inherited complexity of this modeling approach).First of all, key-value pairs within a document do not require any order. For example, the First Name key-value pair can show up either before or after the Last Name key-value pair. Furthermore, documents describing the same type of observation units (such as customers) can have different keys in different documents. A customer may have key-value pairs such as pets or children, or both, but also may not have either. These two characteristics, the lack of order and the flexibility of definition, reflect the real-world scenario and provide flexibility to the usage. The downside of them is that the applications utilizing these databases must carry the burden of checking key existence and searching for key-value pairs in an unsorted...

Setting up a PostgreSQL relational database

Relational DBMS (RDBMS) is the dominant DBMS in the current market. There are many different types of RDBMS. They can be loosely categorized into two groups, commercial and open source. Different RDBMSs differ slightly in the way they operate data and even in some minor parts of the SQL syntax. There is an American National Standards Institute (ANSI) standard for SQL, which is largely followed by all RDBMSs. But each RDBMS may also have its own interpretations and extensions of the standard.In this book, you will use one of the most popular open source RDBMSs, PostgreSQL. In this section, you will go through a series of exercises, install a copy of PostgreSQL on your local machine, launch its user interface, and import a sample database into your local PostgreSQL DBMS server. This PostgreSQL database will be used for the activities described in the rest of this book. After the installation, the PostgreSQL DBMS server will be running on the backend...

Activity 1

You have set up your working environment. You have got PostgreSQL up and running. You have even installed the sqlda database. Now it is time to take a look at the data you have in hand and identify its characteristics.Please create SQL statements to fetch the first 20 rows of each table in your sqlda database. Please answer the following two questions: Which table is empty? Which table has fewer than 20 records?

Summary

The chapter provided an introduction to data management systems, focusing on SQL and its applications in data analytics. It covered topics such as data modeling, relational databases, and the installation and use of PostgreSQL. These topics lay the foundation for the remaining chapters of this book and ensure you have an established environment for all the exercises and activities in the coming chapters.In the rest of this book, you will learn about SQL statements for different tasks involved in managing and analyzing data. The starting point is table creation, which is covered in the next chapter.