• What is the difference between truncate and drop?
  • What is the difference between truncate and delete?
  • How to connect database from any client tools?
  • How do you delete rows but keep one row?
  • What is deadlock.How to resolve deadlock?
  • Which version of oracle are you using how to find out?
  • Display list of tables from ahmed’s or any user’s schema?
  • Show all the employees who were hired on he day of the week on which the highest number of employees were hired.
  • Display list of schema and total number of tables in each schema. Sort the list from the largest number of tables to lowest number of tables.
  • How would you increase the performance of the following select statement –

             select * from sales_history  where customer_id =288309

  •  How will you identify duplicate rows in a table?
  •  Get the details from employees table whose joining month is January.?
  •  What is the difference between UNION and UNION ALL?
  •  what is an execution plan?How to see plan of any SQL statement?
  •  How do you find out an execution plan of a select statement?
  •  Can you have 2 primary keys on one table?
  •  What is a composite primary key?
  •  How to find out what is the primary key of a table?
  •  How to find out if a table has any indexes and on what columns?
  •  Is select statement a DDL or DML?
  •  What is the difference between DDL and DML?
  •  What is locking?
  •  Does select statement lock the rows?
  •  What kind of lock is acquired by update or delete?
  •  Explain what is a savepoint ? In what scenario would you use it? Give an example.
  •  What is the use of cascade constraint?
  •  What is the meaning of a transaction? Give an example.
  •  Find the third max salary from the employees table?
  •  Display all employees whose employee_id is an odd number?
  •  List dept no., Dept name that do not have any employees in it. Write this query using 2 different ways
  •  Display department name and total number of employees in that department. Display only top 5 by total number.
  •  Display Department name and total number of employees in that department. List departments even if   they do not have any employees in it.
  •  Select all records where last_name starts with ‘s’ and its length is 6 char.
  •  Display employee name, his/her department name, his/her manager name. Sort it by Department Name from a-z
  •  If there are two tables tab1 and tab2 with identical structure. How will you find out employees that are in tab1 and also in tab2.
  •  If there are two tables tab1 and tab2 with identical structure.
  • How will you find out employees of tab1 except those employees of tab1 whoever present in tab2. 
  •  Display list of departments and total salary of that department. Display only list of departments whose total salary is more than 50000.
  •  Display employee names, salary, jobs of employees who were hired in the year of 2000
  •  Display all regions from the regions tables and total number of employees in that region. List only those regions that have more than 12 number of employees. Arrange the list from highest employee number to lowest employee number
  •  Display list of tables in your schema ?
  •  Display list of objects in ahmed’s schema?
  •  How do you find out the current date?
  •  How do you find out the current time?
  •  How to find out the foreign keys on any table?
  •  How do you change the sql prompt in sqlplus?
  •  How do you create a materialized view?
  •  How do you refrest a MV?
  •  What is the difference between fast and complete refresh?
  •  What is a materialized view log?
  •  What is a difference between materialized view and a regular view?
  •  T/F – Data in Oracle is case sensitive?
  •  T/F – SQL commands are case sensitive.?
  •  How do find out the time taken for executing a select statement in sqlplus.
  •  What is an escape character? Give an example.
  •  List total number of employees who are from Americas region and whose salary is more than 4000
  •  Display total number of employees from the Europe region who had worked in the Sales department in the year 1998.
  •  List of employees and their salaries whose salary is less that the average salary of the company.
  •  List employees who earn more that the average salary of their department?
  •  Display list of schema and total number of tables in each schema. Sort the list from the largest number of tables to lowest number of tables.
  •  Get names of employees from ESC table who has ‘%’ in Last_Name?
  •  Which country has the maximum number of employees?
  •  List of employees and their salary who are getting paid below 50% of their salary range.
  •  Display a list of departments and managers’ name along with their phone numbers. Do not display country code of phone number.
  •  Write a query that will identify all employees who work in departments located in the United Kingdom and whose salary is more than the average salary of their department.
  •  What are the most common steps you have encountered in an execution plan?
  •  Increase the salary range of all jobs from the Executive department by 20%.
  •  Have you used a Database Link. How, Where. Give Example?
  •  What is a difference between Primary key and Unique Index?
  •  How do you find out if there are any rows that are locked in a table?
  •  What is the difference between views and synonym?
  •  Why do we need public synonym?
  •  What is private synonym and how to create it?
  •  What are the scenarios if user having simple view and still not able to insert any row in base table?
  •  Why do we need views?are views physically present in database?
  •  How do some one know that how many constraints are on any table?
  •  Create a query that displays salary,employee_id and indicates the amounts of their annual  salaries with asterisks. Each asterisk signifies a thousand dollars. If the salary>1000 then salary should be padded with asterisks else the original salary?
  •  I have one table which is having certain data in it what will happen if I want to apply primary key at this level?
  •  What are the new fetures of 12C?
  •  What is listagg function,rank and dense rank in sql?
  •  What is inline view?
  •  What is global sequence and session sequence in 12C?
  •  Difference between 11g and 12C?
  •  What are the operator precedence?
  •  Suppose you are having a table t1 whose structure and values are as
IDNAME
1A
2B
3C
4D
5E

You have to write a select statement so that the output of the query will be

IDNAME
1A
2BB
3CCC
4DDDD
5EEEEE
  •  How many data dictionary you know.Name some important one?and there types also?
  •  What is flashback?explain in reference of rollback?
  •  What are the differences among ROWNUM, RANK and DENSE_RANK?
  • What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?
  • What is self join and why it is required?
  • What is the difference between UNION,MINUS and INTERSECT?
  • What is the difference between WHERE and HAVING clause?
  • What is the difference between JOIN and UNION?
  • What is the difference between UNION and UNION ALL?
  • What is the difference between INNER and OUTER JOIN?
  • What is constraints? Tell abt its level?
  • What is ACID property in database?
  • What are the different types of statements in sql?
  • What are the differences between primary,foreign and unique constraints?
  • Can a table have multiple primary key,foreign key and unique key?
  • What is an index? How a database index can help performance?
  • What is the difference between inner and outer join? Explain with example?
  • What is the difference between JOIN and UNION?
  • What is the difference between WHERE clause and HAVING clause?
  • What is the difference among UNION, MINUS and INTERSECT?
  • What is Self Join and why is it required?
  • How can we transpose a table using SQL (changing rows to column or vice-vers)?
  • How to generate row number in SQL Without ROWNUM
  • What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?
  • What are the differences among ROWNUM, RANK and DENSE_RANK?
  • What is an execution plan? When would you use it? How would you view the execution plan?
  • List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.?
  • How can you select all the even number records from a table? All the odd number records?
  • What are the NVL and the NVL2 functions in SQL? How do they differ?
  • Difference between varchar and varchar2 data types?
  • What is COALESCE function?
  • what are lobs ? and  What is BLOB datatype?
  • How do we represent comments in Oracle?
  • What is DML?
  • What is DDL?
  • What is DQL?
  • What is DCL?
  • What is TCL?
  • What is the difference between TRANSLATE and REPLACE?
  • What is the usage of Merge Statement?
  • What is WITH CHECK OPTION?
  • What do you mean by GROUP BY Clause? Why we use it?
  • What is a sub query and what are the different types of subqueries?
  • What is the difference between rename and alias?
  • What is a View?Its types?
  • What is materialized view?
  • Which are physical in oracle sql?Name them?
  • Can we store pictures in the database and if so, how it can be done?
  • What is an integrity constraint?
  • What is difference between SUBSTR and INSTR?
  • What are privileges and Grants?
  • What is ORACLE_SID? and ORACLE_HOME? and ORACLE_BASE?
  • What is rowid? How can we view last record added to a table?
  • What is dual table ?and What is the data type of DUAL table?
  • What is difference between Cartesian Join and Cross Join?
  • How will covert rows into columns?
  • What are the analytical function?
  • What is Function-Based Index? Create a Function-Based Index
  • What are Btree and bitmap indexes?
  • What is index organised table?
  • How will you find out the size of table or index?
  • How will you Find out the dependencies of table towards plsql object or sql bjects?
  • How to gather table stat?is it beneficial?
  • How to pass hint in sql statements? explain some of the hint?
  • What does cache and no cache options mean while creating a sequence?
  • What are meaning of the ON DELETE CASCADE and ON DELETE SET NULL?
  • Do we have any thing like ON UPDATE CASCADE?
  • What are virtual columns ? And How to Create Them?
  • What is SQl injection ?
  • What is Data Warehousing ?
  • What are Data Marts?
  • What is ETL process in Data warehousing? What are the different stages in “Data warehousing”?
  • What is Data mining ?
  • Can a table have more than one foreign key and primary key defined?
  • What are Advantages of COMMIT and ROLLBACK Statements?
  • What are the types  of lock ?When row get lock or when table get lock?
  • What is ORA-60?
  • What is Flashback in SQL?AND its advantage?
  • What is the difference between synonym and views?
  • What is the differrence between View and materialized View?
  • What is sequence?What is cache and cycle  in sequence?
  • What is the benefit if public synonym?
  • How to create an excel sheet or csv file in sqldeveloper tool or maually?
  • How to export or import a table through sqldeveloper tool ?

Categories:

Tags:

No responses yet

Leave a Reply

Your email address will not be published. Required fields are marked *