With reference to following relations PERSONAL and JOB answer the questions that follow:

Q. With reference to following relations PERSONAL and JOB answer the questions that follow:

Create following tables such that Empno and Sno are not null and unique, date of birth is after '12-Jan-1960', name is never blank, Area and Native place is valid, hobby, dept is not empty, salary is between 4000 and 10000.

Table: Personal

 
 
 
	 
Empno
 
	 
Name
 
	 
Dobirth
 
	 
Native place
 
	 
Hobby
 
 
 
	 
123
 
	 
Amit
 
	 
23-Jan-1965
 
	 
Delhi
 
	 
Music
 
 
 
	 
127
 
	 
Manoj
 
	 
12-dec-1976
 
	 
Mumbai
 
	 
Writing
 
 
 
	 
124
 
	 
Abhai
 
	 
11-aug-1975
 
	 
Allahabad
 
	 
Music
 
 
 
	 
125
 
	 
Vinod
 
	 
04-apr-1977
 
	 
Delhi
 
	 
Sports
 
 
 
	 
128
 
	 
Abhay
 
	 
10-mar-1974
 
	 
Mumbai
 
	 
Gardening
 
 
 
	 
129
 
	 
Ramesh
 
	 
28-oct-1981
 
	 
Pune
 
	 
Sports
 
 
 
 

Table: job

 
 
 
	 
Sno
 
	 
Area
 
	 
App_date
 
	 
Salary
 
	 
Retd_date
 
	 
Dept
 
 
 
	 
123
 
	 
Agra
 
	 
25-jan-2006
 
	 
5000
 
	 
25-jan-2026
 
	 
Marketing
 
 
 
	 
127
 
	 
Mathura
 
	 
22-dec-2006
 
	 
6000
 
	 
22-dec-2026
 
	 
Finance
 
 
 
	 
124
 
	 
Agra
 
	 
19-aug-2007
 
	 
5500
 
	 
19-aug-2027
 
	 
Marketing
 
 
 
	 
125
 
	 
Delhi
 
	 
14-apr-2004
 
	 
8500
 
	 
14-apr-2018
 
	 
Sales
 
 
 
	 
128
 
	 
Pune
 
	 
13-mar-2008
 
	 
7500
 
	 
13-mar-2028
 
	 
Sales
 
 
 

(a) Show empno, name and salary of those who have Sports as hobby.

(b) Show name of the eldest employee.

(c) Show number of employee area wise.

(d) Show youngest employees from each Native place.

(e) Show Sno, Name, Hobby and Salary in descending order of Salary.

(f) Show the hobbies of those whose name pronounces as 'Abhay'.

(g) Show the appointment date and native place of those whose name starts with 'A' or ends in 'd'.

(h) Show the salary expense with suitable column heading of those who shall retire after 20-jan-2006.

(i) Show additional burden on the company in case salary of employees having hobby as sports, is increased by 10%.

(j) Show the hobby of which there are 2 or more employees.

(k) Show how many employee shall retire today if maximum length of service is 20 years.

(l) Show those employee name and date of birth who have served more than 17 years as on date.

(m) Show names of those who earn more than all of the employees of Sales dept.

(n) Increase salary of the employees by 5% of their present salary with hobby as Music or they
have completed at least 3 years of service.

(o) Write the output of:

(i) Select distinct hobby from personal;
(ii) Select avg(salary) from personal, job where Personal.Empno = Job.Sno and Area in ('Agra',
'Delhi');
(iii) Select count(distinct Native_place) from personal.
(iv) Select name, max(salary) from Personal, Job where Personal.Empno = Job.Sno;

(p) Add a new tuple in the table Personal essentially with hobby as Music.

(q) Insert a new column email in Job table.

(r) Create a table with values of columns empno, name, and hobby.

(s) Create a view of Personal and Job details of those who have served less than 15 years.

(t) Erase the records of employee from Job table whose hobby is not Sports.

(u) Remove the table Personal.

Answer :-

(a)
Select empno , name , salary from personal , Job  where Empno =  Sno and Hobby = “Sports” ;

(b)
Select * from personal where (select min( dobirth ) from personal ) = dobirth ;

(c)
Select count(name ) , from Personal , Job where Empno = Sno group by Area ;

(d)

Select name from personal Group by Native-place Order by max(dobirth) ;

(e)
Select Sno,Name , Hobbay , Salary from Personal , Job where Empno = Sno order by Salary desc ;

(f)
Select Hobby from Personal where name = “Abhay” ;

(g)
Select App_date , Native -place from Personal , Job where Empno = Sno and Name like “A%” or name like “%r” ;

(h)
Select salary as ' salary expense' from personal P, job J where p.empno=J.sno AND retd_date< '20160120' ;

(i)

Select ( sum ( Salary ) * 0.10 ) as “Additional_burden” from Personal , Job where Empno = Sno and Hobby = “Sports” ;

(j)
Select Hobby from Personal group by hobby having Count(*) >= 2 ;

(k)
Select name from personal , job where empno = sno and ( curdate( ) - app_date ) >= 200000 ;

(l)

Select name , Dobrirth from personal , job where empno = Sno and ( curdate() - app_date ) > 170000 ;

(m) Select Name from Personal, Job where Empno = Sno and Dept = “Sales” Order by max( Salary) in ('Sales');

(n) Update Personal , job
set salary = salary + 0.05 * salary
where Empno = Sno and Hobby = “Music”
or ( curdate() - App_date ) >= 30000 ;

(o)

(i)

distinct hobby

Music

Writing

Sports

Gardening

(ii)

avg(salary)

6333.3333

(iii)

count(distinct Native_place)

4

(iv)

Name	Max(salary)
Vinod	8500

 
(p)
Insert into Personal (Hobby) values (135,"PathWalla", 20081990, "Lucknow" “Music”);

(q)
Alter table Job add column email char(100) ;

(r)
Create table new_table (Select Empno , name,Hobby from Personal);

(s)
Select * from personal , job where empno = sno and ( retd_date - app_date ) < 150000 ;

(t)

Delete from job, personal where empno = Sno and hobby < >  “Sports” ;

(u)
Drop Table Personal;