Interview questions with answers on SQL VOL-IV

Q.1  What is database Trigger?

Ans:  A database trigger is a PL/SQL block that can defined to automatically execute for insert, update, and delete statements against a table. The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted.

Q.2  What are the different types of data models ?

Ans: 

• Entity relationship model
•  Relational model
•  Hierarchical model
•  Network model
•  Object oriented model
•  Object relational model

Q.3  What is Self Join?

Ans:  A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table.

Q.4  What are the type of Synonyms?

Ans:      There are two types of Synonyms are :

• Private

• Public

Q.5  What is an Integrity Constrains?

Ans:  An integrity constraint is a declarative way to define a business rule for a column of a table.

Q.6  What is referential integrity?

Ans:  Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.

Q.7  What is 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.

Q.8  What is a synonym?

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

Q.9  What is Rollback Segment?

Ans:  A Database contains one or more Rollback Segments to temporarily store "undo" information.

Q.10  What does COMMIT do?

Ans:  A 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.

Q.11  What are defaults? Is there a column to which a default can't be bound?

Ans:  A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. See CREATE DEFUALT in books online.

Q.12  What is diffrence between Co-related sub query and nested sub query?

Ans:  Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

Q.13  What is the use of DBCC commands?

Ans:  DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

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

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

Q.16  What are different type of Collation Sensitivity?

Ans:  The different phases of transaction are

• Kana Sensitivity

• Width sensitivity

• Case sensitivity

• Accent sensitivity

Q.17  What is the difference between a primary key and a unique key?

Ans:  Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

Q.18  What is the difference between Function and Stored Procedure?

Ans: Difference between Function and Stored Procedure

• UDF can be used in the SQL statements anywhere in the WHERE / HAVING / SELECT section where as Stored procedures cannot be.

• UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.

• Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

Q.19  What command do we use to rename a db?

Ans:  sp_renamedb “oldname” , “newname”  If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.

Q.20  What is BCP?

Ans:  BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

Q.21  What is Cross Join?

Ans:  A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

Q.22  What is Storage Manager?

Ans:  It is a program module that provides the interface between the low-level data stored in database, application programs and queries submitted to the system.

Q.23  What is "index covering" of a query?

Ans:  Index covering means that "Data can be found only using indexes, without touching the tables"

Q.24  What are stored-procedures? And what are the advantages of using them?

Ans:  Stored procedures are database objects that perform a user defined operation. A stored procedure can have a set of compound SQL statements. A stored procedure executes the SQL commands and returns the result to the client. Stored procedures are used to reduce network traffic.

Q.25  What is OLTP?

Ans:  Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible.

Q.26  What is DDL (Data Definition Language)?

Ans:  A data base schema is specifies by a set of definitions expressed by a special language called DDL.

Q.27  What is Weak Entity set?

Ans:  An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set.

Q.28  What is a deadlock?

Ans:  Two processes wating to update the rows of a table which are locked by the other process then deadlock arises.

Q.29  What do you mean by flat file database?

Ans:  It is a database in which there are no programs or user access languages. It has no cross-file capabilities but is user-friendly and provides user-interface management.

Q.30  Define candidate key, alternate key, composite key.

Ans:  A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

Posted in: SQL,SQL Question