Week 110 – Intermediate

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.

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);

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!

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.