Common Database Commands

     As we know that database testing is one of the vital parts of the testing activity to check the data imported properly from source data into database after running SSIS or related stored procedure execution. As a test engineer we should aware of common database commands to deal with our testing activities. In this article, I would like to share some commonly used database commands and examples.

Following are common data manipulation commands,

• SELECT: Select the data from database.
• UPDATE: Update table data.
• DELETE: Delete rows from a table.
• AS: Rename column or table with an alias.
• FROM: Specify the table name we are pulling from.
• WHERE: Filter query to match a condition.
• JOIN: Combine rows from two or more tables.
• AND: Combine condition in a query, all conditions must be met.
• OR: Combine condition in a query, one condition must be met.
• LIKE: Specify a pattern in a column.
• IN: Specify multiple values when using WHERE.
• IS NULL: Return only rows with NULL value.
• LIMIT: Limit number of rows returned.
• CASE: Return value on specified condition.

Following are common data defining commands,

• CREATE: Create database, table, index or views.
• DROP: Delete database, table, or index.
• ALTER TABLE: Add/Remove columns from table.

Following are common supporting/operation commands,

• GROUP BY: Group rows that have same values into summary rows.
• ORDER BY: Set order of result. Use DESC to reverse order. Use ASC to ascending order sorting.
• HAVING: Same as WHERE but used for aggregate function.
• SUM: Return sum of column.
• AVG: Return of average of column.
• MIN: Return minimum value of column.
• MAX: Return maximum value of column.
• COUNT: Return number of rows.
• DISTINCT: Considers each element of the array as a separate value.

Following are some examples:

Create Database or Index or Table,

CREATE DATABASE MyDatabase;

CREATE INDEX IndexName ON TableName(col1);

CREATE TABLE MyTable(Id int, name varchar(12));

Delete Database or Table,

DROP DATABASE MyDatabase;

DROP TABLE MyTable;

Update Table,

UPDATE MyTable SET col2=69 WHERE col1=5;

Delete Records,

DELETE FROM MyTable WHERE col1=5;

Add or Remove Column,

ALTER TABLE MyTable ADD col5 int;

ALTER TABLE My Table DROP COLUMN col5;

Select all records from table with filter applied,

SELECT * FROM MyTable WHERE col1>6;

Select first 15 records for 3 columns,

SELECT col1, col2, col3 FROM MyTable LIMIT 15;

Select all records with multiple filters applied,

SELECT * FROM MyTable WHERE col1>5 AND col2<2;

Select all records from col1 and col2 ordering by col1,

SELECT col1, col2 FROM MyTable ORDER BY 1;

Return count of records in table,

SELECT COUNT(*) FROM MyTable;

Return sum of col1,

SELECT SUM(col1) from MyTable;

Return maximum value from col1,

SELECT MAX(col1) FROM MyTable;

Calculate statistics by grouping,

SELECT AVG(col1) FROM MyTable GROUP BY col2;

Combine data from two tables using a left join,

SELECT * FROM MyTable1 AS t1 LEFT JOIN MyTable2 AS t2 ON t2.col1=t1.col1;

Aggregate and filter results,

SELECT col1, AVG(col2) * AVG(col3) AS total FROM MyTable GROUP BY col1 HAVING total >4

Use of CASE statement,

SELECT col1,
CASE
WHEN col1>10 THEN “more than 10”
WHEN col1<10 THEN “less than 10”
ELSE “10”
END AS NewColName
FROM MyTable;

     I hope you really enjoyed to read and understand the basic commands and examples. You can utilize these commands in your daily database testing activities based on your requirement. Thanks for reading.

make it perfect!