DBMS for Software Tester

MySQL Workbench provides a graphical tool for working with MySQL servers and databases. MySQL Workbench fully supports MySQL versions 5.5 and higher.

CREATE is a DDL command used to create databases, tables, triggers and other database objects.

The DML commands in change the present data in the SQL database. We can easily access, store, modify, update, and delete the existing records from the database using DML commands.

MySQL primary key is a single or combination of the field, which is used to identify each record in a table uniquely. If the column contains primary key constraints, then it cannot be null or empty. A table may have duplicate columns, but it can contain only one primary key. It always contains a unique value in a column.

The foreign key is used to link one or more than one table together. It is also known as the referencing key. A foreign key matches the primary key field of another table. If the column contains a foreign key, then data repetition and empty fields are allowed.

A unique key in MySQL is a single field or combination of fields that ensure all values going to store in the column will be unique. More than one column will be allowed to use a unique key constraint and null values are also allowed in the unique key column.

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

The NOT NULL constraint enforces a column to NOT accept NULL values.

The DEFAULT constraint is used to set a default value for a column. The default value will be added to all new records if no other value is specified.

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

Arithmetic operators are used to perform the arithmetic operations on MySQL queries.

The comparison operators in MySQL are used to compare values between operands and return true or false according to the condition specified in the statement.

SQL logical operators are used to test for the truth of the condition.

AND: TRUE if both Boolean expressions are TRUE.

OR: TRUE if either Boolean expression is TRUE.

BETWEEN: TRUE if the operand is within a range.

IN: TRUE if the operand is equal to one of a list of expressions.

LIKE: TRUE if the operand matches a pattern.

NOT: Reverses the value of any other Boolean operator.

IS NULL: It compares a value with a NULL value.

Limit: The LIMIT clause is used to specify the number of records to return. Limit is always used last in any query formation.

ORDER BY: The ORDER BY keyword is used to sort the result set in ascending or descending order.

GROUP BY is one of the most useful MySQL clauses. It groups rows with the same values into summary rows. The clause returns one row for each group. In a query, MySQL GROUP BY is located after the FROM and WERE clauses, but before the HAVING, DISTINCT, ORDER BY, LIMIT clauses.

MySQL DISTINCT clause is used to remove duplicate records from the table and fetch only the unique records. The DISTINCT clause is only used with the SELECT statement.

JOINS are used to retrieve data from multiple tables in a single query. For JOINs to work, the tables need to be related to each other with a common key value. JOIN clauses are used in the SELECT, UPDATE, and DELETE statements.

Inner Join: INNER JOINs are used to fetch only common matching records.

Left Join: LEFT JOINs are used to fetch complete data from the left table and only common data from the right table.

Right Join: RIGHT JOINs are used to fetch complete data from the right table and only common data from the left table.

Full Join: FULL JOINS uses matrix multiplications.

Union is an operator that allows us to combine two or more results from multiple SELECT queries into a single result set

AS: AS is used to rename a column or table with an alias.

Atomicity: It means that either the entire transaction takes place at once or doesn’t happen at all.

Consistency : This means that integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to the correctness of a database.

Isolation : This property ensures that multiple transactions can occur concurrently without leading to the inconsistency of the database state.

Durability : This property ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if a system failure occurs.

RENAME TABLE renames one or more tables. You must have ALTER and DROP privileges for the original table, and CREATE and INSERT privileges for the new table.

DROP: The DROP Command drops the complete database or table.

Delete: The DELETE command deletes one or more existing records from the table in the database.

Truncate: The TRUNCATE Command deletes all the rows from the existing table, leaving the row with the column names.

MySQL's aggregate function is used to perform calculations on multiple values and return the result in a single value like the average of all values, the sum of all values, and maximum & minimum values among certain groups of values. We mostly use the aggregate functions with SELECT statements in the data query languages.