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.

πi _d_ _(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