Question 1: Find all instructors that belong to the “Physics” Department: (just the name) sql answer

Question 1:
Find all instructors that belong to the “Physics” Department: (just the name)


SQL answer:
select name
from instructor where dept_name=”Physics”


Rational algebra answer:
σ _{dept_name=”Physics”} (instructors)


Question 2:
Find the instructor with the highest salary: (name and id)


SQL answer:
select name, ID, Max(Salary)
from instructor


Rational algebra answer:
∏ _{instructor.salary} (σ _{instructor.salary < d,salary} (instructor x ρ_d (instructor)))
Question 3:
Find the average salary in every department:


SQL answer:
SELECT avg(salary)
from instructor


Rational algebra answer:
_{G avg(salary)} (instructor)
Question 4:
Show which department has the highest as average salary:


SQL answer:
SELECT max(SALARY) as avgSalary
FROM instructor


Rational algebra answer:
∏ _{G max(salary) avgSalary}(instructor)
Question 5:
Show all courses that were NOT taught in the year 2017


SQL answer:
select *
from course
where year=2017 and course = null


Rational algebra answer:
∏course_id (σ _{semester=null Λ year=2017} (section))
Question 6:
Please provide an alternative to the following SQL statements without using “INTERSECT”
(select course_id from section where sem = 'Fall' and year = 2017)
intersect
(select course_id from section where sem = 'Spring' and year = 2018)


SQL answer:
select course_id from section where semester = 'Fall' and year = 2017
EXCEPT
select course_id from section where semester = 'Spring' and year = 2018


Rational algebra answer:
∏_{course_id} (σ _{semester=“Fall” Λ year=2009} (section)) U ∏_{course_id} (σ _{semester=“Spring” Λ year=2010} (section))


Bonus question:
Yossi has decided to rewrite the following SQL statement without using the word “between”

select name

from instructor
where salary between 90000 and 100000
He wrote:
select name
from instructor
where salary > 90000 and salary < 100000
Was he correct? Can you fix his query?


SQL answer :
SELECT name
FROM instructor
WHERE salary > 90000 AND salary<100000;


Rational algebra answer:
_name(instructor)
_{salary >= 90000 salary<=100000}