Basic SQL dba Interview Q&A Part1

What is RDMS?

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model

What are the Properties of the Relational Tables?

    Six Properties of Relational Tables:

Values Are Atomic
This property implies that columns in a relational table are not repeating group or arrays. The key benefit of the one value property is that it simplifies data manipulation logic. Such tables are referred to as being in the “first normal form” (1NF).

Column Values Are of the Same Kind
In relational terms this means that all values in a column come from the same domain. A domain is a set of values which a column may have. This property simplifies data access because developers and users can be certain of the type of data contained in a given column. It also simplifies data validation. Because all values are from the same domain, the domain can be defined and enforced with the Data Definition Language (DDL) of the database software.

Each Row is Unique
This property ensures that no two rows in a relational table are identical; there is at least one column, or set of columns, the values of which uniquely identify each row in the table. Such columns are called primary keys. This property guarantees that every row in a relational table is meaningful and that a specific row can be identified by specifying the primary key value.

The Sequence of Columns is Insignificant
This property states that the ordering of the columns in the relational table has no meaning. Columns can be retrieved in any order and in various sequences. The benefit of this property is that it enables many users to share the same table without concern of how the table is organized. It also permits the physical structure of the database to change without affecting the relational tables.

The Sequence of Rows is Insignificant
This property is analogous the one above but applies to rows instead of columns. The main benefit is that the rows of a relational table can be retrieved in different order and sequences. Adding information to a relational table is simplified and does not affect existing queries.

Each Column Has a Unique Name
Because the sequence of columns is insignificant, columns must be referenced by name and not by position. A column name need not be unique within an entire database but only within the table to which it belongs.

What is Normalization?

A set of database design rules that minimizes data redundancy and results in a database in which the database engine and application software can easily enforce integrity.

What is De-normalization?

To introduce redundancy into a table in order to incorporate data from a related table. The related table can then be eliminated. Denormalization can improve efficiency and performance by reducing complexity in a data warehouse schema

How is ACID property related to Database?

is a set of properties that you would like to apply when modifying a database.

• Atomocity: all database operations with in a transaction must complete successfully, if one of database operations fails, entire transaction should fail.
• Consistency:The transaction should record only the consistent data means it should  insert only valid data in the database
• Isolation: Isolation indicates how the transaction are behaving while multiple users accessing the same piece of data
• Durability: When the transaction committed(completed)

What are the Different Normalization Forms?

1.    First Normal Form

Eliminate repeating groups in individual tables.

Create a separate table for each set of related data.

Identify each set of related data with a primary key.

2.    Second Normal Form

Create separate tables for sets of values that apply to multiple records.

Relate these tables with a foreign key.

3.    Third Normal Form

Eliminate fields that do not depend on the key.

What is a Stored Procedure?

       By putting a piece of SQL code into a stored procedure, you have a way to reuse the code. When you want to update the code or modify it for use in another application, all you have to do is make the change in one place. That means more maintainable code and less time trying to track down problems.

What is Trigger?

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.

1. After Triggers (For Triggers)
2. Instead Of Triggers

What is View?

A View is a "Virtual Table". It is not like a simple table, but is a virtual table which contains columns and data from different tables (may be one or more tables). A View does not contain any data, it is a set of queries that are applied to one or more tables that is stored within the database as an object. After creating a view from some table(s), it used as a reference of those tables and when executed, it shows only those data which are already 

mentioned in the query during the creation of the View.

What is an Index?

An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes, they are just used to speed up searches/queries.

What is Linked Server ?

Linked Servers allows you to connect to other database instances on the same server or on another machine or remote servers. 

It allows SQL Server to execute SQL scripts against OLE DB data sources on remote servers using OLE DB providers. 

The remote servers can be SQL Server, Oracle etc. which means those databases that support OLE DB can be used for linking servers. 

After setting up the Linked Servers we can easily access the other server tables, procedures etc. 

How to setup Linked Servers?

This can be done in two ways.

1. Using Transact-SQL

Using the following syntax we can create a Linked Server.



Example:

This will create a Linked Server; we can view this from Management Studio. 

what is Cursor?

Introduction: What is a Cursor? A Cursor is a SQL Object, or we can say like a virtual table that retrieves data from the table one row at a time. We use cursors when we need to update records in a database table in singleton fashion in other words row by row. Life Cycle of Cursor: Declare Cursor - > Open -> Fetch - > Close -> Deallocate  What is Collation? Collation refers to a set of rules that determine how the data is stored and compared.Character data is stored using rules that define the correct character sequence, with options for specifying  case-sensitivity, accent marks, and character width. What is Subquery? Explain the proeperties of Subquery? A subquery is a SELECT statement with another SQL statement,  Subqueries are further classified as either a correlated subquery or a nested subquery.

What are different types of Joins?

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

There are many types of join.

• Inner Join

1. Equi-join
2. Natural Join

• Outer Join

1. Left outer Join
2. Right outer join
3. Full outer join

• Cross Join
• Self Join

What is Primary Key and Foreign Key?

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Most tables should have a primary key, and each table can have only ONE primary key.

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

What is user defined functions? what are the user defined functions that can be created?

There are three types of User-Defined functions in SQL Server:

1. Scalar Function
2. Inline Function
3. Multi-statement Table-valued Function

Scalar UDFs 

Scalar UDFs return a single value. They are similar to built-in functions such as GETDATE(), or OBJECT_NAME(), which return a single string, date, or integer. The value returned by a scalar UDF can be based on the parameters passed.
Scalar UDFs can return any scalar system-supplied data type, except TIMESTAMP. You cannot return values with a user-defined data type from scalar UDFs. If you want to do so, you must specify the underlying system-supplied data type instead. 

In-line UDF
In-line UDFs return a single row or multiple rows and can contain a single SELECT statement. Because in-line UDFs are limited to a single SELECT, they can't contain much logic. They can be effective, however, for lookups that return multiple values, such as the top five best-selling books with title, author, and publication date.

Multi-statement UDFs 

The multi-statement UDFs can contain any number of statements that populate the table variable to be returned. Notice that although you can use INSERT, UPDATE, and DELETE statements against the table variable being returned, a function cannot modify data in permanent tables. Multi-statement UDFs come in handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.

what is Data warehousing?

Online transaction processing (OLTP) database contains huge transaction records. Sometimes, it has millions and billions of rows. These data are stored in relational database. When business requires analysis of these transactions, it takes huge amount of time to process these data. Business user wants to analyse these data in small time period. They don't want to have complexity in schema too. Sometimes, business user wants to analyse data from other source too. These other source data could be from different business sources. So database designer comes with a solution to implement Data warehouse system for business data. This system is separate from OLTP database. So, analysis process becomes easier than before.

What languages BI uses to achieve the goal?

MDX – Multidimensional Expressions
DMX – Data Mining Extensions
XMLA – XML for Analysis

What is an Identity?

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set.

What is Standby Servers? Explain Types of Standby Servers?

Standby Server is a type of server that can be brought online in a situation when Primary Server goes offline and application needs continuous (high) availability of the server. There is always a need to set up a mechanism where data and objects from primary server are moved to secondary (standby) server. This mechanism usually involves the process of moving backup from the primary server to the secondary server using T-SQL scripts. Often, database wizards are used to set up this process.

Different types of standby servers are given as follows:

1) Hot Standby:

Hot Standby can be achieved in the SQL Server using SQL Server 2005 Enterprise Edition and the later enterprise versions. SQL Server 2005 has introduced Mirroring of database that can be configured for automatic failover in a disaster situation. In the case of synchronous mirroring, the database is replicated to both the servers simultaneously. This is a little expensive but provides the best high availability. In this case, both primary and standby servers have same data all the time.

2) Warm Standby:

In Warm Standby, automatic failover is not configured. This is usually set up using Log Shipping or asynchronous mirroring. Sometimes warm standby is lagging by a few minutes or seconds, which results into loss of few latest updates when the primary server fails and secondary server needs to come online. Sometimes a warm standby server that is lagging by a few transactions is brought back to the current state by applying the recent transaction log.

3) Cold Standby:

Code Standby servers need to be switched manually, and sometimes all the backups as well as the required OS need to be applied. Cold Standby just physically replaces the previous server.

What is Dirty Read?

A dirty read occurs when two operations, say, read and write occur together giving the incorrect or unedited data. Suppose, A changed a row but did not committed the changes. B reads the uncommitted data but his view of the data may be wrong so that is Dirty Read.

Why can’t I use Outer Join in an Indexed View?

Rows can logically disappear from an indexed view based on OUTER JOIN when you insert data into a base table. This makes incrementally updating OUTER JOIN views relatively complex to implement, and the performance of the implementation would be slower than for views based on standard (INNER) JOIN.

What is the Correct Order of the Logical Query Processing Phases?

The correct order of the Logical Query Processing Phases is as follows:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. TOP
11. ORDER BY

Which TCP/IP port does the SQL Server run on? How can it be Changed?

By default (unless changed) the default TCP Port for SQL Server is 1433.

What are the Difference between Clustered and a Non-clustered Index?

Cluster index sorts the data with respect to the column on which it is applied, where as non-clustered index do not, it is just a logical sorting. Thus we can have only single clustered index and multiple non-clustered index on a table. Primary index on a table is clustered index. By default CREATE INDEX statement creates a non-clustered index

what are the difference index configurations a table can have?

A table can have one of the following index Configurations:

1. No indexes
2. A clustered index
3. A clustered index and many nonclustered indexes
4. A nonclustered index
5. Many nonclustered indexes

What is OLTP(Online Transaction Processing)?

OLTP (online transaction processing) is a class of software programs capable of supporting transaction-oriented applications on the Internet.

Typically, OLTP systems are used for order entry, financial transactions, customer relationship management (CRM) and retail sales. Such systems have a large number of users who conduct short transactions. Databasequeries are usually simple, require sub-second response times and return relatively few records

What is difference between DELETE and TRUNCATE  Commands?

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.

What are different type of Locks?

listed below the types of locks available.. 

* Shared (S) 
* Update (U) 
* Exclusive (X) 
* Intent 
* Schema 
* Bulk Update (BU) 
* Key-range 

What are Pessimistic Lock and Optimistic Lock?

1) Optimistic means....the table is open for read/write over entire network for all users/sessions. We can move the cursor, backward or forward dynamically.

2) Pessimestic means... the table is open for read/write only for that current session. The other session users can not edit the same.

When is the use of UPDATED_STATISTICS command?

We add statistics on the columns that don't have statistics in order to boost query performance. UPDATE_STATISTICS Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan but in some cases you can improve query performance by using UPDATE_STATISTICS or the stored procedure sp_updatestats  to update statistics more frequently than the default updates.

UPDATE_STATISTICS command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to updatethe indexes to take these changes into account.