Introduction to SQL

1 Introduction toSQLPhil SpectorIntroduction to SQLPhil SpectorStatistical Computing FacilityUniversity of California, BerkeleyIntroduction toSQLI ntroduction 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 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 toSQLWhat is SQL?

2 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 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 toSQLWhat is SQL?

3 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 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 toSQLWhat is SQL?

4 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 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 dataIntroduction toSQLWhen do you need a Database?

5 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 dataIntroduction 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 dataIntroduction toSQLWhen do you need a Database?

6 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 dataIntroduction 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 dataIntroduction toSQLWhen do you need a Database?

7 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 dataIntroduction 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 dataIntroduction toSQLUses of DatabasesTraditional Uses:ILive QueriesIReport GenerationINormalization, foreign keys, joins, uses:IStorage - data is extracted and analyzed in anotherapplicationIBackends to web sitesITraditional rules may not be as importantIntroduction toSQLUses of DatabasesTraditional Uses.

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

9 IStorage - data is extracted and analyzed in anotherapplicationIBackends to web sitesITraditional rules may not be as importantIntroduction toSQLUses of DatabasesTraditional Uses:ILive QueriesIReport GenerationINormalization, foreign keys, joins, uses:IStorage - data is extracted and analyzed in anotherapplicationIBackends to web sitesITraditional rules may not be as importantIntroduction toSQLUses of DatabasesTraditional Uses:ILive QueriesIReport GenerationINormalization, foreign keys, joins, uses:IStorage - data is extracted and analyzed in anotherapplicationIBackends to web sitesITraditional rules may not be as importantIntroduction toSQLWays to Use SQLI console command (mysql -uuser-pdbname)IGUI interfaces are often availableIInterfaces to many programming languages: R,python, perl, PHP, - use SQL without a database serverIPROC SQLin SASI ntroduction toSQLWays to Use SQLI console command (mysql -uuser-pdbname)IGUI interfaces are often availableIInterfaces to many programming languages.

10 R,python, perl, PHP, - use SQL without a database serverIPROC SQLin SASI ntroduction toSQLWays to Use SQLI console command (mysql -uuser-pdbname)IGUI interfaces are often availableIInterfaces to many programming languages: R,python, perl, PHP, - use SQL without a database serverIPROC SQLin SASI ntroduction toSQLWays to Use SQLI console command (mysql -uuser-pdbname)IGUI interfaces are often availableIInterfaces to many programming languages: R,python, perl, PHP, - use SQL without a database serverIPROC SQLin SASI ntroduction toSQLWays to Use SQLI console command (mysql -uuser-pdbname)IGUI interfaces are often availableIInterfaces to many programming languages: R,python, perl, PHP, - use SQL without a database serverIPROC SQLin SASI ntroduction toSQLWays to Use SQLI console command (mysql -uuser-pdbname)IGUI interfaces are often availableIInterfaces to many programming languages.