IMPORTANT ORACLE QUERIES ----------------------------------------------------1) Display the names of the employees who are working in the company for The past 5 years; SQL>select ename from emp where to_char (sysdate,'YYYY')-to_char (hiredate,'YYYY')>=5; 2) Display the various jobs and total number of employees within each job Group. SQL>select job, count (job) from EMP group by job; 3) Display the depart numbers and max salary for each department. SQL>select deptno, max (sal) from EMP group by deptno; 4) Display the depart numbers with more than three employees in each dept. SQL>select deptno, count (deptno) from EMP group by deptno having Count (*)>3; 5) Display the name of the employee who earns highest salary. SQL>select ename from emp where sal= (select max (sal) from emp); 6) Display the names of the employees who earn highest salary in their respective departments. SQL>select ename, sal, deptno from EMP where sal in (select max (sal) from EMP group by deptno); 7) Display your age in days. SQL>select to_date (sysdate)-to_date ('10-sep-77') from dual Display your age in months. SQL>select months_between (sysdate,'10-sep-77') from dual 9) Display the details of those who do not have any person working under them. SQL>select e.ename from emp, emp e where emp.mgr=e.empno group by e.ename having count (*) =1; 10) Find out the top 5 earners of company? SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>= (SELECT COUNT (DISTINCT SAL) FROM EMP A WHERE A.SAL>=E.SAL) ORDER BY SAL DESC; 11) Display name of those employees who are getting the highest salary?
SQL>select ename from emp where sal= (select max (sal) from emp); 12) Select count of employee in each department where count greater than 3? SQL>select count (*) from emp group by deptno having count (deptno)>3 13) Display dname where at least 3 are working and display only department name? SQL>select distinct d.dname from dept d, emp e where d.deptno=e.deptno and 3>any (select count (deptno) from emp group by deptno) 14) Find out last 5(least) earners of the company? SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>= (SELECT COUNT (DISTINCT SAL) FROM EMP A WHERE A.SAL<=E.SAL) ORDER BY SAL DESC; 15) Display the 10th record of emp table (without using rowid) SQL>SELECT * FROM EMP WHERE ROWNUM<11 MINUS SELECT * FROM EMP WHERE ROWNUM<10 16) Display the 10th record of emp table without using group by and rowid? SQL>SELECT * FROM EMP WHERE ROWNUM<11 MINUS SELECT * FROM EMP WHERE ROWNUM<10 RO WNUM<10 17) Delete the 10th record of emp table. SQL>DELETE FROM EMP WHERE EMPNO= (SELECT EMPNO FROM EMP WHERE ROWNUM<11 MINUS SELECT EMPNO FROM EMP WHERE ROWNUM<10) 18) Display ename, dname even if there are no employees working in a Particular department (use outer join). SQL>select ename, dname from emp, dept where emp.deptno=dept.deptno (+) 19) Display employee name and his manager name. SQL>select p.ename, e.ename from emp e, emp p where e.empno=p.mgr; 20) Display the department name and total number of employees in each department. SQL>select dname, count (ename) from emp, dept where emp.deptno=dept.deptno group by dname; 21) TO DISPLAY 5 TO 7 ROWS FROM A TABLE SQL>select ename from emp Where rowid in (select rowid from emp where rownum<=7 Minus Select rowid from emp where rownum<5)
22) DISPLAY TOP N ROWS FROM TABLE? SQL>SELECT * FROM (SELECT * FROM EMP ORDER BY ENAME DESC) WHERE ROWNUM <10;
SUBQUERIES A query nested within a query is known as sub query. For example, you want to see all the employees whose salary is above average salary. For this you have to first compute the average salary using AVG function and then compare employees salaries with this computed salary. This is possible using subquery. Here the sub query will first compute the average salary and then main query will execute. Select * from emp where sal > (select avg(sal) from emp); Similarly we want to see the name and empno of that employee whose salary is maximum. Select * from emp where sal = (select max(sal) from emp); To see second maximum salary Select max(sal) from emp where sal < (select max(sal) from emp); Similarly to see the Third highest salary. Select max(sal) from emp where sal < (select max(sal) from emp Where sal < (select max(sal) from emp)); We want to see how many employees are there whose salary is above average. Select count(*) from emp where sal > (select max(sal) from emp); We want to see those employees who are working in Hyderabad. Remember emp and dept are joined on deptno and city column is in the dept table. Assuming that wherever the department is located the employee is working in that city. Select * from emp where deptno in (select deptno from dept where city=’HYD’); You can also use subquery in FROM clause of SELECT statement. For example the following query returns the top 5 salaries from employees table. Select sal from (select sal from emp order sal desc) where rownum <= 5; To see the sum salary deptwise you can give the following query. Select sum(sal) from emp group by deptno; Now to see the average total salary deptwise you can give a sub query in FROM clause. select avg(depttotal) from (select sum(sal) as depttotal from emp group by deptno); WITH The above average total salary department wise can also be achieved in 9i using WITH clause given below WITH DEPTOT AS (select sum(sal) as dsal from emp group by deptno) select avg(dsal) from deptot; GROUP BY QUERIES You can group query results on some column values. When you give a SELECT statement without group by clause then all the resultant rows are treated as a single
group. For Example, we want to see the sum salary of all employees dept wise. Then the following query will achieved the result Select deptno,sum(sal) from emp group by deptno; Similarly we want to see the average salary dept wise Select deptno,avg(sal) from emp group by deptno; Similarly we want to see the maximum salary in each department. Select deptno,max(sal) from emp group by deptno; Similarly the minimum salary. Select deptno,min(sal) from emp group by deptno; Now we want to see the number of employees working in each department. Select deptno,count(*) from emp group by deptno; Now we want to see total salary department wise where the dept wise total salary is above 5000. For this you have to use HAVING clause. Remember HAVING clause is used to filter groups and WHERE clause is used to filter rows. You cannot use WHERE clause to filter groups. select deptno,sum(sal) from emp group by deptno having sum(sal) >= 5000; We want to see those departments and the number of employees working in them where the number of employees is more than 2. Select deptno, count(*) from emp group by deptno Having count(*) >=2; Instead of displaying deptno you can also display deptnames by using join conditions. For example we want to see deptname and average salary of them. Select dname,avg(sal) from emp,dept where emp.deptno=dept.deptno group by dname; Similarly to see sum of sal. Select dname,sum(sal) from emp,dept where emp.deptno=dept.deptno group by dname; Now we want to see the cities name and the no of employees working in each city. Remember emp and dept are joined on deptno and city column is in the dept table. Assuming that wherever the department is located the employee is working in that city. Select dept.city,count(empno) from emp,dept where emp.deptno=dept.deptno Group by dept.city; ROLLUP The ROLLUP operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the GROUP BY specification, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values by using it with the SUM function. When used with SUM, ROLLUP generates subtotals from the most detailed level to the grand total. Aggregate functions such as COUNT can be used to produce other kinds of superaggregates. For example, given three expressions (n=3) in the ROLLUP clause of the simple_grouping_clause, the operation results in n+1 = 3+1 = 4 groupings. Rows grouped on the values of the first 'n' expressions are called regular rows, and the others are called superaggregate rows. The following query uses rollup operation to show sales amount product wise and year wise. To see the structure of the sales table refer to appendices. Select prod,year,sum(amt) from sales
group by rollup(prod,year); CUBE The CUBE operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the specification, and returns a single row of summary information for each group. You can use the CUBE operation to produce cross-tabulation values. For example, given three expressions (n=3) in the CUBE clause of the simple_grouping_clause, the operation results in 2n = 23 = 8 groupings. Rows grouped on the values of 'n' expressions are called regular rows, and the rest are called superaggregate rows. The following query uses CUBE operation to show sales amount product wise and year wise. To see the structure of the sales table refer to appendices. Select prod,year,sum(amt) from sales group by CUBE(prod,year);
CASE EXPRESSION CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures. For example the following query uses CASE expression to display Department Names based on deptno. Select empno,ename,sal,CASE deptno when 10 then ‘Accounts’ When 20 then ‘Sales’ When 30 then ‘R&D’ Else “Unknown’ end From emp; The follow Refer to the Salespeople, Customers, and Orders Tables. Write queries for the following : 1. List the Salespeople table. 2. List the name and commission of all salespeople in London. 3. List all customers who were either located in San Jose or had a rating above 200. 4. Find all salespeople that were located in either Barcelona or London. 5. Extract from the Salespeople table, all salespeople with commission ranging from 0.10 to 0.12. 6. Display all salespeople and customers located in London. 7. Write a query that produces all rows from the Customers table for which the salesperson’s number is 1001. 8. Find all customers with NULL values in their city column. 9. Write a query that produces the rating followed by the name of each customer in San Jose. 10. Write a query that will give all orders for more than $1000. 11. Write a query that will give the names and cities of all salespeople in London with a rating <=100, unless the customer are located in Rome. 12. Write a query that will give names and cities of all salespeople in London with a commission above 0.10. 13. Write a query in two ways, that will produce all orders taken on October 3rd or 4th, 1990. 14. Write a query that selects all of the customers serviced by Steve
or Robert. 15. Write a query that will produce all of the customers whose names begin with a letter from A to G. 16. Write a query that selects all orders except those with zeroes or NULLs in the Amount field. 17. Write a query that counts all orders for October 3rd. 18. Write a query that counts the number of different non-NULL city values in the Customers table. 19. Write a query that selects the highest rating in each city. 20. Write a query that selects the first customer, in alphabetical order whose name begins with the letter H.
GCP ETL – Q&A KnowledgeWorks Page 97 of 129 21. Count the number of salespeople currently listing orders in the Orders table. 22. Find the largest order taken by each salesperson. 23. List the largest order taken by each salesperson on each date. 24. Show the names of all customers matched with the salespeople serving them. 25. Find all pairs of customers having the same rating. 26. Display all orders of the salesperson Robert. 27. Find all orders credited to the same salesperson that services Periera. 28. Display all orders that are greater than the average for October 4th. 29. Find all orders attributed to salespeople in London. 30. Display the commissions of all salespeople servicing customers in London. 31. Find salespeople with customers located in their cities. 32. Output only those customers whose ratings are higher than every customer in Rome. 33. Write a query that selects each customer’s smallest order. 34. Write a query that counts the number of salespeople registering orders for each day (if a salesperson has more than one order on a given day, he should be counted only once). 35. Write a query on the orders table that will produce the order number, the salesperson number and the amount of commission, for that order. 36. Write a query on the Customers table that will find the highest rating in each city. The output should be of the form : For the city city , the highest rating is rating 37. Write a query that lists customers in descending order of rating. Output the rating field first, followed by the customer’s name and number. 38. Write a query that totals the orders for each day and places the results in descending order. 39. Write a query that lists each order number followed by the name of the customer who made the order. 40. Write a query that gives the names of both the salesperson and the customer for each order after the order number.
GCP ETL – Q&A KnowledgeWorks Page 98 of 129 41. Write a query that produces all customers serviced by salespeople with a commission above 12%. Output the customer’s name, the salesperson name, and the salesperson’s rate of commission. 42. Write a query that calculates the amount of the salespersons’ commission on each order by a customer with a rating above 100.
43. Write a query that uses a sub-query to obtain all orders for the customer named Harold. 44. Write a query that produces the names and ratings of all customers who have above average orders. 45. Write a query that selects the total amount in orders for each salesperson for who this total is greater than the amount of the largest order in the table. 46. Write a query that uses the EXISTS operator to extract all salespeople who have customers with a rating of 300. Solve this problem using a JOIN also. 47. Write a query that will find all salespeople who have no customers located in their city.
Interview Questions: 1) what is datawarehouse ? Datawarehouse is a subject oriented,integrated,non-volatile,time variant relational database which is designed for query and analysis rather than transaction processing 2) what is fact table and dimension table ? 3) DDL - alter Data definition language-used to create,drop,truncate and alter the tables and columns 4) touch Touch command is used for creating a zero byte file and also for changing the date and timestamp of an existing file 5) Test strategy and test plan Test strategy-prepared by the project manager or the QA lead and it explains which module to be tested and with which method 6) ETL 7) which is important between priority and severity ? Severity is the measure as to what extent the defect is harmful for the application on the other hand Priority is releated as to which defect the tester wants the dev team to fix first. A defect say a spelling error in the application is a cosmetic defect but if that occur on all the screens of the UI then that makes it a high priority so this is an example of High priority and low severity. A system crash is an example oh high severity but suppose that occurs when user click a button and the usage of that too is very low so there is no urgency to fix it now so this is an example of low priority and high severity. 8) Like queries in SQL 9) Rownum queries in SQL 10) Can Webservice be used to log defect in CQ tool? 11) test command in unix
12) Whatis a view?
A view is a filter that provides access to a subset of the table. For example, a view can be defined that accesses only fields A, B, and C of the table. Or maybe a view that accesses only rows from the table where the value of the Company field is "12". You can have a table without having a view. But you cannot have a view without having a table for the view to look at.
13) Difference b/w table and view? table occupies space in the db whereas a view does not 14) Does view holds memory space in db ? no 15) what is unique identifier? Unique identifier in DW is an attribute used to uniquely identify each rows of an entity 16) What is KERNEL ? kernel is the key component of unix OS which interacts with the underlying h/w thro the device drivers built in it. It manages computer memory allocates the computer resources among the users maintains the file system handle errors and interrupts 17) Test command 18) function Ness-Apr 9th ------------1) What is the difference between table,view and MV ? 2) What is surrogate key? 3) Project Architecture 4) Scenario for table to table load 5) Normalization 6) Can OLAP data be dumped into OLTP? 7) How to display duplicate records in a table? SQL> select ENAME,count(*) ENAME,count(*) from emp 2 group by ename 3 having count(*)>1;
8) Emp 10 20 30 40 50 60 70 80
Dept 30 40 60 70
In the above two tables,Dept is the lookup table.Write a query to display the output in Emp table withe null column value replaced with *EUK*
9) A ----10 20 30
B --10,20 20,30
How to get the o/p as in B from A ? 10) How will you replace f1,r from 'f1 race' with a1 and a respectively ? 11) Get the top five salaries in five diff depts of a company. SQL> select * from 2 (select ENAME,DEPTNO,SALARY,rank( ENAME,DEPTNO,SALARY,rank()) over(partition by DEPTNO order by SALARY desc) RANK from emp) 3 where rank<6; 12) How will you display the string 'BANGALORE'S' from DUAL? select 'BANGALORE''S' from dual; 13)how to convert sysdate(22-APR-10) to 22/04/10 ? SQL> select to_char(sysdate,'dd/mm/yy') from dual; TO_CHAR( -------22/04/10 =================================================== 1) how to get the rank of a particular employee from a table based on his salary? select * from (select ENAME,DEPTNO,SALARY,rank() ENAME,DEPTNO,SALARY,rank() over(partition by DEPTNO order by SALARY desc) RANK from emp) where ename= 2) How to find 2nd highest salary? SQL> select max(salary) max(salary) from emp where where salary<(select salary<(select max(salary) from emp); emp);
3) how to find the nth highest salary? select distinct(a.salary) from emp a where &n= (select count(distinct b.salary) from emp b where a.salary<=b.Salary) 4) DECODE and CASE : select ename,deptno,decode(deptn ename,deptno,decode(deptno,1,'A', o,1,'A', 2,'B', 'NEW') DEPT from emp select ename,deptno, (case deptno when 1 then 'A' when 2 then 'B' else 'NEW' end) from emp
5) How to display a number in words ? SQL> select to_char(to_date(25000,'J'),'jsp') as NUM 2 from dual; NUM -------------------twenty-five thousand NOTE: Here the 'j' used as a format in to_date function return the number in julian year. Whereas the 'jsp' used in to_char made the julian year spelled out. i.e. j-julian year and sp-spelling so jsp.
6) how to convert sysdate(22-APR-10) to 22/04/10 ? SQL> select to_char(sysdate,'dd/mm/yy') from dual; TO_CHAR( -------22/04/10 7) how to convert sysdate(22-APR-10) to 22/APR/10? SQL> select to_char(sysdate,'dd/MON/yy') from dual; TO_CHAR(S --------22/APR/10 8) How will you populate code for the emp based on their deptno ? SQL> select * from emp; ENAME DEPTNO SALARY ---------- ---------- ---------RIN 2 6000 SEN 3 3000 RIN 2 6000 JIM 2 RAM 1 5000 RAJ 1 2500 RAJ 2 2555 Ans ---SQL> select ename,deptno,decode(deptno, ename,deptno,decode(deptno,1,'A', 1,'A', 2 2,'B', 3 'Zero') as CODE from emp; ENAME DEPTNO CODE ---------- ---------- ---RIN 2B SEN 3 Zero
RIN JIM RAM RAJ RAJ
2B 2B 1A 1A 2B
9) To find the nth row? SELECT * FROM ( SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 ) WHERE RN = 100; 10) how to delete the duplicates? 11) Display the emp whose sal
How to find out the database name from SQL*PLUS command prompt?
Select * from global_name;
13) Display the salary as ‘N/A’ wherever the salary is NULL select nvl(to_char(sal),'N/A') salary from emp order by sal desc; 14) Display the top n rows from a table Select col from(Select col from table order by col desc) Where rownum” 16) Display 5th row from a table Select * from table where rownum<6 Minus Select * from table where rownum<5; 17) How to delete duplicate records from a table ? SQL> delete from emp 2 where rowid rowid not in (select min(rowid) min(rowid) from emp 3 group by NAME,DEPTNO,SAL,EMPID); 18) iGate 1) Diff bet betwee ween n Delete Delete and and Trun Truncat cate e 2) Table A is is in DB1 and Table Table B is in DB2.Wh DB2.While ile loading loading data data from table table A to table table B, some records has been left out. How will you find out the missed records ? 3) Diff bet betwee ween n Union Union and Joi Join n 4) Diff b/w inne innerr join join and and outer outer joi join n 5) How to find the size of a database ?
