Interview questions with answers on SQL VOL-III ~ Academic Projects & Tech Interview

Q.1 What is Index?

Ans: An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name.

Q.2 What is the difference between clustered and a non-clustered index?

Ans: A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.A Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

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

Ans: 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. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Q.4 What is log shipping?

Ans: Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server.

Q.5 What are primary keys and foreign keys?

Ans: Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most undamental of all keys and constraints. A table can have only one Primary key.Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

Q.6 What are check constraint?

Ans: A Check constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

Q.7 What is an Data Abtration?

Ans: A major purpose of a database system is to provide users with an abstract view of the data.There are three levels of data abstraction

Physical level
Logical level
View level

Q.8 What is a Database instance?

Ans: A database instance (Server) is a set of memory structure and background processes that access a set of database files.

Q.9 What are Roles?

Ans: Roles are named groups of related privileges that are granted to users or other roles.

Q.10 What is SQLPlus?

Ans: SQLPlus is an application that recognizes & executes SQL commands & specialized SQL*Plus commands that can customize reports, provide help & edit facility & maintain system variables.

Q.11 What is the difference between normalization and denormalization?

Ans:

Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier.

Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation.

Q.12 What is a trigger?

Ans: Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete).Triggers are executed automatically on occurance of one of the data-modification operations.

Q.13 What is the difference between static and dynamic SQL?

Ans: Static SQL is hard-coded in a program when the programmer knows the statements to be executed.Dynamic SQL the program must dynamically allocate memory to receive the query results.

Q.14 What is UNIQUE KEY constraint?

Ans: A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

Q.15 What is NOT NULL Constraint?

Ans: A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

Q.16 What is meant by query optimization?

Ans: The phase that identifies an efficient execution plan for evaluating a query that has the least estimated cost is referred to as query optimization.

Q.17 What is meant by embedded SQL?

Ans: They are SQL statements that are embedded with in application program and are prepared during the program preparation process before the program is executed.

Q.18 What is File Manager?

Ans: It is a program module, which manages the allocation of space on disk storage and data structure used to represent information stored on a disk.

Q.19 Define Indexes?

Ans: Index is a general term for an Oracle/SQL features used to primarily to speed execution and imposes uniqueness upon certain data. The most important of an index is to ensure uniqueness of rows and help in speedy retrieval of data.

Q.20 What is data integrity?

Ans: Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.

Q.21 What is the difference between static and dynamic SQL?

Ans: Static SQL is hard-coded in a program when the programmer knows the statements to be executed.For dynamic SQL the program must dynamically allocate memory to receive the query results.

Q.22 Define Unique Key?

Ans: Unique key is a one or more column that must be unique for each row of the table.It is similar to primary key. Primary key column will not accept a null. Whereas the unique key column will accept a null values.

Q.23 Define Synonym?

Ans: Synonym is an alternative method to creating a view that includes the entire table or view from another user it to create a synonym.A synonym is a name assigned to a table or view that may thereafter be used to refer to it.

Q.24 What is Transaction Manager?

Ans: It is a program module, which ensures that database, remains in a consistent state despite system failures and concurrent transaction execution proceeds without conflicting.

Q.25 What kind of User-Defined Functions can be created?

Ans: There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.

Q.26 What is De-normalization?

Ans: De-normalization is the process of attempting to optimize the performance of a database by adding redundant data.De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

Q.27 Define transaction?

Ans: A collection of operations that fom a single logical unit of works are called transaction.

Q.28 Define Constraints?

Ans: Constraints is a rule or restriction concerning a piece of data that is enforced at the data level. A Constraint clause can constrain a single column or group of columns in a table. There are five types of Constraint namely
    Null / Not Null
    Primary Key
    Unique
    Check or Validation
    Foreign Key or References Key

Q.29 What are types of sub-queries?

Ans:

Single-row subquery, where the subquery returns only one row.

Multiple-row subquery, where the subquery returns multiple rows.

Multiple column subquery, where the subquery returns multiple columns.

Q.30 Define Clusters?

Ans: Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. A cluster contains one or more tables, which have one or more column in common among them.

Posted in: SQL,SQL Question