Get employee details from employee table whose employee name are “John” and “Roy”
Select * from EMPLOYEE where FIRST_NAME in (‘John’,’Roy’)
Get employee details from employee table whose employee name are not “John” and “Roy”
Select * from EMPLOYEE where FIRST_NAME not in (‘John’,’Roy’)
Get employee details from employee table whose first name starts with ‘J’
Select * from EMPLOYEE where FIRST_NAME like ‘J%’
Get employee details from employee table whose first name contains ‘o’
Select * from EMPLOYEE where FIRST_NAME like ‘%o%’
Get employee details from employee table whose first name ends with ‘n’ and name contains 4 letters
Select * from EMPLOYEE where FIRST_NAME like ‘___n’ (Underscores)
Get employee details from employee table whose Salary between 500000 and 800000
Select * from EMPLOYEE where Salary between 500000 and 800000
Get employee details from employee table whose joining year is “2013”
SQL Queries in MySQL, Select * from EMPLOYEE where year(joining_date)=’2013′
Get database date
select now()
Get department wise average salary from employee table order by salary ascending
select DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalary asc
HAVING Clause
The MySQL HAVING clause is often used with the GROUP BY clause. When using with the GROUP BY clause, we can apply a filter condition to the columns that appear in the GROUP BY clause. If the GROUP BY clause is omitted, the HAVING clause behaves like the WHERE clause.
Notice that the HAVING clause applies the filter condition to each group of rows, while the WHERE clause applies the filter condition to each individual row.
Select department,total salary with respect to a department from employee table where total salary greater than 800000 order by Total_Salary descending
Select DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT having sum(SALARY) >800000 order by Total_Salary desc
Select employee details from employee table if data exists in incentive table ?
select * from EMPLOYEE where exists (select * from INCENTIVES)
Since MySQL does not support MINUS operator here is one way to do it
Instead of
SELECT x, y FROM table_a
MINUS
SELECT x, y FROM table_b;
use
SELECT a.x, a.y
FROM table_a a LEFT JOIN table_b b
ON a.x = b.x AND a.y = b.y
WHERE b.x IS NULL;
Select 20 % of salary from John , 10% of Salary for Roy and for other 15 % of salary from employee table
SELECT FIRST_NAME, CASE FIRST_NAME WHEN ‘John’ THEN SALARY * .2 WHEN ‘Roy’ THEN SALARY * .10 ELSE SALARY * .15 END “Deduced_Amount” FROM EMPLOYEE
Select Last Name from employee table which contain only numbers
Select * from EMPLOYEE where lower(LAST_NAME)=upper(LAST_NAME)