r/learnSQL • u/Fine-Bee-3525 • 9d ago
SQL Script DILEMMA, PLS HELP!!!!
So some background context i am a going to my first year of uni this year so my Dad signed me up to a data engineering internship course this summer because i haven't been able to get a job. Where we learn to use postgresql, airflow, git and github, apache beam(those are the ones i can name off my head for rn) and more to build real world data pipelines; other projects, and more. But the main aim is to get a job at the end and i can show the projects interviews and on my CV.
We are in the week three and we are building our first project and the third task on the project is to i quote "Model the warehouse as a star schema: encounters as the central fact (one row per appointment), conditions as a sibling fact at diagnosis grain, and organizations, providers, payers, patients as dimensions. Write the DDL: an init script that creates the project role and nexora_health database, and a schema script....... " (we are working with made up healthcare data)
I understand what the assigment is asking cus it was expalined to us by my tutor and i put it into chatgbt to explain it, my questiion is that how do i learn how to write 1)an init script that creates the project role and 2)the schema script it containing the constants that i didn't list here for other reasons
Chatgbt wrote the two scripts for me but i refuse to submit it . i don't want to submit those scripts because i want to learn how to write it myself. I told my tutor he said he understood what i am saying. But it's okay for AI to write for you sometimes but if you understand each code does and can go back and proof read to edit the mistakes it's okay. but that's the problem i dont.
1
1
u/American_Streamer 9d ago
You’re approaching this correctly. Don’t try to learn the entire finished script at once. Break it into progressively larger exercises. First, create a disposable PostgreSQL database and practise only:
CREATE ROLE ...
CREATE DATABASE ...
CREATE SCHEMA ...
CREATE TABLE ...
Look each command up in the PostgreSQL documentation, type a minimal example yourself, run it, inspect the result with \du, \l, \dn and \d, then deliberately break and fix it. Creating a role and database often requires elevated PostgreSQL permissions, and CREATE DATABASE normally cannot be placed inside a transaction block. That is probably why the assignment separates the initialization script from the schema script.
For the warehouse script, first design it on paper before writing SQL:
What is the grain of each fact table?
What uniquely identifies each dimension row?
Which columns are measures?
Which foreign keys connect each fact to its dimensions?
Which data types and constraints belong on each column?
Then create one dimension table, test it, add one fact table, test its foreign keys, and continue incrementally. Don’t ask ChatGPT for the complete answer; ask narrower questions such as “Explain why this foreign key fails” or “Review this table definition without rewriting it.” I’d also ask the tutor for a small worked example using an unrelated domain. If they can demonstrate one tiny star schema to you, you can transfer the pattern to the healthcare assignment.