ORACLE SQL and PL/SQL

Practical
1. Select all information from SALGRADE table. GRADE LOSAL HISAL -----------------------------------1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 HIREDATE ------------17-DEC-80 20-FEB-81 22-FEB-81 02-APR-81 28-SEP-81 01-MAY-81 09-JUN-81 19-APR-87 17-NOV-81 08-SEP-81 23-MAY-87 03-DEC-81 03-DEC-81 23-JAN-82 SAL --------800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300 COMM ---------300 500 1400 DEPTNO -----------20 30 30 20 30 30 10 20 10 30 20 30 20 10

2. Select all information from the EMP table. EMPNO ENAME JOB MGR ------------ -----------------------------7369 SMITH CLERK 7902 7499 ALLEN SALESMAN 7698 7521 WARD SALESMAN 7698 7566 JONES MANAGER 7839 7654 MARTIN SALESMAN 7698 7698 BLAKE MANAGER 7839 7782 CLARK MANAGER 7839 7788 SCOTT ANALYST 7566 7839 KING PRESIDENT 7844 TURNER SALESMAN 7698 7876 ADAMS CLERK 7788 7900 JAMES CLERK 7698 7902 FORD ANALYST 7566 7934 MILLER CLERK 7782 14 rows selected. 3.

List all employees with the EMPNO, ENAME, SAL and DEPTNO columns. EMPNO ENAME SAL DEPTNO ------------ --------------------------------7369 SMITH 800 20 7499 ALLEN 1600 30 7521 WARD 1250 30 7566 JONES 2975 20 7654 MARTIN 1250 30 7698 BLAKE 2850 30 7782 CLARK 2450 10 7788 SCOTT 3000 20 7839 KING 5000 10 7844 TURNER 1500 30 7876 ADAMS 1100 20 7900 JAMES 950 30 7902 FORD 3000 20 7934 MILLER 1300 10 List DEPARTMENT NO and DEPARTMENT NAME from the DEPT table DEPTNO DNAME ------------ -------------------10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS

4.

ORACLE SQL and PL/SQL

5. Display all the different JOB types JOB -----------CLERK MANAGER PRESIDENT ANALYST SALESMAN Display all the different JOB types and the DEPARTMENT NO. JOB DEPTNO ----------------------ANALYST 20 CLERK 10 CLERK 20 CLERK 30 MANAGER 10 MANAGER 20 MANAGER 30 PRESIDENT 10 SALESMAN 30 9 rows selected. Select data as displayed. Who, What and When -----------------------------------------------------------------------------------SMITH HAS HELD THE POSITION OF CLERK IN DEPT 20 SINCE 13-JUN-83 Display NAME, Salary, COMMISSION and Total Remuneration for all employees. NAME Salary COMMISSION Total Remuneration ----------------------------------------------SMITH 800 9600 ALLEN 1600 300 22800 WARD 1250 500 21000 JONES 2975 35700 MARTIN 1250 1400 31800 BLAKE 2850 34200 CLARK 2450 29400 SCOTT 3000 36000 KING 5000 60000 TURNER 1500 0 18000 ADAMS 1100 13200 JAMES 950 11400 FORD 3000 36000 MILLER 1300 15600 14 rows selected. 9. Select all employees who have a salary between 1000 and 2000 from the EMP table. EMPNO ENAME JOB MGR HIREDATE SAL COMM ------------ ------------------------------ --------------------- ---------7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 7876 ADAMS CLERK 7788 23-MAY-87 1100 7934 MILLER CLERK 7782 23-JAN-82 1300 6 rows selected. DEPTNO -----------30 30 30 30 20 10

6.

7.

8.

ORACLE SQL and PL/SQL

10. List department numbers and names in department name order. DEPTNO DNAME ------------ -------------------10 ACCOUNTING 40 OPERATIONS 20 RESEARCH 30 SALES 11. List the details of the employee in departments 10 and 20 in alphabetical order of the name. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------------ ------------------------------ --------------------- --------------------7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7934 MILLER CLERK 7782 23-JAN-82 1300 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 8 rows selected. 12. List names and jobs of all clerks in department 20. ENAME -----------ADAMS SMITH JOB ----------CLERK CLERK

13. List the employee name and salary increased by 15% and expressed as a whole number. DEPTNO ENAME SAL PCTSAL ---------- ---------------------------20 SMITH 800 920 30 ALLEN 1600 1840 30 WARD 1250 1438 20 JONES 2975 3421 30 MARTIN 1250 1438 30 BLAKE 2850 3278 10 CLARK 2450 2818 20 SCOTT 3000 3450 10 KING 5000 5750 30 TURNER 1500 1725 20 ADAMS 1100 1265 30 JAMES 950 1093 20 FORD 3000 3450 10 MILLER 1300 1495 14 rows selected. 14. Display all employees names which have TH or LL in them. ENAME -----------SMITH ALLEN MILLER

ORACLE SQL and PL/SQL

15. Produce the following output. EMPLOYEE_AND_JOB ---------------------------------SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK 14 rows selected. 16. Produce the following output. EMPLOYEE ---------------------SMITH (Clerk) ALLEN (Salesman) WARD (Salesman) JONES (Manager) MARTIN (Salesman) BLAKE (Manager) CLARK (Manager) SCOTT (Analyst) KING (President) TURNER (Salesman) ADAMS (Clerk) JAMES (Clerk) FORD (Analyst) MILLER (Clerk) 14 rows selected. 17. It has been discovered that the sales people in department 30 are not all male. Produce following output ENAME DEPTNO JOB NEW_JOB ------------------- -------------------ALLEN 30 SALESMAN Salesperson WARD 30 SALESMAN Salesperson MARTIN 30 SALESMAN Salesperson BLAKE 30 MANAGER Manager TURNER 30 SALESMAN Salesperson JAMES 30 CLERK Clerk 6 rows selected. 18. Display each employees name and hire date from dept. 20. ENAME DATE_HIRED --------------------------------------SMITH December, Seventeenth 1980 JONES April, Second 1981 SCOTT April, Nineteenth 1987 ADAMS May, Twenty-Third 1987 FORD December, Third 1981

ORACLE SQL and PL/SQL

19. Display the date of the next Friday that is six months from the hire date. The resulting date should appear as Friday, March 12th, 1982. Order the results by hire date. 20. Display each employee name with hire date and salary review date. Assume review date is one year after hire date. Order the output in ascending review date order. ENAME HIREDATE REVIEW -------------------------SMITH 17-DEC-80 17-DEC-81 ALLEN 20-FEB-81 20-FEB-82 WARD 22-FEB-81 22-FEB-82 JONES 02-APR-81 02-APR-82 BLAKE 01-MAY-81 01-MAY-82 CLARK 09-JUN-81 09-JUN-82 TURNER 08-SEP-81 08-SEP-82 MARTIN 28-SEP-81 28-SEP-82 KING 17-NOV-81 17-NOV-82 JAMES 03-DEC-81 03-DEC-82 FORD 03-DEC-81 03-DEC-82 MILLER 23-JAN-82 23-JAN-83 SCOTT 19-APR-87 19-APR-88 ADAMS 23-MAY-87 23-MAY-88 14 rows selected. 21. List all the employees who have a manager. EMPNO ENAME JOB ------------ ---------------------7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN 7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7782 CLARK MANAGER 7788 SCOTT ANALYST 7844 TURNER SALESMAN 7876 ADAMS CLERK 7900 JAMES CLERK 7902 FORD ANALYST 7934 MILLER CLERK 13 rows selected. 22. Display all employees who were hired during 1981. EMPNO ENAME HIREDATE SAL ------------ -------------------------------7499 ALLEN 20-FEB-81 1600 7521 WARD 22-FEB-81 1250 7566 JONES 02-APR-81 2975 7654 MARTIN 28-SEP-81 1250 7698 BLAKE 01-MAY-81 2850 7782 CLARK 09-JUN-81 2450 7839 KING 17-NOV-81 5000 7844 TURNER 08-SEP-81 1500 7900 JAMES 03-DEC-81 950 7902 FORD 03-DEC-81 3000 10 rows selected. COMM ---------300 500 1400 DEPTNO -----------30 30 20 30 30 10 10 30 30 20 SAL --------800 1600 1250 2975 1250 2850 2450 3000 1500 1100 950 3000 1300

ORACLE SQL and PL/SQL

23. Find the minimum salary of all employees. MINIMUM ---------800 24. Find the minimum, maximum and average salaries of all employees. MIN(SAL) MAX(SAL) AVG(SAL) ---------------------------800 5000 2073.21429 25. List the minimum and maximum salary for each job types. JOB MINIMUM MAXIMUM --------------------------ANALYST 3000 3000 CLERK 800 1300 MANAGER 2450 2975 PRESIDENT 5000 5000 SALESMAN 1250 1600 26. Find out how many managers there are without listing them. MANAGERS ---------3 27. Find the average salary and average total remuneration for each job type. Remember SALESMAN earn commission. JOB AVSAL AVCOMP --------------------------ANALYST 3000 36000 CLERK 1037.5 12450 MANAGER 2758.33333 33100 PRESIDENT 5000 60000 SALESMAN 1400 23400 28. List lowest paid employees working for each managers. Exclude any groups where the minimum salary is less than 1000. Sort the output by salary. MGR MIN(SAL) ------------------7788 1100 7782 1300 7839 2450 7566 3000 5000 29. Find all departments which have more than three employees. DEPTNO COUNT(*) ------------------20 5 30 6 30. Find out the difference between highest and lowest salaries. DIFFERENCE ---------4200 31. Display the department that has no employees. DEPTNO DNAME ---------- --------------

ORACLE SQL and PL/SQL

40 OPERATIONS 32. Check whether all employees numbers are indeed unique. 33. List all employees by name and number along with their managers name and number. EMPNO ENAME MGRNO MGR_NAME ---------- ------------------- ---------7369 SMITH 7902 FORD 7499 ALLEN 7698 BLAKE 7521 WARD 7698 BLAKE 7566 JONES 7839 KING 7654 MARTIN 7698 BLAKE 7698 BLAKE 7839 KING 7782 CLARK 7839 KING 7788 SCOTT 7566 JONES 7844 TURNER 7698 BLAKE 7876 ADAMS 7788 SCOTT 7900 JAMES 7698 BLAKE 7902 FORD 7566 JONES 7934 MILLER 7782 CLARK 13 rows selected. 34. Modify solution to question 2 to display KING who has no manager. EMPNO ENAME MGRNO MGR_NAME ---------- ------------------- ---------7369 SMITH 7902 FORD 7499 ALLEN 7698 BLAKE 7521 WARD 7698 BLAKE 7566 JONES 7839 KING 7654 MARTIN 7698 BLAKE 7698 BLAKE 7839 KING 7782 CLARK 7839 KING 7788 SCOTT 7566 JONES 7839 KING 7844 TURNER 7698 BLAKE 7876 ADAMS 7788 SCOTT 7900 JAMES 7698 BLAKE 7902 FORD 7566 JONES 7934 MILLER 7782 CLARK 14 rows selected. 35. Find the most recently hired employees in each department. Order by hiredate. DEPTNO ENAME HIREDATE ---------- -----------------30 JAMES 03-DEC-81 10 MILLER 23-JAN-82 20 ADAMS 23-MAY-87 36. Find all employees who joined the company before their managers. EMPLOYEE HIREDATE MANAGER HIREDATE ----------------------------------SMITH 17-DEC-80 FORD 03-DEC-81 ALLEN 20-FEB-81 BLAKE 01-MAY-81 WARD 22-FEB-81 BLAKE 01-MAY-81 JONES 02-APR-81 KING 17-NOV-81 BLAKE 01-MAY-81 KING 17-NOV-81 CLARK 09-JUN-81 KING 17-NOV-81

ORACLE SQL and PL/SQL

6 rows selected. 37. Find the employee(s) who earn the highest salary in each job type. Sort in descending salary order. JOB ENAME SAL --------------------------PRESIDENT KING 5000 ANALYST SCOTT 3000 ANALYST FORD 3000 MANAGER JONES 2975 SALESMAN ALLEN 1600 CLERK MILLER 1300 6 rows selected. 38. Find the employees who earn the minimum salary for their job. Display the result in ascending order of salary. ENAME JOB SAL --------------------------SMITH CLERK 800 WARD SALESMAN 1250 MARTIN SALESMAN 1250 CLARK MANAGER 2450 SCOTT ANALYST 3000 FORD ANALYST 3000 KING PRESIDENT 5000 7 rows selected. 39. Show the following details for any employee who earns a salary greater than the average for their department. Sort in department number order. ENAME SALARY DEPTNO ---------------------------KING 5000 10 JONES 2975 20 SCOTT 3000 20 FORD 3000 20 ALLEN 1600 30 BLAKE 2850 30 6 rows selected. 40. List all the departments where there are no employees. (Using a sub-query ) DEPTNO DNAME ---------- -------------40 OPERATIONS 41. Display the following information for the department with the Highest annual remuneration. DEPTNO COMPENSATION --------------------30 139200 42. Modify question 4 to display the average salary for the departments. ENAME SALARY DEPTNO DEPT_AVG ------------------------------------ALLEN 1600 30 1566.66667 BLAKE 2850 30 1566.66667 JONES 2975 20 2175 FORD 3000 20 2175 SCOTT 3000 20 2175

ORACLE SQL and PL/SQL

KING 5000 10 2916.66667

6 rows selected. 43. Who are the top 3 earners in the company? Display their name and salary. ENAME SAL ------------------SCOTT 3000 KING 5000 FORD 3000 44. In which year did most people join the company? Display the year and number of employees. YEAR NUMBER_OF_EMPLOYEES ---------------------1981 10 45. Write a query to display an * against the row of the most recently hired employee. Display ename, hiredate and column maxdate. Display the result in hiredate order. ENAME HIREDATE M -----------------SMITH 17-DEC-80 ALLEN 20-FEB-81 WARD 22-FEB-81 JONES 02-APR-81 BLAKE 01-MAY-81 CLARK 09-JUN-81 TURNER 08-SEP-81 MARTIN 28-SEP-81 KING 17-NOV-81 FORD 03-DEC-81 JAMES 03-DEC-81 MILLER 23-JAN-82 SCOTT 19-APR-87 ADAMS 23-MAY-87 * 14 rows selected. 46. Create a table called projects with columns as specified below. In addition define projid as the PRIMARY KEY column and ensure that p_end_date dates are not earlier than p_start_date dates. Column Name Data Type Size -----------------------------------------------------PROJID NUMBER 4 P_DESC VARCHAR2 20 P_START_DATE DATE P_END_DATE DATE BUDGET_AMOUNT NUMBER 7, 2 MAX_NO_STAFF NUMBER 2 47. Create a second table assignments as shown below. Define its projid column as a FOREIGN KEY which references the project table. Your tables empno column is further FOREIGN KEY to emp. These two columns (projid and empno) should not allow NULL values. Column Name Data Type Size -----------------------------------------------------PROJID NUMBER 4 EMPNO NUMBER 4 A_START_DATE DATE A_END_DATE DATE BILL_RATE NUMBER 4, 2 ASSIGN_TYPE VARCHAR2 2

ORACLE SQL and PL/SQL

48. Add a LONG column called comments to your project table. Also add a NUMBER column called hours to the assignments table. 49. Use the USER_OBJECTS dictionary view to list the objects owned by your Oracle account. How many objects do you own? 50. Define a constraint on the assignments table to ensure unique combination of projid and employee number. 51. Insert into the projects table the following rows. PROJID 1 P_DESC WRITE C030 COURSE P_START_DATE 02-JAN-88 P_END_DATE 07-JAN-88 BUDGET_AMOUNT 500 MAX_NO_STAFF 1 52. Insert into the assignments table the following rows. PROJID 1 1 EMPNO 7369 7902 A_START_DATE 01-JAN-88 04-JAN-88 A_END_DATE 03-JAN-88 07-JAN-88 BILL_RATE 50.00 55.00 ASSIGN_TYPE WR WR HOURS 15 20 2 PROOF READ NOTES 01-JAN-89 10-JAN-89 600 1 2 7844 01-JAN-89 10-JAN-89 45.50 PF 30

53. Update assignment type to read WT instead of WR. A type of PF should be left unchanged. 54. Insert two more projects with assignments of your own choice. 55. Define a View which will produce the following output when referenced in a query. DEPTNO AVERAGE MAXIMUM MINIMUM SUM NO_SALS ------------------------------------------------------10 2916.66667 5000 1300 8750 3 20 2175 3000 800 10875 5 30 1566.66667 2850 950 9400 6 NO_COMMS ---------0 0 4

56. Using the View from question 1, extract the following information. Employee number should be entered at run-time. Enter value for empno: 7902 old 1: select emp.empno, ename, job, sal, hiredate, minimum, maximum, average from emp, aggre where emp.deptno = aggre.deptno and emp.empno = &empno new 1: select emp.empno, ename, job, sal, hiredate, minimum, maximum, average from emp, aggre where emp.deptno = aggre.deptno and emp.empno = 7902 EMPNO ---------7902 ENAME ---------FORD JOB --------ANALYST SAL --------3000 HIREDATE --------03-DEC-81 MINIMUM ---------800 MAXIMUM ---------3000 AVERAGE ---------2175

57. Create a View to enforce the following restrictions when Inserting data into the assignments table. a. Projid must be less than 2000. b. End date must be after Start date. c. Valid assign_types are PF, WT, ED. d. Bill_rate is less than 50.00 from assign_type Pf, less than 60.00 fro assign_type WT, less than 70.00 for assign_type ED. e. Empno must be valid. f. Remember WITH CHECK OPTION clause. 58. Insert minimum 3 records satisfying all the assign_type into assignments table using the View created.

10

ORACLE SQL and PL/SQL

59. Query the Data Dictionary to see your view. 60. Remove the created view and check whether the based table is affected or not. 61. Create a Non Unique Index on the projid column of the assignments table. 62. Query the appropriate Data Dictionary table to display information about your Indexes. 63. Drop the Index. 64. Create a Sequence for the empno column of emp table. 65. Change the increment value to 1 in the created Sequence. 66. Use the created Sequence to Insert the values for the empno. 67. Drop the Sequence. 68. Create the Synonym.

11