Follow Us :

SQL Machine Learning

Avatto > > DATA SCIENTIST > > SHORT QUESTIONS > > 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’)