Introduction to SQL - University of California, Berkeley

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?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?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 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 ?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 ?

4 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 ?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 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.

6 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: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.

7 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: 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.

8 R,python, perl, PHP, - use SQL without a database serverIPROC SQLin SASI ntroduction 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 severaltablesIntroduction 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 severaltablesIntroduction 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 severaltablesIntroduction toSQLSome Relational database ConceptsIA database server can contain many databasesIDatabases are collections of tablesITables are two-dimensional with rows (observations)and columns (variables)

9 ILimited mathematical and summary operationsavailableIVery good at combining information from severaltablesIntroduction 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 severaltablesIntroduction 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 severaltablesIntroduction toSQLF inding 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.

10 These commands will help figure out what savailable:ISHOW DATABASES;ISHOW TABLES IN database ;ISHOW COLUMNS IN table;IDESCRIBE table;- shows the columns and theirtypesIntroduction toSQLF inding 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 theirtypesIntroduction toSQLF inding 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 theirtypesIntroduction toSQLF inding 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.