$**$A SQL TABLE IS PROVIDED. PLEASE ANSWER THE FOLLOWING QUESTIONS USING THE SQL TABLE.$**$

$-$List all students with a major in "Computer Science".

$-$Show the name and department of each course.

$-$Display the name, major, and year of each student, ordered by year in descending order.

$-$Select students who are enrolled in a specific course.

Insert:

$-$Add a new student to the database with their name, major, and year.

$-$Create a new course record with its name, department, and instructor.

Update:

$-$Increase the grade of all students in a specific course by one letter grade.

$-$Update the department of a course.

Delete:

$-$Remove students who haven't enrolled in any courses.

$-$Delete courses that no students are currently enrolled in$.$

Distinct & Count:

$-$Show the distinct list of departments offering courses.

$1.$ Count the total number of students enrolled in each course.

Order By & Null Values:

$-$Display courses alphabetically, ordered by name.

$-$List students with missing grade information $($null values$).$

Aliases & Concatenation:

$2.$ Use an alias to rename the "grade" column to "letter$\_$grade".

$3.$ Create a new column named "full$\_$name" by concatenating the "first$\_$name" and "last$\_$name" columns from the Students table $($assuming such columns exist$).$

Literal Characters & Strings:

$-$Find courses whose name starts with the letter $"$M$".$

$-$Search for students whose name contains the word "Smith".

WHERE:

$-$List students who are juniors $($year equal to $3).$

$-$Show courses taught by a specific instructor.

$-$Find students enrolled in both a specific course and another specific course.

Between & In:

$-$Display courses offered by the "Math" department.

$4.$ List students with a grade between $"$B$"$ and $"$A$".$

Not In & Like:

$-$Show courses that are not offered by the "English" department.

$5.$ Find students whose name starts with "John" but doesn't have $"$Jr$."$ in it$.$

Wildcard Characters:

$6.$ List courses whose name starts with "intro" and ends with "tion".

$-$Find students whose name contains the letter $"$a$"$ anywhere within it

Joins:

$7.$ Combine student names with their enrolled course names using a join between Students and Enrollments tables.

$8.$ Show the name, major, and grade of each student for a specific course using joins.

Group By & Having:

$9.$ Calculate the average grade for each course using Group By and Avg.

$10.$ Find courses where the average grade is above a specific threshold using Group By$,$ Having, and Avg.

Outer Joins:

$11.$ List all students, even if they haven't enrolled in any courses, using a left join between Students and Enrollments tables.

$12.$ Show all courses, along with the number of enrolled students $($even if there are none$),$ using a right join between Courses and Enrollments tables.

Advanced Joins:

Self Join:

$13.$ Find students who are enrolled in the same course with a friend $($like being in the same course$)($use a self$-$join on the Enrollments table with a condition on student$\_$id$).$

$14.$ Identify courses that have prerequisites, using a self$-$join on the Courses table with a condition on a "prerequisite$\_$course$\_$id$"$ column $($if added$).$

Full Outer Join:

$14.$ List all students and their enrolled courses, even if a student hasn't enrolled in any or a course doesn't have any students enrolled $($use a full outer join$).$

$15.$ Show all courses with their instructors and enrolled students, even if a course has no instructor or students $($use a full outer join$).$

Nested Joins:

$16.$ Find students enrolled in a course taught by an instructor from a specific department $($use two joins: one between Courses and Instructors, and another between Enrollments and the joined table$).$

$17.$ List courses that share at least one student with another specific course $($use joins to combine Enrollments table with itself$).$