Difference between revisions of "This Is Your Brain On Informatics: Q&A"

From Pathology Education Instructional Resource
Jump to: navigation, search
(Created page with "==MariaDB Examples== ===Manually Creating a Table=== <p>This data table was uploaded as a TSV (tab separated values) file.</p> <p>LOAD DATA INFILE '[data table pathname]' INT...")
 
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
==MariaDB Examples==
+
== MariaDB Examples ==
  
===Manually Creating a Table===
+
=== Manually Creating a Table ===
 
<p>This data table was uploaded as a TSV (tab separated values) file.</p>
 
<p>This data table was uploaded as a TSV (tab separated values) file.</p>
 
<p>LOAD DATA INFILE '[data table pathname]' INTO TABLE `test` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES</p>
 
<p>LOAD DATA INFILE '[data table pathname]' INTO TABLE `test` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES</p>
  
===Quintiles===
+
=== Quintiles ===
  
<p>Q:  Find the maximums in column 2 of quintiles based on column 1 </p>
+
Q:  Find the maximums in column 2 of quintiles based on column 1
  
<p>
 
 
The following will create a temporary table that will order [var1], hold [var2] and also create a column with a counter variable named "id".
 
The following will create a temporary table that will order [var1], hold [var2] and also create a column with a counter variable named "id".
<br /><br />
+
 
 +
 
 
A:  CREATE TEMPORARY TABLE `rank` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY) SELECT [var1],[var2] FROM `test` WHERE [var3] = "string" AND [var1] <10000000 ORDER BY [var1];
 
A:  CREATE TEMPORARY TABLE `rank` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY) SELECT [var1],[var2] FROM `test` WHERE [var3] = "string" AND [var1] <10000000 ORDER BY [var1];
<br /><br />
+
 
 +
 
 
Then the use of the next command will need to be repeated for each quintile to find the maximum for each one.
 
Then the use of the next command will need to be repeated for each quintile to find the maximum for each one.
<br /><br />
+
 
 +
 
 
A:  SELECT MAX([var2]) FROM `rank` WHERE id BETWEEN [calculated lower bound of quintile] AND [calculated upper bound of quintile];
 
A:  SELECT MAX([var2]) FROM `rank` WHERE id BETWEEN [calculated lower bound of quintile] AND [calculated upper bound of quintile];
</p>
+
 
 +
 
 +
{{Template: This Is Your Brain On Informatics}}
 +
 
 +
[[Category:This Is Your Brain On Informatics]]

Latest revision as of 02:35, 19 March 2014

MariaDB Examples

Manually Creating a Table

This data table was uploaded as a TSV (tab separated values) file.

LOAD DATA INFILE '[data table pathname]' INTO TABLE `test` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES

Quintiles

Q: Find the maximums in column 2 of quintiles based on column 1

The following will create a temporary table that will order [var1], hold [var2] and also create a column with a counter variable named "id".


A: CREATE TEMPORARY TABLE `rank` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY) SELECT [var1],[var2] FROM `test` WHERE [var3] = "string" AND [var1] <10000000 ORDER BY [var1];


Then the use of the next command will need to be repeated for each quintile to find the maximum for each one.


A: SELECT MAX([var2]) FROM `rank` WHERE id BETWEEN [calculated lower bound of quintile] AND [calculated upper bound of quintile];