{ "cells": [ { "cell_type": "markdown", "id": "c36ccc67", "metadata": {}, "source": [ "# SQL Query Primer\n", "\n", "## Query Clauses\n", "SQL offers six clauses ({ref}`query-clauses`) to query your data. All SQL queries will use at least two clauses, i.e., `SELECT` and `FROM`. \n", "\n", "```{table} Query Clauses\n", ":name: query-clauses\n", "| **Clause Name** \t| **Purpose** \t|\n", "|-----------------\t|---------------------------------------------------------------------------------------\t|\n", "| SELECT \t| Determines which columns to include in the query's result set \t|\n", "| FROM \t| Identifies the tables from which to retrieve data and how the tables should be joined \t|\n", "| WHERE \t| Filters out unwanted data \t|\n", "| GROUP BY \t| Used to group rows together by common column values \t|\n", "| HAVING \t| Filters out unwanted groups \t|\n", "| ORDER BY \t| Sorts the rows of the final result set by one or more columns \t|\n", "```\n", "\n", "(sql:primer:basic)=\n", "### Basic query\n", "The most basic SQL query will have a `SELECT` and `FROM` clause. Select lets you choose the columns\n", "you want. In case you want all the columns, you can use `*`, which indicates to SQL you want all the columns. \n", "The `FROM` clause lets you specify the table you want to query. The following is the most basic SQL query:\n", "You can select all the columns by using `*` after the `SELECT`. Note all SQL queries are terminated \n", "by a semicolon (`;`). You can format your SQL with as many spaces and tabs as you like. To indicate to SQL\n", "that your query statement is complete, terminate it with a semicolon. \n", "\n", "Query: Select all columns and all rows from the `PatientCorePopulatedTable` table. \n", "\n", "```sql\n", "SELECT\n", " *\n", "FROM\n", " PatientCorePopulatedTable;\n", "```\n", "\n", "By default all the rows are selected since no filtering is applied." ] }, { "cell_type": "code", "execution_count": 1, "id": "bd1e4d68", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PatientIDPatientGenderPatientDateOfBirthPatientRacePatientMaritalStatusPatientLanguagePatientPopulationPercentageBelowPoverty
0FB2ABB23-C9D0-4D09-8464-49BF0B982F0FMale1947-12-28 02:45:40.547UnknownMarriedIcelandic18.080000
164182B95-EB72-4E2B-BE77-8050B71498CEMale1952-01-18 19:51:12.917African AmericanSeparatedEnglish13.030000
2DB22A4D9-7E4D-485C-916A-9CD1386507FBFemale1970-07-25 13:04:20.717AsianMarriedEnglish6.670000
36E70D84D-C75F-477C-BC37-9177C3698C66Male1979-01-04 05:45:29.580WhiteMarriedEnglish16.090000
4C8556CC0-32FC-4CA5-A8CD-9CCF38816167Female1921-04-11 11:39:49.197WhiteMarriedEnglish18.200000
57FD13988-E58A-4A5C-8680-89AC200950FAMale1965-07-12 15:41:20.523WhiteMarriedSpanish12.410000
6C60FE675-CA52-4C55-A233-F4B27E94987FMale1957-10-30 23:26:15.303AsianMarriedSpanish12.800000
7B39DC5AC-E003-4E6A-91B6-FC07625A1285Female1935-11-03 21:07:09.040WhiteMarriedEnglish15.310000
8FA157FA5-F488-4884-BF87-E144630D595CFemale1932-11-01 06:19:56.577WhiteSingleEnglish16.320000
9B7E9FC4C-5182-4A34-954E-CEF5FC07E96DFemale1985-12-11 02:48:16.907UnknownSingleEnglish11.430000
101A40AF35-C6D4-4D46-B475-A15D84E8A9D5Male1949-01-10 15:37:35.543WhiteMarriedEnglish11.250000
11DA6CECFF-DE13-4C4C-919F-64E1A2B76C9DMale1924-06-27 19:37:58.823AsianDivorcedEnglish83.750000
120A9BA3E4-CF3C-49C4-9774-5EEA2EE7D123Male1980-04-04 07:59:15.820WhiteSingleSpanish14.280000
137C788499-7798-484B-A027-9FCDC4C0DADBMale1926-08-13 10:22:16.247WhiteMarriedEnglish11.890000
14C54B5AAD-98E8-472D-BAA0-638D9F3BD024Female1972-11-13 22:50:43.600African AmericanSingleUnknown94.000000
156985D824-3269-4D12-A9DD-B932D640E26EFemale1967-10-27 03:58:29.027WhiteMarriedEnglish12.010000
16D8B53AA2-7953-4477-9EA4-68400EBAAC5CMale1951-06-11 20:11:10.003African AmericanUnknownEnglish16.890000
17DB92CDC6-FA9B-4492-BC2C-0C588AD78956Male1977-06-30 01:46:05.467UnknownSingleIcelandic1.700000
1825B786AF-0F99-478C-9CFA-0EA607E45834Male1926-08-20 00:21:38.870WhiteMarriedEnglish93.600000
19F00C64F8-2033-4640-80FE-F1F62CBE26A5Female1957-06-04 18:31:01.177African AmericanSingleEnglish15.720000
20B70E5A76-F2BC-41E4-B037-CD4D9ABA0967Male1962-12-15 01:59:01.737WhiteSingleIcelandic13.100000
21F0B53A2C-98CA-415D-B928-E3FD0E52B22AMale1950-06-20 10:31:18.337African AmericanMarriedEnglish14.870000
221A8791E3-A61C-455A-8DEE-763EB90C9B2CMale1973-08-16 10:58:34.413AsianSingleEnglish13.970000
2381C5B13B-F6B2-4E57-9593-6E7E4C13B2CEFemale1930-05-28 02:59:42.857WhiteMarriedIcelandic19.220000
24220C8D43-1322-4A9D-B890-D426942A3649Male1971-03-13 18:31:24.877AsianMarriedEnglish16.710000
25C242E3A4-E785-4DF1-A0E4-3B568DC88F2EMale1963-02-23 21:46:28.840AsianSingleSpanish7.590000
260BC491C5-5A45-4067-BD11-A78BEA00D3BEFemale1921-04-18 01:56:01.807UnknownMarriedEnglish18.050000
2779A7BA2A-D35A-4CB8-A835-6BAA13B0058CFemale1952-05-03 02:30:21.563WhiteSingleSpanish16.240000
28FFCDECD6-4048-4DCB-B910-1218160005B3Male1941-05-06 14:56:42.687WhiteSingleEnglish14.490000
29CD2ADB1B-97F7-4EF6-BC5C-3E0EC562A06FFemale1963-04-16 08:54:08.647WhiteSingleIcelandic11.160000
30A50BE9B4-8A0B-4169-B894-F7BD86D7D90BFemale1987-04-18 16:31:16.333AsianDivorcedEnglish14.070000
3169CC25ED-A54A-4BAF-97E3-774BB3C9DED1Female1974-10-13 14:51:28.997UnknownMarriedEnglish17.720000
3269B5D2A0-12FD-46EF-A5FF-B29C4BAFBE49Male1954-05-02 16:38:15.957WhiteMarriedEnglish18.650000
338D389A8C-A6D8-4447-9DDE-1A28AB4EC667Female1964-07-10 08:50:13.707AsianMarriedEnglish4.340000
34967987B9-FFEF-4776-85CF-AE05CA81F583Male1947-02-13 10:26:55.520WhiteWidowedEnglish88.660000
359BBF3A51-443D-438B-9289-B98B8E0577C0Male1944-11-25 06:12:56.860WhiteMarriedEnglish16.760000
366D8008ED-D623-4BE4-B93B-335F9797C170Female1978-03-14 13:41:28.337WhiteMarriedIcelandic14.810000
37B3892204-880B-40EF-B3BB-B824B50E99E5Male1978-08-06 08:38:27.330WhiteSingleSpanish16.860000
3849DADA25-F2C2-42BB-8210-D78E6C7B0D48Male1962-04-08 10:18:26.263African AmericanMarriedEnglish19.500000
397548B6CF-79D9-461D-A0C5-20B861406FACMale1956-11-13 14:08:00.253WhiteMarriedIcelandic14.030000
40B5D31F01-7273-4901-B56F-8139769A11EFFemale1971-04-02 18:56:59.353AsianUnknownUnknown82.080000
4180D356B4-F974-441F-A5F2-F95986D119A2Female1938-03-06 18:24:18.297WhiteSingleEnglish18.880000
42A0A976C8-9B30-4492-B8C4-5B25095B9192Male1931-05-26 14:54:15.847AsianSingleEnglish19.520000
43016A590E-D093-4667-A5DA-D68EA6987D93Male1960-12-06 06:37:05.640WhiteUnknownEnglish15.020000
443B11D6B3-A36A-4B69-A437-C29BF425A941Female1954-05-18 10:19:19.110AsianSeparatedEnglish10.800000
45E483DE6E-D4E6-47FD-905B-22EE86EC7ACEMale1945-11-18 04:14:31.573African AmericanSingleEnglish16.880000
46B2EB15FA-5431-4804-9309-4215BDC778C0Male1951-04-13 20:14:02.953WhiteDivorcedEnglish19.410000
4753B9FFDD-F80B-43BE-93CF-C34A023EE7E9Male1921-08-22 19:17:09.227WhiteDivorcedIcelandic18.170000
48C5D09468-574F-4802-B56F-DB38F4EB1687Male1939-07-07 19:39:49.753African AmericanMarriedIcelandic10.870000
49EA7C2F0F-DA1C-4CE8-9700-4BB1FC7AF3FBMale1969-01-05 00:15:09.627UnknownMarriedEnglish3.300000
5035FE7491-1A1D-48CB-810C-8DC2599AB3DDMale1969-11-02 06:34:34.527WhiteMarriedEnglish4.510000
51868E700E-3C56-458F-A477-078D671DCB20Female1978-09-21 15:29:44.770African AmericanMarriedIcelandic5.940000
5256A35E74-90BE-44A0-B7BA-7743BB152133Female1968-11-09 15:29:19.557WhiteMarriedEnglish8.810000
53CC12B481-B516-455B-884F-4CA900B29F2EFemale1985-10-21 07:59:04.777UnknownDivorcedEnglish14.580000
54714823AF-C52C-414C-B53B-C43EACD194C3Male1952-05-08 23:51:50.127WhiteMarriedEnglish13.230000
553231F930-2978-4F50-8234-755449851E7BMale1979-05-26 04:58:10.627WhiteSingleEnglish18.360000
56C2CCB1AB-6633-4CB3-B4E8-157E6FB02376Female1964-05-07 10:20:37.740WhiteSingleSpanish15.980000
571311FEE4-2FDC-46E4-83D3-1550A3E51D2CFemale1988-03-28 03:09:22.807WhiteSingleEnglish14.990000
5821792512-2D40-4326-BEA2-A40127EB24FFMale1938-03-24 19:25:53.980WhiteSingleUnknown89.440000
59EEAFC0B3-B835-4D99-AB33-2F9428E54E5FFemale1961-01-08 15:19:15.490AsianUnknownEnglish18.950000
602EE42DEF-37CA-4694-827E-FA4EAF882BFCMale1964-04-27 00:41:40.410WhiteMarriedEnglish18.910000
6136775002-9EC3-4889-AD4F-80DC6855C8D8Female1963-07-16 22:16:48.477AsianSingleSpanish15.560000
624C201C71-CCED-40D1-9642-F9C8C485B854Male1926-09-22 09:17:14.713African AmericanMarriedEnglish10.300000
632A8772FE-61DB-483E-B6BF-6C0A74BA9C2AFemale1966-10-14 15:31:48.427AsianSingleEnglish11.150000
64C65A4ADE-112E-49E4-B72A-0DED22C242EDMale1955-04-07 18:55:17.077WhiteMarriedEnglish19.660000
6580AC01B2-BD55-4BE0-A59A-4024104CF4E9Female1965-05-14 15:25:09.740African AmericanMarriedEnglish19.740000
666A57AC0C-57F3-4C19-98A1-51135EFBC4FFFemale1958-07-07 16:00:19.383UnknownMarriedEnglish94.060000
677A025E77-7832-4F53-B9A7-09A3F98AC17EFemale1951-07-12 08:23:45.520AsianSingleEnglish16.690000
68DCE5AEB8-6DB9-4106-8AE4-02CCC5C23741Female1967-11-09 08:14:51.143WhiteSingleUnknown16.430000
69886B5885-1EE2-49F3-98D5-A2F02EB8A9D4Female1970-04-27 09:15:11.080UnknownMarriedEnglish10.350000
700E0EADE8-5592-4E0B-9F88-D7596E32EE08Female1942-07-13 21:15:37.517WhiteMarriedSpanish18.800000
710681FA35-A794-4684-97BD-00B88370DB41Female1978-10-02 21:46:05.300AsianUnknownSpanish19.160000
722E26695A-EFB0-4C7F-9318-E3030B154E39Female1963-06-28 05:37:36.843AsianMarriedEnglish14.200000
73DDC0BC57-7A4E-4E02-9282-177750B74FBCMale1921-03-26 14:38:51.803WhiteSingleEnglish18.410000
74E250799D-F6DE-4914-ADB4-B08A6E5029B9Female1945-08-04 19:03:00.757WhiteSingleUnknown12.860000
759C75DF1F-9DA6-4C98-8F5B-E10BDC805ED0Female1952-07-19 13:35:56.833WhiteDivorcedEnglish88.650000
76A19A0B00-4C9A-4206-B1FE-17E6DA3CEB0BFemale1967-07-01 01:44:24.907UnknownMarriedEnglish17.840000
77E5478913-6819-4977-BB11-4C8B61175B56Female1980-03-21 13:08:56.867WhiteSingleEnglish12.920000
7866154E24-D3EE-4311-89DB-6195278F9B3CFemale1944-08-26 13:03:24.297WhiteDivorcedEnglish19.490000
79BC44CE19-9FC5-4AC9-A296-9EBC5E3D03AEFemale1953-06-04 03:16:17.843African AmericanMarriedEnglish15.040000
80CC9CDA72-B37A-4F8F-AFE4-B08F56A183BEFemale1942-04-14 14:01:01.130AsianMarriedSpanish14.150000
81EA9B67E2-15F0-450B-A8A6-14F6E4AE3D12Female1930-04-08 20:59:31.057AsianSeparatedIcelandic98.400000
823E462A8F-7B90-43A1-A8B6-AD82CB5002C9Female1986-12-20 16:41:34.793WhiteMarriedSpanish18.330000
839E18822E-7D13-45C7-B50E-F95CFF92BC3EMale1954-10-16 06:45:56.257UnknownSingleEnglish15.240000
84A7142B71-A144-4D56-BD14-3E966B01DB37Male1960-03-28 13:40:52.270WhiteDivorcedSpanish19.520000
85FE0B9B59-1927-45B7-8556-E079DC1DE30AMale1955-08-23 01:01:22.260WhiteDivorcedEnglish6.480000
8698F593D2-8894-49BB-93B9-5A0E2CF85E2EFemale1944-07-15 19:04:11.487African AmericanMarriedEnglish9.100000
871A220558-5996-43E1-AE5D-7B96180FED35Male1937-09-07 22:23:53.143AsianMarriedEnglish15.110000
887A7332AD-88B1-4848-9356-E5260E477C59Female1944-12-01 06:30:01.543UnknownMarriedEnglish19.460000
896D5DCAC1-17FE-4D7C-923B-806EFBA3E6DFMale1961-10-22 02:47:13.170AsianMarriedEnglish13.900000
9036E2F89E-777A-4D77-9D95-0D70A8AB416FMale1980-05-30 13:23:50.703African AmericanSeparatedEnglish19.360000
91672D554B-D6D1-40B2-A6A4-21A4CB6B1AA6Male1983-06-17 04:46:13.753African AmericanSingleIcelandic11.040000
9203A481F5-B32A-4A91-BD42-43EB78FEBA77Female1968-02-07 23:02:38.017AsianSingleUnknown93.230000
932A5251B1-0945-47FA-A65C-7A6381562591Female1942-01-07 16:45:33.060UnknownMarriedSpanish18.050000
948AF47463-8534-4203-B210-C2290F6CE689Female1952-06-27 17:29:04.187WhiteDivorcedEnglish11.880000
95135C831F-7DA5-46C0-959C-EBCBD8810B43Male1971-05-13 04:40:05.623WhiteUnknownSpanish12.380000
968856096E-E59C-4156-A767-C091AF799C80Female1988-11-25 02:59:36.373WhiteDivorcedEnglish11.080000
976623F5D6-D581-4268-9F9B-21612FBBF7B5Female1943-02-17 15:36:13.787AsianSingleSpanish14.490000
9865A7FBE0-EA9F-49E9-9824-D8F3AD98DAC0Female1962-11-30 06:28:33.110WhiteSeparatedSpanish17.980000
99FB909FAE-72DD-4F6F-9828-D92183DF185FMale1940-07-15 12:18:41.080WhiteSingleSpanish14.900000
\n" ], "text/plain": [ "" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT\n", " *\n", "FROM\n", " PatientCorePopulatedTable;\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "2b1bebb4", "metadata": {}, "source": [ "(sql:primer:limit_rows)=\n", "### Limit rows\n", "You can limit the number of rows to 10 by adding `LIMIT 10` after the `FROM` clause. \n", "\n", "**Query:** Select all columns and from the `PatientCorePopulatedTable` table and limit to 10 rows. \n", "\n", "```sql\n", "SELECT\n", " *\n", "FROM\n", " PatientCorePopulatedTable\n", "LIMIT 10;\n", "```" ] }, { "cell_type": "code", "execution_count": 2, "id": "fa424e39", "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PatientIDPatientGenderPatientDateOfBirthPatientRacePatientMaritalStatusPatientLanguagePatientPopulationPercentageBelowPoverty
0FB2ABB23-C9D0-4D09-8464-49BF0B982F0FMale1947-12-28 02:45:40.547UnknownMarriedIcelandic18.080000
164182B95-EB72-4E2B-BE77-8050B71498CEMale1952-01-18 19:51:12.917African AmericanSeparatedEnglish13.030000
2DB22A4D9-7E4D-485C-916A-9CD1386507FBFemale1970-07-25 13:04:20.717AsianMarriedEnglish6.670000
36E70D84D-C75F-477C-BC37-9177C3698C66Male1979-01-04 05:45:29.580WhiteMarriedEnglish16.090000
4C8556CC0-32FC-4CA5-A8CD-9CCF38816167Female1921-04-11 11:39:49.197WhiteMarriedEnglish18.200000
57FD13988-E58A-4A5C-8680-89AC200950FAMale1965-07-12 15:41:20.523WhiteMarriedSpanish12.410000
6C60FE675-CA52-4C55-A233-F4B27E94987FMale1957-10-30 23:26:15.303AsianMarriedSpanish12.800000
7B39DC5AC-E003-4E6A-91B6-FC07625A1285Female1935-11-03 21:07:09.040WhiteMarriedEnglish15.310000
8FA157FA5-F488-4884-BF87-E144630D595CFemale1932-11-01 06:19:56.577WhiteSingleEnglish16.320000
9B7E9FC4C-5182-4A34-954E-CEF5FC07E96DFemale1985-12-11 02:48:16.907UnknownSingleEnglish11.430000
\n" ], "text/plain": [ "" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT\n", " *\n", "FROM\n", " PatientCorePopulatedTable\n", "LIMIT 10;\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "de6b13c6", "metadata": {}, "source": [ "(sql:primer:select_some_columns)=\n", "### Select some columns\n", "You can select columns from a table by specifying them after the `SELECT` clause. Multiple\n", "columns are separated by a comma (`,`). \n", "\n", "**Query**: Select the PatientID and PatientDateOfBirth columns and limit to 10 rows.\n", "\n", "```sql\n", "SELECT\n", " PatientID,\n", " PatientDateOfBirth\n", "FROM\n", " PatientCorePopulatedTable\n", "LIMIT 10;\n", "```" ] }, { "cell_type": "code", "execution_count": 3, "id": "14643eef", "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PatientIDPatientDateOfBirth
0FB2ABB23-C9D0-4D09-8464-49BF0B982F0F1947-12-28 02:45:40.547
164182B95-EB72-4E2B-BE77-8050B71498CE1952-01-18 19:51:12.917
2DB22A4D9-7E4D-485C-916A-9CD1386507FB1970-07-25 13:04:20.717
36E70D84D-C75F-477C-BC37-9177C3698C661979-01-04 05:45:29.580
4C8556CC0-32FC-4CA5-A8CD-9CCF388161671921-04-11 11:39:49.197
57FD13988-E58A-4A5C-8680-89AC200950FA1965-07-12 15:41:20.523
6C60FE675-CA52-4C55-A233-F4B27E94987F1957-10-30 23:26:15.303
7B39DC5AC-E003-4E6A-91B6-FC07625A12851935-11-03 21:07:09.040
8FA157FA5-F488-4884-BF87-E144630D595C1932-11-01 06:19:56.577
9B7E9FC4C-5182-4A34-954E-CEF5FC07E96D1985-12-11 02:48:16.907
\n" ], "text/plain": [ "" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT\n", " PatientID,\n", " PatientDateOfBirth\n", "FROM\n", " PatientCorePopulatedTable\n", "LIMIT 10;\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "aaa3f5a5", "metadata": {}, "source": [ "(sql:primer:using_column_alias)=\n", "### Using column alias\n", "Notice that all the column names are prefixed by `Patient`. You can give columns a different name, meaning an alias. \n", "There are two ways to do alias. You can put the alias name right after the actual column name, e.g., `PatientID PTID`\n", "or you can use the `AS` keyword to indicate explicitly that you are aliasing a column name, e.g., `PatientID AS PTID`.\n", "Note that if the alias has a space, then it should be in quotes. \n", "\n", "**Query**: Select the PatientID and PatientDateOfBirth columns, but alias PatientID to PTID and PatientDateOfBirth \n", "to \"Date of Birth\" and limit to 10 rows.\n", "\n", "```sql\n", "SELECT\n", " PatientID PTID, \n", " PatientDateOfBirth AS \"Date of Birth\"\n", "FROM\n", " PatientCorePopulatedTable\n", "LIMIT 10;\n", "```" ] }, { "cell_type": "code", "execution_count": 4, "id": "fa393df5", "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PTIDDate of Birth
0FB2ABB23-C9D0-4D09-8464-49BF0B982F0F1947-12-28 02:45:40.547
164182B95-EB72-4E2B-BE77-8050B71498CE1952-01-18 19:51:12.917
2DB22A4D9-7E4D-485C-916A-9CD1386507FB1970-07-25 13:04:20.717
36E70D84D-C75F-477C-BC37-9177C3698C661979-01-04 05:45:29.580
4C8556CC0-32FC-4CA5-A8CD-9CCF388161671921-04-11 11:39:49.197
57FD13988-E58A-4A5C-8680-89AC200950FA1965-07-12 15:41:20.523
6C60FE675-CA52-4C55-A233-F4B27E94987F1957-10-30 23:26:15.303
7B39DC5AC-E003-4E6A-91B6-FC07625A12851935-11-03 21:07:09.040
8FA157FA5-F488-4884-BF87-E144630D595C1932-11-01 06:19:56.577
9B7E9FC4C-5182-4A34-954E-CEF5FC07E96D1985-12-11 02:48:16.907
\n" ], "text/plain": [ "" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT\n", " PatientID PTID, \n", " PatientDateOfBirth AS \"Date of Birth\"\n", "FROM\n", " PatientCorePopulatedTable\n", "LIMIT 10;\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "47f021de", "metadata": {}, "source": [ "(sql:primer:adding_column)=\n", "### Adding columns not from the table\n", "Besides selecting columns in the table, you can also add the following columns to your query:\n", "1. literals such as numbers or strings\n", "1. Math expressions such as `PatientPopulationPercentageBelowPoverty + 1`, or `PatientPopulationPercentageBelowPoverty * 100`\n", "\n", "**Query**: Select the PatientID, Hospital, and PatientPopulationPercentageBelowPoverty columns, alias PatientID to PTID, \n", "make the `Hospital` column 'Buffalo General', multiply `PatientPopulationPercentageBelowPoverty` by 10 and alias it to\n", "`Poverty Level`, and limit to 10 rows. Note that if the alias name has a space, then the name needs to inside of quotes. \n", "\n", "```sql\n", "SELECT\n", " PatientID PTID, \n", " 'Buffalo General' Hospital,\n", " PatientPopulationPercentageBelowPoverty * 10 \"Poverty Level\"\n", "FROM\n", " PatientCorePopulatedTable\n", "LIMIT 10;\n", "```" ] }, { "cell_type": "code", "execution_count": 5, "id": "81435424", "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PTIDHospitalPoverty Level
0FB2ABB23-C9D0-4D09-8464-49BF0B982F0FBuffalo General180.800000
164182B95-EB72-4E2B-BE77-8050B71498CEBuffalo General130.300000
2DB22A4D9-7E4D-485C-916A-9CD1386507FBBuffalo General66.700000
36E70D84D-C75F-477C-BC37-9177C3698C66Buffalo General160.900000
4C8556CC0-32FC-4CA5-A8CD-9CCF38816167Buffalo General182.000000
57FD13988-E58A-4A5C-8680-89AC200950FABuffalo General124.100000
6C60FE675-CA52-4C55-A233-F4B27E94987FBuffalo General128.000000
7B39DC5AC-E003-4E6A-91B6-FC07625A1285Buffalo General153.100000
8FA157FA5-F488-4884-BF87-E144630D595CBuffalo General163.200000
9B7E9FC4C-5182-4A34-954E-CEF5FC07E96DBuffalo General114.300000
\n" ], "text/plain": [ "" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT\n", " PatientID PTID, \n", " 'Buffalo General' Hospital,\n", " PatientPopulationPercentageBelowPoverty * 10 \"Poverty Level\"\n", "FROM\n", " PatientCorePopulatedTable\n", "LIMIT 10;\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "b4fd0727", "metadata": {}, "source": [ "(sql:primer:removing_duplicates)=\n", "### Removing duplicates\n", "In some cases you might get duplicate rows. You remove these duplicate rows by putting the `DISTINCT` keyword\n", "after the `SELECT` keyword. One use of this is to get distinct values of a given column. \n", "\n", "Query: Select only the `PatientMaritalStatus` column from the `PatientCorePopulatedTable` table.\n", "\n", "```sql\n", "SELECT\n", " PatientMaritalStatus\n", "FROM\n", " PatientCorePopulatedTable;\n", "```" ] }, { "cell_type": "code", "execution_count": 6, "id": "a98b20a4", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PatientMaritalStatus
0Married
1Separated
2Married
3Married
4Married
5Married
6Married
7Married
8Single
9Single
10Married
11Divorced
12Single
13Married
14Single
15Married
16Unknown
17Single
18Married
19Single
20Single
21Married
22Single
23Married
24Married
25Single
26Married
27Single
28Single
29Single
30Divorced
31Married
32Married
33Married
34Widowed
35Married
36Married
37Single
38Married
39Married
40Unknown
41Single
42Single
43Unknown
44Separated
45Single
46Divorced
47Divorced
48Married
49Married
50Married
51Married
52Married
53Divorced
54Married
55Single
56Single
57Single
58Single
59Unknown
60Married
61Single
62Married
63Single
64Married
65Married
66Married
67Single
68Single
69Married
70Married
71Unknown
72Married
73Single
74Single
75Divorced
76Married
77Single
78Divorced
79Married
80Married
81Separated
82Married
83Single
84Divorced
85Divorced
86Married
87Married
88Married
89Married
90Separated
91Single
92Single
93Married
94Divorced
95Unknown
96Divorced
97Single
98Separated
99Single
\n" ], "text/plain": [ "" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT\n", " PatientMaritalStatus\n", "FROM\n", " PatientCorePopulatedTable;\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "a57ee63c", "metadata": {}, "source": [ "**Query:** Select the `PatientMaritalStatus` column from the `PatientCorePopulatedTable` table but\n", "only select distinct values. \n", "\n", "```sql\n", "SELECT DISTINCT\n", " PatientMaritalStatus\n", "FROM\n", " PatientCorePopulatedTable;\n", "```" ] }, { "cell_type": "code", "execution_count": 7, "id": "0988662e", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PatientMaritalStatus
0Married
1Separated
2Single
3Divorced
4Unknown
5Widowed
\n" ], "text/plain": [ "" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT DISTINCT\n", " PatientMaritalStatus\n", "FROM\n", " PatientCorePopulatedTable;\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "95d5480c", "metadata": {}, "source": [ "(sql:primer:removing_duplicates_2)=\n", "### Removing duplicates with multiple columns\n", "The `DISTINCT` keyword can also be used to find distinct combination of columns. **It is also used sometimes with joins\n", "to remove duplicate rows.**\n", "\n", "**Query:** Select `PatientRace` and `PatientMaritalStatus` columns from the `PatientCorePopulatedTable` table but\n", "only select distinct values. This query finds the distinct combinations of race and martial status. \n", "\n", "```sql\n", "SELECT DISTINCT\n", " PatientRace,\n", " PatientMaritalStatus\n", "FROM\n", " PatientCorePopulatedTable\n", "ORDER BY PatientRace, PatientMaritalStatus\n", "```" ] }, { "cell_type": "code", "execution_count": 8, "id": "573a5b64", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PatientRacePatientMaritalStatus
0African AmericanMarried
1African AmericanSeparated
2African AmericanSingle
3African AmericanUnknown
4AsianDivorced
5AsianMarried
6AsianSeparated
7AsianSingle
8AsianUnknown
9UnknownDivorced
10UnknownMarried
11UnknownSingle
12WhiteDivorced
13WhiteMarried
14WhiteSeparated
15WhiteSingle
16WhiteUnknown
17WhiteWidowed
\n" ], "text/plain": [ "" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT DISTINCT\n", " PatientRace,\n", " PatientMaritalStatus\n", "FROM\n", " PatientCorePopulatedTable\n", "ORDER BY PatientRace, PatientMaritalStatus\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "b7ffcef2", "metadata": {}, "source": [ "### `FROM` multiple tables\n", "The power of SQL lies in the fact that you can combine tables together based on some shared column between\n", "tables. The `FROM` clause allows you to select from multiple tables. \n", "\n", "You should now that there are four types of tables in SQL:\n", "1. Permanent tables (i.e., created using the `CREATE TABLE` statement)\n", "2. Derived tables (i.e., rows returned by a subquery and held in memory)\n", "3. Temporary tables (i.e., volatile data held in memory)\n", "4. Virtual tables (i.e, created using the `CREATE VIEW` statement)\n", "\n", "We have been using permanent tables so far. Temporary and virtual tables will be covered later. The following\n", "is an example a derived table. \n", "\n", "(sql:primer:derived_table)=\n", "#### Derived table\n", "A derived query is a query held in memory. You surround it a pair of parenthesis and give it a name. \n", "\n", "**Query**: Create a subquery called `dx_codes` which selects the `PrimaryDiagnosisCode` and `PrimaryDiagnosisDescription`\n", "columns from the `AdmissionsDiagnosesCorePopulatedTable` where the `AdmissionID` is equal to `1`. Then use this derived query in another query that concatenates the diagnosis code and diagnosis description, e.g., \"(M01.X) Direct infection of joint in infectious and parasitic diseases classified elsewhere\". Fields and string literals can be concatenated in SQLite using `||`. \n", "\n", "```sql\n", "SELECT '(' || dx_codes.code || ') ' || dx_codes.description AS CodeWDescription\n", "FROM\n", " (\n", " SELECT\n", " PrimaryDiagnosisCode code,\n", " PrimaryDiagnosisDescription description \n", " FROM\n", " AdmissionsDiagnosesCorePopulatedTable\n", " WHERE AdmissionID = 1\n", " ) dx_codes\n", "```" ] }, { "cell_type": "code", "execution_count": 9, "id": "2e31b0c6", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 CodeWDescription
0(C91.00) Acute lymphoblastic leukemia not having achieved remission
1(M05.442) Rheumatoid myopathy with rheumatoid arthritis of left hand
2(K91) Intraoperative and postprocedural complications and disorders of digestive system, not elsewhere classified
3(M05.752) Rheumatoid arthritis with rheumatoid factor of left hip without organ or systems involvement
4(C94.6) Myelodysplastic disease, not classified
5(C92.51) Acute myelomonocytic leukemia, in remission
6(D12.7) Benign neoplasm of rectosigmoid junction
7(C79.72) Secondary malignant neoplasm of left adrenal gland
8(M06.31) Rheumatoid nodule, shoulder
9(Z13.810) Encounter for screening for upper gastrointestinal disorder
10(Z13.83) Encounter for screening for respiratory disorder NEC
11(D39.12) Neoplasm of uncertain behavior of left ovary
12(O99.41) Diseases of the circulatory system complicating pregnancy
13(O07.32) Renal failure following failed attempted termination of pregnancy
14(E10.630) Type 1 diabetes mellitus with periodontal disease
15(F11) Opioid related disorders
16(D13.1) Benign neoplasm of stomach
17(F31.4) Bipolar disorder, current episode depressed, severe, without psychotic features
18(O10.113) Pre-existing hypertensive heart disease complicating pregnancy, third trimester
19(N49.2) Inflammatory disorders of scrotum
20(I25.82) Chronic total occlusion of coronary artery
21(C51.2) Malignant neoplasm of clitoris
22(H75.01) Mastoiditis in infectious and parasitic diseases classified elsewhere, right ear
23(M01.X5) Direct infection of hip in infectious and parasitic diseases classified elsewhere
24(E10.339) Type 1 diabetes mellitus with moderate nonproliferative diabetic retinopathy without macular edema
25(C19) Malignant neoplasm of rectosigmoid junction
26(I67.81) Acute cerebrovascular insufficiency
27(C92.50) Acute myelomonocytic leukemia, not having achieved remission
28(I25.84) Coronary atherosclerosis due to calcified coronary lesion
29(M63) Disorders of muscle in diseases classified elsewhere
30(O9A.1) Malignant neoplasm complicating pregnancy, childbirth and the puerperium
31(C75.1) Malignant neoplasm of pituitary gland
32(M11.071) Hydroxyapatite deposition disease, right ankle and foot
33(F01.5) Vascular dementia
34(G47) Sleep disorders
35(G96.1) Disorders of meninges, not elsewhere classified
36(C91.5) Adult T-cell lymphoma/leukemia (HTLV-1-associated)
37(Z91.15) Patient's noncompliance with renal dialysis
38(C92.12) Chronic myeloid leukemia, BCR/ABL-positive, in relapse
39(M05.742) Rheumatoid arthritis with rheumatoid factor of left hand without organ or systems involvement
40(J66.1) Flax-dressers' disease
41(N18.1) Chronic kidney disease, stage 1
42(C88.3) Immunoproliferative small intestinal disease
43(Z12) Encounter for screening for malignant neoplasms
44(M05.261) Rheumatoid vasculitis with rheumatoid arthritis of right knee
45(B40.1) Chronic pulmonary blastomycosis
46(H34) Retinal vascular occlusions
47(M24.251) Disorder of ligament, right hip
48(O99.712) Diseases of the skin and subcutaneous tissue complicating pregnancy, second trimester
49(I79.0) Aneurysm of aorta in diseases classified elsewhere
50(A98.3) Marburg virus disease
51(M05.722) Rheumatoid arthritis with rheumatoid factor of left elbow without organ or systems involvement
52(H16.433) Localized vascularization of cornea, bilateral
53(N25.1) Nephrogenic diabetes insipidus
54(M02.37) Reiter's disease, ankle and foot
55(M10.31) Gout due to renal impairment, shoulder
56(I25.110) Atherosclerotic heart disease of native coronary artery with unstable angina pectoris
57(E72.4) Disorders of ornithine metabolism
58(O24.111) Pre-existing diabetes mellitus, type 2, in pregnancy, first trimester
59(Z13.0) Encounter for screening for diseases of the blood and blood-forming organs and certain disorders involving the immune mechanism
60(M24.22) Disorder of ligament, elbow
61(C03.0) Malignant neoplasm of upper gum
62(Z12.2) Encounter for screening for malignant neoplasm of respiratory organs
63(C69.61) Malignant neoplasm of right orbit
64(O10.213) Pre-existing hypertensive chronic kidney disease complicating pregnancy, third trimester
65(M90.51) Osteonecrosis in diseases classified elsewhere, shoulder
66(F63.81) Intermittent explosive disorder
67(O10.13) Pre-existing hypertensive heart disease complicating the puerperium
68(C94.22) Acute megakaryoblastic leukemia, in relapse
69(C11) Malignant neoplasm of nasopharynx
70(I43) Cardiomyopathy in diseases classified elsewhere
71(E09.359) Drug or chemical induced diabetes mellitus with proliferative diabetic retinopathy without macular edema
72(M90.6) Osteitis deformans in neoplastic diseases
73(C72.1) Malignant neoplasm of cauda equina
74(F12.180) Cannabis abuse with cannabis-induced anxiety disorder
75(C18.3) Malignant neoplasm of hepatic flexure
76(M05.272) Rheumatoid vasculitis with rheumatoid arthritis of left ankle and foot
77(E10.32) Type 1 diabetes mellitus with mild nonproliferative diabetic retinopathy
78(M06.361) Rheumatoid nodule, right knee
79(H26.21) Cataract with neovascularization
80(Z12.13) Encounter for screening for malignant neoplasm of small intestine
81(D35.5) Benign neoplasm of carotid body
82(D16.2) Benign neoplasm of long bones of lower limb
83(M06.041) Rheumatoid arthritis without rheumatoid factor, right hand
84(M05.732) Rheumatoid arthritis with rheumatoid factor of left wrist without organ or systems involvement
85(M10.37) Gout due to renal impairment, ankle and foot
86(C67.5) Malignant neoplasm of bladder neck
87(M10.361) Gout due to renal impairment, right knee
88(K08.121) Complete loss of teeth due to periodontal diseases, class I
89(E30) Disorders of puberty, not elsewhere classified
90(G71) Primary disorders of muscles
91(C18.3) Malignant neoplasm of hepatic flexure
92(O99.351) Diseases of the nervous system complicating pregnancy, first trimester
93(M05.25) Rheumatoid vasculitis with rheumatoid arthritis of hip
94(C57.21) Malignant neoplasm of right round ligament
95(K00) Disorders of tooth development and eruption
96(M63.832) Disorders of muscle in diseases classified elsewhere, left forearm
97(C72.3) Malignant neoplasm of optic nerve
98(M05.27) Rheumatoid vasculitis with rheumatoid arthritis of ankle and foot
99(F06.1) Catatonic disorder due to known physiological condition
\n" ], "text/plain": [ "" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT '(' || dx_codes.code || ') ' || dx_codes.description AS CodeWDescription\n", "FROM\n", " (\n", " SELECT\n", " PrimaryDiagnosisCode code,\n", " PrimaryDiagnosisDescription description \n", " FROM\n", " AdmissionsDiagnosesCorePopulatedTable\n", " WHERE AdmissionID = 1\n", " ) dx_codes\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "3893e57a", "metadata": {}, "source": [ "(sql:primer:where_clause)=\n", "### The `WHERE` Clause\n", "The `WHERE` clause allows you to filter out unwanted rows. For string fields, you can use the equality operator (`=`) or \n", "the `LIKE` operator. For numerical and date fields, you can use all the usually operators such as greater than, less than, etc. \n", "`WHERE` clauses can be combined using `AND` and `OR`. Parenthesis can be used to clarify grouping of the clauses. The\n", "`WHERE` clauses are put after the `FROM` clause. \n", "\n", "**Query:** Select all patients from `PatientCorePopulatedTable` table that are either married and african american or married and white and \n", "the `PatientPopulationPercentageBelowPoverty` is above 15. Select the following columns: PatientID, PatientRace, PatientMaritalStatus and\n", "PatientPopulationPercentageBelowPoverty. \n", "\n", "```sql\n", "SELECT \n", " PatientID, \n", " PatientRace, \n", " PatientMaritalStatus,\n", " PatientPopulationPercentageBelowPoverty\n", "FROM\n", " PatientCorePopulatedTable\n", "WHERE ((PatientRace = 'White' AND PatientMaritalStatus\t= 'Married') OR (PatientRace = 'African American' AND PatientMaritalStatus = 'Married'))\n", " AND PatientPopulationPercentageBelowPoverty > 15\n", "```" ] }, { "cell_type": "code", "execution_count": 10, "id": "e9a87fa1", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PatientIDPatientRacePatientMaritalStatusPatientPopulationPercentageBelowPoverty
06E70D84D-C75F-477C-BC37-9177C3698C66WhiteMarried16.090000
1C8556CC0-32FC-4CA5-A8CD-9CCF38816167WhiteMarried18.200000
2B39DC5AC-E003-4E6A-91B6-FC07625A1285WhiteMarried15.310000
325B786AF-0F99-478C-9CFA-0EA607E45834WhiteMarried93.600000
481C5B13B-F6B2-4E57-9593-6E7E4C13B2CEWhiteMarried19.220000
569B5D2A0-12FD-46EF-A5FF-B29C4BAFBE49WhiteMarried18.650000
69BBF3A51-443D-438B-9289-B98B8E0577C0WhiteMarried16.760000
749DADA25-F2C2-42BB-8210-D78E6C7B0D48African AmericanMarried19.500000
82EE42DEF-37CA-4694-827E-FA4EAF882BFCWhiteMarried18.910000
9C65A4ADE-112E-49E4-B72A-0DED22C242EDWhiteMarried19.660000
1080AC01B2-BD55-4BE0-A59A-4024104CF4E9African AmericanMarried19.740000
110E0EADE8-5592-4E0B-9F88-D7596E32EE08WhiteMarried18.800000
12BC44CE19-9FC5-4AC9-A296-9EBC5E3D03AEAfrican AmericanMarried15.040000
133E462A8F-7B90-43A1-A8B6-AD82CB5002C9WhiteMarried18.330000
\n" ], "text/plain": [ "" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT \n", " PatientID, \n", " PatientRace, \n", " PatientMaritalStatus,\n", " PatientPopulationPercentageBelowPoverty\n", "FROM\n", " PatientCorePopulatedTable\n", "WHERE (\n", "\t(\n", "\t\tPatientRace = 'White' AND PatientMaritalStatus\t= 'Married') \n", "\t\tOR (PatientRace = 'African American' AND PatientMaritalStatus\t= 'Married')\n", "\t)\n", " AND PatientPopulationPercentageBelowPoverty > 15\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "7b8d0d4b", "metadata": {}, "source": [ "(sql:primer:order_by_clause)=\n", "### The `ORDER BY` Clauses \n", "You can order the the rows by column(s) using the `ORDER BY` clause. This clause is put after the `WHERE` clause. You can specify\n", "multiple columns separated by comma. You can also specify ascending order using the `ASC` keyword after the column name \n", "and descending order by using the `DESC` keyword. The default sorting order is ascending. A shortcut for descending is putting `-` before the column name. Finally\n", "you can sort the columns by its numerical position. \n", "\n", "#### Sort by columns\n", "\n", "**Query:** Select the `PatientMaritalStatus` and `PatientPopulationPercentageBelowPoverty` columns from \n", "the `PatientCorePopulatedTable` table and sort by `PatientPopulationPercentageBelowPoverty`.\n", "\n", "```sql\n", "SELECT \n", " PatientMaritalStatus, \n", " PatientPopulationPercentageBelowPoverty\n", "FROM\n", " PatientCorePopulatedTable\n", "ORDER BY PatientPopulationPercentageBelowPoverty;\n", "```" ] }, { "cell_type": "code", "execution_count": 11, "id": "bc7ca552", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PatientMaritalStatusPatientPopulationPercentageBelowPoverty
0Single1.700000
1Married3.300000
2Married4.340000
3Married4.510000
4Married5.940000
5Divorced6.480000
6Married6.670000
7Single7.590000
8Married8.810000
9Married9.100000
10Married10.300000
11Married10.350000
12Separated10.800000
13Married10.870000
14Single11.040000
15Divorced11.080000
16Single11.150000
17Single11.160000
18Married11.250000
19Single11.430000
20Divorced11.880000
21Married11.890000
22Married12.010000
23Unknown12.380000
24Married12.410000
25Married12.800000
26Single12.860000
27Single12.920000
28Separated13.030000
29Single13.100000
30Married13.230000
31Married13.900000
32Single13.970000
33Married14.030000
34Divorced14.070000
35Married14.150000
36Married14.200000
37Single14.280000
38Single14.490000
39Single14.490000
40Divorced14.580000
41Married14.810000
42Married14.870000
43Single14.900000
44Single14.990000
45Unknown15.020000
46Married15.040000
47Married15.110000
48Single15.240000
49Married15.310000
50Single15.560000
51Single15.720000
52Single15.980000
53Married16.090000
54Single16.240000
55Single16.320000
56Single16.430000
57Single16.690000
58Married16.710000
59Married16.760000
60Single16.860000
61Single16.880000
62Unknown16.890000
63Married17.720000
64Married17.840000
65Separated17.980000
66Married18.050000
67Married18.050000
68Married18.080000
69Divorced18.170000
70Married18.200000
71Married18.330000
72Single18.360000
73Single18.410000
74Married18.650000
75Married18.800000
76Single18.880000
77Married18.910000
78Unknown18.950000
79Unknown19.160000
80Married19.220000
81Separated19.360000
82Divorced19.410000
83Married19.460000
84Divorced19.490000
85Married19.500000
86Single19.520000
87Divorced19.520000
88Married19.660000
89Married19.740000
90Unknown82.080000
91Divorced83.750000
92Divorced88.650000
93Widowed88.660000
94Single89.440000
95Single93.230000
96Married93.600000
97Single94.000000
98Married94.060000
99Separated98.400000
\n" ], "text/plain": [ "" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT \n", " PatientMaritalStatus, \n", " PatientPopulationPercentageBelowPoverty\n", "FROM\n", " PatientCorePopulatedTable\n", "ORDER BY PatientPopulationPercentageBelowPoverty;\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "ab067db5", "metadata": {}, "source": [ "**Query:** Select the `PatientMaritalStatus` and `PatientPopulationPercentageBelowPoverty` columns from \n", "the `PatientCorePopulatedTable` table and sort by `PatientMaritalStatus` and `PatientPopulationPercentageBelowPoverty`.\n", "\n", "```sql\n", "SELECT \n", " PatientMaritalStatus, \n", " PatientPopulationPercentageBelowPoverty\n", "FROM\n", " PatientCorePopulatedTable\n", "ORDER BY PatientMaritalStatus, PatientPopulationPercentageBelowPoverty;\n", "```" ] }, { "cell_type": "code", "execution_count": 12, "id": "926c6988", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PatientMaritalStatusPatientPopulationPercentageBelowPoverty
0Divorced6.480000
1Divorced11.080000
2Divorced11.880000
3Divorced14.070000
4Divorced14.580000
5Divorced18.170000
6Divorced19.410000
7Divorced19.490000
8Divorced19.520000
9Divorced83.750000
10Divorced88.650000
11Married3.300000
12Married4.340000
13Married4.510000
14Married5.940000
15Married6.670000
16Married8.810000
17Married9.100000
18Married10.300000
19Married10.350000
20Married10.870000
21Married11.250000
22Married11.890000
23Married12.010000
24Married12.410000
25Married12.800000
26Married13.230000
27Married13.900000
28Married14.030000
29Married14.150000
30Married14.200000
31Married14.810000
32Married14.870000
33Married15.040000
34Married15.110000
35Married15.310000
36Married16.090000
37Married16.710000
38Married16.760000
39Married17.720000
40Married17.840000
41Married18.050000
42Married18.050000
43Married18.080000
44Married18.200000
45Married18.330000
46Married18.650000
47Married18.800000
48Married18.910000
49Married19.220000
50Married19.460000
51Married19.500000
52Married19.660000
53Married19.740000
54Married93.600000
55Married94.060000
56Separated10.800000
57Separated13.030000
58Separated17.980000
59Separated19.360000
60Separated98.400000
61Single1.700000
62Single7.590000
63Single11.040000
64Single11.150000
65Single11.160000
66Single11.430000
67Single12.860000
68Single12.920000
69Single13.100000
70Single13.970000
71Single14.280000
72Single14.490000
73Single14.490000
74Single14.900000
75Single14.990000
76Single15.240000
77Single15.560000
78Single15.720000
79Single15.980000
80Single16.240000
81Single16.320000
82Single16.430000
83Single16.690000
84Single16.860000
85Single16.880000
86Single18.360000
87Single18.410000
88Single18.880000
89Single19.520000
90Single89.440000
91Single93.230000
92Single94.000000
93Unknown12.380000
94Unknown15.020000
95Unknown16.890000
96Unknown18.950000
97Unknown19.160000
98Unknown82.080000
99Widowed88.660000
\n" ], "text/plain": [ "" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT \n", " PatientMaritalStatus, \n", " PatientPopulationPercentageBelowPoverty\n", "FROM\n", " PatientCorePopulatedTable\n", "ORDER BY PatientMaritalStatus, PatientPopulationPercentageBelowPoverty;\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "7cf0fe1e", "metadata": {}, "source": [ "#### Ascending versus Descending Sort Order\n", "\n", "**Query:** Select the `PatientMaritalStatus` and `PatientPopulationPercentageBelowPoverty` columns from \n", "the `PatientCorePopulatedTable` table and sort by `PatientMaritalStatus` and `PatientPopulationPercentageBelowPoverty` by descending order\n", "using the keyword `DESC`.\n", "\n", "```sql\n", "SELECT \n", " PatientMaritalStatus, \n", " PatientPopulationPercentageBelowPoverty\n", "FROM\n", " PatientCorePopulatedTable\n", "ORDER BY PatientMaritalStatus, PatientPopulationPercentageBelowPoverty DESC;\n", "```" ] }, { "cell_type": "code", "execution_count": 13, "id": "555a7015", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PatientMaritalStatusPatientPopulationPercentageBelowPoverty
0Divorced88.650000
1Divorced83.750000
2Divorced19.520000
3Divorced19.490000
4Divorced19.410000
5Divorced18.170000
6Divorced14.580000
7Divorced14.070000
8Divorced11.880000
9Divorced11.080000
10Divorced6.480000
11Married94.060000
12Married93.600000
13Married19.740000
14Married19.660000
15Married19.500000
16Married19.460000
17Married19.220000
18Married18.910000
19Married18.800000
20Married18.650000
21Married18.330000
22Married18.200000
23Married18.080000
24Married18.050000
25Married18.050000
26Married17.840000
27Married17.720000
28Married16.760000
29Married16.710000
30Married16.090000
31Married15.310000
32Married15.110000
33Married15.040000
34Married14.870000
35Married14.810000
36Married14.200000
37Married14.150000
38Married14.030000
39Married13.900000
40Married13.230000
41Married12.800000
42Married12.410000
43Married12.010000
44Married11.890000
45Married11.250000
46Married10.870000
47Married10.350000
48Married10.300000
49Married9.100000
50Married8.810000
51Married6.670000
52Married5.940000
53Married4.510000
54Married4.340000
55Married3.300000
56Separated98.400000
57Separated19.360000
58Separated17.980000
59Separated13.030000
60Separated10.800000
61Single94.000000
62Single93.230000
63Single89.440000
64Single19.520000
65Single18.880000
66Single18.410000
67Single18.360000
68Single16.880000
69Single16.860000
70Single16.690000
71Single16.430000
72Single16.320000
73Single16.240000
74Single15.980000
75Single15.720000
76Single15.560000
77Single15.240000
78Single14.990000
79Single14.900000
80Single14.490000
81Single14.490000
82Single14.280000
83Single13.970000
84Single13.100000
85Single12.920000
86Single12.860000
87Single11.430000
88Single11.160000
89Single11.150000
90Single11.040000
91Single7.590000
92Single1.700000
93Unknown82.080000
94Unknown19.160000
95Unknown18.950000
96Unknown16.890000
97Unknown15.020000
98Unknown12.380000
99Widowed88.660000
\n" ], "text/plain": [ "" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT \n", " PatientMaritalStatus, \n", " PatientPopulationPercentageBelowPoverty\n", "FROM\n", " PatientCorePopulatedTable\n", "ORDER BY PatientMaritalStatus, PatientPopulationPercentageBelowPoverty DESC;\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "060b2a89", "metadata": {}, "source": [ "**Query:** Select the `PatientMaritalStatus` and `PatientPopulationPercentageBelowPoverty` columns from \n", "the `PatientCorePopulatedTable` table and sort by `PatientMaritalStatus` and `PatientPopulationPercentageBelowPoverty` by descending order\n", "using the `-`.\n", "\n", "```sql\n", "SELECT \n", " PatientMaritalStatus, \n", " PatientPopulationPercentageBelowPoverty\n", "FROM\n", " PatientCorePopulatedTable\n", "ORDER BY PatientMaritalStatus, -PatientPopulationPercentageBelowPoverty;\n", "```" ] }, { "cell_type": "code", "execution_count": 14, "id": "27c74ea4", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PatientMaritalStatusPatientPopulationPercentageBelowPoverty
0Divorced88.650000
1Divorced83.750000
2Divorced19.520000
3Divorced19.490000
4Divorced19.410000
5Divorced18.170000
6Divorced14.580000
7Divorced14.070000
8Divorced11.880000
9Divorced11.080000
10Divorced6.480000
11Married94.060000
12Married93.600000
13Married19.740000
14Married19.660000
15Married19.500000
16Married19.460000
17Married19.220000
18Married18.910000
19Married18.800000
20Married18.650000
21Married18.330000
22Married18.200000
23Married18.080000
24Married18.050000
25Married18.050000
26Married17.840000
27Married17.720000
28Married16.760000
29Married16.710000
30Married16.090000
31Married15.310000
32Married15.110000
33Married15.040000
34Married14.870000
35Married14.810000
36Married14.200000
37Married14.150000
38Married14.030000
39Married13.900000
40Married13.230000
41Married12.800000
42Married12.410000
43Married12.010000
44Married11.890000
45Married11.250000
46Married10.870000
47Married10.350000
48Married10.300000
49Married9.100000
50Married8.810000
51Married6.670000
52Married5.940000
53Married4.510000
54Married4.340000
55Married3.300000
56Separated98.400000
57Separated19.360000
58Separated17.980000
59Separated13.030000
60Separated10.800000
61Single94.000000
62Single93.230000
63Single89.440000
64Single19.520000
65Single18.880000
66Single18.410000
67Single18.360000
68Single16.880000
69Single16.860000
70Single16.690000
71Single16.430000
72Single16.320000
73Single16.240000
74Single15.980000
75Single15.720000
76Single15.560000
77Single15.240000
78Single14.990000
79Single14.900000
80Single14.490000
81Single14.490000
82Single14.280000
83Single13.970000
84Single13.100000
85Single12.920000
86Single12.860000
87Single11.430000
88Single11.160000
89Single11.150000
90Single11.040000
91Single7.590000
92Single1.700000
93Unknown82.080000
94Unknown19.160000
95Unknown18.950000
96Unknown16.890000
97Unknown15.020000
98Unknown12.380000
99Widowed88.660000
\n" ], "text/plain": [ "" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT \n", " PatientMaritalStatus, \n", " PatientPopulationPercentageBelowPoverty\n", "FROM\n", " PatientCorePopulatedTable\n", "ORDER BY PatientMaritalStatus, -PatientPopulationPercentageBelowPoverty;\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')" ] }, { "cell_type": "markdown", "id": "4aeb34f4", "metadata": {}, "source": [ "#### Sorting using numerical position\n", "When sorting by numerical position, you cannot use `-`. You must use the keyword `DESC`. Column names make your code more explicit, which you should prefer. \n", "\n", "**Query:** Select the `PatientMaritalStatus` and `PatientPopulationPercentageBelowPoverty` columns from \n", "the `PatientCorePopulatedTable` table and sort by `PatientMaritalStatus` and `PatientPopulationPercentageBelowPoverty` by descending order\n", "using the keyword `DESC`, but using the numerical position of `PatientPopulationPercentageBelowPoverty`. \n", "\n", "```sql\n", "SELECT \n", " PatientMaritalStatus, \n", " PatientPopulationPercentageBelowPoverty\n", "FROM\n", " PatientCorePopulatedTable\n", "ORDER BY PatientMaritalStatus, 2 DESC;\n", "```" ] }, { "cell_type": "code", "execution_count": 15, "id": "ef97c9ca", "metadata": { "tags": [ "hide-input", "output_scroll" ] }, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 PatientMaritalStatusPatientPopulationPercentageBelowPoverty
0Divorced88.650000
1Divorced83.750000
2Divorced19.520000
3Divorced19.490000
4Divorced19.410000
5Divorced18.170000
6Divorced14.580000
7Divorced14.070000
8Divorced11.880000
9Divorced11.080000
10Divorced6.480000
11Married94.060000
12Married93.600000
13Married19.740000
14Married19.660000
15Married19.500000
16Married19.460000
17Married19.220000
18Married18.910000
19Married18.800000
20Married18.650000
21Married18.330000
22Married18.200000
23Married18.080000
24Married18.050000
25Married18.050000
26Married17.840000
27Married17.720000
28Married16.760000
29Married16.710000
30Married16.090000
31Married15.310000
32Married15.110000
33Married15.040000
34Married14.870000
35Married14.810000
36Married14.200000
37Married14.150000
38Married14.030000
39Married13.900000
40Married13.230000
41Married12.800000
42Married12.410000
43Married12.010000
44Married11.890000
45Married11.250000
46Married10.870000
47Married10.350000
48Married10.300000
49Married9.100000
50Married8.810000
51Married6.670000
52Married5.940000
53Married4.510000
54Married4.340000
55Married3.300000
56Separated98.400000
57Separated19.360000
58Separated17.980000
59Separated13.030000
60Separated10.800000
61Single94.000000
62Single93.230000
63Single89.440000
64Single19.520000
65Single18.880000
66Single18.410000
67Single18.360000
68Single16.880000
69Single16.860000
70Single16.690000
71Single16.430000
72Single16.320000
73Single16.240000
74Single15.980000
75Single15.720000
76Single15.560000
77Single15.240000
78Single14.990000
79Single14.900000
80Single14.490000
81Single14.490000
82Single14.280000
83Single13.970000
84Single13.100000
85Single12.920000
86Single12.860000
87Single11.430000
88Single11.160000
89Single11.150000
90Single11.040000
91Single7.590000
92Single1.700000
93Unknown82.080000
94Unknown19.160000
95Unknown18.950000
96Unknown16.890000
97Unknown15.020000
98Unknown12.380000
99Widowed88.660000
\n" ], "text/plain": [ "" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "import pandas as pd\n", "\n", "conn = sqlite3.connect('100_patients.db')\n", "\n", "sql_statement = \"\"\"\n", "SELECT \n", " PatientMaritalStatus, \n", " PatientPopulationPercentageBelowPoverty\n", "FROM\n", " PatientCorePopulatedTable\n", "ORDER BY PatientMaritalStatus, 2 DESC;\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn)\n", "df.style.set_table_attributes('style=\"font-size: 12px\"')\n" ] } ], "metadata": { "jupytext": { "formats": "md:myst", "text_representation": { "extension": ".md", "format_name": "myst" } }, "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.7" }, "source_map": [ 11, 51, 66, 82, 98, 116, 133, 154, 171, 192, 210, 226, 241, 253, 268, 287, 304, 339, 360, 385, 409, 432, 449, 463, 480, 498, 515, 530, 547, 566 ] }, "nbformat": 4, "nbformat_minor": 5 }