write the following SQL queries. write a query to select the students from each

Question

write the following SQL queries. write a query to select the students from each course. Write a query to list the average GPA per course. Write a query to List the number of students in each course. Write a query to list which students each teacher is teaching. Write a query to list the number of students with each declared major. Please explain your queries and be clear. I will upvote clear answer. Thanks

create table student( student_num varchar2(10) PRIMARY KEY, Fname varchar2(20), Lname varchar2(20),
age decimal(5,2), GPA REAL, CHECK (GPA>=0.00), CHECK (GPA<=4.00), gender varchar2(10),
major varchar2(30), year varchar2(20));

create table address( s_number varchar2(10) references student(student_num), street varchar2(20),
city varchar2(20), state varchar2(20), zipcode number(10), country varchar2(20));

create table instructor ( I_id varchar2(10) PRIMARY KEY, name varchar2(20),
dept varchar2(20));

create table section( SectionNumber varchar2(10) PRIMARY KEY, term varchar2(10), year number(10),
location varchar2(20), s_id varchar2(10) references student(student_num),
instructor_id varchar2(10) references instructor(I_id));

create table course( course_num varchar2(10) PRIMARY KEY, name varchar2(20),
creditHours number(5), section_id varchar2(10) references section(SectionNumber));

Explanation / Answer

Answers:

--write a query to select the students from each course.

Select c.name as course, s.Fname as StudentName
From course c inner join section sec on c.section_id = sec.SectionNumber
inner join student s on s.student_num = sec.s_id
order by c.name ASC

--Write a query to list the average GPA per course.
--Average GPA = (total GPA for that course / Number of Student in that course)

-- we first get the number of student and the totalGPA for each course and then find the average.

Declare @temp table (course_num varchar(10),TotalGPA REAL, NoStudent INT)
INSERT INTO @temp
(
course_num,
TotalGPA,
NoStudent
)
Select
c.course_num,
SUM(s.GPA),
COUNT(s.student_num)
From
course c inner join section sec on c.section_id = sec.SectionNumber
inner join student s on s.student_num = sec.s_id
Group by c.course_num

Select c.name as course,
(t.TotalGPA/t.NoStudent) as AverageGPA
From course c inner join @temp t on t.course_num = c.course_num
order by c.name

--Write a query to List the number of students in each course.

Select
c.name as course,
COUNT(s.student_num) as numberofstudent
From
course c inner join section sec on c.section_id = sec.SectionNumber
inner join student s on s.student_num = sec.s_id
Group by c.name

--Write a query to list which students each teacher is teaching.

Select i.name as InstructorName,
s.Fname as StudentName
From instructor i inner join section sec on sec.instructor_id = i.I_id
inner join student s on s.student_num = sec.s_id
order by i.name

--Write a query to list the number of students with each declared major.

Select s.major as Major,
COUNT(s.student_num) as NumberofStudent
From student s
group by s.major order by s.major

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.