If you are preparing for a SQL developer interview. Then, you are at the right place. RDBMS is one of the most commonly used databases in the software world. That's why SQL skills play a vital role for a software developer. In this post, we covered the top 50 most commonly asked SQL interview questions and answers which are helpful for fresher’s as well experienced candidates.
You can go through these questions for a quick revision of major SQL concepts before appearing for an interview.
Answer: SQL stands for Structured Query Language. SQL is used for storing, modifying and retrieving data in databases. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database or retrieve data from a database.
Answer: RDBMS stands for Relational Database Management System. (RDBMS) is a database management system (DBMS) based on the relational model of data which is for storing in data in a tabular form. RDBMS system supports a tabular structure of data and a relationship between them to access stored data or information.
Example of RDBMS, SQL SERVER, MySQL, PostgreSQL, SQL, etc.
Answer: There are three significant subsets of the SQL:
CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME are the following SQL commands which are used for defining the database schema and used to create and modify the structure of database objects in a database.SELECT, INSERT, UPDATE, DELETE are the following SQL commands which are used for manipulation of data in a database.GRANT and REVOKE are the following SQL commands.Answer: These two authentication modes in SQL Server are as follow:
Answer: The differences between DELETE and TRUNCATE statement are as follow:
| DELETE | TRUNCATE |
DELETE is a DML command. |
TRUNCATE is a DDL command. |
DELETE statement supports WHERE clause. |
TRUNCATE statement does not support WHERE clause. |
| If your table column referenced by foreign key then you can delete rows if there is no reference to the row. | If your table column referenced by foreign key then you can’t TRUNCATE the table even if the referred table is empty. |
DELETE statement does not reset the identity column to its initial value. |
TRUNCATE Statement reset the identity column to its initial value. |
DELETE statement is executed using a row lock, each row in the table is locked for deletion. |
TRUNCATE table always locks the table and page but not each row. As it removes all the data. |
DELETE activates a trigger because the operation is logged individually. |
TRUNCATE table cannot activate a trigger because the operation does not log individual row deletions. |
Performance wise, DELETE is Slower than TRUNCATE, because it Keeps logs. |
Performance wise, TRUNCATE is faster than DELETE, because it doesn’t Keep logs. |
Answer: SQL UNION clause/operator is used to combine the result set of two or more SELECT statements and only returned unique records/rows.
UNION and UNION ALL Both used to combine the result-set of two or more SELECT statements. The Only difference between both UNION and UNION ALL is that UNION returns distinct records while UNION ALL returns all the records.
Answer: There are two types of temporary table in SQL as follow:
| Local temporary table | Global temporary table |
| Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. | Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. |
| Local temporary table name is stared with a single hash ("#") sign. | Global temporary table name is stared with double hash ("##") sign. |
| Stored in temp database. | Stored in temp database. |
| Automatically get deleted when a user disconnects or can be deleted using drop command | Automatically get deleted when all referencing connections closed or can be deleted manually by using DROP command. |
Answer: SQL Server Profiler is a tool to create and manage traces and analyze and replay trace results. In other words, SQL Profiler is a tool that enables you to monitor events within your SQL Server (or more specifically, a SQL Server instance). Events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem.
Answer:
TABLE: A table is a collection of related data held in a table format within a database. It consists of columns and rows.
Field: A Field is referred to a no. of columns specified in a table.
For example, a table named “tbl_StudentInfo” having various columns such as StudentName, StudentAge, Student_RollNo, etc. These columns are termed as Field in a table.
Answer: When a stored procedure can call itself until it reaches maximum boundary condition. This is known as the Recursive Store Procedure.
Answer: A query within a query is known as sub-query. The outer query is called as the main query, and inner query within parenthesis is called sub-query. Sub-query is always executed first, and the result of sub-query is passed on to the main query (outer query).
The properties of sub-query are as follow:
Answer: The types of sub-query are as follow:
IN, ANY, ALL, or EXISTS operators. WHERE clause. A correlated subquery is evaluated once for each row.Answer:
NULL value, it must contain unique values.PRIMARY KEY, which may consist of single or multiple fields.Answer:
UNIQUE and PRIMARY KEY constraint ensures that the uniqueness for a column or set of columns.UNIQUE and PRIMARY KEY is that you can have multiple UNIQUE constraints per table but only one PRIMARY KEY constraint per table.Answer:
PRIMARY KEY constraint in another table.PRIMARY KEY is termed as the parent table or referenced table, and a table containing the FOREIGN KEY is termed as the child table.Answer:
Answer: SQL has different types of Join as follow:
FOREIGN KEY that references its own PRIMARY KEY.Answer: A VIEW is a virtual table which consists of rows and column like a table and it does not physically exist. If the data changes in the underlying table, then the same changes are also reflected in the VIEW.
Answer: Indexes are used to find rows with specific column values quickly. An INDEX allows you to find the specific row from a database without scanning the entire table. Indexes are created on table and views.
Answer: The difference between Cluster and Non-Cluster Index are as follow:
| Clustered Index | Non-Clustered Index |
| Clustered indexes sort and store the data rows in the table or view based on their key values. | Non-clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key-value entry has a pointer to the data row that contains the key value. |
| A table can only have one Clustered Index. | A table can have multiple Non-Clustered Index. Prior to SQL Server 2008 only 249 Non-clustered Indexes can be created. With SQL Server 2008 and above 999 Non-clustered Indexes can be created. |
| A Primary Key constraint creates a Clustered Index by default. | A Unique Key constraint created a Nonclustered Index by default. |
| Faster to read than non-clustered as data is physically stored in index order. | Quicker for insert and update operations than a clustered index. |
Answer: In SQL, a query is a request for data or information from a database table or combination of tables.
Answer: The difference between the WHERE and HAVING clauses are as follow:
GROUP BY clause.GROUP BY clauseWHERE clause whereas we can use aggregate functions with HAVING ClauseNote: When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.
Answer: A stored procedure is a set of SQL statements with an assigned name, which are stored in a database as a group so it can be reused whenever we want.
Answer: The difference between stored procedure and function are as follow:
| Stored Procedure | Function |
| Stored Procedure can return zero, single or multiple values. | Function must return a value (which may be a scalar or a table). |
| Stored Procedure can have input/output parameter. | Only input parameter. |
| We can use transaction in Stored Procedure. | We can't use transaction in User Defined Functions. |
| We can call a function from a Stored Procedure. | We can't call Stored Procedure from function. |
| We can't use Stored Procedure in SELECT/ WHERE/ HAVING statement. | We can use User Defined Functions in SELECT/ WHERE/ HAVING statement. |
| We can use exception handling using Try-Catch block in Stored Procedure. | We can't use Try-Catch block in User Defined Functions. |
| Stored Procedure can use temporary tables. | Function can not use temporary tables. |
Answer:
Answer: A SQL trigger is a special type of stored procedure. A SQL trigger is a set of SQL statements stored in the database. A SQL trigger is executed or fired whenever an event associated with a table occurs, for example, insert, update or delete query fired on a table. It is special because it is not called directly like a stored procedure.
Answer: Yes, we can.
SET IDENTITY_INSERT TABLE1 ON
INSERT INTO TABLE1 (ID,NAME)
SELECT ID,NAME FROM TEMPTB1;
SET IDENTITY_INSERT OFFAnswer:
with cte as
(
select salary,DENSE_RANK() over (order by salary desc) as DenseRank from Employees
)
select top 1 salary from cte where DenseRank=5;Answer: @@SPID Returns the session ID of the current user process.
For Example, This example returns the session ID, login name, and user name for the current user process. A query is as follow:
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name';ID Login Name User Name
------ ------------------------------ --- ------------------------------
55 LAPTOP-LH3JFI6M\GOD dbo
Answer: Magic Tables are the specially created table which is available only inside of a trigger when we perform the insert, update, and delete operations. The tables "INSERTED" and "DELETED" are called magic tables of the SQL Server.
We can not see these tables in the database. But we can access these tables from the "TRIGGER".
Answer: A query will be:
Select * from sys.objects where type='tr';Answer:
TRUNCATE: Removes all rows from a table without logging the individual row deletions.
DROP: Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables.
Answer: No, NULL values are not the same as that of ZERO or a blank space. NULL value represents an absence of value whereas Zero is a number value. It is definite with precise mathematical properties and blank space is a character or empty string value.
Answer: UserNames and Passwords are stored in sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.
Answer: The difference between NVARCHAR and VARCHAR types are as follow:
| NVARCHAR | VARCHAR |
| NVARCHAR stores UNICODE data. If you have requirements to store UNICODE or multilingual data, NVARCHAR is the choice. | VARCHAR stores ASCII data and should be your data type of choice for normal use. |
| NVARCHAR uses 2 bytes per character. | VARCHAR uses 1 byte per character. |
| Optional parameter n value can be from 1 to 4000.Can store maximum 4000 Unicode/Non-Unicode characters. | Optional Parameter n value can be from 1 to 8000.Can store maximum 8000 Non-Unicode characters. |
| Max indicates that the maximum storage size is 2^31-1 bytes (2 GB). | Max indicates that the maximum storage size is 2^31-1 bytes (2 GB). |
Answer:
SET NOCOUNT ON: This prevents the message from showing which contains the number of affected rows.
SET NOCOUNT OFF: This shows the number of affected rows in a message window.
Note: Using SET NOCOUNT ON within a stored procedure can improve the performance of the stored procedure by a significant margin.
Answer: A CTE or common table expression is an expression which contains temporary result set which is defined in a SQL statement.
Answer:
Select * Into From Where 1 = 0;Note: that this will not copy indexes, keys, etc.
Answer: Both GETDATE and SYSDATETIME are used to fetch the current date time of the server. There is a slight difference between GETDATE and SYSDATETIME is that:
For GETDATE, the precision is till milliseconds and in case of SYSDATETIME, the precision is till nanoseconds.
SELECT GETDATE() as 'GetDate Function', SYSDATETIME() as 'SysDateTime Function';GetDate Function SysDateTime Function
--------------------------------- ----------------------------------------
2019-05-25 17:38:22.130 2019-05-25 17:38:22.1327587
Answer: Use these query to fetch the alternate records from a table
--For even:-
Select studentId from (Select rowID, studentId from student) where mod(rowID,2)=0;
--or for odd:-
Select studentId from (Select rowID, studentId from student) where mod(rowID,2)=1;Answer: The query used to fetch the first 5 characters of the string are as follow:
Select SUBSTRING('Tutorialsrack',1,5) as 'First 5 Characters';
First 5 Characters
----------------------
Tutor
Select LEFT('Tutorialsrack',5) as 'First 5 Characters';First 5 Characters
-----------------------
Tutor
Answer: These are the most common aggregate functions in SQL:
Answer:
In MySQL:
select id from tableName order by id desc limit 1;In SQL Server:
select top 1 id from tableName order by id desc;Answer: SQL injection is one of the most common web hacking techniques. SQL Injection is a code injection technique which is used to attack data-driven applications. Using this technique, malicious SQL statements are inserted into an input field for execution using webpage input (e.g. to dump the database contents to the attacker).
Answer: If both tables are truly the same schema:
INSERT INTO newTable
SELECT * FROM oldTable;Otherwise, you'll have to specify the column names:
INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable;Answer: Use this statement for Update views:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;Answer: Use this statement for updating F as M and M as F:
UPDATE TestTable SET Gender = CASE Gender WHEN 'F' THEN 'M' ELSE 'F' END;Answer:
ELSE clause.ELSE part and no conditions are true, then it returns NULL.Answer: Use this statement to get all the tables from a database:
SELECT * FROM sys.Tables;Answer:
Exec WITH RECOMPILE ;or we can include WITHRECOMPILE in the stored procedure itself.
I hope this article will help you to prepare for your SQL Interview. Share your valuable feedback and help us to improve. If you find anything incorrect, or you want to share more information about the topic discussed above. please post your comment at the bottom of this article. Thank you!
Comments