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
129	Bangalore	21-july-2003	7000	21-july-2023	Finance

(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 atleast 3 years of service.

(o) Write the output of :

1. Select distinct hobby from personal ;
2. Select avg(salary) from personal, job where Personal.Empno = Job.Sno and Area in ('Agra','Delhi') ;
3. Select count(distinct Native_place) from personal.
4. 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.

(a)

SELECT P.EMPNO, P.NAME, J.Salary 
FROM PERSONAL P, JOB J
WHERE P.EMPNO = J.Sno AND P.Hobby = 'Sports';

Output

+-------+--------+--------+
| EMPNO | NAME   | Salary |
+-------+--------+--------+
|   125 | Vinod  |   8500 |
|   129 | Ramesh |   7000 |
+-------+--------+--------+

(b)

SELECT name
FROM personal
WHERE dobirth = (
    SELECT MIN(dobirth)
    FROM personal
);

Output

+------+
| name |
+------+
| Amit |
+------+

(c)

SELECT Area, COUNT(Sno) AS Employee_Count
FROM Job
GROUP BY Area;

Output

+-----------+----------------+
| Area      | Employee_Count |
+-----------+----------------+
| Agra      |              2 |
| Delhi     |              1 |
| Mathura   |              1 |
| Pune      |              1 |
| Bangalore |              1 |
+-----------+----------------+

(d)

SELECT Name, `Native-place`, dobirth  
FROM personal  
WHERE dobirth = (SELECT MAX(dobirth)         
FROM personal p2          
WHERE personal.`Native-place` = p2.`Native-place` ) ;

Output

+--------+--------------+------------+
| Name   | Native-place | dobirth    |
+--------+--------------+------------+
| Abhai  | Allahabad    | 1975-08-11 |
| Vinod  | Delhi        | 1977-04-04 |
| Manoj  | Mumbai       | 1976-12-12 |
| Ramesh | Pune         | 1981-10-28 |
+--------+--------------+------------+

(e)

SELECT SNO, NAME, HOBBY, SALARY 
FROM PERSONAL, JOB 
WHERE PERSONAL.EMPNO = JOB.SNO 
ORDER BY SALARY DESC;

Output

+-----+--------+-----------+--------+
| SNO | NAME   | HOBBY     | SALARY |
+-----+--------+-----------+--------+
| 125 | Vinod  | Sports    |   8500 |
| 128 | Abhay  | Gardening |   7500 |
| 129 | Ramesh | Sports    |   7000 |
| 127 | Manoj  | Writing   |   6000 |
| 124 | Abhai  | Music     |   5500 |
| 123 | Amit   | Music     |   5000 |
+-----+--------+-----------+--------+

(f)

SELECT HOBBY 
FROM PERSONAL 
WHERE Name = 'abhay' or Name = 'abhai' ;

Output

+-----------+
| HOBBY     |
+-----------+
| Music     |
| Gardening |
+-----------+

(g)

SELECT App_date, nativeplace 
FROM personal, job 
WHERE personal.empno = job.sno 
AND (Name LIKE 'A%' OR Name LIKE '%d') ;

Output

+------------+--------------+
| App_date   | native-place |
+------------+--------------+
| 2006-01-25 | Delhi        |
| 2007-08-19 | Allahabad    |
| 2004-04-14 | Delhi        |
| 2008-03-13 | Mumbai       |
+------------+--------------+

(h)

SELECT Salary AS "Salary Expense" 
FROM Job
WHERE `Retd_date` > '2006-01-20';

Output

+----------------+
| Salary Expense |
+----------------+
|           5000 |
|           5500 |
|           8500 |
|           6000 |
|           7500 |
|           7000 |
+----------------+

(i)

SELECT SUM(Salary * 0.1) AS "Additional Burden" 
FROM PERSONAL, JOB
WHERE PERSONAL.EMPNO = JOB.SNO AND HOBBY = 'SPORTS' ;

Output

+-------------------+
| Additional Burden |
+-------------------+
|            1550.0 |
+-------------------+

(j)

SELECT Hobby 
FROM PERSONAL 
GROUP BY Hobby 
HAVING COUNT(*) >= 2;

Output

+--------+
| Hobby  |
+--------+
| Music  |
| Sports |
+--------+

(k)

SELECT COUNT(*)  
FROM Job
WHERE DATEDIFF(CURDATE(), App_date) >= 20 * 365;

Output

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

(l)

SELECT P.Name, P.Dobirth 
FROM Personal P, Job J 
WHERE P.Empno = J.Sno AND J.Retd_date > CURDATE() AND DATEDIFF(CURDATE(), J.App_date) > 17 * 365;

Output

+-------+------------+
| Name  | Dobirth    |
+-------+------------+
| Amit  | 1965-01-23 |
| Manoj | 1976-12-12 |
+-------+------------+

(m)

SELECT Name 
FROM Personal, job
where personal.Empno = job.Sno 
and job.Salary > ( select max(salary) 
from job
where dept = 'sales');

Explanation

There will be no output because there are no employees whose salary is greater than the highest salary in the Sales department.

(n)

UPDATE Job J, Personal P
SET J.Salary = (J.Salary * 0.05 ) + J.Salary
WHERE J.Sno = P.Empno
AND (P.Hobby = 'Music' 
OR DATEDIFF(CURDATE(), J.App_date) >= 3 * 365);

Output

+-----+-----------+------------+--------+------------+-----------+
| sno | area      | app_date   | salary | retd_date  | dept      |
+-----+-----------+------------+--------+------------+-----------+
| 123 | Agra      | 2006-01-25 |   5250 | 2026-01-25 | Marketing |
| 124 | Agra      | 2007-08-19 |   5775 | 2027-08-19 | Marketing |
| 125 | Delhi     | 2004-04-14 |   8925 | 2018-04-14 | Sales     |
| 127 | Mathura   | 2006-12-22 |   6300 | 2026-12-22 | Finance   |
| 128 | Pune      | 2008-03-13 |   7875 | 2028-03-13 | Sales     |
| 129 | Bangalore | 2003-07-21 |   7350 | 2023-07-21 | Finance   |
+-----+-----------+------------+--------+------------+-----------+

(o)

1.

Select distinct hobby from personal ; 

Output

+-----------+
| hobby     |
+-----------+
| Music     |
| Sports    |
| Writing   |
| Gardening |
+-----------+

2.

Select avg(salary) from personal, job where Personal.Empno = Job.Sno and Area in ('Agra','Delhi') ;  

Output

+-------------+
| AVG(SALARY) |
+-------------+
|   6650.0000 |
+-------------+

3.

Select count(distinct Native_place) from personal;

Output

+--------------------------------+
| COUNT(DISTINCT `NATIVE-PLACE`) |
+--------------------------------+
|                              4 |
+--------------------------------+

4.

Select name, max(salary) 
from Personal, Job where Personal.Empno = Job.Sno ;

Output

+------+-------------+
| name | max(salary) |
+------+-------------+
| Amit |        8500 |
+------+-------------+

Explanation

The given query retrieves the maximum salary from the 'Job' table and pairs it with the corresponding employee name from the 'Personal' table based on the matching Empno and Sno values. However, including a non-group field like 'name' in the select-list means it will return the value from the first record of the group for the 'name' field. Therefore, 'Amit' is the first record in the 'Personal' table, the query returns 'Amit' as the value for the 'name' field.

(p)

INSERT INTO Personal (Empno, Name, Dobirth, `Native-place`, Hobby)
VALUES (130, 'Amruta', '1990-05-15', 'Chennai', 'Music');

Output

select * from personal;

+-------+--------+------------+--------------+-----------+
| empno | name   | dobirth    | native-place | hobby     |
+-------+--------+------------+--------------+-----------+
|   123 | Amit   | 1965-01-23 | Delhi        | Music     |
|   124 | Abhai  | 1975-08-11 | Allahabad    | Music     |
|   125 | Vinod  | 1977-04-04 | Delhi        | Sports    |
|   127 | Manoj  | 1976-12-12 | Mumbai       | Writing   |
|   128 | Abhay  | 1974-03-10 | Mumbai       | Gardening |
|   129 | Ramesh | 1981-10-28 | Pune         | Sports    |
|   130 | Amruta | 1990-05-15 | Chennai      | Music     |
+-------+--------+------------+--------------+-----------+

(q)

ALTER TABLE Job
ADD COLUMN Email VARCHAR(55); 

(r)

insert into empdetails(empno, name, hobby)
select empno, name, hobby
from personal ;

Output

select * from empdetails ;

+-------+--------+-----------+
| Empno | Name   | Hobby     |
+-------+--------+-----------+
|   123 | Amit   | Music     |
|   124 | Abhai  | Music     |
|   125 | Vinod  | Sports    |
|   127 | Manoj  | Writing   |
|   128 | Abhay  | Gardening |
|   129 | Ramesh | Sports    |
|   130 | Amruta | Music     |
+-------+--------+-----------+

(s)

CREATE VIEW LessThan15YearsView AS
SELECT * FROM Personal p, Job j   
WHERE p.Empno = j.Sno AND 
DATEDIFF(CURDATE(), J.App_date) < 15 * 365;

(t)

DELETE j
FROM Job j, Personal p 
WHERE j.Sno = p.Empno AND p.Hobby <> 'Sports';

(u)

DROP TAbLE Personal;