Write queries using aggregate functions in relational algebra (DBMS)?

DBMSDatabaseBig Data Analytics

The aggregate functions are follows −

  • max()

  • min()

  • sum()

  • average()

  • count()

Consider the following tables −

Employee − Emp(empid,ename,salary)

EmpidEmpnameSalary
E1Ravi4000
E2Sanjay3500
E3Sruthi3000
E4Alok3000
E5Pritam3000

Dept − Dept(deptid,dname)

DepidDname
D1Cse
D2Ece
D3Civil

Project − Project(projected,pname)

ProjectidPname
P1Database
P2networking

Worksin − Worksin(empid,depid)

EmpidDepid
E1P1
E2P2
E3P1
E4P2
E5P2

Assign − Assign(empid,projectid)

EmpidProjectid
E1P1
E2P2
E3P1
E4P2
E5P2

Example 1

Display the details of the employee who works in the ece department.

Step 1

Use the below mentioned syntax. Here, T1= deptid of Ece

T1=∏deptid(σdname=ece(dept))

Output

Deptid
D2

Step 2

Here, ⋈= {T1.deptid=worksin.deptid} and T2= all empid of Ece

T2= ∏empid(T1⋈worksin)

Output

Empid
E3
E4

Step 3

Here, T3=(T2⋈emp), ⋈={T2.empid=emp.empid} and T3= details of all employees of Ece

EmpidEmpnameSalary
E3Smruthi3000
E4Alok3000

Example 2

Display all names of employees who work on database projects.

Step 1

Use the command mentioned below −

T1=∏projectid(σpname=database(project))

Output

Projectid
P1

Step 2

Use the command given below −

T2= ∏empid(T1⋈assign)

Output

Empid
E1
E2

Step 3

Use the command given below −

T3= ∏empname(T2⋈emp)

Output

Empname
Ravi
Smruti
raja
Published on 06-Jul-2021 14:51:49
Advertisements