Build a University Database from Scratch | SQL Hands-On Project with PostgreSQL | Part 2

Welcome to Part 2 of Week 7! Now that you know the fundamentals from Part 1, it's time to get your hands dirty. In this session, we design and build a complete multi-table University Database from scratch — and then write 15 real-world SQL queries to pull meaningful insights from it.

⚠️ Prerequisites: Watch Part 1 first and make sure PostgreSQL + pgAdmin are installed and running!

🏗️ What We Build:
• A full university database with 5 related tables: Departments, Teachers, Students, Courses, and Enrollments
• Proper relationships with primary keys, foreign keys, and constraints
• Sample data population across all tables

🔍 Query Challenges We Tackle:
• List all students with their department names (LEFT JOIN)
• Find all courses taught by each teacher
• Show enrollment details with student and course info
• Count students per department
• Calculate average GPA by major using GROUP BY + HAVING
• Find students enrolled in more than 2 courses
• Discover the most popular courses by enrollment count
• Identify students with A grades across all courses
• Find students NOT enrolled in any course (LEFT JOIN + NULL check)
• Calculate total credits earned per student
• And more advanced multi-table query patterns!

🗂️ Sections Covered:
• Project Overview & Schema Design (10 min)
• Step 1: Creating All 5 Tables with Relationships (15 min)
• Step 2: Inserting Sample Data (10 min)
• Step 3: Writing 15 Practice Queries (20 min)

This is the kind of real-world database design you'll use in your Java applications, and in Week 8 we'll connect all of this to Java using JDBC. Start practicing these queries!

📂 Resources & Links:
• Part 1 (Watch First): [link to Part 1]
• PostgreSQL Docs: https://www.postgresql.org/docs/
• DB Diagram (Schema Visualizer): https://dbdiagram.io/
• LeetCode SQL Practice: https://leetcode.com/problemset/database/
• HackerRank SQL: https://www.hackerrank.com/domains/sql
• Week 7 Teaching Guide: https://github.com/setyuth/Java-Programming-II/blob/main/Week%207%20Introduction%20to%20Databases%20and%20SQL/Documentation/Week%207_%20Introduction%20to%20Databases%20and%20SQL.md

🔜 Coming Up in Week 8:
• Connecting Java to PostgreSQL using JDBC
• PreparedStatement, ResultSet, and CRUD from Java code
• SQL Injection prevention
• Building a complete Java Data Access Layer

Subscribe : https://www.youtube.com/@KhmerSide?sub_confirmation=1

Видео Build a University Database from Scratch | SQL Hands-On Project with PostgreSQL | Part 2 канала KhmerSide