Sql Starter Dml Toc
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Sql Starter Dml Toc as PDF for free.
More details
- Words: 1,019
- Pages: 10
Lewis Cunningham
The SQL Starter Series Book 1 - DML An Introduction to the SQL Data Manipulation Language SQL is
Used By ●
Oracle
●
MySQL
●
Postgres
●
Ingres
●
DB2
●
SQL Server
●
Firebird
●
Other SQL Databases
Copyright Notice © Copyright 2008 Lewis R Cunningham All Rights Reserved. All trademarks and service marks contained within this publication are the properties of their respective owners. The material in this publication (print and electronic) is protected under International and Federal Copyright Laws and Treaties, and as such, any unauthorized reprint or use of this material is strictly prohibited. The material in this electronic publication can be stored only on one computer at one time. You may not copy, forward, or transfer this publication or any part of it, whether in electronic or printed form, to another person or entity. Reproduction or translation of any part of this work without the permission of the copyright holder is against the law.
You Do Not Have Resale Or Giveaway Rights To The Electronic Version Of This Book
Table of Contents
Table of Contents Copyright Notice......................................................................................................................2 Preface....................................................................................................................................6 About this Book...................................................................................................................6 Conventions........................................................................................................................6 About the Author.................................................................................................................6 Chapter 1 - Introduction...........................................................................................................8 SQL.....................................................................................................................................8 DML.....................................................................................................................................9 DDL.....................................................................................................................................9 Data Types........................................................................................................................10 String..........................................................................................................................................11 Numeric......................................................................................................................................13 Date............................................................................................................................................14 Boolean.......................................................................................................................................15 Expressions and Operators..............................................................................................15 Common SQL Functions...................................................................................................17 String Functions.........................................................................................................................17 Numeric Functions.....................................................................................................................18 Date Functions............................................................................................................................18 Conversion Functions.................................................................................................................19 Aggregate Functions..................................................................................................................19 Chapter 2 - Transaction Control............................................................................................21 Begin Transaction/End Transaction..................................................................................21 Rollback............................................................................................................................22 Savepoint..........................................................................................................................23 Commit..............................................................................................................................24 Chapter 3 - Viewing Data......................................................................................................26 Thinking in Sets.................................................................................................................26 Select................................................................................................................................27 The SQL Starter
DatabaseWisdom.com
3
Table of Contents From..................................................................................................................................30 Where................................................................................................................................32 Equality......................................................................................................................................33 Greater Than and Less Than.......................................................................................................33 Between......................................................................................................................................34 Null.............................................................................................................................................34 Wild Cards..................................................................................................................................35 Multiple Where Criteria.............................................................................................................36 IN................................................................................................................................................38 Order By............................................................................................................................38 Group By...........................................................................................................................40 Having .......................................................................................................................................41 Querying Multiple Tables...................................................................................................42 Inner Join....................................................................................................................................43 Outer Join...................................................................................................................................45 Full Outer Join............................................................................................................................47 Cartesian Join.............................................................................................................................48 Sub-queries.......................................................................................................................50 Union.................................................................................................................................52 Union All....................................................................................................................................53 Minus.................................................................................................................................55 Calling SQL Functions......................................................................................................56 Limiting Results.................................................................................................................58 Rownum Syntax.........................................................................................................................58 Limit Syntax...............................................................................................................................58 Chapter 4 - Adding Data........................................................................................................59 Insert.................................................................................................................................59 Multi-row Insert..................................................................................................................61 Insert Select......................................................................................................................61 Chapter 5 - Updating Data....................................................................................................63 Chapter 6 - Removing Data..................................................................................................68 Appendix A – Getting a Database To Start With...................................................................70 Oracle * Recommended Download..................................................................................70 The SQL Starter
DatabaseWisdom.com
4
Table of Contents MySQL * Recommended Download.................................................................................71 Postgres............................................................................................................................71 MS SQL Server Express...................................................................................................71 DB2 Express.....................................................................................................................72 Firebird..............................................................................................................................73 Ingres................................................................................................................................73 Appendix B – Functions By Database..................................................................................74 Appendix C – Data Types By Database..............................................................................105 Appendix C – The Data Dictionary......................................................................................111 Index....................................................................................................................................115
The SQL Starter
DatabaseWisdom.com
5
Preface
Preface About this Book The idea of doing this book came about from the confluence of several things. I already had one traditionally published book and did not really enjoy the experience. I am frequently asked to recommend a book for those new to databases looking to learn the basics. Finally, I think traditional books contain too much fluff and are too expensive. My goal with this book is to get a no nonsense reference for new SQL users out to market quickly and for a reasonable price. With electronic delivery and Print On Demand (POD) publishing, the technology is there to accomplish the speed and price I am looking for. I sat down and wrote an outline of what I would want to see in a SQL starter book. That outline gave me my title and the contents you will find below.
Conventions There aren't too many conventions in the following book. I tried to keep keywords in upper case when writing code. Code looks like this.
Notes and other pertinent information looks like this. If you are reading this book electronically, the links are live. You can click through and get to the web site referenced.
About the Author With over 20 years of multi-vendor and open source database experience as a database developer, data modeler and architect, over 15 years of that using the Oracle database, The SQL Starter
DatabaseWisdom.com
6
Preface Lewis Cunningham is one of a small group of people chosen to be an Oracle Ace Director. As an ACE Director, Lewis is expected to provide feedback to Oracle and to promote Oracle via user groups and conferences. Lewis has published numerous papers and maintains a very popular blog, An Expert's Guide to Oracle Technology and a database tutorial site, Database Wisdom. Lewis is a published author, having written the ultimate guide to the open source Postgres based EnterpriseDB Advanced Server (since renamed Postgres Plus Advanced Server). The book, EnterpriseDB: The Definitive Reference, was the first reference guide available for this open source database. Lewis's specialties revolve around anything having to do with databases: data architecture, data modeling, data warehousing, business intelligence, open source databases, XML databases, replication and cloud computing. Lewis is a Certified PL/SQL Developer and is a member of the Oracle Developer Tools User Group (ODTUG), Independent Oracle User Group (IOUG), the Suncoast Oracle user Group (SOUG) and the Association for Computing Machinery (ACM).
The SQL Starter
DatabaseWisdom.com
7
Index
Index Ace...................................................................................................................................................7, 51 Aggregate Functions............................................................................................................................19 BEGIN TRANSACTION..............................................................................................................21, 22 Between................................................................................................................................................34 BIGINT................................................................................................................................................14 BINARY FLOAT.................................................................................................................................14 Boolean................................................................................................................................................15 Cartesian Join.......................................................................................................................................48 CHAR..................................................................................................................................................12 CHARACTER.....................................................................................................................................12 CLOB...................................................................................................................................................12 Commit..............................................................................................................................21, 22, 24, 25 Common SQL Functions.....................................................................................................................17 Conversion Functions..........................................................................................................................19 Data Definition Language................................................................................................................9, 22 Data Manipulation Language.............................................................................................................1, 9 Data Types............................................................................................................................................10 Date......................................................................................................................................................14 Date Functions.....................................................................................................................................18 DATETIME..........................................................................................................................................14 Db2 Express.........................................................................................................................................72 Ddl..................................................................................................................8, 9, 10, 21, 22, 53, 54, 69 DELETE..............................................................................................................................................68 DML...............................................................................................................1, 9, 10, 21, 22, 23, 26, 68 DOUBLE.............................................................................................................................................14 DUAL.......................................................................................................19, 28, 29, 49, 53, 56, 57, 113 END TRANSACTION............................................................................................................21, 22, 24 Equality................................................................................................................................................33 Expressions..........................................................................................................................................15 Firebird.............................................................................................................................................1, 73 FLOAT.................................................................................................................................................14 Foreign key..........................................................................................................................................42 From.....................................................................................................................................................30 Full Outer Join.....................................................................................................................................47 Greater Than........................................................................................................................................33 Group By..............................................................................................................................................40 Having .................................................................................................................................................41 IN.........................................................................................................................................................38 The SQL Starter
DatabaseWisdom.com
8
Index Inner Join.............................................................................................................................................43 Insert....................................................................................................................................................59 Insert Select..........................................................................................................................................61 INT.......................................................................................................................................................14 INTEGER............................................................................................................................................14 Join.......................................................................................................................................................43 Less Than.............................................................................................................................................33 Limit.....................................................................................................................................................58 Minus...................................................................................................................................................55 MS SQL Server Express......................................................................................................................71 MySQL................................................................................................................................1, 26, 61, 71 Null......................................................................................................................................................34 NUMBER............................................................................................................................................14 NUMERIC.....................................................................................................................................13, 14 Numeric Functions...............................................................................................................................18 Oracle.....................................................................1, 6, 7, 22, 23, 25, 26, 28, 30, 35, 45, 58, 61, 70, 72 Order By...............................................................................................................................................38 Outer Join.............................................................................................................................................45 Postgres........................................................................................................................................1, 7, 61 Primary key..........................................................................................................................................42 REAL...................................................................................................................................................14 ROLLBACK......................................................................................................................21, 22, 23, 24 Rownum...............................................................................................................................................58 Savepoint........................................................................................................................................23, 24 Select....................................................................................................................................................27 Semi-colon.............................................................................................................................................8 SMALLINT.........................................................................................................................................14 SQL. 1, 7, 8, 9, 10, 21, 23, 25, 26, 27, 30, 31, 32, 33, 35, 36, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 61, 62, 63, 64, 65, 66, 68, 69 String....................................................................................................................................................11 String Functions...................................................................................................................................17 Sub-queries..........................................................................................................................................50 TEXT...................................................................................................................................................12 Timestamp............................................................................................................................................14 Transaction...................................................................................................................21, 22, 23, 24, 25 Transaction Control..............................................................................................................................21 Union....................................................................................................................................................52 Union All..............................................................................................................................................53 UPDATE..............................................................................................................................................63 VARCHAR..........................................................................................................................................12 The SQL Starter
DatabaseWisdom.com
9
Index VARCHAR2........................................................................................................................................12 Where...................................................................................................................................................32 Wild Cards...........................................................................................................................................35
The SQL Starter
DatabaseWisdom.com
10
The SQL Starter Series Book 1 - DML An Introduction to the SQL Data Manipulation Language SQL is
Used By ●
Oracle
●
MySQL
●
Postgres
●
Ingres
●
DB2
●
SQL Server
●
Firebird
●
Other SQL Databases
Copyright Notice © Copyright 2008 Lewis R Cunningham All Rights Reserved. All trademarks and service marks contained within this publication are the properties of their respective owners. The material in this publication (print and electronic) is protected under International and Federal Copyright Laws and Treaties, and as such, any unauthorized reprint or use of this material is strictly prohibited. The material in this electronic publication can be stored only on one computer at one time. You may not copy, forward, or transfer this publication or any part of it, whether in electronic or printed form, to another person or entity. Reproduction or translation of any part of this work without the permission of the copyright holder is against the law.
You Do Not Have Resale Or Giveaway Rights To The Electronic Version Of This Book
Table of Contents
Table of Contents Copyright Notice......................................................................................................................2 Preface....................................................................................................................................6 About this Book...................................................................................................................6 Conventions........................................................................................................................6 About the Author.................................................................................................................6 Chapter 1 - Introduction...........................................................................................................8 SQL.....................................................................................................................................8 DML.....................................................................................................................................9 DDL.....................................................................................................................................9 Data Types........................................................................................................................10 String..........................................................................................................................................11 Numeric......................................................................................................................................13 Date............................................................................................................................................14 Boolean.......................................................................................................................................15 Expressions and Operators..............................................................................................15 Common SQL Functions...................................................................................................17 String Functions.........................................................................................................................17 Numeric Functions.....................................................................................................................18 Date Functions............................................................................................................................18 Conversion Functions.................................................................................................................19 Aggregate Functions..................................................................................................................19 Chapter 2 - Transaction Control............................................................................................21 Begin Transaction/End Transaction..................................................................................21 Rollback............................................................................................................................22 Savepoint..........................................................................................................................23 Commit..............................................................................................................................24 Chapter 3 - Viewing Data......................................................................................................26 Thinking in Sets.................................................................................................................26 Select................................................................................................................................27 The SQL Starter
DatabaseWisdom.com
3
Table of Contents From..................................................................................................................................30 Where................................................................................................................................32 Equality......................................................................................................................................33 Greater Than and Less Than.......................................................................................................33 Between......................................................................................................................................34 Null.............................................................................................................................................34 Wild Cards..................................................................................................................................35 Multiple Where Criteria.............................................................................................................36 IN................................................................................................................................................38 Order By............................................................................................................................38 Group By...........................................................................................................................40 Having .......................................................................................................................................41 Querying Multiple Tables...................................................................................................42 Inner Join....................................................................................................................................43 Outer Join...................................................................................................................................45 Full Outer Join............................................................................................................................47 Cartesian Join.............................................................................................................................48 Sub-queries.......................................................................................................................50 Union.................................................................................................................................52 Union All....................................................................................................................................53 Minus.................................................................................................................................55 Calling SQL Functions......................................................................................................56 Limiting Results.................................................................................................................58 Rownum Syntax.........................................................................................................................58 Limit Syntax...............................................................................................................................58 Chapter 4 - Adding Data........................................................................................................59 Insert.................................................................................................................................59 Multi-row Insert..................................................................................................................61 Insert Select......................................................................................................................61 Chapter 5 - Updating Data....................................................................................................63 Chapter 6 - Removing Data..................................................................................................68 Appendix A – Getting a Database To Start With...................................................................70 Oracle * Recommended Download..................................................................................70 The SQL Starter
DatabaseWisdom.com
4
Table of Contents MySQL * Recommended Download.................................................................................71 Postgres............................................................................................................................71 MS SQL Server Express...................................................................................................71 DB2 Express.....................................................................................................................72 Firebird..............................................................................................................................73 Ingres................................................................................................................................73 Appendix B – Functions By Database..................................................................................74 Appendix C – Data Types By Database..............................................................................105 Appendix C – The Data Dictionary......................................................................................111 Index....................................................................................................................................115
The SQL Starter
DatabaseWisdom.com
5
Preface
Preface About this Book The idea of doing this book came about from the confluence of several things. I already had one traditionally published book and did not really enjoy the experience. I am frequently asked to recommend a book for those new to databases looking to learn the basics. Finally, I think traditional books contain too much fluff and are too expensive. My goal with this book is to get a no nonsense reference for new SQL users out to market quickly and for a reasonable price. With electronic delivery and Print On Demand (POD) publishing, the technology is there to accomplish the speed and price I am looking for. I sat down and wrote an outline of what I would want to see in a SQL starter book. That outline gave me my title and the contents you will find below.
Conventions There aren't too many conventions in the following book. I tried to keep keywords in upper case when writing code. Code looks like this.
Notes and other pertinent information looks like this. If you are reading this book electronically, the links are live. You can click through and get to the web site referenced.
About the Author With over 20 years of multi-vendor and open source database experience as a database developer, data modeler and architect, over 15 years of that using the Oracle database, The SQL Starter
DatabaseWisdom.com
6
Preface Lewis Cunningham is one of a small group of people chosen to be an Oracle Ace Director. As an ACE Director, Lewis is expected to provide feedback to Oracle and to promote Oracle via user groups and conferences. Lewis has published numerous papers and maintains a very popular blog, An Expert's Guide to Oracle Technology and a database tutorial site, Database Wisdom. Lewis is a published author, having written the ultimate guide to the open source Postgres based EnterpriseDB Advanced Server (since renamed Postgres Plus Advanced Server). The book, EnterpriseDB: The Definitive Reference, was the first reference guide available for this open source database. Lewis's specialties revolve around anything having to do with databases: data architecture, data modeling, data warehousing, business intelligence, open source databases, XML databases, replication and cloud computing. Lewis is a Certified PL/SQL Developer and is a member of the Oracle Developer Tools User Group (ODTUG), Independent Oracle User Group (IOUG), the Suncoast Oracle user Group (SOUG) and the Association for Computing Machinery (ACM).
The SQL Starter
DatabaseWisdom.com
7
Index
Index Ace...................................................................................................................................................7, 51 Aggregate Functions............................................................................................................................19 BEGIN TRANSACTION..............................................................................................................21, 22 Between................................................................................................................................................34 BIGINT................................................................................................................................................14 BINARY FLOAT.................................................................................................................................14 Boolean................................................................................................................................................15 Cartesian Join.......................................................................................................................................48 CHAR..................................................................................................................................................12 CHARACTER.....................................................................................................................................12 CLOB...................................................................................................................................................12 Commit..............................................................................................................................21, 22, 24, 25 Common SQL Functions.....................................................................................................................17 Conversion Functions..........................................................................................................................19 Data Definition Language................................................................................................................9, 22 Data Manipulation Language.............................................................................................................1, 9 Data Types............................................................................................................................................10 Date......................................................................................................................................................14 Date Functions.....................................................................................................................................18 DATETIME..........................................................................................................................................14 Db2 Express.........................................................................................................................................72 Ddl..................................................................................................................8, 9, 10, 21, 22, 53, 54, 69 DELETE..............................................................................................................................................68 DML...............................................................................................................1, 9, 10, 21, 22, 23, 26, 68 DOUBLE.............................................................................................................................................14 DUAL.......................................................................................................19, 28, 29, 49, 53, 56, 57, 113 END TRANSACTION............................................................................................................21, 22, 24 Equality................................................................................................................................................33 Expressions..........................................................................................................................................15 Firebird.............................................................................................................................................1, 73 FLOAT.................................................................................................................................................14 Foreign key..........................................................................................................................................42 From.....................................................................................................................................................30 Full Outer Join.....................................................................................................................................47 Greater Than........................................................................................................................................33 Group By..............................................................................................................................................40 Having .................................................................................................................................................41 IN.........................................................................................................................................................38 The SQL Starter
DatabaseWisdom.com
8
Index Inner Join.............................................................................................................................................43 Insert....................................................................................................................................................59 Insert Select..........................................................................................................................................61 INT.......................................................................................................................................................14 INTEGER............................................................................................................................................14 Join.......................................................................................................................................................43 Less Than.............................................................................................................................................33 Limit.....................................................................................................................................................58 Minus...................................................................................................................................................55 MS SQL Server Express......................................................................................................................71 MySQL................................................................................................................................1, 26, 61, 71 Null......................................................................................................................................................34 NUMBER............................................................................................................................................14 NUMERIC.....................................................................................................................................13, 14 Numeric Functions...............................................................................................................................18 Oracle.....................................................................1, 6, 7, 22, 23, 25, 26, 28, 30, 35, 45, 58, 61, 70, 72 Order By...............................................................................................................................................38 Outer Join.............................................................................................................................................45 Postgres........................................................................................................................................1, 7, 61 Primary key..........................................................................................................................................42 REAL...................................................................................................................................................14 ROLLBACK......................................................................................................................21, 22, 23, 24 Rownum...............................................................................................................................................58 Savepoint........................................................................................................................................23, 24 Select....................................................................................................................................................27 Semi-colon.............................................................................................................................................8 SMALLINT.........................................................................................................................................14 SQL. 1, 7, 8, 9, 10, 21, 23, 25, 26, 27, 30, 31, 32, 33, 35, 36, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 61, 62, 63, 64, 65, 66, 68, 69 String....................................................................................................................................................11 String Functions...................................................................................................................................17 Sub-queries..........................................................................................................................................50 TEXT...................................................................................................................................................12 Timestamp............................................................................................................................................14 Transaction...................................................................................................................21, 22, 23, 24, 25 Transaction Control..............................................................................................................................21 Union....................................................................................................................................................52 Union All..............................................................................................................................................53 UPDATE..............................................................................................................................................63 VARCHAR..........................................................................................................................................12 The SQL Starter
DatabaseWisdom.com
9
Index VARCHAR2........................................................................................................................................12 Where...................................................................................................................................................32 Wild Cards...........................................................................................................................................35
The SQL Starter
DatabaseWisdom.com
10
Related Documents
Sql Starter Dml Toc
November 2019 21
Sql Bag 2 Dml
May 2020 13
Sql Bag 2 Dml Update
May 2020 6
Dml
November 2019 28
Dml
May 2020 22
Starter
November 2019 69More Documents from ""
Lewis Cunningham-oraclexe
August 2019 19
Oracle 10g Security And Audit
August 2019 37
Oracle Streams - Step By Step Doc
August 2019 28
Lewis Cunningham-oracle Xml
August 2019 25
10 Questions On Oracle Application Express
August 2019 20