Mastering Relational Algebra for Efficient Database Queries | DBMS Queries

 Introduction

Relational algebra is a fundamental concept in the world of databases. It provides a structured way to manipulate data stored in relational database systems. In this article, we will explore the power of relational algebra through practical examples and queries.

Query 1: Finding Employees at IBM

Heading 1: Query Description

In this query, we will discover how to retrieve the names, street addresses, and cities of residence of all employees who work for 'IBM' and earn more than Rs. 15,000/-
Heading 2: SQL Query

SELECT L.person_name, L.street, L.city
FROM Lives L
JOIN Works W ON L.person_name = W.person_name
WHERE W.company_name = 'IBM' AND W.salary > 15000;

Query 2: Exploring Company Locations

Heading 1: Query Description

In this section, we aim to find the names of all companies located in "Nagpur."

Heading 2: SQL Query

SELECT DISTINCT L.company_name
FROM Located-in L
WHERE L.city = 'Nagpur';

Query 3: Employee Database

Heading 1: Query Description

Let's delve into a database containing information about employees, their departments, and more. We'll run several queries to extract valuable insights.

Heading 2: Query 3a - Finding Employees at First Bank Corporation

Heading 3: SQL Query

SELECT E.emp_name, E.city
FROM employee E
JOIN works W ON E.emp_name = W.emp_name
WHERE W.company_name = 'First Bank Corporation';

Heading 2: Query 3b - Employees at First Bank Corporation Earning Over $10,000

Heading 3: SQL Query

SELECT E.emp_name, E.street, E.city
FROM employee E
JOIN works W ON E.emp_name = W.emp_name
WHERE W.company_name = 'First Bank Corporation' AND W.salary > 10000;

Heading 2: Query 3c - Finding Employee at IBM with Salary Over $20,000

Heading 3: SQL Query

SELECT E.emp_name
FROM employee E
JOIN works W ON E.emp_name = W.emp_name
WHERE W.company_name = 'IBM' AND W.salary > 20000;

Query 4: Employee and Department Database

Heading 1: Query Description

In this section, we will work with a database containing information about employees, departments, and projects. We will run several relational algebra queries to gain valuable insights.

Heading 2: Query 4i - Average Salary of Female Employees

Heading 3: Relational Algebra Expression

π AVG(Salary) (σ Gender='female' (Employee))

Heading 2: Query 4ii - Employees in the Research Department

Heading 3: Relational Algebra Expression

π ename, Add (σ D_name='Research Department' (Employee ⨝ Dno=dept_no Dept))

Heading 2: Query 4iii - Total Hours Spent on Each Project

Heading 3: Relational Algebra Expression

π Pname, SUM(hours) (Project ⨝ Pno=Pno Works_On)

Heading 2: Query 4iv - Employees in Department 4 Working Over 12 Hours Per Week

Heading 3: Relational Algebra Expression

π ename (σ Dno=4 ∧ hours>12 (Employee ⨝ SS#=SS# Works_On))

Query 5: Sales Database

Heading 1: Query Description

In this section, we will work with a sales database, exploring queries related to salespeople, orders, and customers.

Heading 2: Query 5i - Salespeople with Orders from "ASIAN CONSTRUCTION"

Heading 3: SQL Query

SELECT S.Name, S.Percent_of_Quota
FROM SALESPERSON S
JOIN ORDER O ON S.Name = O.salespersonname
WHERE O.Custname = 'ASIAN CONSTRUCTION';

Heading 2: Query 5ii - Quota Percentages for Salespeople with Orders in "MUMBAI"

Heading 3: SQL Query

SELECT DISTINCT S.Percent_of_Quota
FROM SALESPERSON S
JOIN ORDER O ON S.Name = O.salespersonname
JOIN CUSTOMER C ON O.Custname = C.Name
WHERE C.city = 'MUMBAI';

Query 6: Loan and Payment Database

Heading 1: Query Description

In this section, we will explore a database related to loans and payments, running queries to analyze loan and payment data.

Heading 2: Query 6i - Average Loan Amount

Heading 3: SQL Query

SELECT AVG(Amount) AS AvgLoanAmount
FROM Loan;

Heading 2: Query 6ii - Maximum Loan Amount

Heading 3: SQL Query

SELECT MAX(Amount) AS MaxLoanAmount
FROM Loan;

Heading 2: Query 6iii - Minimum Payment Amount

Heading 3: SQL Query

SELECT MIN(Payment_amount) AS MinPaymentAmount
FROM Payment;

Heading 2: Query 6iv - Total Number of Payments

Heading 3: SQL Query

SELECT COUNT(*) AS TotalPayments
FROM Payment;

Heading 2: Query 6v - Total Amount Paid for All Loans

Heading 3: SQL Query

SELECT SUM(Payment_amount) AS TotalAmountPaid
FROM Payment;

Heading 2: Query 6vi - Loans with Payments Greater Than $5,000

Heading 3: SQL Query

SELECT DISTINCT L.Loan#
FROM Loan L
JOIN Payment P ON L.Loan# = P.Loan#
WHERE P.Payment_amount > 5000;

Conclusion

Relational algebra is a powerful tool for querying databases, allowing you to extract valuable information and gain insights from complex data structures. By mastering the concepts and queries presented in this article, you'll be well-equipped to handle various database scenarios efficiently.