Answers on Basic SELECT statement with Conditions:
1. SQL> SELECT * FROM emp WHERE ename LIKE 'S%';
2. SQL> SELECT * FROM emp WHERE ename LIKE '_L%';
3. SQL> SELECT * FROM emp WHERE ename LIKE '%E_';
4. SQL> SELECT * FROM emp WHERE ename LIKE '____';
5. SQL> SELECT * FROM emp WHERE ename LIKE '%L%';
6. SQL> SELECT * FROM emp WHERE ename LIKE '_____%';
7. SQL> SELECT * FROM emp WHERE sal BETWEEN 2000 and 3000;
9. SQL> SELECT * FROM emp WHERE mgr IS NULL OR COMM IS NULL;
10. SQL> SELECT * FROM emp WHERE mgr IS NULL AND COMM IS NULL;
11. SQL> SELECT * from emp WHERE job = 'MANAGER';
12. SQL> SELECT * from emp WHERE job = 'MANAGER' and deptno in (10,20);
13. SQL> SELECT * FROM emp WHERE job IN ('CLERK','ANALYST') AND sal >= 1000 AND deptno IN (20,30);
14. SQL> SELECT * FROM emp where deptno in (20,30) and comm IS NULL;
15. SQL> SELECT * FROM emp WHERE ename LIKE ('A%') OR ename LIKE ('S%');
16. SQL>SELECT * FROM emp WHERE ename NOT LIKE ('%S') AND deptno IN (20,30);
17. SQL> SELECT * FROM emp where comm IS NOT NULL AND sal > 1500 AND deptno = 30;
18. SQL> SELECT * FROM emp where comm IS NOT NULL AND sal > 1500 AND job = 'MANAGER'
19. SQL> SELECT * FROM emp WHERE job = 'MANAGER' OR job = 'CLERK' AND sal >=2000 AND deptno NOT IN (10,20);
20. SQL> SELECT * FROM emp WHERE COMM IS NOT NULL;
21. SQL> SELECT * from emp WHERE sal NOT BETWEEN 2000 AND 3000 AND job LIKE ('%MAN%');
Comments
In ans 15 the condition is dat deptno should be 20 along wid the other conditions.
SQL> SELECT * FROM emp WHERE job = 'MANAGER' OR job = 'CLERK' AND sal >=2000 AND deptno NOT IN (10,20);
The above ans returns the records who are working in department 10,20,30. but the question is not
to display the employees who are working in department 10 and 20.
for this the ans is:
SQL> SELECT * FROM emp WHERE (job = 'MANAGER' OR job = 'CLERK') AND sal >=2000 AND deptno NOT IN (10,20);