1. Please explain what is Database Testing?
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.
2. Explain me what are the different DDL commands in SQL?
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
3. Tell us what is a temp table?
A temp table is a temporary storage structure to store the data temporarily.
4. Tell me what is a Composite PRIMARY KEY?
Composite PRIMARY KEY is a primary key created on more than one column (combination of multiple fields) in a table.
5. Do you know SQL Data Types?
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.
6. Tell us what is the difference between Delete, Truncate and Drop command?
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.
7. Tell me what are aggregate functions in SQL?
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
8. Tell us what do you mean by table and field in SQL?
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
9. Tell me what do you mean by ROWID ?
It's a 18 character long pseudo column attached with each row of a table.
10. Can you explain me what is a Database?
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.
11. Tell me what are the popular Database Management Systems in the IT Industry?
☛ Oracle,
☛ MySQL,
☛ Microsoft SQL Server,
☛ PostgreSQL,
☛ Sybase,
☛ MongoDB,
☛ DB2, and
☛ Microsoft Access etc.,
12. Tell me what are the different DCL commands in SQL?
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
13. Tell us what is a Subquery?
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.
14. Explain me what are SQL constraints?
SQL constraints are the set of rules that enforced some restriction while inserting, deleting or updating of data in the databases.
15. Tell us what is the difference between UNIQUE and PRIMARY KEY constraints?
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.
16. Tell us what is a DEFAULT constraint?
DEFAULT constraint is used to include a default value in a column when no value is supplied at the time of inserting a record.
17. Tell me which TCP/IP port does SQL Server run?
By default it is 1433
18. Tell us what is the difference between Having and Where clause?
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.
19. Tell me what are the three ways that Dynamic SQL can be executed?
☛ Writing a query with parameters.
☛ Using EXEC.
☛ Using sp_executesql.
20. Explain me what do you mean by data integrity?
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.
22. Do you know what is the difference between GUI Testing and Database Testing?
☛ 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;
25. Tell us what are the different types of joins?
Types of Joins are as follows:
☛ INNER JOIN
☛ LEFT JOIN
☛ RIGHT JOIN
☛ OUTER JOIN
