DBMS-Slip7

Slip7

Questions

Consider the following Entities and Relationships                                      

Employee (emp_no, name, skill, payrate)

Position (posting_no, skill)

Relation between Employee and Position is Many to Many with day and shift as descriptive attribute.

Constraint: Primary key, payrate should be > 0.

Solution:-

Create a Database in 3NF & write queries for following.

Find the names and rate of pay all employees who allocated a duty.

SQL>Select name, payrate from Employeee;

Give employee number who are working at posting_no. 201, but don’t have the skills of waiter.

SQL>Select name  from  Employeee, Position, Employeee_Position where   Employeee.    emp_no = Employeee_Position. emp_no and  position.posting_no=Employeee_Position.posting_no

and Position.posting_no=1 and skillp not in('waiter’);

Display a list of names of employees who have skill of chef and who has assigned a duty.

SQL>select name ,skillp from Employeee, Position, Employeee_Position where   Employeee.  emp_no = Employeee_Position. emp_no and  Position.posting_no= Employeee_Position.posting_no and Position.Skillp=’chef’;

Display emp_no and dates for all employees who are working on Tuesday and at least one other day.

SQL> select emp_no,day  from Employeee, Employeee_Position where   Employeee. emp_no = Employeee_Position. emp_no and  day=’ Tuesday’;

Displayshiftwise employee details.

SQL>select name,shift from Employeee, Employeee_Position where   Employeee. emp_no = Employeee_Position. emp_no   group by shift ,name;