new.txt

1. show alll jobs max and min salary

SELECT MAX(MIN_SALARY), MIN(MAX_SALARY)
FROM jobs

2.

SELECT MAX(MIN_SALARY), MIN(MAX_SALARY), count(JOB_ID)
FROM jobs
WHERE JOB_ID NOT LIKE "AD_%"

3.

SELECT  count(JOB_ID)
FROM jobs
WHERE JOB_ID NOT LIKE "AD_%"
GROUP BY (MAX_SALARY-MIN_SALARY) DIV 10000

4.

SELECT MANAGER_ID, COUNT(*), count(EMPLOYEE_ID)

FROM employees
WHERE DEPARTMENT_ID !=50
GROUP  by MANAGER_ID

5.

SELECT MANAGER_ID, COUNT(*) as member

FROM employees
WHERE DEPARTMENT_ID !=50
GROUP  by MANAGER_ID
ORDER  by member DESC LIMIT 0,1

6.

SELECT COUNT(*), COUNTRY_ID, POSTAL_CODE

FROM locations
WHERE POSTAL_CODE>5000
AND COUNTRY_ID IN("UK","US","JP")
GROUP BY COUNTRY_ID

7.

SELECT COUNT(*), UPPER(LEFT(FIRST_NAME,1))

FROM employees

GROUP BY UPPER(LEFT(FIRST_NAME,1))
HAVING SUM(SALARY)>=50000

8.

SELECT COUNT(*),MONTH(HIRE_DATE),year(HIRE_DATE)
from employees

GROUP BY year(HIRE_DATE),MONTH(HIRE_DATE)