1 / 25

DATABASE PROGRAMMING

DATABASE PROGRAMMING. Sections 5-7. Write a query that shows the average, maximum, and minimum salaries for all employees with jobs in the programming department. Write a query that shows the average, maximum, and minimum salaries for all employees with jobs in the programming department.

carlos-cannon
Download Presentation

DATABASE PROGRAMMING

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DATABASE PROGRAMMING Sections 5-7

  2. Write a query that shows the average, maximum, and minimum salaries for all employees with jobs in the programming department.

  3. Write a query that shows the average, maximum, and minimum salaries for all employees with jobs in the programming department. SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%PROG%';

  4. What is the oldest hire date, and the most recent hire date for all employees in the programming department?

  5. What is the oldest hire date, and the most recent hire date for all employees in the programming department? SELECT MIN(hire_date), MAX(hire_date) FROM employees WHERE job_id like ‘%PROG%’;

  6. How many employees are there in department 60? How many departments are there? (Use complete employees table, not just the partial table shown here)

  7. How many employees are there in department 60? SELECT COUNT(*) FROM employees WHERE department_id = 60; How many departments are there (using all of the employees table found in HTML DB)? SELECT COUNT(DISTINCT department_id) FROM employees;

  8. Show the department_id and average salary for each department.

  9. Show the department_id and average salary for each department . SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

  10. Show the department_id, job_id, and total salary for each department.

  11. Show the department_id, job_id and the total salary for each job within the department (using full table instead of partial table above) . SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id;

  12. Show the department_id, and average salary for all departments where the average salary is greater $10,000. (Use full employees table found in HTML DB)

  13. Show the department_id, and average salary for all departments where the average salary is greater $10,000. (Use full employees table found in HTML DB) This results in an error: “group function not allowed here” SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 10000 GROUP BY department_id; SELECT department_id, AVG(salary) FROM employees HAVING AVG(salary) > 10000 GROUP BY department_id

  14. Show the department_id, and average salary for all departments where the maximum salary is greater $10,000. (Use full employees table found in HTML DB)

  15. Show the department_id, and average salary for all departments where the maximum salary is greater $10,000. (Use full employees table found in HTML DB) SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000

  16. Show the job id and the total salary for all jobs that aren’t in programming where the total salary is greater than $10,000. Arrange the data by the total salary in ascending order.

  17. Show the job id and the total salary for all jobs that aren’t in programming where the total salary is greater than $10,000. Arrange the data by the total salary in ascending order. SELECT job_id, SUM(salary) FROM employees WHERE job_id NOT LIKE '%PROG%' GROUP BY job_id HAVING SUM(salary) > 10000 ORDER BY SUM(salary)

  18. Assuming you don’t know what Matos’ salary is, write a query which will show all employees’ with a salary less than his salary.

  19. From subquery • SELECT last_name • FROM employees • WHERE salary < • (SELECT salary • FROM employees • WHERE last_name = 'Matos'); Assuming you don’t know what Matos’ salary is, write a query which will show all employees’ with a salary less than his salary.

  20. Find the job with the lowest average salary.(Use the full employees table)

  21. SELECT job_id, AVG(salary) FROM employees GROUP BY job_id HAVING AVG(salary) = (SELECT MIN(AVG(salary)) FROM employees GROUP BY job_id); From Subquery Result of full query Find the job with the lowest average salary.(Use the full employees table)

  22. Why will the following query not work? • SELECT employee_id, last_name • FROM employees • WHERE salary = • (SELECT MIN(salary) • FROM employees • GROUP BY department_id)

  23. Why will the following query not work? • SELECT employee_id, last_name • FROM employees • WHERE salary = • (SELECT MIN(salary) • FROM employees • GROUP BY department_id) Single row operator • This will work: • SELECT employee_id, last_name • FROM employees • WHERE salary IN • (SELECT MIN(salary) • FROM employees • GROUP BY department_id) IN, ANY, ALL are multiple-row comparison operators

  24. Why does the following query not work? • SELECT emp.last_name • FROM employees emp • WHERE emp.employee_id NOT IN • (SELECT mgr.manager_id • FROM employees mgr);

  25. Why does the following query not work? • SELECT last_name • FROM employees • WHERE employee_id NOT IN • (SELECT manager_id • FROM employees); Subquery returns NULL values • This one will work: • SELECT last_name • FROM employees • WHERE employee_id NOT IN • (SELECT manager_id • FROM employees • WHERE manager_id IS NOT NULL);

More Related