Answer to Question #222384 in Databases | SQL | Oracle | MS Access for Vaishnavi sanjay P

Question

Q.14 Consider the following Schema
course(courseid,title,deptname,credits)
instructor (ID, name, deptname, salary)
1. “Find the average salary of instructors in the Computer Science department.
2. “Find the total number of instructors who teach a DBE course.
3. find the number of tuples in the course relation
4. “Find the average salary in each department.
5. “Find the average salary of all instructors.

Answer 1

-- 1. “Find the average salary of instructors in the Computer Science department.

SELECT AVG(salary) FROM instructor 
WHERE deptname='Computer Science'

-- 2. “Find the total number of instructors who teach a DBE course.
-- For this query, there should be another table that maintains the relation between instructor and course
-- COnsider below relation exist
-- teaches(ID, course_id, sec_id, semester, year)

SELECT COUNT(distinct ID) FROM teaches where course_id in 
(SELECT courseid FROM course WHERE title='DBE' )

-- 3. find the number of tuples in the course relation


SELECT COUNT(*) FROM course

-- 4. “Find the average salary in each department.


SELECT deptname,AVG(salary) FROM instructor
GROUP BY deptname

-- 5. “Find the average salary of all instructors.

SELECT AVG(salary) FROM instructor