Environment Sql Interview Questions With Examples Pdf


Thursday, April 25, 2019

Common SQL Interview Questions 2. Practical SQL Query Interview Questions ( SQL Server Queries examples with answers). I don't want to. Here are top 39 objective type sample SQL Interview questions and their answers are given just These sample questions are framed by experts from Intellipaat who trains for SQL Training to give you .. How to store pdf file in SQL Server?. SQL Server. Interview Questions and Answers. For All Database Developers and. Developers Administrators. Pinal Dave. Vinod Kumar.

Language:English, Spanish, Indonesian
Genre:Science & Research
Published (Last):19.12.2015
ePub File Size:21.80 MB
PDF File Size:13.34 MB
Distribution:Free* [*Regsitration Required]
Uploaded by: MONSERRATE

SQL Interview Questions with Answers .. analyze later. For example, you can monitor a production environment to see which stored procedures are hampering . Top 60 Advanced SQL Interview Questions And Answers Pdf, You can crack For Example: SELECT column_name(s) FROM table_name WHERE condition;. Top 50 Basic SQL Interview Questions And Answers pdf will mainly contain In the above example, emp refers to alias name for employee table and dept refers .

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. This increases the system performance as each lock is nothing but a memory structure.

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.

Top 60 Advanced SQL Interview Questions And Answers Pdf

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

Top 50 Basic SQL Interview Questions And Answers Pdf

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: Level 1: Lock escalation threshold is determined dynamically by SQL server. It is a storage technology that has one logical unit consisting of multiple disk drive components. Level Due to this values in the unique key columns may or may not be NULL. Level 2: What is bit data type and what's the information that can be stored inside a bit column?

It is used to store the boolean information of the form 1 true or 0 false. RAID S: Level 0: Primary can only be one in each table as it is one of the special cases of the unique key whereas a unique key can be many.

There are 12 Raid Levels which are as follows: Candidate Key is a key which provides the uniqueness of the column s. Level 7: It adds caching to Level 3 or 4.

Top SQL Interview Questions And Answers

Lock escalation is the process of reducing the overhead of the system by converting many fine grain locks into fewer coarse grain locks.

Define candidate key. Multiple mirrors are created and then stripes over it. Bit data type is the smallest type used in a language. Primary key is a combination of columns which uniquely specify a row whereas a unique key is related to the superkey and can uniquely identify each row in the table.

RAID stands for Redundant array of independent disks which was earlier called as Redundant array of inexpensive disks. Level 3: It identifies each row of a table as unique. Level 4: It increases the performance by replicating and dividing the data through many levels between multiple physical drives. Level 5: This gives assurance of uniqueness of data when the columns are joined together.

Restricting access to data. Making complex queries simple. Ensuring data independence. Providing different views of same data. Auto increment keyword allows the user to create a unique number to get generated whenever a new record is inserted into the table.

The fundamental difference between Union and Union All command is, Union is by default distinct i. Whereas, Union All combines all the rows including duplicates in the result set of different select statements.

Transaction is a set of operations performed in a logical sequence. It is executed as a whole, if any statement in the transaction fails, the whole transaction is marked as failed and not committed to the database. There are 3 types of SQL sandbox, such as. Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. External Access Sandbox: User can have access to files without having a right to manipulate the memory allocation.

Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory. One table can have only one clustered index but multiple nonclustered indexes.

Clustered indexes can be read rapidly rather than non-clustered indexes. Clustered indexes store data physically in the table or view and non-clustered indexes do not store data in table as it has separate structure from data row. Shared locks — Allows data to be read-only Select operations , prevents the data to be updated when in shared lock. Update locks — Applied to resources that can be updated.

There can be only one update lock on a data at a time. Intent locks — A notification mechanism using which a transaction conveys that intends to acquire lock on data. Schema locks- Used for operations when schema or structure of the database is required to be updated. A table is a database object used to store records in a field in the form of columns and rows that holds data. Database testing involves checking the integrity of actual data in the front end with the data present in the database.

Relation or links are between entities that have something to do with each other. Relationships are defined as the connection between the tables in a database. There are various relationships, namely:. One to One Relationship. One to Many Relationship. Many to One Relationship. Self-Referencing Relationship. Implicitly by omitting column from column list.

Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database.

A View can either have all the rows of a table or specific rows based on certain condition. Basic Syntax: Name of the table condition: Condition to select rows For more details on how to create and use view, please refer to this article.

An SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are: This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.

This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on right side, the result-set will contain null. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null. The result-set will contain all the rows from both the tables.

The rows for which there is no matching, the result-set will contain NULL values. To know about each of these joins in details, refer this. The use of indexes and constraints helps in faster retreival and better management of data within the databases.

Whereas in DBMS there is no relation between the tables data may even be stored in files. RDBMS has primary keys and data is stored in tables. DBMS has no concept of primary keys with data stored in navigational or hierarchical form. A field is an entity used for storing a particular type of data within a table like numbers, characters, dates etc. It eliminates all the duplicated values. View Detailed Post. An inner join returns rows when there is at least some matching data between two or more tables that are being compared.

An outer join returns rows from both tables that include the records that are unmatched from one or both the tables. It is a language used to interact with the database, i. Using SQL, we can do many things, for example — we can execute queries, we can insert records in a table, we can update records, we can create a database, we can create a table, we can delete a table etc. SQL Server has a feature for sending mails. Stored procedures can also be used for sending mail on demand. The following is the process for sending emails from database.

Write a script to send an e-mail. The following is the script. SQL commands are the set of commands used to communicate and manage the data present in the database. The different type of SQL commands are-. Select the radio button: Delete command is a DML command, it removes rows from table based on the condition specified in the where clause, being a DML statement we can rollback changes made by delete command.

Truncate is a DDL command, it removes all the rows from table and also frees the space held unlike delete command. It takes lock on the table while delete command takes lock on rows of table. Drop is a DDL command, it removes the complete data along with the table structure unlike truncate command that removes only the rows.

The short answer is no, a table is not allowed to contain multiple primary keys but it allows to have one composite primary key consisting of two or more columns. Denormalization refers to a technique which is used to access data from higher to lower forms of a database. It helps the database managers to increase the performance of the entire infrastructure as it introduces redundancy into a table.

It adds the redundant data into a table by incorporating database queries that combine data from various tables into a single table. The largest number that can be represented in a single byte is or The number of possible values is i. To rename database objects. Constraints are used to specify the limit on the data type of the table. It can be specified while creating or altering the table statement.

The sample of constraints are:. Explain different types of index? There are three types of index namely:. Unique Index: This index does not allow the field to have duplicate values if the column is unique indexed.

If a primary key is defined, a unique index can be applied automatically. Clustered Index: This index reorders the physical order of the table and searches based on the basis of key values. Each table can only have one clustered index. Non-Clustered Index: Non-Clustered Index does not alter the physical order of the table and maintains a logical order of the data.

Each table can have many nonclustered indexes. There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one. The first three normal forms are usually adequate. Third Normal Form 3NF — Dependent solely on the primary key and no other non-key supporting column value.

It is used to ensure that the data transactions are processed reliably in a database system. Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.

Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state. Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error. Trigger in SQL is are a special type of stored procedures that are defined to execute automatically in place or after data modifications.

It allows you to execute a batch of code when an insert, update or any other query is executed against a specific table. Arithmetic Operators Logical Operators Comparison Operators Apart from this SQL Interview Questions blog, if you want to get trained from professionals on this technology, you can opt for a structured training from edureka!

Skip to content 1. What are properties of the transaction? Explain the working of SQL Privileges? This command is used provide database access to user apart from an administrator. This command is used provide database deny or remove access to database objects. It can be rolled back.

The operation cannot be rolled back DROP command removes a table from the database completely. What is Referential Integrity? Why are SQL functions used? SQL functions are used for the following purposes: To perform some calculations on the data To modify individual data items To manipulate the output To format dates and numbers To convert the data types 8.

What do you mean by recursive stored procedure? List some case manipulation functions in SQL? There are three case manipulation functions in SQL, namely: New 51 Machine Learning lnterview questions For example- Select emp. What are aggregate and scalar functions? What is a Stored Procedure? List some advantages and disadvantages of Stored Procedure?

List all the types of user-defined functions? There are three types of user-defined functions, namely: What do you mean by Collation? What are the different types of Collation Sensitivity?

Following are the different types of collation sensitivity: You can go to the below steps to change authentication mode in SQL Server: What is a Data warehouse? What are Local and Global variables? How can you fetch alternate records from a table?

You might also like: ASP.NET WITH C# TUTORIAL PDF

For example- To display even numbers, use the following command: When are we going to use truncate and delete? Explain with an example?

How to find second highest salary of an Employee? Here is SQL query using Subquery:

CHERYLE from Mississippi
I do love fiercely . Look over my other posts. I am highly influenced by snowkiting.