Database Developer Interview Questions And Answers

It is AKA back-end testing or data testing.
Database testing involves in verifying the integrity of data in the front end with the data present in the back end. It validates the schema, database tables, columns, indexes, stored procedures, triggers, data duplication, orphan records, junk records. It involves in updating records in a database and verifying the same on the front end.

DDL commands are used to define or alter the structure of the database.

☛ CREATE: To create databases and database objects
☛ ALTER: To alter existing database objects
☛ DROP: To drop databases and databases objects
☛ TRUNCATE: To remove all records from a table but not its database structure
☛ RENAME: To rename database objects

A temp table is a temporary storage structure to store the data temporarily.

Composite PRIMARY KEY is a primary key created on more than one column (combination of multiple fields) in a table.

In SQL Server, each column in a database table has a name and a data type. We need to decide what type of data to store inside each and every column of a table while creating a SQL table.

The difference between the Delete, Truncate and Drop command is

☛ Delete command is a DML command, it is used to delete rows from a table. It can be rolled back.
☛ Truncate is a DDL command, it is used to delete all the rows from the table and free the space containing the table. It cant be rolled back.
☛ Drop is a DDL command, it removes the complete data along with the table structure(unlike truncate command that removes only the rows). All the tables’ rows, indexes, and privileges will also be removed.

SQL aggregate functions return a single value, calculated from values in a column. Some of the aggregate functions in SQL are as follows

☛ AVG() – This function returns the average value
☛ COUNT() – This function returns the number of rows
☛ MAX() – This function returns the largest value
☛ MIN() – This function returns the smallest value
☛ ROUND() – This function rounds a numeric field to the number of decimals specified
☛ SUM() – This function returns the sum

A table refers to a collection of data in an organised manner in form of rows and columns. A field refers to the number of columns in a table. For example:

Table: StudentInformation
Field: Stu Id, Stu Name, Stu Marks

It’s a 18 character long pseudo column attached with each row of a table.

A database is a collection of information in an organized form for faster and better access, storage and manipulation. It can also be defined as a collection of tables, schema, views and other database objects.

☛ Oracle,
☛ MySQL,
☛ Microsoft SQL Server,
☛ PostgreSQL,
☛ Sybase,
☛ MongoDB,
☛ DB2, and
☛ Microsoft Access etc.,

DCL commands are used to create roles, grant permission and control access to the database objects.

☛ GRANT: To provide user access
☛ DENY: To deny permissions to users
☛ REVOKE: To remove user access

A Subquery is a SQL query within another query. It is a subset of a Select statement whose return values are used in filtering the conditions of the main query.

SQL constraints are the set of rules that enforced some restriction while inserting, deleting or updating of data in the databases.

There should be only one PRIMARY KEY in a table whereas there can be any number of UNIQUE Keys.
PRIMARY KEY doesn’t allow NULL values whereas Unique key allows NULL values.

DEFAULT constraint is used to include a default value in a column when no value is supplied at the time of inserting a record.

By default it is 1433

Where clause is used to fetch data from a database that specifies particular criteria whereas a Having clause is used along with ‘GROUP BY’ to fetch data that meets particular criteria specified by the Aggregate functions. Where clause cannot be used with Aggregate functions, but the Having clause can.

☛ Writing a query with parameters.
☛ Using EXEC.
☛ Using sp_executesql.

Data Integrity defines the accuracy as well as the consistency of the data stored in a database. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

An index refers to a performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and hence it will be faster to retrieve data.

☛ GUI Testing is AKA User Interface Testing or Front-end testing
☛ Database Testing is AKA back-end testing or data testing.
☛ GUI Testing deals with all the testable items that are open to the user to interaction such as Menus, Forms etc.
☛ Database Testing deals with all the testable items that are generally hidden from the user.
☛ The tester who is performing GUI Testing doesn’t need to know Structured Query Language
☛ The tester who is performing Database Testing needs to know Structured Query Language
☛ GUI Testing includes invalidating the text boxes, check boxes, buttons, drop-downs, forms etc., majorly the look and feel of the overall application
☛ Database Testing involves in verifying the integrity of data in the front end with the data present in the back end. It validates the schema, database tables, columns, indexes, stored procedures, triggers, data duplication, orphan records, junk records. It involves in updating records in a database and verifying the same on the front end.

RDBMS stands for Relational Database Management System. RDBMS is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed using Structured Query Language (SQL)

A view is like a subset of a table which is stored logically in a database. A view is a virtual table. It contains rows and columns similar to a real table. The fields in the view are fields from one or more real tables. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity.

CREATE VIEW view_name AS SELECT column_name1, column_name2 FROM table_name WHERE CONDITION;

Types of Joins are as follows:

☛ INNER JOIN
☛ LEFT JOIN
☛ RIGHT JOIN
☛ OUTER JOIN