Question

Consider the following tables EMPLOYEE and SALGRADE and answer (b) and (c) parts of this question:

(a) What do you understand by Selection and Projection operations in relational algebra ? 

(b) Write SQL commands for the following statements :

1. To display the details of all EMPLOYEES in descending order of DOJ.

2. To display NAME and DESIGN of those EMPLOYEES, whose SAL-GRADE is either S02 or S03.

3. TO display the content Of all the EMPLOYEES table, whose DOJ is in between ’09-Feb-2006′ and ’08-Aug-2009′.

4. To add a new row with the following: 109, ‘HarishRoy’, ‘HEAD-IT’, ‘SOX, ’09- Sep-2007′, ’21-Apr-1983’

(c) Give the output of the following SQL queries :

1. SELECT COUNT(SGRADE), SGRADE FROM EMPLOYEE GROUP BY SGRADE;

2. SELECT MIN(DOB), MAX(DOJ) FROM EMPLOYEE;

3. SELECT NAME, SALARY FROM EMPLOYEE E, SAL-GRADE S WHERE E.SGRADE= S.SGRADE AND E.ECODE<103′;

4. SELECT SGRADE, SALARY +HRA FROM SALGRADE WHERE SGRADE =SGRADE=’S02;’

Answer 1

Projection(ff): In relational algebra, a projection is a unary operation. The result of such projection is defined as the set obtained when the components Of the tuple R are restricted to the set {a1……an}. It discards (or excludes) the other attributes. 

Selection( σ,): In relational algebra, a selection is a unary operation written as πaθb(R)πaθvor (R) where:

1. a and b are attribute names.

2. p is a binary operation in the set.

3. v is a value constant.

4. R is a relation.

The selection (R) selects all those tuples in R for which 9 holds between the a and the b

(b)

1. SELECT FROM EMPLOYEE ORDER BY DOJ DESC;

2. SELECT NAME, DESIGN FROM EMPLOYEE WHERE SGRADE – “S02” OR SGRADE = “SO3;

3. SELECT * FROM EMPLOYEE WHERE DOJ BETWEEN ’09-FEB-2006′ AND ’08- AUG -200%

4. INSERT INTO EMPLOYEE VALUES(109, “HARSH RAY”, “HEAD-IT.S02”, ’09-SEP- 2007′, ’21-APR-1983′);