Oracle Database SQL - Practise - Question - Scott Schema Examples
Oracle SQL Practice Questions for Students
All Question are Based on Scott Schema
Exhibits EMP TABLE
Exhibits DEPT TABLE
Exhibits SALGRADE TABLE
Question
1) Display all the records in emp table?
2) Display all the records in emp table where employee belongs to deptno 10?
3) Display all the records in emp table where employee does not belong to deptno 30?
4) Display total number of records in Emp table?
5) Display emp table with salary descending order?
6) Display first five records in employee table?
7) Display all the records in emp table order by ascending deptno, descending salary?
8) Display all employees those who were joined in year 1981?
9) Display COMM in emp table. Display zero in place of null.
10) Display the records in emp table where MGR in 7698,7566 and sal should be greater then 1500
2) Display all the records in emp table where employee belongs to deptno 10?
3) Display all the records in emp table where employee does not belong to deptno 30?
4) Display total number of records in Emp table?
5) Display emp table with salary descending order?
6) Display first five records in employee table?
7) Display all the records in emp table order by ascending deptno, descending salary?
8) Display all employees those who were joined in year 1981?
9) Display COMM in emp table. Display zero in place of null.
10) Display the records in emp table where MGR in 7698,7566 and sal should be greater then 1500
11) Display all employees where employees hired before
01-JAN-1981
12) Display all employees with how many years they have been servicing in the company?
13) Display all employees those were not joined in 1981?
14) Display all employees where their hiredate belongs to third quarter?
15) Display all employees where their salary is less then the Ford’s salary?
16) Display all the records in EMP table along with the rowid?
17) Display all records in EMP table those were joined before SCOTT joined?
18) Display all employees those who were joined in third quarter of 1981?
19) Add 3 months with hiredate in EMP table and display the result?
20) Display the date for next TUESDAY in hiredate column?
21) Find the date, 15 days after today’s date.
22) Write a query to display current date?
23) Display distinct job from emp table?
24) Display all the records in emp table where employee hired after 28-SEP-81 and before 03-DEC-81?
25) Write a query that displays the employee’s names with the first letter capitalized and all other letters lowercase for all employees whose name starts with J, A, or M
26) Display all jobs that are in department 10. Include the location of department in the output.
27) Write a query to display the employee name, department name of all employees who earn a commission
28) Display the empno, ename, sal, and salary increased by 15%.
29) Display ename, sal, grade. Use emp, salgrade table
30) Display all employees and corresponding managers
31) Display all the departments where employee salary greater then average salary of that department.
32) Display all employees whose salary greater then the manager salary?
33) Display employees where length of ename is 5
34) Display all employees where ename start with J and ends with S
35) Display all employees where employee does not belong to 10,20,40
36) Display all employees where jobs does not belong to PRESIDENT and MANAGER?
37) Display the maximum salary in the emp table
38) Display average salary for job SALESMAN
39) Display all three figures salary in emp table
40) Display all records in emp table for employee who does not receive any commission
41) Display all ename where first character could be anything, but second character should be L?
42) Display nth highest and nth lowest salary in emp table?
43) Display all the departments where department has 3 employees?
44) Display emp name and corresponding subordinates. Use CONNECT BY clause.
45) Display sum of salary for each department. The output should be in one record
46) Display all department with Minimum salary and maximum salary?
47) Display all ename, sal, deptno,dname from emp, dept table where all department which has employees as well as department does not have any employees. This query should include non matching rows.
48) Display all ename, sal, deptno from emp, dept table where all employees which has matching department as well as employee does not have any departments. This query should include non matching rows.
Note: In the below query, employee will always have matching record in dept table. Emp, dept table may not be good example to answer this question.
49) Display all ename, sal, deptno from emp, dept table where all employees which has matching and non matching department as well as all departments in dept table which has matching and non matching employees. This query should include non matching rows on both the tables.
Note: In the below query, employee will always have matching record in dept table. Emp, dept table may not be good example to answer this question.
50) Display all ename, empno, dname, loc from emp, dept table without joining two tables
51) Display all the departments where department does not have any employees
52) Display all the departments where department does have atleast one employee
53) Display all employees those who are not managers?
54) Display ename, deptno from emp table with format of {ename} belongs to {deptno}
55) Display total number of employees hired for 1980,1981,1982. The output should be in one record.
56) Display ename, deptno from employee table. Also add another column in the same query and it should display ten for dept 10, twenty for dept 20, thirty for dept 30, fourty for dept 40
57) Display all the records in emp table. The ename should be lower case. The job first character should be upper case and rest of the character in job field should be lower case.
58) Display all employees those who have joined in first week of the month ?
59) Display all empoyees those who have joined in the 49th week of the year?
60) Display empno, deptno, salary, salary difference between current record and previous record in emp table. Deptno should be in descending order.
61) Create table emp1 and copy the emp table for deptno 10 while creating the table
62) Create table emp2 with same structure of emp table. Do not copy the data
63) Insert new record in emp1 table, Merge the emp1 table on emp table.
64) Display all the records for deptno which belongs to employee name JAMES?
65) Display all the records in emp table where salary should be less then or equal to ADAMS salary?
66) Display all employees those were joined before employee WARD joined?
67) Display all subordinate those who are working under BLAKE?
68) Display all subordinate(all levels) for employee BLAKE?
69) Display all record in emp table for deptno which belongs to KING's Job?
70) Display the employees for empno which belongs to job PRESIDENT?
71) Display list of ename those who have joined in Year 81 as MANAGER?
72) Display who is making highest commission?
73) Display who is senior most employee? How many years has been working?
74) Display who is most experienced and least experienced employee?
75) Display ename, sal, grade, dname, loc for each employee.
76) Display all employee whose location is DALLAS?
77) Display ename, job, dname, deptno for each employee by using INLINE view?
78) List ename, job, sal and department of all employees whose salary is not within the salary grade?
79) Use EMP and EMP1 table. Query should have only three columns. Display empno,ename,sal from both tables inluding duplicates.
80) Delete emp table for detpno 10 and 20.
81) Delete all employees those are not getting any commission?
82) Delete all employees those who employeed more then 28 years
83) Add duplicate records in emp1 table. Delete the duplicate records in emp1 table.
84) Delete the employees where employee salary greater then average salary of department salary?
85) Delete all employees those who are reporting to BLAKE?
86) Delete all levels of employees those who are under BLAKE?
12) Display all employees with how many years they have been servicing in the company?
13) Display all employees those were not joined in 1981?
14) Display all employees where their hiredate belongs to third quarter?
15) Display all employees where their salary is less then the Ford’s salary?
16) Display all the records in EMP table along with the rowid?
17) Display all records in EMP table those were joined before SCOTT joined?
18) Display all employees those who were joined in third quarter of 1981?
19) Add 3 months with hiredate in EMP table and display the result?
20) Display the date for next TUESDAY in hiredate column?
21) Find the date, 15 days after today’s date.
22) Write a query to display current date?
23) Display distinct job from emp table?
24) Display all the records in emp table where employee hired after 28-SEP-81 and before 03-DEC-81?
25) Write a query that displays the employee’s names with the first letter capitalized and all other letters lowercase for all employees whose name starts with J, A, or M
26) Display all jobs that are in department 10. Include the location of department in the output.
27) Write a query to display the employee name, department name of all employees who earn a commission
28) Display the empno, ename, sal, and salary increased by 15%.
29) Display ename, sal, grade. Use emp, salgrade table
30) Display all employees and corresponding managers
31) Display all the departments where employee salary greater then average salary of that department.
32) Display all employees whose salary greater then the manager salary?
33) Display employees where length of ename is 5
34) Display all employees where ename start with J and ends with S
35) Display all employees where employee does not belong to 10,20,40
36) Display all employees where jobs does not belong to PRESIDENT and MANAGER?
37) Display the maximum salary in the emp table
38) Display average salary for job SALESMAN
39) Display all three figures salary in emp table
40) Display all records in emp table for employee who does not receive any commission
41) Display all ename where first character could be anything, but second character should be L?
42) Display nth highest and nth lowest salary in emp table?
43) Display all the departments where department has 3 employees?
44) Display emp name and corresponding subordinates. Use CONNECT BY clause.
45) Display sum of salary for each department. The output should be in one record
46) Display all department with Minimum salary and maximum salary?
47) Display all ename, sal, deptno,dname from emp, dept table where all department which has employees as well as department does not have any employees. This query should include non matching rows.
48) Display all ename, sal, deptno from emp, dept table where all employees which has matching department as well as employee does not have any departments. This query should include non matching rows.
Note: In the below query, employee will always have matching record in dept table. Emp, dept table may not be good example to answer this question.
49) Display all ename, sal, deptno from emp, dept table where all employees which has matching and non matching department as well as all departments in dept table which has matching and non matching employees. This query should include non matching rows on both the tables.
Note: In the below query, employee will always have matching record in dept table. Emp, dept table may not be good example to answer this question.
50) Display all ename, empno, dname, loc from emp, dept table without joining two tables
51) Display all the departments where department does not have any employees
52) Display all the departments where department does have atleast one employee
53) Display all employees those who are not managers?
54) Display ename, deptno from emp table with format of {ename} belongs to {deptno}
55) Display total number of employees hired for 1980,1981,1982. The output should be in one record.
56) Display ename, deptno from employee table. Also add another column in the same query and it should display ten for dept 10, twenty for dept 20, thirty for dept 30, fourty for dept 40
57) Display all the records in emp table. The ename should be lower case. The job first character should be upper case and rest of the character in job field should be lower case.
58) Display all employees those who have joined in first week of the month ?
59) Display all empoyees those who have joined in the 49th week of the year?
60) Display empno, deptno, salary, salary difference between current record and previous record in emp table. Deptno should be in descending order.
61) Create table emp1 and copy the emp table for deptno 10 while creating the table
62) Create table emp2 with same structure of emp table. Do not copy the data
63) Insert new record in emp1 table, Merge the emp1 table on emp table.
64) Display all the records for deptno which belongs to employee name JAMES?
65) Display all the records in emp table where salary should be less then or equal to ADAMS salary?
66) Display all employees those were joined before employee WARD joined?
67) Display all subordinate those who are working under BLAKE?
68) Display all subordinate(all levels) for employee BLAKE?
69) Display all record in emp table for deptno which belongs to KING's Job?
70) Display the employees for empno which belongs to job PRESIDENT?
71) Display list of ename those who have joined in Year 81 as MANAGER?
72) Display who is making highest commission?
73) Display who is senior most employee? How many years has been working?
74) Display who is most experienced and least experienced employee?
75) Display ename, sal, grade, dname, loc for each employee.
76) Display all employee whose location is DALLAS?
77) Display ename, job, dname, deptno for each employee by using INLINE view?
78) List ename, job, sal and department of all employees whose salary is not within the salary grade?
79) Use EMP and EMP1 table. Query should have only three columns. Display empno,ename,sal from both tables inluding duplicates.
80) Delete emp table for detpno 10 and 20.
81) Delete all employees those are not getting any commission?
82) Delete all employees those who employeed more then 28 years
83) Add duplicate records in emp1 table. Delete the duplicate records in emp1 table.
84) Delete the employees where employee salary greater then average salary of department salary?
85) Delete all employees those who are reporting to BLAKE?
86) Delete all levels of employees those who are under BLAKE?
87)
Delete all employees those who are only managers?
88) Remove the department in dept table where dept does not have any employees?
89) Remove all grade 2 employees in emp table?
90) Remove all the employees in SMITH's department
91) Remove least paid employee who are reporting to BLAKE ?
92) Remove all employees
who were joined before SMITH joined? 88) Remove the department in dept table where dept does not have any employees?
89) Remove all grade 2 employees in emp table?
90) Remove all the employees in SMITH's department
91) Remove least paid employee who are reporting to BLAKE ?
93) Rename the employee name JONES to ANDY
94) Change the WARD's hiredate to one day ahead
95) Update MARTIN salary same as SMITH's salary
96) Increase the salary 5% for employee those who are earning commission less then 1000
97) Increase 250$ commission for BLAKE's team
98) Increase 100$ for employee who is making more then averge salary of his department?
99) Increase 1% salary for employee who is making lowest salary in dept 10
100) Reduce the commission amount from employee salary for each employee who were joined after ALLEN joined.
101) Increase commission 10$ for employees those who are located in NEW YORK.
For any help in solving the above queries you can contact me on Google+ or comment below.
Join SQL Training Courses at Unisoft Technologies Nagpur
www.unisoftindia.org
nice
ReplyDeleteThank you so much for providing information on this. It was very useful.
ReplyDeleteAviation Academy in Chennai
Air hostess training in Chennai
Airport management courses in Chennai
Ground staff training in Chennai
best aviation academy in Chennai
air hostess institute in Chennai
Airline Courses in Chennai
airport ground staff training in Chennai
Great Post. It was so informative and are you looking for the best home elevators in India. Click here: Home lift in Bangalore | home lift India
ReplyDeleteCan i get the solution
ReplyDeletehttp://myorastuff.blogspot.com/2009/09/oracle-sql-questions.html
Deletecan i get the solution
ReplyDeletehttp://myorastuff.blogspot.com/2009/09/oracle-sql-questions.html
DeleteThanks for sharing this great information on Oracle Fusion. Actually I was looking for the same information on internet for Oracle Fusion HCM Interview Questions and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more about Fusion financial by attending Oracle Fusion Financials Training.
ReplyDeleteI was browsing through various sites and blogs and then I came across yours. It was a great blog. Here is a referred link same as yours oracle fusion hcm training. Thanks for sharing this with us. It really helped us to enhance our knowledge.
ReplyDeleteI was very happy to find this site. I really enjoyed reading this article today and think it might be one of the best articles I have read so far. I wanted to thank you for this excellent reading !! I really enjoy every part and have bookmarked you to see the new things you post. Well done for this excellent article. Please keep this work of the same quality.
ReplyDeleteData Science Course in Bangalore
Such a useful blog with needed information and thanks for sharing this amazing blog.
ReplyDeleteAWS Certification in Chennai
DevOps Course in Chennai
ReplyDeleteWonderful Blog!!! Waiting for your upcoming data... thanks for sharing with us.
Software Testing Training in Chennai
Software Testing Online Course
Software Testing Course in Coimbatore
Really wonderful blog! Thanks for taking your valuable time to share this with us. Keep us updated with more such blogs.
ReplyDeleteAWS Certification in Chennai
AWS Online Training
AWS Training in Coimbatore
Your site is truly cool and this is an extraordinary moving article and If it's not too much trouble share more like that. Thank You..
ReplyDeleteDigital Marketing Course in Hyderabad
Only required information’s., Great blog to check out.
ReplyDeleteSelenium Training in Chennai
Software Testing Training in Chennai
Thank a lot. You have done excellent job. I enjoyed your blog . Nice efforts
ReplyDeleteData Science Certification in Hyderabad
I need to thank you for this very good read and i have bookmarked to check out new things from your post. Thank you very much for sharing such a useful article and will definitely saved and revisit your site.
ReplyDeleteData Science Course
adanaescort01.com - adiyamanescortxx.com - afyonarackiralama.net - aksarayescort.net - antalyaoyunpark.com - aydinescortkiz.com - balikesirescortlar.com - batmanescortlar.com - bitlisescortlar.com - burdurescortlar.com - bursamalaysias.com - diyarbakirambar.com - edirnedespor.com - erzurumyolkosusu.com - eskisehirescortlari.com - gaziantepekspres.org - gebzeescortkiz.com - giresunmaraton.com - hataykoleji.com - ispartakpss.com - karabukteknik.com - kastamonuajans.net - kayserivalisi.com - kilisescort.com - kocaeliescortlar.com - konyaescortlar.com - kutahyaizemlak.com - malatyadataksi.com - manisaescortlar.com - marasatasoyemlak.com - mardinfanatik.com - mersinmoda.com - muglaapart.net - nigdeyapi.com - orduescortt.com - osmaniyeyorum.com - sakaryanur.com - samsunescortlar.com - siirteyatirim.com - sincanoto.com - tekirdagescortlar.com - tokatforum.com - usakbasin.com - vanescortilan.com - yalovadaemlak.com - yozgattanal.com - sanliurfadayim.com - zonguldakescort.com
ReplyDeletecami avizesi - cami avizeleri - avize cami - no deposit bonus forex 2021 - takipçi satın al - takipçi satın al - takipçi satın al - takipcialdim.com/tiktok-takipci-satin-al/ - instagram beğeni satın al - instagram beğeni satın al - btcturk - tiktok izlenme satın al - sms onay - youtube izlenme satın al - no deposit bonus forex 2021 - tiktok jeton hilesi - tiktok beğeni satın al - binance - takipçi satın al - uc satın al - sms onay - sms onay - tiktok takipçi satın al - tiktok beğeni satın al - twitter takipçi satın al - trend topic satın al - youtube abone satın al - instagram beğeni satın al - tiktok beğeni satın al - twitter takipçi satın al - trend topic satın al - youtube abone satın al - takipcialdim.com/instagram-begeni-satin-al/ - tiktok takipçi satın al - tiktok beğeni satın al - twitter takipçi satın al - trend topic satın al - youtube abone satın al - instagram beğeni satın al - perde modelleri - instagram takipçi satın al - takipçi satın al - instagram takipçi satın al - betboo
ReplyDeleteThanks For Your Post, Are You Interested to learn digital marketing training for free, here is a source for you.
ReplyDeletedigital marketing training institute
digital marketing course Training in madhapur
digital marketing Training near me
digital marketing training in vijaywada
digital marketing training in hyderabad
Thanks for sharing this Interesting post with the community!
ReplyDeleteeye hospital in chennai
lasik surgery cost in chennai
cataract surgery cost in chennai
Thanks Your post is so cool and this is an extraordinary moving article and If it's not too much trouble share more like that.
ReplyDeleteDigital Marketing Course in Hyderabad
Your website is really cool with great inspiring articles and thanks for sharing this amazing and educative blog post!
ReplyDeleteCloud Computing Courses in Hyderabad
I like viewing this web page which comprehend the price of delivering the excellent useful resource free of charge and truly adored reading your posting. Thank you!
ReplyDeleteData Science Certification Course
ReplyDeleteNice & Informative Blog ! We offer welcome to yorkie puppies nearme. Check it out!...
puppies for sale near me
yorkie puppies with home training
where to buy Yorkie
Yorkie Female Puppies for sale
Yorkie puppies ready for their forever homes
I am always searching online for articles that can help me and you made some good points in Features also. Keep working, great job
ReplyDeleteData Science Training
Interesting post. which i wondered about this issue so thanks for posting and very good article which is a really very nice and useful article. Thank you
ReplyDeleteData Science Course in Noida
Well done for this excellent article. and really enjoyed reading this article today it might be one of the best articles I have read so far and please keep this work of the same quality.
ReplyDeleteData Analytics Course in Noida
Informative Post. The information you have posted is very useful and sites you have referred was good. Thanks for sharing.
ReplyDeleteData Science Course with Placement
Very great post which I really enjoy reading this and it is not everyday that I have the possibility to see something like this. Thank You.
ReplyDeleteBest Online Data Science Courses
Just a shine from you here and have never expected anything less from you and have not disappointed me at all which i guess you will continue the quality work. Great post.
ReplyDeleteData Science Training in Gurgaon
Very informative message! There is so much information here that can help me thank you for sharing
ReplyDeleteData Analytics Course in Chandigarh
Hydraulic Lift
ReplyDeleteHospital Lift
Goods Lift
Passenger Lift
lift manufacturers in Chennai
lift companies in Chennai
Wow. It is a very trending topic. I read many articles on this topic. But, selecting words in this articles is very good. A visa for Turkey from US is especially for the US Citizen. It is a fast and secure way of visa for USA Citizens.
ReplyDeleteThanks for sharing this valuable information, we also provide instagram video download and,
ReplyDeletereally appreciate your hard work and good research that help us in for of your good valuable article. Keep updating us with your awesome content.
instagram beğeni satın al
ReplyDeleteyurtdışı kargo
seo fiyatları
saç ekimi
dedektör
fantazi iç giyim
sosyal medya yönetimi
farmasi üyelik
mobil ödeme bozdurma
bitcoin nasıl alınır
ReplyDeletetiktok jeton hilesi
youtube abone satın al
gate io güvenilir mi
referans kimliği nedir
tiktok takipçi satın al
bitcoin nasıl alınır
mobil ödeme bozdurma
mobil ödeme bozdurma
Thanks for sharing this great article we appreciate it, we provide instagram reels download freely and unlimited.
ReplyDeleteWow! I have read your article and it's so good I will share it with family and friends. I just informed the travelers that the e visa of Turkey is easy to get through the online process. Travelers who wish to travel to Turkey can apply for it.
ReplyDeleteI'm really interested in this. It should be very good for me. Thank you. บริษัทสร้างบ้านอุดร
ReplyDeleteThis is very interesting, what do other people think? เทคนิคบาคาร่าออนไลน์
ReplyDeleteIn order to gain an in-demand set of skills required for today's job opportunities in Data Science, APTRON offers the best Data Science training course in Noida.
ReplyDeleteConsidered as the best Data Science training in Delhi, APTRON Data Science training is also organized through online platforms and training courses. APTRON is recognized as the best Data Science Training Institution in Delhi because of its platform that enables people to explore and experiment with any task with real-time projects.
ReplyDeleteRealize your Data Science dreams with Data Science Training in Gurgaon. Don't dream of becoming a certified Data Science professional. Our Data Science courses provide advanced, real-time, hands-on projects to help tackle any type of project.
ReplyDeleteThis post is very simple to read and appreciate without leaving any details out.
ReplyDeletedata science course with placement in Bangalore Great work!.
In this article, you will get to know why the demand for data scientists has increased in the industry.
ReplyDelete360 DigiTMG Provides
best data science courses in hyderabad
Fortinet is the best certification course to enter in cybersecurity field. Anyone can start their career in network security with Fortinet security training. The first step certifications are NSE1, NSE2 and NSE3 from where you can start learning Fortinet course.
ReplyDelete