Introduction to
SQL
Phil Spector
Introduction to SQL
Phil Spector
Statistical Computing Facility
University of California, Berkeley
Introduction to
SQL
Introduction to
SQL What is SQL?
Introduction to
SQL What is SQL?
I Structured Query Language
Introduction to
SQL What is SQL?
I Structured Query Language
I Usually “talk” to a database server
Introduction to
SQL What is SQL?
I Structured Query Language
I Usually “talk” to a database server
I Used as front end to many databases (mysql,
postgresql, oracle, sybase)
Introduction to
SQL What is SQL?
I Structured Query Language
I Usually “talk” to a database server
I Used as front end to many databases (mysql,
postgresql, oracle, sybase)
I Three Subsystems: data description, data access and
privileges
Introduction to
SQL What is SQL?
I Structured Query Language
I Usually “talk” to a database server
I Used as front end to many databases (mysql,
postgresql, oracle, sybase)
I Three Subsystems: data description, data access and
privileges
I Optimized for certain data arrangements
Introduction to
SQL What is SQL?
I Structured Query Language
I Usually “talk” to a database server
I Used as front end to many databases (mysql,
postgresql, oracle, sybase)
I Three Subsystems: data description, data access and
privileges
I Optimized for certain data arrangements
I The language is case-sensitive, but I use upper case
for keywords.
Introduction to
SQL When do you need a Database?
Introduction to
SQL When do you need a Database?
I Multiple simultaneous changes to data (concurrency)
Introduction to
SQL When do you need a Database?
I Multiple simultaneous changes to data (concurrency)
I Data changes on a regular basis
Introduction to
SQL When do you need a Database?
I Multiple simultaneous changes to data (concurrency)
I Data changes on a regular basis
I Large data sets where you only need some
observations/variables
Introduction to
SQL When do you need a Database?
I Multiple simultaneous changes to data (concurrency)
I Data changes on a regular basis
I Large data sets where you only need some
observations/variables
I Share huge data set among many people
Introduction to
SQL When do you need a Database?
I Multiple simultaneous changes to data (concurrency)
I Data changes on a regular basis
I Large data sets where you only need some
observations/variables
I Share huge data set among many people
I Rapid queries with no analysis
Introduction to
SQL When do you need a Database?
I Multiple simultaneous changes to data (concurrency)
I Data changes on a regular basis
I Large data sets where you only need some
observations/variables
I Share huge data set among many people
I Rapid queries with no analysis
I Web interfaces to data, especially dynamic data
Introduction to
SQL Uses of Databases
Traditional Uses:
Introduction to
SQL Uses of Databases
Traditional Uses:
I Live Queries
Introduction to
SQL Uses of Databases
Traditional Uses:
I Live Queries
I Report Generation
Introduction to
SQL Uses of Databases
Traditional Uses:
I Live Queries
I Report Generation
I Normalization, foreign keys, joins, etc.
Introduction to
SQL Uses of Databases
Traditional Uses:
I Live Queries
I Report Generation
I Normalization, foreign keys, joins, etc.
Newer uses:
Introduction to
SQL Uses of Databases
Traditional Uses:
I Live Queries
I Report Generation
I Normalization, foreign keys, joins, etc.
Newer uses:
I Storage - data is extracted and analyzed in another
application
Introduction to
SQL Uses of Databases
Traditional Uses:
I Live Queries
I Report Generation
I Normalization, foreign keys, joins, etc.
Newer uses:
I Storage - data is extracted and analyzed in another
application
I Backends to web sites
Introduction to
SQL Uses of Databases
Traditional Uses:
I Live Queries
I Report Generation
I Normalization, foreign keys, joins, etc.
Newer uses:
I Storage - data is extracted and analyzed in another
application
I Backends to web sites
I Traditional rules may not be as important
Introduction to
SQL Ways to Use SQL
Introduction to
SQL Ways to Use SQL
I console command (mysql -u user -p dbname)
Introduction to
SQL Ways to Use SQL
I console command (mysql -u user -p dbname)
I GUI interfaces are often available
Introduction to
SQL Ways to Use SQL
I console command (mysql -u user -p dbname)
I GUI interfaces are often available
I Interfaces to many programming languages: R,
python, perl, PHP, etc.
Introduction to
SQL Ways to Use SQL
I console command (mysql -u user -p dbname)
I GUI interfaces are often available
I Interfaces to many programming languages: R,
python, perl, PHP, etc.
I SQLite - use SQL without a database server
Introduction to
SQL Ways to Use SQL
I console command (mysql -u user -p dbname)
I GUI interfaces are often available
I Interfaces to many programming languages: R,
python, perl, PHP, etc.
I SQLite - use SQL without a database server
I PROC SQL in SAS
Introduction to
SQL Some Relational Database Concepts
Introduction to
SQL Some Relational Database Concepts
I A database server can contain many databases
Introduction to
SQL Some Relational Database Concepts
I A database server can contain many databases
I Databases are collections of tables
Introduction to
SQL Some Relational Database Concepts
I A database server can contain many databases
I Databases are collections of tables
I Tables are two-dimensional with rows (observations)
and columns (variables)
Introduction to
SQL Some Relational Database Concepts
I A database server can contain many databases
I Databases are collections of tables
I Tables are two-dimensional with rows (observations)
and columns (variables)
I Limited mathematical and summary operations
available
Introduction to
SQL Some Relational Database Concepts
I A database server can contain many databases
I Databases are collections of tables
I Tables are two-dimensional with rows (observations)
and columns (variables)
I Limited mathematical and summary operations
available
I Very good at combining information from several
tables
