Question

Test-case Queries (DML)

At least 10 test queries (pertaining to warehouse management)

You must use the following clauses in one or more of your test queries :

[ FROM, WHERE, GROUP BY, HAVING, ORDER BY ]

You must use the following statement options in one or more of your test queries :

[ column aliases, computed columns, SQL’s DISTINCT clause, JOIN ON syntax ]

You must use at least one of the special operators in one or more of your test queries :

[ BETWEEN, IN, LIKE, IS NULL, NOT ]

You must use at least one of the aggregate functions in one or more of your test queries :

[ Count, MIN, MAX, SUM, AVG ]

Answer 1

Hello, Student I hope you are doing great in lockdown.

Here is Test-case Queries for the following test cases, if still you have any doubt then feel free to ask in comment section, I am always happy to help you.

Please upvote.

What is DML?

DML stands for data manipulation language.

DML is a computer programming language used for adding , deleting, and updating data in a database.

Q1) You must use the following clauses in one or more of your test queries :

[ FROM, WHERE, GROUP BY, HAVING, ORDER BY ]

FROM - Provides the Data Source for the Query

WHERE - Filters Out Rows From the Data Source Based on Boolean Validation Conditional Expression

GROUP BY - GROUP BY query groups rows that have the same value into summary rows.

HAVING - HAVING was added to sql because the WHERE keyword could not be used with aggregate functions.

ORDER BY - Sort the result-set in ascending or descending order.

Queries: -

SELECT Salesperson, AVG(TotalSale)
 FROM SALES
 GROUP BY Salesperson;

SELECT * FROM Customers
ORDER BY Country;

SELECT * FROM Customers
WHERE Country='Mexico';

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

Q2) You must use the following statement options in one or more of your test queries :

[ column aliases, computed columns, SQL’s DISTINCT clause, JOIN ON syntax ]

SQL Aliases: - used to give a table, or a column in a table, a temporary name and used to make column names more readable.

column aliases: -

SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;

computed columns:- Also known as Generated columns,columns are computed based on predefined expressions.

CREATE TABLE test.Product (
id INT NOT NULL AUTO_INCREMENT,
amount DECIMAL(10,2) NULL,
sold_out DECIMAL(10,2) NULL,
faulty DECIMAL(10,2) NULL,
remain_amount DECIMAL(10,2) NULL,
PRIMARY KEY (id));

DISTINCT:- DISTINCT clause is used to remove duplicate records from the table and fetch only the unique records.

-- SELECT DISTINCT officer_name, address FROM officers;

Q3) You must use at least one of the special operators in one or more of your test queries :

[ BETWEEN, IN, LIKE, IS NULL, NOT ]

BETWEEN - selects values within a given range

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

IN - IN operator allows you to specify multiple values in a WHERE clause

SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');

LIKE - used in a WHERE clause to search for a specified pattern in a column

SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

IS NULL - column value does not exist. (server based query)

NOT - The NOT operator displays a record if the condition is NOT TRUE

SELECT * FROM Customers WHERE NOT Country='Germany';

Q4) You must use at least one of the aggregate functions in one or more of your test queries :

[ Count, MIN, MAX, SUM, AVG ]

Count-  COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause.

SELECT COUNT(ProductID) FROM Products;

MIN - MIN() function gives the minimum value of the selected column.

SELECT MIN(Price) AS SmallestPrice FROM Products;

MAX - MAX() function gives the maximum value of the selected column.

SELECT MAX(Price) AS LargestPrice FROM Products;

SUM - SUM() function returns the total sum

SELECT SUM(Quantity) FROM OrderDetails;

AVG - AVG() function returns the average value

SELECT AVG(Price) FROM Products;

I have included 20 test queries to understand everything.

Please do not forget to hit that like or thumbs-up button, it really motivates me<3

Thank you!!

Have a nice day:)