Queries (10-20)

//queries from 10-20

11. Query to display the Name, Salary and Commission for all the employees who earn commission. Sort the data in descending order of Salary and Commission.

SELECT Ename,Salary,Commission FROM employee WHERE Commission > 0.00 ORDER BY Salary DESC,Commission DESC;

12. Query to display Name of all the employees where the third letter of their name is ‘A’.

SELECT Ename FROM employee WHERE Ename LIKE '__A%';

13. Query to display Name of all employees either have two ‘R’s or have two ‘A’s in their name and are either in Dept No = 30 or their Manger’s Employee No = 778.

SELECT Ename,Dno,Manager FROM employee WHERE Ename LIKE '%A%A%' OR Ename LIKE '%R%R%' AND Dno=30 OR Manager='778';

14. Query to display Name, Salary and Commission for all employees whose Commission Amount is greater than their Salary increased by 5%.

SELECT Ename,Salary,Commission FROM employee WHERE Commission > (Salary+Salary*0.05);

15. Query to display the Current Date.

SELECT CURDATE();

16. Query to display Name, Hire Date and Salary Review Date which is the 1st Monday after six months of employment.

SELECT Ename,Hire_date,date_add(date_add(Hire_date,INTERVAL 6 MONTH),INTERVAL (7-WEEKDAY(date_add(Hire_date,INTERVAL 6 MONTH))) DAY) AS REVIEW_DATE FROM employee;

17. Query to display Name and calculate the number of months between today and the date each employee was hired.

SELECT Ename,12 *  (YEAR(curdate())-YEAR(Hire_date)) + (MONTH(CURDATE())-MONTH(Hire_date)) AS MONTHS FROM employee;

18. Query to display the following for each employee:- <E-Name> earns < Salary> monthly but wants < 3 * Current Salary >. Label the Column as Dream Salary.

SELECT CONCAT(Ename,' earns ',Salary,' monthly but wants ',3*Salary) AS DREAMY_SALARY FROM employee;

19. Query to display Name with the 1st letter capitalized and all other letter lower case and length of their name of all the employees whose name starts with ‘J’, ’A’ and ‘M’.

SELECT CONCAT( UPPER(SUBSTRING(Ename,1,1)) , LOWER(SUBSTRING(Ename,2,50))) AS NAME,LENGTH(Ename) AS LENGTH  FROM employee WHERE Ename LIKE 'J%' OR Ename LIKE 'A%' OR Ename LIKE 'M%';

20. Query to display Name, Hire Date and Day of the week on which the employee started.

SELECT Ename, Hire_date, DAYNAME(Hire_date) AS WEEK_DAY FROM employee;