DBMS-Slip7

Slip7
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;