Sql Interview Questions with 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.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.

6.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.

7.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.

8.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;

9.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.

10. 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.

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

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

12.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.

13.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.

14.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.

15.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.

16.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.

17.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.

18.What port does SQL server run on?

1433 is the standard port for SQL server.

19.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;