Week5 - Introduction to Relational databases, Relational Algebra and Keys / Design

The questions can come in any order, so make sure you are selecting right option for all questions.

1. Write a query to perform the below Selection Operation.

Note: Order the result by employee id

σ_{age≥ 25}(employee)

select * from employee where age >=25 order by id

2. Write a query to perform the below Projection Operation. Note: Order the result by employee name

π_ename,salary(employee)

select ename,salary from employee order by ename

3. Write a query to perform the below Union Operation.

π_id(employee) ∪ π_employeeid(leavedetails)

select id from employee
union
select employee_id from leave_details

4. Write a query to perform the below Intersection Operation.

π_id(employee) ∩ π_employeeid(leavedetails)

select id from employee
intersect
select employee_id from leave_details

5. Write a query to perform the below Cartesian Product Operation.

Note: Order the result by employee id

employee χ department

select * from employee
cross join department
order by employee.id

6. Write a query to perform the below Join Operation.

Note: Select all columns of employee, works and department table. Order the result by employee id.

employee ⋈ works ⋈ department

select * from employee e
join works w on e.id=w.employee_id
join department d on d.id = w.department_id
order by e.id

7. Write a query to perform the below Difference Operation. 

employee – leave_details

select id from employee
minus
select employee_id from leave_details

8. Write a query to perform the below Aggregation Operation. 

select avg(salary) as average_salary from employee