Monday 9 May 2011

Oracle interview based Queries- 1

 

It is pre-assumed that one has Emp,Dept tables in their Database.All one needs to do is to copy and paste the following queries and execute them.

1) Display the details of all employees

SQL>Select * from emp;

2) Display the depart information from department table

SQL>select * from dept;

3) Display the name and job for all the employees

SQL>select ename,job from emp;

4) Display the name and salary for all the employees

SQL>select ename,sal from emp;

5) Display the employee no and totalsalary for all the employees

SQL>select empno,ename,sal,comm, sal+nvl(comm,0) as"total salary" from

emp

6) Display the employee name and annual salary for all employees.

SQL>select ename, 12*(sal+nvl(comm,0)) as "annual Sal" from emp

7) Display the names of all the employees who are working in depart number 10.

SQL>select emame from emp where deptno=10;

8) Display the names of all the employees who are working as clerks and drawing a salary more than 3000.

SQL>select ename from emp where job='CLERK' and sal>3000;

9) Display the employee number and name who are earning comm.

SQL>select empno,ename from emp where comm is not null;

10) Display the employee number and name who do not earn any comm.

SQL>select empno,ename from emp where comm is null;

11) Display the names of employees who are working as clerks,salesman or analyst and drawing a salary more than 3000.

SQL>select ename from emp where job='CLERK' OR JOB='SALESMAN'

OR JOB='ANALYST' AND SAL>3000;

12) 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;

13) Display the list of employees who have joined the company before

30-JUN-90 or after 31-DEC-90.

a)select ename from emp where hiredate < '30-JUN-1990' or hiredate >

'31-DEC-90';

14) Display current Date.

SQL>select sysdate from dual;

15) Display the list of all users in your database(use catalog table).

SQL>select username from all_users;

16) Display the names of all tables from current user;

SQL>select tname from tab;

17) Display the name of the current user.

SQL>show user

18) Display the names of employees working in depart number 10 or 20 or 40 or employees working as CLERKS,SALESMAN or ANALYST.

SQL>select ename from emp where deptno in(10,20,40) or job

in('CLERKS','SALESMAN','ANALYST');

19) Display the names of employees whose name starts with alaphabet S.

SQL>select ename from emp where ename like 'S%';

20) Display the Employee names for employees whose name ends with alaphabet S.

SQL>select ename from emp where ename like '%S';

21) Display the names of employees whose names have second alphabet A in their names.

SQL>select ename from emp where ename like '_A%';

22) select the names of the employee whose names is exactly five characters in length.

SQL>select ename from emp where length(ename)=5;

23) Display the names of the employee who are not working as MANAGERS.

SQL>select ename from emp where job not in('MANAGER');

24) Display the names of the employee who are not working as SALESMAN OR CLERK OR ANALYST.

SQL>select ename from emp where job not in ('SALESMAN','CLERK','ANALYST');

25) Display all rows from emp table.The system should wait after every

screen full of informaction.

SQL>set pause on

26) Display the total number of employee working in the company.

SQL>select count(*) from emp;

27) Display the total salary beiging paid to all employees.

SQL>select sum(sal) from emp;

28) Display the maximum salary from emp table.

SQL>select max(sal) from emp;

29) Display the minimum salary from emp table.

SQL>select min(sal) from emp;

30) Display the average salary from emp table.

SQL>select avg(sal) from emp;

31) Display the maximum salary being paid to CLERK.

SQL>select max(sal) from emp where job='CLERK';

32) Display the maximum salary being paid to depart number 20.

SQL>select max(sal) from emp where deptno=20;

33) Display the minimum salary being paid to any SALESMAN.

SQL>select min(sal) from emp where job='SALESMAN';

34) Display the average salary drawn by MANAGERS.

SQL>select avg(sal) from emp where job='MANAGER';

35) Display the total salary drawn by ANALYST working in depart number 40.

SQL>select sum(sal) from emp where job='ANALYST' and deptno=40;

36) Display the names of the employee in order of salary i.e the name of the employee earning lowest salary should salary appear first.

SQL>select ename from emp order by sal;

37) Display the names of the employee in descending order of salary.

a)select ename from emp order by sal desc;

38) Display the names of the employee in order of employee name.

a)select ename from emp order by ename;

39) Display empno,ename,deptno,sal sort the output first base on name and within name by deptno and with in deptno by sal.

SQL>select empno,ename,deptno,sal from emp order by

40) Display the name of the employee along with their annual salary(sal*12).The name of the employee earning highest annual salary should apper first.

SQL>select ename,sal*12 from emp order by sal desc;

41) Display name,salary,hra,pf,da,total salary for each employee. The output should be in the order of total salary,hra 15% of salary,da 10% of salary,pf 5% salary,total salary will be(salary+hra+da)-pf.

SQL>select ename,sal,sal/100*15 as hra,sal/100*5 as pf,sal/100*10 as

da, sal+sal/100*15+sal/100*10-sal/100*5 as total from emp;

42) Display depart numbers and total number of employees working in each department.

SQL>select deptno,count(deptno)from emp group by deptno;

43) Display the various jobs and total number of employees within each job group.

SQL>select job,count(job)from emp group by job;

44) Display the depart numbers and total salary for each department.

SQL>select deptno,sum(sal) from emp group by deptno;

45) Display the depart numbers and max salary for each department.

SQL>select deptno,max(sal) from emp group by deptno;

46) Display the various jobs and total salary for each job

SQL>select job,sum(sal) from emp group by job;

47) Display the various jobs and total salary for each job

SQL>select job,min(sal) from emp group by job;

48) 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;

49) Display the various jobs along with total salary for each of the jobs

where total salary is greater than 40000.

SQL>select job,sum(sal) from emp group by job having sum(sal)>40000;

50) Display the various jobs along with total number of employees in each job.The output should contain only those jobs with more than three employees.

SQL>select job,count(empno) from emp group by job having count(job)>3

100 comments :

  1. change background color or text color to white

    ReplyDelete
  2. Anonymous6:11 pm

    hybernet is a framework Tool which helps in Functional and Regression testing of an application. If you are interested in hybernet training, our real time working.
    Hibernate Training in Chennai,

    ReplyDelete
  3. Anonymous6:13 pm

    Nice site.... refer this site .if Our vision succes!Training are focused on perfect improvement of technical skills for Freshers and working professional. Our Training classes are sure to help the trainee with Realtime methodologies.
    Oracle Rac Training Chennai
    haddoop:

    ReplyDelete
  4. give details about table so by that we can understand how queries work

    ReplyDelete
  5. Solve This ........

    We define an employee's total earnings to be their monthly worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as space-separated integers.

    ReplyDelete
  6. Please post the table columns as well.thanks..what is PF etc ???

    ReplyDelete
  7. Thank you so much for the informative article share with us Oracle Jobs

    ReplyDelete
  8. I read your blog it's really good, thanks for sharing valuable information with us.
    Excellent Article, Nice to read your article, very informative.
    Excellent article Top AC mechanics Professional Camera sellers ChennaiCatering Service in ChennaiTop Educational Institute Chennai

    Excellent Article
    swimmingpool contractor chennai
    swimmingpool equipment dealer chennai
    swimmingpool consultant chennai

    ReplyDelete
  9. Thanks For Sharing Such an Great Info.

    67500/12

    ReplyDelete
  10. Very Useful Interview Questions For Preparing good 1
    This Blog is very helpful and useful,came to know that i should be strong in my basics and this blog helps me to improve it,Chicago Immediate Care Services Provided by Us.Thanks For Posting.I Am refereed by my friend to this blog and i also want to refer my other friends to this blog.

    ReplyDelete
  11. I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post.is article.
    python training institute in chennai
    python training in velachery
    python training institute in chennai


    ReplyDelete
  12. Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point.

    Blueprism online training

    Blue Prism Training in Pune

    ReplyDelete
  13. It seems you are so busy in last month. The detail you shared about your work and it is really impressive that's why i am waiting for your post because i get the new ideas over here and you really write so well.
    Data Science course in rajaji nagar | Data Science with Python course in chenni
    Data Science course in electronic city | Data Science course in USA
    Data science course in pune | Data science course in kalyan nagar

    ReplyDelete
  14. Hello I am so delighted I found your blog, I really found you by mistake, while I was looking on Yahoo for something else, anyways I am here now and would just like to say thanks for a tremendous post. Please do keep up the great work.

    java training in chennai | java training in bangalore

    java online training | java training in pune

    ReplyDelete
  15. Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
    Application Packagining Training
    Python Training

    ReplyDelete
  16. Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point.
    SAP GRC Classes

    SAP Hana admin Classes

    ReplyDelete
  17. Outstanding blog post, I have marked your site so ideally I’ll see much more on this subject in the foreseeable future.
    angularjs-Training in sholinganallur

    angularjs-Training in velachery

    angularjs Training in bangalore

    angularjs Training in bangalore

    angularjs Training in btm

    ReplyDelete
  18. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
    Sql Server Developer Online Training

    SAP PM Online Training

    SAP Hybris Online Training

    SAP Fiori & ui5 Online Training

    ReplyDelete
  19. thanks for sharing check out Vijay Devarakonda Wiki thanks forr sharing such an lovely info....



    ReplyDelete
  20. I am really enjoying reading your well written articles.
    It looks like you spend a lot of effort and time on your blog.
    I have bookmarked it and I am looking forward to reading new articles. Keep up the good work..
    hadoop training in bangalore
    big data training in bangalore
    Hadoop Training in Chennai
    Big Data Training in Chennai

    ReplyDelete
  21. Display an (*) in front of recently hired employee hire date and also rest of all the employees should be displayed

    ReplyDelete
  22. A universal message I suppose, not giving up is the formula for success I think. Some things take longer than others to accomplish, so people must understand that they should have their eyes on the goal, and that should keep them motivated to see it out til the end.

    devops online training

    aws online training

    data science with python online training

    data science online training

    rpa online training

    ReplyDelete
  23. Enterprise Support telephone number is successfully delivering the whole world class technical assistance for QuickBooks Enterprise Tech Support Number at comfort of your house. We understand your growing business need

    ReplyDelete
  24. QuickBooks has completely transformed just how people used to perform their business earlier. In order to get familiar with it, you should welcome this positive change. Supervisors at QuickBooks Support Phone Number have trained all their executives to combat the problems in this software.

    ReplyDelete
  25. As QuickBooks Customer Service Premier has various industry versions such as retail, manufacturing & wholesale, general contractor, general business, Non-profit & Professional Services,

    ReplyDelete
  26. To save some time and money in resolving the situation technically, try not to think twice to offer us a call at Choose QuickBooks Enterprise Phone Support.

    ReplyDelete
  27. Every user will get 24/7 support services with this online technical experts using QuickBooks support phone number. When you’re stuck in times for which you can’t discover a way to get rid of a concern, all that's necessary would be to dial Quickbooks Support
    . Be patient; they will certainly inevitably and instantly solve your queries.

    ReplyDelete
  28. For that actual reason, dig recommends that you simply solely dial the authentic QuickBooks Support sign anytime you want any facilitate and your QuickBooks. Our QuickBooks specialists will assist you remotely over a network.

    ReplyDelete
  29. You could face problem in reconciling QuickBooks Technical Support Number dollars, there might be issues while you try to reconcile your charge cards, you will find problem inside the settings for the report and so on and so forth.

    ReplyDelete
  30. QuickBooks Payroll Service Number is present 24/7. You can actually call them anytime. The experts are thrilled to aid.

    ReplyDelete
  31. Each one of these issues mentioned above are a couple of types of what kind of tech glitches users may face. QuickBooks Enterprise help is the only solution when it comes to selection of issues. So, contact with our QuickBooks support team with the QuickBooks Enterprise customer support number to enjoy all the latest plans and services made available from us globally. Dial our QuickBooks Enterprise Tech Support Number to get an immediate QuickBooks help.

    ReplyDelete
  32. checks or client reports. We plan to give you the immediate support by our well- masterly technicians. A team of Intuit QuickBook Support dedicated professionals is invariably accessible in your case so as to arranged all of your problems in an attempt that you’ll be able to perform your work whilst not hampering the productivity.

    ReplyDelete
  33. Top features of QuickBooks Payroll Technical Support Number would be the main elements of customer attraction. These features are crucial in deciding how good the payroll service is within QuickBooks. Let’s check out the primary options that come with QuickBooks Payroll.

    ReplyDelete
  34. We've been here to improve your understanding with regards to the payroll updates happens in QuickBooks Payroll Support Phone Number, desktop, pro, premier 2019 versions. Solve your queries related to QuickBooks Online Payroll whether Enhanced or Full Service.

    ReplyDelete
  35. Our research team at QuickBooks Tech Support Phone Number is dependable for most other reasons as well. We now have customer care executives which are exceptionally supportive and pay complete awareness of the demand of technical assistance made by QuickBooks users. Our research team is often prepared beforehand because of the the best option solutions that are of good help and less time intensive. Their pre-preparedness helps them extend their hundred percent support to all the entrepreneurs in addition to individual users of QuickBooks.

    ReplyDelete
  36. QuickBooks Tech Support Number professionals are terribly dedicated and could solve your whole issues with no fuss. If you call, you may be greeted by our client service representative when taking all of your concern he/she will transfer your preference in to the involved department.

    ReplyDelete
  37. the ability to track over 100,000 customers and vendors, and add over 30 users to the company data file, QB Enterprise is predictably a little complicated to use. Our QuickBooks Enterprise Tech Support Phone Number Help Desk is fully equipped to guide you through the data backup option to keep a record of all of your graphics, data files and invoices.

    ReplyDelete
  38. Hope now you recognize that how to relate with QuickBooks Enterprise Help Phone Number USA. We've been independent alternative party support company for intuit QuickBooks, we do not have almost any link with direct QuickBooks, the employment of name Images and logos on website only for reference purposes only.

    ReplyDelete
  39. you’ll scan in-depth articles concerning most of the errors and also how you can resolve them. Rectifying errors desires in-depth details about the system and its particular intricacies. Our internet site may be a go-to supply for everything associated with QuickBook Tech Support Phone Number.

    ReplyDelete
  40. side. In the event that you hire our service, you are receiving the very best solution. We're going to assure you as a result of the error-free service. QuickBooks Support Number is internationally recognized. You have to started to used to understand this help.

    ReplyDelete
  41. And also with this, many QuickBooks Tech Support Number users encounter some heavy and unexpected errors such as QuickBooks error 3371 and many other. So this kind of sort of tough situations,

    ReplyDelete
  42. If you’re looking for small-business accounting solutions, initial thing experts and happy costumers will recommend you is QuickBooks by Intuit Inc. Intuit’s products for construction contractors through the Quickbooks Pro, Simple Start Plus Pack, Quickbooks Premier Contractor, and QuickBooks Customer Support Number .

    ReplyDelete
  43. As well as with this, many QuickBooks users encounter some heavy and unexpected errors such as QuickBooks error 3371 and so many more. So this kind of sort of tough situations, you are able to rely on on-to the best QuickBooks Tech Support Number you need to subscribe so that you can use our all amazing support services whenever you face error or any type of issue.

    ReplyDelete
  44. We are going to provide full support to you personally. It is possible to deal with a lot of the errors. We need to just coach QuickBooks Technical Support Number on something. Thoughts is broken trained, you are getting everything fine.

    ReplyDelete
  45. Quickbooks Customer support serving a number of users daily , quite possible you will definitely hand up or need to watch for long time in order to connect with all the Help Desk team . According to statics released by the Bing & Google search insights a lot more than 50,000 folks searching the net to find the QuickBooks Payroll Support Phone Number on a regular basis and more than 2,000 quarries pertaining to Quickbooks issues and errors .

    ReplyDelete
  46. Our QuickBooks Support telephone number channel- We comprehend the complexity and need using this accounting software in day to day life. You can’t watch out for more or less time for it to get a fix of each and every single QB error. Therefore we have designed a especially dedicated team of certified professionals at QuickBooks Support contact number that are able to understanding your issues and errors in minimum time as well as in probably the most convenient way. Dial our QuickBooks Phone Number and avail the top solution you will need.

    ReplyDelete
  47. In scenarios where in actuality the HP Printer Support Number laptop plugged in not charging windows 10, fix it up by running a hardware test and diagnose the problem which persists the product.

    ReplyDelete
  48. As QuickBooks Payroll Support Number we utilize the responsibility of resolving all of the problems that hinder the performance of this exuberant software.

    ReplyDelete
  49. Yes, with the peerless assistance for QuickBooks Enterprise software, all of us can perform performing full clean up, inventory management, report management to meet your needs. The bottom line is, we could manage your entire QuickBooks Enterprise Support Phone Number accounting for your requirements.

    ReplyDelete
  50. The support team at our end is quite helpful in terms of handling the issues that pop up in QuickBooks Payroll Support Phone Number or any of its versions. With regards to problems, every one us understand that these are typically inevitable.

    ReplyDelete

  51. QuickBooks Support Phone Number is accounting software, which can be a cloud-based application manufactured by Inuit Inc. In fact, the software has been developed utilizing the intention of keeping a safe record of financial needs regarding the business.

    ReplyDelete

  52. You are able to choose for any QuickBooks Tech Support Number versions looking on the desires. QuickBooks are often generally split into 2 categories: QuickBooks online version and QuickBooks Desktop version.

    ReplyDelete
  53. QuickBooks Payroll Support Number as well provides all possible assist with the users to make use of it optimally. An individual who keeps experience of experts has the ability to realize in regards to the latest updates.

    ReplyDelete
  54. The world wide web is stuffed with faux numbers WHO decision themselves the QuickBooks Tech Support Number Provider. you’ll value more highly to dial their variety however that could be terribly risky. you’ll lose your QuickBooks Company file or the code itself. dig recommends dialing solely the authentic QuickBooks Support contact number.

    ReplyDelete
  55. You can easily choose for any QuickBooks Payroll Support Phone Number versions looking on your desires. QuickBooks are often generally divided in to 2 categories: QuickBooks online version and QuickBooks Desktop version.

    ReplyDelete
  56. Hi Mate, this is what I exactly was looking for. The post provided in your blog is simple and outstanding. I can correlate the post with my requirement. It’s one of the most amazing posts I have come across. Thanks for sharing it. Have a stress free life, while working on QuickBooks. In case you face any issue while working on this accounting software, feel free to call us at QuickBooks Desktop Support Phone Number at 1-833-441-8848 for immediate help.

    ReplyDelete
  57. Thank you for sharing such an incredible post. I am impressed by your writing style. QuickBooks Accounting Solution is the best software to manage your time and money and increase the productivity of your business. Reach the QuickBooks Payroll Support Phone Number 24 x7 hours +1-844-200-2627 for any queries related to this accounting software. We are available round-the-clock to help our clients.

    ReplyDelete
  58. Hey! Excellent Post. Thanks for sharing such an informative blog. I have read your other blogs, and I love them. QuickBooks Payroll is a demanding version of QuickBooks that streamlines your payroll activities. However, you can face some issues in the software. To solve any error, call on QuickBooks Payroll Support Phone Number 1 (877) 282-6222. Visit us: https://qbtechnicalsupportphone.com/quickbooks-payroll-support-phone-number/

    ReplyDelete
  59. Experts available at QuickBooks Desktop Support Phone Number +1 (877) 282-6222, remove the issues emerging while performing bookkeeping on QuickBooks. Surely, you would be well aware that how mind-blowing is this accounting program. Visit us: https://www.enetservepartners.com/quickbooks-desktop-support/

    ReplyDelete
  60. Hey! I liked your blog. It has a high-quality content and supporting statement for the topic. For a business like yours, you can use QuickBooks software for managing your accounting activities. In case you discover any inconvenience in your software, dial QuickBooks Payroll Tech Support Number 1-833-401-0204 and get the paramount level of customer satisfaction at your door. Read more: https://www.mildaccounting.com/quickbooks-payroll-tech-support-number/

    ReplyDelete
  61. Hi! Superb blog. Thanks for writing for such informative content. If you want a robust accounting software for managing your business accounts, then go for QuickBooks. To get support for the software, dial QuickBooks Phone Number 1-833-401-0204 and experience world-class technical assistance at your door. Read more: https://tinyurl.com/y44kbf5u or visit: https://www.techiesupportnumber.com/quickbooks-phone-number/

    ReplyDelete
  62. Here we list some definitions for the words contained in your error, in an attempt to help you understand your problem. This is a work in progress, so sometimes we might define the word incorrectly, so feel free to skip this section! If you would like to learn How To Fix Quickbooks Error 9999, you can continue reading this blog.

    ReplyDelete
  63. Got stuck in between while estimating Payroll? Don’t fret, Call QuickBooks Customer Service Number 833-780-0086. Our experts at QuickBooks Payroll support offer reliable assistance to the users. For More Visit: http://bit.ly/2V6Sgk7

    ReplyDelete
  64. it is an amazing blog to explore more

    BEST ANGULAR JS TRAINING IN CHENNAI WITH PLACEMENT

    https://www.acte.in/angular-js-training-in-chennai
    https://www.acte.in/angular-js-training-in-annanagar
    https://www.acte.in/angular-js-training-in-omr
    https://www.acte.in/angular-js-training-in-porur
    https://www.acte.in/angular-js-training-in-tambaram
    https://www.acte.in/angular-js-training-in-velachery


    ReplyDelete
  65. Very informative!
    Facing QuickBooks error 1603? if you hang up with QuickBooks error 1603 Get live support 24*7 from QuickBooks expert on tool-free Number.
    Click here to know How to fix QuickBooks error 1603
    Dial for any tech support: 1-844-908-0801

    ReplyDelete
  66. This comment has been removed by the author.

    ReplyDelete
  67. Very Nice Article if you are Quickbooks Lover to improve your business then you can get benefit of team at
    quickbooks customer service +1 267-773-4333

    ReplyDelete
  68. Very Good Blog !! If You Are using Quickbooks Software For mange your Business but sometimes You May Face many problems during Installation but Quickbooks also Offer best QuickBooks Customer Service team and call at +18556753194

    ReplyDelete
  69. Thanks For Sharing Wonderful Information If you need help with your business, the best thing to do is contact a QuickBooks expert at
    quickbooks customer service+17735165910

    ReplyDelete
  70. This comment has been removed by the author.

    ReplyDelete
  71. Very Good Blog !! I Quickbooks is an accounting software that help businesses manage their finances and payments. If you need help, the QuickBooks customer service +1 347-982-0046 team is here for help you.

    ReplyDelete
  72. Excellent post. Thanks for sharing such great and helpful info with us. Keep it up. I appreciate this informative post. Want to know about QuickBooks Visit here.
    QuickBooks enterprises support!

    ReplyDelete
  73. If you are looking for online help about QuickBooks then you need to need to dial QuickBooks customer service +1 877-755-5046 that number for your queries and errors issues.

    ReplyDelete
  74. This comment has been removed by the author.

    ReplyDelete
  75. Great Blog! In Case when you are Facing some Unexpected technical Issue then get the best technical solutions for QuickBooks queries. call us on Quickbooks customer service +1 347-982-0046

    ReplyDelete
  76. QuickBooks also offers Quickbooks customer service If you need help with your QuickBooks account, call the +1 855-604-1500 customer service number above.

    ReplyDelete
  77. QuickBooks is amazing software is best choice of your business if you are looking growth of their company call us on
    QuickBooks customer service +1 855-377-7767

    ReplyDelete
  78. Wow Great Content ! By dialing
    QuickBooks customer service +18556753194 you can reach our team at QuickBooks customer service who will be able to provide assistance with any question or issue that you may have.

    ReplyDelete
  79. QuickBooks- Accounting software is very popular accounting . ! . If you want to get in touch with your QuickBooks Expert then Dial QuickBooks customer service +1 602-362-8345

    ReplyDelete