Test Assistent
Given Schema Structure of Tables for any database
Students must create tables based on the following structure.
1. Departments Table
Stores department details.
Attributes:
· dept_id — Integer — Primary Key
· dept_name — Varchar(100) — Not Null
2. Students Table
Stores student details.
Attributes:
· student_id — Integer — Primary Key
· name — Varchar(50) — Not Null
· age — Integer
· gender — Char(1)
· city — Varchar(50)
· dept_id — Integer — Foreign Key referencing Departments(dept_id)
Relationship:
· Many students belong to one department.
3. Instructors Table
Stores instructor details.
Attributes:
· instructor_id — Integer — Primary Key
· name — Varchar(60) — Not Null
· dept_id — Integer — Foreign Key referencing Departments(dept_id)
Relationship:
· One department can have many instructors.
4. Courses Table
Stores course details.
Attributes:
· course_id — Integer — Primary Key
· course_name — Varchar(100) — Not Null
· credits — Integer
· dept_id — Integer — Foreign Key referencing Departments(dept_id)
Relationship:
· One department offers many courses.
5. Course_Instructor Table
Stores mapping between courses and instructors (Many-to-Many relationship).
Attributes:
· course_id — Integer — Foreign Key referencing Courses(course_id)
· instructor_id — Integer — Foreign Key referencing Instructors(instructor_id)
Primary Key:
· Composite Primary Key (course_id, instructor_id)
6. Enrollments Table
Stores student enrollment details.
Attributes:
· enroll_id — Integer — Primary Key
· student_id — Integer — Foreign Key referencing Students(student_id)
· course_id — Integer — Foreign Key referencing Courses(course_id)
· semester — Varchar(10)
· score — Integer (Can be NULL before grading)
· grade — Varchar(2) (Can be NULL before grading)
Relationships:
· One student can enroll in many courses.
· One course can have many students.
Minimum Data Requirement
Students must insert:
· At least 3 departments
· At least 15 students
· At least 10 courses
· At least 20 enrollment records
· Some NULL values in score and grade
Project Tasks (Implement Queries For All)
Section A – Basic Retrieval
1. Select all columns from Students.
2. Select only name and city.
3. Display distinct cities of students.
4. Select students where age > 22.
5. Select students from Delhi AND age = 24.
6. Select students from Mumbai OR Chennai.
7. Select students NOT from Delhi.
8. Order students by age descending and name ascending.
Section B – Data Manipulation
9. Insert a new student record.
10. Update a student's city.
11. Delete one enrollment record.
12. Show enrollments where score IS NULL.
13. Update NULL scores to a numeric value.
Section C – Filtering & Conditions
14. Select students where dept_id IN (1,3).
15. Select enrollments with score BETWEEN 70 AND 90.
16. Select students whose name starts with 'A'.
17. Select students whose name contains 'ar'.
Section D – Aggregate Functions
18. Find MIN and MAX score.
19. Calculate AVG score.
20. Calculate SUM of scores for a specific course.
21. Count number of students per department.
22. Show courses having more than 2 students (GROUP BY + HAVING).
Section E – SELECT TOP / LIMIT
23.
Retrieve
top 3 students based on highest score.
(Provide syntax according to the RDBMS used.)
Section F – Joins
24. INNER JOIN students and departments.
25. JOIN enrollments with students and courses.
26. LEFT JOIN example between enrollments and students.
27. RIGHT JOIN example.
28. FULL OUTER JOIN example (or alternative method if DB does not support it).
Section G – Advanced Queries
29. List students who scored above overall average (Subquery).
30. Show top 5 students based on average score (Aggregation + ORDER BY + LIMIT/TOP).
31. Count number of NULL scores and non-NULL scores separately.
32. Use column aliases in a JOIN query.