SQL Fundamentals – Practical Workbook Project

Overview

This repository contains the completed SQL Data Technician Workbook – Week 3, demonstrating core SQL querying skills, database concepts, relational modelling, and practical problem-solving using real-world scenarios. The workbook includes research tasks, SQL solutions for the Northwind database, SQL join operations, and advanced queries using the MySQL world database.

This project showcases foundational SQL abilities including querying, filtering, sorting, joining tables, grouping, aggregating, and designing database schemas.

Skills Demonstrated:

Database Concepts

• Understanding primary keys, secondary keys, and foreign keys
• One-to-one, one-to-many and many-to-many relationships
• Differences between relational and non-relational databases
• Choosing appropriate database models for different types of data

SQL Querying (Northwind Database)

Practical scenarios completed include:

• Selecting full tables and specific fields
• Filtering by numeric ranges, categories, and text patterns
• Removing duplicates using DISTINCT
• Ordering and sorting data
• Using logical operators (AND, OR)
• Retrieving category-specific products
• Extracting customer, product, and order information
• Using BETWEEN, ORDER BY, and comparison operators

SQL Joins

Clear understanding and examples of:

• Inner join
• Left join
• Right join
• Full outer join
• Cross join
• Self-join

Applied to real Northwind business tasks such as:

• Linking suppliers to products
• Joining customers, orders, and employees
• Combining category, supplier, and product data
• Summarising product counts per category
• Analysing sales volume and pricing

SQL Querying (world Database)

A series of analytical tasks performed on global city and country data, including:

• Population analysis
• Listing cities by prefix
• Identifying highest or lowest population values
• Selecting capital cities
• Sorting with LIMIT and OFFSET
• Counting entries and aggregating demographic information
• Calculating average population by country
• Identifying countries with low population density
• Extracting cities for thematic or geographic filters

These tasks demonstrate competency with:

• Aggregation functions (COUNT, SUM, AVG)
• Pattern matching (LIKE)
• Filtering by ranges
• Sorting and limiting output
• Problem-solving using SQL for practical scenarios

Database Design & Written Case Study

The workbook includes a 500-word essay covering:

• Understanding business requirements for a retail database
• Designing database schema with tables for products, customers, inventory, suppliers, and sales
• Creating relationships using primary and foreign keys
• Example SQL CREATE TABLE and INSERT statements
• Data accuracy, constraints, and referential integrity
• Backup strategy and data security considerations

How to Use This Repository

This project demonstrates the full set of SQL skills covered during Week 3 of Data Technician training. You can explore:

• The workbook with all written answers and SQL queries
• Realistic SQL scenarios using Northwind and world databases
• Research tasks explaining key database theory
• Join explanations and example use cases
• A full database-design case study

This repository serves as a practical demonstration of SQL competency for portfolio or assessment purposes.

References

• JustIT Training Resources
• MySQL documentation
• Northwind sample database
• MySQL World database
• Additional reading from course revision guides