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.