WIXLIB

Introduction toSQLPhil SpectorIntroduction to SQLPhil SpectorStatistical Computing FacilityUniversity of California, Berkeley

Introduction toSQL

Introduction toSQLWhat is SQL?

Introduction toSQLWhat is SQL?IStructured Query Language

Introduction toSQLWhat is SQL?IStructured Query LanguageIUsually “talk” to a database server

Introduction toSQLWhat is SQL?IStructured Query LanguageIUsually “talk” to a database serverIUsed as front end to many databases (mysql,postgresql, oracle, sybase)

Introduction toSQLWhat is SQL?IStructured Query LanguageIUsually “talk” to a database serverIUsed as front end to many databases (mysql,postgresql, oracle, sybase)IThree Subsystems: data description, data access andprivileges

Introduction toSQLWhat is SQL?IStructured Query LanguageIUsually “talk” to a database serverIUsed as front end to many databases (mysql,postgresql, oracle, sybase)IThree Subsystems: data description, data access andprivilegesIOptimized for certain data arrangements

Introduction toSQLWhat is SQL?IStructured Query LanguageIUsually “talk” to a database serverIUsed as front end to many databases (mysql,postgresql, oracle, sybase)IThree Subsystems: data description, data access andprivilegesIOptimized for certain data arrangementsIThe language is case-sensitive, but I use upper casefor keywords.

Introduction toSQLWhen do you need a Database?

Introduction toSQLWhen do you need a Database?IMultiple simultaneous changes to data (concurrency)

Introduction toSQLWhen do you need a Database?IMultiple simultaneous changes to data (concurrency)IData changes on a regular basis

Introduction toSQLWhen do you need a Database?IMultiple simultaneous changes to data (concurrency)IData changes on a regular basisILarge data sets where you only need someobservations/variables

Introduction toSQLWhen do you need a Database?IMultiple simultaneous changes to data (concurrency)IData changes on a regular basisILarge data sets where you only need someobservations/variablesIShare huge data set among many people

Introduction toSQLWhen do you need a Database?IMultiple simultaneous changes to data (concurrency)IData changes on a regular basisILarge data sets where you only need someobservations/variablesIShare huge data set among many peopleIRapid queries with no analysis

Introduction toSQLWhen do you need a Database?IMultiple simultaneous changes to data (concurrency)IData changes on a regular basisILarge data sets where you only need someobservations/variablesIShare huge data set among many peopleIRapid queries with no analysisIWeb interfaces to data, especially dynamic data

Introduction toSQLUses of DatabasesTraditional Uses:

Introduction toSQLUses of DatabasesTraditional Uses:ILive Queries

Introduction toSQLUses of DatabasesTraditional Uses:ILive QueriesIReport Generation

Introduction toSQLUses of DatabasesTraditional Uses:ILive QueriesIReport GenerationINormalization, foreign keys, joins, etc.

Introduction toSQLUses of DatabasesTraditional Uses:ILive QueriesIReport GenerationINormalization, foreign keys, joins, etc.Newer uses:

Introduction toSQLUses of DatabasesTraditional Uses:ILive QueriesIReport GenerationINormalization, foreign keys, joins, etc.Newer uses:IStorage - data is extracted and analyzed in anotherapplication

Introduction toSQLUses of DatabasesTraditional Uses:ILive QueriesIReport GenerationINormalization, foreign keys, joins, etc.Newer uses:IStorage - data is extracted and analyzed in anotherapplicationIBackends to web sites

Introduction toSQLUses of DatabasesTraditional Uses:ILive QueriesIReport GenerationINormalization, foreign keys, joins, etc.Newer uses:IStorage - data is extracted and analyzed in anotherapplicationIBackends to web sitesITraditional rules may not be as important

Introduction toSQLWays to Use SQL

Introduction toSQLWays to Use SQLIconsole command (mysql -u user -p dbname)

Introduction toSQLWays to Use SQLIconsole command (mysql -u user -p dbname)IGUI interfaces are often available

Introduction toSQLWays to Use SQLIconsole command (mysql -u user -p dbname)IGUI interfaces are often availableIInterfaces to many programming languages: R,python, perl, PHP, etc.

Introduction toSQLWays to Use SQLIconsole command (mysql -u user -p dbname)IGUI interfaces are often availableIInterfaces to many programming languages: R,python, perl, PHP, etc.ISQLite - use SQL without a database server

Introduction toSQLWays to Use SQLIconsole command (mysql -u user -p dbname)IGUI interfaces are often availableIInterfaces to many programming languages: R,python, perl, PHP, etc.ISQLite - use SQL without a database serverIPROC SQL in SAS

Introduction toSQLSome Relational Database Concepts

Introduction toSQLSome Relational Database ConceptsIA database server can contain many databases

Introduction toSQLSome Relational Database ConceptsIA database server can contain many databasesIDatabases are collections of tables

Introduction toSQLSome Relational Database ConceptsIA database server can contain many databasesIDatabases are collections of tablesITables are two-dimensional with rows (observations)and columns (variables)

Introduction toSQLSome Relational Database ConceptsIA database server can contain many databasesIDatabases are collections of tablesITables are two-dimensional with rows (observations)and columns (variables)ILimited mathematical and summary operationsavailable

Introduction toSQLSome Relational Database ConceptsIA database server can contain many databasesIDatabases are collections of tablesITables are two-dimensional with rows (observations)and columns (variables)ILimited mathematical and summary operationsavailableIVery good at combining information from severaltables

Introduction toSQLFinding Your Way Around the ServerSince a single server can support many databases, eachcontaining many tables, with each table having a varietyof columns, it’s easy to get lost when you’re working withdatabases. These commands will help figure out what’savailable:

Introduction toSQLFinding Your Way Around the ServerSince a single server can support many databases, eachcontaining many tables, with each table having a varietyof columns, it’s easy to get lost when you’re working withdatabases. These commands will help figure out what’savailable:ISHOW DATABASES;

Introduction toSQLFinding Your Way Around the ServerSince a single server can support many databases, eachcontaining many tables, with each table having a varietyof columns, it’s easy to get lost when you’re working withdatabases. These commands will help figure out what’savailable:ISHOW DATABASES;ISHOW TABLES IN database;

Introduction toSQLFinding Your Way Around the ServerSince a single server can support many databases, eachcontaining many tables, with each table having a varietyof columns, it’s easy to get lost when you’re working withdatabases. These commands will help figure out what’savailable:ISHOW DATABASES;ISHOW TABLES IN database;ISHOW COLUMNS IN table;

Introduction toSQLFinding Your Way Around the ServerSince a single server can support many databases, eachcontaining many tables, with each table having a varietyof columns, it’s easy to get lost when you’re working withdatabases. These commands will help figure out what’savailable:ISHOW DATABASES;ISHOW TABLES IN database;ISHOW COLUMNS IN table;IDESCRIBE table; - shows the columns and theirtypes

Introduction toSQLVariable TypesSQL supports a very large number of different formats forinternal storage of information.

Introduction toSQLVariable TypesSQL supports a very large number of different formats forinternal storage of information.Numeric

Introduction toSQLVariable TypesSQL supports a very large number of different formats forinternal storage of information.NumericIINTEGER, SMALLINT, BIGINT

Introduction toSQLVariable TypesSQL supports a very large number of different formats forinternal storage of information.NumericIINTEGER, SMALLINT, BIGINTINUMERIC(w,d), DECIMAL(w,d) - numbers with widthw and d decimal places

Introduction toSQLVariable TypesSQL supports a very large number of different formats forinternal storage of information.NumericIINTEGER, SMALLINT, BIGINTINUMERIC(w,d), DECIMAL(w,d) - numbers with widthw and d decimal placesIREAL, DOUBLE PRECISION - machine and databasedependent

Introduction toSQLVariable TypesSQL supports a very large number of different formats forinternal storage of information.NumericIINTEGER, SMALLINT, BIGINTINUMERIC(w,d), DECIMAL(w,d) - numbers with widthw and d decimal placesIREAL, DOUBLE PRECISION - machine and databasedependentIFLOAT(p) - floating point number with p binarydigits of precision

Introduction toSQLVariable Types (cont’d)Character

Introduction toSQLVariable Types (cont’d)CharacterICHARACTER(L) - a fixed-length character of length L

Introduction toSQLVariable Types (cont’d)CharacterICHARACTER(L) - a fixed-length character of length LICHARACTER VARYING(L) or VARCHAR(L) - supportsmaximum length of L

Introduction toSQLVariable Types (cont’d)CharacterICHARACTER(L) - a fixed-length character of length LICHARACTER VARYING(L) or VARCHAR(L) - supportsmaximum length of LBinary

Introduction toSQLVariable Types (cont’d)CharacterICHARACTER(L) - a fixed-length character of length LICHARACTER VARYING(L) or VARCHAR(L) - supportsmaximum length of LBinaryIBIT(L), BIT VARYING(L) - like correspondingcharacters

Introduction toSQLVariable Types (cont’d)CharacterICHARACTER(L) - a fixed-length character of length LICHARACTER VARYING(L) or VARCHAR(L) - supportsmaximum length of LBinaryIBIT(L), BIT VARYING(L) - like correspondingcharactersIBINARY LARGE OBJECT(L) or BLOB(L)

Introduction toSQLVariable Types (cont’d)CharacterICHARACTER(L) - a fixed-length character of length LICHARACTER VARYING(L) or VARCHAR(L) - supportsmaximum length of LBinaryIBIT(L), BIT VARYING(L) - like correspondingcharactersIBINARY LARGE OBJECT(L) or BLOB(L)Temporal

Introduction toSQLVariable Types (cont’d)CharacterICHARACTER(L) - a fixed-length character of length LICHARACTER VARYING(L) or VARCHAR(L) - supportsmaximum length of LBinaryIBIT(L), BIT VARYING(L) - like correspondingcharactersIBINARY LARGE OBJECT(L) or BLOB(L)TemporalIDATE

Introduction toSQLVariable Types (cont’d)CharacterICHARACTER(L) - a fixed-length character of length LICHARACTER VARYING(L) or VARCHAR(L) - supportsmaximum length of LBinaryIBIT(L), BIT VARYING(L) - like correspondingcharactersIBINARY LARGE OBJECT(L) or BLOB(L)TemporalIDATEITIME

Introduction toSQLVariable Types (cont’d)CharacterICHARACTER(L) - a fixed-length character of length LICHARACTER VARYING(L) or VARCHAR(L) - supportsmaximum length of LBinaryIBIT(L), BIT VARYING(L) - like correspondingcharactersIBINARY LARGE OBJECT(L) or BLOB(L)TemporalIDATEITIMEITIMESTAMP

Introduction toSQLCREATE TABLE statementSuppose we have data measured on the height and weightof children over a range of ages. The first step is decidingon the appropriate variable types, and creating the tablewith the CREATE TABLE command.

Introduction toSQLCREATE TABLE statementSuppose we have data measured on the height and weightof children over a range of ages. The first step is decidingon the appropriate variable types, and creating the tablewith the CREATE TABLE command.CREATE TABLE kids(id CHAR(6),race SMALLINT,age DECIMAL(6,3),height DECIMAL(7,3),weight DECIMAL(7,3),sex SMALLINT);

Introduction toSQLEntering observations into a tableWe could now enter individual items with the INSERTcommand:INSERT INTO kids VALUES(100011,2,10.346,148.5,38.95,1);This quickly gets tedious. We can automate the processusing the LOAD DATA command:

Introduction toSQLEntering observations into a tableWe could now enter individual items with the INSERTcommand:INSERT INTO kids VALUES(100011,2,10.346,148.5,38.95,1);This quickly gets tedious. We can automate the processusing the LOAD DATA command:LOAD DATA INFILE ’kids.tab’INTO TABLE kidsFIELDS TERMINATED BY ’\t’;