A Quick Relational Database Tour - Revision history

Dondi: /* Creating a Table */ Fix markup.

2017-09-26T21:58:45Z

‎Creating a Table: Fix markup.

Dondi: /* Creating a Table */ Indicate bash working directory.

2017-09-26T21:53:20Z

‎Creating a Table: Indicate bash working directory.

Dondi: /* Inserting Data */ Markup fix.

2017-09-26T21:35:41Z

‎Inserting Data: Markup fix.

Dondi: /* Creating a Table */ Even moar version updates.

2017-09-26T21:30:41Z

‎Creating a Table: Even moar version updates.

Dondi: /* Creating a Database */ Latest version updates.

2017-09-26T21:26:39Z

‎Creating a Database: Latest version updates.

Dondi: /* Running PostgreSQL on the Lab Machines */ More software version updates.

2017-09-26T21:25:09Z

‎Running PostgreSQL on the Lab Machines: More software version updates.

Dondi: /* Running PostgreSQL on the Lab Machines */ Update to latest pgAdmin.

2017-09-26T21:24:31Z

‎Running PostgreSQL on the Lab Machines: Update to latest pgAdmin.

Dondi: /* The Notorious Apostrophe */ Wiki markup fix.

2017-09-26T06:53:49Z

‎The Notorious Apostrophe: Wiki markup fix.

Dondi: /* An Introduction to SQL Select Queries */ Transcribe a few more sections.

2017-09-26T06:52:48Z

‎An Introduction to SQL Select Queries: Transcribe a few more sections.

Dondi: Type out some basic select queries.

2017-09-26T06:30:07Z

Type out some basic select queries.

@@ Line 36: / Line 36: @@
 It’s hard to use a database without any data—so let’s bring some in. For this tour, we’ll use some publicly-available data: the [https://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm FDA Drug Database].
-Download the linked .zip file in the ''Download File'' section of that page. Or '''curl''' it from the command line! You can use '''curl -O''' to automatically save the download as a file. To access the Windows Documents folder from ‘’’bash’’’, ‘’’cd’’’ to ‘’’/mnt/c/Users/biolab/Documents’’’.
+Download the linked .zip file in the ''Download File'' section of that page. Or '''curl''' it from the command line! You can use '''curl -O''' to automatically save the download as a file. To access the Windows Documents folder from '''bash''', '''cd''' to '''/mnt/c/Users/biolab/Documents'''.
 When you decompress the file, you’ll see that it’s a bunch of .txt files. Many databases are distributed in this format for maximum interoperability. However, this means that we have to do some work to get it into an actual relational database management system like PostgreSQL.

@@ Line 36: / Line 36: @@
 It’s hard to use a database without any data—so let’s bring some in. For this tour, we’ll use some publicly-available data: the [https://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm FDA Drug Database].
-Download the linked .zip file in the ''Download File'' section of that page. Or '''curl''' it from the command line! You can use '''curl -O''' to automatically save the download as a file.
+Download the linked .zip file in the ''Download File'' section of that page. Or '''curl''' it from the command line! You can use '''curl -O''' to automatically save the download as a file. To access the Windows Documents folder from ‘’’bash’’’, ‘’’cd’’’ to ‘’’/mnt/c/Users/biolab/Documents’’’.
 When you decompress the file, you’ll see that it’s a bunch of .txt files. Many databases are distributed in this format for maximum interoperability. However, this means that we have to do some work to get it into an actual relational database management system like PostgreSQL.

@@ Line 60: / Line 60: @@
 == Inserting Data ==
-The SQL <code>insert</code> statement allows you to add a record to a table. But if you look at the _Products.txt_ file, you’ll notice that it is what’s called a _tab-delimited text file_. How does one build an <code>insert</code> statement for each of these lines without having to do a lot of typing? One solution involves a command you’ve seen before: '''sed'''!
+The SQL <code>insert</code> statement allows you to add a record to a table. But if you look at the ''Products.txt'' file, you’ll notice that it is what’s called a ''tab-delimited text file''. How does one build an <code>insert</code> statement for each of these lines without having to do a lot of typing? One solution involves a command you’ve seen before: '''sed'''!
 Since this is a quick tour, we won't spend time to explain exactly how we come up with the '''sed''' command below.  But you should be able to see the pieces:

@@ Line 46: / Line 46: @@
   create table product(appl_no varchar(6) not null, product_no varchar(6) not null, form varchar(255), strength varchar(240), reference_drug int, drug_name varchar(125), active_ingredient varchar(255), reference_standard int, primary key (appl_no, product_no))
-To do this in '''pgAdmin''', click the SQL button in the toolbar. A new window with an '''SQL Editor''' tab appears. You can type (or copy-paste) the above command into that tab.
+To do this in '''pgAdmin''', choose '''Tools > Query Tool''' from the menu bar. A new '''Query''' tab appears. You can type (or copy-paste) the above command into that tab.
-As always, watch out for typos! When ready, click on the '''Execute query''' button in the toolbar. (its button looks like a green play button)
+As always, watch out for typos! When ready, click on the '''Execute/Refresh''' button in the toolbar. (its button looks like a lightning bolt)
 Upon executing the query, a success message should appear in the '''Messages''' tab of the ''Output Pane'' in the bottom half of the window.

@@ Line 20: / Line 20: @@
 == Creating a Database ==
-Once the server is running, the red '''x''' disappears from the ''PostgreSQL 9.6 (localhost:5432)'' icon, and additional icons appear beneath it. If you click on the '''+''' button to the left of the ''Databases'' icon, you will see the databases that are currently available.  Initially, you will see a single database called '''postgres'''.
+Once the server is running, the red '''x''' disappears from the ''PostgreSQL 9.6'' icon, and additional icons appear beneath it. If you click on the '''+''' button to the left of the ''Databases'' icon, you will see the databases that are currently available.  Initially, you will see a single database called '''postgres'''.
-To create a database, right-click on the ''Databases'' icon and choose ''New Database...'' from the menu that appears. In the '''New Database''' dialog, the only information you need to supply is your new database’s name.
+To create a database, right-click on the ''Databases'' icon and choose ''Create > Database...'' from the menu that appears. In the '''Create - Database''' dialog, the only information you need to supply is your new database’s name.
-When you click '''OK''', you will return to the main '''pgAdmin''' window and you should see your new database underneath the ''Databases'' icon.
+When you click '''Save''', you will return to the main '''pgAdmin''' window and you should see your new database underneath the ''Databases'' icon.
 Note that you only need to go through this creation process once; that database will remain available until it is explicitly deleted.

@@ Line 14: / Line 14: @@
 #* Browser
 #** Servers (1)
-#*** PostgreSQL 9.6 (localhost:5432)
+#*** PostgreSQL 9.6
-# Double-click on ''PostgreSQL 9.6 (localhost:5432)'' to connect to the database server
+# Double-click on ''PostgreSQL 9.6'' to connect to the database server
 # The password to start the server will be given in class

@@ Line 12: / Line 12: @@
 # Launch '''pgAdmin'''
 # The '''pgAdmin''' window starts with a hierarchical view on the left that starts with three layers:
-#* Server Groups
+#* Browser
 #** Servers (1)
 #*** PostgreSQL 9.6 (localhost:5432)

@@ Line 178: / Line 178: @@
 Fortunately, SQL has a solution: apostrophes ''inside'' text should be indicated via ''two consecutive'' apostrophes, or '''<nowiki>''</nowiki>'''.  When encountered, SQL converts this pair of apostrophes into a single one, and does not interpret these apostrophes as ending a piece of text.  Thus, the above query will work if rewritten in this way:
-  select * from product where drug_name = 'CHILDREN''S ADVIL'
+  select * from product where drug_name = 'CHILDREN'&apos;S ADVIL'
 While the solution does exist, it isn’t automatic: you need to be aware that apostrophes have to be written as “double apostrophes” before passing any text values on to SQL. Keep this in mind when interacting with any database that uses SQL (which is to say, most relational database management systems).

← Older revision		Revision as of 06:52, 26 September 2017
Line 117:		Line 117:

	The caveat here is that '''~''' is a PostgreSQL-specific feature: if you move to other database systems (such as Microsoft Access), that feature may either be done differently or missing completely, since it is not part of the official SQL standard.		The caveat here is that '''~''' is a PostgreSQL-specific feature: if you move to other database systems (such as Microsoft Access), that feature may either be done differently or missing completely, since it is not part of the official SQL standard.
		+
		+	=== Sorting ===
		+
		+	As you play with various queries on the ''product'' table, you’ve probably noticed that results are returned in no particular order; if you’d like to sort the results in some way, tack on an ''order by'' clause at the end of the ''select'' query:
		+
		+	select drug_name, form, strength from product where form like '%ORAL%' order by drug_name
		+
		+	You can add more fields for a very specific sort order:
		+
		+	select drug_name, form, strength from product where form like '%ORAL%' order by drug_name, form
		+
		+	The above query returns the same records, but this time sorted by drug_name first, then by form ''within'' the records with the same drug_name.
		+
		+	Sort order is ascending by default (e.g., A to Z, 0 to 9); for the reverse order, add ''desc'' to the field(s) that you’d like to see sorted in reverse:
		+
		+	select drug_name, form, strength from product where form like '%ORAL%' order by drug_name desc, form
		+
		+	This will display records with drug names displayed in reverse alphabetical order; within each drug name, however, form will still be sorted in ascending order.
		+
		+	=== Aggregate Queries ===
		+
		+	For large databases, SQL provides ''aggregate'' (a.k.a. “grouping”) queries that summarize multiple records in different ways.
		+
		+	The simplest form of summary is counting: ''how many'' records were retrieved? A simple overall count is done by using ''count(*)'' as the thing to select (or ''project'', in formal relational algebraic terms):
		+
		+	select count(*) from product where drug_name like '%ASPIRIN%'
		+
		+	Of course, you can mix and match everything you have learned so far. To count the drug products whose names include “ASPIRIN” and are delivered by capsule, you can query:
		+
		+	select count(*) from product where drug_name like '%ASPIRIN%' and form like '%CAPSULE%'
		+
		+	Aggregators other than ''count'' are available, such as ''min'', ''max'', and ''avg'' (average or mean). We don’t have meaningful numeric fields in this table so we’ll leave that for another time.
		+
		+	Finally, you can aggregate ''multiple groups'' of data, so that you get different sets of statistics. The ''group by'' keyword does this:
		+
		+	select form, count(*) from product where drug_name ~ 'CODEINE' group by form
		+
		+	The main rule with ''group by'' is that the column being grouped should also be part of the ''select'' clause. This makes sense because otherwise, you wouldn’t be able to tell which group was which! And of course, you can mix and match. For example, the query above might yield more insight if we arrange the data by count:
		+
		+	select form, count(*) from product where drug_name ~ 'CODEINE' group by form order by count
		+
		+	=== Joins ===
		+
		+	Thus far, we’ve only been working with one table: ''product''. The FDA drug database has 11 tables overall. The FDA drug database definition diagram shows how these tables relate to each other. SQL can ''join'' related tables together; although we do not have enough time to show this live (because it would entail loading additional tables plus having a deeper understanding of how to navigate those tables), the concept is important enough to at least describe.
		+
		+	An SQL ''join'' uses the same basic ''select'' command, but requires the tables being joined (such as ''product'' and ''application''), as well as the fields/columns to use for “joining” records. “Joining” records means that their fields are combined to create a new “virtual” record. All other parts of the ''select'' command retain the same meanings as before:
		+
		+	select '''columns''' from '''table1''' inner join '''table2''' on ('''join condition''') where '''conditions'''
		+
		+	As in the basic ''select'' command, you can tailor the ''where'' conditions as you need to pull records based on other criteria.
		+
		+	If you like what’s happening here, then solely for your personal edification, you can try to create and load all eleven tables from the FDA drug database into PostgreSQL. Slap a web service and some web pages on this database and you have yourself a data-driven web app!
		+
		+	=== The Notorious Apostrophe ===
		+
		+	You might have noticed that, because the apostrophe or single quote is used to indicate specific values in SQL (e.g., '%ASPIRIN%', 'CAPSULE', '^600', etc.), we run into a potential problem when the value itself should contain an apostrophe. This is not as uncommon as one might think; for example, a good number of drug products have apostrophes in their names (''INFANTS' FEVERALL'', ''CHILDREN'S MOTRIN'', and ''WOMEN'S ROGAINE'', to name a few). A statement such as the one below will not behave as you might expect, since the apostrophe will be misinterpreted as ending a piece of text rather than as part of the text itself:
		+
		+	select * from product where drug_name = 'CHILDREN'S ADVIL'
		+
		+	Fortunately, SQL has a solution: apostrophes ''inside'' text should be indicated via ''two consecutive'' apostrophes, or '''<nowiki>''</nowiki>'''. When encountered, SQL converts this pair of apostrophes into a single one, and does not interpret these apostrophes as ending a piece of text. Thus, the above query will work if rewritten in this way:
		+
		+	select * from product where drug_name = 'CHILDREN''S ADVIL'
		+
		+	While the solution does exist, it isn’t automatic: you need to be aware that apostrophes have to be written as “double apostrophes” before passing any text values on to SQL. Keep this in mind when interacting with any database that uses SQL (which is to say, most relational database management systems).

	== SQL Injection ==		== SQL Injection ==

← Older revision		Revision as of 06:30, 26 September 2017
Line 79:		Line 79:

	To save us some time, just like in a cooking show, we have a “baked version” of this file for your convenience. [[Media:Products.sql.zip\|Download it from this link]], unzip, then copy/paste into '''pgAdmin''' as stated above.		To save us some time, just like in a cooking show, we have a “baked version” of this file for your convenience. [[Media:Products.sql.zip\|Download it from this link]], unzip, then copy/paste into '''pgAdmin''' as stated above.
		+
		+	== An Introduction to SQL Select Queries ==
		+
		+	Database activities are triggered via ''SQL'' commands. The ''select'' command is the SQL “kitchen sink” for retrieving information from a database. Its general, basic form is:
		+
		+	select '''''columns''''' from '''''tables''''' where '''''conditions'''''
		+
		+	As you will see, ''select'' can do even more, but let’s start simple.
		+
		+	=== Basics ===
		+
		+	The simplest type of ''select'' queries involve getting records from an individual table based on relatively simple conditions. For example:
		+
		+	select form from product where drug_name = 'ASPIRIN'
		+
		+	...will retrieve the method of delivery for drug products whose name is ''exactly'' “ASPIRIN.”
		+
		+	In addition to equality, ''like'' lends further flexibility. The ''like'' comparison allows for pattern matching, similar but not identical to '''grep'''. In SQL, the percent sign (“'''%'''”) is a “wildcard” that can represent any number of letters and symbols. ''like'' and '''%''' can be combined for broader queries, such as this one, which expands the previous query to include any drug whose name ''contains'' “ASPIRIN:”
		+
		+	select form from product where drug_name like '%ASPIRIN%'
		+
		+	If you want ''select'' to display ''all'' columns of a database record, use the asterisk ('''*'''):
		+
		+	select * from product where drug_name like '%ASPIRIN%'
		+
		+	Conditions can be combined via ''and'' and ''or''; for example, retrieving drugs whose name contains ''either'' “ASPIRIN” or “TYLENOL” can be done with:
		+
		+	select * from product where drug_name like '%ASPIRIN%' or drug_name like '%TYLENOL%'
		+
		+	The ''like'' comparator can do simple text matches, but it does ''not'' use regular expressions (i.e., the search patterns recognized by '''grep''' and '''sed'''). This area is a little shaky in SQL-land; there is an official ''similar to'' comparator which is the official way to make regular expression comparisons, but the format for those expressions is not the same as the format used by '''grep''' and '''sed'''.
		+
		+	Fortunately, PostgreSQL has a specific, PostgreSQL-only comparator that ''does'' match the same patterns used by '''grep''' and '''sed''': the tilde ('''~'''). Comparing with '''~''' is equivalent to a '''grep'''- or '''sed'''-like comparison:
		+
		+	select * from product where drug_name ~ '(DOPAMINE\|NITRO)'
		+	select * from product where drug_name ~ 'ATE$'
		+	select * from product where strength ~ '^600'
		+
		+	The caveat here is that '''~''' is a PostgreSQL-specific feature: if you move to other database systems (such as Microsoft Access), that feature may either be done differently or missing completely, since it is not part of the official SQL standard.

	== SQL Injection ==		== SQL Injection ==