SQL Interview Question And Answer | SQL Interview Question And Answer for Fresher | SQL Server | Set 13

1.Explain Active/Active and Active/Passive cluster configurations?
 Ans: Many Diff are there :
           1.Active/Active :  It is the bassically use for the default nodes, Here first node will be default and second node will be named instance. Both node will be active.
                         Its Move group from cluster administration is possible for both side.
                         System performance will go down, if both resources are in one node.

          2.Active/Passive : Its also basically use for nodes But in this case Only one Active node with default instance. No system performance degradation will be there for this case even if we switchover to the other node.  Both have same configuration.

2.What is Data Compression?
Ans: In the new version of SQl Server 2008 are diff type of Data Compressions :
          1. Row Compression
          2. Page Compression
          3. Dictionary Compression

3.What is the mean of Row Compression?
 Ans: Row compression changes the format of physical storage of data. It minimize the metadata
          In SQL SERVE 2008 Data Com column information, length, offsets etc .associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar.

4.What is the Page Compression?
 Ans: Its is bassically use for the sharing of the two common data b/w rows of the given page. Its have a many tecniques for the compression of the data :
          1. Row Compression
          2. Prefix Compression

5.What is the mean of Dictionary Compression?
 Ans: Dictionary Compression is bassically use for the searching . Seaching bassically the duolicat This Dictionary mainly work is the differentiate between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page. value thoroughout the page,and stores them to CI

6.What is Xml Datatype?
 Ans: Xml is the bassically that type of Datatype which is use for SQL Server Datatype, Its a Xml documents and fragments in a Sql server. Xml bassically a instances that is missing a single top-level element. we can create columns and variables of the xml type and store XML instances in them. The xml data type and associated methods help integrate XML into the relational framework of Server.

7.What do you mean by Leaf Nodes?
 Ans: A Leaf Node is bassically type of Container which is contain the Index terms and horizontal pointer also for other leaf nodes .Which can be many .

8.What do you mean by Branch Node?
 Ans: A Branch Node is bassically contain the Pointers for leaf nodes or the other Branch nodes , Which can be two or more.

9.What is the mean of Root Node?
 Ans: A Root Node is bassically use for containing the node Pointers to branch node which can be only one.

10.What is the mean of UNIONALL?
 Ans: UNIONALL is bassically a command, It is much like a UNION COMMAND, But it is some diff'es like : THis command is select all values.

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 09 | Set 10
Set 11 | Set 13

Read more »

Posted in: SQL Interview Question And Answer,SQL Interview Question And Answer for Fresher,SQL Server

SQL Interview Question And Answer | SQL Interview Question And Answer for Fresher | SQL Server | Set 12

1.What does COMMIT do ?
 Ans: COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.

2.What does ROLLBACK do ?
 Ans: ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

3.What's the maximum size of a row?
 Ans: 8060 bytes.

4.What is lock escalation?
 Ans: Lock escalation is the bassically use for the converting a lot of low level locks like a row lock or the level locks into higher level locks which is like table locks. Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks.

5.What is denormalization and when would you go for it?
 Ans: denormalization is the reverse process of normalization it is also indate the name of denenormalisaiotn . It's maily use for the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

6.What are the Different methods of loading Dimension tables?
 Ans: There are many diffenrt of ways are there which is two different ways to load data in dimension tables. 
            1. Direct (Fast) : It is the bassically use for the loading purpose in this way All the constraints and keys are can be disabled before the data is loaded. Once a tym data is loaded, it is validated against all the constraints and keys. If data is found invalid or dirty it is not included in index and all future processes are skipped on this data.
            2. Conventional (Slow) : It is bassically for the All the constraints and keys are validated against the data before, it is loaded, this way data integrity is maintained.

7.What is ER Diagram?
 Ans: ER Diagrame is stands for tEntity Relationship Diagrams , It  is the bassically a major data modelling tool . A type of diagram used in data modeling for relational data bases. These diagrams show the structure of each table and the links between tables. and It will also help organize the data in our project into entities and define the relationships between the entities. This process has proved to enable the analyst to produce a good database structure so that the data can be stored and retrieved in a most efficient manner. An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database.

8.What is surrogate key?
 Ans: Surrogated keys are always integer or numeric.  It is useful because the natural primary key can change and this makes updates more difficult .Surrogate key is the bassically a type of sunsitution key for the primary key . It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table..

9.What do you mean by Snow schema?
 Ans: Snow Schema is the bassically known as snow flake schema, Its mainly interlinked or may have one to many relationship with other tables. This schema is normalized and results in complex join and very complex query as well as slower results.

10.What do you mean by Star schema?
 Ans: Star Schema is bassically use for got a faster results becouse it is a single fact table with N number of Dimension, which will be linked directly with a fact table. This schema is De-normalized and results in simple join and less complex query as well as faster results.

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 09 | Set 10
Set 11 | Set 13

>

Read more »

Posted in: SQL Interview Question And Answer,SQL Interview Question And Answer for Fresher,SQL Server

SQL Interview Question And Answer | SQL Interview Question And Answer for Fresher | SQL Server | Set 11

1.What are the properties and different Types of Sub-Queries?
 Ans: Properties of Sub-Query
          1.A sub-query must be enclosed in the parenthesis.
          2.A sub-query must be put in the right hand of the comparison operator, and
          3.A sub-query cannot contain an ORDER-BY clause.
          4.A query can contain more than one sub-query.
      Types of Sub-Query
         1.Single-row sub-query, where the sub-query returns only one row.
         2.Multiple-row sub-query, where the sub-query returns multiple rows,. and
         3.Multiple column sub-query, where the sub-query returns multiple columns


2.What is SQL Profiler?
 Ans: SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.

       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.

3.Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
 Ans: Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

4.What is SQLCMD?
 Ans: SqlCmd is bassically a very important for that SQL Server It can be work with two modes :
        1. BATCH and
        2. Intractive Mode
            It is the Enhaceversion of isql which is depricated Eventuallyand osql which is not include in SQL Server 2005 RTM, It is the Better replacement of OSQL and ISQL. It provides way more functionality that other two options. 

5.What is the Dirty Read?
 Ans: Dirty Read is bassically a type of error its generate when two operations access in the same time for the example Two oprations says,read and write occurs togethergiving the incurrect.

6.What is Pivot and Unpivote?
 Ans: A pivot table is bassically use for the sorting the table, and also doing the work in table like a counting and totalling the data which is stored in one table or spread sheet and its also creat the second table for the display the sumrized data,It can be turns the value of a specified column into column names, effectively rotating a table. but in case of Unpivote Its a exactly opposit to the pivote.

7. What is Catalouge Views?
Ans: Catalouge Views is basssically use for the returing the information, it is used by the SQL Server Database Engine.It is the very importnt general interface catalouge to the matadata and Its provide the most efficient way to obtain ,transform, and present costomised forms of this information .all user available catalouge matadata is exposed through catalouge views.

8.What is the mean of Dictionary Compression?
 Ans: Dictionary Compression is bassically use for the searching . Seaching bassically the duolicat This Dictionary mainly work is the differentiate between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page. value thoroughout the page,and stores them to CI

9.What is the Page Compression?
 Ans: Its is bassically use for the sharing of the two common data b/w rows of the given page. Its have a many tecniques for the compression of the data :
        1. Row Compression
        2. Prefix Compression

10.What is the mean of Row Compression?
Ans: Row compression changes the format of physical storage of data. It minimize the metadata
    In SQL SERVE 2008 Data Com column information, length, offsets etc .associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar.

11. What is Data Compression?
Ans: In the new version of SQl Server 2008 are diff type of Data Compressions :
         1. Row Compression
         2. Page Compression
         3. Dictionary Compression

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 09 | Set 10
Set 11 | Set 13

>

Read more »

Posted in: SQL Interview Question And Answer,SQL Interview Question And Answer for Fresher,SQL Server

SQL Interview Question And Answer | SQL Interview Question And Answer for Fresher | SQL Server | Set 10

1.What is an Oracle sequence?
 Ans: A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

2.What is a synonym?
 Ans: A synonym is an alias for a table, view, sequence or program unit.

3.What are the types of synonyms?
 Ans: There are two types of synonyms private and public.

4.What is a private synonym?
 Ans: Only its owner can access a private synonym.

5.What is a public synonym?
 Ans: Any database user can access a public synonym.

6.What are synonyms used for?
 Ans: 1. Mask the real name and owner of an object.
         2. Provide public access to an object
         3. Provide location transparency for tables, views or program units of a remote database.
         4. Simplify the SQL statements for database users.

7.What is an Oracle index?
 Ans: An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

8.How are the index updates?
 Ans: Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.

9.What is Rollback Segment ?
 Ans: A Database contains one or more Rollback Segments to temporarily store "undo" information.

10.What are the Characteristics of Data Files ?
 Ans: A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 09 | Set 10
Set 11 | Set 13

>

Read more »

Posted in: SQL Interview Question And Answer,SQL Interview Question And Answer for Fresher,SQL Server

SQL Interview Question And Answer | SQL Interview Question And Answer for Fresher | SQL Server | Set 09

1.What is the difference between a Local and a Global temporary table?
 Ans: A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
         A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

2.How to get @@ERROR and @@ROWCOUNT at the same time?
 Ans: 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

3.What are the advantages of using Stored Procedures?
 Ans: 1.Stored procedure can reduced network traffic and latency, boosting application performance.
         2.Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
         3.Stored procedures help promote code reuse.
         4.Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
         5.Stored procedures provide better security to your data.

4.Can SQL Servers linked to other servers like Oracle?
 Ans: SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

5.What is the UNION?
 Ans: UNION is the bassically a type of a command which is used for the selection procedure, It is select the related information from the two tables, similarily JOIN command. when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

6.What is the RANK() OVER?
 Ans: It is bassically Return the value as a rank of each row within the partition of a result set.

7.What is the mean of ROW_NUMBER() OVER?
 Ans: It s the sequential no of the row with in the partition pf the resultset,starting at 1 for the First row in each partition. 

8.What is the Ranking functions?
 Ans: Ranking Function is the bassically use for the return value which is the ranking of the each row in the partion. These ranking functins are non Deterministic. there are many diff type of ranking functions :
         1.ROW_NUMBER() OVER([<Partion_By_Clause>]<Order_By_Clause>)
         2.RANK() OVER([<Partion_By_Clause>]<Order_By_Clause>)
         3.DENSE_RANK() OVER([<Partion_By_Clause>]<Order_By_Clause>)

9.What is the ROW_NUMBER()?
 Ans: Row Function is bassically use for the retuning a coulumns which is type of expressions. which is contain the rows no with in the result set.This is only a number used in the context of the result set, if the result changes, the ROW_NUMBER() will change

10.What is an Aggregate Function?
 Ans: Aggregation Function is basically a type of function, which is use for the calculation on the set of values and It is also return the single value , Its also have a HAVING CLAUSE, Which is used along with GROUP BY.for filtering query using aggregate values, Its ignore NULL Values excpt COUNT Function, Its have a many functions which is there :

        1. AVG
        2. MIN
        3. CHECKSUM_AGG
        4. SUM
        5. COUNT
        6. STDEV
        7. COUNT_BIG
        8. STDEVP
        9. GROUPING
        10. VAR
        11. MAX
        12. VARP

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 10

>

Read more »

Posted in: SQL Interview Question And Answer,SQL Interview Question And Answer for Fresher,SQL Server

SQL Interview Question And Answer | SQL Interview Question And Answer for Fresher | SQL Server | Set 08

1.What is Transaction ?
  Ans: A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.

2.What is Read-Only Transaction ?
  Ans: A Read-Only transaction ensures that the results of each query executed in the transaction are consistant with respect to the same point in time.

3.What is a deadlock ? Explain .
  Ans: Two processes wating to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
        These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally

4.What do you mean by Live Lock?
  Ans: A livelock is a single lock which is bassically work on the request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely. When four denials are cmplete then SQL Server detects the situation and refuses further shared locks.

5.What is a cluster Key ?
  Ans: The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.

6.What is Parallel Server ?
  Ans: Multiple instances accessing the same database (Only In Multi-CPU environments)

7.What are the advantages of views?
  Ans: 1.Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
         2.Hide data complexity.
         3. Simplify commands for the user.
         4.Present the data in a different perspective from that of the base table.
         5.Store complex queries.

8.What are different types of Collation Sensitivity?
  Ans: 1.Case sensitivity - A and a, B and b, etc.
         2.Accent sensitivity
         3.Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
         4.Width sensitivity - A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive.

9.When is the use of UPDATE_STATISTICS command?
  Ans: This 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 update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

10.What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

 Ans: They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 09 | Set 10
Set 11 | Set 13

>

Read more »

Posted in: SQL Interview Question And Answer,SQL Interview Question And Answer for Fresher,SQL Server

SQL Interview Question And Answer | SQL Interview Question And Answer for Fresher | SQL Server | Set 07

1. What is RDBMS?
 Ans: Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

2.What are the properties of the Relational tables?
 Ans: Relational tables have six properties:

       1.Values are atomic.
       2.Column values are of the same kind.
       3.Each row is unique.
       4.The sequence of columns is insignificant.
       5.The sequence of rows is insignificant.
       6.Each column must have a unique name.

3.What are Schema Objects?
 Ans: Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

4.Can objects of the same schema reside in different tablespaces?
 Ans: Yes.

5.Can a tablespace hold objects from different schemes?
 Ans: Yes.

6.What is Oracle table?
Ans: A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

7.What is an Oracle view?
 Ans: A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

8.What is Partial Backup ?
 Ans: A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

9.What is Mirrored on-line Redo Log ?
 Ans: A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.

10.Can a view based on another view?
 Ans: Yes.

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 09 | Set 10
Set 11 | Set 13

>

Read more »

Posted in: SQL Interview Question And Answer,SQL Interview Question And Answer for Fresher,SQL Server

SQL Interview Question And Answer | SQL Interview Question And Answer for Fresher | SQL Server | Set-06

1.What is a Linked Server?
 Ans: Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

2.What is a constraint? What are the different constraints available?
 Ans: Constraint is defined as imposing predefined rules on table columns

          There mainly two types of constraints. Those are
           1. Column constraint:  It is specified as part of a column definition and applies only to that column
           2. Table constraint: It is declared independently from a column definition and can apply to more than one column in a table
           These constraints must be used when constraint is applied for more than one column of a table

3.How do we suppress (avoid) duplicate rows in select statement?
 Ans: Distinct is a key word. It is used to suppress duplicate rows in select statement output.

4.What is the difference between count() and count(*)?
Ans: Count () counts  the non-null values  in a column and where as count(*) counts rows of query results and does not depend on the presence or absence of null values in a column. If there are no rows, it returns a value of zero

5.What are the different clauses available in SQL?
 Ans: There are 4 clauses. Those are
            1. where
            2. group by
            3. having
            4. order by

6.How do we use ‘group by’ clause?
 Ans: The group by clause is used in select statement to collect data across multiple records and group the results by one or more columns.

7.How is ‘having’ clause is used?
 Ans: The having clause is used along with the group by clause. The having clause can be used to select and reject row groups. The having clause specifies condition for groups.

8.How many types of triggers are there?
Ans:  1. Insert
          2. Delete
          3. Update
          4. Instead of

9.What is Collation?
 Ans: Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width.

10.What are the authentication modes in SQL Server? How can it be changed?
 Ans: Windows mode and Mixed Mode - SQL and Windows.

         To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 09 | Set 10
Set 11 | Set 13

>

Read more »

Posted in: SQL Interview Question And Answer,SQL Interview Question And Answer for Fresher,SQL Server

SQL Interview Question And Answer | SQL Interview Question And Answer for Fresher | SQL Server | Set-05

1.What is View?
 Ans: View is a virtual table in the database  whose contains are defined  by  a query. To the database user, the view appear just like a real table with a set of named columns and rows of data. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

2.What is TRIGGER?
 Ans: A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table.

3.What is Nested Trigger?
 Ans: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

4.What is an Integrity Constrains ?
 Ans: An integrity constraint is a declarative way to define a business rule for a column of a table.

5.What is an Index ?
 Ans: An Index is an optional structure associated with a table to have direct access to rows,   which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

6.What is Cursor?
 Ans: Cursor is a database object used by applications to manipulate data in a set on a row-by- row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

      In order to work with a cursor we need to perform some steps in the following order:
        1. Declare cursor
        2.Open cursor
        3. Fetch row from the cursor
        4.Process fetched row
        5.Close cursor
        6.Deallocate cursor


7.What is an Index Segment ?
 Ans: Each Index has an Index segment that stores all of its data.

8.What is hit ratio ?
 Ans: It is a measure of well the data cache buffer is handling requests for data. Hit Ratio = (Logical Reads - Physical Reads - Hits Misses)/ Logical Reads.

9.What is a Linked Server?
 Ans: Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

10.What is DataWarehousing?
 Ans: 1.Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
          2.Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
          3.Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting.
          4.Integrated, meaning that the database contains data from most or all of an organization's operational applications, and that this data is made consistent.
   

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 09 | Set 10
Set 11 | Set 13

>

Read more »

Posted in: SQL Interview Question And Answer,SQL Interview Question And Answer for Fresher,SQL Server

SQL Interview Question And Answer | SQL Interview Question And Answer for Fresher | SQL Server | Set-04

1.What is join?
 Ans: SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

2.What is Inner Join?
 Ans: Inner join is a join that selects only those records from both database tables that have matching values.
 
      Syntax:  SELECT column_name(s) FROM table_name1  INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

3.What is Outer Join?
 Ans: Outer join selects all the records from one table and select only those records in second table that have matching values.

      Left Outer Join: Left Outer Join is a join that select all the record from left table(1st table) in database  and select matching rows from the right table(2nd table).

      Syntax: SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

      Right Outer Join: Right Outer Join is a join that select all the records from right table in database and select matching rows from left table in database.

      Syntax: SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2  ON table_name1.column_name=table_name2.column_name

4.What is Self JOin?
 Ans: SQL self-join simply is a normal join which is used to join a table to itself.

      Syntax: SELECT column_list From table_A  AS A INNER JOIN table_A AS B ON A.column name1 == B.column_name1,… WHERE row_conditions

5.What Storeprocedure?
 Ans: Storeprocedure is a precompiled collection of SQL statement and optional control of flow statements stored under a name and processed as a unit.

  Advantage:
        1.They allow modular programming: we can create the procedure once, store it in the database and we can call it more than one times.
        2. They allow faster execution.
        3.They can reduce network traffic.
        4. Storeprocedure provides a better security to your data.
        5.We can change storeprocedure code with out affecting client.

6.What is difference between DELETE and TRUNCATE?
 Ans: 1.TRUNCATE will actually remove s all the rows from a table after we run the TRUNCAT command
        DELETE command removes rows data from a table based on the condition that we provide with  WHERE clause
        2.TRUNCATE cannot activate TRIGGER. But DELETE can activate TRIGGER.
        3.TRUNCATE can not be RollBack but DELETE can be RollBack.
        4.TRUNCATE is DDL command and DELETE is DML command.
        5.TRUNCATE reset the identity of the table but DELETE does not reset the identity of the table.
7.What is SUB-QUERY?
 Ans:Sub query are often refer to as sub select as they allow a select statement to be executed arbitrarily with in the body of another SQL statement.

8.Difference between VARCHAR and VARCHAR 2?
 Ans: 1.VARCHAR store upto 2000 bytes of character whereas VARCHAR2 store upto 4000 bytes of character.
         2.If  we declare datatype as VARCHAR then it occupy space for NULL values whereas VARCHAR 2 not occupy space for NULL value.

9.What is Clustered Index?
Ans: Clustered Indexes defines the physical shorting of a database table's rows in a storage-media.

10.What is Non Clustered Index?
  Ans: Non Clustered Index are created outside of the database table and contain a shorted list of reference to the table itself.

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 09 | Set 10
Set 11 | Set 13

Read more »

Posted in: SQL Interview Question And Answer,SQL Interview Question And Answer for Fresher,SQL Server

SQL Interview Question And Answer | SQL Interview Question And Answer for Fresher | SQL Server | Set-03

1.What is a transaction and what are ACID properties?
  Ans: A transaction is a logical unit of work in which, all the steps must be performed or none.

          ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.

2.What is non-key attributes?
  Ans: The attributes other than the primary key attributes in a table/relation are called non-key attributes.

3.What is a NULL value?
  Ans: A null value is used to indicate the absence of a value. It is not a zero or blank character. Null cannot be compared to any other value.

4.Why do we use is null, is not null operators?
  Ans: If it is needed to find out the rows, then we have to use is null operator so that we get the rows whose one of the column values  is null and in order to retrieve the rows which does not have null as any column value.

5.What are aggregate functions / column functions?
  Ans: A SQL column function takes an entire column of data as its arguments and produces a single data item that summarizes the column.

           Sum(), avg(), min(),  max(), count() and count(*)

6.What is a tablespace?
  Ans: 1.Tablespace are a mechanism or a means by which logical objects such as tables, indexes, and views are mapped to a specific data file.
           2.A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

7.What is SYSTEM tablespace and when is it created?
 Ans: Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

8.Explain the relationship among database, tablespace and data file ?
  Ans: Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

9.What is schema?
  Ans: A schema is collection of database objects of a user.

         Schema is the over all design of the database

         Schema basically Pronounce skee-ma, It is the structure of a database system,It described in a formal language supported by the database management system (DBMS). In the RDBMS (Relational database System) , the schema defines the tables, the fields in each table, and the Schemas are generally stored in a data dictionary . Even a schema is defined in text database language , the term is often used to refer to a graphical depiction of the database structure.

10.What are Schema Objects?
 Ans: Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 09 | Set 10
Set 11 | Set 13

Read more »

Posted in: SQL Interview Question And Answer,SQL Interview Question And Answer for Fresher,SQL Server

SQL Interview Question And Answer | SQL Server | SQL Interview Question And Answer for Fresher | Set-02

1.What is Functional Dependency?
 Ans: Functional dependency, denoted by X->Y between two set of attributes X and Y, means that the value of Y component are determined by the value of X component. We can also say that Y is functional depend on X.

2.What is the Normalization?
 Ans: Normalization is the process of analyzing relation scheme in term of their functional dependency to achieve the minimizing redundancy and minimizing insertion, deletion and update anomalies.  

        1NF: A relation is said to be in first normal form if all its attributes are atomic.
  
        2NF : A relation is said to be in 2nd normal form if all its non prime attributes are functionally   dependent on prime attributes.

        3NF: A relation is said to be in 3rd  Normal form if it is in 2nd normal form and there is no transitive dependency.
 
3.What is de-normalization and when would you go for it?
 Ans: As the name indicates, de-normalization is the reverse process of normalization. It is the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

4.How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
 Ans: One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

        One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

        Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

5.What is Composite Candidate key?
 Ans: A candidate key comprising of two or more attributes.

6.What is non-key attributes?
 Ans: The attributes other than the primary key attributes in a table/relation are called non-key attributes

7.What is an index?
 Ans: An index is a structure that provides rapid access to the rows of a table based on the values of one or more columns.

8.What are the advantages of index?
 Ans: 1.It speeds up the execution of SQL statements with search conditions that refer to the indexed column(s).

         2.It is most appropriate when retrieval of data from tables is more frequent than inserts and updates.

9.What are the disadvantages of having an index?
 Ans: 1.It consumes additional disk space.
   
          2.The index table must be updated every time a row is added to the table and every time the indexed column is updated in existing row. This imposes additional overhead on insert and update statements for the table.

10.Why do we use “where” clause?
 Ans: “Where” is a clause, it used to specify a search condition that limits the number of rows retrieved.

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 09 | Set 10
Set 11 | Set 13

Read more »

Posted in: SQL Interview Question And Answer,SQL Interview Question And Answer for Fresher,SQL Server

SQL Interview Question And Answer | SQL Interview Question And Answer for Fresher | SQL Server | Set-01

1. What is SQL?
 Ans: SQL stands for Structured Query Language. It is a query language used to communicate with a database

2. What is a database query?
  Ans: The process of requesting data from the database and receiving back the results is called a database query.

3.What are the SQL statements?
  Ans::Data definition language (create, alter, drop, truncate)
     Data manipulation language (insert, delete, update)
     Data control language (grant, revoke)
     Data querying/ retrieving language (select)
     Transaction control language (commit, rollback)

4. What is a key?
 Ans:: Key is a pre defined constraint. There are different keys are available. Those are
      
        1.Primary key
        2.Not null
        3.Primary key
        4.Check
        5.Default

5. What is Primary Key?
 Ans: Primary keys are unique identifier for each row.

6. What is Candidate Key?
  Ans:: Candidate key is a combination of attributes that can uniquely identify a database record with out any extronious data.

7. What is Foreign key?
  Ans:: A foreign key is a referential constraint between two tables. That means foreign key identifies a column or set of column in one table that refers to a column or set of column in another table.

8. What is Unique Key?
  Ans:: unique key is a candidate key to uniquely identifies each row in a table. No two distinct rows in a table can have same value in those columns which is declared as unique.

9. What is Composite Key?
  Ans:: A key formed by combining at least two or more columns is called composite key

10. What is the difference between Primary Key and unique Key?
  Ans:: 1. Both primary key and unique key enforces uniqueness of the columns on which they  are defined.
          2.By default Primary key creates a clustered index on the column.
             By default Unique key creates non clustered index.
          3.The major difference is- the primary key does not allow NULL. But Unique key allows  one NULL   only.

Previous

Next

:: Click the links below for similar type Questions and answers ::

Set 01 | Set 02 | Set 03 | Set 04 | Set 05 | Set 06 | Set 07 | Set 08 | Set 09 | Set 10
Set 11 | Set 13

Read more »

Posted in: Competitive Examination,SQL Interview Question And Answer,SQL Server

SQL Server

SQL Server

Read more »

Posted in: SQL Server