by 
We’ve gotten the message that some of our DBAs are loosing grasp of the internal structure of our FakeCompany inc. Let’s try to help them out!
To aid in this, we’re initiating a challenge! Your task is to create a reference table that encapsulates the essential aspects of our internal database structure.
What You Need to Do
Examine the Provided Table Image: A reference image (see below) outlines the basic format and information your table should include.
Create a Similar Table: Develop a table that mirrors the structure shown in the image. Make sure your table is tailored to reflect our specific database architecture at FakeCompany Inc.
The starting code for this challenge:
Startup code
CREATE OR REPLACE table departments (department_name varchar, department_ID int, head_department_ID int);
INSERT INTO departments (department_name, department_ID, head_department_ID) VALUES
('Research & Development', 1, NULL), -- The Research & Development department is the top level.
('Product Development', 11, 1),
('Software Design', 111, 11),
('Product Testing', 112, 11),
('Human Resources', 2, 1),
('Recruitment', 21, 2),
('Employee Relations', 22, 2);
Remember if you want to participate:
- 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)
- Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
- Post the URL in the comments of the challenge.
22 responses to “Week 73 – Intermediate”
-
Love this! So many use cases in Snowflake: role hierarchy, grants, access history…
- Solution URL – https://github.com/mferle/FrostyFriday/tree/main/Week%2073%20%E2%80%93%20Intermediate
-
Thanks for the challenge… flashbacks from some time ago working with oracle!
- Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_73.sql
-
I’ve been waiting for a chance to use this functionality for years. Every time I think I’ve found a use case, we either reshape the data or opt for an alternative pattern (deliberately being vague to avoid spoilers in my comment).
Thanks for another fun challenge!
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
-
First time using this subclause, cool! It seems to be supported by Oracle databases as well
- Solution URL – https://github.com/darko-nimbus/FROSTYFRIDAYCHALLENGES/blob/98e9f5f8130d155a8bb3e73674c3bfcea26edef7/73%20-%20Intermediate%20-%20Interconnected%20Data/73%20-%20Intermediate%20-%20Interconnected%20Data.sql
-
Really a good challenge to practice with recursive CTEs!
- Solution URL – https://github.com/marco-scatassi-nimbus/Frosty-Friday/tree/main/week73
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Leave a Reply
You must be logged in to post a comment.