Week 110 - Administration

Imagine you’re working with a database that contains two primary tables: employees and departments. Each table has columns with associated comments that provide additional information about the data. Your goal is to manage and visualize these comments efficiently and automatically.

Your Employees' table

CREATE OR REPLACE TABLE employees (
    EMPLOYEE_ID INTEGER COMMENT 'The unique ID for each employee',
    NAME STRING COMMENT 'Full name of the employee',
    DEPARTMENT_ID INTEGER COMMENT 'ID of the department to which the employee belongs',
    SALARY FLOAT COMMENT 'Salary of the employee'
);

INSERT INTO employees (EMPLOYEE_ID, NAME, DEPARTMENT_ID, SALARY) VALUES
    (1, 'Alice Smith', 101, 75000),
    (2, 'Bob Johnson', 102, 80000),
    (3, 'Carol Williams', 101, 72000),
    (4, 'David Brown', 103, 68000),
    (5, 'Emma Davis', 102, 73000);

Your Departments' table

CREATE OR REPLACE TABLE departments (
    DEPARTMENT_ID INTEGER COMMENT 'Unique ID for the department', 
    DEPARTMENT_NAME STRING COMMENT 'Name of the department',
    LOCATION STRING COMMENT 'Location where the department is based' 
);

INSERT INTO departments (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION) VALUES
    (101, 'Engineering', 'San Francisco'),
    (102, 'Marketing', 'New York'),
    (103, 'HR', 'Chicago');

In this challenge, you'll need to address the problem of keeping column comments updated and integrating them into a final view. This involves creating a system to store and continuously update column comments in an automated manner. Additionally, you'll need to ensure that these comments are easily accessible and visible when combining data from different tables into a presentation view.

The end result should be a view that not only combines the data from the tables but also includes the updated comments, providing a comprehensive and informative overview.

With this challenge, you’ll add a powerful tool to your data management arsenal, enhancing both clarity and usability for anyone who interacts with the database. May the INFORMATION_SCHEMA be with you!