Learning SQL on PostgreSQL:
Gespeichert in:
| 1. Verfasser: | |
|---|---|
| Format: | Buch |
| Sprache: | English |
| Veröffentlicht: |
Portland, Oregon
Franklin, Beedle & Associates Inc.
[2021]
|
| Schlagworte: | |
| Online-Zugang: | Inhaltsverzeichnis |
| Beschreibung: | X, 310 SeitenSeiten |
| ISBN: | 9781590282878 |
Internformat
MARC
| LEADER | 00000nam a22000008c 4500 | ||
|---|---|---|---|
| 001 | BV048896653 | ||
| 003 | DE-604 | ||
| 005 | 20230908 | ||
| 007 | t | ||
| 008 | 230412s2021 |||| 00||| eng d | ||
| 020 | |a 9781590282878 |9 978-1-59028-287-8 | ||
| 035 | |a (OCoLC)1401178276 | ||
| 035 | |a (DE-599)BVBBV048896653 | ||
| 040 | |a DE-604 |b ger |e rda | ||
| 041 | 0 | |a eng | |
| 049 | |a DE-473 | ||
| 084 | |a ST 271 |0 (DE-625)143639: |2 rvk | ||
| 100 | 1 | |a Conger, Steve |e Verfasser |0 (DE-588)1301873330 |4 aut | |
| 245 | 1 | 0 | |a Learning SQL on PostgreSQL |c Steven Conger |
| 264 | 1 | |a Portland, Oregon |b Franklin, Beedle & Associates Inc. |c [2021] | |
| 264 | 4 | |c © 2021 | |
| 300 | |a X, 310 SeitenSeiten | ||
| 336 | |b txt |2 rdacontent | ||
| 337 | |b n |2 rdamedia | ||
| 338 | |b nc |2 rdacarrier | ||
| 650 | 0 | 7 | |a PostgreSQL |0 (DE-588)4690455-4 |2 gnd |9 rswk-swf |
| 650 | 0 | 7 | |a Datenmodell |0 (DE-588)4192516-6 |2 gnd |9 rswk-swf |
| 650 | 0 | 7 | |a SQL |0 (DE-588)4134010-3 |2 gnd |9 rswk-swf |
| 653 | 0 | |a SQL (Computer program language) | |
| 653 | 0 | |a Computer programming | |
| 653 | 0 | |a Querying (Computer science) | |
| 653 | 0 | |a SQL (Langage de programmation) | |
| 689 | 0 | 0 | |a Datenmodell |0 (DE-588)4192516-6 |D s |
| 689 | 0 | 1 | |a SQL |0 (DE-588)4134010-3 |D s |
| 689 | 0 | |5 DE-604 | |
| 689 | 1 | 0 | |a PostgreSQL |0 (DE-588)4690455-4 |D s |
| 689 | 1 | |5 DE-604 | |
| 776 | 0 | 8 | |i Online version |z 978--159028-288-5 |
| 856 | 4 | 2 | |m Digitalisierung UB Bamberg - ADAM Catalogue Enrichment |q application/pdf |u http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=034161155&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |3 Inhaltsverzeichnis |
| 999 | |a oai:aleph.bib-bvb.de:BVB01-034161155 | ||
Datensatz im Suchindex
| _version_ | 1804185056719142912 |
|---|---|
| adam_text | CONTENTS Preface.........................................................................................vii CHAPTER 1 1.1 1.2 1.3 1.4 1.5 1.6 1.7 CHAPTER 2 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10 CHAPTER 3 3.1 3.2 PRELIMINARIES 1 Objectives............................................................... ...................... SQL................................................................................................. 1 SQL vs. NoSQL............................................................................... 5 Demand.......................................................................................... 6 Postg reSQL.................................................................................... 6 The Relational Model in Brief...................................................... 7 Our Databases: communityassistpg and techcertificate..................................................................... 14 1.6.1 Community Assist.............................................................. 14 1.6.2 The techcertificate Database.......................................... 20 Using pgAdmin........................................................................... 30 Chapter Summary..................................................................... 34 Exercises.....................................................................................34 SINGLE-TABLE SELECTS 37 Objectives................................................................................... 37 Basic SELECT................................................................................37
ORDER BY..................................................................................... 39 Aliasing Column Names.............................................................. 44 The WHERE Clause................................. 45 AND, OR, NOT, BETWEEN............................................................ 48 BETWEEN.......................................................................................54 Null Values................................................................................... 55 LIKE with Wildcards..................................................................... 57 CASE............................................................................................. 59 DISTINCT...................................................................................... 60 Chapter Summary..................................................................... 63 Exercises.....................................................................................64 SELECT, CALCULATIONS, AND BUILT-IN FUNCTIONS 67 Objectives................................................................................... 67 Calculations................................................................................. 67 Math Functions............................................................................ 70 111
IV CONTENTS 3.3 3.4 3.5 3.6 3.7 CHAPTER 4 4.1 4.2 4.3 4.4 4.5 4.6 CHAPTER 5 5.1 5.2 5.3 5.4 5.5 CHAPTER 6 Date and Time Functions.............................................................. 75 Operators for Manipulating Dates............................................. 77 EXTRACT.......................................................................................... 78 String Functions............................................................................. 80 Aggregate Functions..................................................................... 87 Chapter Summary........................................................................ 94 Exercises......................................................................................... 95 JOINS 97 Objectives....................................................................................... 97 JOINS................................................................................................. 97 CROSS JOINS.................................................................................... 99 INNER JOINS.................................................................................. 102 INNER JOINS and Aggregates..................................................... 110 OUTER JOINS................................................................................ 113 4.5.1 LEFT OUTER JOIN................................................................. 113 4.5.2 RIGHT OUTER JOIN............................................................... 114 4.5.3 FULL
JOINS........................................................................... 117 Rarer JOINS.................................................................................... 118 4.6.1 NATURAL JOINS...................................................................118 4.6.2 SELF JOINS............................................................................. 119 4.6.3 NON-EQUI JOINS...................................................................120 Chapter Summary...................................................................... 122 Exercises.................................................................................... ... SUBQUERIES 125 Objectives.....................................................................................125 Subqueries in the WHERE Clause................................................125 Subqueries in the SELECT Clause.............................................. 130 Subqueries in the FROM Clause (Table or Derived Expressions).................................................. 133 Common Table Expressions.........................................................136 Correlated Subqueries.......... ..................................................... 138 Chapter Summary...................................................................... 140 Exercises...................................................................................... 141 MANIPULATING DATA 145 6.1 6.2 Objectives.................................................................................... 145
INSERT......................................................... 145 UPDATE................................................ . ........................................ 6.3 6.4 DELETE............................................ ............................................ 156 TRUNCATE..................................................................................... 156 6.5 Relational Constraints on
CONTENTS INSERT, UPDATE, and DELETE..................................................... 156 Chapter Summary....................................................................... 157 Exercises........................................................................................ 158 CHAPTER? CREATING AND 161 ALTERING TABLES 7.1 7.2 7.3 7.4 CHAPTERS 8.1 8.2 CHAPTERS 9.1 9.2 Objectives...................................................................................... 161 Creating Tables............................................................................... 161 Table Columns and Their Data Types and Constraints............................................................................. 163 7.2.1 Data Types in More Detail.................................................... 168 Altering an Existing Table............................................................ 171 Temporary Tables........................................................................... 176 Chapter Summary....................................................................... 179 Exercises........................................................................................ 180 SCHEMAS AND VIEWS 183 Objectives............................................................... 183 Schemas......................................................................................... 183 Views.............................................................................................. 185 8.2.1 Updatable View......................................................................190 8.2.2 The WITH CHECK
OPTION Clause........................................194 8.2.3 Materialized Views................................................................ 195 Chapter Summary....................................................................... 198 Exercises........................................................................................ 199 SET OPERATORS AND WINDOW FUNCTIONS 201 Objectives...................................................................................... 201 Set Operators.................................................................................. 201 9.1.1 UNION................................................................................... 201 9.1.2 INTERSECT.............................................................................. 204 9.1.3 EXCEPT................................................................................... 206 Window Functions........................................................................ 208 9.2.1 RANK....................................................................................... 210 9.2.2 DENSE_RANK......................................................................... 211 9.2.3 ROW_NUMBER..................................................................... 211 9.2.4 FIRST_VALUE and LAST_VALUE........................................... 214 Chapter Summary....................................................................... 216 Exercises........................................................................................ 217 V
VI CONTENTS CHAPTER 10 10.1 USER-DEFINED FUNCTIONS, STORED PROCEDURES, AND TRIGGERS 221 Objectives.............................................................................. 221 User-Defined Functions.......................................................... 222 10.1.1 User-defined Functions Returning a Table.................... 228 10.2 10.3 CHAPTER 11 11.1 11.2 11.3 11.4 Stored Procedures.................................................................. 229 Trigger Functions................................................................... 235 Chapter Summary................................................................ 240 Exercises................................................................................241 ADMINISTRATIVE COMMANDS 245 Objectives.............................................................................. 245 An Overview of Catalogs inPostgreSQL................................. 245 information_schema Catalog.................................................246 Indexes.................................................................................... 250 Users, Logins, and Roles......................................................... 253 11.4.1 Altering Roles................................................................. 263 11.4.2 Dropping Roles................................................................ 264 Chapter Summary.................................................................264 Exercises................................................................................ 265 CHAPTER 12 12.1 12.2 12.3 Appendix A Appendix B
AppendixC PUTTING IT ALL TOGETHER: BEGINNINGS OF DATA ANALYSIS 267 Objectives.............................................................................. 267 From SQL to Data Analysis: an Overview.............................. 267 Determining the Current Status and Discovering Issues.......................................................... 268 Further Explorations through Data Analysis.........................276 Chapter Summary................................................................ 279 Exercises................................................................................ 280 Installing PostgreSQL and pgAdmin.....................................283 Setting up the Databases....................................................... 295 Backups.................................................................................. 301 Index.......................................................................................307
|
| adam_txt |
CONTENTS Preface.vii CHAPTER 1 1.1 1.2 1.3 1.4 1.5 1.6 1.7 CHAPTER 2 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10 CHAPTER 3 3.1 3.2 PRELIMINARIES 1 Objectives. . SQL. 1 SQL vs. NoSQL. 5 Demand. 6 Postg reSQL. 6 The Relational Model in Brief. 7 Our Databases: communityassistpg and techcertificate. 14 1.6.1 Community Assist. 14 1.6.2 The techcertificate Database. 20 Using pgAdmin. 30 Chapter Summary. 34 Exercises.34 SINGLE-TABLE SELECTS 37 Objectives. 37 Basic SELECT.37
ORDER BY. 39 Aliasing Column Names. 44 The WHERE Clause. 45 AND, OR, NOT, BETWEEN. 48 BETWEEN.54 Null Values. 55 LIKE with Wildcards. 57 CASE. 59 DISTINCT. 60 Chapter Summary. 63 Exercises.64 SELECT, CALCULATIONS, AND BUILT-IN FUNCTIONS 67 Objectives. 67 Calculations. 67 Math Functions. 70 111
IV CONTENTS 3.3 3.4 3.5 3.6 3.7 CHAPTER 4 4.1 4.2 4.3 4.4 4.5 4.6 CHAPTER 5 5.1 5.2 5.3 5.4 5.5 CHAPTER 6 Date and Time Functions. 75 Operators for Manipulating Dates. 77 EXTRACT. 78 String Functions. 80 Aggregate Functions. 87 Chapter Summary. 94 Exercises. 95 JOINS 97 Objectives. 97 JOINS. 97 CROSS JOINS. 99 INNER JOINS. 102 INNER JOINS and Aggregates. 110 OUTER JOINS. 113 4.5.1 LEFT OUTER JOIN. 113 4.5.2 RIGHT OUTER JOIN. 114 4.5.3 FULL
JOINS. 117 Rarer JOINS. 118 4.6.1 NATURAL JOINS.118 4.6.2 SELF JOINS. 119 4.6.3 NON-EQUI JOINS.120 Chapter Summary. 122 Exercises. . SUBQUERIES 125 Objectives.125 Subqueries in the WHERE Clause.125 Subqueries in the SELECT Clause. 130 Subqueries in the FROM Clause (Table or Derived Expressions). 133 Common Table Expressions.136 Correlated Subqueries. . 138 Chapter Summary. 140 Exercises. 141 MANIPULATING DATA 145 6.1 6.2 Objectives. 145
INSERT. 145 UPDATE. . . 6.3 6.4 DELETE. . 156 TRUNCATE. 156 6.5 Relational Constraints on
CONTENTS INSERT, UPDATE, and DELETE. 156 Chapter Summary. 157 Exercises. 158 CHAPTER? CREATING AND 161 ALTERING TABLES 7.1 7.2 7.3 7.4 CHAPTERS 8.1 8.2 CHAPTERS 9.1 9.2 Objectives. 161 Creating Tables. 161 Table Columns and Their Data Types and Constraints. 163 7.2.1 Data Types in More Detail. 168 Altering an Existing Table. 171 Temporary Tables. 176 Chapter Summary. 179 Exercises. 180 SCHEMAS AND VIEWS 183 Objectives. 183 Schemas. 183 Views. 185 8.2.1 Updatable View.190 8.2.2 The WITH CHECK
OPTION Clause.194 8.2.3 Materialized Views. 195 Chapter Summary. 198 Exercises. 199 SET OPERATORS AND WINDOW FUNCTIONS 201 Objectives. 201 Set Operators. 201 9.1.1 UNION. 201 9.1.2 INTERSECT. 204 9.1.3 EXCEPT. 206 Window Functions. 208 9.2.1 RANK. 210 9.2.2 DENSE_RANK. 211 9.2.3 ROW_NUMBER. 211 9.2.4 FIRST_VALUE and LAST_VALUE. 214 Chapter Summary. 216 Exercises. 217 V
VI CONTENTS CHAPTER 10 10.1 USER-DEFINED FUNCTIONS, STORED PROCEDURES, AND TRIGGERS 221 Objectives. 221 User-Defined Functions. 222 10.1.1 User-defined Functions Returning a Table. 228 10.2 10.3 CHAPTER 11 11.1 11.2 11.3 11.4 Stored Procedures. 229 Trigger Functions. 235 Chapter Summary. 240 Exercises.241 ADMINISTRATIVE COMMANDS 245 Objectives. 245 An Overview of Catalogs inPostgreSQL. 245 information_schema Catalog.246 Indexes. 250 Users, Logins, and Roles. 253 11.4.1 Altering Roles. 263 11.4.2 Dropping Roles. 264 Chapter Summary.264 Exercises. 265 CHAPTER 12 12.1 12.2 12.3 Appendix A Appendix B
AppendixC PUTTING IT ALL TOGETHER: BEGINNINGS OF DATA ANALYSIS 267 Objectives. 267 From SQL to Data Analysis: an Overview. 267 Determining the Current Status and Discovering Issues. 268 Further Explorations through Data Analysis.276 Chapter Summary. 279 Exercises. 280 Installing PostgreSQL and pgAdmin.283 Setting up the Databases. 295 Backups. 301 Index.307 |
| any_adam_object | 1 |
| any_adam_object_boolean | 1 |
| author | Conger, Steve |
| author_GND | (DE-588)1301873330 |
| author_facet | Conger, Steve |
| author_role | aut |
| author_sort | Conger, Steve |
| author_variant | s c sc |
| building | Verbundindex |
| bvnumber | BV048896653 |
| classification_rvk | ST 271 |
| ctrlnum | (OCoLC)1401178276 (DE-599)BVBBV048896653 |
| discipline | Informatik |
| discipline_str_mv | Informatik |
| format | Book |
| fullrecord | <?xml version="1.0" encoding="UTF-8"?><collection xmlns="http://www.loc.gov/MARC21/slim"><record><leader>01684nam a22004458c 4500</leader><controlfield tag="001">BV048896653</controlfield><controlfield tag="003">DE-604</controlfield><controlfield tag="005">20230908 </controlfield><controlfield tag="007">t</controlfield><controlfield tag="008">230412s2021 |||| 00||| eng d</controlfield><datafield tag="020" ind1=" " ind2=" "><subfield code="a">9781590282878</subfield><subfield code="9">978-1-59028-287-8</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(OCoLC)1401178276</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(DE-599)BVBBV048896653</subfield></datafield><datafield tag="040" ind1=" " ind2=" "><subfield code="a">DE-604</subfield><subfield code="b">ger</subfield><subfield code="e">rda</subfield></datafield><datafield tag="041" ind1="0" ind2=" "><subfield code="a">eng</subfield></datafield><datafield tag="049" ind1=" " ind2=" "><subfield code="a">DE-473</subfield></datafield><datafield tag="084" ind1=" " ind2=" "><subfield code="a">ST 271</subfield><subfield code="0">(DE-625)143639:</subfield><subfield code="2">rvk</subfield></datafield><datafield tag="100" ind1="1" ind2=" "><subfield code="a">Conger, Steve</subfield><subfield code="e">Verfasser</subfield><subfield code="0">(DE-588)1301873330</subfield><subfield code="4">aut</subfield></datafield><datafield tag="245" ind1="1" ind2="0"><subfield code="a">Learning SQL on PostgreSQL</subfield><subfield code="c">Steven Conger</subfield></datafield><datafield tag="264" ind1=" " ind2="1"><subfield code="a">Portland, Oregon</subfield><subfield code="b">Franklin, Beedle & Associates Inc.</subfield><subfield code="c">[2021]</subfield></datafield><datafield tag="264" ind1=" " ind2="4"><subfield code="c">© 2021</subfield></datafield><datafield tag="300" ind1=" " ind2=" "><subfield code="a">X, 310 SeitenSeiten</subfield></datafield><datafield tag="336" ind1=" " ind2=" "><subfield code="b">txt</subfield><subfield code="2">rdacontent</subfield></datafield><datafield tag="337" ind1=" " ind2=" "><subfield code="b">n</subfield><subfield code="2">rdamedia</subfield></datafield><datafield tag="338" ind1=" " ind2=" "><subfield code="b">nc</subfield><subfield code="2">rdacarrier</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">PostgreSQL</subfield><subfield code="0">(DE-588)4690455-4</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">Datenmodell</subfield><subfield code="0">(DE-588)4192516-6</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">SQL</subfield><subfield code="0">(DE-588)4134010-3</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="653" ind1=" " ind2="0"><subfield code="a">SQL (Computer program language)</subfield></datafield><datafield tag="653" ind1=" " ind2="0"><subfield code="a">Computer programming</subfield></datafield><datafield tag="653" ind1=" " ind2="0"><subfield code="a">Querying (Computer science)</subfield></datafield><datafield tag="653" ind1=" " ind2="0"><subfield code="a">SQL (Langage de programmation)</subfield></datafield><datafield tag="689" ind1="0" ind2="0"><subfield code="a">Datenmodell</subfield><subfield code="0">(DE-588)4192516-6</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="0" ind2="1"><subfield code="a">SQL</subfield><subfield code="0">(DE-588)4134010-3</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="0" ind2=" "><subfield code="5">DE-604</subfield></datafield><datafield tag="689" ind1="1" ind2="0"><subfield code="a">PostgreSQL</subfield><subfield code="0">(DE-588)4690455-4</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="1" ind2=" "><subfield code="5">DE-604</subfield></datafield><datafield tag="776" ind1="0" ind2="8"><subfield code="i">Online version</subfield><subfield code="z">978--159028-288-5</subfield></datafield><datafield tag="856" ind1="4" ind2="2"><subfield code="m">Digitalisierung UB Bamberg - ADAM Catalogue Enrichment</subfield><subfield code="q">application/pdf</subfield><subfield code="u">http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=034161155&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA</subfield><subfield code="3">Inhaltsverzeichnis</subfield></datafield><datafield tag="999" ind1=" " ind2=" "><subfield code="a">oai:aleph.bib-bvb.de:BVB01-034161155</subfield></datafield></record></collection> |
| id | DE-604.BV048896653 |
| illustrated | Not Illustrated |
| index_date | 2024-07-03T21:49:42Z |
| indexdate | 2024-07-10T09:49:10Z |
| institution | BVB |
| isbn | 9781590282878 |
| language | English |
| oai_aleph_id | oai:aleph.bib-bvb.de:BVB01-034161155 |
| oclc_num | 1401178276 |
| open_access_boolean | |
| owner | DE-473 DE-BY-UBG |
| owner_facet | DE-473 DE-BY-UBG |
| physical | X, 310 SeitenSeiten |
| publishDate | 2021 |
| publishDateSearch | 2021 |
| publishDateSort | 2021 |
| publisher | Franklin, Beedle & Associates Inc. |
| record_format | marc |
| spelling | Conger, Steve Verfasser (DE-588)1301873330 aut Learning SQL on PostgreSQL Steven Conger Portland, Oregon Franklin, Beedle & Associates Inc. [2021] © 2021 X, 310 SeitenSeiten txt rdacontent n rdamedia nc rdacarrier PostgreSQL (DE-588)4690455-4 gnd rswk-swf Datenmodell (DE-588)4192516-6 gnd rswk-swf SQL (DE-588)4134010-3 gnd rswk-swf SQL (Computer program language) Computer programming Querying (Computer science) SQL (Langage de programmation) Datenmodell (DE-588)4192516-6 s SQL (DE-588)4134010-3 s DE-604 PostgreSQL (DE-588)4690455-4 s Online version 978--159028-288-5 Digitalisierung UB Bamberg - ADAM Catalogue Enrichment application/pdf http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=034161155&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA Inhaltsverzeichnis |
| spellingShingle | Conger, Steve Learning SQL on PostgreSQL PostgreSQL (DE-588)4690455-4 gnd Datenmodell (DE-588)4192516-6 gnd SQL (DE-588)4134010-3 gnd |
| subject_GND | (DE-588)4690455-4 (DE-588)4192516-6 (DE-588)4134010-3 |
| title | Learning SQL on PostgreSQL |
| title_auth | Learning SQL on PostgreSQL |
| title_exact_search | Learning SQL on PostgreSQL |
| title_exact_search_txtP | Learning SQL on PostgreSQL |
| title_full | Learning SQL on PostgreSQL Steven Conger |
| title_fullStr | Learning SQL on PostgreSQL Steven Conger |
| title_full_unstemmed | Learning SQL on PostgreSQL Steven Conger |
| title_short | Learning SQL on PostgreSQL |
| title_sort | learning sql on postgresql |
| topic | PostgreSQL (DE-588)4690455-4 gnd Datenmodell (DE-588)4192516-6 gnd SQL (DE-588)4134010-3 gnd |
| topic_facet | PostgreSQL Datenmodell SQL |
| url | http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=034161155&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |
| work_keys_str_mv | AT congersteve learningsqlonpostgresql |