What are SQL Analytics queries? | SQL Analytics Queries

In my previous article I have given idea about the data analytics. I have also given the idea about data mining. In this article I would like to give you idea about SQL Analytics and SQL Analytics Queries. The SQL analytics is nothing but the systematic way of analyzing the data with particular statistics. If we are using the SQL in fetching the analytical data you can call it as SQL Analytics.

SQL analytics Queries :

In this section I would like to give you some SQL queries which are used for SQL Analytics. I want to give you multiple queries with examples which will use for SQL data analysis and modeling purpose. But before that user needs to know the basic sql queries which are used for data analysis.Following are Prerequisties for digging the data for analysis purpose :

1.User should know Basic SQL Queries

2.User should know the how to use query to retrieve data with filters.

3.User should know how to use Having and group by clause.

4.User should know how to use the joins in SQL.

5.User should know how to use Inner Queries to analyse specific data.

I would like to give you multiple examples of queries which are used for SQL Analytics. The SQL analytics is used for analytis of the data. So the simple Select queries are also used in case of doing data analyis with where clause. The first step is user should know all simple select queries.

Query 1 : If User wants to check the data for Employee from Employee table whose department is IT and HR. —> Simple SQL

In this case user wants to use IN operator to check the department data from Employee table.

Select * from Employee where Department IN (‘IT’,’HR’);

Query 2 : If the departement column is not present in Employee table and it is present is Department table.The joining condition will be Dept_id.User need to find out the data for Employees whose associated department is ‘IT’ or ‘HR’. —> Use of IN clause

Select E.* from Employee E ,Department D where E.Dept_id=D.Dept_id and D,Department IN(‘IT’,’HR’);

In above query user needs to use joining condition to fetch the information.

Query 3 : If user wants to fetch data for Employees whose name Starts with ‘A’? —> Use of Pattern Matching

Select * from Employee where Employee_Name LIKE ‘A%’;

The above query will fetch all Employee whose name starts with A.

Query 4 : If user wants to fetch data for Employees whose department is ‘IT’ and Salary is greater than 30000. —> Use of AND clause

Select * from Employee where department=’IT’ and salary > 30000;

Query 5 : If user wants to count the number of employees whose department is ‘IT’ and salary is greater than 30000. —> Use of Count Function

Select count(*) from Employee where department=’IT’ and salary > 30000;

Query 6 : If user wants to calculate Employess with its department. —> Group by Clause

Select count(Employee_name) from Employee group by Department;

Query 7 : If user wants to calculate Employees with its department which is order by descending order.-–> Group by and Order by clause

Select count(*) from Employee group by Department order by Count;

Query 8 : If user wants to fetch the name of distinct department from Department table.

Select distinct Department_name from Department;

The above query will fetch the distinct department names from department table.

Query 9 : If user wants to fetch the employee data from employee table where department names are IT and Finance.

Select * from Employee where Department_Name IN

(select distinct department_name from Department where department_name in (‘IT’,’Finance’);

Query 10 : If Employee salary is greater than 30000 the Employee will be High Salaried else it is regular pay. If user wants to fetch this using queries which query do we need to use.

Select Case when Salary > 30000 then ‘High Salaried’ else ‘Regular Pay’ from Employee;

These are some important queries which are used for SQL Analytics. I would like to give some best practices to use the queries while SQL Analytics is used.

1.Try avoid using SELECT * from :

The Select * statement is slower than Select Column_names from table; statement. User needs to use select column_names in spite of select * statement.

2.Try to follow order of Keywords in SQL :

The user needs to follow the order of Keywords in SQL as follows.

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. ORDER BY

With using the same user can build up the query for SQL Analytics to fetch the data from the table.

3.Use of proper aliases.

There are so many times where user needs to use proper Alias name.The names in table and columns are not proper and we need to give the proper names while fetching data.

Example :

If column name has only P_Cust_Num which is nothing but ‘Customer Number’. User need to give proeper alias for that column.

Select P_Cust_Num as “Customer Number” from Customers;

The above select statement will give the Customer number for customers.

4.Use of proper comments :

The user needs to use the proper comments. There are so many times when user wants to give the proper comment to understand what is fetching from query. It is always best practice to use the proper comments.

I hope you like this article on SQL Analytics and queries we are using for SQL Analytics .If you like the article or if you have any issues kindly comment in comment section.