Database Testing Interview Questions & Answers
Best Database Testing TOP Interview Questions and Answers
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.
1. What does SQL stand for?
SQL stands for Structured Query Language.
2. How to select all records from the table?
To select all the records from the table we need to use following syntax:
Select * from table_name;
3. Define join and name different type of joins?
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:
- Right Join
- Outer Join
- Full Join
- Cross Join
- Self Join.
4. What is the syntax to add record to a table?
To add record in a table INSERT syntax is used.
Ex: INSERT into table_name VALUES (value1, value2..);
5. How do you add a column to a table?
To add another column in the table following command has been used.
ALTER TABLE table_name ADD (column_name);
6. Define SQL Delete statement.
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
WHERE <Condition>
7. Define COMMIT ?
COMMIT saves all changes made by DML statements.
8. What is a primary key?
A Primary key is column whose values uniquely identify every row in a table. Primary key values can never be reused.
9. What are foreign keys?
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.
10. What is CHECK Constraint?
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.
11. Is it possible for a table to have more than one foreign key?
Yes, a table can have many foreign keys and only one primary key.
12. What are the possible values for BOOLEAN data field.
For a BOOLEAN data field two values are possible: -1(true) and 0(false).
13. What is a stored procedure?
A stored procedure is a set of SQL queries which can take input and send back output.
14. What is identity in SQL?
An identity column in the SQL automatically generates numeric values. We can defined a start and increment value of identity column.
15. What is Normalization?
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.
16. What is Trigger?
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)
17. How to select random rows from a table?
Using SAMPLE clause we can select random rows.
Example:
SELECT * FROM table_name SAMPLE(10);
18. Which TCP/IP port does SQL Server run?
By default SQL Server runs on port 1433.
19. Write a SQL SELECT query that only returns each name only once from a table?
To get the each name only once, we need to use the DISTINCT keyword.
SELECT DISTINCT name FROM table_name;
20. Explain DML and DDL?
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.
21. Can we rename a column in the output of SQL query?
Yes using the following syntax we can do this.
SELECT column_name AS new_name FROM table_name;
22. Give the order of SQL SELECT ?
Order of SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clause are mandatory.
23. Suppose a Student column has two columns, Name and Marks. How to get name and marks of top three students.
SELECT Name, Marks FROM Student s1 where 3 <= (SELECT COUNT(*) FROM Students s2 WHERE s1.marks = s2.marks)
24. What is SQL comments?
SQL comments can be put by two consecutive hyphens (–).
25. Difference between TRUNCATE, DELETE and DROP commands?
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.
26. What are the properties of a transaction?
Generally these properties are referred as ACID properties. They are:
Atomicity
Consistency
Isolation
Durability.
27. What do you mean by ROWID ?
It’s a 18 character long pseudo column attached with each row of a table.
28. Define UNION, MINUS, UNION ALL, INTERSECT ?
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.
29. What is a transaction?
A transaction is a sequence of code that runs against a database. It takes database from one consistent state to another.
30. What is difference between UNIQUE and PRIMARY KEY constraints?
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.
31. What is a composite primary key?
Primary key created on more than one column is called composite primary key.
32. What is an Index ?
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.
33. What is the Subquery ?
A Subquery is sub set of select statements whose return values are used in filtering conditions of the main query.
34. What do you mean by query optimization?
Query optimization is a process in which database system compares different query strategies and select the query with the least cost.
35. What is Collation?
Set of rules that defines how data is stored, how case sensitivity and Kana character can be treated etc.
36. What is Referential Integrity?
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.
37. What is Case Function?
Case facilitates if-then-else type of logic in SQL. It evaluates a list of conditions and returns one of multiple possible result expressions.
38. Define a temp table?
A temp table is a temporary storage structure to store the data temporarily.
39. How we can avoid duplicating records in a query?
By using DISTINCT keyword duplicating records in a query can be avoided.
40. Explain the difference between Rename and Alias?
Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column.
41. What is a View?
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.
42. What are the advantages of Views?
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.
43. List the various privileges that a user can grant to another user?
SELECT, CONNECT, RESOURCES.
44. What is schema?
A schema is collection of database objects of a User.
45. What is Table ?
A table is the basic unit of data storage in the database management system. Table data is stored in rows and columns.
46. Do View contain Data?
No, Views are virtual structure.
47. Can a View based on another View?
Yes, A View is based on another View.
48. What is difference between Having clause and Where clause?
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.
49. What is difference between Local and Global temporary table?
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.
50. What is CTE?
A CTE or common table expression is an expression which contains temporary result set which is defined in a SQL statement.