Raphael Russo

A normalized relational database for a sample university in BCNF. A Python script generates test data, and R and SQL are used to populate a local SQLite database. Has a variety of sample SQL queries from simple to advanced with CTEs and window functions and uses views, triggers, and indexes for optimization.

Example query to get the students with the top ten GPA's per major if they have at least 3 grades reported:

                
                WITH MinClasses AS (
                    SELECT student_id
                    FROM Enrollment
                    WHERE grade IS NOT NULL
                    GROUP BY student_id
                    HAVING COUNT(*) >= 3
                )
                SELECT 
                    student_id AS 'StuID',
                    email AS 'StuEmail',
                    gpa AS 'StuGPA',
                    major_name AS 'MajorName'
                FROM (
                    SELECT
                        m.major_name,
                        s.student_id,
                        s.email,
                        s.gpa,
                        DENSE_RANK() OVER (PARTITION BY sm.major_id ORDER BY s.gpa DESC) AS stuRank
                    FROM
                        Students s
                    JOIN StudentMajors sm ON s.student_id = sm.student_id
                    JOIN Majors m ON sm.major_id = m.major_id
                    WHERE
                        s.GPA IS NOT NULL
                        AND s.student_id IN (SELECT student_id FROM MinClasses)
                ) TopStudents
                WHERE stuRank <= 10;
                
                

Made With:

• SQL
• SQLite
• R
• Python