13. (a)SELECT [Link], [Link], [Link] FROM PERSONAL P, JOB J WHERE [Link] = J.
Sno
AND [Link] = 'Sports';
(b)SELECT name FROM personal WHERE dobirth = (SELECT MIN(dobirth) FROM personal);
(c)SELECT Area, COUNT(Sno) AS Employee_Count FROM Job GROUP BY Area;
(d)SELECT Name, `Native-place`, dobirth FROM personal WHERE dobirth = (SELECT MAX(dobirth)
FROM personal p2 WHERE personal.`Native-place` = p2.`Native-place` ) ;
(e)SELECT SNO, NAME, HOBBY, SALARY FROM PERSONAL, JOB WHERE [Link] =
[Link] ORDER BY SALARY DESC;
(f)SELECT HOBBY FROM PERSONAL WHERE Name = 'abhay' or Name = 'abhai' ;
(g)SELECT App_date, nativeplace FROM personal, job WHERE [Link] = [Link] AND (Name
LIKE 'A%' OR Name LIKE '%d') ;
(h)SELECT Salary AS "Salary Expense" FROM Job WHERE `Retd_date` > '2006-01-20';
(i)SELECT SUM(Salary * 0.1) AS "Additional Burden" FROM PERSONAL, JOB WHERE
[Link] = [Link] AND HOBBY = 'SPORTS' ;
(j)SELECT Hobby FROM PERSONAL GROUP BY Hobby HAVING COUNT(*) >= 2;
(k)SELECT COUNT(*) FROM JobWHERE DATEDIFF(CURDATE(), App_date) >= 20 * 365;
(l)SELECT [Link], [Link] FROM Personal P, Job J WHERE [Link] = [Link] AND J.Retd_date >
CURDATE() AND DATEDIFF(CURDATE(), J.App_date) > 17 * 365;
(m)SELECT Name FROM Personal, jobwhere [Link] = [Link] and [Link] > ( select
max(salary) from jobwhere dept = 'sales');
(n)UPDATE Job J, Personal P SET [Link] = ([Link] * 0.05 ) + [Link] WHERE [Link] = [Link] AND
([Link] = 'Music' OR DATEDIFF(CURDATE(), J.App_date) >= 3 * 365);
(o)
[Link] distinct hobby from personal ;
[Link] avg(salary) from personal, job where [Link] = [Link] and Area in ('Agra','Delhi') ;
[Link] count(distinct Native_place) from personal;
[Link] name, max(salary) from Personal, Job where [Link] = [Link] ;
(p)INSERT INTO Personal (Empno, Name, Dobirth, `Native-place`, Hobby)
VALUES (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 ;
(s)CREATE VIEW LessThan15YearsView ASSELECT * FROM Personal p, Job j WHERE [Link] =
[Link] AND DATEDIFF(CURDATE(), J.App_date) < 15 * 365;
(t)DELETE j FROM Job j, Personal p WHERE [Link] = [Link] AND [Link] <> 'Sports';
(u)DROP Table Personal;
[Link] COMMANDS:
(i)SELECT Firstname, Lastname, Address, CityFROM EmployeesWHERE City = 'Paris';
(ii)SELECT *FROM Employees ORDER BY Firstname DESC;
(iii)SELECT [Link], [Link], ([Link] + [Link]) AS TotalSalaryFROM Employees e,
EmpSalary s WHERE [Link] = [Link] AND [Link] = 'Manager';
(iv)SELECT Designation, MAX(Salary) FROM EmpSalary WHERE Designation IN ('Manager', 'Clerk')
group by designation;
14 OUTPUT:
(i)+-----------------+------------+
| FIRSTNAME | SALARY |
+-----------------+------------+
| RACHEL | 32000 |
| PETER | 28000 |
+----------------+------------+
(ii)+---------------------------------------------+
| COUNT (DISTINCT DESIGNATION) |
+----------------------------------------------+
| 4 |
+----------------------------------------------+
(iii)+--------------------+---------------------+
| DESIGNATION | SUM(SALARY) |
+--------------------+---------------------+
| MANAGER | 215000 |
| CLERK | 135000 |
+--------------------+----------------------+
(iv) +-------------------+
| SUM (BENEFITS) |
+-----------------------+
| 32000 |
+-----------------------+
15. SELECT job, AVG(Sal) AS AvgSalary FROM Empl GROUP BY job HAVING COUNT(*) > 3;
16. SELECT Job FROM Empl GROUP BY Job HAVING MAX(Sal) >= 3000;
17. SELECT COUNT(*) AS NumManagers FROM EmpSalary WHERE Designation = 'Manager';
18. SELECT deptno, COUNT(*) AS employee_count FROM EMPL GROUP BY deptno;
19. SELECT deptno, SUM(sal) AS total_salary FROM EMPL GROUP BY deptno;
20. SELECT deptno, MAX(sal) AS max_salary FROM EMPL GROUP BY deptno;
21. SELECT c.customer_id, COUNT(o.order_id) AS total_orders FROM Customers c, orders o
WHERE c.customer_id = o.customer_id GROUP BY c.customer_id;
22. SELECT c.customer_id, [Link], SUM([Link]) AS total_order_amount FROM Customers c,
orders o WHERE c.customer_id = o.customer_id GROUP BY c.customer_id, [Link];
23. SELECT CONCAT(c.first_name, ' ', c.last_name) AS customer_name, SUM([Link]) AS
total_order_amount FROM Customers c, Orders o WHERE c.customer_id = o.customer_id GROUP BY
c.customer_id;
[Link] [Link] AS Department_Name, COUNT([Link]) AS Number_of_Employees
FROM DEPT d, EMPL e WHERE [Link] = [Link] GROUP BY [Link];
25. SELECT [Link] AS Employee_Name, [Link] AS Department_Name FROM EMPL e,
DEPT d WHERE [Link] = [Link];