Introduction to Data Modeling in Snowflake
Nuno Rocha
Director of Engineering
ALTER TABLE classes
ADD FOREIGN KEY (school_id) REFERENCES schools(school_id);
CREATE OR REPLACE TABLE enrollments ( -- Create a new entity with a unique identifier enrollment_id NUMBER(10,0) PRIMARY KEY
-- Add the entity's attributes year NUMBER(4,0), semester VARCHAR(255)
-- Add relationships to other entities FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (class_id) REFERENCES classes(class_id)
);
SELECT students.name
FROM students;
SELECT students.name
FROM students
JOIN enrollments
ON students.student_id = enrollments.student_id;
SELECT students.name
FROM students
JOIN enrollments
ON students.student_id = enrollments.student_id
WHERE enrollments.year = '2023';
SELECT students.name,
classes.class_name
FROM students
JOIN enrollments
ON students.student_id = enrollments.student_id
JOIN classes
ON enrollments.class_id = classes.class_id
WHERE enrollments.year = '2023';
SELECT students.name,
classes.class_name
FROM students
JOIN enrollments
ON students.student_id = enrollments.student_id
JOIN classes
ON enrollments.class_id = classes.class_id
JOIN schools
ON classes.department_id = schools.school_id
WHERE enrollments.year = '2023'
AND schools.school_name = 'Science';
JOIN ON
: SQL clause to combine rows from tables, based ON
a related column.SELECT FROM
: SQL command to fetch columns from a table.JOIN ON
: SQL clause to combine rows from tables, based ON
a related column.WHERE
: SQL clause to filter records based on a set condition.AND
: Logical operator used with WHERE
clause to combine multiple conditions.-- Querying data from merged entities filtered by specific conditions
SELECT column_name
FROM table_name
JOIN other_table ON table_name.FK = other_table.PK
WHERE column_name condition value
AND column_name condition value;
Introduction to Data Modeling in Snowflake