# Questions on Relational Algebra (Part 1)

## Questions on Relational Algebra (Part 1)

Consider the relations:

1. Suppliers(SID, Sname, Rating)
2. Parts(PID, Pname, Color)
3. Catalog( SID, PID, Cost)
`Query 1: Retrieve SIDs of Suppliers whose rating > 10.`
```Solution:
```
`Query 2: Retrieve SIDs of Suppliers who supplied red parts.`
```Solution:
```
`Query 3: Retrieve Sname of Suppliers who supplied red parts.`
```Solution:

From optimization point of view 2) & 3) are same, only no. of
fields(columns) are reduced but rows are same.
The input output cost of 2) & 3) is also same but of 1) is far more.```
```Query 4: Retrieve SIDs of Suppliers to supply some red part or some
green part.```
```Solution:
```
```Query 5: Retrieve SIDs of Suppliers to supply some red part and some
green part.```
```Solution:
```
`Query 6: Retrieve SIDs of Suppliers who supplied atleast 1 part.`
```Solution:
```
`Query 7: Retrieve SIDs of Suppliers who supplied atleast two parts.`
`Solution: Let us consider the Catalog(SID,PID,Cost) relation as :`
 SID PID Cost S1 P1 – S1 P2 – S2 P1 –
`Query 8: Retrieve SIDs of Suppliers who supplied atleast three parts.`
```Solution:
```
`Query 9: Retrieve SIDs of Suppliers who supplied exactly two parts.`
```Solution:
```
`Query 10: Retrieve SIDs of Suppliers who supplied atmost two parts.`
```Solution:
```
```Query 11: Retrieve SIDs of Suppliers whose rating >10 and who
supplied some red part.```
```Solution:
```
`Query 12: Retrieve SIDs of Suppliers who don't supply any part.`
```Solution:
```
`Query 13: Retrieve SIDs of Suppliers who supply every part.`
```Solution:
```
`Query 14: Retrieve SIDs of Suppliers who supplied every red part.`
```Solution:

```
```Query 15: Retrieve SIDs of Suppliers who supplied every red part or
green part.```
```Solution:

```
```Query 16: Retrieve SIDs of Suppliers who supplied every red part or
supply every green part.```
```Solution:

```
```Query 17: Find the pairs of sids such that the supplier with the
first sid charges more for some part than the supplier
with the second sid.```
`Solution : Let the Catalog(SID,PID,Cost) will be`
 SID PID Cost S1 P1 30 S1 P2 20 S2 P1 10

Clearly, in the question, we have

• SID’s should be different.
• PID’s should be same.
• And Cost should be different.
`    `
`Query 18: Find the pids of parts that are supplied by at least two different suppliers.`
```Solution:
```
```Query 19: Retrieve SIDs of Suppliers who supplied most and least
expensive part.```
`Solution : Let the Catalog(SID,PID,Cost) relation will be`
 SID PID Cost S1 P1 10 S1 P2 20 S2 P1 30

Steps to Retrieve SID’s of suppliers who supplied most and least expensive part :

• Retrieve Disqualified sets Firstly :
1. For Most Expensive Part : SID’s of suppliers who don’t supplied most expensive part.
2. For Least Expensive Part : SID’s of suppliers who don’t supplied least expensive part.
• From all the SID’s
```

```
```Query 20: Retrieve SIDs of Suppliers who supplied Second most
expensive part.```
```Solution :
```

### Incoming search terms:

• consider the following relation algebra suppliers gate questions 2009
• descriptive type question on Relational algebra
• relational algebra dbms with supplier and parts example