SQL queries used during the exercise:
- SHOW TABLES;
- DESCRIBE emp;
- DESCRIBE dept;
- DESCRIBE salgrade;
- SELECT * FROM dept;
- SELECT * FROM salgrade;
- SELECT ename, sal, hiredate FROM emp;
- SELECT location FROM dept;
- SELECT ename,sal FROM emp ORDER BY sal;
- SELECT * FROM salgrade ORDER BY losal;
- SELECT grade FROM salgrade ORDER BY losal;
- SELECT ename FROM emp ORDER BY hiredate DESC;
- SELECT COUNT(*) FROM dept;
- SELECT COUNT(*) FROM salgrade;
- SELECT ename, sal*12+1*comm AS 'Total Salary' FROM emp;
- SELECT CURRENT_TIME();
- SELECT * FROM emp WHERE deptno = 20;
- SELECT * FROM emp WHERE mgr = 7698;
- SELECT * FROM emp WHERE job = 'ANALYST';
- SELECT * FROM emp WHERE mgr IS NULL;
- SELECT ename FROM emp WHERE job='ANALYST' AND deptno = 10;
- SELECT ename FROM emp WHERE job='ANALYST' OR job='MANAGER' AND deptno = 30;
- SELECT empno FROM emp WHERE ename IN ('Jones','Smith','Blake');
- SELECT * FROM emp WHERE sal > 2000;
- SELECT ename,sal FROM emp WHERE sal >= 900 AND sal <=1000;
- SELECT ename,deptno FROM emp WHERE deptno != 20;
- SELECT ename FROM emp WHERE ename LIKE '%S%';
- SELECT ename FROM emp WHERE ename LIKE 'J%';
- SELECT ename FROM emp WHERE ename LIKE '%S';
- SELECT DISTINCT job FROM emp;
- SELECT empno FROM emp WHERE job="MANAGER";
- SELECT DISTINCT mgr FROM emp;
- SELECT emp.ename,dept.location FROM emp,dept WHERE emp.deptno = dept.deptno;
- SELECT emp.ename, dept.dname FROM emp, dept WHERE emp.deptno = dept.deptno AND sal>1000;
- SELECT emp.*, salgrade.* FROM emp, salgrade;
- SELECT emp.ename, emp.sal, salgrade.grade FROM salgrade, emp WHERE emp.sal >= salgrade.losal AND emp.sal <= salgrade.hisal;
- SELECT e.ename, m.ename FROM emp e, emp m WHERE e.mgr = m.empno AND e.deptno=20;
- SELECT e.ename, m.ename, d.dname FROM emp e, emp m , dept d WHERE e.mgr = m.empno AND m.deptno = d.deptno;
5. Multiple tables: Step 14
http://kitwallace.co.uk/SQLTutor/main.xq?id=join1&stepNo=14&command=SELECT+e.ename%2C+m.ename%2C+de.dname%2C+dm.dname+FROM+emp+e%2C+emp+m+%2C+dept+de%2C+dept+dm+WHERE+e.mgr+%3D+m.empno+AND+dm.dname+%3D+de.dname%3B
Home.