Sql Interview Questions

Make sure that you guys learn all the topics before go through the Interview questions

Basic Questions:

• Which Version of Oracle are you using?
• What is the meaning of i, g and c in oracle version?
• What is Schema in Oracle?
• What is Schema Objects?
• What is dual table in oracle?
• What are all the Schema objects available in oracle?
• What is SQL?
• What is Data Dictionary table in oracle?
• How to select data from other schema?
• How to Select Unique Records from the particular column? or 
• How To select distinct records from the particular column.
• How to view the structure of the Table?    
• Which Data Dictionary table contain Table information?
• Which Data Dictionary Table contain information about all the objects in database?

  Click here to learn Retriving data using SELECT statement

Restriction and Sorting:

• What are all the operators available in oracle?
• Difference between IN and EXISTS ? Which one is more Faster? Why?
• Which operator is used for pattern matching or to do wildcard search?
• Write a query to display all the name which starts with S.
• Write a query to display all the name starts with S and ends with character n.
• Write a query to display all the employees who are all working for department 90 and their name must starts with S.
• Display all the job id which contain _ (underscore) as 3rd character.
• Write a query to print all the first_name which contains five characters.
• Write a query to display all the employees who are all working in department 10,20,50 and 90.
• Write a query to display first name, salary and department id of the employees who are all not working for 10,20,50 and 90.
• Display all the employees who are all hired in year 1994.
• Write a query to display who are all getting salary between 5000 and 7000.
• Display First_name, salary, department_id and manager_id of the employee who don't have manager.
• Display all the records in employees table and sort the first name in ascending order.
• Display first name, department id and salary from employees table and sort the records ( sort department id in ascending order and salary in descending order)    
• What is the default ordering of an ORDER BY clause in a SELECT statement .

Click here to learn Restriction and sorting

 Choose a job you LOVE and you will never have to work a day in your life

- Confucius

Functions

• What are the types of functions available in oracle.
• Difference between single row function and multiple row function.
• List out all the case and character functions.
• Display first three characters from first name.
• Display last two character from last name.
• Display all the first name and position of a in that name (first occurrence of a).    
• Display all the first name and position of a in that name (second occurrence of a)
• Display all the name which contain two or more number of a 's in the first name.
• Difference between SUBSTR and INSTR function.
• Difference between REPLACE and TRANSLATE function.
• Difference between LPAD and RPAD.
• Difference between LTRIM and RTRIM.
• Display all the first name and its length.
• List out all the number functions in oracle.
• List out all the Date functions in oracle?
• Display all the first name and their total year of experience. rename first name column name as name and second column name as Year of Exep.
• How to display months between two given date.
• Write a query to display today's date.
• Write a query to display the date after 3 months from today.
• Display last date of the current month.
• Display the up coming Wednesday date.
• Which date function return number as output.  
• What are all the type conversion functions available.
• How to convert date into character.
• How to convert character in to date.
• What is the use of general function.
• Explain NVL, NVL2 , NULLIF and COALESCE function with example.
• What are all the aggregate functions available in oracle.
• Write a query to select maximum salary from employees table.
• Write a query to select second maximum salary from employees table.
• Display average salary in the department 90.
• Display number of employees working in department 90 and 60.
• Display all the department id and its maximum salary.
• Display all the department id and number of employees working in that department.   
• Display all the department id and salary allocated for that department.
• Display all the department id and number of employees working in that department. Total no employees working for the particular department must be greater than 30.
• Difference between WHERE clause and HAVING clause. 
• Difference between DECODE and CASE.   
• How to update all Male to Female and all female to Male by using single update statement?       

Joins:

• What is joins? What are all the difference types of joins available?
• Explain outer join and its types with example.
• What is Self Join and why is it required?
• What is the difference between inner and outer join? Explain with example. 
• What is a Cartesian product. 
• If I try to Fetch data from 25 tables. How many number of join condition required?

 Click here to learn Joins

Sub Queries:

• What is a sub query? What are all the types of sub query?
• List out the operators of single row and multiple row sub query.
• Difference between single row and multiple row sub query.
• What is Inline view?
• Write a query to display all the employees who are all getting salary more than the average salary of the department 60.
• What is cor related sub query?
• Write a query to display all the employees who are all getting salary more than the average salary of the their department.
• Write a query to display all the employees who are all getting salary more than their manager salary.
• What are the advantages and disadvantages of using a sub query?    
• How to rewrite joins by using sub query? Give example.

DDL, DML, DCL and DCL statements:

• List out all the DDL statements.
• What are all the data types available in oracle.
• Difference between VARCHAR2 and CHAR data type.
• How to create table in oracle.
• How to create table with DEFAULT option.
• How to copy the structure of particular table by using sub query? ( With data) 
• How to copy the structure of particular table by using sub query? ( With out data)
• What is the use of DROP option in the ALTER TABLE command.
• How to add the column after the table has been created?
• How to rename the particular column in a table?
• How to change the data type of the particular column after the table has been created.
• How to rename the table.
• How to drop the table?
• How to truncate the table?
• What are all the DML statements available?
• How to INSERT records in to selective no of columns?
• How to insert data by using sub query?
• How to insert all the data from table X to table Y?
• How to update more than one column at a time.
• How to delete all the employees working  in department 30.
• If we ignore WHERE clause in UPDATE statement what ll happen?
• If we ignore WHERE clause in DELETE statement what will happen?        
• Difference between DELETE, TRUNCATE and DROP.
• What is the use of MERGE statement? Explain it with syntax.
• What are all the Transaction Control Language available?
• A table contain list of students record with their gender details. Write a update statement to update all the male in to female and female into male.

Constraints:

• What are all the type of constraint available in oracle?
• Difference between PRIMARY KEY and UNIQUE key.
• Difference between PRIMARY KEY ans FOREIGN KEY.
• Difference between UNIQUE key and FOREIGN KEY.
• Write a syntax to create FOREIGN constraint.
• How to create constraint after the table has been created?
• How to enable and disable a constraint.
• Which data dictionary table contain information about constraints?         
• Explain ON DELETE CASCADE and ON DELETE SET NULL.

Views:

• What is View?
• What are are all the different types of views?
• View contain data or not?
• Which Data Dictionary table contain View information? 
• Is it possible to create view with out base table? 
• What is a use of view WITH CHECK OPTION and WITH READ ONLY?
• List out the advantage and disadvantage of views.
• Is it possible to perform DML operation on view?
• How to perform insert operation on complex view? 

Index:

• What is INDEX?
• When to create Index and when not to create index?
• What are are all the types of index available?
• How to enable and disable index?
• Which Data Dictionary table contain information about Indexes.

 Pseudo columns and sequences:

• How to select first 5 records from a table?
• What are all the Pseudo columns available?
• Write a query to find out nth maximum salary? 
• While creating a sequence, what does cache and nocache options mean?
• How do we set the LASTVALUE value in an Oracle Sequence?
• How to Recompile the Procedure and Function?  
      

Synonyms:

• How to create synonyms?
• Explain Private and public Synonym.
• Which Data Dictionary Table contain information about Sequences?

Set operators:

• What is the difference between JOIN and UNION?
• What is the difference between UNION and UNION ALL?
• What is the difference among UNION, MINUS and INTERSECT?
• What are all the restriction we need to follow while using SET OPERATORS.

Analytical Functions:

• What are the differences among ROWNUM, RANK and DENSE_RANK?
• Explain LEAD and LAG Function.

Difference Between Questions in SQL:

• Difference between IN and EXISTS ? Which one is more Faster? Why?
• Difference between SUBSTR and INSTR function.
• Difference between REPLACE and TRANSLATE function.
• Difference between LPAD and RPAD.
• Difference between LTRIM and RTRIM.
• Difference between WHERE clause and HAVING clause.
• Difference between DECODE and CASE. 
• What is the difference between inner and outer join? Explain with example.  
• Difference between single row and multiple row sub query.
• Difference between DELETE, TRUNCATE and DROP.
• Difference between PRIMARY KEY and UNIQUE key.
• Difference between PRIMARY KEY ans FOREIGN KEY.
• Difference between UNIQUE key and FOREIGN KEY.
• What is the difference between JOIN and UNION?
• What is the difference between UNION and UNION ALL?
• What is the difference among UNION, MINUS and INTERSECT?

Give your valuable feedback below :)