-
Notifications
You must be signed in to change notification settings - Fork 0
/
analysis.sql
93 lines (57 loc) · 2.6 KB
/
analysis.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
SELECT id, lastname, firstname, gender, s.salary
FROM employees AS e
JOIN salaries AS s
ON e.id = s.employee_id;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT *
FROM employees
WHERE DATE_PART('year', startdate) IN (SELECT *
FROM(
SELECT DATE_PART('year',startdate)
FROM employees
) AS s
WHERE date_part = 1986)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT department_id AS "Department ID",
d.name AS "Department Name",
m.id AS "Manager ID",
lastname AS "Manager Last-Name",
firstname AS "Manager First-Name",
m.startdate AS "Manager Start Date"
FROM departments_managers AS m
JOIN departments d
ON m.department_id = d.id
JOIN employees e
ON m.id = e.id;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT d."name", e."id", e.lastname, e.firstname
FROM employees e
JOIN departments_employees de
ON e.id = de.employee_id
JOIN departments d
ON de.department_id = d.id
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT *
FROM employees
WHERE firstname = 'Hercules' AND lastname LIKE 'B%'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT d."name", e."id", e.lastname, e.firstname
FROM employees e
JOIN departments_employees de
ON e.id = de.employee_id
JOIN departments d
ON de.department_id = d.id
WHERE d.name = 'Sales'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT d."name", e."id", e.lastname, e.firstname
FROM employees e
JOIN departments_employees de
ON e.id = de.employee_id
JOIN departments d
ON de.department_id = d.id
WHERE d.name = 'Sales' OR d.name = 'Development'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT lastname, COUNT(*)
FROM employees
GROUP BY lastname
ORDER BY count DESC