Educational Institution Database

Objective

Design a database for an educational institution to demonstrate SQL query exectutions. The database will be hosted on a Postgres server and commands given via an interactive shell. PostgreSQL (opens in a new tab) is an open source relational database and used by top companies such a Apple (opens in a new tab), Instagram (opens in a new tab), and Spotify. (opens in a new tab) SQL (Structured Query Language) is a flexible and powerful language for analysis and to retreive meaningful insight from data.

Setup the Educational Institution Database demo

This GitHub repository (opens in a new tab) contains SQL files for setting up an educational institution database, including tables for cohorts and students. Follow the steps below to get started.

Prerequisites

PostgreSQL is required to setup and run the query files. Download and install Postgres before proceeding:

• Download PostgreSQL on your operating system. (opens in a new tab)
• Setup and start the Postgres server. (opens in a new tab)

Database Setup

Open Interactive Shell

Open a PostgreSQL interactive shell from the project directory. The shell allows for commands and interaction with the database:

psql

Create Database

From the interactive shell run the following command to create a new database:

CREATE DATABASE edudb;

Connect to Database

Connect to the newly created database from the shell:

\c edudb;

Run Migrations

Execute migration files to create tables in the database:

\i migrations/students_cohorts.sql
\i migrations/assignment_submissions.sql
\i migrations/teachers_assistance_requests.sql

Seed the Database

Populate the tables with sample data using seed files:

\i seeds/students.sql
\i seeds/teachers_seeds.sql
\i seeds/cohorts.sql
\i seeds/assignments_seeds.sql
\i seeds/assignment_submissions_seeds.sql
\i seeds/assistance_requests_seeds.sql

Run SQL Queries

Explore and execute SQL query files to perform analysis on the educational institution database.

SQL query file directories:

0_selects
1_queries
2_queries_joins
3_queries_group-by
4_queries

Example SQL Queries

The query files demonstrate a range of statistical analysis using the sample data. Here are some of the queries available to run in the in the fileset:

• SELECT statement with WHERE clause:

  \i 0_selects/1_students_without_github.sql
   
  -- executes:
   
  SELECT id, name, email, cohort_id
  FROM students
  WHERE github = '' OR github IS NULL
  ORDER BY cohort_id;

• JOIN query with AGGREGATE function:

  \i 2_queries_joins/1_total_student_time_spent_on_assignments.sql
   
  --- executes:
   
  SELECT SUM(assignment_submissions.duration) AS total_duration
  FROM assignment_submissions
  JOIN students ON students.id = assignment_submissions.student_id
  WHERE students.name = 'Ibrahim Schimmel';

• MULTIPLE-JOIN query with AGGREGATE function:

  \i 4_queries/13_teachers_total_assistances_per_cohort.sql
   
  -- executes:
   
  SELECT DISTINCT(teachers.name) AS teacher,
         cohorts.name AS cohort,
         COUNT(assistance_requests) AS total_assistances
  FROM students
  JOIN cohorts ON cohorts.id = cohort_id
  JOIN assistance_requests ON students.id = assistance_requests.student_id
  JOIN teachers ON teachers.id = assistance_requests.teacher_id
  WHERE cohorts.name = 'JUL02'
  GROUP BY teacher, cohort
  ORDER BY teacher;

  Feel free to explore further and modify these queries based on your specific needs!