sql-querying-and-database-design/Morgan_Assignment6.sql at main · KadenaPSG/sql-querying-and-database-design · GitHub

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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
/*
    Name: Gilbert Morgan
    DTSC660: Data and Database Management with SQL
    Assignment 6
*/

--------------------------------------------------------------------------------
/*				    Chosen Data Set and Reason for Selecting		          */
--------------------------------------------------------------------------------

/*
I selected the Data Science Salaries dataset because it directly relates to my future career path.
Since I am getting a MS in Data Science I thought it would make sense to do some salary research.
This data may help me better understand what compensation might look like after graduation and what roles to target.
*/

--------------------------------------------------------------------------------
/*				                 Select Statement      		  		          */
--------------------------------------------------------------------------------

SELECT * FROM salaries LIMIT 5;

--------------------------------------------------------------------------------
/*				                   Backup Table     		  		          */
--------------------------------------------------------------------------------

DROP TABLE IF EXISTS salaries_backup;

CREATE TABLE salaries_backup AS
SELECT * FROM salaries;

--------------------------------------------------------------------------------
/*				                 Duplicate Column      		  		          */
--------------------------------------------------------------------------------

ALTER TABLE salaries
ADD COLUMN total_yearly_compensation_duplicate NUMERIC;

UPDATE salaries
SET total_yearly_compensation_duplicate = total_yearly_compensation;

--------------------------------------------------------------------------------
/*		              Question 1 - Indentification query                      */
--------------------------------------------------------------------------------

SELECT DISTINCT tag FROM salaries
WHERE tag ILIKE 'NA';

--------------------------------------------------------------------------------
/*				          Question 1 - Update query                           */
--------------------------------------------------------------------------------

UPDATE salaries
SET tag = NULL
WHERE tag ILIKE 'NA';

--------------------------------------------------------------------------------
/*				        Question 1 - Validation query                         */
--------------------------------------------------------------------------------

SELECT * FROM salaries
WHERE tag IS NULL;

--------------------------------------------------------------------------------
/*				        Question 1 - Rationale Comment                        */
--------------------------------------------------------------------------------

/*
I originally checked for 'NA' in the location column but realized it didn’t apply. Instead,
I found 'NA' in the tag column — likely a placeholder for missing job focus info.
I replaced it with NULL so SQL treats it properly, especially in filtering or grouping by tag.
*/

--------------------------------------------------------------------------------
/*		              Question 2 - Indentification query                      */
--------------------------------------------------------------------------------

SELECT * FROM salaries
WHERE total_yearly_compensation = 0;

--------------------------------------------------------------------------------
/*				          Question 2 - Update query                           */
--------------------------------------------------------------------------------

UPDATE salaries
SET total_yearly_compensation = (
    SELECT ROUND(AVG(total_yearly_compensation)::NUMERIC, 2)
    FROM salaries
    WHERE total_yearly_compensation > 0
)
WHERE total_yearly_compensation = 0;

--------------------------------------------------------------------------------
/*				        Question 2 - Validation query                         */
--------------------------------------------------------------------------------

SELECT * FROM salaries
WHERE total_yearly_compensation = 0;

--------------------------------------------------------------------------------
/*				        Question 2 - Rationale Comment                        */
--------------------------------------------------------------------------------

/*
Some of the entries had a salary of 0, which doesn’t make much sense for a job listing.
That’s probably a mistake or missing data. I replaced those with the average salary from the dataset
so the numbers are more realistic and don’t throw off any future analysis or comparisons.
*/

--------------------------------------------------------------------------------
/*		              Question 3 - Indentification query                      */
--------------------------------------------------------------------------------

SELECT DISTINCT title FROM salaries
WHERE title ILIKE '%sr%';

--------------------------------------------------------------------------------
/*				          Question 3 - Update query                           */
--------------------------------------------------------------------------------

UPDATE salaries
SET title = 'Senior Data Scientist'
WHERE title ILIKE 'sr%';

--------------------------------------------------------------------------------
/*				        Question 3 - Validation query                         */
--------------------------------------------------------------------------------

SELECT DISTINCT title FROM salaries
WHERE title = 'Senior Data Scientist';

--------------------------------------------------------------------------------
/*				        Question 3 - Rationale Comment                        */
--------------------------------------------------------------------------------

/*
There were a bunch of slightly different versions of the same job title — like 'Sr.', 'Sr', and 'Sr Data Scientist'.
Even though they all mean the same thing, having them written differently can mess up analysis.
So I standardized them all to 'Senior Data Scientist' to keep things clean and make grouping more accurate.
*/

--------------------------------------------------------------------------------
/*		              Question 4 - Indentification query                      */
--------------------------------------------------------------------------------

SELECT total_yearly_compensation, NULL AS compensation_tier FROM salaries LIMIT 10;

--------------------------------------------------------------------------------
/*				          Question 4 - Update query                           */
--------------------------------------------------------------------------------

ALTER TABLE salaries
ADD COLUMN compensation_tier TEXT;

UPDATE salaries
SET compensation_tier = CASE
    WHEN total_yearly_compensation < 50000 THEN 'Low'
    WHEN total_yearly_compensation BETWEEN 50000 AND 100000 THEN 'Mid'
    ELSE 'High'
END;

--------------------------------------------------------------------------------
/*				        Question 4 - Validation query                         */
--------------------------------------------------------------------------------

SELECT total_yearly_compensation, compensation_tier FROM salaries LIMIT 10;

--------------------------------------------------------------------------------
/*				        Question 4 - Rationale Comment                        */
--------------------------------------------------------------------------------

/*
I added a new column called compensation_tier to break salaries into simple categories like Low, Mid, and High.
This makes it way easier to spot trends and compare different roles or locations without digging through a bunch of numbers.
It also helps when you want to quickly group people based on how much they’re making.
*/