Dear Readers, Welcome to Database Testing Interview Questions and Answers have been designed specially to get you acquainted with the nature of questions you may encounter during your Job interview for the subject of Database Testing. These Database Testing Questions are very important for campus placement test and job interviews. As per my experience good interviewers hardly plan to ask any particular questions during your Job interview and these model questions are asked in the online technical test and interview of many IT companies.
SQL stands for Structured Query Language.
To select all the records from the table we need to use following syntax:
Select * from table_name;
Join keyword is used to fetch data from related two or more tables. It returns rows where there is at least one match in both the tables included in join. Read more here.
Type of joins are:
To add record in a table INSERT syntax is used.
Ex: INSERT into table_name VALUES (value1, value2..);
To add another column in the table following command has been used.
ALTER TABLE table_name ADD (column_name);
Delete is used to delete a row or rows from a table based on the specified condition.
Basic syntax is as follows:
DELETE FROM table_name
COMMIT saves all changes made by DML statements.
A Primary key is column whose values uniquely identify every row in a table. Primary key values can never be reused.
When a one table’s primary key field is added to related tables in order to create the common field which relates the two tables, it called a foreign key in other tables.
Foreign Key constraints enforce referential integrity.
A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.
Yes, a table can have many foreign keys and only one primary key.
For a BOOLEAN data field two values are possible: -1(true) and 0(false).
A stored procedure is a set of SQL queries which can take input and send back output.
An identity column in the SQL automatically generates numeric values. We can defined a start and increment value of identity column.
The process of table design to minimize the data redundancy is called normalization. We need to divide a database into two or more table and define relationships between them.
Trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table)
Using SAMPLE clause we can select random rows.
SELECT * FROM table_name SAMPLE(10);
By default SQL Server runs on port 1433.
To get the each name only once, we need to use the DISTINCT keyword.
SELECT DISTINCT name FROM table_name;
DML stands for Data Manipulation Language. INSERT, UPDATE and DELETE are DML statements.
DDL stands for Data Definition Language. CREATE ,ALTER, DROP, RENAME are DDL statements.
Yes using the following syntax we can do this.
SELECT column_name AS new_name FROM table_name;
Order of SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clause are mandatory.
SELECT Name, Marks FROM Student s1 where 3 <= (SELECT COUNT(*) FROM Students s2 WHERE s1.marks = s2.marks)
SQL comments can be put by two consecutive hyphens (–).
DELETE removes some or all rows from a table based on the condition. It can be rolled back.
TRUNCATE removes ALL rows from a table by de-allocating the memory pages. The operation cannot be rolled back
DROP command removes a table from the database completely.
Generally these properties are referred as ACID properties. They are:
It’s a 18 character long pseudo column attached with each row of a table.
MINUS – returns all distinct rows selected by the first query but not by the second.
UNION – returns all distinct rows selected by either query
UNION ALL – returns all rows selected by either query, including all duplicates.
INTERSECT – returns all distinct rows selected by both queries.
A transaction is a sequence of code that runs against a database. It takes database from one consistent state to another.
A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys.
Primary key cannot contain Null values whereas Unique key can contain Null values.
Primary key created on more than one column is called composite primary key.
An Index is an special structure associated with a table speed up the performance of queries. Index can be created on one or more columns of a table.
A Subquery is sub set of select statements whose return values are used in filtering conditions of the main query.
Query optimization is a process in which database system compares different query strategies and select the query with the least cost.
Set of rules that defines how data is stored, how case sensitivity and Kana character can be treated etc.
Set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.
Case facilitates if-then-else type of logic in SQL. It evaluates a list of conditions and returns one of multiple possible result expressions.
A temp table is a temporary storage structure to store the data temporarily.
By using DISTINCT keyword duplicating records in a query can be avoided.
Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column.
A view is a virtual table which contains data from one or more tables. Views restrict data access of table by selecting only required values and make complex queries easy.
Advantages of Views:
Views restrict access to the data because the view can display selective columns from the table.
Views can be used to make simple queries to retrieve the results of complicated queries. For example, views can be used to query information from multiple tables without the user knowing.
SELECT, CONNECT, RESOURCES.
A schema is collection of database objects of a User.
A table is the basic unit of data storage in the database management system. Table data is stored in rows and columns.
No, Views are virtual structure.
Yes, A View is based on another View.
Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause.
If GROUP BY clause is not used then Having behaves like WHERE clause only.
If defined in inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the db but its rows disappears when the connection is closed.
A CTE or common table expression is an expression which contains temporary result set which is defined in a SQL statement.