MySQL Queries

SQL Query to find second highest salary of Employee

select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );

There is a table which contains two column Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above average students.

SELECT student, marks from table where marks > SELECT AVG(marks) from table)

How do you find all employees which are also manager? You have given a standard employee table with an additional column mgr_id, which contains employee id of the manager. , mgr_id

Answer: You need to know about self-join to solve this problem. In Self Join, you can join two instances of the same table to find out additional details as shown below SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;

search for employees whose last names don't start with the character B, you can use the NOT LIKE with a pattern as shown in the following query:

SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName NOT LIKE 'B%';

find all employees whose last names contain on , you use the following query with the pattern %on%

SELECT employeeNumber, lastName, firstName FROM employees WHERE lastname LIKE '%on%';

find products whose product codes contain the string _20 , you can use the pattern %\_20%

SELECT productCode, productName FROM products WHERE productCode LIKE '%\_20%';

To find employees whose first names start with T , end with m, and contain any single character between e.g., Tom , Tim, you use the underscore (_) wildcard to construct the pattern as follows:

SELECT employeeNumber, lastName, firstName FROM employees WHERE firstname LIKE 'T_m';

Write an SQL Query to find name of employee whose name Start with 'M'

SELECT * FROM Employees WHERE EmpName like 'M%';

Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?

SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno) then delete: DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);

find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.

SELECT * from Employees WHERE UPPER(EmpName) like '%JOE%';

Write an SQL Query find number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.

SELECT COUNT(*), sex from Employees WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' GROUP BY sex;

Write an SQL Query to print the name of the distinct employee whose DOB is between 01/01/1960 to 31/12/1975.

SELECT DISTINCT EmpName FROM Employees WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975';

SQL Query to find Max Salary from each department id.

SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID.

SQL Query to find Max Salary from each department Name.

SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName;

Write an SQL Query to find an employee whose Salary is equal or greater than 10000.

SELECT EmpName FROM Employees WHERE Salary>=10000;

Write SQL Query to display the current date.

SELECT GetDate();

Write an SQL Query to check whether date passed to Query is the date of given format or not.

SELECT ISDATE('1/08/13') AS "MM/DD/YY";

Write an SQL Query to find the year from date.

SELECT YEAR(GETDATE()) as "Year";

What is different using percentage ( % ) wildcard and underscore ( _ ) wildcard in MySQL?

The percentage ( % ) wildcard matches any string of zero or more characters. The underscore ( _ ) wildcard matches any single character.