-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathFortune 500 Analysis Project
97 lines (82 loc) · 4.34 KB
/
Fortune 500 Analysis Project
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
89
90
91
92
93
94
95
96
97
#This project utilizes the following Fortune 500 Analysis data:
CREATE TABLE fortune_companies (
company_id INTEGER PRIMARY KEY,
company_name TEXT,
industry TEXT,
revenue REAL,
employees INTEGER,
healthcare_benefits BIT,
paid_time_off_days INTEGER,
maternity_leave_weeks INTEGER,
avg_employee_tenure REAL
);
INSERT INTO fortune_companies (company_name, industry, revenue, employees, healthcare_benefits, paid_time_off_days, maternity_leave_weeks, avg_employee_tenure)
VALUES
('Apple Inc.', 'Technology', 365.7, 147000, 1, 20, 12, 4.5),
('Walmart Inc.', 'Retail', 523.96, 2200000, 1, 15, 8, 6.2),
('Exxon Mobil Corporation', 'Energy', 265.01, 72000, 0, 18, 6, 7.8),
('Amazon.com Inc.', 'Technology', 386.06, 1370000, 1, 22, 14, 5.1),
('JPMorgan Chase & Co.', 'Financials', 160.1, 255998, 1, 21, 12, 6.9),
('Verizon Communications Inc.', 'Telecommunications', 131.88, 132600, 0, 15, 6, 5.5),
('Company A', 'Retail', 235.4, 2000, 1, 18, 10, 5.8),
('Company B', 'Healthcare', 400.7, 2300, 1, 22, 13, 5.7),
('Company C', 'Manufacturing', 300.2, 2000, 1, 18, 10, 5.8),
('Company D', 'Healthcare', 150.5, 3500, 1, 20, 12, 6.5),
('Company E', 'Finance', 280.7, 1800, 0, 14, 8, 4.2),
('Company F', 'Technology', 420.1, 2500, 1, 22, 14, 7.1),
('Company G', 'Retail', 190.8, 1500, 1, 16, 9, 5.3),
('Company H', 'Energy', 280.5, 2200, 0, 15, 8, 6.8),
('Company I', 'Telecommunications', 110.3, 1800, 1, 19, 11, 4.9),
('Company J', 'Manufacturing', 390.6, 2700, 1, 21, 13, 6.2),
('Company K', 'Healthcare', 180.2, 3200, 1, 17, 9, 7.4),
('Company L', 'Finance', 230.4, 1900, 0, 13, 7, 5.6),
('Company M', 'Technology', 340.9, 2800, 1, 23, 15, 6.9),
('Company N', 'Retail', 200.6, 1600, 1, 15, 8, 4.7),
('Company O', 'Energy', 260.2, 2400, 0, 14, 7, 6.1),
('Company P', 'Telecommunications', 130.5, 2100, 1, 20, 12, 5.3),
('Company Q', 'Manufacturing', 360.0, 2900, 1, 22, 14, 7.8),
('Company R', 'Technology', 400.7, 2300, 1, 22, 13, 5.7),
('Company S', 'Retail', 210.8, 1600, 0, 16, 9, 4.9),
('Company T', 'Energy', 290.5, 2200, 1, 15, 8, 7.2),
('Company U', 'Telecommunications', 140.3, 1900, 1, 20, 12, 6.1),
('Company V', 'Manufacturing', 350.6, 2800, 1, 22, 14, 5.4),
('Company W', 'Healthcare', 160.2, 3300, 0, 18, 10, 4.8),
('Company X', 'Finance', 240.4, 2000, 1, 13, 7, 7.1),
('Company Y', 'Technology', 320.9, 2700, 1, 23, 15, 5.6),
('Company Z', 'Retail', 180.6, 1400, 0, 14, 8, 6.3),
('Company AA', 'Energy', 240.2, 2600, 1, 17, 9, 6.5),
('Company BB', 'Telecommunications', 120.5, 2100, 0, 19, 11, 4.5),
('Company CC', 'Manufacturing', 380.0, 3000, 1, 21, 13, 7.3),
('Company DD', 'Healthcare', 170.2, 3200, 1, 17, 9, 5.8),
('Company EE', 'Finance', 250.4, 1900, 0, 12, 6, 6.4),
('Company FF', 'Technology', 300.9, 2500, 1, 24, 16, 6.9),
('Company GG', 'Retail', 190.6, 1700, 0, 13, 7, 5.2),
('Company HH', 'Energy', 280.2, 2300, 1, 16, 9, 6.8),
('Company II', 'Telecommunications', 110.5, 2000, 1, 21, 12, 4.9),
('Company JJ', 'Manufacturing', 370.0, 3100, 1, 20, 12, 7.6),
('Company KK', 'Healthcare', 150.2, 3400, 0, 16, 8, 5.3);
#Project:
#Do each of the following at least once:
#Group the data in a new way using a CASE statement.
SELECT COUNT(*) AS "Amount of Companies",
CASE WHEN paid_time_off_days >=20 THEN "More Than 20 Days"
WHEN paid_time_off_days >=15 THEN "15 to 19 days"
WHEN paid_time_off_days >=10 THEN "10 to 14 days"
ELSE "under 10 days" END AS "Paid_Time_Off"
FROM fortune_companies
GROUP BY Paid_Time_Off
#Use a HAVING clause to determine something interesting about the data per category.
SELECT industry, ROUND(SUM(revenue),2) AS total_revenue_million
FROM fortune_companies
GROUP BY industry
HAVING total_revenue_million>=1500;
#Use logical operators like AND or OR to filter the data in an interesting way.
SELECT company_name, healthcare_benefits, avg_employee_tenure
FROM fortune_companies
WHERE healthcare_benefits=1 AND avg_employee_tenure >=6
#Use an aggregate function like AVG, SUM, COUNT, MAX, and/or MIN to return summary statistics about the data.
SELECT industry, healthcare_benefits, avg_employee_tenure, ROUND(SUM(revenue),2) AS total_revenue_million
FROM fortune_companies
WHERE healthcare_benefits=1 AND avg_employee_tenure >=6
GROUP BY industry
HAVING total_revenue_million>=1000