I need sql code. I send major, instructors, student, grade_performance table pho
ID: 3852164 • Letter: I
Question
I need sql code. I send major, instructors, student, grade_performance table photo because its not print.
MAJOR table
INSTRUCTOR
table
STUDENT table
GRADE_PERFORMANCE table
Please complete following exercises based on above tables. (5 points per exercise)
1. Please write down SQL select statement to generate and display results based on following criteria:
5 columns: student_id, student full name, major, advisor full name, and gpa
Display all students
Instructors are also students’ advisors
Sort data based on student’s major in alphabetical order
2. Please write down SQL select statement to generate and display results based on following criteria:
5 columns: student_id, student full name, major, gpa, and grade performance Display all non- web students.
Sort data based on student’s gpa from lowest to highest
3. Please write down SQL select statement to generate and display results based on following criteria:
3columns: major, number of student, and average of gpa Display all students.
Sort data based on student’s major in alphabetical order
4. Please write down SQL select statement to generate and display results based on following criteria:
5 columns: student initial, number of student, and average of gpa Display all students.
Sort data based on student’s initial in alphabetical order
5. Please write down SQL select statement to generate and display results based on following criteria:
5 columns: student initial, number of student, and average of gpa
Display all students with student’s last_name initial is either ‘B’, ‘G’, or ‘F’. Sort data based on average of gpa from lowest to highest
Pease cormplete owing eaecl based on above tables 15 points per esemheExplanation / Answer
Program :Please consider below one is final program
SELECT name
FROM students JOIN grades ON students.id = grades.id
WHERE grade = 'A' AND num LIKE 'CSCI%'
UNION
SELECT name
FROM students JOIN grades ON students.id = grades.id
WHERE num IN (SELECT num
FROM sections JOIN students ON instructor_id = id
WHERE name = 'Burch')
If you use UNION ALL, then the DBMS will not attempt to remove duplicates.
You can use INTERSECT to find the intersection of two sets. This lists the names of all students have have both earned an A in a CSCI course and have taken a course from Burch.
SELECT name
FROM students JOIN grades ON students.id = grades.id
WHERE grade = 'A' AND num LIKE 'CSCI%'
INTERSECT
SELECT name
FROM students JOIN grades ON students.id = grades.id
WHERE num IN (SELECT num
FROM sections JOIN students ON instructor_id = id
WHERE name = 'Burch')
And finally you can use EXCEPT to list those that appear in one set but not another. Following lists all students who received in an A in a CSCI course, but who have not taken a course from Burch.
SELECT name
FROM students JOIN grades ON students.id = grades.id
WHERE grade = 'A' AND num LIKE 'CSCI%'
EXCEPT
SELECT name
FROM students JOIN grades ON students.id = grades.id
WHERE num IN (SELECT num
FROM sections JOIN students ON instructor_id = id
WHERE name = 'Burch')