Department wise employees having salary greater than average salary

Correlation subqueries

 Let us consider an employee table and department table. For readers simplicity we are just giving minimum fields in the table/

Table employees

/*Table structure for table `tbl_employee` */

CREATE TABLE `tbl_employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)

/*Data for the table `tbl_employee` */

insert  into `tbl_employee`(`id`,`name`,`salary`,`department_id`) values (1,'Sachin',2500,1),(2,'Dravid',9000,1),(3,'Ganguly',7000,1),(4,'Azaruddin',10000,1),(5,'Jadeja',8000,2),(6,'Sreenath',9500,2),(7,'Rohit',7800,2),(8,'Kambli',4000,2),(9,'Kapil',10001,3),(10,'Kohli',2300,2),(11,'Pant',3400,1);

Table Employees

Table department

/*Table structure for table `tbl_departments` */


CREATE TABLE `tbl_departments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


/*Data for the table `tbl_departments` */

insert  into `tbl_departments`(`id`,`name`) values (1,'ODI'),(2,'TEST'),(3,'T20'),(4,'IPL');

Table department

Query 1 : Select all employees whose salary is greater than average salary of all departments

select * from tbl_employee where salary > (select avg(salary) from tbl_employee)

will return all the employees having the salary greater than the average salary ie 6681.91

Query 2 : Select employees whose salary is greater than their departments average salary

SELECT * FROM tbl_employee e1 where salary > (select AVG(salary) from tbl_employee e2 where e2.department_id = e1.department_id)

will returns the  department wise employees having the salary greater than the average salary

Query 3 : Select top two employees having higher salaries in their department

SELECT * FROM tbl_employee e1 WHERE 2 >  (SELECT Count(DISTINCT salary) FROM tbl_employee e2 WHERE e2.Salary > e1.Salary  AND e1.department_id = e2.department_id );

Top 2 salaries in each department

department employees having their higher salaries

Thanking you

How to crack MicroSoft Office Excel Password from a protected sheet using macros

Hi guys,


We worked in Consumerfed Infomation Technology Section, You already read our articles before.
Today we came up with a solution that you were searching for a long.

Hope everyone worked in excel sheet (microsoft) and knows how to protect excel sheet using a password.

Even once you have tried to crack the password? Here is a solution for it.

Try this.

Dont forget to post you comments, your responses are valuable



You have to run the following macros ( Id : 36909381)

Press Alt + fll then paste the code open the active password protected sheet then run the macros



Sub CrackExcelPassword()
  'Author +bithesh soubhagya

  Dim i As Integer, j As Integer, k As Integer
  Dim l As Integer, m As Integer, n As Integer
  Dim i1 As Integer, i2 As Integer, i3 As Integer
  Dim i4 As Integer, i5 As Integer, i6 As Integer
  On Error Resume Next
  For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
  For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
  For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
  For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
   
  ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
      Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
      Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
  If ActiveSheet.ProtectContents = False Then
      MsgBox "One usable password is " & Chr(i) & Chr(j) & _
          Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
          Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
   ActiveWorkbook.Sheets(1).Select
   Range("a1").FormulaR1C1 = Chr(i) & Chr(j) & _
          Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
          Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
       Exit Sub
  End If
  Next: Next: Next: Next: Next: Next
  Next: Next: Next: Next: Next: Next

End Sub


Thanks you reading this post.

You can send your queires to our email.. or you can post comments below.

:- Bithesh consumerfed kozhikode regional office

TicTacToe playing with computer full java source code | algorithm

watch this video

Developed a two player tic tac toe game in java.  The user can also play with the computer,  we developed a new algorithm that makes computers to investigate user moves and to choose the best moves to win the game. So we request the readers to watch the video showing how it works.  Our developers are working on the ui parts. We will update the source code in this website soon.  You can post your comments & queries. Please follow the blog for our update. Your feedback are valuable.

Full source code will be available in this blog soon..

Tic Tac Toe Game