database programming

DATABASE PROGRAMMINGDATABASE PROGRAMMING

Sections 5-7

FIRST_NAME LAST_NAME SALARY HIRE_DATE JOB_IDDEPARTMENT_ID

Steven King 24000 17-Jun-87 AD_PRES 90Lex De Haan 17000 13-Jan-93 AD_VP 90Alexander Hunold 9000 03-Jan-90 IT_PROG 60Bruce Ernst 6000 21-May-91 IT_PROG 60Diana Lorentz 4200 07-Feb-99 IT_PROG 60Kevin Mourgos 5800 16-Nov-99 ST_MAN 50Randall Matos 2600 15-Mar-98 ST_CLERK 50Peter Vargas 2500 09-Jul-98 ST_CLERK 50Eleni Zlotkey 10500 29-Jan-00 SA_MAN 80

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 employeesWHERE job_id LIKE '%PROG%';AVG(SALARY) MAX(SALARY) MIN(SALARY)

6400 9000 4200



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



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



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 employeesWHERE job_id like ‘%PROG%’;

MIN(HIRE_DATE) MAX(HIRE_DATE)03-Jan-90 07-Feb-99

How many employees are there in department 60?

How many departments are there? (Use complete employees table, not just the partial table shown here)



SELECT COUNT(*)FROM employeesWHERE department_id = 60;

How many employees are there in department 60?

COUNT(*)3



How many departments are there (using all of the employees table found in HTML DB)?

SELECT COUNT(DISTINCT department_id)FROM employees; COUNT(DISTINCTDEPARTMENT_ID)

7



Show the department_id and average salary for each department.



Show the department_id and average salary for each department .

DEPARTMENT_ID AVG(SALARY)10 4400.0020 9500.0050 3500.0060 6400.0080 10033.3390 19333.33

110 10150.00(null) 7000.00

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id;



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



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;

DEPARTMENT_ID JOB_ID

SUM (SALARY)

(null) SA_REP 700010 AD_ASST 440020 MK_MAN 1300020 MK_REP 600050 ST_MAN 580050 ST_CLERK 1170060 IT_PROG 1920080 SA_MAN 1050080 SA_REP 1960090 AD_VP 3400090 AD_PRES 24000

110 AC_MGR 12000110AC_ACCOUNT 8300

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)

SELECT department_id, AVG(salary)FROM employeesWHERE AVG(salary) > 10000GROUP BY department_id;

This results in an error:

“group function not allowed here”

SELECT department_id, AVG(salary)FROM employeesHAVING AVG(salary) > 10000GROUP BY department_id DEPARTMENT_ID AVG(SALARY)

80 10033.3333390 19333.33333

110 10150

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)

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

DEPARTMENT_ID AVG(SALARY)20 950080 10033.3333390 19333.33333110 10150

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)

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) JOB_ID SUM(SALARY)SA_MAN 10500ST_CLERK 11700AC_MGR 12000MK_MAN 13000AD_PRES 24000SA_REP 26600AD_VP 34000

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.



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


Steven King 24000 17-Jun-87 AD_PRES 90Lex De Haan 17000 13-Jan-93 AD_VP 90Alexander Hunold 9000 03-Jan-90 IT_PROG 60Bruce Ernst 6000 21-May-91 IT_PROG 60Diana Lorentz 4200 07-Feb-99 IT_PROG 60Kevin Mourgos 5800 16-Nov-99 ST_MAN 50

Randall Matos 2600 15-Mar-98 ST_CLERK 50

Peter Vargas 2500 09-Jul-98 ST_CLERK 50Eleni Zlotkey 10500 29-Jan-00 SA_MAN 80

From subquery

SELECT last_nameFROM employeesWHERE salary <

(SELECT salaryFROM employeesWHERE last_name = 'Matos');

LAST_NAMEVargas

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

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



SELECT job_id, AVG(salary)

FROM employees

GROUP BY job_id

HAVING AVG(salary) = (SELECT MIN(AVG(salary))

FROM employees

GROUP BY job_id);

MIN(AVG(SALARY))2925

JOB_ID AVG(SALARY)ST_CLERK 2925

From Subquery

Result of full query

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

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)

This will work:SELECT employee_id, last_nameFROM employeesWHERE salary IN

(SELECT MIN(salary)FROM employeesGROUP BY department_id)

Why will the following query not work?SELECT employee_id, last_nameFROM employeesWHERE salary =

(SELECT MIN(salary)FROM employeesGROUP BY department_id)

Single row operator

IN, ANY, ALL are multiple-row comparison operators

Why does the following query not work?

SELECT emp.last_nameFROM employees empWHERE emp.employee_id NOT IN

(SELECT mgr.manager_idFROM employees mgr);

Why does the following query not work?

SELECT last_nameFROM employees WHERE employee_id NOT IN

(SELECT manager_idFROM employees);

Subquery returns NULL values

This one will work:

SELECT last_nameFROM employeesWHERE employee_id NOT IN

(SELECT manager_idFROM employees WHERE manager_id IS NOT NULL);

database programming

Documents

programming department

recent hire date

oldest hire date

d haan1700013

datejo haan1700013

prog60bru avgsalary

minimum salaries

datefrom employeeswhere