Practical Sql, 2nd Edition : A Beginner’s Guide To Storytelling With Data by Anthony Debarros

Analyze data like a professional, even if you are a novice. Practical SQL is a user-friendly and fast-paced manual to SQL (Structured Query Language), the universally recognized programming language for organizing, defining, and exploring data in relational databases. Anthony DeBarros, a journalist and data analyst, concentrates on utilizing SQL to uncover the narrative hidden within your data.

The book employs PostgreSQL, an open-source database, and its companion pgAdmin interface, imparting concepts that are applicable to a variety of database management systems such as MySQL, Oracle, and SQLite. The journey begins with an introduction to databases and SQL, followed by hands-on exercises using real-world datasets, including New York City taxi rides, US Census demographics, and earthquakes from the US Geological Survey.

Each chapter incorporates examples and exercises that equip even those with no prior programming experience with the necessary tools to construct powerful databases and access information efficiently. Key skills covered in the book include:

• Create databases and related tables using your own data
• Aggregate, sort, and filter data to detect patterns
• Utilize functions for basic calculations and advanced statistical operations
• Identify and rectify data errors
• Analyze spatial data with a geographic information system (PostGIS)
• Generate advanced queries and automate tasks

This updated second edition has been extensively revised to encompass the latest SQL features, including advanced query techniques for data manipulation. Additionally, it has two supplementary chapters: one providing detailed instructions for system setup and another devoted to using PostgreSQL with the widely used JSON data interchange format. Practical SQL aims to make learning SQL a straightforward and engaging experience, offering clear examples and an easily understandable approach to equip readers with the skills to construct and manage their own databases. *Please note that Practical SQL does not cover T-SQL, a variant of SQL employed by Microsoft SQL Server.

Running SQL Queries in pgAdmin: A Beginner’s Guide

In this article, we will explore how to run queries in pgAdmin, which is a graphical user interface used for connecting, working with, and running queries against PostgreSQL databases. While there are many GUI tools available for PostgreSQL, pgAdmin is popular because it is free, open source, and comes bundled with PostgreSQL installers.

The Components of pgAdmin

When we open pgAdmin, we will see several key component areas. On the left-hand side, we have the browser or object browser. This displays the server we are connected to and the objects within that server, such as databases. In the middle section, we have the working area, where we can edit and write queries and view the results. This area includes a query editor, scratch pad (which can be used for notes), and a data output area.

Running Queries

To run a query in pgAdmin, we first select the database we want to work with in the browser. Then, we go to the Tools menu, select Query Tool, and a query editor will open. In the query editor, we can either type our SQL query or open a saved file of queries.

To run a query, we can either highlight the query and click the execute icon or press F5 on the keyboard. The results will be displayed in the data output area, along with some information in the status bar, such as the number of rows returned and the time taken to complete the query.

We can continue to run additional queries by highlighting them and clicking run. It is also possible to run the entire query editor at once by clicking anywhere and executing the queries, but it is recommended to run queries individually to avoid unintended consequences.

Working with Spatial Data

If we are working with geospatial data in the PostGIS database, pgAdmin offers a rudimentary mapping tool. When running a query that results in geospatial data, the data output area will display a column header with a map icon. Clicking on the map icon will open a map using the OpenStreetMap layer, showing the spatial results of the query. While not as comprehensive as dedicated mapping programs, this feature allows for basic exploration and visualization of the data.

Conclusion

That concludes our introduction to running queries in pgAdmin. If you are interested in learning more about SQL and data storytelling, check out my book, “Practical SQL: A Beginner’s Guide to Storytelling with Data.”

Take care and happy querying!