Update the salaries of faculty as their current salary + 10000 · (the number of

Question

Update the salaries of faculty as their current salary + 10000 · (the number of courses they are teaching).

Consider the following library database schema and example instance storing book bookid price total_copies 84.66 uction of Algorit Database System Concepts74.99 Stochastic Calulus for Finance 41.02 Stochastic Calculus for Finance II 55.22 course courseid titl structor text DB Organization Advanced DB Organization Math Finance I Math Finance II student faculty enroll studentid courseid studentid name gpa Tom 3.3 John 38 Mary 3.0 Kris 3.6 Alex 3.5 facultyiod James70000 Sarah60000 Jay80000 Rache 70000 Pau85000 book checkout bookid studentid date 2017-08-29 2017-09-02 2017-09-07 Hints: All the attributes that have integer values are of type INT; numbers with decimal point are of type NUMERIC; the attribute date of book checkout relation is of type DATE; others are of type VARCHAR Attributes with black background form the primary key of an relation The attribute instructorid of relation course is a foreign key to relation facuity, and tertbookid is a foreign key to relation book. The attribute studentid of relation enroll is a foreign key to relation student, and courseid is a foreign key to relation course. The attribute bookid of relation book_checkout is a foreign key to relation book, and studentid is a foreign key to relation student.

Explanation / Answer

As per your requirement, you want to give the increment to specific faculty as per the number of subject he or she taught-

in the above schenario the james with instructor is=1 taugh two subject(Algorithm & Math finance 1) so according to query it increment like salary=salary+10000*2

other three faculties teach one subject so salary=salary+10000*1

and paul does not teach any subject so there is no increment to paul

Query is look like this:

update faculty f set salary=salary+1000*(select count(instructorid) from course c where f.instructorid=c.instructorid);

Step1: first we need to find the no. of subject is taught by any faculty for this we used sub query and used count function for instructorid column

Step2:now we count the number of subject for every faculty on the behalf of instructorid matching

Step3:now update the salary on behalf matching count.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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