Top 25 SQL Interview Questions

What is the difference between primary key, unique key and foreign key.

Primary key = column will always have unique or distinct values. NULLs are not allowed. Unique key = column will always have unique or distinct values. NULLS are allowed. Foreign key = Foreign key is used to create a master child kind of relationship between two tables.

What are steps you would take to tune a SQL query?

1. Check the SQL Query. 2. Check if index is created for the desired columns. 3. Check if table statistics are up to date. 4. Check the explain plan.

What are the rules to follow when using UNION operator?

UNION operator can be used to combine two different SQL Queries. The output would be the result combined from both these queries. Duplicate records would not be returned.

What are the DCL, TCL and DQL languages?

DCL stands for Data Control Language. DCL includes GRANT and REVOKE statements. TCL stands for Transaction Control Language. TCL includes COMMIT, ROLLBACK and SAVEPOINT. DQL stands for Data Query Language. It includes only the SELECT statement.

What are DDL and DML languages? Give examples.

DDL stands for Data Definition Language. They include CREATE, DROP, ALTER and TRUNCATE statements. DML stands for Data Manipulation Language. DML includes INSERT, UPDATE, DELETE and MERGE statements.

What is the difference between DISTINCT and GROUP BY?

DISTINCT clause will return unique column values. Depending on the list of columns you provide I the DISTINCT clause, it will fetch the unique combination of values for all those combined columns. If you provide just a single column in DISTINCT then it fetches just the unique values in that specific column. GROUP BY clause will group together the data based on the columns specified in group by. Which will then return just one record for each unique value in the column specified in group by.

What is the difference between a function and a procedure?

Function should always return a value whereas for a procedure it's not mandatory to return a value.

When can a function NOT be called from SELECT query?

If the function includes DML operations like INSERT, UPDATE, DELETE etc then it cannot be called from a SELECT query. Because SELECT statement cannot change the state of the database.

What are indexes? Why do we use it?

Index is a database object which is applied on one or more columns of a table. When a column (or list of columns) from the table is Indexed, database creates a pointer to each value stored in that column. This significantly improves the query execution time since the database will have a more efficient way to find a particular value from the column based on its index.

Is it good to have the same subquery multiple times in your query? If no then how can you solve this?

It is not good to have the same subquery multiple times in your query. We can avoid this by using the WITH clause.

What is the difference between LEFT, RIGHT, FULL outer join and INNER join?

LEFT JOIN will fetch all records from the left table (table placed on the left side during the join) even if those records are not present in right table (table placed on the right side during the join) RIGHT JOIN will fetch all records from the right table (table placed on the right side during the join) even if those records are not present in left table (table placed on the left side during the join). FULL JOIN will fetch all records from both left and right table. INNER JOIN will fetch only those records which are present in both the joined tables.

How can you convert a text into date format? Consider the given text as "31-01-2021".

Oracle: SELECT TO_DATE('31-01-2021', 'DD-MM-YYYY') as date_value FROM DUAL; MySQL: SELECT DATE_FORMAT('31-01-2021', '%d-%m-%Y') as date_value; Microsoft SQL Server (MSSQL): SELECT CAST('31-01-2021' as DATE) as date_value; PostgreSQL: SELECT TO_DATE('31-01-2021', 'DD-MM-YYYY') as date_value;

Imagine there is a FULL_NAME column in a table which has values like "Elon Musk", "Bill Gates", "Jeff Bezos" etc. So each full name has a first name, a space and a last name. Which functions would you use to fetch only the first name from this FULL_NAME column? Give example.

Oracle: SELECT SUBSTR(full_name, 1, INSTR(full_name, ' ', 1, 1) - 1) as first_name FROM dual; MySQL: SELECT SUBSTRING(full_name, 1, INSTR(full_name, ' ', 1, 1) - 1) as first_name FROM dual; Microsoft SQL Server (MSSQL): SELECT SUBSTRING(full_name, 1, CHARINDEX(' ', full_name) - 1) as first_name; PostgreSQL: SELECT SUBSTR(full_name, 1, POSITION(' ' IN full_name) - 1) as first_name;

Which function can be used to fetch yesterdays date? Provide example.

Oracle: SELECT SYSDATE - 1 as previous_day FROM DUAL; MySQL: Below query would return the date and timestamp. SELECT DATE_SUB(SYSDATE(), INTERVAL 1 DAY) as previous_day; Below query only returns the date. SELECT DATE_SUB(CAST(SYSDATE() AS DATE), INTERVAL 1 DAY) as previous_day; Microsoft SQL Server (MSSQL): Below query would return the date and timestamp. SELECT DATEADD(DAY, -1, GETDATE()); Below query only returns the date. SELECT DATEADD(DAY, -1, CAST(GETDATE() AS DATE)); PostgreSQL: SELECT CURRENT_DATE - 1 as previous_day FROM DUAL;

What is the difference between RANK, DENSE_RANK and ROW_NUMBER window function?

RANK() and DENSE_RANK() functions will assign a rank to each row within each partitioned result set. However, the RANK() function skips a rank if there are duplicate rows whereas DENSE_RANK() function will never skip a rank. ROW_NUMBER() function will assign a unique row number to every row within each partitioned result set. It does not matter if the rows are duplicate or not.

What is the difference between a views and a materialized views?

Similar to views, materialized views are also database objects which are formed based on a SQL Query however unlike views, the contents or data of the materialized views are periodically refreshed based on its configuration.

What is the difference between DELETE and TRUNCATE statement?

The DELETE statement commits the changes made and is part of the DML language. Whereas the TRUNCATE state does not commit the changes made and is part of the DDL language.

What is MERGE statement?

Used to either perform INSERT or UPDATE based on the data in the respective table.

What is the difference between a view and a synonym?

View is a database object which is created based on a SQL Query, may be formed by different tables, and can change if the resulting query changes. Synonym on the other hand is just an alias or an alternate name that you can provide to any database objects such as tables, views, sequences, procedures etc.

Difference between WHERE and HAVING clause.

WHERE clause is used to filter records from the table. We can also specify join conditions between two tables in the WHERE clause. HAVING clause is used to filter records returned from the GROUP BY clause. So if a SQL query has WHERE, GROUP BY and HAVING clause then first the data gets filtered based on WHERE condition, only after this grouping of data takes place. Finally based on the conditions in HAVING clause the grouped data again gets filtered.

What are subqueries? Where can we use them?

A SELECT query statement which is placed inside another SELECT query is termed as a subquery. Subquery can also be termed as inner query.

What is trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be considered to be similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures. Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself; so when the trigger is fired because of data modification, it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger. (Read more here)

What are aggregate functions? Name and explain different types of aggregate functions in SQL?

Aggregate function can be used to perform calculation on a set of values, which will then return a single value. We can use aggregate function either with GROUP BY clause or without it. Aggregate functions include SUM, AVG, MAX, MIN, COUNT

What is PRAGMA AUTONOMOUS TRANSACTION?

We can declare the stored program like a procedure as a PRAGMA AUTONOMOUS TRANSACTION which means that any transaction committed or rolled back in this procedure will not impact any open transactions in the program from where this procedure was called from. To understand this further, let's imagine we have two procedures, pr_main and pr_log. PR_MAIN is a normal procedure whereas PR_LOG is declared as PRAGMA AUTONOMOUS TRANSACTION. In the execution block of PR_MAIN, let's imagine we do some DML operations like INSERT 100 records into a test table and then within the exception handling block of PR_MAIN we call the PR_LOG procedure. PR_LOG procedure will do some inserts into the log table and then do some commits. Now when we call the PR_MAIN procedure, if there was an unexpected exception then the PR_LOG procedure will get called. However, any commit done in the PR_LOG procedure will not impact the open transactions in PR_MAIN procedure because PR_LOG is declared as autonomous transaction so the commits and rollback within a autonomous transaction procedure only impacts its own transaction. It's like autonomous transaction procedure will have its own database session so any transactions commit or rollback only impacts its internal database session.

Why do we use CASE Statement in SQL? Give example

We use the CASE statement to fetch or show a particular value based on certain condition. It is similar to the IF ELSE statement.

Can we use aggregate function as window function? If yes then how do we do it?

Yes, we can use aggregate function as a window function by using the OVER clause. Aggregate function will reduce the number of rows or records since they perform calculation of a set of row values to return a single value. Whereas window function does not reduce the number of records.