SQL Interview Questions

1. What is a Query Plan?
1. A query plan is a set of steps that the database management system executes in order to complete the query. 
2. The reason we have query plans is that the SQL you write may declare your intentions, but it does not tell SQL the exact logic flow to use.  The query optimizer determines that.  The result of that is the query plan
3. The lesson to learn from this when in doubt check the execution plan.  If you feel a query is running slow and an equivalent query, such as a join, may be faster, write one up and check the plan.  Check to see which uses more efficient steps.  This much better than guessing.  As you get better a reading plans you’ll start to notice things about your databases, such as whether you need to add an index.
2. Database Indexes
1. Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
2. An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.
3. Single-Column Indexes
1. A single-column index is created based on only one table column.
4. Unique Indexes
1. Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows
5. Composite Indexes
1. A composite index is an index on two or more columns of a table. Its basic syntax is as follows.
6. CREATE INDEX id_index ON students (stdId) USING BTREE;
1. Will do ab binary search on stdId column and it increases the performance.
7. A database index is somewhat similar to this table of contents in a book. Indexing will help a data base query to be retrieved fast (Because the query does not require to go through the entire table to get the data, but it will find the data blocks from the index.).
3. What is the difference between UNION and UNION ALL?
1. UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.
2. There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates.
4.  How to retrieve data from json string saved in a column?
1. JSON
2. {"male" : 2000, "female" : 3000, "other" : 600}
3. Query
4. SELECT ID, CITY, json_extract(POPULATION_JSON_DATA, '$.male') AS POPULATION_MALE, json_extract(POPULATION_JSON_DATA, '$.female') AS POPULATION_FEMALE, json_extract(POPULATION_JSON_DATA, '$.other') AS POPULATION_OTHER FROM JSON_TABLE;
5. Oracle bind variables?
1. Using :variable instead of direct values
2. Before Oracle runs a SQL statement it checks it's valid and determines how to access the tables and join them together. This is called parsing. The optimizer has the task of figuring out which table access and join methods to use. This produces an execution plan. When Oracle sends a statement to the optimiser to do this it's called a hard parse.
3. If a plan already exists for a query, Oracle doesn't need to go through the optimization process again. It can reuse the existing plan. This is referred to as soft parsing.
4. select * from orders where order_id = :ord;
5. If we run this query twice in succession with different values for :ord there's one hard parse and one soft parse and increases the performance.