QUESTION 6

Question 6
WORKER (worker_id, name, wage_per_hour, specialised_in, manager_id)
JOB (job_id, type_of_job, status)
JOB_ASSIGNED (worker_id, job_id, starting_date, number_of_days)

CREATE TABLE

Worker:

create table worker

(

      worker_id number(10) primary key,

      name varchar2(10),

      wage_per_hour number(10),

      specialised_in varchar2(20),

      manager_id number(10)

);

Job:

create table job

(

      job_id number(10) primary key,

      type_of_job varchar2(10),

      status varchar2(10)

);

Assigned:

create table job_assigned

(

      worker_id number(10) references worker(worker_id),

      job_id number(10) references job(job_id),

      starting_date date,

      number_of_days number,

      primary key(worker_id,job_id)

);

INSERT RECORDS

-- Insert Into Worker

insert into worker values(001,'pankaj',1000,'computer','');

insert into worker values(002,'jigee',500,'medical',005);

insert into worker values(003,'ami',100,'sports',002);

insert into worker values(004,'geeta',600,'civil',006);

insert into worker values(005,'sumit',750,'engg',001);

insert into worker values(006,'parul',500,'autocad',003);

insert into worker values(008,'monica',500,'computer',009);

insert into worker values(009,'rahul',800,'computer',006);

insert into worker values(007,'sanju',1100,'civil',008);

--  Insert Into Job

insert into job values(05,'computer','incomplete');

insert into job values(02,'banking','half');

insert into job values(03,'engg','one third');

insert into job values(01,'electrical','incomplete');

insert into job values(04,'autocad','incomplete');

insert into job values(07,'civil','half');

insert into job values(06,'medical','one fourth');

-- Insert Into Job_Assigned

insert into job_assigned values(002,06,'02-jan-2000',350);

insert into job_assigned values(001,06,'15-jan-2010',50);

insert into job_assigned values(003,01,'22-mar-2003',150);

insert into job_assigned values(001,05,'01-jan-2000',05);

insert into job_assigned values(002,04,'02-jan-2008',20);

insert into job_assigned values(004,05,'28-dec-2000',10);

insert into job_assigned values(002,05,'30-apr-2000',89);

insert into job_assigned values(003,05,'30-apr-2000',89);

1. Display the date on which each worker is going to end his presently assigned job.
   
   select worker_id,starting_date+number_of_days "ENDING DATE" from job_assigned;
   
2. Display how many days remain for each worker to finish his job.
   
   select worker_id, round(starting_date+number_of_days - sysdate) "REMAINING DAYS"  from job_assigned where starting_date+number_of_days>sysdate;
   
3. Display the STARTING_DATE in the following format – ‘The fifth day of the month of October, 2004’.
   
   select 'The ' || to_char(starting_date,'ddth')||' day of the month of '||to_char(starting_date,'Month, yyyy') "DATE" from job_assigned;
   
4. Change the status to ‘Complete’ for all those jobs, which started in year 2000.
   
   update job set status='complete' where job_id in
   
   (
   select job_id from job_assigned where    to_char(starting_date,'yyyy')='2000' group by job_id
   )
5. Display job details of all those jobs where at least 25 workers are working.
   
   select job_id,count(worker_id) "TOTAL WORKER" from job_assigned group by job_id having count(worker_id)>=3;
   
   
6. Display all those jobs that are already completed.
   
   select ja.job_id,j.type_of_job,ja.worker_id from job_assigned ja,job j where (ja.starting_date+ja.number_of_days)<sysdate and j.job_id=ja.job_id;
   
7. Find all the jobs, which will begin within the next two weeks.
   
   select ja.job_id,j.type_of_job,ja.worker_id from job_assigned ja,job j where ja.starting_date>sysdate and ja.starting_date+ja.number_of_days<sysdate+14;
   
8. List all workers who have their wage per hour ten times greater than the wage of their managers.
   
   select a.name "worker name" from worker a,worker b where
   a.manager_id=b.worker_id and a.wage_per_hour>b.wage_per_hour*2;
   
9. List the names of workers who have been assigned the job of molding.
   
   select w.worker_id,w.name from worker w,job j,job_assigned ja where w.worker_id=ja.worker_id and j.job_id=ja.job_id and j.type_of_job='computer';
   
10. What is the total number of days allocated for packaging the goods for all the workers together.
    
    select job_id, avg(number_of_days) from job_assigned group by job_id;
    
11. Which workers receive higher than average wage per hour.
    
    select * from worker where wage_per_hour>(select avg(wage_per_hour) from worker);
    
12. Display details of workers who are working on more than one job.
    
    select worker_id, count(job_id) from job_assigned group by worker_id having count(job_id)>1;
    
13. Which workers having specialization in polishing start their job in December?
    
    select w.worker_id , name from worker w, job_assigned ja where w.specialise d_in='computer' and to_char(ja.starting_date,'mon')='dec';
    
14. Display details of workers who are specialized in the same field as that of Mr. Cacophonix or have a wage per hour more than any of the workers.
    
    select * from worker where wage_per_hour>(select MAX(wage_per_hour) from worker) or specialised_in=(select specialised_in from worker where name='pankaj');