Grouping Records, Joins in SQL — Question 13
Back to all questionsWith 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 :
- Select distinct hobby from personal ;
- Select avg(salary) from personal, job where Personal.Empno = Job.Sno and Area in ('Agra','Delhi') ;
- Select count(distinct Native_place) from personal.
- 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';+-------+--------+--------+
| EMPNO | NAME | Salary |
+-------+--------+--------+
| 125 | Vinod | 8500 |
| 129 | Ramesh | 7000 |
+-------+--------+--------+
(b)
SELECT name
FROM personal
WHERE dobirth = (
SELECT MIN(dobirth)
FROM personal
);+------+
| name |
+------+
| Amit |
+------+
(c)
SELECT Area, COUNT(Sno) AS Employee_Count
FROM Job
GROUP BY Area;+-----------+----------------+
| 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` ) ;+--------+--------------+------------+
| 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;+-----+--------+-----------+--------+
| 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' ;+-----------+
| HOBBY |
+-----------+
| Music |
| Gardening |
+-----------+
(g)
SELECT App_date, nativeplace
FROM personal, job
WHERE personal.empno = job.sno
AND (Name LIKE 'A%' OR Name LIKE '%d') ;+------------+--------------+
| 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';+----------------+
| 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' ;+-------------------+
| Additional Burden |
+-------------------+
| 1550.0 |
+-------------------+
(j)
SELECT Hobby
FROM PERSONAL
GROUP BY Hobby
HAVING COUNT(*) >= 2;+--------+
| Hobby |
+--------+
| Music |
| Sports |
+--------+
(k)
SELECT COUNT(*)
FROM Job
WHERE DATEDIFF(CURDATE(), App_date) >= 20 * 365;+----------+
| 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;+-------+------------+
| 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');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);+-----+-----------+------------+--------+------------+-----------+
| 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 ; +-----------+
| hobby |
+-----------+
| Music |
| Sports |
| Writing |
| Gardening |
+-----------+
2.
Select avg(salary) from personal, job where Personal.Empno = Job.Sno and Area in ('Agra','Delhi') ; +-------------+
| AVG(SALARY) |
+-------------+
| 6650.0000 |
+-------------+
3.
Select count(distinct Native_place) from personal;+--------------------------------+
| COUNT(DISTINCT `NATIVE-PLACE`) |
+--------------------------------+
| 4 |
+--------------------------------+
4.
Select name, max(salary)
from Personal, Job where Personal.Empno = Job.Sno ;+------+-------------+
| name | max(salary) |
+------+-------------+
| Amit | 8500 |
+------+-------------+
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');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 ;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;