Database Interview Questions and Answers

Answer - 1 : - Lookup tables are like constraints which hold a value.  The content in the lookup tables dosen't change often, hence refered as lookup (reference). Example : Countries table Countries are not added on regular bases hence one can consider the table as a lookup table.  As and when needed in some other table the countries information can be fetched base on PK - Country-Code column. Eg - Emp_Per_Info.

Answer - 2 : - "An attack technique used to exploit web sites by altering backend SQL statements through manipulating application input." SQL Injection happens when a developer accepts user input that is directly placed into a SQL Statement and doesn't properly filter out dangerous characters. This can allow an attacker to not only steal data from your database, but also modify and delete it. Certain SQL Servers such as Microsoft SQL Server contain Stored and Extended Procedures (database server functions). If an attacker can obtain access to these Procedures it may be possible to compromise the entire machine. Attackers commonly insert single qoutes into a URL's query string, or into a forms input field to test for SQL Injection. If an attacker receives an error message like the one below there is a good chance that the application is vulnerable to SQL Injection.

Answer - 3 : - When an attacker executes SQL Injection attacks sometimes the server responds with error messages from the database server complaining that the SQL Query's syntax is incorrect. Blind SQL injection is identical to normal SQL Injection except that when an attacker attempts to exploit an application rather then getting a useful error message they get a generic page specified by the developer instead. This makes exploiting a potential SQL Injection attack more difficult but not impossible. An attacker can still steal data by asking a series of True and False questions through sql statements.

Answer - 4 : - Unnormalized relation will contain update,insert,deletion anamolies. 1nf will remove repeating colums. 2nf will remove subsets of data. In 2nf every non-key attribute should depend on entire primary key In 3nf, all columns should depend directly upon primary key and transitive dependency should be removed(a->b b->c c->a). In 4nf, multivalued dependancy should be removed. In BCNF, every determinant should be a candidate key.

Answer - 5 : - A data warehouse in an Enterprise reporting solution. It will typically hold all historical data for the company for all time. A datamart is a smaller version of the data warehouse. It's going to hold a year or two's worth of information, and may not hold all the tables in the data warehouse. While the data warehouse is for the enterprise, a datamart is typically for a departments use.

Answer - 6 : - A relational database management system (RDBMS) is a program that lets you create, update, and administer a relational database

Answer - 7 : - Setup a job on each server which contains code to send you an email. Setup an alert on the SQL Agent which monitors for event Id 1480. When the event fires have it run the job.

Answer - 8 : -  Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular commercial and open source databases currently in use are based on the relational model. A short definition of an RDBMS may be a DBMS in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.  

Answer - 9 : - Rule 0: The system must qualify as relational, as a database, and as a management system. For a system to qualify as a relational database management system (RDBMS), that system must use its relational facilities (exclusively) to manage the database. Rule 1  : The information rule: All information in the database is to be represented in one and only one way, namely by values in column positions within rows of tables. Rule 2: The guaranteed access rule: All data must be accessible with no ambiguity. This rule is essentially a restatement of the fundamental requirement for primary keys. It says that every individual scalar value in the database must be logically addressable by specifying the name of the containing table, the name of the containing column and the primary key value of the containing row. Rule 3: Systematic treatment of null values: The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number," in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way. Rule 4: Active online catalog based on the relational model: The system must support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language. That is, users must be able to access the database's structure (catalog) using the same query language that they use to access the database's data. Rule 5: The comprehensive data sublanguage rule: The system must support at least one relational language that (a) Has a linear syntax (b) Can be used both interactively and within application programs, (c) Supports data definition operations (including view definitions), data manipulation operations (update as well as retrieval), security and integrity constraints, and transaction management operations (begin, commit, and rollback). Rule 6: The view updating rule: All views that are theoretically upd

Answer - 10 : - A DBMS has to be persistent, that is it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods independent of a specific application for accessing the information that is stored. RDBMS is a Relational Data Base Management System Relational DBMS. This adds the additional condition that the system supports a tabular structure for the data, with enforced relationships between the tables. This excludes the databases that don't support a tabular structure or don't enforce relationships between tables. Many DBA's think that RDBMS is a Client Server Database system but thats not the case with RDBMS. Yes you can say DBMS does not impose any constraints or security with regard to data manipulation it is user or the programmer responsibility to ensure the ACID PROPERTY of the database whereas the rdbms is more with this regard bcz rdbms difine the integrity constraint for the purpose of holding ACID PROPERTY.