Some Useful Queries for Beginners

SELECT * FROM EMP;
SELECT EMPNO, ENAME, SAL FROM EMP;
SELECT * FROM EMP WHERE DEPTNO=20;
SELECT * FROM EMP WHERE HIREDATE = ’02-APR-81’;
SELECT * FROM EMP WHERE SAL = 5000;
SELECT * FROM EMP WHERE HIREDATE <= ’23-MAY-86’;
SELECT * FROM EMP WHERE SAL >= 1500;
SELECT * FROM EMP WHERE SAL ! = 3000;
SELECT * FROM EMP WHERE DEPTNO <> 20;
SELECT * FROM EMP WHERE HIREDATE BETWEEN ’01-JAN-81’ AND ’31-
DEC-81’;
SELECT * FROM EMP WHERE DEPTNO IN (20, 30);
SELECT * FROM EMP WHERE JOB IN (‘ANALYST’,’SALESMAN’);
SELECT * FROM EMP WHERE HIREDATE IN (’20-FEB-81’,’03-DEC-81’);
SELECT * FROM EMP WHERE ENAME LIKE ‘%M%’;
SELECT * FROM EMP WHERE JOB LIKE ‘M%ER’;
SELECT * FROM EMP WHERE ENAME LIKE ‘__R%’;
SELECT * FROM EMP WHERE ENAME LIKE ‘%\_%’ ESCAPE ‘\’;
SELECT * FROM EMP WHERE COMM IS NULL;
SELECT * FROM EMP WHERE JOB = ‘MANAGER’ AND HIREDATE < ’10-JUN-
81’;
SELECT * FROM EMP WHERE SAL < 5000 AND COMM IS NULL;
SELECT * FROM EMP WHERE DEPTNO = 30 OR JOB = ‘PRESIDENT’;
SELECT * FROM EMP WHERE COMM IS NULL OR ENAME LIKE ‘A%’;
SELECT * FROM EMP WHERE JOB NOT LIKE ‘M%’;
SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 3000;
SELECT * FROM EMP ORDER BY ENAME;
SELECT * FROM EMP ORDER BY JOB, ENAME;
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP ORDER BY 3;
SELECT ENAME EMPLOYEE FROM EMP ORDER BY EMPLOYEE;
---------------------------------------------------------------------------------------------------
SELECT UPPER (‘asif khan’), LOWER (‘SQL PLUS’), INITCAP (‘ORACLE’) FROM 
DUAL;
SELECT EMPNO, RPAD (ENAME, 20,’.’), LPAD (SAL, 6,’*’) FROM EMP;
SELECT INSTR (‘PAKISTAN’,’A’, 1) FROM DUAL;
SELECT INSTR (‘PAKISTAN’,’A’, 3) FROM DUAL;
SELECT INSTR (‘PAKISTAN’,’A’, -1) FROM DUAL;
SELECT INSTR (‘PAKISTAN’,’A’, -3) FROM DUAL;
SELECT ROUND (125.978, 1) FROM DUAL;
SELECT ROUND (123.978,-1) FROM DUAL;
SELECT ROUND (123.978, 0) FROM DUAL;
SELECT ROUND (123.978) FROM DUAL;
SELECT ROUND (126.978,-2) FROM DUAL;
SELECT ROUND (156.978,-2) FROM DUAL;
SELECT ROUND (149.978,-2) FROM DUAL;
SELECT TO_CHAR (SYSDATE,’DD-MON-YYYY’) FROM DUAL;
SELECT TO_CHAR (TO_DATE (’27-OCT-17’, ’DD-MON-RR’),’YYYY’) FROM DUAL;
SELECT TO_CHAR (TO_DATE (’27-OCT-17’, ’DD-MON-YY’),’YYYY’) FROM DUAL;
SELECT TO_CHAR (15000, ‘$99,999.9’) FROM DUAL;
SELECT TO_CHAR (50, ‘$99, 9999.9’) FROM DUAL; 
SELECT 3000 + TO_NUMBER (‘5000’) FROM DUAL;
SELECT TO_CHAR (SYSDATE, ‘YYYY YEAR’) FROM DUAL;
SELECT TO_CHAR (SYSDATE, ‘DY DAY’) FROM DUAL;
SELECT EMPNO, DEPTNO, DNAME FROM EMP, DEPT 
WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO, DEPT.DEPTNO, JOB, DNAME FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB = ‘MANAGER’;
SELECT E.EMPNO, E.ENAME, S.GRADE, D.DNAME FROM EMP E, DEPT D, 
SALGRADE S WHERE E.DEPTNO=D.DEPTNO AND E.SAL BETWEEN S.LOSAL 
AND S.HISAL;
SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E, DEPT D 
WHERE E.DEPTNO (+) =D.DEPTNO;
SELECT WORKER.ENAME||’WORK FOR’||MANAGER.ENAME FROM EMP 
WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO;
SELECT WORKER.ENAME||’WORK FOR’||MANAGER.ENAME FROM EMP 
WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO (+);
SELECT COUNT (COMM) FROM EMP;
SELECT JOB, MAX (SAL) FROM EMP GROUP BY JOB;
Q.1. Find the All Employees Who Are Earning More than Mr. JONES
SELECT * FROM EMP 
WHERE SAL> (SELECT SAL FROM EMP
WHERE ENAME = ‘JONES’);
Q2. Display All Employees Which Are Working In a Same Department Of Mr. 
SMITH
SELECT * FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP
WHERE ENAME = ‘SMITH’);
Q3. DISPLAY ALL EMPLOYEES WHO WERE HIRED AFTER MR.FORD WAS 
HIRED.
SELECT * FROM EMP
WHERE HIREDATE > (SELECT HIREDATE FROM EMP
WHERE ENAME = ‘FORD’)
Q4. DISPLAY ALL EMPLOYEES WHO ARE EARNING MORE THAN THE SALARY 
OF THAT EMPLOYEE WHICH HAS HIGHEST SALARY IN DEPARTMENT 30.
SELECT * FROM EMP
WHERE SAL > (SELECT MAX (SAL) FROM EMP
WHERE DEPTNO = 30);
=====================================================================
Questions
1. Which is the subset of SQL commands used to manipulate Oracle Database 
structures, including tables?
2. What operator performs pattern matching?
3. What operator tests column for the absence of data?
4. Which command executes the contents of a specified file?
5. What is the parameter substitution symbol used with INSERT INTO 
command?
6. Which command displays the SQL command in the SQL buffer, and then 
executes it?
7. What are the wildcards used for pattern matching?
8. State true or false. EXISTS, SOME, ANY are operators in SQL.
True
9. State true or false. !=, <>, ^= all denote the same operation.
10. What are the privileges that can be granted on a table by a user to others?
11. What command is used to get back the privileges offered by the GRANT 
command?
12. Which system tables contain information on privileges granted and privileges 
obtained?
13. Which system table contains information on constraints on all the tables 
created?
15. What is the difference between TRUNCATE and DELETE commands?
16. What command is used to create a table by copying the structure of another 
table?
17. What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', 
'**'),'*','TROUBLE') FROM DUAL;
18. What will be the output of the following query?
SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );
19. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;
20. Which date function is used to find the difference between two dates?