SQL practice

Knowledge Map

• SQL practice
  • Knowledge Map
    • 1: Hyperskill
      • Projects
    • 2: SoftUni
    • 3: Practical SQL (book)
      • Ch01: Setting Up Your Coding Environment
      • Ch02: Creating Your First Database and Table
      • Ch03: Beginning Data Exploration with SELECT
      • Ch04: Understading Data Types
      • Ch05: Importing and Exporting Data
      • Ch06: Basic Math and Stats with SQL
      • Ch07: Joining Tables in a Relational Database
      • Ch08: Table Design That Works for You
      • Ch09: Extracting Information by Grouping and Summarizing
      • Ch10: Inspecting and Modifying Data
      • Ch11: Statistical Functions in SQL
      • Ch12: Working with Dates and Times
      • Ch13: Advanced Query Techniques
      • Ch14: Mining Text to Find Meaningful Data
      • Ch15: Analyzing Spatial Data with PostGIS
      • Ch16: Working with JSON Data
    • 4: SQL Puzzles and Answers (book)

1: Hyperskill

• Hyperskill DB exercises

Projects

• Electronics Store Customer ⭐4.4 3hr
  • Stages
    • 1 - Fast PCs
    • 2 - Average printer price
    • 3 - Total cost of laptops
    • 4 - PCs and laptops
    • 5 - High-end laptop vs. PC Prices
    • 6 - Ranking PCs by RAM
• Databases for Sales ⭐4.2 7hr
• Hacker Attack ⭐4.5 7hr
  • Stages
    • 1 - School members
    • 2 - Who are you?
    • 3 - You are a student
    • 4 - Plenty of files
    • 5 - I know your score
    • 6 - Grade codes
    • 7 - Land ho!
• Library Management System ⭐4.0 6hr
• School of Wizards and Magic Wars ⭐4.3 9hr
• Computer Store Database ⭐4.2 7hr
  • Stages
    • 1 - Expensive printers
    • 2 - Marketable laptops
    • 3 - Uniques
    • 3 - Trust no one

2: SoftUni

• SoftUni DB exercises

3: Practical SQL (book)

• Anthony DeBarros, No Starch Press

Ch01: Setting Up Your Coding Environment

listing	solution	num
01-01	check postgress version	1

---

Ch02: Creating Your First Database and Table

listing	solution	num
02-01	create a database - analysis	2
02-02	create table - teachers	3
02-03	insert data - teachers	4

---

Ch03: Beginning Data Exploration with SELECT

listing	solution	num
03-01	querying all rows and columns - teachers	5
03-02	querying a subset of columns	6
03-03	sorting a column with order by	7
03-04	sorting multiple columns with order by	8
03-05	querying distinct values	9
03-06	querying distinct pairs of values	10
03-07	filtering rows using where	11
03-08	filtering with like and ilike	12
03-09	combining operators using and & or	13
03-10	a select statement including where and order by	14

---

Ch04: Understading Data Types

listing	solution	num
04-01	character data types in aciton	15
04-02	number data types in action	16
04-03	rounding issues with float columns	17
04-04	the timestamp and interval types in action	18
04-05	using the interval data type	19
04-06	three cast() examples	20

---

Ch05: Importing and Exporting Data

listing	solution	num
05-01	using copy for data import	21
05-02	create table statement for census county population estimates	22
05-03	importing census data using copy	23
05-04	creating a table to track superviser salaries	24
05-05	importing salaries data from csv to three table columns	25
05-06	importing a subset of rows with where	26
05-07	using a temporary table to add a defalt value to a column during import	27
05-08	exporting an entire table with copy	28
05-09	exporting selected columns from a table with copy	29
05-10	exporting query result with copy	30

---

Ch06: Basic Math and Stats with SQL

listing	solution	num
06-01	basic addition, subtraciton and multiplicatin with sql	31
06-02	integer and decimal division with sql	32
06-03	exponents, roots, and factorials with sql	33
06-04	selecting census population estimate columns with aliases	34
06-05	subtracting two columns in us_counties_pop_est_2019	35
06-06	checking census data totals	36
06-07	calculating the percent of a county's area that is water	37
06-08	calculating the percent change	38
06-09	using the sum() and avg() aggregate functions	39
06-10	testing sql percentile functions	40
06-11	using sum(), avg(), and percentile_cont() aggregate functions	41
06-12	passing an array of values to percentile_cont()	42
06-13	using unnest() to turn an array into rows	43
06-14	finding the most frequent value with mode()	44

---

Ch07: Joining Tables in a Relational Database

listing	solution	num
07-01	creating the departments and employees table	45
07-02	joining the employees and departments table	46
07-03	creating two tables to explore join types	47
07-04	using join	48
07-05	join with using	49
07-06	using left join	50
07-07	using right join	51
07-08	using full outer join	52
07-09	using cross join	53
07-10	filtering to show missing values with is null	54
07-11	querying specific columns in a join	55
07-12	simplifying code with table aliases	56
07-13	joining multiple tables	57
07-14	combining query results with union	58
07-15	combining query results with union all	59
07-16	customizing a union query	60
07-17	combining query results with intersect and except	61
07-18	performing math on joined census tables	62

---

Ch08: Table Design That Works for You

listing	solution	num
08-01	declaring a single-column natural key as a primary key	63
08-02	an example of a primary key violation	64
08-03	declaring a composite primary key as a natural key	65
08-04	example of a composite primary key violation	66
08-05	declaring a bigint column as a surrogate key using identity	67
08-06	restarting an identity sequence	68
08-07	a foreign key example	69
08-08	examples of check constraints	70
08-09	a unique constraint example	71
08-10	a not null constraint example	72
08-11	dropping and adding a primary key and a not null constraint	73
08-12	importing new york city address data	74
08-13	benchmark queries for index performance	75
08-14	creating a b-tree index on the new_york_addresses table	76

---

Ch09: Extracting Information by Grouping and Summarizing

listing	solution	num
09-01	creating and filling the 2018 public libraries survey table	77
09-02	creating and filling the 2017 and 2016 public libraries survey table	78
09-03	using count() for table row counts	79
09-04	using count() for the number of values in a column	80
09-05	using count() for the number of distinct values in a column	81
09-06	finding the most and fewest vists using max() and min()	82
09-07	using group by on the stabr column	83
09-08	using group by on the city and stabr columns	84
09-09	using group by with count() on the stabr column	85
09-10	using group by with count() of the stabr and stataddr columns	86
09-11	using the sum() aggregate function to total visits to libraries in 2016, 2017, and 2018	87
09-12	using sum() to total visits on joined 2018, 2017, and 2016 tables	88
09-13	using group by to track percent change in library visits by state	89
09-14	using a having clause to filter the results of an aggregate query	90

---

Ch10: Inspecting and Modifying Data

listing	solution	num
10-01	importing the fsis meat, poultry, and egg inspection directory	91
10-02	finding multiple companies at the same address	92
10-03	grouping and counting states	93
10-04	using is null to find missing values in the st column	94
10-05	using group by and count() to find inconsistent company names	95
10-06	using length() and count() to test the zip column	96
10-07	filtering with length() to find short zip values	97
10-08	backing up a table	98
10-09	creating and filling the st_copy column with alter table and update	99
10-10	checking values in the st and st_copy columns	100
10-11	updating the st column for the three establishments	101
10-12	restoring original st column values	102
10-13	creating and filling the company_standard column	103
10-14	using an update statement to modify column values that match a string	104
10-15	creating and filling the zip_copy column	105
10-16	modifying codes in the zip column missing two leading zeros	106
10-17	modifying codes in the zip column missing one leading zero	107
10-18	creating and filling a state_regions table	108
10-19	adding and updating an inspection_deadline column	109
10-20	viewing updated inspection_date values	110
10-21	deleting rows matching an expression	111
10-22	removing a column from a table using drop	112
10-23	removing a table from a databse using drop	113
10-24	demonstrating a transaction block	114
10-25	backing up a table while adding and filling a new column	115
10-26	swapping table names using alter table	116

---

Ch11: Statistical Functions in SQL

listing	solution	num
11-01	creating a 2014-2018 acs 5-year estimates table and importing data	119
11-02	using corr(y, x) to measure the relationship between educaiton and income	120
11-03	using corr(y, x) on additional variables	121
11-04	regression slope and intercept function	122
11-05	calculating the coefficient of determination, or r-squared	123
11-06	using the rank() and dense_rank() window functions	124
11-07	applying rank() within groups using partition by	125
11-08	creating and filling a table for census county business pattern data	126
11-09	finding business rates per thousand population in counties with 50,000 or more people	127
11-10	creating a rolling average for export data	128

---

Ch12: Working with Dates and Times

listing	solution	num
12-01	extracting the components of a timestamp value	129
12-02	three functions for making datetimes from components	130
12-03	comparing current_timestamp and clock_timestamp() during row insert	131
12-04	viewing your current time zone setting	132
12-04	showing time zone abbreviations and names	133
12-05	setting the time zone for a client session	134
12-06	creating a table and importing nyc yellow taxi data	135
12-07	counting taxi trips by hour	136
12-08	exporting taxi pickups per hour to a csv file	137
12-09	calculating median trip time by hour	138
12-10	creating a table to hold train trip data	139
12-11	calculating the length of each trip segment	140
12-12	calculating cumulative intervals using over	141
12-13	using justify_interval() to better format cumulative trip duration	142

---

Ch13: Advanced Query Techniques

listing	solution	num
13-01	using a subquery in a where clause	117
13-02	using a subquery in where clause with delete	118
13-03	subquery as a derived table in a from clause	143
13-04	joining two derived tables	144
13-05	adding a subquery to a column list	145
13-06	using a subquery in a calculation	146
13-07	creating and filling a retirees table	147
13-08	generating values for the in operator	148
13-09	using a correlated subquery with where exists	149
13-10	using a correlated subquery with where not exists	150
13-11	using lateral subqueries in the from clause	151
13-12	using a subquery with a lateral join	152
13-13	using a simple cte to count large counties	153
13-14	using ctes in a table join	154
13-15	using ctes to minimize redundant code	155
13-16	creating and filling the ice_cream_survey.csv	156
13-17	generating the ice cream survey crosstab	157
13-18	creating and filling a temperature_readings table	158
13-19	generating the temperature readings crosstab	159
13-20	reclassifying temperature data with case	160
13-21	using case in a cte	161

---

Ch14: Mining Text to Find Meaningful Data

listing	solution	num
14	using regular expressions in a where clause	162
14	regular expression functions to replace and split text	163
14	finding an array length	164
14	creating and loading the crime_reports table	165
14	using regexp_match() to find the first date	166
14	using the regexp_matches() function with the 'g' flag	167
14	using regexp_match() to find the second date	168
14	using a capture group to return only the date	169
14	matching case number, date, crime type, and city	170
14	retrieving a value from within an array	171
14	updating the crime_reports date_1 column	172
14	updating all crime_reports columns	173
14	viewing selected crime data	174
14	converting text to tsvector data	175
14	converting search terms to tsquery data	176
14	querying a tsvector type with a tsquery	177
14	creating and filling the president_speeches table	178
14	converting speeches to tsvector in the search_speech_text column	179
14	creating a gin index for text search	180
14	finding speeches containing the word "vietnam"	181
14	displaying search results with ts_headline()	182
14	finding speeches wth the word 'transportation' but not 'roads'	183
14	finding speeches where 'defense' follows 'military'	184
14	scoring relevance with ts_rank()	185
14	normalizing ts_rank() by speech length	186

---

Ch15: Analyzing Spatial Data with PostGIS

listing	solution	num
15	loading the postgis extension	187
15	retrieving the well-known text for srid 4326	188
15	using st_geomfromtext() to create spatial objects	189
15	using st_geogfromtext() to create spatial objects	190
15	functions specific to making points	191
15	functions specific to making linestrings	192
15	functions specific to making polygons	193
15	creating and loading the farmers_markets table	194
15	creating and indexing a geography column	195
15	using st_dwithin() to locaate farmers market within 10 kilometers of a point	196
15	using st_distance() to calculate the miles between yankee stadium	197
15	using st_distance() for each row in farmers_markets	198
15	usign the <-> distance operator for a nearest neighbors search	199
15	checking the geom column's well-known text representation	200
15	finding the largest counties by area using st_area()	201
15	using st_within() to find the county belonging to a pair of coordinates	202
15	using st_dwithin() to count people near lincoln, nebraska	203
15	displaying countries near lincoln, nebraska	204
15	using st_geometrytype() to determine geometry	205
15	spatial join with st_intersects() to find roads crossing the santa fe river	206
15	using st_intersection() to show where roads cross the river	207

---

Ch16: Working with JSON Data

listing	solution	num
16	json with information about two films	208
16	creating a table to hold json data and adding an index	209
16	retrieving a json key value with field extraction operators	210
16	retrieving a json aray value with element extraction operators	211
16	retrieving a json key value with path extraction operators	212
16	demonstrating the @> containment operator	213
16	using a containment operator in a where clause	214
16	demonstrating the <@ containment operator	215
16	demonstrating existence operators	216
16	json with data on one earthquake	217
16	creating and loading an earthquakes table	218
16	retrieving the earthquake time	219
16	converting the time value to a timestamp	220
16	finding the minimum and maximum earthquake times	221
16	finding the five earthquakes with the largest magnitude	222
16	finding earthquakes with the most did you feel it reports	223
16	extracting the earthquake's location data	224
16	converting json location data to postgis geography	225
16	converting json coordinates to a postgis geometry column	226
16	finding earthquakes within 50 miles of downtown tulsa, oklahoma	227
16	turning query results into json with to_json()	228
16	specifying columns to convert to json	229
16	generating key names with a subquery	230
16	aggregating the rows and converting to json	231
16	adding a top-level key/value pair via concatenation	232
16	setting an array value at a path	233
16	deleting values from json	234
16	finding the length of an array	235
16	returning array elements as rows	236
16	returning key values from each item in an array	237

---

Ch17: Saving Time with Views, Functions, and Triggers

listing	solution	num
17	creating a view that displays nevada 2019 counties	238
17	querying the nevada_counties_pop_2019	239

---

4: SQL Puzzles and Answers (book)

• Joe Celko's book