SQL

Creating and selecting

• Select
  • select * from <table> LIMIT <number e.g. 5> ORDER BY <col> desc #select all columns, limiting to the number stated and sorted by descending order
  • select <col> from <table> WHERE <col> = "<search query>"
  • select <col> from <table> WHERE <col> LIKE "%<search query>%"
  • select <col> from <table> WHERE <col> BETWEEN "<value 1>" AND "<value 2>"
  • select <col> from <table> WHERE <col> IN ('<value 1>', '<value 2>')

• select COUNT(<col>) from <table> WHERE <col> = "<search query>"
• select DISTINCT(<col>) from <table> WHERE <col> = "<search query>"
• select <col>, COUNT(<col>) AS <new col name> from <table> GROUP BY <col> HAVING <e.g. count(col) > n > #group by and condition

Opening and Closing files

Load

• %load_ext sql
• %sql ibm_db_sa://my-username:my-password@dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50000/BLUDB
• %%sql <function> #use sql for whole cell in Juptyer Notebook (else %)
  • e.g. select * from INTERNATIONAL_STUDENT_TEST_SCORES where country = :country #: is Python variable

Create

• Create
  • CREATE TABLE <table>(
    • <col> <VARCHAR(2)> <TABLE_PK> PRIMARY KEY (<col>) NOT NULL)

Drop

• DROP TABLE #table name;

Using Python

• import pandas
  • df = pandas.read_sql(<SQL statement>, <connection>)
  • df

Inserting, Deleting, Updating records

Insert

• INSERT INTO <table> (<col>)
  • VALUES(<'value1', 'value 2'>), (<'value1', 'value 2'>)

Delete

• DELETE FROM <table>
  • WHERE (<col> = <value>) OR WHERE (<col> in (<row value>))

Update

• UPDATE <table>
  • SET (<col> = <value>)
  • WHERE (<col> = <value>)