Lesson 009: Advanced filtering using SQL WHERE

Introduction In the previous 2 posts I covered SQL select statements, and we looked at the Lesson_007 and Lesson_008 files in the learning-data-science GitHub repository. Lesson 007 covered the basics of SQL selects statements, and Lesson 008 covered LIMIT and OFFSET, column aliases, data type casting, creating columns on-the-fly, mathematical operations. In this post, LessonContinue reading “Lesson 009: Advanced filtering using SQL WHERE”

Lesson 008: SQL SELECT Part 2

Introduction In the previous post I covered part 1 of SQL select statements. We looked at the Lesson_007 files in the learning-data-science GitHub repository to learn the basics of SQL select statements. In this post, the second of 2 posts focused on SQL select statements, I cover LIMIT, OFFSET, mathematical operations, creating columns on theContinue reading “Lesson 008: SQL SELECT Part 2”

Lesson 007: SQL Select Part 1

Introduction In the previous post I covered SQL Statements. We looked at the Lesson_006 files in the learning-data-science GitHub repository to learn about SQL statements, run several SQL statements strung together in a SQL script, and wrote a basic select statement. In this post, Lesson 007, we’ll learn about SQL select statements in more detail,Continue reading “Lesson 007: SQL Select Part 1”

Lesson 006: SQL Statements

Introduction In the previous post I covered Entity Relationship Models (ERM); and we looked at the Lesson_005 files in the learning-data-science GitHub repository to see an example of how to turn an ERM into a SQL script to create the database structure expressed in the ERM. In this post, Lesson 006, we’ll learn about SQLContinue reading “Lesson 006: SQL Statements”

Lesson 005: Entity Relationship Models

Introduction In the previous post I covered inserting data into a PostgreSQL table; and we used the Lesson_004 files in the learning-data-science GitHub repository to create foreign keys in the public.sample table. In this post, Lesson 005, we’ll take a step back and prepare an Entity Relationship (ER) model for my_first_database. We’ll use the ERContinue reading “Lesson 005: Entity Relationship Models”

Lesson 004: Foreign Key Constraints

Introduction In the previous post I covered inserting data into a PostgreSQL table; and we used the Lesson_003 files in the learning-data-science GitHub repository to insert data into the dom_study table. In this post, Lesson 004, we’ll create 2 more tables, set up foreign key constraints, and insert data into the 2 tables. Since dataContinue reading “Lesson 004: Foreign Key Constraints”

Lesson 003: Inserting Data into a Table

Introduction In the previous post I introduced PostgreSQL sequences, tables, and columns; and we used the Lesson_002 files in the learning-data-science GitHub repository to create a table for storing metadata about scientific studies. In this post, Lesson 003, we’ll insert data into the dom_study table that we created in the previous post. Since data analysisContinue reading “Lesson 003: Inserting Data into a Table”

Lesson 002: Creating Tables in PostgreSQL

Introduction In the previous post I introduced PostgreSQL schemas, and we used the Lesson_001 files in the learning-data-science GitHub repository to create a schema for organizing domain tables. In this post, Lesson 002, we’ll create a sequence and a data table, and add column comments. Since data analysis and management are best learned by doing,Continue reading “Lesson 002: Creating Tables in PostgreSQL”

Lesson 001: Creating a Schema

Introduction In the previous post we installed pgAdmin and PostgreSQL, and created a database. In this post, Lesson 001 of Learning Data Science, we’ll discuss database schemas and create a new schema in my_first_database. Since data analysis and management are best learned by doing, the objective of this and future posts is to get readersContinue reading “Lesson 001: Creating a Schema”

Creating a PostgreSQL Database

Introduction In the previous post I introduced the learning-data-science GitHub repository, defined forking a repository, and provided instructions for forking the learning-data-science repository and cloning it to your computer. In this post, we’ll install pgAdmin and PostgreSQL, and create a database. Since data analysis and management are best learned by doing, the objective of thisContinue reading “Creating a PostgreSQL Database”