This project involves creating and querying a relational database for an academic setting. The database includes tables for students, groups, teachers, subjects, and grades. It is populated with random data using the Faker package. The project also contains a series of SQL query files to extract specific information from the database.
- Students Table: Information about students.
- Groups Table: Details of student groups.
- Teachers Table: Records of teachers.
- Subjects Table: List of subjects with corresponding teachers.
- Grades Table: Students' grades for each subject with timestamps.
- Approximately 30-50 students, 3 groups, 5-8 subjects, 3-5 teachers.
- Up to 20 grades for each student across all subjects.
- Data populated using the Faker package.
query_1.sql: Top 5 students with the highest average score across all subjects.query_2.sql: Student with the highest average in a specific subject.query_3.sql: Average scores in groups for a specific subject.query_4.sql: Overall average score across all students and subjects.query_5.sql: Courses taught by a specific teacher.query_6.sql: List of students in a specific group.query_7.sql: Grades of students in a specific group for a particular subject.query_8.sql: Average grades given by a specific teacher.query_9.sql: List of courses attended by a student.query_10.sql: Courses taught to a specific student by a specific teacher.
advanced_query_1.sql: Average grade a particular teacher gives to a specific student.advanced_query_2.sql: Grades of students in a specific group for a particular subject during the last class.
- Set up the database using the provided schema.
- Populate the database with data using Faker.
- Execute SQL queries using a database client or through a programming language interface like Python's
cursor.execute(sql).
Clone the repository. To set up the database, you'll need a SQL database server like MySQL or PostgreSQL. The Faker package is required for data generation.
- Ensure the database schema is correctly set up.
- Validate the data generation scripts for proper and randomized data entry.
- Test each SQL query for accuracy and efficiency.