In my previous articles I have given the complex sql interview question with examples. There is always need to do the validation for the data and some queries for data validation. We need to do the data validation with using multiple SQL queries. These SQL queries are most important for data validation. I would like to give the real examples for Data validation and will give you exactly where the SQL queries for data validation are using.
SQL Queries for Data Validation :
Query 1 : We require to fetch the data from Particular table.
Answer :
This is most common Scenario where we got the data from table and need to fetch the data from table. We will take example for Employee table.
Select * from Employees;
Query 2 ; Fetching the Specific data from multiple tables.
Answer :
There are so many scenarios where we require to fetch the data from multiple tables. We require to validate the data by joining multiple tables.
Select e.empno,e.name,d.department from Employee e,Department d where e.deptid=d.deptid;
Query 3 : Finding the total number of records from specific table.
Answer:
Many times we require to count the number of records from the table. This is most commonly used query for validating the data. You can validate if any real time transaction is happening. The count will get increase if data is inserting in the table or it may get decrease if deleting from the table. You can use aggregate function named count to find total number of records.
Select count(*) from Employees;
Query 4 : Getting the Result of the query in sorting.
Answer :
To validate the sorting data from SQL we can use the order by clause. We can validate the data in sorting manner using order by clause. In Real life example if you want to sort the employee data according to alphabetical order then with using order by you can sort it using a single query.
Select * from Employees order by Employee_name;
There are many times we require to sort the data with date field in descending order.
Select * from Emplyees order by Hire_Date desc;
The order by clause is most important clause for validating data in ascending or descending order.
Query 5 : Find total number of records in specified condition.
Answer :
There are many times we require to validate that data is correctly inserted for specific condition. Lets say If you want to check the count of records of employees who’s department is ‘Management’ then you need to use following SQL statement.
Select Count(*) from Employees where Department_name=’Management’;
If department name column is not available in Employee table. There is relation between employee and department table then use following join query,
Select count(a.Employee_id) from Employee e,Department d where e.deptid=d.deptid and d.dept_name=’Management’;
Query 6 : Checking the Case Sensitive data.
Answer :
We require to compare and validate the case sensitive data as data does not inserted in case sensitive manner. You can use the following query if you want to fetch the data whose name is ‘Amit’. You require to use character function for the same.
Select * from Employee where Upper(Ename) = ‘AMIT’;
Select * from Employee where Lower(Ename) = ‘amit’;
You can either use upper or lower function so that it will give you correct data and remove the case sensitiveness.
Query 7 : Distinct values from table.
Answer :
Many situations we require to fetch the distinct data from the table whether we have added that properly or not in table. Following query is useful to fetch distinct values from table.
Select distinct Gender from Employee ;
The above query will give you the distinct information about gender column.
Query 8 : Grouping of the data.
Answer :
We require to do data validation at group level. As real life situation if we require to count the number of employees departmentwise we need to use group by clause.
Select Department_name,Count(employee_name) from Employee group by Department_name;
The query will give the departmentwise count for employees.
Query 9 : Validate the data for part of string.
Answer :
Sometimes we require to validate the data where we dont know the exact data. We need to use the % with LIKE keyword
Select * from Employee where Employee_name like ‘%am%’;
The above query will fetch the employee data which includes string ‘am’.
Query 10 : If you want to validate first and last record from the Employee table.
Answer :
Select * from Employee where Rowid= select max(Rowid) from Employee;
Select * from Employee where Rowid= select min(Rowid) from Employee;
The above queries will give the first and last record from employee table.
Query 11 : Finding duplicate records from table.
Answer :
We require to find the duplicate records from the table and sometimes we require to remove that records. Following is the best way to find duplicate records,
SELECT Employee_Num, COUNT(Employee_Num) FROM Employees GROUP BY Employee_Num
HAVING COUNT(Employee_Num) > 1;
The above query will give you the duplicate records from Employees table.
Query 12 : Datewise Employee data validation.
Answer :
Sometimes we require to use multiple date functions to do the validation of the data. Suppose you want to check the data of employee who joined after 1st Jan 2020 then you can use the following query to fetch data,
Select * from Employee where Hire_date > ’31-Dec-2020′;
If you want to fetch the employees data who joined between 1st Jan 2020 to 30th Jan 2020.
Select * from Employee where Hire_date between ’01-01-2020′ and ’30-01-2020′;
Query 13 : What is query to check the data excluding some column data.
Answer :
If we require to exclude the specific data from then we need to use IN,ANY Operators. If you want to exclude the employees from marketing department and finance department then you can use following query,
Select * from Employees where department_name not in (‘Marketing’,’Finance’);
Query 14 : Find out the data which has specific length.
Answer :
Sometimes we require to do the validation for the data with specific length. In real life scenario if PIN No is 6 digits and we require to find out the data where length is greater than or less than 6 then you can use following query.
Select *, Pin_length from (Select length(Pin) Pin_length from Employee) where Pin_length > 6 or pin_length<6;
These are some most important queries which we can use for validation. You need to brush up some skills like – functions in SQL, Like Wildcard character, Operators in SQL etc. I hope the above queries are useful queries for data validation purpose. If you suggest any other queries kindly comment in comments box.