Standard Queries

Below are examples of some standard queries. In some examples datasets are actual datasets in SQLShare, and in other cases the code reflects a generic example. In the latter instances [user].[table_] is used to indicate a particular dataset.

---

###Choose only entries that occur once You may want to use this command when you are only interested in a certain value/data entry that is specific (i.e. does not occur multiple times in your dataset) ``` SELECT * FROM [file name] WHERE [col of interest from file] IN (SELECT [col of interest] FROM [file name] GROUP BY [col of interest] HAVING COUNT (*) = 1) ``` Code explanation -The subquery, within parentheses, chooses only those values in your column of interest that occur one time. -The main query makes those values that occur one time into a new file. -Instead of = 1 you could also use < 2

###Keep only unique entries in a file

SELECT DISTINCT [col 1 name], [col 2 name]
FROM [file name]

Code explanation -The new file will only have unique combinations of values in columns 1 and 2 (redundancies will be removed)

###Join files on top of each other and add column denoting file origination

SELECT
1 AS fileID, *
FROM [user].[table1]
UNION ALL
SELECT
2 AS fileID, *
FROM [user].[table2]

Code explanation

• "fileID" is the name of the new column. A 1 in that column means the data in that row came from file 1, a 2 means it came from file 2.
• The input files for this command had identical structures

---

###Rename columns in a table - Use "Edit Dataset" button in SQLShare - After executing the query, press the "Update Dataset" button below the query window. - Use the "as" function.

Example:

SELECT 
column1 AS newcolumnname1,   
column2 AS newcolumnname2
FROM [user].[table1]

Code explanation:

SELECT -- Standard SQL to identify the source information you want to work with.

[table1] -- This is the name of your source table.

.column1 -- Specifies which column in [table1] to act upon. Note: This is the actual name of the column (e.g. if the column was named "SeqID", you'd have .SeqID; not .column1)

as -- Instructs SQL to rename specified column from [table1]

newcolumnname1 -- Assigns your desired name to the column.

, -- Allows you to separate multiple commands. Can be used extensively to allow for multiple commands in a single line. In this example, it is used to rename two columns in [table1].

FROM -- Standard SQL to identify location of source data.

Practical application of the "as" function: Example:

SELECT GeneCGS+CDS_CGs AS Total_CGs
FROM [sr320@washington.edu].[Stats_mRNA_CGs]

Code explanation:

This adds, mathematically, the numerical content of two columns (GeneCGS and CDS_CGs) that exist in the Stats_mRNA_CGs table and names the resulting column "Total_CGs".

---

###Count the number of terms in a column

SELECT column_name, COUNT(column_name)
FROM [user].[table_]
GROUP BY column_name

Code explanation:

SELECT -- Standard SQL to identify the source information you want to work with.

column_name -- Name of the column with the terms in it that you would like to count.

count(column_name) -- For our purposes, this will count the number of same terms in the specified column. However, the function is actually counting the number of rows in the table that contain the same terms in the specified column.

FROM -- Standard SQL to identify location source of data.

GROUP BY -- Groups the data by the terms in the specified column.

---

###Conventional Joining of tables (Keeping everything in first table)

SELECT * FROM [sr320@washington.edu].[table_QPX_Experiment_UniqueReads_simple.txt]
LEFT JOIN [sr320@washington.edu].[QPX_CLC_experiment_Kal_tab.txt]
ON [sr320@washington.edu].[table_QPX_Experiment_UniqueReads_simple.txt]."Feature ID"=[sr320@washington.edu].[QPX_CLC_experiment_Kal_tab.txt]."Feature ID"

---

###Removing Duplicates Records

SELECT distinct -- returns only distinct values
seqname,
Rel_CpG_pos,
methratio,
GOSlim_bin 
FROM [sr320@washington.edu].[table_BiGill_Gene_Methratio_VD_rmdup.csv]
Where aspect = 'P'

---

###Counting Records Select Count (*) from (SELECT seqname, Rel_CpG_pos, methratio, GOSlim_bin FROM [sr320@washington.edu].[table_BiGill_Gene_Methratio_VD_rmdup.csv] ) foo -- note needs alias

---

###Operate depending on column value SELECT cd.Column9 as seqname,
cd.Column2 as source,
cd.Column3 as feature,

Case when Column7 = '+'
then Column4 - mRNA_start + 1
Else mRNA_end - Column4 + 1
END as start,

 
FROM 
[sr320@washington.edu].[CDS GFF with Gene start and stop] cd