<jupyter_start><jupyter_text>ISB Cancer Gateway in the Cloud (ISB-CGC) SQL Reference Guide  This guide is intended as a quick refresher to SQL to help users understand and follow the hands-on portion of our Cloud Computing training session.In this notebook, we will use SQLite3 a small self-contained package to create a database and practice querying. To learn more about SQLite3 please visit [Here ](https://www.sqlite.org/index.html )  What is SQL?SQL or Structured Query Language, is a language to talk to databases. SQL provides a method to inquire about the contents of a database in a predictable and consistent syntax to receive useful results. It allows you to select specific data and to build complex reports. Today, SQL is a universal language of data. We use SQL to retrieve data from ISB-CGC BigQuery tables directly in Google BigQuery console.  Notebook Setup Import Dependencies<jupyter_code>import sqlite3<jupyter_output><empty_output><jupyter_text>Initialize Database and Connect<jupyter_code>conn = sqlite3.connect('proteins.db')
cur = conn.cursor()
conn.row_factory = sqlite3.Row<jupyter_output><empty_output><jupyter_text>How do I create a table in SQL?Databases contain tables, which can be visualized as a spreadsheet. There's a series of rows (called records in a database) and columns. The intersection of a row and a column is called a field.You can create a table with the CREATE query. It's useful to combine this with the IF NOT EXISTS statement, which prevents SQLite from clobbering an existing table.In this example, We will create a table called proteins containing two columns ID and Name. There are five data types (also referred to as storage classes) in SQL:TEXT: a text stringINTEGER: a whole numberREAL: a floating point (unlimited decimal places) numberBLOB: binary data (for instance, a .jpeg or .webp image)NULL: a null valueIn our example, We will use the data type TEXT for ID and Name. Similarly, we will also create another table called properties which will contain the ID and location and their data types. To prevent a record from being created without data in a specified field, you can add the NOT NULL directive.The SQL to create this field is: name TEXT NOT NULL<jupyter_code># Create proteins table
query = """
  DROP TABLE IF EXISTS proteins
"""
cur.execute(query)

query = """
  CREATE TABLE proteins (
    id TEXT,
    name TEXT
  )
"""
cur.execute(query)

# Create properties table
query = """
  DROP TABLE IF EXISTS properties
"""
query = """
  CREATE TABLE properties (
    id TEXT,
    location TEXT
  )
"""
cur.execute(query)<jupyter_output><empty_output><jupyter_text>How do I add data to a table?You can populate your new tables with some sample data by using the INSERT SQL keyword.You can add as many columns as you want to add to the table. If you want to add values in all columns of a table, then there is no need to specify column names, you can use the VALUES keyword to only insert values as shown below.The VALUES keyword expects a list in parentheses but can take multiple lists separated by commas.<jupyter_code># Insert into proteins table
cur.execute("INSERT INTO proteins VALUES ('P26939', 'TLNI')")
cur.execute("INSERT INTO proteins VALUES ('Q99PL5', 'PRBP')")
#cur.execute("INSERT INTO proteins VALUES ('Q6PB66', 'PGBM')")
#cur.execute("INSERT INTO proteins VALUES ('P11275', 'PPPE')")
#cur.execute("INSERT INTO proteins VALUES ('P26935', 'TLNP')")
cur.execute("INSERT INTO proteins VALUES ('PPR8PP', 'BRCC')")
cur.execute("INSERT INTO proteins VALUES ('QQ9PL6', 'TN11')")
cur.execute("INSERT INTO proteins VALUES ('QQPB66', 'TNNP')")
cur.execute("INSERT INTO proteins VALUES ('P11275', 'TINH')")
cur.execute("INSERT INTO proteins VALUES ('P36969', 'PPQL')")

# Insert into properties table
cur.execute("INSERT INTO properties VALUES ('P26939', 'cytoskeleton')")
cur.execute("INSERT INTO properties VALUES ('Q99PL5', 'cytosol')")
cur.execute("INSERT INTO properties VALUES ('Q6PB66', 'nucleus')")
cur.execute("INSERT INTO properties VALUES ('P11275', 'plasma')")
cur.execute("INSERT INTO properties VALUES ('P26935', 'plasma')")
cur.execute("INSERT INTO properties VALUES ('PPR8PP', 'cytoskeleton')")
cur.execute("INSERT INTO properties VALUES ('QQ9PL6', 'cytosol')")
#cur.execute("INSERT INTO properties VALUES ('QQPB66', 'nucleus')")
#cur.execute("INSERT INTO properties VALUES ('P11275', 'plasma')")
cur.execute("INSERT INTO properties VALUES ('P36969', 'plasma')")<jupyter_output><empty_output><jupyter_text>How do I select/fetch data in SQL? SELECT queries are used to fetch data from a database. We will use SELECT keyword for fetching table data in SQL. The syntax for retrieving/fetching data using SELECT keyword is:>SELECT column1, column2, column3, ... FROM table_name;To select all data:>SELECT * FROM table_name;To select only distinct values (no duplicate records):>SELECT DISTINCT column1, column2, column3, ... FROM table_name;A simple SELECT example would be this query below, which will return all the columns and rows from the table.Example:SELECT * FROM `isb-cgc-bq.TCGA_versioned.clinical_gdc_r24`LIMIT 1000The asterisk (*) means that we want to grab all the columns, without excluding anything. Since SQL databases usually consist of more than one table, the FROM keyword is required to specify which table we want to look in. LIMIT keyword can be used to specify how many lines we would like to fetch. Remember: The order of clauses matters in SQL. SQL uses the following order of precedence: FROM, SELECT, LIMIT. SELECT Example:<jupyter_code>cur.execute("SELECT * FROM proteins")
print(cur.fetchall())
cur.execute("SELECT id FROM proteins LIMIT 2")
print(cur.fetchall())<jupyter_output>[('P26939',), ('Q99PL5',)]<jupyter_text>How do I filter/retrieve data in SQL based on a condition? WHERE keyword allows us to filter data in SQL depending on a condition. The syntax for WHERE clause is:>SELECT column1, column2, column3, ... FROM table_name WHERE condition;In the example below, we can select how many mutations have been observed in KRAS. WHERE Example:SELECT COUNT(DISTINCT(sample_barcode_tumor)) AS numSamplesFROM `isb-cgc-bq.TCGA_versioned.somatic_mutation_hg38_gdc_r10`WHERE Hugo_Symbol="KRAS" SELECT Example combined with WHERE Clause<jupyter_code>cur.execute("SELECT * FROM properties WHERE location='cytoskeleton'")
print(cur.fetchall())
cur.execute("SELECT id FROM properties WHERE location='plasma'")
print(cur.fetchall())<jupyter_output>[('P11275',), ('P26935',), ('P36969',)]<jupyter_text>In the above example, we have used the ‘=’ operator. You can use other operators such as ‘>’, ‘=’, ‘<=’, IN, LIKE, and ‘BETWEEN’ depending upon your condition. How do I group data in SQL based on a condition? GROUP BY and ORDER BY clause allows us to filter data depending on a condition.GROUP BY in SQL is used to arrange similar data into a group. The GROUP BY clause follows the WHERE clause and comes before the ORDER BY clause.ORDER BY clause is used to sort the data in the ascending or descending order.Syntax:>SELECT column1, column 2…>FROM table_name>WHERE [condition]>GROUP BY column1, column2>ORDER BY column1, column2;In the example below, you can use the query to group the data in the proteins table, group it based on column name and order it in ASC order.  GROUP BY combined with ORDER BY Example:<jupyter_code>cur.execute("SELECT * FROM proteins WHERE name='BRCC' GROUP BY id ORDER BY name ASC")
print(cur.fetchall())<jupyter_output>[('PPR8PP', 'BRCC')]<jupyter_text>How do I query from more than one table?In more complex databases, most of the time there are several tables connected to each other in some way and linking the tables logically allows us to use the information stored in both tables at the same time.JOINS are used for fetching information from more than one table. A JOIN is a means for combining fields from two tables by using values common to each. These common variables are referred to as keys. The commonly used types of joins are: INNER JOIN The most important and frequently used of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.The INNER JOIN matches each row in one table with every row in other tables and allows you to query rows that contain columns from both tables.INNER JOIN returns rows that have matching values in both tables.The INNER JOIN is an optional clause of the SELECT statement. It appears immediately after the FROM clause. The basic syntax of the INNER JOIN is as follows:>SELECT table1.column1, table2.column2...FROM table1INNER JOIN table2ON table1.common_field = table2.common_field; INNER JOIN Example:<jupyter_code>cur.execute("SELECT proteins.name,properties.location FROM proteins INNER JOIN properties ON proteins.id = properties.id")
print(cur.fetchall())<jupyter_output>[('TLNI', 'cytoskeleton'), ('PRBP', 'cytosol'), ('BRCC', 'cytoskeleton'), ('TN11', 'cytosol'), ('TINH', 'plasma'), ('PPQL', 'plasma')]<jupyter_text>INNER JOIN combined with GROUP BY Clause Example:<jupyter_code>cur.execute("SELECT proteins.name,properties.location FROM proteins INNER JOIN properties ON proteins.id = properties.id GROUP BY proteins.id;")
print(cur.fetchall())<jupyter_output>[('TINH', 'plasma'), ('TLNI', 'cytoskeleton'), ('PPQL', 'plasma'), ('BRCC', 'cytoskeleton'), ('PRBP', 'cytosol'), ('TN11', 'cytosol')]<jupyter_text>LEFT JOIN The LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.LEFT JOIN keeps observations that are present in the left (first) table, dropping those that are only present in the other.The basic syntax of a LEFT JOIN is as follows:>SELECT table1.column1, table2.column2...FROM table1LEFT JOIN table2ON table1.common_field = table2.common_field;Here, the given condition could be any given expression based on your requirement. LEFT JOIN Example:<jupyter_code>cur.execute("SELECT proteins.name,properties.location FROM proteins LEFT JOIN properties ON proteins.id = properties.id")
print(cur.fetchall())<jupyter_output>[('TLNI', 'cytoskeleton'), ('PRBP', 'cytosol'), ('BRCC', 'cytoskeleton'), ('TN11', 'cytosol'), ('TNNP', None), ('TINH', 'plasma'), ('PPQL', 'plasma')]
{'fraction_non_alphanumeric': 0.09049466537342386, 'fraction_numerical': 0.014742967992240542, 'mean_word_length': 5.605381165919282, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 47, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '403712', 'license': 'Apache-2.0', 'n_tokens_mistral': 3047, 'n_tokens_neox': 2780, 'n_words': 1332, 'path': 'TeachingMaterials/2021-10-NIHLibrarySession/ISB_Cancer_Gateway_in_the_Cloud_(ISB_CGC)_SQL_Reference_Guide.ipynb', 'repo_name': 'rpatil524/Community-Notebooks', 'size': '10310'}