Embedded Files
Day 1: Database Fundamentals & Relationships
Key Concepts:
Primary Key: A unique identifier column for each record or row in a database table.
Secondary Key: Used for efficient searching and indexing; allows duplicates and NULL values.
Primary vs. Foreign Keys: Primary keys uniquely identify records within a table, while foreign keys link to primary keys in other tables to connect data without duplicating it.
Database Relationships:
One-to-One (1:1): A person linked to their single passport record.
One-to-Many (1-N): A single customer who has placed multiple store orders.
Many-to-Many (N-M): Multiple students enrolled across multiple different courses.
Relational vs. Non-Relational (NoSQL):
Relational: Uses structured tables with fixed schemas and SQL constraints.
Non-Relational: Schema-less structures (documents, key-value pairs) ideal for rapidly changing, unstructured data like social media posts, media files, and comments.
Day 3: SQL Join Operations
Inner Join: Combines rows from two or more tables only where matching values exist in both tables.
Left Join: Keeps all records from the left table and appends matching data from the right table (unmatched fields return NULL).
Right Join: Retains all records from the right table and pulls in matching rows from the left table.
Full Join: Combines all records from both tables, returning NULL for missing matches on either side.
Self-Join: Joins a table to itself to compare values across columns within the exact same dataset.
Cross Join: Generates a Cartesian product that pairs every single row of the first table with every row of the second.
Day 4 (Part 1): Retail Database Proposal (Holland & Barrett)
Business Requirements: Centralizes inventory (SKUs, categories, stock, suppliers), sales transactions, supplier contact details, and customer loyalty points to streamline store operations and cashier checkout.
Database Schema:
Inventory (PK: SKU_ID, FK: Supplier_ID)
Supplier (PK: Supplier_ID)
Customer (PK: Customer_ID)
Sales (PK: Transactions_ID, FKs: Customer_ID, SKU_ID)
Core SQL Commands: Uses CREATE DATABASE, CREATE TABLE, ALTER TABLE, CREATE INDEX, INSERT INTO, and SELECT to structure, link, and query data.
Maintenance & Security: Protected via referential integrity constraints, role-based user access, encryption, and regular automated backups.
Suggested Image Prompt (Part 2 - SQL Practical):
"Isometric 3D illustration of a glowing code editor screen showing PostgreSQL queries and data filtering pipelines, modern blue and dark slate UI aesthetic, ultra-clean matte style."
Day 4 (Part 2): PostgreSQL Query Operations
Filtering & Selection: Executed SELECT DISTINCT, WHERE, BETWEEN, and ORDER BY statements to filter population statistics, isolate language shares (e.g., Angola, Albania), and perform pattern searches (LIKE).
Aggregations: Applied COUNT(), MAX(), SUM(), GROUP BY, and HAVING functions to summarize language percentages and identify countries with multiple official languages.
Subqueries: Built nested SQL queries to isolate countries exceeding global population averages and extract complex multi-language data criteria.
Page updated
Google Sites
Report abuse