Can you please help me with this! I am complelty lost, I am trying to use SQL da

Question

Can you please help me with this! I am complelty lost, I am trying to use SQL data tools - I know I need Sections and Instructor table but what else?

QUERY Create a query that shows ebook revenue by instructor, sorted in descending order by revenue.

Instructor Last Name - Instructor First Name - DEPARTMENT_ID - REVENUE

I also need a chart that shows enrollments by department!

DEPT_NAME - ENROLLMENT

Any help would be amazing, Thanks

DATABASE DIAGRAM SUBJECTS COLLEGE COURSES SUBJECT-ID SUBJECTNAME COLLEGE ID COURSECODE SUBJECT_ID COURSE_NUMBER HOURS COURSE NAME COLLEGE_NAME - COLLEGEID - DEPARTMENTS DEPARTMENT ID DEPT NAME COLLEGE_ID INSTRUCTOR INSTRUCTOR_ID INSTRUCTOR LAST NAME INSTRUCTOR FIRST_NAME INSTRUCTOR_EMAIL DEPARTMENT ID SECTIONS SECTION ENROUIMENT COURSE CODE INSTRUCTOR_ID SEMESTER SECTION YEAR ISBN EBOOKS ISBN PRICE ONLINE LAB PUBLISHER

Explanation / Answer

Query :-

    Select Instructor_Last_Name,Instructor_First_Name,DEPARTMENT_ID,sum(PRICE) as REVENUE

    from Instructor I,Sections S,Ebooks E

    where I.Instructor_ID = S.Instructor_ID and S.ISBN = E.ISBN

    group by Instructor_Last_Name,Instructor_First_Name,DEPARTMENT_ID;

Explanation :-

    Here in order to get e book revenue of a instructor we need to join

    table instructor and ebook but there is no common column in it

    hence we used the table sections in which we have isbn as common

    column of ebook. Hence we joined all the 3 tables.

    Now in the obtained records we need to group based on the

    instructor details and we will sum the price to get revenue.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---