Database Developer

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.

By default it is 1433

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

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.

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.

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

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.

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

☛ Oracle,

☛ MySQL,

☛ Microsoft SQL Server,

☛ PostgreSQL,

☛ Sybase,

☛ MongoDB,

☛ DB2, and

☛ Microsoft Access etc.,

Types of Joins are as follows:

☛ INNER JOIN

☛ LEFT JOIN

☛ RIGHT JOIN

☛ OUTER JOIN

A table can have many FOREIGN KEY’s.

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;

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.