Employee
(emp_no, name, skill, payrate)
Position
(posting_no, skill)
The
relationship between Employee and Position is Many to Many with day and shift
as
descriptive
attribute.
Constraint:
Primary key, payrate should be > 0
SQL>create
table Employee(emp_no int primary key, name varchar(10), skill varchar(10),
payrate int);
SQL>insert
into Employee values(101,'asha','chef',2000);
SQL>create
table Position (posting_no int primary key,skillp varchar(10));
SQL>insert
into Position values(101,'asd');
SQL>create
table EP(emp_no int,posting_no int,Shift varchar(10));
SQL>insert into EP values(101,101,'morning');
1.
Display skill of employees name wise.
SQL>Select
* from Employeee order by name;
2.
Update the posting of employee to 220 whose skill is “Manager”.
SQL>UPDATE
postion SET posting_no= 220 WHERE
skill='Manager';
Consider the above tables and execute the
following queries:
1. Find
the names and rate of pay of all employees who has allocated a duty.
SQL>
Select name,rate from employee;
2. Give
employee number who is working at posting_no. 201, but don’t have the
skill
of waiter.
SQL>Select
name from Employeee, Position, EP where Employee.emp_no = EP. emp_no and position.posting_no=EP.posting_no
and
Position.posting_no=1 and skillp not in('waiter’);
3.
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’;
4.
Display shift wise employee details.
SQL>select
name,shift from Employeee, Employeee_Position where Employeee. emp_no = Employeee_Position.
emp_no order by shift ;
5.
Update payrate of employees to 20000 whose skill is waiter.
SQL>UPDATE
employee SET payrate= 20000 WHERE
skill='waiter';
