SQL Interview Questions And Answers - Part 1

Frequently Asked Questions On SQL | SQL FAQs


1. Write a query to copy table structure without the data.


CREATE TABLE emp_new AS
SELECT * FROM emp WHERE 1 = 2;

2. Write a query to rename a column phone to phone_number.

CREATE TABLE emp_new (emp_id, emp_name, address, phone_number, salary) AS
SELECT emp_id, emp_name, address, phone, salary FROM emp;
DROP TABLE emp;
RENAME emp_new TO emp;

3. Write a query to display 1 row selected while there is no data in the table.
SELECT COUNT(*) FROM emp;

4. Write a query which display emp_id & emp_name along with an extra field * & All Employees like this.

Employee Code
Employee Name
*
All Employees
E001
Susheel
E002
Jain

SELECT emp_id "Employee Code", emp_name "Employee Name" FROM emp
UNION
SELECT '*' "Employee Code", 'All Employees' "Employee Name" FROM emp
ORDER BY 1;

5. Write a query to display alternate odd or even rows.

SELECT * FROM emp WHERE (ROWID, 0) NOT IN
(SELECT ROWID, MOD(ROWNUM, 2) FROM emp);

6. Display the name of  employees and their managers from emp table by using self join

SELECT e.emp_name, m.emp_name FROM emp e, emp m WHERE e.manager_id = m.emp_id
ORDER BY e.emp_id;

7. Write a query to display the departments and no. of employees working in those departments

SELECT dept_id, COUNT(emp_id) FROM emp GROUP BY dept_id;

8. Write a query which displays those entries which are not duplicate.

SELECT * FROM emp WHERE ROWID NOT IN
(SELECT MIN(ROWID) FROM emp GROUP BY emp_id);

9. Write a query which display emp_id and min & max salary in same column like this.
Employee
Salary
E001
1,000  min(salary)
E005
10,000 max(salary)

SELECT emp_id "Employee", salary "Salary" FROM emp
WHERE salary = (SELECT MIN(salary) FROM emp)
UNION
SELECT emp_id "Employee", salary "Salary" FROM emp
WHERE salary = (SELECT MAX(salary) FROM emp);

SELECT emp_id, salary FROM emp
WHERE salary <= all (SELECT salary FROM emp)
UNION
SELECT emp_id, salary FROM emp
WHERE salary >= all (SELECT salary FROM emp);

10. Write a query to display nearest min and nearest max  from average salary.

SELECT emp_id, salary FROM emp WHERE salary <= (SELECT MAX(salary) FROM emp WHERE salary = (SELECT AVG(salary) FROM emp))
UNION
SELECT emp_id, salary FROM emp WHERE salary >= (SELECT MIN(salary) FROM emp WHERE salary = (SELECT AVG(salary) FROM emp));

11. Display the list of top n employees getting highest salary

SELECT empno, sal FROM (SELECT empno, sal FROM emp ORDER BY sal DESC)
WHERE rownum < = &n;

12. Find Maximum salary Department and Employee wise

SELECT a.empno, a.deptno, b.sal
 
FROM emp a, (SELECT * FROM (SELECT empno, deptno, sal, dense_rank()
over(partition by deptno order by sal desc) rn FROM emp)) b
 
WHERE a.deptno = b.deptno(+) AND b.empno(+) = a.empno AND b.rn(+) = 1;

13. Display the list of employee getting nth highest salary.

SELECT empno, sal FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= &n
MINUS
SELECT empno, sal FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= &n-1;

OR,

SELECT rn, empno, sal FROM (SELECT rownum rn, empno, sal FROM
(SELECT empno, sal FROM emp ORDER BY sal DESC)) WHERE  rn = &n;

OR,

SELECT DISTINCT a.empno, a.sal FROM emp a WHERE &n =
(SELECT COUNT(DISTINCT b.sal) FROM emp b WHERE a.sal <= b.sal);



OR,

SELECT * FROM (SELECT empno,sal,rank() over (ORDER BY sal DESC) rnk FROM emp)
WHERE rnk <= &n;
 

OR,

SELECT level, max(salary) FROM emp WHERE level = &n
CONNECT BY PRIOR salary > salary GROUP BY level;

14. Delete duplicate entries from a table.

DELETE FROM emp WHERE ROWID NOT IN
(SELECT MIN(ROWID) FROM emp GROUP BY emp_id);

15. Delete the entries from emp table which are not matching from emp_history table by using correlated subquery.

DELETE FROM emp e WHERE (e.emp_id, e.manager_id, e.salary) NOT IN
(SELECT h.emp_id, h.manager_id, h.salary FROM emp_history h
WHERE e.emp_id = h.emp_id AND e.manager_id = h.manager_id AND e.salary = h.salary);

COMMENTS

Name

Advance Table In OAF,1,AOL Concepts,18,AP Holds,1,Apps Initialization,1,Blanket Purchase Orders,1,Blanket Releases,1,Careers,9,Common Errors and Solutions,2,Concurrent Programs,10,Contract Purchase Orders,1,Corporate Culture,3,Corporate Ladder,3,Customer Interface,3,Customization,1,De-Normalization,1,EAM,1,EBS Tutorials,10,Education,16,Emterprise Asset Management,1,English,1,Enterprise Asset Management,1,Entrepreneur,1,Error Fixes,1,Executables,2,Extension,1,fnd_user,1,FNDLOAD,1,Global Agreements,1,Guest Posts,7,Handy Scripts,63,Health Tips,1,Important Apps Queries,1,Infographic,3,Interview Preparation,57,Interview Q/A,57,Inventory Org ID,1,Iproc,4,Iprocurement,4,IT,3,jDeveloper,1,Job Search,1,Key Tables In Oracle Apps,5,Learning English,1,MDS,1,Metadata Tables,2,Multi Org Architecture,2,Normalization,1,O2C,1,OAF Interview,49,OAF Personalization,1,OAF Tutorials,52,Operating Unit,1,Oracle Alerts,1,Oracle AP,11,Oracle APIs,4,Oracle Application Object Library,19,Oracle Applications,10,Oracle Apps,2,Oracle Apps Interviews,29,Oracle Apps Tips,8,Oracle Apps Tutorials,97,Oracle AR,18,Oracle Assets,2,Oracle Dabase Structure,2,Oracle Database Architecture,2,Oracle Database Key Tables,3,Oracle EAM,2,Oracle EBS,5,Oracle Financials,2,Oracle Forms And Reports,17,Oracle Forms Interview Questions,6,Oracle Forms Tutorials,6,Oracle GL,4,Oracle Holds,1,Oracle HRMS,10,Oracle Human Resources,1,Oracle Inventory,4,Oracle Payables,1,Oracle Payroll,1,Oracle POTutorials,1,Oracle Purchasing,7,Oracle Reports Interview,9,Oracle Reports Tutorials,3,Oracle System Administrator,4,Oracle Workflow,3,Order Management,2,Organization ID,3,Organizations,3,Performance Tuning,2,Personalization,1,PL/SQL Block,1,PL/SQL Interview Questions,11,PL/SQL Scripts,44,PL/SQL Tutorials,53,Planned Purchase Orders,1,PO,1,PO Action History,1,PO_ACTION_HISTORY,1,POracle Purchasing,1,PR,1,Procedures And Functions,2,Profile Options,2,Purchase Orders,1,Purchase Requisition,2,Receipts,1,Request Groups,1,Responsibility,1,Shell Script,1,SOA Tutorials,4,Social Media,1,Software Industry,2,Software Tools,1,SQL And PL/SQL Interview,38,SQL Interview,51,SQL Scripts,38,SQL Tutorials,43,Standard Purchase Orders,1,Troubleshooting,1,Unexpected error,1,Unix/Linux Commands,3,User,1,Value Sets,1,Weblogic,1,XML Publisher,3,
ltr
item
Oracle Apps Guy: SQL Interview Questions And Answers - Part 1
SQL Interview Questions And Answers - Part 1
Oracle Apps Guy
http://www.oracleappsguy.com/2011/11/sql-interview-questions-and-answers.html
http://www.oracleappsguy.com/
http://www.oracleappsguy.com/
http://www.oracleappsguy.com/2011/11/sql-interview-questions-and-answers.html
true
4186973960539901882
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS CONTENT IS PREMIUM Please share to unlock Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy