Oracle Sql - Questions and Answers ;
1. Determine the Structure of
DEPT Table and its Contents.
DESC DEPT; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)
2. Determine the Structure of EMP Table and its Contents.
DESC EMP; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
3. Display the Ename and Deptno from Emp table whose Empno is 7788.
SELECT ENAME, DEPTNO FROM EMP WHERE EMPNO=7788; ENAME DEPTNO ---------- ---------- SCOTT 20
4. Display the Ename, Sal, Comm from Emp table who earn commission ans
sort the records in descending order of Salary and Comm. Use column's
numeric position in the ORDER BY Clause.
SELECT ENAME, SAL, COMM FROM EMP WHERE COMM >=0 ORDER BY 2 DESC, 3 DESC; ENAME SAL COMM ---------- ---------- ---------- ALLEN 1900 300 TURNER 1500 0 MARTIN 1250 1400 WARD 1250 500
5. The HR department needs a query to display all unique job codes from the
EMP table.
SELECT DISTINCT JOB FROM EMP; JOB --------- CLERK SALESMAN PRESIDENT MANAGER ANALYST
6. The HR department wants more description column headings for its report
on employees. Name the column headings EMp#, Employee, Job, Hire Date
respectively by giving Column Alias.
SELECT EMPNO AS EMP#, ENAME AS EMPLOYEE, JOB AS JOB, HIREDATE AS HIREDATE FROM EMP; EMP# EMPLOYEE JOB HIREDATE ---------- ---------- --------- --------- 7839 KING PRESIDENT 17-NOV-81 7698 BLAKE MANAGER 01-MAY-81 7782 CLARK MANAGER 09-JUN-81 7566 JONES MANAGER 02-APR-81 7788 SCOTT ANALYST 09-DEC-82 7902 FORD ANALYST 03-DEC-81 7369 SMITH CLERK 17-DEC-80 7499 ALLEN SALESMAN 20-FEB-81 7521 WARD SALESMAN 22-FEB-81 7654 MARTIN SALESMAN 28-SEP-81 7844 TURNER SALESMAN 08-SEP-81 7876 ADAMS CLERK 12-JAN-83 7900 JAMES CLERK 03-DEC-81 7934 MILLER CLERK 23-JAN-82
7. The HR department has requested a report of all employees and their job
id's. Display the last name concatenated with the job id (separated by a
comma and space) and name the column Employee and Title by giving
Column Alias.
SELECT ENAME||', '||JOB "EMPLOYEE AND TITLE" FROM EMP; EMPLOYEE AND TITLE --------------------- KING, PRESIDENT BLAKE, MANAGER CLARK, MANAGER JONES, MANAGER SCOTT, ANALYST FORD, ANALYST SMITH, CLERK ALLEN, SALESMAN WARD, SALESMAN MARTIN, SALESMAN TURNER, SALESMAN ADAMS, CLERK JAMES, CLERK MILLER, CLERK
8. To familiarize yourself with the data in the EMP table, create a query to
display all the data from that table . Separate each column output by a
comma. ENAME, JOB, HIREDATE,MGR. Name the column title "THE_OUTPUT".
SELECT ENAME||', '||JOB||', '||HIREDATE||', '||MGR "THE_OUTPUT" FROM EMP; THE_OUTPUT ----------------------------------------------------------------------------------- KING, PRESIDENT, 17-NOV-81, BLAKE, MANAGER, 01-MAY-81, 7839 CLARK, MANAGER, 09-JUN-81, 7839 JONES, MANAGER, 02-APR-81, 7839 SCOTT, ANALYST, 09-DEC-82, 7566 FORD, ANALYST, 03-DEC-81, 7566 SMITH, CLERK, 17-DEC-80, 7902 ALLEN, SALESMAN, 20-FEB-81, 7698 WARD, SALESMAN, 22-FEB-81, 7698 MARTIN, SALESMAN, 28-SEP-81, 7698 TURNER, SALESMAN, 08-SEP-81, 7698 ADAMS, CLERK, 12-JAN-83, 7788 JAMES, CLERK, 03-DEC-81, 7698 MILLER, CLERK, 23-JAN-82, 7782
9. Create a report to display the ename, job, hiredate for the employees name is SCOTT or TURNER. Order the query in ascending order by hiredate.
SELECT ENAME,JOB,HIREDATE FROM EMP WHERE ENAME='SCOTT' OR ENAME='TURNER' ORDER BY HIREDATE; ENAME JOB HIREDATE ---------- --------- --------- TURNER SALESMAN 08-SEP-81 SCOTT ANALYST 09-DEC-82
10. Display the ename and department number of all employees in departments 20 or 30 in ascending order by ename.
SELECT ENAME,DEPTNO FROM EMP WHERE DEPTNO=20 OR DEPTNO=30 ORDER BY ENAME; ENAME DEPTNO ---------- ---------- ADAMS 20 ALLEN 30 BLAKE 30 FORD 20 JAMES 30 JONES 20 MARTIN 30 SCOTT 20 SMITH 20 TURNER 30 WARD 30
11. Modify the previous query to display the last name and salary of employees who earn between 2000 and 3000 and are in department 20 or 30. Label the columns Employee and Monthly Salary, respectively giving Column Alias.
SELECT ENAME AS EMPLOYEE, SAL "MONTHLY SALARY" FROM EMP WHERE (SAL BETWEEN 2000 AND 3000) AND (DEPTNO=20 OR DEPTNO=30) ORDER BY ENAME; EMPLOYEE MONTHLY SALARY ---------- -------------- BLAKE 3000 FORD 3000 JONES 2975 SCOTT 3000
12. The HR department needs a report that displays the last name and
hiredate for all employees who were hired in 1981.
SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE LIKE '%81'; ENAME HIREDATE ---------- --------- KING 17-NOV-81 BLAKE 01-MAY-81 CLARK 09-JUN-81 JONES 02-APR-81 FORD 03-DEC-81 ALLEN 20-FEB-81 WARD 22-FEB-81 MARTIN 28-SEP-81 TURNER 08-SEP-81 JAMES 03-DEC-81
13. Display the Ename, Sal of employees who earn more than an amount the
user specifies after a prompt.
SELECT ENAME, SAL FROM EMP WHERE SAL>&SALARY; Enter value for salary: 2000 old 1: SELECT ENAME, SAL FROM EMP WHERE SAL>&SALARY new 1: SELECT ENAME, SAL FROM EMP WHERE SAL>2000 ENAME SAL ---------- ---------- KING 5500 BLAKE 3000 CLARK 2450 JONES 2975 SCOTT 3000 FORD 3000 ESWAR 12000
14. Create a report to display the last name and job title of all employees
who do not have a manager.
SELECT ENAME, JOB FROM EMP WHERE MGR IS NULL; ENAME JOB ---------- --------- KING PRESIDENT
15. Create a query that prompts the user for Manager ID and generate EMPNO,
ENAME, SAL, DEPTNO. The user should have the ability to sort the records on
a selected column.
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE MGR=&MANAGER_ID ORDER BY &COLUMNNUMBER; Enter value for manager_id: 7839 Enter value for columnnumber: 1 old 1: SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE MGR=&MANAGER_ID ORDER BY &COLUMNNUMBER new 1: SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE MGR=7839 ORDER BY 1 EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 7566 JONES 2975 20 7698 BLAKE 3000 30 7782 CLARK 2450 10