Explore Patient Data with SQL in PostgreSQL

Table of Contents

1. Challenge

   Introduction and Setup

   Welcome to the hospital's data analytics team! Your first assignment is to get comfortable with the patient database. In this lab, you'll use SQL (Structured Query Language), the standard language for interacting with relational databases, to explore patient and encounter data.

   The database is hosted on PostgreSQL, a powerful open-source database system. You'll learn to retrieve, filter, and sort data to answer real-world questions.

   We have already connected you to the database and set up two tables for you:

   • patients: Contains demographic information about each patient (name, city, date of birth, etc.).
   • encounters: Contains records of every hospital visit or interaction (date, type, etc.).

   Let's get started!

2. Challenge

   Writing Your First Queries

   The foundation of SQL is the SELECT statement. It's used to retrieve data from a database. You can select all columns using * or specify individual column names.

   To manage the amount of data you get back, you can use the LIMIT clause, which restricts the number of rows returned. This is very useful for taking a quick peek at a table's structure and data without retrieving thousands or millions of rows.

   In this step, you'll write your first queries to get a feel for the patients table.

3. Challenge

   Filtering and Sorting Data

   Simply selecting data is useful, but the real power of SQL comes from filtering and sorting it. The WHERE clause allows you to specify conditions to filter rows, so you only get the data that matches your criteria. You can combine multiple conditions using operators like AND.

   The ORDER BY clause lets you sort the results based on one or more columns, in either ascending (ASC) or descending (DESC) order. This is essential for finding things like the 'newest', 'oldest', 'highest', or 'lowest' values.

   Let's use these new clauses to answer some more specific questions.

4. Challenge

   Analyzing Hospital Encounters

   Now that you're comfortable with the patients table, let's turn our attention to the encounters table. This table logs every interaction a patient has with the hospital, from routine check-ups to emergencies. Analyzing this data is key to understanding hospital operations.

   We'll apply the same skills—selecting, filtering, and sorting—to answer questions about patient visits.

5. Challenge

   Answering Complex Questions

   It's time to put all your new skills together. Most real-world data requests aren't simple; they require you to combine multiple techniques to get the right answer. In this final step, you'll tackle two requests that require you to use filtering on multiple criteria (AND), along with sorting and selecting from the correct tables.

   Answering these questions will prove you have a solid grasp of fundamental SQL operations.