Databases Unit 3 SQL Language: Basics - PoliTO

DatabasesSQL language: basicsDatabasesUnit 3SQL language: basicsDBMGSQL language: basicsIntroductionThe SELECT statement: basicsNested queriesSet operatorsUpdate commandsTable managementDBMGElena Baralis and Tania Cerquitelli 2013 Politecnico di Torino21

DatabasesSQL language: basicsSQL language: basicsIntroductionDBMGIntroductionIntroduction to the SQL languageLanguage commandsNotationDBMGElena Baralis and Tania Cerquitelli 2013 Politecnico di Torino42

DatabasesSQL language: basicsIntroductionIntroduction to the SQL languageDBMGThe SQL languageA language for managing relational databasesStructured Query LanguageSQL provides commands todefine the schema of a relational databaseread and write datadefine the schema of derived tablesdefinire user access priviligesmanage transactionsDBMGElena Baralis and Tania Cerquitelli 2013 Politecnico di Torino63

DatabasesSQL language: basicsThe SQL languageSQL is a set-level languageoperators are applied to relationsthe result is always a relationSQL is a declarative languageit describes what to do and not how to do itit has a higher level of abstraction compared totraditional programming languagesDBMG7The SQL languageThe language may be used in two waysinteractivecompileda host language encapsulates the SQL commandsSQL commands can be distinguished from the hostlanguage commands by means of appropriatesyntactical devicesDBMGElena Baralis and Tania Cerquitelli 2013 Politecnico di Torino84

DatabasesSQL language: basicsIntroductionLanguage commandsDBMGThe SQL languageIt can be subdivided intoDML (Data Manipulation Language)language for querying and updating the dataDDL (Data Definition Language)language for defining the database structureDBMGElena Baralis and Tania Cerquitelli 2013 Politecnico di Torino105

DatabasesSQL language: basicsData Manipulation LanguageTo query a database in order to extract the dataof interestSELECTTo modify a database instanceinsertion of new information into a tableINSERTupdate of the information in the databaseUPDATEdeletion of obsolete dataDELETEDBMG11Data Definition LanguageTo define a database schemacreation, modification and deletion of tablesCREATE, ALTER, DROP TABLETo define derived tablescreation, modification and deletion of tables whosecontent is obtained from other database tablesCREATE, ALTER, DROP VIEWTo define complementary data structures forefficiently retrieving the datacreation and deletion of indicesDBMGCREATE, DROP INDEXElena Baralis and Tania Cerquitelli 2013 Politecnico di Torino126

DatabasesSQL language: basicsData Definition LanguageTo define user access privilegesgrant and revocation of privileges on resourcesGRANT, REVOKETo define transactionstermination of a transactionCOMMIT, ROLLBACKDBMG13Evolution of the SQL standardInformalnameBasic SQLSQL-2Official nameSQL-86Basic constructsSQL-89Referential integritySQL-92Relational modelSeveral new constructs3 levels: entry, intermediate, fullSQL:1999Object-relational modelOrganized into multiple partsTriggers, external functions, SQL:2003Extensions to the object modelRemoval of unused constructsNew parts: SQL/JRT, SQL/XMLSQL-3DBMGElena Baralis and Tania Cerquitelli 2013 Politecnico di TorinoFeatures147

DatabasesSQL language: basicsIntroduzioneNotationDBMGSyntax of SQL commandsNotationlanguage keywordsupper case, dark blue colorvariable termsitalic fontDBMGElena Baralis and Tania Cerquitelli 2013 Politecnico di Torino168

DatabasesSQL language: basicsSyntax of SQL commandsGrammarangle brackets to isolate a syntactic termsquare brackets [ ]to mark that the enclosed term is optionalbraces { }to mark that the enclosed term may not appear ormay repeated an arbitrary number of itemsvertical bar to mark that the term must be chosen among thoseseparated by the barsDBMG17Example databaseSupplier and part DBtable P: it describes the available productsprimary key: PIdtable S: it describes the suppliersprimary key: SIdtable SP: it describes supplies, by relating eachproduct to the suppliers that provide itprimary key: (SId, PId)DBMGElena Baralis and Tania Cerquitelli 2013 Politecnico di Torino189

DatabasesSQL language: basicsInstance of the example nstance of the example hensDBMGElena Baralis and Tania Cerquitelli 2013 Politecnico di Torino2010

DatabasesSQL language: basicsInstance of the example databaseSPDBMGElena Baralis and Tania Cerquitelli 2013 Politecnico di 111

Unit 3 SQL language: basics DBMG 2 SQL language: basics Introduction The SELECT statement: basics Nested queries Set operators Update commands Table management. Databases SQL language: basics Elena Baralisand Tania Cerquitelli 2013 Politecnico