Sql Query

A

Here we will explain some important sql query that is used in your daily routine in find record in database.

**get  Department ,no of employees in a department ,total salary with respect to a department from employee table order by total salary-

Here show sql table data of employee:---

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

Now write a query exactly for output result...

select department,COUNT(First_name),SUM(salary) totalsalary from Employee group by Department order by totalsalary desc

Here is output result to get  DEPARTMENT ,NO OF EMPLOYEES IN A DEPARTMENT ,TOTAL SALARY WITH RESPECT TO A DEPARTMENT FROM EMPLOYEE TABLE ORDER BY TOTAL SALARY.....

department	(No column name)	totalsalary
Sr. Programmer	1	1000000
HR Manager	1	800000
CA	1	700000
SALES MANAGER	1	600000

B

**get  Department wise average salary from employee table order by salary ascending-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

Note:-The GROUP BY clause is used with the SQL SELECT statement

select department,AVG(salary) avgsalary from Employee group by department order by avgsalary asc

Output :Here is output department details and avgsalary detail of employee.

department	avgsalary
SALES MANAGER	600000
CA	700000
HR Manager	800000
Sr. Programmer	1000000

C

**get  Department wise Maximum salary from employee table order by salary ascending-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

Note:-The MAX() function returns the largest value(Salary) of the selected column in table..

select department,MAX(salary) maxsalary from Employee group by Department order by maxsalary asc

Here output of department details and maxsalary details of employee...

department	maxsalary
SALES MANAGER	600000
CA	700000
HR Manager	800000
Sr. Programmer	1000000

D

get  Department wise minimum  salary from employee table order by salary ascending-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

Note:-The MIN() function returns the smallest value(Salary) of the selected column in table..

select department, MIN(salary) minsalary from Employee group by Department order by minsalary asc

Output :Here output department details and minimum salary of employee..

department	minsalary
SALES MANAGER	600000
CA	700000
HR Manager	800000
Sr. Programmer	1000000

E

**Select no of employee joined with respect to year and month from employee table-

Here Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

Note:The COUNT() function returns the number of rows that matches a specified Place in a record..
Write Sql Query ..

select DATEPART(yyyy,joining_date) joinyear,DATEPART(mm,joining_date)joiningmonth,COUNT(*) totalemp from Employee group by DATEPART(yyyy,joining_date),DATEPART(mm,joining_date)

Output :Here output NO OF EMPLOYEE JOINED WITH RESPECT TO YEAR AND MONTH FROM EMPLOYEE TABLE..

joinyear	joiningmonth	totalemp
2013	1	2
2013	2	2

F

**get department,total salary with respect to a department from employee table where total salary greater than 800000 order by total salary descending-

Here Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

Note: The where keyword could not be used with aggrigrate function where we use "Having clause".

select department,sum(salary) totalsalary from employee group by Department having SUM(salary)>800000 order by totalsalary desc

Here output :-

department	totalsalary
Sr. Programmer	1000000

G

**Select top 2 salary from employee table-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select top 2 * from Employee order by Salary desc

Here output :

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	00:00.0	Sr. Programmer
2	Ranchiki	Singh	800000	00:00.0	HR Manager

H

**Select top 2^nd highest salary from employee table-

Table Name---------Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
1	Pankaj	Kumar	1000000	2013-01-01 00:00:00.000	Sr. Programmer
2	Ranchiki	Singh	800000	2013-01-01 00:00:00.000	HR Manager
3	Gaurav	Singh	700000	2013-02-01 00:00:00.000	CA
4	Vijay	Singh	600000	2013-02-01 00:00:00.000	SALES MANAGER

select MIN(salary) from (select top 2 * from Employee) a

Here output:-

	No column name
	800000