1. Display any one employee name from each JOB group 2. Write a query to display ename from EMP table. MILLER to be displayed in first row and rest in ascending order select ename from emp where ename ='MILLER' union select ename from emp where ename=any(select ename from emp where ename<>'MILLER')order by ename 3. Display ENAME, Manager Name for employees who has EMPNO as odd number; Display ENAME, Manager’s Manager Name for all the employees who has EMPNO as even number. If Manager’s Manager is not present display Manager Name and display employee name if no manager is present for that employee. select m.ename , e.ename manager from emp e join emp m on(e.empno=m.mgr)where mod(m.empno,2)=1 union select m.ename, e.ename manager from emp e join emp m on(e.empno=m.mgr) where mod(m.empno,2)=0 union select ename, 'no manager' from emp where mgr is null 4. Display details of employees having net pay greater than any other employee’s salary select * from emp where (sal+nvl(comm,0))> any(select sal from emp ) 5. Write a query to display three columns and one row (three columns: 10, 20, 30 and row should consist of total salary in that department) select * from (select job, sal, deptno from emp) pivot (select sum(sal) for deptno 10,20,30)) order by job; 6. Write a query to display addr_id that is present in three tables and respective latest addr_dt. Address1 (addr_id, addr_dt), Address2 (addr_id, addr_dt), Address3(addr_id, addr_dt) create table address1(addr_id number(10), addr_dt varchar2(20)) create table address2(addr_id number(10), addr_dt varchar2(20)) create table address3(addr_id number(10), addr_dt varchar2(20)) desc table address1 select * from address1 select * from address2 select * from address3 select addr_id, addr_date from address1 union select addr_id, addr_date from address2 union select addr_id, addr_date from address3 7. Below is the organization chart based on table EMP. Write a query to display NAME, TOTAL_SALARY for each MANAGER directly reporting to KING. (TOTAL_SALARY is sum of salaries drawn by employees reporting to him including his salary) 8. Write a query on source to get required output (column headers are shown in bold to differentiate with data) Source Table: JOB_CONTROL TABLE_NAME PARAMETER_NAME PARAMETER_VALUE EMP DEPTNO 20 EMP JOB SALESMAN EMP START_DATE 01-JAN-1981 EMP END_DATE 12-DEC-1990 Expected Output: DEPTNO JOB START_DATE END_DATE 20 SALESMAN 1-Jan-81 12-Dec-90 9. Write a query to display below required output Source Table: EMP_LOCATION NAME LOCATION SMITH NY;CA;IL;GA JAMES MO;AZ;RI FORD NJ SCOTT TX;VA;WA;NH MARTIN MD;CT Required Output: NAME LOCATION FORD NJ JAMES AZ JAMES MO JAMES RI MARTIN CT MARTIN MD SCOTT NH SCOTT TX SCOTT VA SCOTT WA SMITH CA SMITH GA SMITH IL SMITH NY 10. Write a query to display below required output Source table: EMP_LOCATION Required Output NAME LOCATION NAME LOCATION FORD NJ FORD NJ SMITH NY JAMES AZ;MO;RI JAMES MO MARTIN CT;MD SMITH GA SCOTT NH;TX;VA;WA MARTIN MD SMITH CA;GA;IL;NY SCOTT NH JAMES RI SCOTT VA JAMES AZ SCOTT WA MARTIN CT SMITH CA SCOTT TX SMITH IL 11. Display the contents from table EMP as per parameters provided in table JOB_CONTROL. As per current parameter values we need to display employees belonging to deptno 30, with JOB as SALESMAN and hiredate should be between 01-jan-1982 and 31-dec-1995. Do not hard code any values Table: JOB_CONTROL TABLE_NAME PARAMETER_NAME PARAMETER_VALUE EMP DEPTNO 30 EMP JOB SALESMAN EMP FROM_DATE 1-Jan-82 EMP TO_DATE 31-Dec-95 12. There are two tables 1) DEPT_SRC, 2) DEPT_TGT. Write a query to display records from DEPT_SRC along with additional column STATUS. This column should contain either ‘I’ or ‘U’ where ‘I’ -> Insert, ‘U’ -> Update. If a record is not present in DEPT_TGT then status to be displayed as ‘I’, If a record is present and data is different from DEPT_SRC then status to be displayed as ‘U’, if a record is present and data is matching – do not display that record. (Use Deptno to check record existence in DEPT_TGT) DEPT_SRC DEPT_TGT DEPTNO DNAME LOC DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW JERSEY 20 RESEARCH DALLAS 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 40 OPERATIONS BOSTON 13. Write a query to display LOG_KEY, AMOUNT, LOAD_DATE as per given rules Source Table: MSG_LOG LOG_KEY MSG_ID SYSTEM_MESSAGE 1000322 101 Refunded: 79.99; Reason: Missing features; ELOD: Yes. 1000326 101 Refunded: 266.88; Reason: Do not need (have competitor); ELOD: Yes. 1000554 102 Order NQ46887 placed for 79.88 1000351 103 Sale price manual modification for Order NQ7465 from 49.99 to 15.0. Rule1: To populate AMOUNT column - Populate with value between ‘Refunded:’ and ‘;’ for MSG_ID 101 - Populate with value after ‘placed for’ for MSG_ID 102 - Extract value between ‘from’ and ‘to’ for MSG_ID 103 Rule2: Populate LOAD_DATE column with sysdate (no timestamp required – only date is required) EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 12/17/1980 800 20 7499 ALLEN SALESMAN 7698 2/20/1981 1600 300 30 7521 WARD SALESMAN 7698 2/22/1981 1250 500 30 7566 JONES MANAGER 7839 4/2/1981 2975 20 7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 30 7698 BLAKE MANAGER 7839 5/1/1981 2850 30 7782 CLARK MANAGER 7839 6/9/1981 2450 10 7788 SCOTT ANALYST 7566 4/19/1987 3000 20 7839 KING PRESIDENT 11/17/1981 5000 10 7844 TURNER SALESMAN 7698 9/8/1981 1500 0 30 7876 ADAMS CLERK 7788 5/23/1987 1100 20 7900 JAMES CLERK 7698 12/3/1981 950 30 7902 FORD ANALYST 7566 12/3/1981 3000 20 7934 MILLER CLERK 7782 1/23/1982 1300 10 UNIX Questions: 1. Command to search for number of occurrences of a word ‘hello’ as a whole word in file sample.txt ----$grep –w hello sample.txt ?????? ----$grep –n –w hello sample.txt 2. Command to display only 4th col from file student.info in descending order (student.info file has no header, delimiter is semi colon(;), data in file is for columns sno(num), name(char), subject(char), marks(num)) -----$cut -d ';' -f 4 student.info??????????????????? $cut -d ';' -f 4 student.info|sort –r student.info?? $sort –t ‘;’ -k4 student.info?????????????? sort –nk4 student.info 3. Command to change the timestamp of sample.txt file to current timestamp ------$touch -t 201605081145 sample.txt ???? 4. Command to search for files with extension .txt or .dat starting /home/hari except in /home/hari/ITA1 ---$ find /home/hari -path /home/hari/ITA1 -prune -o .\( -name "*.txt" -o -name "*.dat" \) –type f ???????????????????? 5. Assume sample.txt file has no header and has only one column which contains 100000 phone numbers from various places in United States of America, there may be multiple phone numbers present from each area code and also phone numbers are not in order. Please write a command to display only one phone number for each area code. (Area code is first 3 digits from phone number) -------$sort sample.txt | uniq –w3 ??????????????? 6. Command to search for word 'is' in all the files under folder ITA and display only file names ('is' not to be part of other word; do not consider ‘this’ as occurrence) ----$grep -w –r is /home/ITA????????????????? 7. Both the servers ABC and XYZ are on same network with below information. Server1: ABC (username: usera; pwd: abc123) folder structure: /A/B/C/ Files: /A -> a.txt; /A/B -> b.txt; /A/B/C -> c.txt (When you login to server ABC your working directory will be A) Server2: XYZ (username: usery; pwd: xyz123) folder structure: /X/Y/Z/ Files: /X -> x.txt; /X/Y -> y.txt; /X/Y/Z -> z.txt (When you login to server XYZ your working directory will be Y) Please write steps to perform below 3 operations with one time login (without disconnecting and reconnecting) 1) Copy file a.txt to directory Y 2) copy file c.txt to directory X 3) copy file z.txt to directory B 8. Assume that file sample.txt has x lines. Write a command to display output as File sample.txt has x lines --- echo File sample.txt has $wc –l sample.txt If file has 10 lines, x to be replaced with 10 9. What is the purpose and output of command: $ split –d –b1500 hugefile.txt smallfile -----generate 3 files of 1000 lines and 1500 bytes each named smallfileaa smallfileab smallfileac 10. Command to display only first 2 characters from 3rd column from file student.info in descending order of marks (student.info file has no header record, column delimiter is semi colon(;), data in file is for columns sno, name, subject, marks) ------ sort –r –k3 student.info | uniq –w2