Question. 1 To display the name of all departments.
Answer: SQL>SELECT dname FROM dept;
Question.2 To display employee name, sal, job and deptno for all employees in department 30.
Answer:
SQL>SELECT ename,sal,job,deptno
2 FROM emp
Question.3 To display employee name,job,salary,department number for those employees in department 20 who earn 2000 or more, as well as all employees in department 30.
Answer:
SQL>SELECT ename,job,sal,deptno
2 FROM emp
3 WHERE sal>=2000 AND deptno=20 OR deptno=30;
Question.4 To display employee name, job, salary, department number for those employees in department 20 or 30 who earn 2000 or more.
Answer:
SQL> SELECT ename,job,sal,deptno
2 FROM emp
3 WHERE sal>=2000 AND(deptno=20 OR deptno=30);
Question. 5 To display employee name,job for all clerks and analysts.
Answer:
SQL>SELECT ename, job
2 FROM emp
3 WHERE job IN (‘CLERK’,’ANALYST’ );
Question.6 To display employee number,name, department number for those hired between January 1,1995 and January 1,1996.
Answer:
SQL>SELECT empno,ename,deptno,
2 hiredate FROM emp
3 WHERE hiredate BETWEEN ‘01-JAN-95’
4 AND ‘01-JAN-96’;
Question.7 To display name, job, salary for those whose name begins with letter A.
Answer:
SQL Command SQL> SELECT ename,job,sal
2 FROM emp
3 WHERE ename LIKE ‘A%’;
Question.8 To display employee name, job, salary, department number for employees whose name has I as the second character.
Answer:
SQL Command SQL> SELECT ename,job,sal,deptno
2 FROM emp
3 WHERE ename LIKE ‘_I%’;
Question.9 To display employee name, commission for employees who are not eligible for commission.
Answer: SQL> SELECT ename,comm
2 FROM emp
3 WHERE comm IS NULL;
Question. 10 List the name, salary and PF amount of all the employees (PF iscalculated as 10% of salary)
Answer:
SQL> SELECT ename,sal, sal*.1
2 FROM emp ;
Question.11 To display name and salaries of employees in department 20 in ascending order of salary.
Answer: SQL> SELECT ename, sal, deptno
FROM emp
WHERE deptno=20
ORDER BY SAL;
Question. 12 To display name and salaries of employees in department 20 in ascending order of salary, using the position of column in the syntax.
Answer:
SQL> SELECT ename,sal,deptno
2 FROM emp
3 WHERE deptno=20
4 ORDER BY 2;
Question.13 To display number, name, department number, salary of all employees. Order the result by department number and in descending order of salary.
Answer:
SQL> SELECT empno,ename,deptno,sal
2 FROM emp
3 ORDER BY deptno,sal DESC;
Question.14 Display all data from table dept.
Answer: SQL>SELECT * FROM dept;
Question. 15 To display distinct values returned by either query.
Answer:
SQL>SELECT dname FROM dept UNION 2 SELECT dname FROM dept1;
Question.16 To display all the values returned by both queries.
Answer:
SQL>SELECT dname FROM dept UNION ALL
2 SELECT dname FROM dept1;
Question. 17 To display common values which are present in the values returned by both the queries.
Answer:
SQL>SELECT dname FROM dept INTERSECT
2 SELECT dname FROM dept1;
Question.18 To display values returned by the first query which are not present in the values returned by the second query.
Answer:
SQL>SELECT dname FROM dept MINUS
2 SELECT dname FROM dept1;
Question. 19 To display values returned by the first query which are not present in the values returned by the second query.
Answer:
SQL>SELECT dname FROM dept MINUS
2 SELECT * FROM dept1;
