SQL Machine Learning

EmpNo	EName	Job	MGR	HireDate	Sal	Comm	DeptNo
1234	Amit	Waiter	8382	19-Oct-18	50000	500	50
5678	Ashish	Analyst	8635	2-Nov-18	60000	200	51

DeptNo	Dname	Loc
50	Service	Delhi
51	Account	Mumbai

SELECT *
FROM emp e, dept d
WHERE UPPER(d.loc) IN (‘DELHI,’BANGALORE’) AND e.DeptNo = d.DeptNo
AND e.HireDate < (SELECT e.HireDate FROM emp e WHERE EName = ‘Aman’);

SELECT * FROM emp

EmpNo	EName	Job	MGR	HireDate	Sal	Comm	DeptNo
1234	Amit	Waiter	8382	19-Oct-18	50000	500	50
5678	Ashish	Analyst	8635	2-Nov-18	60000	200	51

WHERE TIMESTAMPDIFF(MONTH, HireDate, sysdate)

EmpNo	EName	Job	MGR	HireDate	Sal	Comm	DeptNo
1234	Amit	Waiter	8382	19-Oct-18	50000	500	50
5678	Ashish	Analyst	8635	2-Nov-18	60000	200	51

SELECT *
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE EName = ‘Aman’);

EmpNo	EName	Job	MGR	HireDate	Sal	Comm	DeptNo
1234	Amit	Waiter	8382	19-Oct-18	50000	500	50
5678	Ashish	Analyst	8635	2-Nov-18	60000	200	51

SELECT *
FROM emp w, emp m
WHERE w.MGR = m.EmpNo and w.HireDate < m.HireDate
Or
SELECT *
FROM emp w, emp m
WHERE w.EmpNo = m.MGR and w.HireDate < m.HireDate

EmpNo	EName	Job	MGR	HireDate	Sal	Comm	DeptNo
1234	Amit	Waiter	8382	19-Oct-18	50000	500	50
5678	Ashish	Analyst	8635	2-Nov-18	60000	200	51

SELECT *
FROM emp
WHERE HireDate < (SELECT HireDate FROM emp WHERE EName = ‘Aman’)