Table of Contents

• 📚Table of Contents

PostgreSQL Install

👋 Welcome to the Advanced Section

SQL Topics to Cover

How will we be running queries?

• Running a PostgreSQL database locally on your computer
• Using VS Code to query the database

NOTE: sqlliteviz may still work for the queries here, but it’s not recommended

🤔Why are we using PostgreSQL?

PostgreSQL (pronounced "POST-gres-cue-ell" or "POST-gres”, we’ll use the latter) is the most popular database according to the Stack Overflow Developer Survey from 2023

It’s also the most admired (the proportion of users that have used the same technology in the past year and want to continue using it) and most desired (the proportion of respondents who want to use a technology)… fancy talk for saying those that want to use it want to continue to use it and those that don’t use it also do

Stack Overflow Developer Survey 2023

⬇️ Download PostgreSQL

Why Installing?

We will be running the database locally on your computer; This is a great way to getting familiar with using databases and if you make any mistakes you can easily start over

How to Download?

PostgreSQL: Downloads

1. Download Installer - https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
   1. Choose version 16.1 (the one we use in this course)
      1. NOTE: We can’t guarantee everything we do here will work if you don’t download that version
   2. Download the correct installer for your machine (Mac, Windows, Linux)
2. Go to your downloads file and open the downloaded installer
3. Go through the setup steps (keep default settings)
   1. Create a password (write it down somewhere)
   2. Select port number for the server: 5432 (usedefault)
   3. Select Locale: [Default Locale]
4. Finish Installing
5. Continue Installation (leave as default)
   1. Host: localhost
   2. User Name: postgres
   3. Password: _________ (the one you decided before)
   4. Port 5432
6. Finish Installation

How To Run a Database in PostgreSQL

1. Use pgAdmin app to start up PostgreSQL

   1. The database needs to be running to query it
   2. Anytime you restart your computer, you need to start back up PostgreSQL

2. Inside of pgAdmin select the database to start it up

   1. Click your database located under PostgreSQL 16 / Databases

      

   b. It will turn gold once it is running

   

Code Editor Install

🎉Intro

1. What’s a code editor (or IDE)?

   1. 📝 Code Editor - A digital notepad for writing and organizing programming code. Easy editing, checking, and running code snippets
   2. 📑 Integrated Development Environment (IDE) - A supercharged code editor with tools for writing, testing, and fixing code all in one place. Like a code editor with a toolbelt

   VS Code is a code editor packed with features like an IDE: debugging, syntax highlighting, and extensions for various programming tasks

2. Why are we using VS Code?

   

   According to the Stack Overflow Developer Survey from 2023; VS Code is by far the most popular code editor / integrated development environment

3. What are other options for running SQL queries (not used in this course)?

• Free Option

DBeaver

• Paid Option

DataGrip: The Cross-Platform IDE for Databases & SQL by JetBrains

Note: These options are database management tools specifically designed for … well databases. As a data analyst, I use more than just SQL (i.e., Python), and I like to stay in the same application, so I recommend learning VS Code over these options. (Although these options are WAY more capable for handling SQL queries)

📥 Download VS Code

1. Download VS Code

🔍Overview of VS Code

1. Activity Bar
   • Provides icons for quick access to different views (Explorer, Search, Git, Debug, Extensions)
2. Side Bar
   • Displays additional information based on the selected view in the Activity Bar, such as file explorer for navigating files and folders, search results, source control operations, etc.
3. Code Editor
   • The central area where you write and edit your SQL code, featuring syntax highlighting, IntelliSense, and tabbed interface for open files

📁Setup Project Folder

We’re going to create a folder to have all of our SQL files.

1. Right click on the Explorer sidebar
2. Select “Open Folder”
3. Create a new folder and name the folder: SQL_Data_Analytics_Course

📩 Install Extension

1. Install a PostgreSQL Extensions (Two Required)

Data Types

Manipulate Tables

Create Database (Advanced)