Beispiel - Queries in PostgreSQL

• Involvierte Definitionen:
  • RDBMS
• Veranstaltung: DEDS
• Referenz: Einsendeaufgaben zu KE6

⠀

Beispiel: Queries in PostgreSQL

Sei folgendes Beispiel gegeben:

In PostgreSQL können wir die folgenden Queries wie folgt lösen:

Anzahl Projekte pro Department:

SELECT 
	Department.id,
	Department.name, 
	COUNT(Project.id) as count_project
FROM
	Department
INNER JOIN
	Project on Department.id = Project.department_id
GROUP BY
	Department.id;

Durchschnittliche Dauer aller Projekte:

SELECT 
	AVG(end_date - start_date) as avg_project_duration_days
FROM
	Project;

Durchschnittliche Projektdauer pro Department:

SELECT 
	Department.id,
	Department.name,
	AVG(Project.end_date - Project.start_date) as avg_proj_duration_days
FROM 
	Department
INNER JOIN
	Project on Department.id = Project.department_id
GROUP BY 
	Department.id;

Durchschnittliche Satisfaction pro Department:

SELECT
	Department.id,
	Department.name,
	AVG(CAST(Employee.satisfied AS INTEGER)) as avg_satisfied
FROM 
	Department
INNER JOIN 
	Employee on Department.id = Employee.department_id
GROUP BY
	Department.id;

Durchschnittliche Projektdauer des “Technology” Departments:

SELECT
	Department.id,
	Department.name,
	AVG(Project.end_date - Project.start_date) as avg_proj_duration_days
FROM
	Department
INNER JOIN 
	Project on Department.id = Project.department_id
WHERE
	Department.name = 'Technology'
GROUP BY
	Department.id;

Satisfaction des “Technology” Departments:

SELECT 
	Department.id,
	Department.name,
	AVG(CAST(Employee.satisfied AS INTEGER)) as avg_satisfied
FROM 
	Department
INNER JOIN 
	Employee on Department.id = Employee.department_id
WHERE
	Department.name = 'Technology'
GROUP BY
	Department.id;