Introduction to SQL
February 23, 2012
Calvin Pan
“Any sufficiently advanced technology is
indistinguishable from magic.”
- Arthur C. Clarke
What is SQL?
• Language developed by IBM in 1970s for
manipulating structured data and retrieving
said data
• Several competing implementations from IBM,
Oracle, PostgreSQL, Microsoft (we use this one,
specifically SQL Server 2008)
• Queries: statements that retrieve data
How data in a relational database is
organized
• Tables have columns (fields) and rows (records)
• Tables can be related (value in certain field from
table A must exist in corresponding field from
table B)
• Views (stored queries which can be treated like
tables)
The only statement you need to know
SELECT
• Used to retrieve data from tables
• Can also be used to perform calculations on
data from tables
Components of the SELECT statement
[ WITH <common_table_expression>]
the only required part
SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
commonly used
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
-- parts in square brackets [] are optional
Simple SELECT example
ProbesetID Snp_chr Snp_bp p
1415670_at 1 3013441 0.80984 raw_pvalues
1415670_at 1 3036178 0.0014957
-- comments are preceded by two hyphens
-- * means all columns are returned
SELECT * FROM raw_pvalues
WHERE p < 1
ProbesetID Snp_chr Snp_bp p
1415670_at 1 3013441 0.80984
1415670_at 1 3036178 0.0014957
Another simple SELECT example
ProbesetID Snp_chr Snp_bp p
1415670_at 1 3013441 0.80984 raw_pvalues
1415670_at 1 3036178 0.0014957
-- comments are preceded by two hyphens
-- * means all columns are returned
SELECT * FROM raw_pvalues
WHERE p < 1e-2 AND snp_bp > 3020000
ProbesetID Snp_chr Snp_bp p
1415670_at 1 3036178 0.0014957
SQL Joins
SQL Joins
SQL Joins
SQL Joins
Click here to run query!
aggregate function
alias
derived table
common table
expression (CTE)
Using SQL from R
1. Connect to database
Using SQL from R
1. Connect to database
2. Run query
Using SQL from R
1. Connect to database
2. Run query
3. There is no step 3
Connecting to SQL Server from R
# requires RODBC package to be installed
library(RODBC)
ch = odbcConnect('DSN=Inbred')
# DSN: data source name
# use DTM ODBC Manager to see available DSNs
# on Xenon
Running a SQL query from R
# results is a data frame
results = sqlQuery(ch, 'select * from snp_info')
# or
q = 'select * from snp_info'
results = sqlQuery(ch, q)
References/Resources
• SQL Server Books Online T-SQL reference (main page):
[Link]
• SQL Server Books Online T-SQL reference (SELECT statement):
[Link]
• Tutorial: SQL Server Management Studio:
[Link]
• Tutorial: Writing Transact-SQL Statements:
[Link]
• SQL Server Express Edition (free, requires Windows):
[Link]
• SQL joins: [Link]
[Link]
• RODBC: [Link]
• pyodbc: [Link]
• Instant SQL Formatter (makes code easier to read):
[Link]