Most Useful My SQL Queries for Quality Assurance

Most Useful MySQL Queries for Quality Assurance

There are various types of queries you can run in SQL, but we have identified some of the most useful MySQL DB queries. Before you execute the queries, these two things you must know, especially if you are considering Security Testing Services to ensure your database is secure during these operations.

Step-1 - Basic SQL terms

Step-2 - Properly configured MySQL Workbench tool

Once you are done with these two steps, it's time to see the execution of the Top needed queries and their use in DB one by one in this article.

Table of Contents

• My SQL DB queries
• Tables queries
• Values in SQL
• Wildcard & advanced characters
• Conclusion

My SQL DB Queries:

The following are some of the queries with examples: NOTE: Ignore [] in the below queries.

All DBs:

It is used to get the names of all the DB(s) that are installed in your server.

Query: SHOW databases;

Selecting DB:

It is used to choose and work on a particular DB from the list of the DB(s) in your server. 

Query: USE [DB Name];

Tables in selected DB:

It is used to get all the Tables installed in the selected DB.

Query: SHOW Tables;

 

Table Info:

It is used to get all the Rows and Columns of the selected Table.

Query: SELECT * from [Table Name];

Rows and Columns Info:

It is used to get all the detailed Info of all the Rows and Columns for the selected Table.

Query: DESCRIBE [Table Name];

New Table:

It is used for creating a new Table in the selected DB and entering the Rows and Columns with values in it.

Query: CREATE TABLE [Table Name] [column name datatype(value), column name datatype (value)...];

Where clause:

It is used to filter the record to fulfill the required condition.

Query: SELECT * from [Table Name] WHERE (Condition);

AND / OR / NOT:

There are three types of queries: AND is mainly used to aggregate multiple conditions where OR is used for implementing only 1 condition out of many and NOT is used to a not applying the mentioned condition. 

Query: SELECT * from [Table Name] WHERE (Condition1 AND Conditon2 AND Condition3);

Query: SELECT * from [Table Name] WHERE (Condition1 OR Conditon2);

Query: SELECT * from [Table Name] WHERE NOT (Condition1);

Order By:

This query help to sort the result in Ascending / Descending manner

Query: SELECT * from [Table Name] ORDER BY (column 1, column2) ASC;

Query: SELECT * from [Table Name] ORDER BY (column 1, column2) DESC;

Null Values:

This query is to check for the Null / Not Null values in the columns. 

Query: SELECT * from [Table Name] WHERE (Column Name) IS NULL;

Update:

This is used to update the existing data in the selected Table.

Query: UPDATE [Table Name] SET [column1 = value1, column2 = value2 ...] WHERE (Condition);

Delete:

It is used to delete any data from the selected column of the selected Table. 

Query: DELETE from [Table Name] WHERE (condition);

Select Top:

This Query is used to select or get the required Top no(s) of records from the Table.

Query: SELECT [Column Name] from [Table Name] WHERE (Condition) LIMIT number;

Min / Max:

To get the smallest (MIN()) and largest value (MAX()) from the column.

Query: SELECT MIN (column name) from (Table Name) WHERE [condition];

Query: SELECT MAX (column name) from (Table Name) WHERE [condition];

Cont / Avg / Sum:

To find the AVG() / COUNT() and SUM() for the selected columns in the selected Table.

Query: SELECT COUNT (column name) from [Table Name] WHERE (condition);

Query: SELECT AVG (column name) from [Table Name] WHERE (condition);

Query: SELECT SUM (column name) from [Table Name] WHERE (condition);

Like with Wildcard characters:

 This operator is used to match the same required value from the Table / Column. Mainly there are 2 conjunctions used with the LIKE operator I.e. - (%) and (_). 

You can also use wildcard characters with the LIKE operator. Wildcard character is used to substitute the one or more than one characters in String. There are multiple wildcard characters like - %, _, [], ^, -, etc.

Query: SELECT (column1, column2...) from [Table Name] WHERE [Column Name] LIKE (Wildcard Pattern);

Example 1 - “m%” - It will get all the records starting with ‘M.’

Example 2 - “%a” - It will get all the record ending with ‘A’.

Example 3 - “%n%” - It will get all the record which have ‘n’ in between the name or string.

Example 4 - “_a%” - It will get all the record starting any character and the 2^nd character will be only ‘A’ and can have any character at last.

In:

This operator helps to select multiple values in WHERE clause.

Query: SELECT (Column Name) from [Table Name] WHERE column name IN (valu1, valu2);

Between:

It is used for selecting the values within a provided range.

Query: SELECT (column name) from [Table Name] WHERE column name BETWEEN value1 AND value2;

Aliases:

It is used to set the column name is your readable form.

Query: SELECT column name AS alias name from [Table Name];

Joins:

A Join is used to combine the rows for more than 1 tables between the related columns of those 2 tables. 

Inner Join:

It is used to get the common records from both the Tables. 

Query: SELECT * from [Table Name 1] INNER JOIN [Table Name 2] ON (Table1 column name = Table2 column name);

Left Join:

It gets all the records of the Table 1 and gets the matching records of Tables 2. If there are no matching records in Table 2 then it will return to 0 records.

Query: SELECT * form [Table 1] LEFT JOIN [Table 2] ON (Table1.column name = Table2. column name);

Right Join:

Gets all the records from Table 2 and the matching records of table 1. The resulting record is 0 if there is no matching record in Table 1. 

Query: SELECT * from [Table 1] RIGHT JOIN [Table 2] ON (Table1.column name = Table2. column name);

Full Join:

This query helps you to get all the matching records from Table 1 and Table 2. 

Query: SELECT * from [Table 1] FULL OUTER JOIN [Table 2] ON (table1.column name = table2.column name) WHERE (condition);

Self Join:

 It helps you to get the record from the same Table for the selected condition. 

Query: SELECT * from [Table1, Table1] WHERE (condition);

Union:

This operator selects only distinct values by default. 

Query: SELECT * from [Table 1] UNION SELECT * from [Table 2];

Group By:

It is used to group up the rows that have the same values in to summary rows. 

Query: SELECT * from [Table Name] WHERE (condition) GROUP BY (column name) ORDER BY (Column Name);

Conclusion:

These are the most used SQL Queries. These queries will help you to perform your daily interaction with SQL. Our expert helped you to grind the profound knowledge of My SQL Query implementation in Day-to-Day life through this blog.

There are some advanced queries as well to move ahead in SQL. We will cover those in the future.

About Author

Bhargav Padalia started his career in the QA field two years back. He got his first certification in manual testing; he has claimed the title of the rising star. Currently, he is working as a QA executive in a software testing company.

In his leisure time, he loves watching detective movies and learning new things that make him more proficient in his career.