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
