Business Sql Interview Question Pdf


Monday, May 27, 2019

Top 60 Advanced SQL Interview Questions And Answers Pdf, You can crack the interview to achieve your Provide Tutorial Videos Scenario based. SQL Interview Questions with Answers A stored procedure is a named group of SQL statements that have been previously created and stored in the server. SQL Interview Questions for developers. We cover basic & advanced SQL Server Interview Questions and Answers. Read to crack SQL.

Language:English, Spanish, German
Published (Last):04.05.2015
ePub File Size:29.61 MB
PDF File Size:18.68 MB
Distribution:Free* [*Regsitration Required]
Uploaded by: ANTONE The Conscious Parent. SQL Server Interview Questions and Answers For All Database Developers and. SQL Server. Interview Questions and Answers. For All Database Developers and. Developers Administrators. Pinal Dave. Vinod Kumar. The Char type uses the entire declared length even if fewer characters are stored in the variable. Practically loads empty spaces. The Varchar type does not use.

The following is the process for sending emails from database. It is also possible to query the data by using OpenXML. The following is a procedure for retrieving xml data: Or Store them in a folder and establish the pointer to link them in the database.

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. 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. 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. It is the process of converting a lot of low level locks into higher level locks. Failover clustering is mainly used for data availability. Typically, in a failover cluster, there are two machines. It should be disabled immediately thereafter. For any disaster recovery, the account will be automatically enabled. It should not be used for normal operations. It can be described in terms of: Career-oriented Training. Thanks for making this platform available.

I hope to bring more people from Nigeria to embrace intellipaat as the bridge to filling the gaps in their career needs.

Good Experience. I will definitely recommend this training to others as well as plan to attend other courses from intellipaat.. This training was very informative and helped a lot in understanding Hadoop. You all do a fantastic job! Pls stay in touch. Thank You for sharing these interview questions. These are really helpful and essential questions for interview. I guess if you know SQL Server you can find many ways to figure things out.

I follow best practice, best performance method. What about all the new functionality available with SQL Server ? These are some basic but very essential questions an Sql professional should know. Thanks for sharing. Indeed it helps to do a self evaluatio. You must be logged in to post a comment. How our support Works? Remember Me. Don't have an account? Sign up. Already have an account? Hurry up. Sign Up or Log In using. These sample questions are framed by experts from Intellipaat who trains for SQL Training to give you an idea of type of questions which may be asked in interview.

We have taken full care to give correct answers for all the questions. Do comment your thoughts Happy Job Hunting! What is BCP? When is it used? Learn for free! Subscribe to our youtube Channel. Explain the steps needed to Create the scheduled job? Steps to create a Scheduled Job: Right click on jobs and choose Add New. A New Job window will come into view. Give an associated name for the same.

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. Become SQL Certified in 16 hrs. Explain correlated query work? When is the Explicit Cursor Used?

Find What is Wrong in this Query? 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. The starting position is optional. LEN textData — Returns integer value of the length of the string, excluding trailing blanks.

STUFF textData, start , length , insertTextData — Deletes a specified length of characters and inserts another set of characters at a specified starting point. What are the three ways that Dynamic SQL can be executed?

Writing a query with parameters. Using EXEC. In what version of SQL Server were synonyms released? How do synonyms work and explain its use cases? Synonyms were released with SQL Server 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. If you are a SQL Developer, how can you delete duplicate records in a table with no primary key? If so, what are the commands? 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: Provides ad hoc connection information as part of a four-part object name without using a linked server name.

Executes the specified pass-through query on the specified linked server. Linked Servers: 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. What is the native system stored procedure to execute a command against all databases? The alternative is to use a cursor to process specific commands against each database. How do you maintain database integrity where deletions from one table will automatically cause deletions in another table?

What port does SQL server run on? Explain with an example? What are the risks of storing a hibernate-managed object in cache?

How do you overcome the problems? What is SQL Profiler? Use SQL Profiler to monitor only the events in which you are interested. What are the implications of having it OFF? In this example, Stuff function replaces the string "Intellipaat" onwards the 3rd position 'nte' with 'abc'. In this example, Replace function replaces the occurrence of each 'bc' string with 'xy'.

What is de-normalization in SQL database administration? Give examples. Materialized views for implementation purpose such as: Linking attribute of one relation with other relations. To improve the performance and scalability of web applications. Buffer Cache: Buffer cache is a memory pool in which data pages are read. The ideal performance of the buffer cache is indicated as: Log Caches: Log cache is a memory pool used to read and write the log pages. It is Read answer Explain in brief how SQL server enhances scalability of the database system Read answer What is XPath?

XPath is a language defined by the W3C Read answer What is Log Shipping? UNION command selects distinct and related information from two tables Read answer Explain GO Command. A file or file group can only be used by one database Data definition language is used to define and manage all attributes and properties of a database On the other hand SQL Server supports two security authentication modes On the other hand.

Null means no entry has been made.. UNION command selects distinct and related information from two tables. It implies that the value is either unknown or undefined GO Command is used to signal the end of a batch For example Read answer What is the difference between a Local and a Global temporary table? Read answer Explain Data Definition Language You can also add a gauge to the design surface to show a single relative value.

Adding sub-report. It has minimum of one row group and one column group.. Matrix — A matrix data region display data in pivot table format.

500 SQL Server Interview Questions and Answers - SQL FAQ PDF

Display your data in graphic format using Chart Region The calculation is performed on dataset by report server. July 7. A various chart types are available in SSRS namely line. MIN 7.

The list can be used to display multiple table and matrix.. Matrix 3. In SSRS Sub-report is a stand-alone report which can be link to another report based on its content using parameter.

The following are various aggregate functions available: You can perform grouping. STUFF function is used to insert a string into another string by deleting some characters specified. There are various ways in which you can enhance your report: List 4.

Using expression. It is the timing of the operation that differs from the alias columns. Using custom code. A list data region is free layout..

This can be used in a table or matrix to show the relative value of a field in a range of values in the data region. COUNT 4. If couple of reports are related.

AVG 3. May 7. Table 2. STDEV 8. It is useful for displaying data grouped by row.

Add custom fields. A local temporary table lives until the connection is valid or until the duration of a compound statement. Use sorting. Table Data region has fixed tabular structure i. Sql Server interview. Data regions are report items used to display data from a single dataset.

You can have maximum of 1 report item per cell. Read answer Sql Server interview. MAX 6. What are various ways to enhance the SSRS report? SUM 2. The size of table depends on number of rows dataset fetches i.. It is useful for complex reporting resign..

Custom fields provide with same functionality as alias columns provide in SQL server query. Chart — This data region is for displays the data graphically i.. Add document map navigational links to report item once report is rendered to report Each getting data from different dataset. The size of matrix data region depends on columns and rows fetched.

How do you integrate the SSRS reports in your application? There are 3 ways in which you can integrate reports into your application: VAR Browser control or Report Viewer Control — In this approach. No separate window opens. Context S. A separate login might be required since we are directly calling the report from report server. Address of report server gets expose to user.

July A user does not come to know that he has moved to different server. It is written in Visual basic and is used throughout the report to to retrieve. Navigating to URL i. Using IFrame. SUM is the aggregate function used for numeric data type. The differences are as follows: Expressions provide us with flexibility to customize our report. Explain use of Expression builder. Month Today. Programmatically sending a web request using SOAP to report server. VARP By default.

TotalPages placed in content. Both the drill down and drill through report provide interactive functionality to the SSRS report. DateSerial Year Today. I want the dataset to display Customer Name etc. While the second method has advantage of being available for multiple reports but it has much of configuration overhead. Security of Data Manipulate data.

The advantage of first method is that it is simple and easy to use but disadvantage is that it is available for that report only. The custom field is very useful for the data manipulation like adding some fields whose value can be calculated based on expression. Custom fields can be defined as alias column of the report since the operation is performed on report server rather than on database server.

The New field dialog box will open. Net and call it using property expression or you can write a custom class library and refer it in report server. We can add custom fields as right click on dataset. Why do we need different type of parameter? You can write the code directly into embedded VB. To add custom class library.

To write custom code. Note that you need to create class library and then compile it before referencing it in your SSRS report. SSRS allows developer to add custom code in your report. If it is calculated. Can we use custom code in SSRS? If so. System-level — Administer the report server globally 2. Item-level roles — User can use any of predefined item-level roles or create their own roles by using combination of predefined item-level roles.

These commands are used to check the consistency of the database like validation tasks. For example — 1. Site User. Item-level — Security at report and dataset level System-level roles are: Browser — can navigate to report and run them.

Pre-defined Item-level roles are: If in the code behind the keyword is directly used into the SQL statement. SQL Server interview questions and answers. Full set of records is retrieved then filtered. When a user logs into reporting services. SQL Injection is an attack in which attacker take the advantage of insecure application over internet by running the SQL command against the database and to steal information from it that too using GUI of the website.

Report Manager manages the security at 2 levels — 1. The attacker tries to inject their own SQL into the statement that the application will use to query the database.

System Administrator — can manage report server and report manager security 2. Reporting services maintain role based security. Publisher — As name suggest.

Content Manager — has all permission at item-level. This attack can happen with the applications in which SQL queries are generated in the code. The performance of the view depends on how good the selected statement the view has. Whereas in case of non-clustered index the leaf level is a pointer to the data. Non-clustered index is faster than clustered index because when we use DML statement on clustered index. The Column List Optional. Materialized view is similar to regular views but the output of select query has been saved to a table.

If the select statement has too many joins then it the view will perform poorly. A table can have only one Clustered Index at a time which is generally created on primary key and can have more than one non clustered indexes maximum up to The leaf level of clustered index is actual data pages of the table.

View shows the latest data all the time while the materialized view only shows the fresh data after its result table is updated either by setting a schedule or based on the change in the underlying tables.

Consider the following SQL statement.

SQL Server Interview Questions

EmpName This way the query can be made more readable and easy to understand. While in the case of materialized view. Give a related name for the job. The Index Scan is preferred only when the table is small. Here the rows with equal salaries will not get same ranks. Index seek is preferred for highly sensitive queries. Click on the "Schedules" in the left menu. A New Job window will appear. You can specify recurring schedules also.

For this function. A SQL job can contain one or more schedules. For using this function first specify the function name. A schedule is basically the time at which sql job will run itself. Then specify the OVER function. So the cost of proportional is the number of rows of that table. Right click on jobs and choose Add New.

Index Seek only touches the rows which qualify and the pages that contain that qualifying rows. The clause specifies the column s that you are going to rank.

Table Scan scans every record of the table. Click next on the "Steps" in the left menu. There you will find a Jobs folder. Index Seek and Index Scan are operation for query tuning in execution plans. The drawback of sparse column is that it requires more space for the non null values.

When we define a column as sparse it requires additional 4 Byte for not null values. Here the rows with equal salaries will get same ranks. When a column there is big number of null then by defining that column as spars column we can save a large amount of disk space.

Name varchar 'Name'. For example. Sparse column is a type of column which is used to optimize storage for null values.

Explicit Cursor When the programmer wants to perform the row by row operations for the result set containing more than one row.

To start. That means it will not redirect your application to other server. Backing up. Similarity -These both command will only delete data of the specified table. To Back up the transaction log of the primary database 2.

This has to be done manually. To create a trace 2. SQL Server opens a work area in memory which is called Cursor. To watch the trace results when the trace runs 4. To replay the trace results 5. These cursors are called implicit cursors. To Copy the transaction log backup to every secondary server 3. They are managed by OPEN. The main functions of Log Shipping are as follows: To store the trace results in a table.

Restoring data. Synchronize the database by copying Transaction logs. Reducing required storage space 4. Length of replacement string String2. Using Fill factor SQL will reserve some space on each index page. This function is used to replace the part of string with some another string.

Snapshot Replication. Merge Replication Merge replication replicate data from multiple sources into a single central database. String to overwrite Example: This process is called page split. Starting Position for overwriting Length. String to be overwritten Position.

No need to restructure existing tables for new data.

This set of rules is called Normalization. The fill factor is a value from 1 through that indicates the percentage of the index page to be left empty.

The default value for fill factor is 0. If the table contains the data which is not changed frequently then we can set the fill factor option to SQL Server will create new index page and transfer some rows from the previous index page to the new index page. Reducing repetitive entries. The 'fill factor' option indicate how full SQL Server will create each index page.

We can specify the refresh of database either continuously or on periodic basis. The initial load will be same as in snapshot replication but later it allows change of data both on subscriber and publisher. String3 Example: Transactional Replication In transactional replication data will be same as in snapshot replication. If we want to reduce the number of page splits then we can use Fill factor option. Increased speed and flexibility of queries.

This function is used to replace all the occurrences of particular string by another string. In this replication data can be refreshed periodically and all data will be copied to another database every time the table is refreshed.

In Snapshot Replication snapshot of one database is transferred to another database. String2 String1. When the table's data is modified frequently.

Top 65 SQL Interview Questions You Must Prepare In 2019

It is not possible to make permanent changes to server environment whereas stored procedure can change some of the server environment. User defined functions do not return output parameters while stored procedure can return output parameters. In the case of failure only committed transaction will be recovered and uncommitted transaction will be rolled back.

We want to know in which stored procedure s table EmpDetails is used. Isolation — This property says that one transaction can not retrive the data that has been modified by any other transaction until its completed. Consistency — This property says that the transaction should be always in consistent state. But in the case of Stored procedure when an error occurs the execution will ignore the error and jumps to the next statement.

The transaction follows all or nothing rule. Replace wouldn't work. The execution of User defined function will be stopped if any error occurred in it. If any of statement fails then the entire transaction fails. Atomicity — Transactions may be set of SQL statements. It is not possible to change the table data with the use of User defined functions but you can do it by using stored procedure.

Durability — When any transaction is committed then it must be persisted. The msdb database contains data of database backups. Stored procedure helps in reducing network traffic and latency. The model is a template database which is used for creating a new user database. The Master database contains catalog and data for all databases of the SQL Server instance and it holds the engine together.

SQL Agent. So it helps to reduce the server overhead. List few advantages of Stored procedure. Because SQL Server cannot start if the master database is not working. When the transaction is committed or rolled back then there is no need to use NOLOCK function because the data is already released by the committed transaction.

SQL Server jobs. We can query on both servers using T-SQL.


Stored procedures provide better security to your data. Test your sql server knowledge with our multiple choice questions! It also enhances application performance. The code of the stored procedure can be changed without affecting application. The tempdb contains temporary objects like global and local temporary tables and stored procedures.

When we want to query on remote database server along with the local database server then we can add the remote SQL server to local SQL server in a same group using the concept called Linked Server. By using stored procedures we can reuse the code. DTS packages.

Stored procedures help us in the encapsulation of the code. Level 6:Stored procedures can also be used for sending mail on demand. A file or file group can only be used by one database A view refers to a logical snapshot based on a table or another view.

For example — 1. It takes a string as an argument and returns it by converting it into uppercase. There are various relationships, namely:.

PERRY from Montana
I love sharing PDF docs rarely . Review my other posts. I absolutely love quidditch.