Top SQL Interview Questions And Answers

1. Compare SQL & PL/SQL

Criteria	SQL	PL/SQL
What it is	Single query or command execution	Full programming language
What it comprises	Data source for reports, web pages	Application language to build, format and display report, web pages
Characteristic	Declarative in nature	Procedural in nature
Used for	Manipulating data	Creating applications

2. What is BCP? When is it used?

It is a tool used to duplicate enormous quantity of information from tables and views. It does not facsimile the structures same as foundation to target.
BULK INSERT command helps to bring in a data folder into a record, table or view in a user-specific arrangement.

3. When is the UPDATE_STATISTICS command used?

This command is used, ones the processing of large data is done.
When we delete a large number of files, alteration or reproduction takes place in the tables, to be concerned of these changes we need to restructure the indexes This is done UPDATE_STATISTICS.

4. Explain the steps needed to Create the scheduled job?

Steps to create a Scheduled Job :

1.  Connect to the database of SQL server in SQL Server Management Studio. On the SQL Server Agent, we will find a Jobs folder.
2. Right click on jobs and choose Add New.
3. A New Job window will come into view. Give an associated name for the same.
4. Click next on the “Steps” in the left list of options. An SQL job can have multiple steps either in the form of SQL declaration or a stored practice call.
5. Click on the “Schedules” in the left list of options. An SQL job can comprise of one or supplementary schedules. It is basically the instance at which SQL job will jog itself. We can spell out returning schedules also.

5. What are the different types of constraints? Explain primary key, foreign key, unique key & not null constraints?

Constraints are the rules applied on data columns on table. These are used to bound the type of data that can go into a table. This ensures the accuracy and consistency of the data. If there is any violation between the constraint and the data action, the action is aborted by the constraint. The different types of constraints are:

• PRIMARY KEY:

It is a key which helps you to find the data from the table. It must be unique and not null.

• FOREGIN KEY

A foreign key is a field in a relational table that matches the primary key column of another table.https://intellipaat.com/tutorial/sql-tutorial/constraints/

• UNIQUE:

It ensures that all the values in the column are unique.

• Not NULL:

It indicates that the column cannot have null value.

6. When are we going to use truncate and delete?

1. TRUNCATE is a DDL command, whereas DELETE is a DML command.
2. We can’t execute a trigger in case of TRUNCATE whilst with DELETE, we can accomplish a trigger.
3. TRUNCATE is quicker than DELETE, for the reason that when we use DELETE to delete the data, at that time it store the whole statistics in the rollback gap on or after where we can get the data back after removal. In case of TRUNCATE, it will not store data in rollback gap and will unswervingly rub it out. TRUNCATE do not recover the deleted data.
4. We can use any condition in WHERE clause using DELETE but it is not possible with TRUNCATE.5.If a table is referenced by any foreign key constraints, then TRUNCATE won’t work.

Go through SQL course to learn more about SQL.

7. Explain correlated query work?

It’s most important to be attentive of the arrange of operations in an interrelated subquery.
First, a row is processed in the outer doubt.
Then, for that exacting row, the subquery is executed – as a result for each row processed by the outer query, the subquery will also be processed. In correlated subquery, each time a line is worked for Emp1, the subquery will also make a decision on the exacting row’s value for Emp1.Salary and run. And the outer query will move on to the next row, and the subquery will execute for that row’s value of Emp1.Salary.
It will persist in anticipation of the “WHERE (1) = (… )” state is pleased.

8. When is the Explicit Cursor Used ?

If the developer needs to perform the row by row operations for the result set containing more than one row, then he unambiguously declares a pointer with a name. They are managed by OPEN, FETCH and CLOSE.%FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN characteristics are used in all types of pointers.

9. Find What is Wrong in this Query?

SELECT subject_code, AVG (marks) FROM students WHERE AVG(marks) > 75 GROUP BY subject_code;
The WHERE clause cannot be used to restrict groups. Instead, the HAVING clause should be used.

SELECT subject_code, AVG (marks)
FROM students
HAVING AVG(marks) > 75
GROUP BY subject_code;

10. Write the Syntax for STUFF function in an SQL server?

STUFF (String1, Position, Length, String2)
String1 - String to be overwritten
Position - Starting location for overwriting
Length - Length of substitute string
String2- String to overwrite.

11. Name some commands that can be used to manipulate text in T-SQL code. For example, a command that obtains only a portion of the text or replace a text string, etc.

• CHARINDEX( findTextData, textData, [startingPosition] ) – Returns the starting position of the specified expression in a character string. The starting position is optional.
• LEFT( character_expression , integer_expression ) – Returns the left part of a character string with the specified number of characters.
• LEN( textData ) – Returns integer value of the length of the string, excluding trailing blanks.
• LOWER ( character_expression ) – Returns a character expression after converting uppercase character data to lowercase.
• LTRIM( textData) – Removes leading blanks. PATINDEX( findTextData, textData ) – Returns integer value of the starting position of text found in the string.
• REPLACE( textData, findTextData, replaceWithTextData ) – Replaces occurrences of text found in the string with a new value.
• REPLICATE( character_expression , integer_expression ) – Repeats a character expression for a specified number of times.
• REVERSE( character_expression ) – Returns the reverse of a character expression.
• RTRIM( textData) – Removes trailing blanks. SPACE( numberOfSpaces ) – Repeats space value specified number of times.
• STUFF( textData, start , length , insertTextData ) – Deletes a specified length of characters and inserts another set of characters at a specified starting point.
• SUBSTRING( textData, startPosition, length ) – Returns portion of the string.
• UPPER( character_expression ) – Returns a character expression with lowercase character data converted to uppercase.

12. What are the three ways that Dynamic SQL can be executed?

• Writing a query with parameters.
• Using EXEC.
• Using sp_executesql.

Get a clear understanding of SQL in this riveting blog.

13. In what version of SQL Server were synonyms released? How do synonyms work and explain its use cases? Synonyms were released with SQL Server 2005.

• Synonyms enable the reference of another object (View, Table, Stored Procedure or Function) potentially on a different server, database or schema in your environment. In simple words, the original object that is referenced in the whole code is using a completely different underlying object, but no coding changes are necessary. Think of this as an alias as a means to simplify migrations and application testing without the need to make any dependent coding changes.
• Synonyms can offer a great deal of value when converting underlying database objects without breaking front end or middle tier code. This could be useful during a re-architecture or upgrade project.

Become Master of SQL by going through this SQL training.

14. If you are a SQL Developer, how can you delete duplicate records in a table with no primary key?

Use the SET ROWCOUNT command. For instance,
if you have 2 duplicate rows, you would SET ROWCOUNT 1, execute DELETE command and then SET ROWCOUNT 0.

15. Is it possible to import data directly from T-SQL commands without using SQL Server Integration Services? If so, what are the commands?

Yes, six commands are available to import data directly in the T-SQL language. These commands include :

• BCP : The bulk copy (bcp) command of Microsoft SQL Server provides you with the ability to insert large numbers of records directly from the command line. In addition to being a great tool for command-line aficionados, bcp is a powerful tool for those seeking to insert data into a SQL Server database from within a batch file or other programmatic method.
• Bulk Insert : The BULK INSERT statement was introduced in SQL Server 7 and allows you to interact with bcp (bulk copy program) via a script.
• OpenRowSet : The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.
• OPENDATASOURCE : Provides ad hoc connection information as part of a four-part object name without using a linked server name.
• OPENQUERY : Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name.
• Linked Servers : Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle.

16. What is the native system stored procedure to execute a command against all databases?

• The sp_MSforeachdb system stored procedure accepts the @Command parameter which can be exetecuted against all databases. The ‘?’ is used as a placeholder for the database name to execute the same command.
• The alternative is to use a cursor to process specific commands against each database.

17. How can a SQL Developer prevent T-SQL code from running on a production SQL Server?

Use IF logic with the @@SERVERNAME function compared against a string with a RETURN command before any other logic.

18. How do you maintain database integrity where deletions from one table will automatically cause deletions in another table?

You can create a trigger that will automatically delete elements in the second table when elements from the first table are removed.

19. What port does SQL server run on?

1433 is the standard port for SQL server.

Go through this SQL Video to get clear understanding of SQL.

20. What is the SQL CASE statement used for? Explain with an example?

It allows you to embed an if-else like clause in the SELECT clause.

SELECT Employee_Name, CASE Location
WHEN 'alex' THEN Bonus * 2
WHEN 'robin' THEN Bonus *, 5
ELSE Bonus
END
"New Bonus"
FROM  Intellipaat_employee;

Read this blog to learn why SQL Optimization has always been a important aspect of database management.

21. What are the risks of storing a hibernate-managed object in cache? How do you overcome the problems?

The primary problem here is that the object will outlive the session it came from. Lazily loaded properties won’t get loaded if needed later. To overcome the problem, perform cache on the object’s id and class and then retrieve the object in the current session context.

22. When is the use of UPDATE_STATISTICS command ?

Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too often because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

Syntax

UPDATE STATISTICS table_or_indexed_view_name 
    [ 
        { 
            { index_or_statistics__name }
          | ( { index_or_statistics_name } [ ,...n ] ) 
                }
    ] 
    [    WITH 
        [ 
            FULLSCAN 
            | SAMPLE number { PERCENT | ROWS } 
            | RESAMPLE 
              [ ON PARTITIONS ( {  |  } [, …n] ) ]
            |  [ ,...n ]
        ] 
        [ [ , ] [ ALL | COLUMNS | INDEX ] 
        [ [ , ] NORECOMPUTE ] 
        [ [ , ] INCREMENTAL = { ON | OFF } ]
    ] ;

 ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_contant ]

23. What is SQL Profiler?

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.

Use SQL Profiler to monitor only the events in which you are interested.

If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

These are described in SQL’s online reference guide and on SQL community.

24. What command using Query Analyzer will give you the version of SQL server and operating system?

SELECT SERVERPROPERTY (‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’).

25. What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

26. What is the STUFF function and how does it differ from the REPLACE function in SQL?

Stuff function : – This function is used to replace string from the given start position, passed as 2nd argument with string passed as last argument. In Stuff function, 3rd argument defines the number of characters which are going to be replaced.
Syntax :-

STUFF ( character_expression , start , length , replaceWith_expression )

For example :-

Select Stuff ('Intellipaat', 3, 3, 'abc')
This query will return the string "Iabcllipaat". In this example, Stuff function replaces the string "Intellipaat" onwards the 3rd position('nte') with 'abc'.

Replace Function :– Replace function is used to replace all occurrence of a specified with the string passed as last argument.
Syntax :-

REPLACE ( string_expression , string_pattern , string_replacement )

For example :-

Select Replace ('Abcabcabc', 'bc', 'xy')
This query will return the string Axyaxyaxy. In this example, Replace function replaces the occurrence of each 'bc' string with 'xy'.

27. How to get @@ERROR and @@ROWCOUNT at the same time?

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable.

SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

28. What is de-normalization in SQL database administration? Give examples

De-normalization is used to optimize the readability and performance of the database by adding redundant data. It covers the inefficiencies in the relational database software.
De-normalization logical data design tend to improve the query responses by creating rules in the database which are called as constraints.
Examples include the following :

• Materialized views for implementation purpose such as :
• Storing the count of “many” objects in one-to-many relationship.
• Linking attribute of one relation with other relations.
• To improve the performance and scalability of web applications.

29. Can you explain about buffer cash and log Cache in SQL Server?

• Buffer Cache : Buffer cache is a memory pool in which data pages are read. The ideal performance of the buffer cache is indicated as: 95% indicates that pages that were found in the memory are 95% of time. Another 5% is need physical disk access.
  If the value falls below 90%, it is the indication of more physical memory requirement on the server.
• Log Caches : Log cache is a memory pool used to read and write the log pages. A set of cache pages are available in each log cache. The synchronization is reduced between log and data buffers by managing log cache separately from the buffer cache.

30. Describe how to use Linked Server.

MS SQL Server supports the connection to different OLE DB on an ad hoc basis. This persistent connection is referred as Linked Server.
Following are the steps to use Linked Server for any OLE DB. You can refer this to use an MS-Excel workbook.

1. Open SQL Server Management Studio in SQL Server.
2. Expand Server Objects in Object Explorer.
3. Right-click on Linked Servers. Click on New Linked Server.
4. Select General page in the left pane and
   • Type any name for the linked server in the first text box.
   • Select the Other Data Source option.
   • Click on Microsoft Jet 4.0 OLE DB Provider from the Provider list.
   • Type the Excel as the name of the OLE DB data source.
   • Type the full path and file name of the Excel file in Data Source box.
   • Type the Excel version no. (7.0, 8.0 etc) in the Provider String. Use Excel 8.0 for Excel 2000, Excel 2002 or Excel 97.
   • To create a linked server click on OK.

31. How to find second highest salary of an Employee?

There are many ways to find second highest salary of Employees in SQ. You can either use SQL Join or Subquery to solve this problem.
Here is SQL query using Subquery :

Select MAX(Salary) from Intellipaat_emplyee WHERE Salary NOT IN ( select MAX(Salary) from Intellipaat_employee.

32. Explain how to send email from SQL database.

SQL Server has a feature for sending mails. Stored procedures can also be used for sending mail on demand. With SQL Server 2005, MAPI client is not needed for sending mails.
The following is the process for sending emails from database.

• Make sure that the SQL Server Mail account is configured correctly and enable Database Mail.
• Write a script to send an e-mail. The following is the script.

  USE [YourDB]
  EXEC msdb.dbo.sp_send_dbmail
  @recipients = 'xyz@intellipaat.com; abc@intellipaat.com;pqr@intellipaat.com’ 
  @body = ' A warm wish for your future endeavor',
  @subject = 'This mail was sent using Database Mail' ;
  GO

33. How to make remote connection in database?

The following is the process to make a remote connection in database :

1. Use SQL Server Surface Area Configuration Tool for enabling the remote connection in database.
2. Click on Surface Area Configuration for Services and Connections.
3. Click on SQLEXPRESS/Database Engine/RemoteConnections.
4. Select the radio button: Local and Remote Connections and select ‘Using TCP/IP only’ under Local and Remote Connections.
5. Click on OK button / Apply button

34. What is the purpose of OPENXML clause SQL server stored procedure?

OPENXML parses the XML data in SQL Server in an efficient manner. It’s primary ability is to insert XML data to the RDB. It is also possible to query the data by using OpenXML. The path of the XML element needs to be specified by using ‘xpath’.
The following is a procedure for retrieving xml data:

DECLARE @index int
DECLARE @xmlString varchar(8000)
SET @xmlString ='

abc
9343463943/PhoneNo>


xyz
9342673212

'

EXEC sp_xml_preparedocument @index OUTPUT, @xmlString

SELECT * FROM OPENXML (@index, 'Persons/Person') WITH (id varchar(10), Name varchar(100) 'Name' , PhoneNo varchar(50) 'PhoneNo')

EXEC sp_xml_removedocument @index
The above code snippet results the following:
15201 abc 9343463943 
15202 xyz 9342673212

35. How to store pdf file in SQL Server?

Create a column as type ‘blob’ in a table. Read the content of the file and save in ‘blob’ type column in a table.
Or
Store them in a folder and establish the pointer to link them in the database.

36. Explain the use of keyword WITH ENCRYPTION. Create a Store Procedure with Encryption.

It is a way to convert the original text of the stored procedure into encrypted form. The stored procedure gets obfuscated and the output of this is not visible to

CREATE PROCEDURE Abc
WITH ENCRYPTION
AS
<< SELECT statement>>
GO

WITH ENCRYPTION indicates that SQL Server will convert the original text of CREATE PROCEDURE statement to an encrypted format. Users that do not have no access to system tables or database files cannot retrieve the encrypted text. However, the text will be available to privileged users.

Example:

CREATE PROCEDURE salary_sum
WITH ENCRYTION
AS
SELECT sum(salary)
FROM employee
WHERE emp_dept LIKE Develop

37. What is lock escalation?

Lock escalation is used to convert row locks and page locks into table locks thereby “escalating” the smaller or finer locks. This increases the system performance as each lock is nothing but a memory structure. Too many locks would mean more consumption of memory. Hence, escalation is used.
Lock escalation from SQL Server 7.0 onwards is dynamically managed by SQL Server. It is the process of converting a lot of low level locks into higher level locks.

38. What is Failover clustering overview?

Failover clustering is mainly used for data availability. Typically, in a failover cluster, there are two machines.

• One machine provides the basic services and the second is available to run the service when the primary system fails.
• The primary system is monitored periodically to check if it works. This monitoring may be performed by the failover computer or an independent system also called as cluster controller. In an event of failure of primary computer, the failover system takes control.

39. What is Builtin/Administrator?

The Builtin/Administrator account is basically used during some setup to join some machine in the domain. It should be disabled immediately thereafter. For any disaster recovery, the account will be automatically enabled. It should not be used for normal operations.

40. What XML support does the SQL server extend?

SQL Server (server-side) supports 3 major elements :

1. Creation of XML fragments: This is done from the relational data using FOR XML to the select query.
2. Ability to shred xml data to be stored in the database.
3. Finally, storing the xml data.

Client-side XML support in SQL Server is in the form of SQLXML. It can be described in terms of :

• XML Views : providing bidirectional mapping between XML schemas and relational tables.
• Creation of XML Templates : allows creation of dynamic sections in XML.

SQL server can return XML document using FOR XML clause. XML documents can be added to SQL Server database and you can use the OPENXML clause to display the data from the document as a relational result set. SQL Server 2000 supports XPath queries.