Mysql Function Practice Question - Part 2

Mysql Function¶

• Aggergation Function
• Mathematical/Numerical Function
• Text Function
• Date Function
• CBSE QUESTION

Question based on Aggregation function:

Consider the following table "employees" and answer the following question in mysql

1. Write a query to list the number of jobs available in the employees table.
2. Write a query to get the total salaries payable to employees.
3. Write a query to get the minimum salary from employees table.
4. Write a query to get the maximum salary of an employee working as a Programmer.
5. Write a query to get the average salary and number of employees working the department 90.
6. Write a query to get the highest, lowest, sum, and average salary of all employees.
7. Write a query to get the number of employees with the same job.
8. Write a query to get the difference between the highest and lowest salaries.
9. Write a query to find the manager ID and the salary of the lowest-paid employee for that manager.
10. Write a query to get the department ID and the total salary payable in each department.
11. Write a query to get the average salary for each job ID excluding programmer.
12. Write a query to get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only
13. Write a query to get the job ID and maximum salary of the employees where maximum salary is greater than or equal to 9000
14. Write a query to get the average salary for all departments employing more than 10 employees.

1. SELECT COUNT(DISTINCT job_id) FROM employees;
2. SELECT SUM(salary) FROM employees;
3. SELECT MIN(salary) FROM employees;
4. SELECT MAX(salary) FROM employees WHERE job_id="it_prog";
5. SELECT AVG(salary), COUNT(*) FROM employees WHERE department_id=90;
6. SELECT MAX(salary), MIN(salary), SUM(salary), AVG(salary) FROM employees;
7. SELECT COUNT(*),job_id FROM employees;
8. SELECT MAX(salary) - MIN(salary) FROM employees;
9. SELECT manager_id,MIN(salary) FROM employees GROUP BY manager_id;
10. SELECT department_id,SUM(salary) FROM employees GROUP BY department_id;
11. SELECT AVG(salary), job_id FROM employee GROUP BY job_id WHERE job_id <> "IT_PROG";
12. SELECT job_id, SUM(salary), AVG(salary), MAX(salary), MIN(salary) FROM employees WHERE department_id = '90' GROUP BY job_id;
13. SELECT job_id, MAX(salary) FROM employees GROUP BY job_id HAVING MAX(salary) >=9000;
14. SELECT department_id, AVG(salary), COUNT() FROM employees GROUP BY department_id HAVING COUNT() > 10;

Mathematical/Numerical Function:

Consider the following table "employees" and answer the following question in mysql

1. Write a query to find all the salary in whole number

1. SELECT round(salary,0) FROM employee;

Text/String function

Consider the following table "employees" and answer the following question in mysql

Question:

1. Write a query to update the portion of the phone_number in the employees table, within the phone number the substring '123' will be replaced by '999'.

2. Write a query to get the details of the employees where the length of the first name greater than or equal to 8.

3. Write a query to find all employees where first names are in upper case.
4. Write a query to extract the last 4 character of phone numbers.
5. Write a query to display the length of first name for employees where last name contain character 'c' after 2nd position.

Solution:

1. UPDATE employees SET phone_number = REPLACE(phone_number, '123', '999') WHERE phone_number LIKE '%123%';
2. SELECT * FROM employees WHERE LENGTH(first_name) >= 8;
3. SELECT * FROM employees WHERE first_name = UPPER(first_name);
4. SELECT RIGHT(phone_number, 4) as 'Ph.No.' FROM employees;
5. SELECT length(first_name) FROM employees WHERE INSTR(last_name,'C') > 2;

Date Function:

Consider the following table "employees" and answer the following question in mysql

Question:

1. Write a query to extract the year from the Hire_Date of the employee table.
2. Write a query to get the firstname, lastname who joined in the month of June.
3. Write a query to get first name of employees who joined in 1987.
4. Write a query to get first name, hire date and experience of the employees.

Solution:

1. SELECT year(HIRE_DATE) FROM employee;
2. SELECT first_name, last_name FROM employees WHERE MONTH(HIRE_DATE) = 6;
3. SELECT FIRST_NAME, HIRE_DATE FROM employees WHERE YEAR(HIRE_DATE)=1987;
4. SELECT FIRST_NAME, SYSDATE(), HIRE_DATE, DATEDIFF( SYSDATE(), hire_date )/365 FROM employees;

CBSE QUESTION:

1. Write the SQL functions which will perform the following operations:   CBSE Sample Question 2020-21
   i) To display the name of the month of the current date .
   ii) To remove spaces from the beginning and end of a string, “ Panorama “.
   iii) To display the name of the day eg, Friday or Sunday from your date of birth, dob.
   iv) To display the starting position of your first name(fname) from your whole name(name).
   v) To compute the remainder of division between two numbers, n1 and n2
   

In [ ]: