♥♦ 14+ SQL interview Questions & Answers

SQL interview Questions & Answers is a must for any developer as all non-trivial applications need to talk to a database with CRUD operations. Q3 is the most popular.

Q1. Can you explain DDL statements in regards to SQL?
A1. DDL stands for Data Definition Language, which are statements used to define the database structure or schema. CREATE – to create objects in the database, ALTER – alters the structure of the database, DROP – delete objects from the database, TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed, COMMENT – add comments to the data dictionary, and RENAME – rename an object.

Create a table named “EMPLOYEES” using a DDL statement

Create a table named “EXECUTIVES” using a DDL statement

Q2. Can you explain DML statements in regards to SQL?
A2. DML stands for Data Manipulation Language, which are used for managing data within schema objects. SELECT – retrieve data from the a database, INSERT – insert data into a table, UPDATE – updates existing data within a table, DELETE – deletes all records from a table, the space for the records remain, CALL – call a PL/SQL or Java subprogram, EXPLAIN PLAN – explain access path to data, and LOCK TABLE – control concurrency are DML statements.

To insert data into abbe tables:

Also, note that DCL stands for Data Control Language (DCL) statements with GRANT – gives user’s access privileges to database and REVOKE – withdraw access privileges given with the GRANT command, and TCL statements stand for Transaction Control (TCL) with statements are COMMIT – save work done, SAVEPOINT – identify a point in a transaction to which you can later roll back, ROLLBACK – restore database to original since the last COMMIT, and SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use.

Q3. Explain inner and outer joins?
A3. Joins allow database users to combine data from one table with data from one or more other tables (or views, or synonyms). Tables are joined two at a time making a new table containing all possible combinations of rows from the original two tables.

Joins with venn diagram

Joins with venn diagram

Employees Table

Employees Table

Executives Table

Executives Table

Inner join

SQL inner join

SQL inner join

Left outer join

SQL left outer join

SQL left outer join

Right outer join

SQL right outer join

SQL right outer join

Full outer join

SQL full outer join

SQL full outer join

Q4. What is a self join?
A4. A self-join is a join of a table to itself. In certain scenarios, a self join is a better alternative to a sub-query.

Step 1: If you want to add a new column called “Manager” to “Employees” table, you cal use the ALTER DDL.

Step 2: To update who the manager is with the following DML

SQL all employees with manager_code

SQL all employees with manager_code

Step 3: SQL self join can be used to print employee name and manager name side by side. Basically, you are joining the same table to each other.

SQL self join

SQL self join

Q5. What is a sub-query? How does a sub-query impact on performance? What are the advantages and disadvantages of sub-queries?
A5. It is possible to embed a SQL statement within another. When this is done on the WHERE or the HAVING statements, we have a subquery construct. What is subquery useful for? It is used to join tables and there are cases where the only way to correlate two tables is through a sub-query.

There can be performance problems with sub-queries.The above query can be re-written as a left outer join for a faster performance as shown below:

Similarly, the following sub query

can be rewritten with a “right outer join” as shown below

Replacing sub queries with left/right joins with “is null” in where clause

Advantages:

1) Sub-queries allow you to use the results of another query in the outer query.

2) Sub-queries in some complex SQL queries can simplify coding and improve maintainability by breaking down the complex query into a series of logical steps.

3) In some cases, subqueries are easier to understand than complex joins and unions.

Disadvantages:

When a sub-query is used, the query optimizer of the database server may have to perform additional steps like sorting the results, etc. Hence, in some cases sub-queries can be less efficient than using joins.

Q6. What is a correlated sub-query?
A6. A query is called correlated sub-query when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

If a subquery is not dependent on the outer query it is called a non-correlated subquery.

Q7. Can you explain aggregate SQL functions?
A7. SQL provides aggregate functions to assist with the summarization of large volumes of data.

Data

SQL aggregate

SQL aggregate

SQL aggregate

Q8. How can you compare a part of the name rather than the entire name?
A8. You can use wild card characters like:

* ( % in oracle) : Matches any number of characters.
? ( _ in oracle) : Matches a single character.

To find all the employees who have “au” in their names

Q9. How do you get distinct entries from a table?
A9. The SELECT statement in conjunction with DISTINCT lets you select a set of distinct values from a table.

Q10. How can you find the total number of records in a table?
A10. Use the COUNT key word:

Q11. In your experience, what are some of the common mistakes developers make related to SQL?
A11.

1. Cartesian joins

SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements. If a sql join condition is omitted as shown below:

or if the condition is invalid, then the join operation will result in a Cartesian product. The Cartesian product returns a number of rows equal to the product of all rows in all the tables being joined. For example, if the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, or 200 rows. This query will take a long time to execute.

2. Use of SELECT *

For example, a common misuse of SELECT * is to extract a set of all employees and to insert them into another table called Contractors with the same structure

The above query does the job, however, one day business requirements change and two new columns are added to the Employees table:

All of sudden the query that extracts from the Employees table and insert records into the Contractor table results in error.

“Insert Error: Column name or number of supplied values does not match table definition.”

The fix is to explicitly list the column names in the query:

3. Not using Prepared statements. Prepared statements are more secured and efficient than the ordinary statements. Prepared statements prevent SQL injection attacks.

4. Using the predicate “LIKE” in indexed columns. The “LIKE” predicate typically performs a search without the normal performance benefit of indexes. Using ‘=’, ‘<>‘, etc instead of “LIKE” will increase performance. Also should be aware of that case sensitivity (e.g., ‘A’ versus ‘a’) may be different based upon database Server or configuration.

5. Over use of cursors in stored procedures. If possible, avoid using SQL stored proc cursors. They generally use a lot of Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task.

Q12. What is the difference between “Truncate” and “Delete” commands?
A12. TRUNCATE is useful for purging a table with huge amount of data. Alternatively, you can drop the table and recreate it that makes sense. Firing a delete command instead of a truncate command to empty a table with millions of records can result in locking the whole table and also can take longer time to complete, and at times cause the machine to hang.

a) TRUNCATE TABLE_NAME always locks the table and page but not each row, whereas DELETE statement is executed using a row lock, each row in the table is locked for deletion.

b) Truncate removes all the records in the table whereas delete can be used with WHERE clause to remove records conditionally. That is remove only a handful number of records.

c) Truncate performance is much faster than Delete, as its logging is minimal wheres the Delete command logs every record.

d) Truncate does not retain the identity, whereas DELETE command retains the identity. When you use Truncate, If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column.

e) Truncate cleans up the object statistics and clears the allocated space whereas Delete retains the object statistics and allocated space.

f) TRUNCATE is a DDL (Data Definition Language) and DELETE is a DML (Data Manipulation Language).

g) Data removed by TRUNCATE command cannot be generally rolled back unless the database server specifically supports it. The DELETE command can rollback a transaction.

h) The TRUNCATE command does not fire any triggers, whereas the DELETE command fires any triggers defined on the table. For example, to keep an audit trail of records that have been deleted by inserting the deleted records into an audit table via the DELETE triggers.

Q. Which command will you use to periodically purge data from your tables as part of a house keeping job?
A. Use a DELETE command within a transaction with a WHERE clause to remove data that are older than 7 years. Remove large amount of data in batches as opposed to in a single transaction.

Q13. How will you go about deleting a few records from parent and child tables where the parent table with parent_name = ‘Peter’?
A13. Firstly, you need to delete the child records because the integrity constraint won’t let you delete the parent record when there are child records.

Now, the parent table can be deleted as shown below

Q14. What do you do with the PURGE command?
A14. The purge command is used to clear the recycle bin. It is generally used with the DROP command. For example,

the above command will clear away the table from database as well as from the recycle bin. After executing the purge command, you cannot retrieve the table using a flashback query.

Q15. How will you improve the performance of the following SQL query?

A15.Pick only the columns you are interested in and replace “NOT IN” with “<> AND <>” as shown below.

Print Friendly
The following two tabs change content below.
Arulkumaran Kumaraswamipillai
Mechanical Engineering to Java freelancer since 2003. Published Java/JEE books via Amazon.com in 2005, and sold 35K+ copies. Books are outdated and replaced with this online Java training. join my LinkedIn group.
Arulkumaran Kumaraswamipillai

Mechanical Engineering to Java freelancer since 2003. Published Java/JEE books via Amazon.com in 2005, and sold 35K+ copies. Books are outdated and replaced with this online Java training. join my LinkedIn group.

Posted in SQL
Tags: , , , ,

Leave a Reply

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

*

800+ Interview Q&As ♥Free|♦FAQ (Mouse Hover for Full Text)

open all | close all

200+ Java FAQs – Memory Joggers

open all | close all

16 Java Key Areas to be a top-notch

open all | close all

80+ Java Tutorials – Step by step

open all | close all

100+ Java Coding Exercises

open all | close all

How good are your "Career Skills"?

open all | close all